How to make look and feel of custom Excel sheet made using c# similar to the one downloaded from SharePoint

Question

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:

Currently my custom excel looks like below:

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.

Answer 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.