16

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?

Chuck
  • 3,664
  • 7
  • 42
  • 76
John
  • 521
  • 2
  • 5
  • 12

3 Answers3

26

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)

Nick Grosenbacher
  • 3
  • 1
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    how does Sheet1 get added? – 3pitt Oct 10 '18 at 13:38
  • @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']` – jezrael Oct 10 '18 at 13:41
  • 1
    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' – 3pitt Oct 10 '18 at 13:49
  • @MikePalmice - Yes, `worksheet = writer.sheets['aaa']` only select sheetname called `aaa`. I think if get this error, is necessary upgrade pandas. – jezrael Oct 10 '18 at 13:51
  • pandas: 0.23.4, python: 3.7.0 – 3pitt Oct 10 '18 at 15:36
  • 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? – 3pitt Oct 10 '18 at 16:19
  • 1
    Im getting 'Worksheet' object has no attribute 'write' same error for write-string too – Ratha Dec 18 '19 at 01:05
  • 1
    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](https://stackoverflow.com/a/54072373/11715606) – johnDanger Jul 08 '20 at 23:46
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

SonOfLight
  • 45
  • 7
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.

Jens Wagemaker
  • 354
  • 3
  • 13