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

Free Support Forum - aspose.com

Background color empty when reading a set background color excel file

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;

}

}

Hi,
after looking at your code, there are few things which are unknown for me:
1. forecastCell: what is this? what is its type?


The best way to read the cell is documented in this documented,:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/accessing-cells-of-a-worksheet.html

thanks

Thank you for the reply. Well I am attaching a sample work sheet and the peice of code that i use to read the sheet.

Workbook workbook = new Workbook();

MemoryStream ms = null;
string excelformaterror = String.Empty;
ms = new MemoryStream(excelFile); // where excelFile is a byte array for the work book
workbook.Open(ms, FileFormatType.Excel2003);


Worksheet forecastSheet = workbook.Worksheets["Test"];

Cells forecastCells = forecastSheet.Cells;

Color clr = forecastCells["C11"].Style.BackgroundColor;

upon this stage i see clr to be empty altho we see the color in C11 on the sheet to have a colored background.

Hi,

Please change your line of code:
Color clr = forecastCells[“C11”].Style.BackgroundColor;
to:
Color clr = forecastCells[“C11”].GetDisplayStyle().ForegroundColor;

The Cell.GetDisplayStyle Method is used to get the display style of the cell if the cell is conditionally formatted.

Thank you.