Add Image to Excel file after pandas.to_excel()

Question

I am using xlsxwriter as the engine that I pass to pandas.ExcelWriter. I have a DataFrame and I call to_excel() on it passing the writer that I've previously acquired. I then try grabbing the worksheet using get_worksheet_by_name(), which seems to work well, and then calling worksheet.insert_image() to insert an image.

This isn't working. I have the supposition that to_excel() causes the worksheet to be written and thus not available for mutation later on. Is this correct? If so, would I have to have a manual process for reading every cell in the DataFrame and writing it to Excel myself using xlsxwriter so that I can do the image stuff too? Or is there a way to tell to_excel() not to finish writing the sheet?

Answer 1

This code example will insert a dataframe and the image

The worksheet assignment is just;
worksheet = writer.sheets['Sheet1']

import pandas as pd

data_frame = pd.DataFrame({'Fruits': ['Appple', 'Banana', 'Mango',
                           'Dragon Fruit', 'Musk melon', 'grapes'],
                           'Sales in kg': [20, 30, 15, 10, 50, 40]})

with pd.ExcelWriter("foo.xlsx") as writer:
    data_frame.to_excel(writer,
                        sheet_name="Sheet1",
                        index=False)

    writer.sheets['Sheet1'].insert_image("D2", "pic.png")

However if you were to use

workbook = writer.book

worksheet = workbook.get_worksheet_by_name('Sheet1')
worksheet.insert_image("D2", "pic.png")

instead of writer.sheets, that would work as well.