reading worksheet and preserving conditional formatting

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>

Suggestion : 2

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:,But I cannot find a way to read conditional formatting information in an existing worksheet.,I can find many ways of adding conditional formatting to a spreadsheet at http://openpyxl.readthedocs.org/en/latest/formatting.html,When I read all cells in the file and save it. I get a spreadsheet in which none of the conditional formatting is implement.

xl = openpyxl.load_workbook(filename)

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)

Suggestion : 3

Work with Excel in C# without Interop , Create Excel Files in .Net , Generate Excel Files in C# , Read an Excel File in C#

1._

using IronXL;
using IronXL.Formatting;
using IronXL.Formatting.Enums;
using IronXL.Styles;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Create a specific conditional formatting rule.
ConditionalFormattingRule rule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");
//Set different style options.
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;
//Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);

ConditionalFormattingRule rule1 = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);

workbook.SaveAs("ApplyConditionalFormatting.xlsx");

using IronXL;
using IronXL.Formatting;
using IronXL.Formatting.Enums;
using IronXL.Styles;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

//Create a specific conditional formatting rule.
ConditionalFormattingRule rule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8");
//Set different style options.
rule.FontFormatting.IsBold = true;
rule.FontFormatting.FontColor = "#123456";
rule.BorderFormatting.RightBorderColor = "#ffffff";
rule.BorderFormatting.RightBorderType = BorderType.Thick;
rule.PatternFormatting.BackgroundColor = "#54bdd9";
rule.PatternFormatting.FillPattern = FillPattern.Diamonds;
//Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule);

ConditionalFormattingRule rule1 = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10");
rule1.FontFormatting.IsItalic = true;
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single;
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1);

workbook.SaveAs("ApplyConditionalFormatting.xlsx");

Imports IronXL
Imports IronXL.Formatting
Imports IronXL.Formatting.Enums
Imports IronXL.Styles

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

'Create a specific conditional formatting rule.
Private rule As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")
'Set different style options.
rule.FontFormatting.IsBold = True
rule.FontFormatting.FontColor = "#123456"
rule.BorderFormatting.RightBorderColor = "#ffffff"
rule.BorderFormatting.RightBorderType = BorderType.Thick
rule.PatternFormatting.BackgroundColor = "#54bdd9"
rule.PatternFormatting.FillPattern = FillPattern.Diamonds 'Add formatting with the specified region.
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule)

Dim rule1 As ConditionalFormattingRule = sheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10")
rule1.FontFormatting.IsItalic = True
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single
sheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1)

workbook.SaveAs("ApplyConditionalFormatting.xlsx")

Install - Package IronXL.Excel

Install - Package IronXL.Excel