-1

I have made an excel download console application using csom to download the list data from SharePoint. I want this excel to look like below SharePoint Excel:
SP Excel

Currently my custom excel looks like below:

Custom Excel

Can anyone please help me with the formatting code for my custom excel? I need formatting for alternate rows which can be of any length.

Note: The above SharePoint Excel is the SharePoint OOTB Export to Excel functionality for All items of the list.

munmun poddar
  • 107
  • 10
  • What library are you using to export your excel? also please put the code you are using to export excel – Lucifer May 30 '18 at 07:02
  • I am using Microsoft.Office.Interop.Excel library...Why do you need my export to excel code??? I just need to do the formatting. – munmun poddar May 30 '18 at 07:19
  • Possible duplicate of [Cell color changing In Excel using C#](https://stackoverflow.com/questions/2452417/cell-color-changing-in-excel-using-c-sharp) – Rafalon May 30 '18 at 07:23
  • I need formatting for alternate rows. – munmun poddar May 30 '18 at 07:26
  • Just use a `for` loop and check whether the index is odd or even, and change the color accordingly – Rafalon May 30 '18 at 07:28
  • @munmunpoddar I need your export excel code because you havent mentioned what type of collection you are using to export in excel datatable ot anyting else – Lucifer May 30 '18 at 07:28
  • It is also necessary to provide minimal,compelete & verfiable example -> https://stackoverflow.com/help/mcve – Lucifer May 30 '18 at 07:30
  • @Lucifer I have already mentioned that I am using Microsoft.Office.Interop.Excel library. by this library i had made my object `Excel.Application xlSamp = new Microsoft.Office.Interop.Excel.Application();` – munmun poddar May 30 '18 at 07:32
  • @Rafalon I am not getting how t use it for odd and even rows...I will appreciate if you can help me with the code. – munmun poddar May 30 '18 at 07:34
  • I edited and undeleted my answer – Rafalon May 30 '18 at 07:36

1 Answers1

1

My answer is based on this answer.

The following should work, with workSheet being your Excel worksheet:

// define your colors (header, odd rows, even rows)
var HeaderColor = XlRgbColor.rgbAliceBlue;
var EvenRowColor = XlRgbColor.rgbLightBlue;
var OddRowColor = XlRgbColor.rgbWhite;

// get the column/row count
int ColumnCount = _;
int RowCount = _;

// set the header color
var firstHeaderCell = workSheet.Cells[1, 1];
var lastHeaderCell = workSheet.Cells[1, ColumnCount];
workSheet.Range[firstHeaderCell, lastHeaderCell].Interior.Color = HeaderColor;

// loop through all the rows
for(int i=2; i<=RowCount; i++)
{
    var currentColor = i%2 == 1 ? OddRowColor : EvenRowColor;

    var firstRowCell = workSheet.Cells[i, 1];
    var lastRowCell = workSheet.Cells[i, ColumnCount];
    // set row color based on i being even or odd
    workSheet.Range[firstRowCell, lastRowCell].Interior.Color = currentColor;
}

Note that you can choose your colors using XlRgbColor enumeration.

Rafalon
  • 4,450
  • 2
  • 16
  • 30