案例研究：周业务回顾——具体实现
我的理解
WBR 实现过程展示了一套可复用的 AI 辅助编程工作法：从一个复杂 prompt 开始探索能力边界，遇到报错直接粘贴错误信息迭代，根据上下文是否“干净”来决定用对话还是编辑模式。最关键的判断力在于：何时用“对话”（上下文已合适，如直接粘贴错误信息），何时切换到“编辑”（上下文开始混乱，如多轮错误修复后手动清理 prompt）。最终代码起到“胶水”作用，将三步手工流程压缩为一步——这是原理而非技巧的胜利：对输出长度限制和上下文窗口的深层理解直接指导了任务拆解的时机。核心收获是：在 GenAI 时代，理解原理比记忆具体 prompt 更重要，学会“钓鱼”远胜于得到一条鱼。
相关链接
- Ch02-L07 案例研究 周度业务回顾 问题分析 — 本课是上课问题分析的直接技术续集
- Ch02-L06 应用 使用 ChatGPT 的新方式 编辑 而非对话 — 编辑/对话切换策略在 WBR 实现中得到真实场景的验证
- Ch02-L09 自动化的力量 以及生成式 AI 的角色 — 对 WBR 自动化更深层价值的思考延伸
- Ch06-L07 重访周业务复盘 用Agent访问私有数据 — 后续模块用 Agent 进一步升级了同一个 WBR 案例
原文
Lesson 15 of 68 案例研究：周业务回顾——具体实现 / Case study: Weekly business review - Implementation 分步实现
我们会在直播课中做一次演示，这里先用示例 prompt 和 GPT 的输出介绍基本步骤。
构造 prompt——把调研中学到的东西用起来
构造 prompt 本身并不难。但有一个重要的决策点：要不要把问题拆解成更小的子问题，让 ChatGPT 更有可能一次性写出正确的程序。
=============================================
小测验：为什么把问题拆开能提高成功率？ 这是因为我们在上一节课介绍过的输出长度限制。
=============================================
=============================================
给初学者的小建议
写 prompt 是一门手艺活，做得越多，写得越好。
=============================================
但对初学者（以及面对不熟悉的 LLM）来说，常见做法是先从一个相对复杂的问题入手，如果 LLM 难以写出正确的程序（例如运行时 Python 抛出异常），再逐步拆解。这里我们会先把前两个任务（执行 SQL，以及把结果粘贴到 Excel）合在一起，因为它们在工作流中本来就是相邻的。
执行 SQL 并粘贴结果：这里我们以 SQLite 为例进行演示，但同样的方法可以轻松扩展到其他数据库引擎。注意在我们的场景里 SQL 查询是已知的。如果它比较短，可以直接放进 prompt 里；如果较长，可以用占位符来主动管理上下文窗口。只要你足够理解上下文窗口这个概念，这类优化技巧自然就会信手拈来。
小测验：尝试用一个 prompt 让 ChatGPT 同时完成这两个任务，记得使用编辑（editing）的方式。
示例 prompt
=============================================
I want to connect to a SQLite database events.db. And then execute some given SQL query (read from data.sql). After that I want to collect the result, and insert it into an Excel file WBR.xlsx. It has three tabs. I want to insert the result to the first tab (the name is Data, clean its content before insertion). Can you write a Python program for me?
=============================================
示例回复（这里只展示代码）
=============================================
import sqlite3
import pandas as pd
Path to your SQLite database
db_path = ‘events.db’
Path to your SQL query file
sql_file_path = ‘data.sql’
Path to your Excel file
excel_path = ‘WBR.xlsx’
sheet_name = ‘Data’ # The name of the first tab where you want to insert the data
Function to read SQL query from a file
def read_sql_query(file_path):
with open(file_path, 'r') as file:
return file.read()
Read the SQL query
sql_query = read_sql_query(sql_file_path)
Connect to the SQLite database
conn = sqlite3.connect(db_path)
Execute the SQL query and fetch the data into a DataFrame
df = pd.read_sql_query(sql_query, conn)
Close the database connection
conn.close()
Write the DataFrame to the Excel file, replacing the content of the first tab
with pd.ExcelWriter(excel_path, engine=‘openpyxl’, mode=‘a’, if_sheet_exists=‘replace’) as writer:
df.to_excel(writer, sheet_name=sheet_name, index=False)
print(“Data has been successfully inserted into the Excel file.”)
=============================================
同样地，所有相关文件，包括 events.db、data.sql、WBR.xlsx，都可以从这个链接下载。如果我们真的把代码粘贴并运行，会发现它确实可以跑通并完成任务：它会更新当前文件夹中的 WBR.xlsx，把 Data 选项卡中的内容替换为 SQL 查询返回的结果。这说明这个任务完全在 ChatGPT 的能力范围（也就是“舒适区”）之内，无需再拆解问题。
邮件草稿撰写：类似地，我们可以让 ChatGPT 帮我们起草一封邮件，并把 Excel 中的图表嵌入其中。
小测验：自己动手写一下 prompt 试试。
示例 prompt
=============================================
I have an Excel file called WBR.xlsx with three tabs. The second tab (called Analysis) has a pivot table that needs to be refreshed, and the third tab (called Visualization) has a chart. I’d like to first refresh the pivot table, and then include this chart in an email draft (not as an attachment, but inserted in the body). I’m using the Outlook app on Windows. Can you write a Python program to do that?
=============================================
示例回复（仅代码）
=============================================
import openpyxl
from openpyxl.chart import image
import win32com.client as win32
from PIL import ImageGrab
def refresh_pivot_table(file_path, sheet_name):
wb = openpyxl.load_workbook(file_path)
sheet = wb[sheet_name]
Assuming the pivot table is the first table on the ‘Analysis’ sheet
pivot_table = sheet._pivots[0]
pivot_table.cache.refresh()
wb.save(file_path)
wb.close()
def save_chart_as_image(file_path, sheet_name, image_path):
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
Assuming the chart is the first chart on the ‘Visualization’ sheet
chart = ws._charts[0]
x, y, cx, cy = chart.anchor
img = ws._images.add(chart, x, y, cx, cy)
img.save(image_path)
wb.close()
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
mail.Body = “Here’s the updated chart from our analysis:”
mail.Attachments.Add(image_path, 1) # 1 for AttachmentPosition in body
mail.Display(True) # Opens the draft email
Paths and parameters
excel_file = ‘WBR.xlsx’
pivot_sheet = ‘Analysis’
chart_sheet = ‘Visualization’
chart_image_path = ‘chart.png’
Process functions
refresh_pivot_table(excel_file, pivot_sheet)
save_chart_as_image(excel_file, chart_sheet, chart_image_path)
create_email_with_image(chart_image_path)
=============================================
把这段程序粘贴并运行，它实际上会抛出一个异常：
=============================================
Traceback (most recent call last):
File “paste_in_data.py”, line 38, in
from openpyxl.chart import image
ImportError: cannot import name ‘image’ from ‘openpyxl.chart’ (c:\Users\grapeot\py39\lib\site-packages\openpyxl\chart_init_
.py)
=============================================
我们可以直接把这段异常粘贴回 ChatGPT。注意这里我们刻意选择对话（conversation）模式而不是编辑（edit）模式，因为在这个具体场景下，ChatGPT 自动构造的上下文窗口已经很贴合我们的需要——即便我们手动构造，结果基本也是一样的。
然后 ChatGPT 会更新代码：
示例回复（仅代码）
=============================================
import win32com.client as win32
def refresh_pivot_table_and_chart(excel_path):
excel = win32.Dispatch(“Excel.Application”)
wb = excel.Workbooks.Open(excel_path)
excel.Visible = False
Refresh pivot table
ws = wb.Sheets(‘Analysis’)
pivot_table = ws.PivotTables(1)
pivot_table.RefreshTable()
Copy the chart from ‘Visualization’ to clipboard
chart_sheet = wb.Sheets(‘Visualization’)
chart = chart_sheet.ChartObjects(1)
chart.Copy()
Save the chart as an image
image_path = ‘chart_image.png’
chart_image = win32.Dispatch(“Excel.Application”).GetClipboardFormats()
if chart_image:
chart_paste = win32.gencache.EnsureDispatch('Excel.Application')
chart_paste.Visible = True
ws_paste = chart_paste.Workbooks.Add().Sheets(1)
ws_paste.Paste()
ws_paste.Shapes(1).CopyPicture()
ws_paste.Shapes(1).SaveAsPicture(Filename=image_path, Format=1) # Format=1 for PNG
wb.Close(SaveChanges=False)
excel.Quit()
return image_path
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
mail.HTMLBody = f”
Here’s the updated chart from our analysis:
mail.Display(True) # Opens the draft email
Path to your Excel file
excel_file = ‘WBR.xlsx’
Refresh pivot table and save the chart as an image
chart_image_path = refresh_pivot_table_and_chart(excel_file)
Create and display an email draft with the chart
create_email_with_image(chart_image_path)
=============================================
这段代码同样会抛出一些异常。具体是什么异常对我们来说并不重要，直接把它粘贴回去即可。但因为这已经是第二轮对话，如果继续直接和 ChatGPT 聊，会把上下文窗口搞乱。所以这里我们要主动管理上下文窗口，使用如下 prompt：
示例 prompt
=============================================
<The code we current use, which is pasted from above>
The code throws an exception:
<Content of the exception, pasted from Python>
=============================================
你也可以试试对话式的用法，继续和 ChatGPT 聊天并把异常贴进去。
小测验：我们的 prompt 与一次随意的 ChatGPT 对话有什么不同？
这能很好地体现上下文窗口管理的威力。使用我们这种 prompt，ChatGPT 会更新出如下代码：
示例回复（仅代码）：
=============================================
import win32com.client as win32
def refresh_pivot_table_and_chart(excel_path):
excel = win32.Dispatch(“Excel.Application”)
wb = excel.Workbooks.Open(excel_path)
excel.Visible = False
Refresh pivot table
ws = wb.Sheets(‘Analysis’)
pivot_table = ws.PivotTables(1)
pivot_table.RefreshTable()
Copy the chart from ‘Visualization’ to clipboard
chart_sheet = wb.Sheets(‘Visualization’)
chart = chart_sheet.ChartObjects(1).Chart
Save the chart as an image
image_path = os.path.abspath(‘chart_image.png’)
chart.Export(Filename=image_path)
wb.Close(SaveChanges=False)
excel.Quit()
return image_path
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
mail.HTMLBody = f”
Here’s the updated chart from our analysis:
mail.Display(True) # Opens the draft email
Path to your Excel file
excel_file = ‘WBR.xlsx’
Refresh pivot table and save the chart as an image
chart_image_path = refresh_pivot_table_and_chart(excel_file)
Create and display an email draft with the chart
create_email_with_image(chart_image_path)
=============================================
这次会得到一个 90% 正确的程序。它会先启动 Excel 应用提取图表，再把图表保存到文件；然后启动 Outlook 应用，弹出一个邮件草稿。但图片并没有显示出来。我们直接把相关的 create_email_with_image 函数粘出来（作为一种上下文窗口管理的方式），并问它该怎么办：
示例 prompt
=============================================
def create_email_with_image(image_path):
outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)
mail.To = 'recipient@example.com'
mail.Subject = 'Data Analysis Update'
mail.HTMLBody = f"<p>Here's the updated chart from our analysis:</p><img src='{image_path}'>"
mail.Display(True) # Opens the draft email
The image doesn’t show up.
=============================================
示例回复（仅代码）
=============================================
import win32com.client as win32
import os
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
Convert relative path to absolute path
abs_image_path = os.path.abspath(image_path)
print(f”Attempting to attach image at: {abs_image_path}”) # Debugging line
Check if the file actually exists before trying to attach it
if not os.path.exists(abs_image_path):
raise FileNotFoundError("The specified image file does not exist at the provided path.")
Attach the image file and set a content ID
attachment = mail.Attachments.Add(Source=abs_image_path)
attachment.PropertyAccessor.SetProperty(“http://schemas.microsoft.com/mapi/proptag/0x3712001F”, “ChartImage”)
Use the content ID in the HTML body to reference the image
mail.HTMLBody = f”
Here’s the updated chart from our analysis:
mail.Display(True) # Opens the draft email
Path to your Excel file (adjust as necessary)
excel_file = ‘WBR.xlsx’
Assume chart_image_path has been created and returned from your chart exporting function
chart_image_path = ‘chart_image.png’ # Ensure this is the correct path
Create and display an email draft with the chart
create_email_with_image(chart_image_path)
=============================================
把这个函数替换为 GPT 给出的新版本，这次我们终于得到了想要的邮件草稿：
同样地，可以运行的完整代码也可以在这个链接中找到，文件名为 example.py。注意即便是同一个 prompt，ChatGPT 也可能给出不同的回答，所以你的实际过程可能与上面的示例不同。我们也反复试过几次这些 prompt，只要把所学的方法用起来，并主动管理上下文窗口，绝大多数情况下都能得到一个正确的程序。
这里的核心要点是：在 GenAI 时代，原理与理解远比某条具体的 prompt 重要。学会“怎么钓鱼”！
识别常见卡点
不过有一个比较常见的坑需要注意。当我们使用 win32com 这个库时，必须使用绝对路径（例如 c:\code\chart.png），而不是相对路径（例如 chart.png）。ChatGPT 有时会写对，有时会写错。但我们随时可以问它“为什么我没看到图表导出来”，它会帮我们一起排查。GPT-4 仍然不是完美的，对于复杂任务，可能还是需要几轮对话或编辑才能搞定。
当然，遇到这种情况我们也可以选择把任务拆解开来。但就这个具体例子而言，我们应该能在 5 到 10 分钟内得到一个可以运行的程序——这比一位没有 AI 辅助的专业开发者要快得多。
课后作业
=============================================
ChatGPT 写出的代码仍然比较杂乱，尤其是当我们把两个任务的代码合并到一个文件（例如 example.py）之后。你能否让 ChatGPT 帮我们重构一下，让代码在未来更易于修改和维护？
=============================================
English Original Step-by-step implementation
We will do a demo in the live lecture, and introduce the basic steps with example prompts and GPT output here.
Constructing the prompt – apply research learnings
The prompt construction is straightforward. But there is an important decision point: whether we want to decompose the problem into smaller subproblems so it’ll be easier for ChatGPT to write the correct program in one shot.
=============================================
Pop quiz: Why breaking down the problem increases the chance of success? This is because of the output limit we introduced in the previous lesson.
=============================================
=============================================
Tips for beginners
Prompting is a craft. The more you do, the better you will get at it.
=============================================
But for beginners (and for unfamiliar LLMs), a common practice is to begin from a relatively complicated problem, and gradually decompose if the LLM struggles to write the correct program (e.g. when executing, python throws exceptions). Here we will begin with combining the first two tasks (SQL execution, and result pasting to Excel) together, because they are right adjacent in the workflow.
SQL execution and result pasting: Here we use SQLite as an example to showcase the usage. But it can easily extend to other database engines. Note that the SQL query is known in our scenario. We can include it in the prompt if it’s short, or use a placeholder to intentionally manage the context window. Such optimization tricks should come naturally to you if you understand the concept of context window well enough.
Pop quiz: Explore ways to prompt ChatGPT to complete the two tasks together. Remember to use editing.
Example prompt
=============================================
I want to connect to a SQLite database events.db. And then execute some given SQL query (read from data.sql). After that I want to collect the result, and insert it into an Excel file WBR.xlsx. It has three tabs. I want to insert the result to the first tab (the name is Data, clean its content before insertion). Can you write a Python program for me?
=============================================
Example response (here we include code only)
=============================================
import sqlite3
import pandas as pd
Path to your SQLite database
db_path = ‘events.db’
Path to your SQL query file
sql_file_path = ‘data.sql’
Path to your Excel file
excel_path = ‘WBR.xlsx’
sheet_name = ‘Data’ # The name of the first tab where you want to insert the data
Function to read SQL query from a file
def read_sql_query(file_path):
with open(file_path, ‘r’) as file:
return file.read()
Read the SQL query
sql_query = read_sql_query(sql_file_path)
Connect to the SQLite database
conn = sqlite3.connect(db_path)
Execute the SQL query and fetch the data into a DataFrame
df = pd.read_sql_query(sql_query, conn)
Close the database connection
conn.close()
Write the DataFrame to the Excel file, replacing the content of the first tab
with pd.ExcelWriter(excel_path, engine=‘openpyxl’, mode=‘a’, if_sheet_exists=‘replace’) as writer:
df.to_excel(writer, sheet_name=sheet_name, index=False)
print(“Data has been successfully inserted into the Excel file.”)
=============================================
Again, all the files, including events.db, data.sql, WBR.xlsx, can be downloaded from this link. If we actually paste and execute the code, we will find it is able to run and accomplish the task. This will update the WBR.xlsx in the current folder and replace the content of the data tab using what was retrieved from the SQL query. This indicates that this task is well inside the capability envelope (or “comfort zone”) of ChatGPT. And there is no need to decompose the problem here.
Email drafting: similarly, we could ask ChatGPT to draft email by including the chart from Excel.
Pop quiz: Try prompting yourself.
Example prompt
=============================================
I have an Excel file called WBR.xlsx with three tabs. The second tab (called Analysis) has a pivot table that needs to be refreshed, and the third tab (called Visualization) has a chart. I’d like to first refresh the pivot table, and then include this chart in an email draft (not as an attachment, but inserted in the body). I’m using the Outlook app on Windows. Can you write a Python program to do that?
=============================================
Example response (code only)
=============================================
import openpyxl
from openpyxl.chart import image
import win32com.client as win32
from PIL import ImageGrab
def refresh_pivot_table(file_path, sheet_name):
wb = openpyxl.load_workbook(file_path)
sheet = wb[sheet_name]
Assuming the pivot table is the first table on the ‘Analysis’ sheet
pivot_table = sheet._pivots[0]
pivot_table.cache.refresh()
wb.save(file_path)
wb.close()
def save_chart_as_image(file_path, sheet_name, image_path):
wb = openpyxl.load_workbook(file_path)
ws = wb[sheet_name]
Assuming the chart is the first chart on the ‘Visualization’ sheet
chart = ws._charts[0]
x, y, cx, cy = chart.anchor
img = ws._images.add(chart, x, y, cx, cy)
img.save(image_path)
wb.close()
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
mail.Body = “Here’s the updated chart from our analysis:”
mail.Attachments.Add(image_path, 1) # 1 for AttachmentPosition in body
mail.Display(True) # Opens the draft email
Paths and parameters
excel_file = ‘WBR.xlsx’
pivot_sheet = ‘Analysis’
chart_sheet = ‘Visualization’
chart_image_path = ‘chart.png’
Process functions
refresh_pivot_table(excel_file, pivot_sheet)
save_chart_as_image(excel_file, chart_sheet, chart_image_path)
create_email_with_image(chart_image_path)
=============================================
We can paste and execute the program. And it’ll actually throw an exception:
=============================================
Traceback (most recent call last):
File “paste_in_data.py”, line 38, in
from openpyxl.chart import image
ImportError: cannot import name ‘image’ from ‘openpyxl.chart’ (c:\Users\grapeot\py39\lib\site-packages\openpyxl\chart_init_
.py)
=============================================
We can simply paste the exception back into ChatGPT. Note here we intentionally choose the conversation style rather than edit style because in this specific case, the context window constructed by ChatGPT fits our purpose fairly well. Even if we manually construct the context window, it will look the same.
Then ChatGPT would update the code:
Example response (code only)
=============================================
import win32com.client as win32
def refresh_pivot_table_and_chart(excel_path):
excel = win32.Dispatch(“Excel.Application”)
wb = excel.Workbooks.Open(excel_path)
excel.Visible = False
Refresh pivot table
ws = wb.Sheets(‘Analysis’)
pivot_table = ws.PivotTables(1)
pivot_table.RefreshTable()
Copy the chart from ‘Visualization’ to clipboard
chart_sheet = wb.Sheets(‘Visualization’)
chart = chart_sheet.ChartObjects(1)
chart.Copy()
Save the chart as an image
image_path = ‘chart_image.png’
chart_image = win32.Dispatch(“Excel.Application”).GetClipboardFormats()
if chart_image:
chart_paste = win32.gencache.EnsureDispatch(‘Excel.Application’)
chart_paste.Visible = True
ws_paste = chart_paste.Workbooks.Add().Sheets(1)
ws_paste.Paste()
ws_paste.Shapes(1).CopyPicture()
ws_paste.Shapes(1).SaveAsPicture(Filename=image_path, Format=1) # Format=1 for PNG
wb.Close(SaveChanges=False)
excel.Quit()
return image_path
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
mail.HTMLBody = f”
Here’s the updated chart from our analysis:
mail.Display(True) # Opens the draft email
Path to your Excel file
excel_file = ‘WBR.xlsx’
Refresh pivot table and save the chart as an image
chart_image_path = refresh_pivot_table_and_chart(excel_file)
Create and display an email draft with the chart
create_email_with_image(chart_image_path)
=============================================
The code again throws some exceptions. It doesn’t even matter what the exceptions are for our purpose. We just paste it in. But here since it’s the second round of conversation, directly chatting with ChatGPT will mess up the context window. So we actively manage the context window here. And use the prompt of:
Example prompt
=============================================
<The code we current use, which is pasted from above>
The code throws an exception:
<Content of the exception, pasted from Python>
=============================================
Also feel free to try the conservation style use of ChatGPT by continuing chatting with it and pasting in the exception.
Quiz: What’s different between our prompt vs. a casual conversation with ChatGPT?
This helps illustrate the power of context window management. With our prompt, ChatGPT would update the code:
Example response (code only):
=============================================
import win32com.client as win32
def refresh_pivot_table_and_chart(excel_path):
excel = win32.Dispatch(“Excel.Application”)
wb = excel.Workbooks.Open(excel_path)
excel.Visible = False
Refresh pivot table
ws = wb.Sheets(‘Analysis’)
pivot_table = ws.PivotTables(1)
pivot_table.RefreshTable()
Copy the chart from ‘Visualization’ to clipboard
chart_sheet = wb.Sheets(‘Visualization’)
chart = chart_sheet.ChartObjects(1).Chart
Save the chart as an image
image_path = os.path.abspath(‘chart_image.png’)
chart.Export(Filename=image_path)
wb.Close(SaveChanges=False)
excel.Quit()
return image_path
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
mail.HTMLBody = f”
Here’s the updated chart from our analysis:
mail.Display(True) # Opens the draft email
Path to your Excel file
excel_file = ‘WBR.xlsx’
Refresh pivot table and save the chart as an image
chart_image_path = refresh_pivot_table_and_chart(excel_file)
Create and display an email draft with the chart
create_email_with_image(chart_image_path)
=============================================
This will give a 90% correct program. It first launches the Excel app to extract the chart, and then saves it to a file. After that, it launches the Outlook app, and displays an email draft. But the image doesn’t show up. We simply copy paste the relevant function create_email_with_image (as a means of context window management) and ask it what to do:
Example prompt
=============================================
def create_email_with_image(image_path):
outlook = win32.Dispatch('outlook.application')
mail = outlook.CreateItem(0)
mail.To = 'recipient@example.com'
mail.Subject = 'Data Analysis Update'
mail.HTMLBody = f"<p>Here's the updated chart from our analysis:</p><img src='{image_path}'>"
mail.Display(True) # Opens the draft email
The image doesn’t show up.
=============================================
Example response (code only)
=============================================
import win32com.client as win32
import os
def create_email_with_image(image_path):
outlook = win32.Dispatch(‘outlook.application’)
mail = outlook.CreateItem(0)
mail.To = ‘recipient@example.com’
mail.Subject = ‘Data Analysis Update’
Convert relative path to absolute path
abs_image_path = os.path.abspath(image_path)
print(f”Attempting to attach image at: {abs_image_path}”) # Debugging line
Check if the file actually exists before trying to attach it
if not os.path.exists(abs_image_path):
raise FileNotFoundError(“The specified image file does not exist at the provided path.”)
Attach the image file and set a content ID
attachment = mail.Attachments.Add(Source=abs_image_path)
attachment.PropertyAccessor.SetProperty(“http://schemas.microsoft.com/mapi/proptag/0x3712001F”, “ChartImage”)
Use the content ID in the HTML body to reference the image
mail.HTMLBody = f”
Here’s the updated chart from our analysis:
mail.Display(True) # Opens the draft email
Path to your Excel file (adjust as necessary)
excel_file = ‘WBR.xlsx’
Assume chart_image_path has been created and returned from your chart exporting function
chart_image_path = ‘chart_image.png’ # Ensure this is the correct path
Create and display an email draft with the chart
create_email_with_image(chart_image_path)
=============================================
Replace the function with the version GPT provides. And this time we get the email draft:
A working code can be found in this link as well, with the name example.py. Note ChatGPT may give different answers even to the same prompt. So your journey may be different from the examples shown above. We’ve also tried the prompts a few times. As long as we apply what we learned and intentionally maintain the context window, it will most likely result in a correct program.
The takeaway is that, with GenAI, the principle and understanding is more important than any prompts. Learn to fish!
Identifying common blockers
There is a caveat that happens more often than others though. When we use the library win32com, we need to specify an absolute path (e.g. c:\code\chart.png) instead of a relative path (e.g. chart.png). Sometimes ChatGPT got this right, sometimes got it wrong. But we can always ask it why I didn’t see the chart image exported. And it will help us figure it out. GPT-4 is still not perfect, and it may still take a few rounds of conversations / editing to make things right, especially for complicated tasks.
We can also decompose the task in this case. But for this specific example, we should be able to get a working program in 5-10 minutes, which is much faster than a professional developer (without AI).
Homework
=============================================
The code written by ChatGPT is still messy, especially after we combine the code for both tasks together into a single file (like the example.py). Can you ask ChatGPT to refactor it so it’ll be easier to change and maintain in the future?
=============================================