17

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

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?

Improve this question

4 Answers 4

Reset to default
27

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)

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

how does Sheet1 get added?
@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']
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'
Im getting 'Worksheet' object has no attribute 'write' same error for write-string too
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
|
3

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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")
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.