CODEWITHSUNDEEP
pythonpandasxlsxwriter
John
John

Reputation: 551

Write strings/text and pandas dataframe to excel

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?

Upvotes: 17

Views: 38787

Answers (4)

Howard Wu
Howard Wu

Reputation: 31

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")

Upvotes: 0

Jens Wagemaker
Jens Wagemaker

Reputation: 374

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.

Upvotes: 0

jezrael
jezrael

Reputation: 862731

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)

Upvotes: 27

SonOfLight
SonOfLight

Reputation: 45

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

Upvotes: 3

Related Questions