Code for Conditional Formatting Using Icon set

Aspose.Cells Product Family
1

Hi,

Can you please provide sample code for conditional formatting using Icon sets.

Suppose if we are using Iconset of 3 symbols, how to provide formual for each icon.

for example :

if the cell value between 1 to 10 - show right icon

if value greated than 10 - show icon with !

if value less tha 0 - show icon with x

I hope you get my point.

Thanks,

2

Hi,

Please create your desired conditional formatting manually in MS Excel, save the file and provide us here, we will check and provide the sample code on how to do it with Aspose.Cells APIs.

Also, we recommend you to kindly see the document for your reference on conditional formatting:
http://www.aspose.com/docs/display/cellsnet/Conditional+Formatting

Note: Please see the sub-topic and example especially under “Applying Conditional Formatting to Microsoft Excel 2007 XLSX Files” for your complete reference.

Thank you.

3

hi,

Thanks for your response.

i have attached the sample book.

please check and provide code.

thanks,

4

Hi,


Please see the sample code below to accomplish your task (as per sample.xlsx) regarding conditional formattings for your reference for your needs:

Sample code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
ConditionalFormattingCollection cfs = sheet.ConditionalFormattings;
int index = cfs.Add();
FormatConditionCollection fcs = cfs[index];

//Set the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 10;
ca.StartColumn = 0;
ca.EndColumn = 0;
fcs.AddArea(ca);

//Add condition.
int conditionIndex = fcs.AddCondition(FormatConditionType.IconSet);

FormatCondition fc = fcs[0];
fc.IconSet.Type = IconSetType.Symbols3;
fc.IconSet.ShowValue = true;

fc.IconSet.Cfvos[1].Type = FormatConditionValueType.Number;
fc.IconSet.Cfvos[1].Value = 0;

fc.IconSet.Cfvos[2].Type = FormatConditionValueType.Number;
fc.IconSet.Cfvos[2].Value = 10;


Aspose.Cells.Cell c = sheet.Cells[“A1”];
c.PutValue(7);

//Save the Excel file
workbook.Save(@“e:\test2\outcondiformatting1.xlsx”);