Write strings/text and pandas dataframe to excel

问题:

I'd like to save some text and a dataframe to an excel file like that:

Thus, I've got the following variables:

text1 = "some text here"
text2 = "other text here"
df = pd.DataFrame({"a": [1,2,3,4,5], "b": [6,7,8,9,10], "c": [11,12,13,14,15]})

As I've figured out there is the possibility to use the xlsxwriter to do this which means that I basically have to iterate over the whole dataframe to write each entry to a different cell in the excel workbook. This is quite cumbersome.

So, I thought there must an easier way to do this; something like this:

writer = pd.ExcelWriter("test.xlsx", engine="xlsxwriter")
writer.write(text1, startrow=0, startcol=0)
writer.write(text1, startrow=1, startcol=0)
df.to_excel(writer, startrow=4, startcol=0)

Is there an easier way?

回答1:

You need write or write_string:

text1 = "some text here"
text2 = "other text here"
df = pd.DataFrame({"a": [1,2,3,4,5], "b": [6,7,8,9,10], "c": [11,12,13,14,15]})

writer = pd.ExcelWriter("test.xlsx")
df.to_excel(writer, startrow=4, startcol=0)

worksheet = writer.sheets['Sheet1']
worksheet.write(0, 0, text1)
worksheet.write(1, 0, text2)
#another solution
#worksheet.write_string(0, 0, text1)
#worksheet.write_string(1, 0, text2)

writer.save()

回答2:

Above solution is correct... However

The write function is part of the xlsxwriter library. When declaring the writer you need to indicate what engine you want pandas to use.

writer = pd.ExcelWriter("test.xlsx", engine="xlsxwriter")

xlsxwriters functions are then usable through pandas. All other code in the above solution stays the same.

Ofcourse you require the library to be installed. Here is a programmatic check.

Would comment but rep to low