SpreadsheetGear set background color dynamically

Question

I have a excel report with SpreadsheetGear component and get data from dataTable. one of column is true/false. I want to set background color for my celles dynamically if my data is true the background color must be red and if false the color must be yellow.

string fileName = @"C:\Temp\1.xlsx";
          
// Create a new workbook and worksheet.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
worksheet.Name = "Spice Order";

// Get the top left cell for the DataTable.
SpreadsheetGear.IRange range = worksheet.Cells["A1"];

// Copy the DataTable to the worksheet range.
range.CopyFromDataTable(parentDataTable, SpreadsheetGear.Data.SetDataFlags.None);

// Auto size all worksheet columns which contain data
worksheet.UsedRange.Columns.AutoFit();
worksheet.SaveAs(fileName, FileFormat.OpenXMLWorkbook);

score 1 · Answer 1 · answered Aug 17 '22 at 16:00

Your sample code doesn't really help because it doesn't provide adequate context. For instance, we have no clue what is in "1.xlsx" so also have no clue where the TRUE/FALSE values might be in the target worksheet.

Below is a from-scratch but more complete example demonstrating how you can use Excel's Conditional Formatting feature to change the background / interior color of a given range based on each cell's current value. That way you can setup the rules just once on the applied range and then let them take care of the formatting, which will also automatically change if you or the user updates cell values down the road.

Please also see documentation for more details, such as

IRange.FormatConditions property
IFormatConditions interface
IFormatCondition interface

Example:

using SpreadsheetGear;
...

// Create a workbook with A1:A10 filled with alternating TRUE / FALSE values.
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.ActiveWorksheet;
IRange cells = worksheet.Cells;
for (int i = 0; i < 10; i++)
    cells[i, 0].Value = i % 2 == 0;

// Add a Conditional Format on A1:A10 that formats TRUE values with a red background.
var trueRule = cells["A1:A10"].FormatConditions.Add(FormatConditionType.CellValue, 
    FormatConditionOperator.Equal, "=TRUE", null);
trueRule.Interior.Color = SpreadsheetGear.Colors.Red;

// Add a second that formats FALSE values in yellow.
var falseRule = cells["A1:A10"].FormatConditions.Add(FormatConditionType.CellValue, 
    FormatConditionOperator.Equal, "=FALSE", null);
falseRule.Interior.Color = SpreadsheetGear.Colors.Yellow;

Output:

SpreadsheetGear set background color dynamically

1 Answers1