xlsxwriter formatting is corrupting my excel file

0 votes

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()
Dec 28, 2022 in Others by Kithuzzz
• 38,000 points
2,451 views

1 answer to this question.

0 votes

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:

enter image description here

answered Dec 28, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Excel cannot open the file because the file format or file extension is not valid - PHP Maatwebsite

Try this: if (ob_get_length() == 0 ) { ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,600 points
1,463 views
0 votes
1 answer

Excel file download using java - Error : File is corrupted

Try this: @GetMapping("/downloadDOA") public ...READ MORE

answered Dec 28, 2022 in Others by narikkadan
• 63,600 points
2,365 views
0 votes
1 answer

Why is my code grabbing row number from the wrong Excel sheet?

I changed this: iTotalRows = source.Worksheets("Export").Range("B1:B" & Cells(Rows.Count, ...READ MORE

answered Jan 10, 2023 in Others by narikkadan
• 63,600 points
454 views
0 votes
1 answer

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: (Open Excel File) in Python

Try this: import os import shutil dirpath = os.path.join('C:/Path/Folder', 'Folder') if ...READ MORE

answered Jan 15, 2023 in Others by narikkadan
• 63,600 points
7,867 views
0 votes
1 answer

What are the RGB and HEX codes of the 3 color conditional format in Excel?

In Excel 2016 at least the colors ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,600 points
8,707 views
0 votes
1 answer

Openpyxl password protect excel file python

Workbooks can be secured against certain actions, ...READ MORE

answered Sep 24, 2022 in Others by narikkadan
• 63,600 points
8,284 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,600 points
3,555 views
0 votes
1 answer

How to freeze the top row and the first column using XlsxWriter?

You can use worksheet.freeze_panes() to achieve this . There ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,600 points
2,339 views
0 votes
1 answer
0 votes
1 answer

Is there a reason why these bars wont line up in my excel chart?

STEPS Hover your mouse over any of the ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,600 points
584 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP