Menu
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?
626
In openpyxl, each Cell object has a bunch of attributes we can use for formatting: Collectively, these attributes are called styles but I still prefer calling them just formats. One thing to note is that we can apply styles to only one cell at a time. So we’ll need to do a loop in order to format a range of cells.
OpenPyXL gives you the ability to style the borders on your cell. You can specify a different border style for each of the four sides of a cell. You can use any of the following border styles: Open your Python editor and create a new file named border.py. Then enter the following code in your file: This code will add a border to cell A1 and A3.
To see how you can use fonts in OpenPyXL, create a new file named font_sizes.py and add the following code to it: This code uses three different fonts in three different cells. In A1, you use the default, which is Calibri. Then in A2, you set the font size to Arial and increase the size to 14 points.
Simply sad, openpyxl reads some xml files, then it parses that data, than you can use funtions in openpyxl library to change and add data, and finally when you save your workbook, openpyxl will create xml files, write data to them, and save them as zip archive (which is excel file).
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
176
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
