pythonxlsxwriter

xlsxwriter .add_table() set columns works for one dataframe but not the other

I am trying to add a data table in Excel using python library xlsxwriter

My Dataframe named whole has 26 columns (exactly the letters in the alphabet) and so I am trying to set them using this code:

workbook = xlsxwriter.Workbook(f'workbook.xlsx', {"nan_inf_to_errors": True})
worksheet = workbook.add_worksheet("Column Setter")

worksheet.add_table(f"A1:{chr(ord('@')+len(whole.columns))}{len(whole)}", {'data': 
whole.values.tolist(),
                'banded_columns': True, 'banded_rows': False, 'header_row': True, 
                 'columns': [{'header': col} for col in whole.columns.tolist()]})
worksheet.autofit()

However the output seems to set all the column names to Column1 etc.

enter image description here

This code seems to work perfectly for another dataframe with 3 columns so I'm struggling to understand why this doesn't work. Any answers would be very helpful.

EDIT

I have changed the code to match jmcnamara cpde. It looks as such:

workbook = xlsxwriter.Workbook(f'workbook.xlsx', {"nan_inf_to_errors": True})
worksheet = workbook.add_worksheet("Column Setter")

(max_row, max_col) = whole.shape

worksheet.add_table(0, 0, max_row, max_col - 1,
 {
    'data': whole.values,
    'banded_columns': True,
    'banded_rows': False,
    'header_row': True, 
 }
)

worksheet.autofit()

However this doesn't seem to write the data in at all and just appears with 7 of 26 columns populated. it also doesn't seem to have created a table at all. Please see below.

enter image description here

Solution

  • It should work as expected. Here is an example based on yours with a sample dataframe:

    import pandas as pd
import xlsxwriter

# Create a Pandas dataframe from some sample data.
data = {}
for num in range(1, 27):
    data[f"Data {num}"] = [1, 2, 3, 4, 5]

whole = pd.DataFrame(data)

# Create a workbook and worksheet.
workbook = xlsxwriter.Workbook(f"workbook.xlsx", {"nan_inf_to_errors": True})
worksheet = workbook.add_worksheet("Column Setter")

# Get the dimensions of the dataframe.
(max_row, max_col) = whole.shape

# Add the Excel table structure.
worksheet.add_table(0, 0, max_row, max_col - 1,
    {
        "data": whole.values,
        "banded_columns": True,
        "banded_rows": False,
        "header_row": True,
        "columns": [{"header": col} for col in whole.columns],
    },
)

worksheet.autofit()

workbook.close()

    And the output looks like Expected:

    enter image description here

    I don't know if it is a factor in your output but it is better to use the (row, col) syntax for the table dimensions rather than trying to construct an A1 style string. Also, the tolist() method calls in your example aren't required.