Conditional Formatting And Save()

Hi,


I am facing to an issue that take me time to indentify. I am using Aspose.Cells version 5.0.0.0 (13/06/2010) for trying to replace a reports generation built in with Interop.

The issue :

Not able to apply multiples conditional formatting on the same row/column with Save() and .xlsx

Code :

//Instantiating a Workbook object
Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells[0, 0].PutValue(50);
worksheet.Cells[0, 1].PutValue(-50);

//Adds an empty conditional formatting
int index = worksheet.ConditionalFormattings.Add();

FormatConditions fcs = worksheet.ConditionalFormattings[index];

//Sets the conditional format range.
CellArea ca = new CellArea()
{
StartRow = 0,
EndRow = 0,
StartColumn = 0,
EndColumn = 1
};
fcs.AddArea(ca);

//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterThan, “0”, null);

//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Green;

//Adds condition.
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.LessThan, “0”, null);

//Sets the background color.
FormatCondition fc2 = fcs[conditionIndex2];
fc2.Style.BackgroundColor = Color.Red;

//Saving the Excel file
workbook.Save(“C:\output.xls”); // SUCCESS

workbook.Save(“C:\output.xlsx”); // FAILED

Result :

When I saved the workbook using Excel 2003 format (Default), when I open “output.xls”, I have my first cell in green and the second one in red. => SUCCESS.

When I saved the workbook using Excel 2007 format, when I open “output.xlsx” I have both cells in green. => FAILED.

Could you tell me if I can use a workaround to get it works?

Thanks.
J.

Hi,

Please try our latest version/fix v5.2.0.5. I have tested your updated code (as mentioned below), it works fine for Excel 2007 Xlsx file too.

//Instantiating a Workbook object

Workbook workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells[0, 0].PutValue(50);
worksheet.Cells[0, 1].PutValue(-50);

//Adds an empty conditional formatting
int index = worksheet.ConditionalFormattings.Add();

FormatConditionCollection fcs = worksheet.ConditionalFormattings[index];

//Sets the conditional format range.
CellArea ca = new CellArea()
{
StartRow = 0,
EndRow = 0,
StartColumn = 0,
EndColumn = 1
};
fcs.AddArea(ca);

//Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterThan, “0”, null);

//Sets the background color.
FormatCondition fc = fcs[conditionIndex];
fc.Style.BackgroundColor = Color.Green;

//Adds condition.
int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.LessThan, “0”, null);

//Sets the background color.
FormatCondition fc2 = fcs[conditionIndex2];
fc2.Style.BackgroundColor = Color.Red;

//Saving the Excel file
workbook.Save(“C:\output.xls”); // SUCCESS

workbook.Save(“C:\output.xlsx”); // SUCCESS


Thank you.