Hello,
I am currently trying to read an excel file whose cells have been comditional formated to turn green or red based on the changed cell value to be greater or less than the current value. this works, however when i make changes and try to read this excel file Programatically and check for the cells whose value has been changed based on their background color, I see altho the changed value is present the background color comes out to be empty. My other question is weather this is the right way to find any changed cell values in a sheet. Below is the code i use to condition format the cells initially. any reply is kindly appreciated.
int conditionalFormats = 0;
Range forecastDataRange = workbook.Worksheets.GetRangeByName(planName);
int maxRowPosition = forecastDataRange.RowCount;
int maxColPosition = forecastDataRange.ColumnCount;
rowPosition = forecastDataRange[0, 0].Row;
colPosition = forecastDataRange[0, 0].Column;
for (int x = rowPosition; x < (maxRowPosition + rowPosition); x++)
{
int tmpStartCol = colPosition;
int tmpEndCol = colPosition;
for (int y = colPosition; y <= (maxColPosition + colPosition); y++)
{
bool lastColumn = false;
if (y == maxColPosition + colPosition)
{
lastColumn = true;
}
if (!lastColumn & forecastCells[x, y].StringValue != "")
{
conditionalFormats++;
/FormatConditions fcs = forecastSheet.ConditionalFormattings[
forecastSheet.ConditionalFormattings.Add()];
CellArea ca = new CellArea();
ca.StartRow = x;
ca.EndRow = x;
ca.EndColumn = y;
ca.StartColumn = y;
fcs.AddArea(ca);
// Creates Positive Change Condition Properties
FormatCondition fc = fcs[fcs.AddCondition(
FormatConditionType.CellValue,
OperatorType.GreaterThan,
forecastCells[x, y].DoubleValue.ToString(),
forecastCells[x, y].DoubleValue.ToString())];
fc.Style.Pattern = BackgroundType.Solid;
fc.Style.BackgroundColor = POS_CHANGED_COLOR;
/fc.Style.ForegroundColor = POS_CHANGED_COLOR;
// Creates Negative Change Condition Properties
FormatCondition fc2 = fcs[fcs.AddCondition(
FormatConditionType.CellValue,
OperatorType.LessThan,
forecastCells[x, y].DoubleValue.ToString(),
forecastCells[x, y].DoubleValue.ToString())];
fc2.Style.Pattern = BackgroundType.Solid;
fc2.Style.BackgroundColor = Color.Red;
fc2.Style.ForegroundColor = Color.Red;
}
}