阅读工作表并保留条件格式 [英] reading worksheet and preserving conditional formatting

问题描述

我正在尝试使用openpyxl阅读excel工作表.我认为这样阅读时会丢失工作表中的条件格式信息:

I am trying to read an excel worksheet with openpyxl. I think I am losing the conditional formatting information in the sheet when I read it like so:

xl = openpyxl.load_workbook(filename)

当我读取文件中的所有单元格并将其保存时.我得到一个电子表格，其中没有条件格式的实现.

When I read all cells in the file and save it. I get a spreadsheet in which none of the conditional formatting is implement.

我可以在以下位置找到许多将条件格式添加到电子表格的方法: http://openpyxl.readthedocs.org/en/latest/formatting.html

I can find many ways of adding conditional formatting to a spreadsheet at http://openpyxl.readthedocs.org/en/latest/formatting.html

但是我找不到在现有工作表中读取条件格式信息的方法.

But I cannot find a way to read conditional formatting information in an existing worksheet.

我用于执行读写操作的特定代码是

The specific code I am using for doing the read and write is,

import openpyxl as xl

xlf = xl.load_workbook(r'd:\test\book1.xlsx')
sh = xlf.get_sheet_by_name('Sheet1')
allcells = sh.get_cell_collection()

wb = xl.Workbook()
ws = wb.create_sheet()

for c in allcells:
    row = c.row
    col = xl.cell.column_index_from_string(c.column)
    new_cell = ws.cell(row=row, column=col)
    new_cell.value = c.value
    new_cell.style = c.style.copy()

ws.title = 'test'
wb.save(r'd:\test\book1w.xlsx')

推荐答案

我真的很近，但是我无法保持颜色不变.仍然存在错误，但是如果不是填充选择，至少以下内容会添加保持条件格式设置规则:

I'm really close, but I can't get the color to stay. There is still an error, but at least the following does add keep conditional formatting rules if not the fill choice:

for range_string in sh.conditional_formatting.cf_rules:
    for cfRule in sh.conditional_formatting.cf_rules[range_string]:
        ws.conditional_formatting.add(range_string, cfRule)

使用这种一根衬管可以达到相同的效果(但最终结果相同):

The same is achieved with this one liner (but same end result):

ws.conditional_formatting.update(sh.conditional_formatting.cf_rules)

现在，如果您在excel中打开 Manage Rules ，则所有规则都已存在，但是当您打开文件时，它需要自动修复，并且颜色消失了.这是超级有用的日志(讽刺意在这里):

Now if you open up Manage Rules in excel the rules are all there, but when you open the file it requires automatic repairing and I lose the color. Here's the super helpful log (sarcasm intended here):

<repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet2.xml</repairedRecord></repairedRecords>

当我尝试直接复制 conditional_formatting 的三个属性时，我得到了类似的结果:

I got similar results when I tried to copy conditional_formatting's three attributes directly like so:

ws.conditional_formatting.cf_rules = sh.conditional_formatting.cf_rules.copy()
ws.conditional_formatting.max_priority = sh.conditional_formatting.max_priority
ws.conditional_formatting.parse_rules = sh.conditional_formatting.parse_rules.copy()

我一直在查看源代码以获取想法.

I've been looking at the source code for ideas.

编辑

有一种非常简单的选择.不要创建全新的工作簿和工作表并从头开始进行工作.只需根据需要修改原始文件，然后将其另存为其他名称即可.或者甚至可以先将其另存为其他名称来创建副本，然后再修改副本.这将保留所有格式设置规则.

There is one really easy alternative. Don't create a brand new workbook and worksheet and work on them from scratch. Just modify the original as needed and then save it as a different name. Or you could even start by saving it as a different name to create a copy, then modify the copy. This will preserve all formatting rules.

这篇关于阅读工作表并保留条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！