Does anyone have an idea to how I can change the color of the worksheet from just plain white to an alternating blue, light blue, blue, light blue for each row for the entire workbook? When I first gave my user an excel report extracted from .net using .net codes, the entire excel report work sheet was in the blue, light blue scheme. When I switch over to Aspose.Cells, it only gave me a plain white spreadsheets. I saw an example in the forum but its shading individual rows. I need to do this for the entire workbook. Thanks
Hi,
Well, if you need to apply different shading color for alternative rows in the worksheet, there is no specific APIs to do it in one go, even Ms Excel can’t have any such command. This is not a big deal and you may easily accomplish your task with a few lines of code to apply formatting to alternative rows in the worksheet accordingly for your needs. I have written a sample code to apply formatting to alternative rows in the sheet for your reference, you may refer to it and may update or change it accordingly:
e.g
Sample code:
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Put a string value into a cell
sheet.Cells[“A1”].PutValue(“Import an object Array”);
//Create an object array and fill it with some values
object[] obj = { “Tom”, “John”, “kelly”, 1, 2, 2.8, 5.16, true, false };
//Import the object array to the sheet cells
sheet.Cells.ImportObjectArray(obj, 1, 0, false);
//Autofit all the columns in the sheet
sheet.AutoFitColumns();
//Create first style i.e…, light blue shading color with red font color.
Style style1 = workbook.CreateStyle();
style1.ForegroundColor = Color.LightBlue;
style1.Pattern = BackgroundType.Solid;
style1.Font.Color = Color.Red;
StyleFlag flag1 = new StyleFlag();
flag1.CellShading = true;
flag1.FontColor = true;
//Create second style i.e…, blue shading color with white text color.
Style style2 = workbook.CreateStyle();
style2.ForegroundColor = Color.Blue;
style2.Pattern = BackgroundType.Solid;
style2.Font.Color = Color.White;
StyleFlag flag2 = new StyleFlag();
flag2.CellShading = true;
flag2.FontColor = true;
//Loop through first 100 rows in the worksheet.
for (int i = 0; i < 100; i++)
{
if (i % 2 == 0)
{
sheet.Cells.ApplyRowStyle(i, style1, flag1);
}
else
{
sheet.Cells.ApplyRowStyle(i, style2, flag2);
}
}
workbook.Save(“e:\test2\outalternativerowsstyles1.xlsx”);
Hope, this helps a bit.
Thank you.
Amjad,
Thank you for the sample code. I will give it a go as soon as I finish my current project. Like you I like to keep my users happy. Thanks again, Phil
Hi,
Thanks for your posting and considering Aspose.Cells.
We are glad to know that it your first and foremost priority to keep your customers happy just like us. Let us know if you face any other issue or get some questions relating to our APIs, we will be glad to look into it and help you further.