Conditional Formatting - Aspose.Cells for .NET and Java

Conditional Formatting is an advance feature in Microsoft Excel that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 500. When the value of the cell meets the format condition, the format you select is applied to the cell. If the value of the cell does not meet the format condition, the cell's default formatting is used. In Microsoft Excel, you can select Format | Conditional Formatting menu item and the following dialog would appear that would allow you to specify the conditional formatting:

Figure: Conditional Formatting in Microsoft Excel

Aspose.Cells doesn't fully support to apply conditional formatting on cells at runtime but the possible ways to work with conditional formatting are discussed in coming section.

Applying Conditional Formatting

Aspose.Cells supports conditional formatting in two ways:

Using Designer Spreadsheet
Using Copy Method
Creating Conditional Formatting at Runtime

Both Aspose.Cells for .Net and Java fully support to create conditional format at run time.

Using Designer Spreadsheet

Developers can create a designer spreadsheet using Microsoft Excel containing conditional formatting and then open that designer spreadsheet using Aspose.Cells. Aspose.Cells can load and save designer spreadsheets keeping their conditional formatting settings saved. If you don't know that what is a designer spreadsheet then Click Here for more details.

Using Copy Method

Aspose.Cells also allows developers to copy the conditional format settings from a cell to another cell of the same worksheet by calling the CopyConditionalFormatting method of the Worksheet class. CopyConditionalFormatting method takes the following parameters to copy the conditional formatting settings of a cell to another cell:

Source Row Index, represents the row index of the source cell from where to copy the conditional format settings
Source Column Index, represents the column index of the source cell from where to copy the conditional format settings
Destination Row Index, represents the row index of the destination cell to which the conditional format settings will be copied
Destination Column Index, represents the column index of the destination cell to which the conditional format settings will be copied

Example:

