Free Support Forum - aspose.com

Cell level styles not working in generated Excel 2007 workbook

Hello,

I’m trying to dynamically set individual cell styles, but having no luck. Here is a code snippet:

Workbook wb = new Workbook(FileFormatType.Excel2007Xlsx);

ThemeColor rowTc = new ThemeColor(ThemeColorType.Accent1, 20.0);
ThemeColor altRowTc = new ThemeColor(ThemeColorType.Accent1, 40.0);

Style headerStyle = wb.Styles[wb.Styles.Add()];
headerStyle.BackgroundThemeColor = new ThemeColor(ThemeColorType.Accent1, 100);
headerStyle.IsLocked = true;
headerStyle.Name = “HeaderStyle”;

Style rowStyle = wb.Styles[wb.Styles.Add()];
rowStyle.BackgroundThemeColor = rowTc;
rowStyle.IsLocked = true;

Style alternatingRowStyle = wb.Styles[wb.Styles.Add()];
alternatingRowStyle.BackgroundThemeColor = altRowTc;
alternatingRowStyle.IsLocked = true;

Worksheet ws = wb.Worksheets.Add(“TestSheet”);

// Set the columns
for (int i = 0; i < 10; i++)
{
ws.Cells[0, i].PutValue(“Column #” + i.ToString());
ws.Cells[0, i].SetStyle(headerStyle);
}

// Add a couple of rows
for (int row = 1; row < 5; row++)
{
for (int col = 0; col < 10; col++)
{
ws.Cells[row, col].PutValue(col);

if(row % 2 == 0)
ws.Cells[row, col].SetStyle(rowStyle);
else
ws.Cells[row, col].SetStyle(alternatingRowStyle);
}
}

wb.Save(Path.Combine(@“c:\temp”, Path.ChangeExtension(Path.GetFileName(Path.GetTempFileName()), “.xlsx”)), FileFormatType.Excel2007Xlsx);


What am I doing wrong here? I’ve tried a bunch of other things, including calling .Update() on the style object, setting the cell.Style.Name property, etc. I feel I am missing some simple setting somewhere.

Ultimately I need this kind of cell-level flexibility in my solution. Almost all cells in the generated workbook need to be locked, except for certain editable cells, which also need to have a different look than the rest.

Thank you,
Jim

Hi Jim,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, you need to specify the Style.Pattern property to apply the Background Style. Also, if you are using Style.Pattern = BackgroundType.Solid, so only foreground color (or fill color) will take effect and background color will not be applied (as MS Excel does when Style.Pattern is set as Solid). So you may need to modify your code abit as mentioned below.

Workbook wb = new Workbook(FileFormatType.Excel2007Xlsx);

ThemeColor rowTc = new ThemeColor(ThemeColorType.Accent2, 20.0);

ThemeColor altRowTc = new ThemeColor(ThemeColorType.Accent3, 40.0);

Style headerStyle = wb.Styles[wb.Styles.Add()];

headerStyle.ForegroundThemeColor= new ThemeColor(ThemeColorType.Accent1, 100);

headerStyle.Pattern = BackgroundType.Solid;

headerStyle.IsLocked = true;

headerStyle.Name = "HeaderStyle";

Style rowStyle = wb.Styles[wb.Styles.Add()];

rowStyle.ForegroundThemeColor = rowTc;

rowStyle.Pattern = BackgroundType.Solid;

rowStyle.IsLocked = true;

Style alternatingRowStyle = wb.Styles[wb.Styles.Add()];

alternatingRowStyle.ForegroundThemeColor = altRowTc;

alternatingRowStyle.Pattern = BackgroundType.Solid;

alternatingRowStyle.IsLocked = true;

Worksheet ws = wb.Worksheets.Add("TestSheet");

// Set the columns

for (int i = 0; i < 10; i++)

{

ws.Cells[0, i].PutValue("Column #" + i.ToString());

ws.Cells[0, i].SetStyle(headerStyle);

}

// Add a couple of rows

for (int row = 1; row < 5; row++)

{

for (int col = 0; col < 10; col++)

{

ws.Cells[row, col].PutValue(col);

if (row % 2 == 0)

ws.Cells[row, col].SetStyle(rowStyle);

else

ws.Cells[row, col].SetStyle(alternatingRowStyle);

}

}

wb.Save(@"c:\test_temp\style_test.xlsx", FileFormatType.Excel2007Xlsx);

Please see the following documentation link for further details on Colors & background Patterns:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/colors-background-patterns.html

Thank You & Best Regards,

That did the trick. I knew it had to be something trivial I was overlooking.


Also, for anyone who stumbles into this post, the double values in the ThemeColor() constructors are wrong:
ThemeColorType.Accent2, 20.0) should be ThemeColor(ThemeColorType.Accent2, .2).

More info on the theme styles can be found here: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/excel-2007-themes-and-colors.html under the Utilize Theme Colors section.

Thanks for your quick reply,
Jim