Style formating on FormatCondition

Aspose.Cells Product Family
1

I have a FormatCondition where I have issues setting style format number.


int index = sheet.ConditionalFormattings.Add();

var fcs = sheet.ConditionalFormattings[index];

var ca = new CellArea();

ca.StartRow = 1;

ca.EndRow = 24;

ca.StartColumn = 1;

ca.EndColumn = column + 1;

fcs.AddArea(ca);

var negativeIndex = fcs.AddCondition(FormatConditionType.CellValue);

var negativeCondition = fcs[negativeIndex];

negativeCondition.Operator = OperatorType.LessThan;

negativeCondition.Formula1 = "0.0";

FormatCondition fc = fcs[negativeIndex];

fc.Style.Font.Color = Color.Red;

fc.Style.Number = 2;


The code above works by setting foreground to all negative numbers to red.

But the Style.Number = 2 is not working (I want all numbers to also have the decimal format (e.g. 3.00)


2

Hi,

Thanks for your posting and using Aspose.Cells.

Please download and use the latest version: Aspose.Cells
for .NET v8.3.1.3 it is working fine.

We have tested this issue with the following code and attached the output Excel file and screenshot for your reference. As you can see -2 is showing up as -2.00 in red color which means output Excel file is ok.

C#


Workbook wb = new Workbook();

Worksheet sheet = wb.Worksheets[0];


int index = sheet.ConditionalFormattings.Add();


var fcs = sheet.ConditionalFormattings[index];


var ca = new CellArea();


ca.StartRow = 1;


ca.EndRow = 24;


ca.StartColumn = 1;


ca.EndColumn = 2;


fcs.AddArea(ca);


var negativeIndex = fcs.AddCondition(FormatConditionType.CellValue);


var negativeCondition = fcs[negativeIndex];


negativeCondition.Operator = OperatorType.LessThan;


negativeCondition.Formula1 = “0.0”;


FormatCondition fc = fcs[negativeIndex];


fc.Style.Font.Color = Color.Red;


fc.Style.Number = 2;



wb.Save(“output.xlsx”);



3

Hi


We only have license for 7.3.4.0 and below.
So for this version this is a bug and my only option is to pay the upgrade (to fix this)?
4
Hi,

arntmoberg:
...............

The code above works by setting foreground to all negative numbers to red.

But the Style.Number = 2 is not working (I want all numbers to also have the decimal format (e.g. 3.00)


Well, by default, FormatCondition would work for those values only which satisfy the condition, this is same as per MS Excel. To apply style to all the numbers with your desired formatting (irrespective of those values which are automatically formatted due to conditional formatting applied/ set), you have to apply the formatting by yourself in your code, see the sample code below for your complete reference (see the lines in bold specially). I have also attached the output file for your reference.

e.g

Sample code:


Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];


int column = 1;

int index = sheet.ConditionalFormattings.Add();


var fcs = sheet.ConditionalFormattings[index];


var ca = new CellArea();


ca.StartRow = 1;


ca.EndRow = 24;


ca.StartColumn = 1;


ca.EndColumn = column + 1;


fcs.AddArea(ca);


//Create and apply the style to the range of cells.

Style style = workbook.CreateStyle();

style.Number = 2;

//Create the range based on the cell area

Range range = sheet.Cells.CreateRange(ca.StartRow, ca.StartColumn, ca.EndRow - ca.StartRow + 1, ca.EndColumn - ca.StartColumn + 1);

range.ApplyStyle(style, new StyleFlag() { NumberFormat = true });



var negativeIndex = fcs.AddCondition(FormatConditionType.CellValue);


var negativeCondition = fcs[negativeIndex];


negativeCondition.Operator = OperatorType.LessThan;


negativeCondition.Formula1 = "0.0";


FormatCondition fc = fcs[negativeIndex];


fc.Style.Font.Color = Color.Red;


fc.Style.Number = 2;


workbook.Save("e:\\test2\\outcondtionalformattingstyle1.xlsx");


Let us know if you still have any issue.


Thank you.



5

This would not work for me as I cant use range for the formating. I only want decimal if the number is negative and the spreadsheet is huge (so I cant define many ranges).


Can I set the formating when I add the value to a cell? I tried .PutValue(myNr * 1.00)
6

Hi,

Thanks for your posting and using Aspose.Cells.

Your solution of adding conditional formatting will work fine to resolve your issue. Please see the following sample code. I have attached the source Excel file used in this code and output Excel file generated by it for your reference.

C#


Workbook wb = new Workbook(“source.xlsx”);

Worksheet sheet = wb.Worksheets[0];


int index = sheet.ConditionalFormattings.Add();


var fcs = sheet.ConditionalFormattings[index];


var ca = CellArea.CreateCellArea(“A1”, sheet.Cells.LastCell.Name);


fcs.AddArea(ca);


var negativeIndex = fcs.AddCondition(FormatConditionType.CellValue);


var negativeCondition = fcs[negativeIndex];


negativeCondition.Operator = OperatorType.LessThan;


negativeCondition.Formula1 = “0.0”;


FormatCondition fc = fcs[negativeIndex];


fc.Style.Font.Color = Color.Red;


fc.Style.Number = 2;



wb.Save(“output.xlsx”);