Conditional Formatting Requires Repair in XLSX

[will.marriott]

Hello,

We are generating a Workbook in memory and then applying conditional formatting (among other things).

Code: Select all

// Conditional Formatting
CellRange range = worksheet.Range[headerRows + 1, 1, worksheet.LastRow, worksheet.LastColumn];

// Negative Values
var negCondFormat = range.ConditionalFormats.AddCondition();
negCondFormat.FormatType = ConditionalFormatType.CellValue;
negCondFormat.FirstFormula = "0";
negCondFormat.Operator = ComparisonOperatorType.Less;
negCondFormat.FontColor = System.Drawing.Color.Red;

// Zero Values
var zeroCondFormat = range.ConditionalFormats.AddCondition();
zeroCondFormat.FormatType = ConditionalFormatType.CellValue;
zeroCondFormat.FirstFormula = "0";
zeroCondFormat.Operator = ComparisonOperatorType.Equal;
zeroCondFormat.BackColor = System.Drawing.ColorTranslator.FromHtml("#ccffcc");

workbook.CalculateAllValue();

This used to work with previous versions of Spire.XLS, but v7.5.20 produces XLSX files that when opened, Excel pops up the following message:

We found a problem with some content in 'test.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

The repair log is:

Code: Select all

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
   <logFileName>error146320_01.xml</logFileName>
   <summary>Errors were detected in file 'C:\Users\Will\Downloads\test.xlsx'</summary>
   <repairedRecords summary="Following is a list of repairs:">
      <repairedRecord>Repaired Records: Conditional formatting from /xl/worksheets/sheet1.xml part</repairedRecord>
   </repairedRecords>
</recoveryLog>

XLS files seem to have the conditional formatting without any issues.

[burning.liu]

Dear will.marriott,

Thanks for your inquiry.

Please try Spire.XLS Pack Hotfix Version:7.6.1 to resolve the problem, you can download it via link below:
http://www.e-iceblue.com/downloads/hot_ ... _7.6.1.zip

Best Regards,
Burning
E-iceblue Support Team

[will.marriott]

Hi burning.liu,

I am still experiencing the same issues with the Hotfix.

[burning.liu]

Dear will.marriott,

I made a sample for your requirement, and the result document goes well. You can refer to the code snippet below:

Code: Select all

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Formula = "=ABS(-1.21)";
sheet.Range["A2"].Formula = "=RAND()";
sheet.Range["A3"].Formula = "=33*3/4-30+10";
sheet.Range["A4"].Formula = "=0";
sheet.Range["A5"].Formula = "=ROUNDDOWN(-3.14159, 1)";
sheet.Range["A6"].Formula = "=TRUNC(0.45)";
sheet.Range["A7"].Formula = "=ODD(-1)";

CellRange range = sheet.Range["A1:A7"];
//Less
var negCondFormat = range.ConditionalFormats.AddCondition();
negCondFormat.FormatType = ConditionalFormatType.CellValue;
negCondFormat.FirstFormula = "0";
negCondFormat.Operator = ComparisonOperatorType.Less;
negCondFormat.FontColor = Color.Red;
//Equal
var zeroCondFormat = range.ConditionalFormats.AddCondition();
zeroCondFormat.FormatType = ConditionalFormatType.CellValue;
zeroCondFormat.FirstFormula = "0";
zeroCondFormat.Operator = ComparisonOperatorType.Equal;
zeroCondFormat.BackColor = ColorTranslator.FromHtml("#ccffcc");
//Greater
var posCondFormat=range.ConditionalFormats.AddCondition();
posCondFormat.FormatType=ConditionalFormatType.CellValue;
posCondFormat.FirstFormula="0";
posCondFormat.Operator=ComparisonOperatorType.Greater;
posCondFormat.FontColor = Color.Blue;

using(MemoryStream ms=new MemoryStream())
{
    workbook.SaveToStream(ms);
    File.WriteAllBytes("result.xls", ms.ToArray());
}


Best Regards,
Burning
E-iceblue Support Team

[will.marriott]

Hi Burning,

Thank you for your responses.

After some trial and error, turns out the error was caused by the line:

Code: Select all

workbook.CalculateAllValue();

It has been removed and now all the exports seem to be working fine.

[burning.liu]

Dear will.marriott,

Please feel free to contact us if you have any problems.

Best Regards,
Burning
E-iceblue Support Team