Conditional Formatting

Aspose.Cells Product Family
1

Please find the attached excel sheet. Below are my requirements. Could you please share sample source code for the same.


1) Need to color the row to red, if value of column “InValid” is "True"
2) Need to color the row to green, if the sum of values in column “DataValue1” and “DataValue2” is greater than 100.
2

Hi,

Thanks for your posting and considering Aspose.Cells.

Please provide us your sample excel file which should have your desired conditional formattings as you have explained in your above two points. We will then load your excel file and figure out a code to achieve it using Aspose.Cells.

The conditional formatting inside your current excel file has errors and it does not work. I tried to change the value to True and it did not make the color of the row to red. So it is not working.

You can create your desired conditional formattings manually using Microsoft Excel and post the file here for our reference. Thanks for your cooperation in this regard.

3
Attached excel is created from Aspose by importing data from IList collection.

Algorithm:

Step-1: Import data from IList collection to excel file.

Step-2: Convert IList to typed DataTable.

Step-3: Create Aspose workbook, and import DataTable to workbook.

Step-4: Save Aspose workbook to an excel file.

Source Code:

workbook = new Workbook();
worksheet = workbook.Worksheets[0];

DataTable dataTable = new DataTable("StatusReport");
dataTable.Columns.Add(EDMConstants.ID, typeof(Int64));
dataTable.Columns.Add(EDMConstants.DataValue1, typeof(string));
dataTable.Columns.Add(EDMConstants.DataValue2, typeof(string));
dataTable.Columns.Add(EDMConstants.IsValid, typeof(bool));

foreach (var item in IList)
{
DataRow dr = dataTable.NewRow();

dr[EDMConstants.ID] = item.ID;
dr[EDMConstants.DataValue1] = item.DataValue1;
dr[EDMConstants.DataValue2] = item.DataValue2;
dr[EDMConstants.IsValid] = item.IsValid;
dataTable.Rows.Add(dr);
}

worksheet.Cells.ImportDataTable(dataTable, true, "A2");

//Merge Cells for the Header
worksheet.Cells.Merge(0, 0, 1, 25);
worksheet.Cells["A1"].Value = string.Format("Start Date - {0} and End Date - {1} (Displaying {2} of {3} records)", startDate.ToString("MM/dd/yyyy"), endDate.ToString("MM/dd/yyyy"), intRecordCount, intActualCount);

//Adding background color to a range of cells from A1:Y1(25 columns)
Aspose.Cells.Range range = worksheet.Cells.CreateRange(0, 0, 2, 25);
Aspose.Cells.Style style = worksheet.Cells["A2"].GetStyle();
style.ForegroundColor = System.Drawing.Color.LightGray;
style.Pattern = BackgroundType.Solid;
style.Font.Size = 9;
style.Font.IsBold = true;
range.SetStyle(style);

//Applying style to each row

Aspose.Cells.Style style1 = workbook.CreateStyle();
style.BackgroundColor = System.Drawing.Color.Red;
style.ForegroundColor = System.Drawing.Color.Red;
style.Pattern = BackgroundType.Solid;
//Creating StyleFlag
StyleFlag styleFlag = new StyleFlag();

for (int i = 2; i < intRowCount-1; i++)
{
Cell cell = worksheet.Cells.GetCell(i,18);
Row row = worksheet.Cells.GetRow(i);
if (cell.Value.ToString().ToLower() == "true")
{
row.ApplyStyle(style1, styleFlag);
}
}

//Auto-fit all the columns
workbook.Worksheets[0].AutoFitColumns();

//Saving the Excel file
workbook.Save(Path.Combine(statusFilePath, excelFileName));

Questions:

1) Is there a way to import the data directly from IList collection to excel file without using datatable.

2) Is there any way to apply the conditional style (Value in one column descides the color) directly to range of cells without iterating through each row.
4
nishalkr:
Attached excel is created from Aspose by importing data from IList collection.
Required output formatted file "StatusReport_03312016.xlsx" is also attached.

Algorithm:

Step-1: Import data from IList collection to excel file.

Step-2: Convert IList to typed DataTable.

Step-3: Create Aspose workbook, and import DataTable to workbook.

Step-4: Save Aspose workbook to an excel file.

Source Code:

workbook = new Workbook();
worksheet = workbook.Worksheets[0];

DataTable dataTable = new DataTable("StatusReport");
dataTable.Columns.Add(EDMConstants.ID, typeof(Int64));
dataTable.Columns.Add(EDMConstants.DataValue1, typeof(string));
dataTable.Columns.Add(EDMConstants.DataValue2, typeof(string));
dataTable.Columns.Add(EDMConstants.IsValid, typeof(bool));

foreach (var item in IList)
{
DataRow dr = dataTable.NewRow();

dr[EDMConstants.ID] = item.ID;
dr[EDMConstants.DataValue1] = item.DataValue1;
dr[EDMConstants.DataValue2] = item.DataValue2;
dr[EDMConstants.IsValid] = item.IsValid;
dataTable.Rows.Add(dr);
}

worksheet.Cells.ImportDataTable(dataTable, true, "A2");

//Merge Cells for the Header
worksheet.Cells.Merge(0, 0, 1, 25);
worksheet.Cells["A1"].Value = string.Format("Start Date - {0} and End Date - {1} (Displaying {2} of {3} records)", startDate.ToString("MM/dd/yyyy"), endDate.ToString("MM/dd/yyyy"), intRecordCount, intActualCount);

//Adding background color to a range of cells from A1:Y1(25 columns)
Aspose.Cells.Range range = worksheet.Cells.CreateRange(0, 0, 2, 25);
Aspose.Cells.Style style = worksheet.Cells["A2"].GetStyle();
style.ForegroundColor = System.Drawing.Color.LightGray;
style.Pattern = BackgroundType.Solid;
style.Font.Size = 9;
style.Font.IsBold = true;
range.SetStyle(style);

//Applying style to each row

Aspose.Cells.Style style1 = workbook.CreateStyle();
style.BackgroundColor = System.Drawing.Color.Red;
style.ForegroundColor = System.Drawing.Color.Red;
style.Pattern = BackgroundType.Solid;
//Creating StyleFlag
StyleFlag styleFlag = new StyleFlag();

for (int i = 2; i < intRowCount-1; i++)
{
Cell cell = worksheet.Cells.GetCell(i,18);
Row row = worksheet.Cells.GetRow(i);
if (cell.Value.ToString().ToLower() == "true")
{
row.ApplyStyle(style1, styleFlag);
}
}

//Auto-fit all the columns
workbook.Worksheets[0].AutoFitColumns();

//Saving the Excel file
workbook.Save(Path.Combine(statusFilePath, excelFileName));

Questions:

1) Is there a way to import the data directly from IList collection to excel file without using datatable.

2) Is there any way to apply the conditional style (Value in one column descides the color) directly to range of cells without iterating through each row.
5

Hi,

Thanks for your posting and using Aspose.Cells.

We do not need Aspose.Cells generated excel file. But we need excel file which is created by you manually using Microsoft Excel. It means we need expected output excel file.

We will then investigate your expected output excel file and then provide you a sample code to create your required conditional formatting.

6

See the attached excel file generated manually with conditional formatting rule.

7

Hi,

Thanks for your posting and using Aspose.Cells.

We have looked into your conditional formatting. Please see the following sample code and its output excel file. Please also check the input excel file. I have also attached the screenshot showing the difference between input excel file and the output excel file.

C#

Workbook w = new Workbook(“cf.xlsx”);

Worksheet sh1 = w.Worksheets[0];

int idx = sh1.ConditionalFormattings.Add();
FormatConditionCollection fcc = sh1.ConditionalFormattings[idx];

CellArea area = CellArea.CreateCellArea(“A4”, “G11”);
fcc.AddArea(area);

idx = fcc.AddCondition(FormatConditionType.Expression);
FormatCondition fc = fcc[idx];

fc.Formula1 = “=$F4 = TRUE”;
fc.Style.BackgroundColor = Color.Red;
fc.Operator = OperatorType.None;
fc.Priority = 1;

w.Save(“output.xlsx”);