CODEWITHSUNDEEP
pythonexcelpython-2.7openpyxl
Sam
Sam

Reputation: 4090

Preserve original cell formatting in openpyxl

I am using openpyxl version 2.3.5 to write data into an existing, formatted, excel template (with .xlsx extension). The problem is that when I write to a cell, the formatting of that cell is completely overwritten. For example, Cell A1 has a blue fill. When I execute the following code:

wb = xl.load_workbook('Template.xlsx')
ws = wb.worksheets[0]
ws['A1'] = "TEST"
wb.save('test.xlsx')

The fill of cell A1 is lost. There is a lot of formatting in the spreadsheet, so I do not want to manually specify all of it. I have tried copying the original formatting of the cell from itself to no avail. This code did not work:

ws['A1'].style = ws['A1'].style

Is there any way to keep and/or copy the original style/formatting of the excel spreadsheet, and only write in the data?

Upvotes: 11

Views: 8726

Answers (2)

marcoccw
marcoccw

Reputation: 11

I use "template" approach with openpyxl version 3.0.7

Normally I create a dummy template with style and format, and fill fake data in first row, openpyxl will persist style and format if cell with value

In code, read first row and copy format, let's say is in dictionary (copy style reference: copy cell style openpyxl) Then remove the first row and iterate your own data. Finally apply the style and format back to the new filled cell.

I think this is only way to do.

Upvotes: 1

syncofusion
syncofusion

Reputation: 11

I found that the formatting will be preserved in case the original cell did have a value previously. I am using "template" excels to fill out data and produce reports. Unfortunately with this method I need to have my template pre-populated with dummy values (creates huge size template files), also, need to make sure that dummy values are removed from the sheet from areas where I do not fill data into. its a pain, but at least the formatting remains in there

Upvotes: 1

Related Questions