Need formatting rule for Excel Aspose for certian condition

Question

I have a excel in which i have some columns which adds upto a end column total expences and that column value(expences total cell) decide which is the best row and according to it we have three colors green,red and blue( best green ,medium blue and red worst) .

I am doing it in ASPOSE cells . so any code rule would better fit in my case. thanks in advance.

Answer 1

You may try to add conditional formatting to your Excel spreadsheet to accomplish your task. See the following sample code (with comments) for your reference that demonstrates on how to implement "Color Scales" conditional formatting with rules for your needs.

e.g.

Sample code:

//Instantiating and loading the file
Workbook workbook = new Workbook("g:\\test2\\Book1.xlsx");

//Calculate formulas in the workbook
workbook.CalculateFormula();

//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];

//Add conditional formattings to the sheet
int index = sheet.ConditionalFormattings.Add();
//Get the newly added conditional formatting in the worksheet
FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
//Set the conditional format range, i.e., D2:D11
CellArea ca = new CellArea();
ca.StartRow = 1;
ca.EndRow = 10;
ca.StartColumn = 3;
ca.EndColumn = 3;
fcs.AddArea(ca);

//Add Color scales condition to the collection.
int idx = fcs.AddCondition(FormatConditionType.ColorScale);
//Get the format conditon
FormatCondition fc = fcs[idx];
//Set three colors for min mid and max values accordingly
fc.ColorScale.MinColor = System.Drawing.Color.Green;
fc.ColorScale.MidColor= System.Drawing.Color.Blue;
fc.ColorScale.MaxColor = System.Drawing.Color.Red;

//Saving the Excel file
workbook.Save("g:\\test2\\out1.xlsx");

Hope this helps a bit.

You may also post your queries in the dedicated forum.

PS. I am working as Support developer/ Evangelist at Aspose.