I can't seem to get any of the cells to change colors like I want. Here's what I'm trying to do. I am calculating the value of a certain row and then I want to examine that value to determine what color to use. Here's the code: col = 6 formula = "=IF(D6<> 0,E6/D6,0)" formula = replace(formula,"6",row) oXLW.Worksheets(1).Cells(row, col).Formula = formula ' -- Determine Color Formatting of cells for % Hrs Utilization
When I look at the spreadsheet, it is showing the formula for that cell when I highlight it but the value that is actually showing is a percentage. I'm attaching a snippet of the spreadsheet that is produced and what I'm trying to accomplish. Basically, I want the column F and column I to be formatted with the appropriate color based on the value in the cell.
Aspose.Cells does support conditional formatting. I think you
should use it for your requirements, you may add conditions for your
need with ease. see the following sample code just for reference: //Instantiating a Workbook object Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0]; sheet.Cells[“A1”].PutValue(10);
//Adds an empty conditional formatting int index = sheet.ConditionalFormattings.Add(); FormatConditionCollection fcs = sheet.ConditionalFormattings[index];
//Sets the conditional format range. CellArea ca = new CellArea(); ca.StartRow = 0; ca.EndRow = 9; ca.StartColumn = 1; ca.EndColumn = 1; fcs.AddArea(ca);
//Adds condition. int conditionIndex = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.GreaterOrEqual, “=$A$1”, null); int conditionIndex2 = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.LessThan, “=$A$1”, null); FormatCondition fc = fcs[conditionIndex]; //Sets the background color. fc.Style.BackgroundColor = Color.Red; //Sets the foreground color. fc.Style.ForegroundColor = Color.Green; //Sets the pattern fc.Style.Pattern = BackgroundType.DiagonalCrosshatch;