Write strings/text and pandas dataframe to excel

Question

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?

Echopraxia

Answer 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", engine="xlsxwriter")
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()

Note: write and write_string are actually xlsxwriter package functions. To use them, the package must be installed and pd.ExcelWriter must be initialized with the xlsxwriter engine (in pandas 1.0.5 it defaults to the io.excel.<extension>.writer engine)

Eoin

how does Sheet1 get added? – Ptolemy

@MikePalmice - It is default value, but if need custom use df.to_excel(writer, startrow=4, startcol=0, sheet_name ='aaa') worksheet = writer.sheets['aaa'] – Eoin

right but worksheet = writer.sheets['aaa'] doesnt create a new sheet if it doesnt exist. also i'm getting 'Worksheet' object has no attribute 'write' or 'write_string' – Ptolemy

@MikePalmice - Yes, worksheet = writer.sheets['aaa'] only select sheetname called aaa. I think if get this error, is necessary upgrade pandas. – Eoin

pandas: 0.23.4, python: 3.7.0 – Ptolemy

i tried with your sample code and got the same error. can OP or anyone else try running, and if it works, share their system info plz? – Ptolemy

Im getting 'Worksheet' object has no attribute 'write' same error for write-string too – Variegation

To use the write_string or write methods, pd.ExcelWriter must me initialized with engine='xlsxwriter' and the xlsxwriter package needs to be installed. Credit @SonOfLight – Quotidian

Answer 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

Alysaalyse

Answer 3

You can also use the openpyxl package:

import pandas as pd
df = <pandas dataframe>
with pd.ExcelWriter(filename) as writer:
    text = "Text message"
    text_sheet = writer.book.create_sheet(title='text_sheet')
    text_sheet.cell(column=1, row=1, value=text)
    df.to_excel(writer, sheet_name = 'df_sheet', index = False)

This won't put it on the same sheet though.

Breadroot

Answer 4

Adding to jezrael's answer. If you're using the xlsxwriter engine

You can create the worksheet like so before the "worksheet.write()"

worksheet = writer.book.add_worksheet("Sheet1")

Toler