Using xlsxwriter (or other packages) to create Excel tabs with specific naming, and write dataframe to the corresponding tab

Question

I am trying to query based on different criteria, and then create individual tabs in Excel to store the query results.

For example, I want to query all the results that match criteria A, and write the result to an Excel tab named "A". The query result is stored in the panda data frame format.

My problem is, when I want to perform 4 different queries based on criteria "A", "B", "C", "D", the final Excel file only contains one tab, which corresponds to the last criteria in the list. It seems that all the previous tabs are over-written.

Here is sample code where I replace the SQL query part with a pre-set dataframe and the tab name is set to 0, 1, 2, 3 ... instead of the default Sheet1, Sheet2... in Excel.

import pandas as pd
import xlsxwriter
import datetime



def GCF_Refresh(fileCreatePath, inputName):
    currentDT = str(datetime.datetime.now())
    currentDT = currentDT[0:10]
    loadExcelName = currentDT + '_' + inputName + '_Load_File'
    fileCreatePath = fileCreatePath +'\\' + loadExcelName+'.xlsx'
    wb = xlsxwriter.Workbook(fileCreatePath)
    data = [['tom'], ['nick'], ['juli']]

    # Create the pandas DataFrame
    df = pd.DataFrame(data, columns=['Name'])
    writer = pd.ExcelWriter(fileCreatePath, engine='xlsxwriter')

    for iCount in range(5):
        #worksheet = writer.sheets[str(iCount)]
        #worksheet.write(0, 0, 'Name')
        df['Name'].to_excel(fileCreatePath, sheet_name=str(iCount), startcol=0, startrow=1, header=None, index=False)
        writer.save()
        writer.close()

# Change the file path here to store on your local computer
GCF_Refresh("H:\\", "Bulk_Load")

My goal for this sample code is to have 5 tabs named, 0, 1, 2, 3, 4 and each tab has 'tom', 'nick' and 'juli' printed to it. Right now, I just have one tab (named 4), which is the last tab among all the tabs I expected.

Answer 1

There are a number of errors in the code:

• The xlsx file is created using XlsxWriter directly and then overwritten by creating it Again in Pandas.
• The to_excel() method takes a reference to the writer object not the file path.
• The save() and close() are the same thing and shouldn't be in the loop.

Here is a simplified version of your code with these issues fixes:

import pandas as pd
import xlsxwriter

fileCreatePath = 'test.xlsx'
data = [['tom'], ['nick'], ['juli']]

# Create the pandas DataFrame
df = pd.DataFrame(data, columns=['Name'])
writer = pd.ExcelWriter(fileCreatePath, engine='xlsxwriter')

for iCount in range(5):
    df['Name'].to_excel(writer, 
                        sheet_name=str(iCount), 
                        startcol=0, 
                        startrow=1, 
                        header=None, 
                        index=False)

writer.save()

Output:

See Working with Python Pandas and XlsxWriter in the XlsxWriter docs for some details about getting Pandas and XlsxWriter working together.