在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屋！