How to provide background color to a range of cells. I can create Range using CreateRange method… I need help in giving background color.
Below is the VSTO code
XLW.Range(.Cells(iCtr, 1), .Cells(iCtr, fldCount)).Interior.ColorIndex = 35
Hi,
Thanks for your query.
Please see the document on Named Ranges here:
(Note: please see the description/text and example codes especially under the sub-topic “Format Ranges…Setting Background Color and Font Attributes to a Named Range” for your reference for your needs.)
Hope, this helps you a bit.
Thank you.
Thanks for responding to my query.
With my Range I am able to retrive header part and can apply background.
But I want to apply different format starting from A2 cell to the till end records.
Please let me know how to do this.. I know record count and number of coloumns but it is not working.
Also can we apply styles to cellArea by defining cell strat row, end row, strat column and end column.
Please suggest.
Hi,
Well, if you need to apply different styles to individual cells in the matrix (range of cells), you may easily do it via using Cell.SetStyle() method. I have written a sample code which creates a table with specific rows and columns having header row first. I create different style objects with different background colors. I then set different styles to header row and individual/alternative rows/ records in the table for your reference. Please refer to it and write your own code accordingly for your needs:
e.g
Sample code:
Workbook wb = new Workbook(FileFormatType.Xlsx);
Style headerStyle = wb.CreateStyle();
headerStyle.ForegroundThemeColor = new ThemeColor(ThemeColorType.Accent1, 100);
headerStyle.Pattern = BackgroundType.Solid;
headerStyle.Name = “HeaderStyle”;
Style rowStyle = wb.CreateStyle();
rowStyle.ForegroundColor = Color.LightGreen;
rowStyle.Pattern = BackgroundType.Solid;
Style alternatingRowStyle = wb.CreateStyle();
alternatingRowStyle.ForegroundColor = Color.Yellow;
alternatingRowStyle.Pattern = BackgroundType.Solid;
Worksheet ws = wb.Worksheets[0];
// Set the columns headers with style.
for (int i = 0; i < 10; i++)
{
ws.Cells[0, i].PutValue(“Column #” + i.ToString());
ws.Cells[0, i].SetStyle(headerStyle);
}
// Add some rows for the columns
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(@“e:\test2\outstyle_test1.xlsx”);
Hope, this helps a bit.
Thank you.
Hi Amjad,
Thanks for posting the code.. Now I am able to keep styles for rows other than headers.
Could you please let me know how to keep Borders--> All Borders style to all the cells in the workbook.
Thanks in advance.
Hi Vidya,
There are many approaches to apply the cells borders, however, you have to first create a Style object with required borders set. Once you have set the Style object, you can either iterate over all the cells and set the style individually to each cell or you can create a Range object spanning over the required cell area and apply the style to the Range. Please check the detailed article on setting the cells borders and give it a try on your side. In case you face any difficulty, please feel free to write back.