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

Free Support Forum - aspose.com

Highlighting cells in Excel 2007

Hi, I have the following code that turns a cell red in older versions of Excel (2003 for example).

sheet.Cells[cell.Row, 0].Style.ForegroundColor = Color.Red;
sheet.Cells[cell.Row, 0].Style.Pattern = BackgroundType.Solid;

In Excel 2007 this does not work. Is there a better method?

I have also tried setting the BackgroundColor property, but that doesn't work either.

Hi,

Following code snippet will help you.

//Instantiating a Workbook object
Workbook workbook = new Workbook();

// Add sky blue color to the palette.
workbook.ChangePalette(System.Drawing.Color.SkyBlue, 55);

//Obtaining the first worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

//Now setting the foreground color of the "A1" cell to sky blue
worksheet.Cells["A1"].Style.ForegroundColor = Color.SkyBlue;

//Setting the background pattern of the "A1" cell to solid
worksheet.Cells["A1"].Style.Pattern = BackgroundType.Solid;

//Saving the Excel file
workbook.Save("d:\\test\\fillcolors.xls", FileFormatType.Default);

-------------------------------------------------------------------------------------------

Workbook wb = new Workbook();

// If the color is not in the standard color palette you have to add it into the palette.
wb.ChangePalette(System.Drawing.Color.SkyBlue, 55);

Cells cells = wb.Worksheets[0].Cells;
Cell cell = cells[0, 0];
cell.PutValue("Testing");

int index = wb.Styles.Add();
Style style = wb.Styles[index];

//For Cell's filling color
style.ForegroundColor = System.Drawing.Color.SkyBlue;
style.Pattern = BackgroundType.Solid;

// For text color
style.Font.Color = Color.White;
style.HorizontalAlignment = TextAlignmentType.Center;
cell.Style = style;
wb.Save("C:\\testclr.xls");

Thanks,

Thanks, I tried your suggestion. Still works for Excel 2003, but not 2007.

Here is the updated code...noticed I changed the pallette for position 54 and 55. However, my understanding is that 2007 does not have the pallette restriction. So does it matter if I replace the pallette for a .xlsx document?

private void InjectComment(Worksheet sheet, Cell cell, string comment, bool error)
{
int index;

index = sheet.Comments.Add(cell.Row, cell.Column);
sheet.Comments[index].Note = comment;
sheet.Comments[index].HeightCM = 10;
sheet.Comments[index].WidthCM = 10;

_excel.ChangePalette(Color.Red, 54);
_excel.ChangePalette(Color.FromArgb(255, 153, 0), 55);

sheet.Cells[cell.Row, 0].Style.ForegroundColor = Color.Red;
sheet.Cells[cell.Row, 0].Style.Pattern = BackgroundType.Solid;

if (cell.Column != 0)
{
// light yellow
cell.Style.ForegroundColor = Color.FromArgb(255, 153, 0);
cell.Style.Pattern = BackgroundType.Solid;
}

if (error)
_importErrorCount++;
}

Hi,

I have tested the code (given below) using VS2005, DLL version : 5.1.0.0, 4.8.1.0. It worked as it should. For your consideration i am attaching the results.

Code snippet:

//Instantiating a Workbook object

Workbook workbook = new Workbook();

// Add sky blue color to the palette.

workbook.ChangePalette(System.Drawing.Color.SkyBlue, 55);

//Obtaining the first worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

//Now setting the foreground color of the "A1" cell to sky blue

worksheet.Cells["A1"].Style.ForegroundColor = Color.SkyBlue;

//Setting the background pattern of the "A1" cell to solid

worksheet.Cells["A1"].Style.Pattern = BackgroundType.Solid;

//Saving the Excel file

workbook.Save("C:\\cellBGtest.xls", FileFormatType.Excel2003);

workbook.Save("C:\\cellBGtest.xlsx", FileFormatType.Excel2007Xlsx);

Thanks,

Interesting. Thanks for the code snippet. I pasted the exact code into a new console app that references Cells version 4.3.0.0 (my version).

The .xls file worked fine. The .xlsx file didn't get the highlighted cell.

So is there a free upgrade path to a version that works? If not, any other workarounds for my version?

Hi,

Well, I am afraid v4.3.0 is an old version, I think this is the version in which we started working on xlsx file format, so the Excel 2007 xlsx format features are not completely supported in it, so this version is not enhanced to work with xlsx file format. We recommend you to use our latest versions in which we have enhanced the Excel 2007 file formats and features.

Using the new versions e.g v5.1.1 (latest version), you don’t need to upgrade the color palette of Excel 2007 xlsx format. See the following code that works fine:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Style style = worksheet.Cells[“A1”].GetStyle();
style.ForegroundColor = Color.FromArgb(255, 153, 0);
style.Pattern = BackgroundType.Solid;
worksheet.Cells[“A1”].SetStyle(style);
workbook.Save(“e:\test\Book1.xlsx”);


Thank you.

Hi,

Thanks for considerting Aspose.

To achive this functionality you have to upgrade the version as latest versions have batter support for Excel 2007 (.Xlsx).

Thanks,