pythonexcelpandasxlsxwritertableheader

python xlsxwriter: Keep header in excel when adding a table


I have a panda dataframe that I write to a xslx file, and would like to add a table over that data. I would also like to keep the headers that I have already written, instead of adding them again. Is that possible?

Example:

import pandas as pd
import xlsxwriter as xw

# random dataframe
d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']), 'two' : pd.Series([5., 6., 7., 8.], index=['a', 'b', 'c', 'd'])}
df = pd.DataFrame(d)


# write data to file
writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
df.to_excel(writer,"sheet without table")
df.to_excel(writer,"sheet with table")
df.to_excel(writer,"sheet with table and header")

# get sheets to add the tables
workbook  = writer.book
worksheet_table = writer.sheets['sheet with table']
worksheet_table_header = writer.sheets['sheet with table and header']

# the range in which the table is
end_row = len(df.index)
end_column = len(df.columns)
cell_range = xw.utility.xl_range(0, 0, end_row, end_column)


# add the table that will delete the headers
worksheet_table.add_table(cell_range,{'header_row': True,'first_column': True})

######################################
# The hack

# Using the index in the Table
df.reset_index(inplace=True)
header = [{'header': di} for di in df.columns.tolist()]
worksheet_table_header.add_table(cell_range,{'header_row': True,'first_column': True,'columns':header})

writer.save()

Solution

  • The hack / work around is the only option (as seen from @jmcnamara). In short it is:

    import pandas as pd
    import xlsxwriter as xw
    
    # random dataframe
    d = {'one' : pd.Series([1., 2., 3.], index=['a', 'b', 'c']), 'two' : pd.Series([5., 6., 7., 8.], index=['a', 'b', 'c', 'd'])}
    df = pd.DataFrame(d)
    
    
    # write data to file
    writer = pd.ExcelWriter("test.xlsx", engine='xlsxwriter')
    df.to_excel(writer,"sheet with table and header")
    
    # get sheets to add the tables
    workbook  = writer.book
    worksheet_table_header = writer.sheets['sheet with table and header']
    
    # the range in which the table is
    end_row = len(df.index)
    end_column = len(df.columns)
    cell_range = xw.utility.xl_range(0, 0, end_row, end_column)
    
    ######################################
    # The hack
    
    # Using the index in the Table
    df.reset_index(inplace=True)
    header = [{'header': di} for di in df.columns.tolist()]
    worksheet_table_header.add_table(cell_range,{'header_row': True,'first_column': True,'columns':header})
    
    writer.save()