reading worksheet and preserving conditional formatting

Question

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.

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')

Answer 1

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)

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>

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.

EDIT

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.