0

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.

Weather Report
  • 261
  • 1
  • 2
  • 13

1 Answers1

0

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.

Charlie Clark
  • 18,477
  • 4
  • 49
  • 55
  • Ok, but can I do it dynamically? I do not know the format of the columns beforehand, but it is specified in a given `.xlsx` file. Can I get the column format within openpyxl? – Weather Report Nov 15 '16 at 08:28
  • Also, is there any idea why `LibreOffice` exhibits different (and desirable) formatting? – Weather Report Nov 15 '16 at 09:11
  • Yes, you can look up the relevant `ColumnDimension` in the workbook. LO and MS Excel are applications where the interaction with the use is important, openpyxl is a file format library. We might revisit the functionality at some point in the future but at the moment it's really an anti-pattern that could slow things down and I'd rather we were as fast as possible. – Charlie Clark Nov 15 '16 at 12:49
  • I'm almost there! Could you please explain how to get the `Percent` format using `ColumnDimension`? I've tried (building on the OP code) `print pyxl.worksheet.dimensions.ColumnDimension(ws['A']).style` with no luck. – Weather Report Nov 15 '16 at 15:23
  • Should be available as `number_format` I think. Best check the source code. – Charlie Clark Nov 15 '16 at 16:20
  • Unfortunately neither can I find in docs nor understand from the source how to access the `ColumnDimension` of a particular column. `ColumnDimension(ws['A'])` seems to be the same as `ColumnDimension(' ')` so this syntax is clearly wrong. – Weather Report Nov 15 '16 at 16:37