Formatting Excel cells based on multiple condition

Aspose.Cells Product Family
1

Hi Team,

We have to format Excel cells based on below conditions.

Value is Positive Number - We have to apply currency separator.

Value is Negative Number - We have to apply currency separator with brackets and red color

Value is Zero - We have to display dash (-)

Code:
Aspose.Cells.Range range = cells.CreateRange(0, 0, 10, 15);
Style stl = workbook.CreateStyle();
StyleFlag flg = new StyleFlag();
stl = worksheet.Workbook.CreateStyle();
stl.Font.Size = 11;
stl.Custom = “#,##0;(#,##0)”;
flg = new StyleFlag();
flg.Font = true;
flg.NumberFormat = true;
range.ApplyStyle(stl, flg);

How to achieve above requirement?

2

@SenthilRG27,

You may try to use conditional formattings to add multiple conditions on your desired cells range for your needs. See the following sample code that will accomplish the task for your reference:
e.g
Sample code:

 //Instantiating a Workbook object
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            int index = sheet.ConditionalFormattings.Add();
            FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
            //Sets the conditional format range.
            CellArea ca = new CellArea();//A1:A11
            ca.StartRow = 0;
            ca.EndRow = 10;
            ca.StartColumn = 0;
            ca.EndColumn = 0;
            fcs.AddArea(ca);
            //Adds condition.
            int conditionIndex1 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterThan, "0", null);
            int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.LessThan, "0", null);
            int conditionIndex3 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Equal, "0", null);
            
            //Sets the formatting based on different conditions.
            FormatCondition fc1 = fcs[conditionIndex1];
            fc1.Style.Custom = "$#,##0";
            FormatCondition fc2 = fcs[conditionIndex2];
            fc2.Style.Custom = "$#,##0_);[Red]($#,##0)";
            FormatCondition fc3 = fcs[conditionIndex3];
            fc3.Style.Custom = "-";
            fc3.Style.BackgroundColor = Color.Yellow;
            //Saving the Excel file
            workbook.Save("e:\\test2\\out1.xlsx");

Hope, this helps a bit.

3

Hi Amjad,

Above solution works as expected. Thank you.

4

@SenthilRG27,
You are welcome.

5

@SenthilRG27,

By the way, there is another solution to your requirements of separating positive/negative/zero numbers for formatting. You may try simple custom format for Style, such as:

style.Custom = $#,##0;[Red]($#,##0);"-";

Hope, this helps a bit too.

6

Hi Amjad,

This above solution also works fine. Only issue with above solution is that I need to show “-” in center instead of right alignment.

7

@SenthilRG27,

You may set text alignment (horizontal) of the cell as “Center”. See the document on text alignments for your reference: