We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Conditional formatting based on value in current row

Hi,

I am using code similar to the below to place data in my spreadsheet. This works very well, using the ImportDataTable method.

In one column, I have a numeric field showing how many 'problems' there are with that record. I'd like all (populated) cells in that row to have a different background where that value is above 0 on that row.

I've looked at the related docs and tried several things, but the formula examples seem to reference a specific cell (eg A1) rather than a moving one (eg if this is row 10 I want it to be C10, but if it's row 20 I want it to be C20). I figure I either need a formula that references 'current row' somehow, or I need to access the 'current row' as the ImportDataTable method goes down through the rows.

Thanks!

int defaultStartRow = 9;

int defaultStartCol = 0;

// Create a style to apply for the header - just bold for now

Aspose.Cells.Style DataHeaderStyle = book.Worksheets["Template"].Cells["A2"].GetStyle();

DataHeaderStyle.Font.IsBold = true;

//Not creating new sheet at the moment

//Worksheet sheet = new Worksheet();

// Copy the template sheet and get the returned index of the new sheet

int sheetIndex = book.Worksheets.AddCopy("Template");

Worksheet sheet = book.Worksheets[sheetIndex];

sheet.Name = "Unfiltered";

// Place data and format header.

sheet.Cells.ImportDataTable(dt, true, defaultStartRow, defaultStartColumn, dt.Rows.Count, dt.Columns.Count, false, "mm/dd/yyyy");

sheet.Cells.CreateRange("10:10").SetStyle(DataHeaderStyle);

sheet.AutoFilter.Range = "A10:" + CellsHelper.ColumnIndexToName(sheet.Cells.MaxDataColumn) + "65000";

sheet.AutoFitColumns();

// ** APPLY CONDITIONAL FORMATTING ** //

//Adds an empty conditional formatting

int index = sheet.ConditionalFormattings.Add();

//Initialize FormatConditionCollection from newly inserted Index

FormatConditionCollection fcs = sheet.ConditionalFormattings[index];

//Sets the conditional format range.

CellArea ca = new CellArea();

ca.StartColumn = defaultStartColumn;

ca.StartRow = defaultStartRow;

ca.EndColumn = defaultStartColumn + dt.Columns.Count;

ca.EndRow = defaultStartRow + dt.Rows.Count;

//Assign FormatConditionCollection the Area

fcs.AddArea(ca);

//Adds condition.

int conditionIndex = fcs.AddCondition(FormatConditionType.Expression);

int columnTarget = 10;

//Sets the background color.

FormatCondition fc = fcs[conditionIndex];

// **********

// **********

// NEED FORMULA THAT SAYS 'if column 10 in the current row is > 0, trigger the format'

fc.Formula1 = "=IF...";

//Set BackgroundColor

fc.Style.BackgroundColor = Color.Red;

Hi,

Thanks for your posting and using the Aspose.Cells for .NET.

Please provide us your actual and expected output file. You can create one or both manually using Ms-Excel for illustration.

Also provide us your simple runnable code/project to look into this issue.

We will help you asap.

Many thanks. I've actually worked out the issue but appreciate your efforts.

Cheers

Hi,

It’s good to know, your issue is resolved now.

If you get any other question, please feel free to ask, we will help you asap.