Use style of column for new cells in openpyxl

问题:

Assume that that there is an existing .xlsx file with specified column styles. As a minimal example create an empty sample.xlsx file and by clicking at column A set its number style to Percent (in contrast to default General).

Now, execute the following code

import openpyxl as pyxl    
import math

sample = 'sample.xlsx'
new = 'sample_new.xlsx'

wb = pyxl.load_workbook(sample)
ws = wb.active

ws['A1'] = math.pi
print 'Cell format is %s' % ws['A1'].number_format

wb.save(new)

The output of the print is Cell format is General. And when sample_new.xlsx is opened in Excel the content of A1 cell is indeed 3.1415926 with the cell style being General. Interestingly, if opened in LibreOffice Calc the cell is displayed as desired 314.16% and cell style reads Percent.

If, however, in the original sample.xlsx file one directly sets the property of the cell A1 and not of the whole A-column, the formatting works as expected both in Excel and LibreOffice, while the code prints Cell format is Percent.

In practice I need to append to an existing .xlsx file preserving the original column formatting (which may differ from file to file). How do I achieve that?

I use 2.4.0 version of openpyxl.

回答1:

You'll have to set all cell styles manually. Column and row styles in the specification don't really match up to expectations. They're promises for applications about what to do when creating new cells. There are various reasons, but speed is the main one, why we don't do this in openpyxl.