Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Fri Oct 31, 2014 4:21 am

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.

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Fri Oct 31, 2014 7:50 am

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
Best Regards,
Burning
E-iceblue Support Team
User avatar

burning.liu
 
Posts: 406
Joined: Mon Aug 04, 2014 6:47 am

Mon Nov 03, 2014 10:26 pm

Hi burning.liu,

I am still experiencing the same issues with the Hotfix.

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Tue Nov 04, 2014 3:00 am

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
Best Regards,
Burning
E-iceblue Support Team
User avatar

burning.liu
 
Posts: 406
Joined: Mon Aug 04, 2014 6:47 am

Wed Nov 05, 2014 4:55 am

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.

will.marriott
 
Posts: 27
Joined: Thu Oct 24, 2013 9:47 pm

Wed Nov 05, 2014 5:58 am

Dear will.marriott,

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

Best Regards,
Burning
E-iceblue Support Team
Best Regards,
Burning
E-iceblue Support Team
User avatar

burning.liu
 
Posts: 406
Joined: Mon Aug 04, 2014 6:47 am

Return to Spire.XLS