pandas Python：将图像和数据帧写入同一个 excel 文件

Python: Writing Images and dataframes to the same excel file

提问by Angus Gray

I'm creating an excel dashboard and I want to generate an excel workbook that has some dataframes on half of the sheets, and .png files for the other half. I'm having difficulty writing them to the same file in one go. Here's what I currently have. It seems that when I run my for loop, it won't let me add additional worksheets. Any advice on how I might get my image files added to this workbook? I can't find anything about why I can't add any more worksheets Thanks!

我正在创建一个 excel 仪表板，我想生成一个 excel 工作簿，其中一半的工作表有一些数据框，另一半有 .png 文件。我很难一次性将它们写入同一个文件。这是我目前拥有的。似乎当我运行 for 循环时，它不会让我添加额外的工作表。关于如何将我的图像文件添加到此工作簿的任何建议？我找不到任何关于为什么我不能添加更多工作表的信息 谢谢！

dfs = dict()
dfs['AvgVisitsData'] = avgvisits
dfs['F2FCountsData'] = f2fcounts

writer = pd.ExcelWriter("MyData.xlsx", engine='xlsxwriter')
for name, df in dfs.items():
    df.to_excel(writer, sheet_name=name, index = False)

Then I want to add a couple sheets with some images to the same excel workbook. Something like this, but where I'm not creating a whole new workbook.

然后我想将几张带有一些图像的工作表添加到同一个 Excel 工作簿中。像这样的东西，但我没有创建一个全新的工作簿。

workbook = xlsxwriter.Workbook('MyData.xlsx')
worksheet = workbook.add_worksheet('image1')
worksheet.insert_image('A1', 'MemberCollateral.png')

Anyone have any tips to work around this?

任何人都有解决此问题的任何提示？

回答by jmcnamara

Here is an example of how to get a handle to the underlying XlsxWriter workbook and worksheet objects and insert an image:

以下是如何获取底层 XlsxWriter 工作簿和工作表对象的句柄并插入图像的示例：

import pandas as pd


# Create a Pandas dataframe from some data.
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('pandas_image.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Insert an image.
worksheet.insert_image('D3', 'logo.png')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output:

输出：

See also Working with Python Pandas and XlsxWriterin the XlsxWriter docs for more examples

有关更多示例，另请参阅XlsxWriter 文档中的使用 Python Pandas 和 XlsxWriter

回答by Angus Gray

Here's the solution I came up with. I still cound't find a way to do this without re-importing the workbook with load_workbookbut this got the job done.

这是我想出的解决方案。我仍然无法找到一种方法来做到这一点，而无需重新导入工作簿，load_workbook但这完成了工作。

# assign dataframes to dictionary and export them to excel
avgvisits = pd.DataFrame(pd.read_sql(avgvisits(), cnxn))
f2fcounts = pd.DataFrame(pd.read_sql(f2fcounts(), cnxn))
activityencounters = pd.DataFrame(pd.read_sql(ActivityEncounters(), cnxn))
activityencountersp = activityencounters.pivot_table(values='ActivityCount', index = ['Activity'], columns= ['QuarterYear'], aggfunc=np.max)

dfs = dict()
dfs['AvgVisitsData'] = avgvisits
dfs['F2FIndirect'] = f2fcounts
dfs['ActivityEncounters'] = activityencountersp

writer = pd.ExcelWriter("MyData.xlsx", engine='xlsxwriter')
for name, df in dfs.items():
    if name != 'ActivityEncounters':
        df.to_excel(writer, sheet_name=name, index=False)
    else:
        df.to_excel(writer, sheet_name=name, index=True)

writer.save()
writer.close()

# re-import the excel book and add the graph image files
wb = load_workbook('MyData.xlsx')
png_loc = 'MemberCollateral.png'
wb.create_sheet('MemberCollateralGraph')
ws = wb['MemberCollateralGraph']
my_png = openpyxl.drawing.image.Image(png_loc)
ws.add_image(my_png, 'A1')

png_loc = 'DirectIndirect.png'
ws = wb['F2FIndirect']
my_png = openpyxl.drawing.image.Image(png_loc)
ws.add_image(my_png, 'A10')

png_loc = 'QuarterlyActivitySummary.png'
ws = wb['ActivityEncounters']
my_png = openpyxl.drawing.image.Image(png_loc)
ws.add_image(my_png, 'A10')
wb.save('MyData.xlsx')