[C#]

//Instantiating a Workbook object

Workbook workbook = new Workbook();

//Creating a file stream containing the Excel file to be opened

FileStream fstream = new FileStream("C:\\book1.xls",FileMode.Open);

//Opening the Excel file through the file stream

workbook.Open(fstream);

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

//Copying conditional format settings from cell "A1" to cell "B1"

worksheet.CopyConditionalFormatting(0, 0, 0, 1);

//Saving the modified Excel file in default (that is Excel 2000) format

workbook.Save("C:\\output.xls",FileFormatType.Default);

//Closing the file stream to free all resources

fstream.Close();

[VB.NET]

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\book1.xls",FileMode.Open)

'Opening the Excel file through the file stream

workbook.Open(fstream)

'Accessing the first worksheet in the Excel file

Dim worksheet As Worksheet = workbook.Worksheets(0)

'Copying conditional format settings from cell "A1" to cell "B1"

worksheet.CopyConditionalFormatting(0, 0, 0, 1)

'Saving the modified Excel file in default (that is Excel 2000) format

workbook.Save("C:\\output.xls",FileFormatType.Default)

'Closing the file stream to free all resources

fstream.Close()

Creating at run time with Aspose.Cells for .Net and Java

Add/Delete Conditonal Formattings

[C#]

//Instantiating a Workbook object

Workbook workbook = new Workbook();

Worksheet sheet = workbook.Worksheets[0];

//Adds an empty conditional formatting

int index = sheet.ConditionalFormattings.Add();

FormatConditions fcs = sheet.ConditionalFormattings[index];

//Sets the conditional format range.

CllArea ca = new CellArea();

ca.StartRow = 0;

ca.EndRow = 0;

ca.StartColumn = 0;

ca.EndColumn = 0;

fcs.AddArea(ca);

ca = new CellArea();

ca.StartRow = 1;

ca.EndRow = 1;

ca.StartColumn = 1;

ca.EndColumn = 1;

fcs.AddArea(ca);

//Adds condition.

int conditionIndex = fcs.AddCondition(ConditionValueType.CellValue, OperatorType.Between, "=A2", "100");

//Adds condition.

int conditionIndex2 = fcs.AddCondition(ConditionValueType.CellValue, OperatorType.Between, "50", "100");

//Sets the background color.

FormatCondition fc = fcs[conditionIndex];

fc.Style.BackgroundColor = Color.Red;

//Saving the Excel file

workbook.Save("C:\\output.xls", FileFormatType.Default);

[VB.NET]

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

Dim sheet As Worksheet = workbook.Worksheets(0)

' Adds an empty conditional formatting

Dim index As Integer = sheet.ConditionalFormattings.Add()

Dim fcs As FormatConditions = sheet.ConditionalFormattings(index)

'Sets the conditional format range.

Dim ca As CellArea = New CellArea()

ca.StartRow = 0

ca.EndRow = 0

ca.StartColumn = 0

ca.EndColumn = 0

fcs.AddArea(ca)

ca = New CellArea()

ca.StartRow = 1

ca.EndRow = 1

ca.StartColumn = 1

ca.EndColumn = 1

fcs.AddArea(ca)

'Adds condition.

Integer conditionIndex = fcs.AddCondition(ConditionValueType.CellValue, OperatorType.Between, "=A2", "100")

'Adds condition.

Integer conditionIndex2 = fcs.AddCondition(ConditionValueType.CellValue, OperatorType.Between, "50", "100")

'Sets the background color.

Dim fc As FormatCondition = fcs(conditionIndex)

fc.Style.BackgroundColor = Color.Red

'Saving the Excel file

workbook.Save("C:\\output.xls", FileFormatType.Default)

[JAVA]

//There are two methods to add a conditional formatting:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

Worksheet sheet = workbook.getSheet(0);

ConditionalFormattings cfs = sheet.getConditionalFormattings();

//The first method:adds an empty conditional formatting

int index = cfs.add();

FormatConditions fcs = cfs.get(index);

//Sets the conditional format range.

fcs.addArea(new CellArea(0,0,0,0));

fcs.addArea(new CellArea(1,1,1,1));

fcs.addArea(new CellArea(2,2,5,5));

//Sets condition formulas.

int conditionIndex =

fcs.addFormatCondition(FormatConditionType.CELL_VALUE,OperatorType.BETWEEN,"=A2","100");

FormatCondition fc = fcs.getFormatCondition(conditionIndex);

int conditionIndex2 =

fcs.addFormatCondition(FormatConditionType.CELL_VALUE,OperatorType.BETWEEN,"50","100");

// The second method

int formatCondtionsIndex = cfs.add(new CellArea(9,1,10,1),

FormatConditionType.CELL_VALUE, OperatorType.BETWEEN,"=A1","100");

Set Font

[C#]

fc.Style.Font.IsItalic = true;

fc.Style.Font.IsBold = true;

fc.Style.Font.IsStrikeout = true;

fc.Style.Font.Underline = FontUnderlineType.Double;

fc.Style.Font.Color = Color.Black;

[VB.NET]

fc.Style.Font.IsItalic = True

fc.Style.Font.IsBold = True

fc.Style.Font.IsStrikeout = True

fc.Style.Font.Underline = FontUnderlineType.Double

fc.Style.Font.Color = Color.Black

[JAVA]

Style style = fc.getStyle();

style.getFont().setItalic(true);

style.getFont().setBold(true);

style.getFont().setStrikeOut(true);

style.getFont().setUnderline(Font.UNDERLINE_DOUBLE);

style.getFont().setColor(Color.Black);

Note: You can only change font style (boldness and posture), text color, underline style, and strikeout style.

Set Border

[C#]

fc.Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Dashed;

fc.Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Dashed;

fc.Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Dashed;

fc.Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Dashed;

fc.Style.Borders[BorderType.LeftBorder].Color = Color.FromArgb(0, 255, 255);

fc.Style.Borders[BorderType.RightBorder].Color = Color.FromArgb(0, 255, 255);

fc.Style.Borders[BorderType.TopBorder].Color = Color.FromArgb(0, 255, 255);

fc.Style.Borders[BorderType.BottomBorder].Color = Color.FromArgb(255, 255, 0);

[VB.NET]

fc.Style.Borders(BorderType.LeftBorder).LineStyle = CellBorderType.Dashed

fc.Style.Borders(BorderType.RightBorder).LineStyle = CellBorderType.Dashed

fc.Style.Borders(BorderType.TopBorder).LineStyle = CellBorderType.Dashed

fc.Style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Dashed

fc.Style.Borders(BorderType.LeftBorder).Color = Color.FromArgb(0, 255, 255)

fc.Style.Borders(BorderType.RightBorder).Color = Color.FromArgb(0, 255, 255)

fc.Style.Borders(BorderType.TopBorder).Color = Color.FromArgb(0, 255, 255)

fc.Style.Borders(BorderType.BottomBorder).Color = Color.FromArgb(255, 255, 0)

[JAVA]

Style style = fc.getStyle();

style.setBorderLine(BorderType.LEFT,BorderLineType.DASHED);

style.setBorderLine(BorderType.TOP,BorderLineType.DASHED);

style.setBorderLine(BorderType.RIGHT,BorderLineType.DASHED);

style.setBorderLine(BorderType.BOTTOM,BorderLineType.DASHED);

style.setBorderColor(BorderType.LEFT, new Color(0, 255, 255));

style.setBorderColor(BorderType.TOP, new Color(0, 255, 255));

style.setBorderColor(BorderType.RIGHT, new Color(0, 255, 255));

style.setBorderColor(BorderType.BOTTOM, new Color(255, 255, 0));

Note: You can only use thin line styles to the outline border. Diagonal lines are disallowed.

Set Pattern

[C#]

fc.Style.Pattern = BackgroundType.ReverseDiagonalStripe;

fc.Style.ForegroundColor = Color.FromArgb(255, 255, 0);

fc.Style.BackgroundColor = Color.FromArgb(0, 255, 255);

[VB.NET]

fc.Style.Pattern = BackgroundType.ReverseDiagonalStripe

fc.Style.ForegroundColor = Color.FromArgb(255, 255, 0)

fc.Style.BackgroundColor = Color.FromArgb(0, 255, 255)

[JAVA]

Style style = fc.getStyle();

style.setPatternStyle(PatternType.ReverseDiagonalStripe);

style.setPatternColor(new Color(255,255,0));

style.setColor(new Color(0,255,255));