EasyXLS™ library provides the methods to read or set conditional formatting to a cell or range of cells, including rows or columns, for an Excel file. If a specified condition is met then the defined format is applied to the cell.
The conditional formatting can have one or more condition set, and for each condition a special format is applied.
The supported formatted options are:
- Cell font settings: foreground, bold ant italic options, underline style, strikethrough - Cell border: border line style and color - Cell fill settings: background color
Source code sample
The below example shows how to export an Excel file and set conditional formatting to a range. The first step is to set the range where the conditional formatting will be applied, then set the formatting options.
// Create an instance of the class that exports Excel files having one sheet
ExcelDocument workbook = new ExcelDocument(1);
// Get the first worksheet
ExcelWorksheet xlsTab = ((ExcelWorksheet)workbook.easy_getSheet("Sheet1"));
...
// Set conditional formatting
xlsTab.easy_addConditionalFormatting("A1:C3", ConditionalFormatting.OPERATOR_BETWEEN,
"=9", "=11", true, true, Color.Red);
// Set another conditional formatting
xlsTab.easy_addConditionalFormatting("A6:C6", ConditionalFormatting.OPERATOR_BETWEEN,
"=COS(PI())+2", "", Color.Bisque);
xlsTab.easy_getConditionalFormattingAt("A6:C6").getConditionAt(0).setConditionType(
ConditionalFormatting.CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA);
// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Conditional formatting.xlsx");
' Create an instance of the class that exports Excel files having one sheetDim workbook As New ExcelDocument(1)
' Get the first worksheetDim xlsTab As ExcelWorksheet = workbook.easy_getSheet("Sheet1")
...
' Set conditional formatting
xlsTab.easy_addConditionalFormatting("A1:C3", _
ConditionalFormatting.OPERATOR_BETWEEN, "=9", "=11", True, True, Color.Red)
' Set another conditional formatting
xlsTab.easy_addConditionalFormatting("A6:C6", _
ConditionalFormatting.OPERATOR_BETWEEN, "=COS(PI())+2", "", Color.Bisque)
xlsTab.easy_getConditionalFormattingAt("A6:C6").getConditionAt(0).setConditionType( _
ConditionalFormatting.CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA)
' Export Excel file
workbook.easy_WriteXLSXFile("C:\Samples\Conditional formatting.xlsx")
C++// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
NULL,
CLSCTX_ALL,
__uuidof(EasyXLS::IExcelDocument),
(void**) &workbook) ;
// Create one sheet
workbook->easy_addWorksheet_2("Sheet1");
// Get the first worksheet
EasyXLS::IExcelWorksheetPtr xlsTab =
(EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheet("Sheet1");
...
// Set conditional formatting
xlsTab->easy_addConditionalFormatting_5("A1:C3",
CONDITIONALFORMATTING_OPERATOR_BETWEEN, "=9", "=11", true, true, COLOR_RED);
// Set another conditional formatting
xlsTab->easy_addConditionalFormatting_9("A6:C6",
CONDITIONALFORMATTING_OPERATOR_BETWEEN, "=COS(PI())+2", "", COLOR_BISQUE);
xlsTab->easy_getConditionalFormattingAt_2(
"A6:C6")->getConditionAt(0)->setConditionType(
CONDITIONALFORMATTING_CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA);
// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Conditional formatting.xlsx");
Click here to see Continuous Code ListingC++.NET// Create an instance of the class that exports Excel files having one sheet
ExcelDocument ^workbook = gcnew ExcelDocument(1);
// Get the first worksheet
ExcelWorksheet ^xlsTab =
safe_cast<ExcelWorksheet^>(workbook->easy_getSheet("Sheet1"));
...
// Set conditional formatting
xlsTab->easy_addConditionalFormatting("A1:C3",
ConditionalFormatting::OPERATOR_BETWEEN, "=9", "=11", true, true, Color::Red);
// Set another conditional formatting
xlsTab->easy_addConditionalFormatting("A6:C6",
ConditionalFormatting::OPERATOR_BETWEEN, "=COS(PI())+2", "", Color::Bisque);
xlsTab->easy_getConditionalFormattingAt("A6:C6")->getConditionAt(0)->setConditionType(
ConditionalFormatting::CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA);
// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Conditional formatting.xlsx");
Click here to see Continuous Code Listing
// Create an instance of the class that exports Excel files having one sheet
ExcelDocument workbook = new ExcelDocument(1);
// Get the table of data for the first worksheet
ExcelWorksheet xlsTab = ((ExcelWorksheet)workbook.easy_getSheet("Sheet1"));
...
// Set conditional formatting
xlsTab.easy_addConditionalFormatting("A1:C3", ConditionalFormatting.OPERATOR_BETWEEN,
"=9", "=11", true, true, Color.RED);
// Set another conditional formatting
xlsTab.easy_addConditionalFormatting("A6:C6", ConditionalFormatting.OPERATOR_BETWEEN,
"=COS(PI())+2", "", Color.ORANGE);
xlsTab.easy_getConditionalFormattingAt("A6:C6").getConditionAt(0).setConditionType(
ConditionalFormatting.CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA);
// Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Conditional formatting.xlsx");
.NET:// Create an instance of the class that exports Excel files
$workbook = new COM("EasyXLS.ExcelDocument");
// Create one sheet
$workbook->easy_addWorksheet_2("Sheet1");
// Get the first worksheet
$xlsTab = $workbook->easy_getSheet("Sheet1");
...
// Set conditional formatting
$xlsTab->easy_addConditionalFormatting_5("A1:C3",
$CONDITIONALFORMATTING_OPERATOR_BETWEEN,
"=9", "=11", true, true, (int)$COLOR_RED);
// Set another conditional formatting
$xlsTab->easy_addConditionalFormatting_9("A6:C6",
$CONDITIONALFORMATTING_OPERATOR_BETWEEN,
"=COS(PI())+2", "", (int)$COLOR_BISQUE);
$xlsTab->easy_getConditionalFormattingAt_2(
"A6:C6")->getConditionAt(0)->setConditionType(
$CONDITIONALFORMATTING_CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA);
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Conditional formatting.xlsx");
Click here to see Continuous Code ListingJava:// Create an instance of the class that exports Excel files
$workbook = new java("EasyXLS.ExcelDocument");
// Create a sheet
$workbook->easy_addWorksheet("Sheet1");
// Get the table of data for the first worksheet
$xlsTab = $workbook->easy_getSheet("Sheet1");
...
// Set conditional formatting
$xlsTab->easy_addConditionalFormatting("A1:C3", $CONDITIONALFORMATTING_OPERATOR_BETWEEN,
"=9", "=11", true, true, java("java.awt.Color")->RED);
// Set another conditional formatting
$xlsTab->easy_addConditionalFormatting("A6:C6", $CONDITIONALFORMATTING_OPERATOR_BETWEEN,
"=COS(PI())+2", "", java("java.awt.Color")->ORANGE);
$xlsTab->easy_getConditionalFormattingAt("A6:C6")->getConditionAt(0)->setConditionType(
$CONDITIONALFORMATTING_CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA);
// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Conditional formatting.xlsx");
Click here to see Continuous Code Listing
' Create an instance of the class that exports Excel filesset workbook = Server.CreateObject("EasyXLS.ExcelDocument")
' Create one sheet
workbook.easy_addWorksheet_2("Sheet1")
' Get the first worksheetset xlsTab = workbook.easy_getSheet("Sheet1")
...
' Set conditional formatting
xlsTab.easy_addConditionalFormatting_5 "A1:C3", _
CONDITIONALFORMATTING_OPERATOR_BETWEEN, "=9", "=11", true, true, Clng(COLOR_RED)
' Set another conditional formatting
xlsTab.easy_addConditionalFormatting_9 "A6:C6", _
CONDITIONALFORMATTING_OPERATOR_BETWEEN, "=COS(PI())+2", "", Clng(COLOR_BISQUE)
xlsTab.easy_getConditionalFormattingAt_2("A6:C6").getConditionAt(0).setConditionType( _
CONDITIONALFORMATTING_CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA)
' Export Excel file
workbook.easy_WriteXLSXFile ("C:\Samples\Conditional formatting.xlsx")
<!-- Create an instance of the class that exports Excel files --><cfobject type="java"class="EasyXLS.ExcelDocument"name="workbook"action="CREATE"><!-- Create one sheet --><cfset ret = workbook.easy_addWorksheet("Sheet1")><!-- Get the first worksheet --><cfset xlsTab = workbook.easy_getSheet("Sheet1")>
...
<!-- Set conditional formatting --><cfset xlsTab.easy_addConditionalFormatting("A1:C3",
ConditionalFormatting.OPERATOR_BETWEEN, "=9", "=11", true, true, Color.RED)><!-- Set another conditional formatting --><cfset xlsTab.easy_addConditionalFormatting("A6:C6",
ConditionalFormatting.OPERATOR_BETWEEN, "=COS(PI())+2", "", Color.ORANGE)><cfset xlsTab.easy_getConditionalFormattingAt(
"A6:C6").getConditionAt(0).setConditionType(
ConditionalFormatting.CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA)><!-- Export Excel file --><cfset ret = workbook.easy_WriteXLSXFile("C:\Samples\Conditional formatting.xlsx")>
.NET:# Create an instance of the class that exports Excel files having one sheet
workbook = ExcelDocument(1)
# Get the table of data for the first worksheet
xlsTab = workbook.easy_getSheet("Sheet1")
xlsTable = xlsTab.easy_getExcelTable()
...
# Set conditional formatting
xlsTab.easy_addConditionalFormatting("A1:C3", ConditionalFormatting.OPERATOR_BETWEEN,
"=9", "=11", True, True, Color.Red)
# Set another conditional formatting
xlsTab.easy_addConditionalFormatting("A6:C6", ConditionalFormatting.OPERATOR_BETWEEN,
"=COS(PI())+2", "", Color.Bisque)
xlsTab.easy_getConditionalFormattingAt("A6:C6").getConditionAt(0).setConditionType(
ConditionalFormatting.CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA)
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Conditional formatting.xlsx")
Click here to see Continuous Code ListingJava:# Create an instance of the class that exports Excel files having one sheet
workbook = gateway.jvm.ExcelDocument(1)
# Get the table of data for the first worksheet
xlsTab = workbook.easy_getSheet("Sheet1")
xlsTable = xlsTab.easy_getExcelTable()
...
# Set conditional formatting
xlsTab.easy_addConditionalFormatting("A1:C3", gateway.jvm.ConditionalFormatting.OPERATOR_BETWEEN,
"=9", "=11", True, True, gateway.jvm.Color.RED)
# Set another conditional formatting
xlsTab.easy_addConditionalFormatting("A6:C6", gateway.jvm.ConditionalFormatting.OPERATOR_BETWEEN,
"=COS(PI())+2", "", gateway.jvm.Color.ORANGE)
xlsTab.easy_getConditionalFormattingAt("A6:C6").getConditionAt(0).setConditionType_
(gateway.jvm.ConditionalFormatting.CONDITIONAL_FORMATTING_TYPE_EVALUATE_FORMULA)
# Export Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Conditional formatting.xlsx")
Click here to see Continuous Code Listing
The screen shot below represents the exported Excel file with conditional formatting applied to two ranges: - A1:C3 - the cells data will turn red if the value of the operator is between 9 and 11 - A6:C6 - the background color of the range will turn bisque if the condition is met.
