Set Conditional Formats of Excel and ODS files with C++
Introduction
Conditional formatting is an advanced Microsoft Excel feature 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 condition, the specified format 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, select Format, then Conditional Formatting to open the Conditional Formatting dialog.
Aspose.Cells supports applying conditional formatting to cells at runtime. This article explains how. It also explains how to calculate the color used by Excel for color scale conditional formatting.
Applying Conditional Formatting
Aspose.Cells supports conditional formatting in several ways:
- Using designer spreadsheet
- Using the copy method.
- Creating conditional formatting at runtime.
Using Designer Spreadsheet
Developers can create a designer spreadsheet that contains conditional formatting in Microsoft Excel and then open that spreadsheet with Aspose.Cells. Aspose.Cells loads and saves the designer spreadsheet, keeping any conditional formatting setting.
Using the Copy Method
Aspose.Cells allows developers to copy conditional format settings from one cell to another in the worksheet by calling the Range.Copy() method.
#include <iostream>
#include <memory>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
int main()
{
Aspose::Cells::Startup();
// Source directory path
U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
// Output directory path
U16String outDir(u"..\\Data\\02_OutputDirectory\\");
// Path of input Excel file
U16String inputFilePath = srcDir + u"Book1.xlsx";
// Path of output Excel file
U16String outputFilePath = outDir + u"output.xls";
// Open the Excel file
Workbook workbook(inputFilePath);
// Access the first worksheet in the Excel file
Worksheet worksheet = workbook.GetWorksheets().Get(0);
int totalRowCount = 0;
// Iterate through all worksheets in the workbook
for (int i = 0; i < workbook.GetWorksheets().GetCount(); i++)
{
Worksheet sourceSheet = workbook.GetWorksheets().Get(i);
// Get the maximum display range of the source sheet
Range sourceRange = sourceSheet.GetCells().GetMaxDisplayRange();
// Create a destination range in the first worksheet
Range destRange = worksheet.GetCells().CreateRange(
sourceRange.GetFirstRow() + totalRowCount,
sourceRange.GetFirstColumn(),
sourceRange.GetRowCount(),
sourceRange.GetColumnCount());
// Copy data from source range to destination range
destRange.Copy(sourceRange);
// Update the total row count
totalRowCount += sourceRange.GetRowCount();
}
// Save the modified Excel file
workbook.Save(outputFilePath);
Aspose::Cells::Cleanup();
return 0;
}
Applying Conditional Formatting at Runtime
Aspose.Cells lets you both add and remove conditional formatting at runtime. The code samples below show how to set conditional formatting:
- Instantiate a workbook.
- Add an empty conditional format.
- Set the range that the formatting should apply to.
- Define the formatting conditions.
- Save the file.
After this example comes a number of other smaller examples that show how to apply font settings, borders settings, and patterns.
Microsoft Excel 2007 added more advanced conditional formatting that Aspose.Cells also support. The examples here, illustrate how to use simple formatting, the Microsoft Excel 2007 examples show how to apply more advanced conditional formatting.
#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
int main() {
Aspose::Cells::Startup();
// Source directory path
U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
// Output directory path
U16String outDir(u"..\\Data\\02_OutputDirectory\\");
// Path of input excel file
U16String filePath = srcDir + u"Book1.xlsx";
// Instantiating a Workbook object
Workbook workbook;
Worksheet sheet = workbook.GetWorksheets().Get(0);
// Adds an empty conditional formatting
int index = sheet.GetConditionalFormattings().Add();
FormatConditionCollection fcs = sheet.GetConditionalFormattings().Get(index);
// Sets the conditional format range.
CellArea ca;
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;
fcs.AddArea(ca);
ca = CellArea();
ca.StartRow = 1;
ca.EndRow = 1;
ca.StartColumn = 1;
ca.EndColumn = 1;
fcs.AddArea(ca);
// Adds condition.
int conditionIndex = fcs.AddCondition(FormatConditionType::CellValue, OperatorType::Between, u"=A2", u"100");
// Adds condition.
int conditionIndex2 = fcs.AddCondition(FormatConditionType::CellValue, OperatorType::Between, u"50", u"100");
// Sets the background color.
FormatCondition fc = fcs.Get(conditionIndex);
fc.GetStyle().SetBackgroundColor(Color::Red());
// Saving the Excel file
workbook.Save(outDir + u"output.xls");
std::cout << "Conditional formatting applied successfully!" << std::endl;
Aspose::Cells::Cleanup();
return 0;
}
Set Font
#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
int main()
{
Aspose::Cells::Startup();
// Source directory path
U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
// Output directory path
U16String outDir(u"..\\Data\\02_OutputDirectory\\");
// Create a new workbook
Workbook workbook;
// Add a new worksheet to the workbook
int i = workbook.GetWorksheets().Add();
// Get the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.GetWorksheets().Get(i);
// Access the "A1" cell from the worksheet
Cell cell = worksheet.GetCells().Get(u"A1");
// Add some value to the "A1" cell
cell.PutValue(u"Hello Aspose!");
// Get the style of the cell
Style style = cell.GetStyle();
// Set the font weight to bold
Font font = style.GetFont();
font.SetIsBold(true);
// Apply the style to the cell
cell.SetStyle(style);
// Save the Excel file
workbook.Save(outDir + u"book1.out.xls", SaveFormat::Excel97To2003);
std::cout << "Excel file saved successfully!" << std::endl;
Aspose::Cells::Cleanup();
}
Set Border
#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
int main()
{
Aspose::Cells::Startup();
// Source directory path
U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
// Output directory path
U16String outDir(u"..\\Data\\02_OutputDirectory\\");
// Create a new workbook
Workbook workbook;
// Get the first worksheet
Worksheet sheet = workbook.GetWorksheets().Get(0);
// Add an empty conditional formatting
int index = sheet.GetConditionalFormattings().Add();
FormatConditionCollection fcs = sheet.GetConditionalFormattings().Get(index);
// Set the conditional format range
CellArea ca;
ca.StartRow = 0;
ca.EndRow = 5;
ca.StartColumn = 0;
ca.EndColumn = 3;
fcs.AddArea(ca);
// Add condition
int conditionIndex = fcs.AddCondition(FormatConditionType::CellValue, OperatorType::Between, u"50", u"100");
// Set the background color
FormatCondition fc = fcs.Get(conditionIndex);
fc.GetStyle().GetBorders().Get(BorderType::LeftBorder).SetLineStyle(CellBorderType::Dashed);
fc.GetStyle().GetBorders().Get(BorderType::RightBorder).SetLineStyle(CellBorderType::Dashed);
fc.GetStyle().GetBorders().Get(BorderType::TopBorder).SetLineStyle(CellBorderType::Dashed);
fc.GetStyle().GetBorders().Get(BorderType::BottomBorder).SetLineStyle(CellBorderType::Dashed);
fc.GetStyle().GetBorders().Get(BorderType::LeftBorder).SetColor(Color{0, 255, 255, 255});
fc.GetStyle().GetBorders().Get(BorderType::RightBorder).SetColor(Color{0, 255, 255, 255});
fc.GetStyle().GetBorders().Get(BorderType::TopBorder).SetColor(Color{0, 255, 255, 255});
fc.GetStyle().GetBorders().Get(BorderType::BottomBorder).SetColor(Color{255, 255, 0, 255});
// Save the workbook
workbook.Save(outDir + u"output.xlsx");
Aspose::Cells::Cleanup();
}
Set Pattern
#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;
int main() {
Aspose::Cells::Startup();
// Source directory path
U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
// Output directory path
U16String outDir(u"..\\Data\\02_OutputDirectory\\");
// Create a new workbook
Workbook workbook;
Worksheet sheet = workbook.GetWorksheets().Get(0);
// Adds an empty conditional formatting
int index = sheet.GetConditionalFormattings().Add();
FormatConditionCollection fcs = sheet.GetConditionalFormattings().Get(index);
// Sets the conditional format range
CellArea ca;
ca.StartRow = 0;
ca.EndRow = 5;
ca.StartColumn = 0;
ca.EndColumn = 3;
fcs.AddArea(ca);
// Adds condition
int conditionIndex = fcs.AddCondition(FormatConditionType::CellValue, OperatorType::Between, u"50", u"100");
FormatCondition fc = fcs.Get(conditionIndex);
fc.GetStyle().SetPattern(BackgroundType::ReverseDiagonalStripe);
fc.GetStyle().SetForegroundColor(Color{255, 255, 0, 255});
fc.GetStyle().SetBackgroundColor(Color{0, 255, 255, 255});
// Save the workbook
workbook.Save(outDir + u"output.xlsx");
std::cout << "Conditional formatting applied successfully!" << std::endl;
Aspose::Cells::Cleanup();
return 0;
}
Advance topics
- Adding 2-Color Scale and 3-Color Scale Conditional Formattings
- Apply Advanced Conditional Formatting
- Apply Conditional Formatting in Worksheets
- Apply Shading to Alternate Rows and Columns with Conditional Formatting
- Generate Conditional Formatting DataBars Images
- Get Icon Sets, Data Bars or Color Scales Objects used in Conditional Formatting