export dataframe to new excel worksheet and also write specific values to specific sheet

Question

I'm running into an issue that I think relates to needing to:

export a dataframe to a new Excel worksheet (created at time of export)
write specific values to an existing sheet in same workbook
Doing both of the above in a loop

I can get 1 and 3 to work by themselves, and I can get 2 and 3 to work by themselves, but when I try to do all three things it doesn't work. I think there is some issue with the pandas to_excel using xlsxwriter engine conflicting with the sheets.write(row,column, value) to the same workbook.

For instance, this works by itself (note that I have the "writer" stuff to export dataframe to new sheet commented out):

import pandas as pd
import xlsxwriter

loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
counter = 1
  
# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])
 
workbook = xlsxwriter.Workbook('C:\\Test\\Test.xlsx')
totalsSheet = workbook.add_worksheet('Totals')
writer = pd.ExcelWriter('C:\\Test\\Test.xlsx', engine = 'xlsxwriter')

for sheets in loopList:
    
    #df.to_excel(writer, sheet_name = sheets, index=False)
    totalsSheet.write(counter, counter, sheets + str(counter)) 
    counter+=1
    
#writer.save()
#writer.close()
workbook.close()

The above makes the test.xlsx workbook, with a Totals worksheet, with "A1", "B2", etc. in incrementing row/column.

Likewise, when I comment out the workbook stuff and UN comment the pandas-export dataframe to new sheets, that also works:

import pandas as pd
import xlsxwriter

loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
counter = 1
  
# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])
 
workbook = xlsxwriter.Workbook('C:\\Test\\Test.xlsx')
totalsSheet = workbook.add_worksheet('Totals')
writer = pd.ExcelWriter('C:\\Test\\Test.xlsx', engine = 'xlsxwriter')

for sheets in loopList:
    
    df.to_excel(writer, sheet_name = sheets, index=False)
    #totalsSheet.write(counter, counter, sheets + str(counter)) 
    counter+=1
    
writer.save()
writer.close()
#workbook.close()

The above gives me a new Test workbook with 5 sheets (A, B, C, etc.) with the same dataframe exported to each.

However, I can't seem to do both; depending on the order in which I have the lines that write to Excel, it still only does one or the other (I don't get errors, I just get a result that's not both things I'm trying to do).

Is there a way to accomplish both of these things in the same loop?

I'm using python 3.x.x. Thanks for any help.

mullinscr mullinscr · Accepted Answer · 2021-02-07T20:53:22

Could you not just run it in to separate loops that each open and close the file to ensure that it is available to each processes? Something like...

import pandas as pd
import xlsxwriter

loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
counter = 1
  
# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])

with pd.ExcelWriter('C:\\Test\\Test.xlsx', engine = 'xlsxwriter') as writer: 
    for sheets in loopList: 
        df.to_excel(writer, sheet_name = sheets, index=False)

workbook = xlsxwriter.Workbook('C:\\Test\\Test.xlsx')
totalsSheet = workbook.add_worksheet('Totals')

for sheets in loopList: 
    totalsSheet.write(counter, counter, sheets + str(counter)) 
    counter+=1
    
workbook.close()

Update

Looking into it more, as the docs say xlsxwriter:

cannot read or modify existing Excel XLSX files.

So what you were trying before was causing the overwrite to happen. However, if you look into the docs more you will see that the key is to create the workbook object from the pd.ExcelWriter object. This will mean that both libraries can write to the file at the same time.

I installed xlsxwriter and the code below works for me:

import pandas as pd
import xlsxwriter

# data to write
loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 

# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])

# create the writer object
writer = pd.ExcelWriter('Test.xlsx', engine='xlsxwriter')

# create the workbook object from the current writer object
# this means that pandas and xlsxwriter can both write to it
workbook = writer.book 
totalsSheet = workbook.add_worksheet('Totals')

# set the counter
counter = 1

# lopo through and use xlsx writer to write specific cells
for sheets in loopList: 
    totalsSheet.write(counter, counter, sheets + str(counter)) 
    counter+=1
    
# loop through generating new sheets and writing dfs to the file
for sheets in loopList: 
    df.to_excel(writer, sheet_name = sheets, index=False)

# save the written data
writer.save()

export dataframe to new excel worksheet and also write specific values to specific sheet

1 Answers