1

I would like to create an excel file where one of the sheets is filled with calculated values and titles that are associated with them that would look like this :

Excel_example

In my case I have already inserted 2 previous worksheets that are files with a df using pandas.

My code goes as such :

writer = pd.ExcelWriter(Excel_file.xlsx", engine='xlsxwriter')
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet2')
txt1 = str('TITLE 1')
txt2 = str('TITLE 2')
...
Value1 = 30
Value2 = 45
Value3 = 50

This works without a problem

I then tried adding the new sheet with the wanted information using 2 different ways

1 :

txt1.to_excel(writer, sheet_name='sheet3', startrow=0, startcol=0)
txt2.to_excel(writer, sheet_name='sheet3', startrow=1, startcol=0)
...
Value1.to_excel(writer, sheet_name='sheet3', startrow=1, startcol=1)
...
writer.save()

But this gave me this error message - AttributeError: 'str' object has no attribute 'to_excel'

2 :

worksheet = writer.sheets['sheet3']
worksheet.write(0, 0, txt1)
worksheet.write(1, 0, txt1)
...
worksheet.write(1, 1, Value1)
...
writer.save()

But this gave me this error message - KeyError: 'sheet3'

DjaouadNM
  • 22,013
  • 4
  • 33
  • 55
Mikel Alba
  • 25
  • 5
  • Try using [openpyxl](https://stackoverflow.com/questions/40385689/add-a-new-sheet-to-a-existing-workbook-in-python) as [it may not be possible with xlsxwriter-Obsidian](https://stackoverflow.com/questions/35338572/python-xlsxwriter-add-a-worksheet-to-an-existing-workbook). Try looking at [Python | Writing to an excel file using openpyxl module](https://www.geeksforgeeks.org/python-writing-excel-file-using-openpyxl-module/). – Alias Cartellano Jul 22 '22 at 16:16
  • I am actually trying to create an excel file from scratch. I have code that precedes the one that I have showed that creates the excel file and fills the first two sheets. I am now trying to add an analysis page that would be filed with text and calculations done with the date from the first two worksheets – Mikel Alba Jul 22 '22 at 17:21
  • The third link has the code to create a new workbook and you can use openpyxl to perform just such functions. – Alias Cartellano Jul 22 '22 at 17:25

1 Answers1

2

One way to do it would be to add the additional data to a third dataframe and then do df3.to_excel(writer, sheet_name='sheet3').

This is sort of what you can trying to do in example 1 above but you need to put the data into a dataframe first.

For example 2, the error message is due to the fact that there isn't any 'sheet3'. You will need to add that before you can write to it. Something like this:

worksheet = writer.book.add_worksheet('sheet3')  

worksheet.write(0, 0, txt1)
worksheet.write(1, 0, txt1)
...
jmcnamara
  • 38,196
  • 6
  • 90
  • 108