xlsxwriter formatting is corrupting my excel file

Question

I'm attempting to format an xlsx file using xlsxwriter, but Excel claims that my exported file is damaged.

This line, format1 = workbook. add format('font color':'red'), is where I think the problem lies. yet I cannot figure out why.

I've tried opening the file on a different computer and updated MS Excel to the most recent version, but I keep getting the following error message: We identified an issue with some content in "export top200format.xlsx." Do you wish for us to make every effort to recover? Click Yes if you have faith in the author of this workbook.

Excel was able to open the file by repairing or removing the unreadable content.

import pandas as pd
import os
import xlsxwriter

# IMPORTING dfCAtop200_CLEANED
ca_top200_CLEANED = 'export_top200CLEANEDTEST.xlsx'

# READING 'export_top200CLEANEDTEST.xlsx' FILE
dfCAtop200_Formatted = pd.read_excel(
    ca_top200_CLEANED)

column_list = dfCAtop200_Formatted.columns

writer = pd.ExcelWriter(
    '/Users/BTS/Documents/python_work/ABC Charts/export_top200FORMATTED.xlsx', engine='xlsxwriter')

dfCAtop200_Formatted.to_excel(writer, index=False)

# to remove source header formatting

for idx, val in enumerate(column_list):
    worksheet.write(0, idx, val)

# workbook stuff

workbook = xlsxwriter.Workbook('export_top200FORMATTED.xlsx')
worksheet = writer.sheets['Sheet1']

format1 = workbook.add_format({'font_color': 'red'})

worksheet.set_column('B:B', 11, format1)


writer.save()

Answer 1

The problem is that while attempting to use the worksheet prepared by Pandas to add formatting, the application is replacing the xlsx file created by Pandas with a new one made by XlsxWriter. Here's the problem:

workbook = xlsxwriter.Workbook('export_top200FORMATTED.xlsx')
worksheet = writer.sheets['Sheet1']

The correct way to access a Pandas created workbook or worksheet is shown in the XlsxWriter documentation on Working with Python Pandas and XlsxWriter.

Here is a working example based on your code. It also fixes a issue in the code above where the first row of the dataframe data is overwritten:

import pandas as pd

# Create a Pandas dataframe from some data.
data = [10, 20, 30, 40, 50, 60]
df = pd.DataFrame({'Foo': data,
                   'Bar' : data,
                   'Baz' : data})

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter("formatting.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object. Note that we turn off
# the default header and skip one row to allow us to insert a user defined
# header.
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

# Rewrite the column headers without formatting.
for col_num, value in enumerate(df.columns.values):
    worksheet.write(0, col_num + 1, value)

# Add a format to column B.
format1 = workbook.add_format({'font_color': 'red'})

worksheet.set_column('B:B', 11, format1)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Output: