在openpyxl中使用新单元格的列格式 [英] Use style of column for new cells in openpyxl
问题描述
.xlsx
文件。作为一个最小的例子,创建一个空的 sample.xlsx
文件,并通过点击列 A
将其编号样式设置为 Percent
(与默认常规
相反)。 现在,执行以下代码
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'单元格格式为%s'%ws ['A1']。number_format
wb.save(new)
打印输出是单元格格式为General
。而 sample_new.xlsx
在 Excel
中打开 A1
单元格确实是 3.1415926
,单元格样式为常规
。有趣的是,如果在 LibreOffice Calc
中打开,则会根据需要显示单元格 314.16%
,单元格样式为百分比
。
但是,如果原始 sample.xlsx
直接设置单元格 A1
的属性,而不是整个 A
列的格式,格式可以按预期方式工作 Excel
和 LibreOffice
,而代码打印单元格格式为Percent
。
在实践中,我需要附加一个现有的 .xlsx
文件,保留原始的列格式(可能不同文件)。如何实现?
我使用2.4.0版本的openpyxl。
您必须手动设置所有单元格样式。规格中的列和行样式并不符合预期。他们承诺在创建新单元格时应该怎么做。有各种原因,但速度是主要的,为什么我们不在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.
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.
这篇关于在openpyxl中使用新单元格的列格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!