How add colourful table to Excel using COM introp?

Question

This is my code to generate Excel using COM interop.

 public static void ExportDataTableToExcel(DataTable dt, string filepath)
 {
        object missing = Type.Missing;
        object misValue = System.Reflection.Missing.Value;

        //create excel
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

        //add excel workbook
        Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Add();

        //add worksheet to worlbook
        Microsoft.Office.Interop.Excel.Worksheet ws = wb.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

        //Set the header-row bold
        ws.Range["A1", "A1"].EntireRow.Font.Bold = true;

        //Adjust all columns
        ws.Columns.AutoFit();

       //spit top row
        ws.Application.ActiveWindow.SplitRow = 1;

        //freeze top row
        ws.Application.ActiveWindow.FreezePanes = true;

        int rowCount = 1;

        foreach (DataRow dr in dt.Rows)
        {
            rowCount += 1;

            for (int i = 1; i < dt.Columns.Count + 1; i++)
            {
                // Add the header the first time through
                if (rowCount == 2)
                {
                    ws.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                }

                ws.Cells[rowCount, i] = dr[i - 1].ToString();
            }
        }

        wb.SaveAs(@"C:\ErangaExcelTest.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue,
                                             misValue, misValue, misValue,
                                             Excel.XlSaveAsAccessMode.xlExclusive, misValue,
                                             misValue, misValue, misValue, misValue);

        wb.Close(missing, missing, missing);

        excel.Quit();
    }

This is the Excel which I get from this method.

I need a colourful table like this

What kind of modifications do I need to do have this kind of colourful Excel ?

Answer 1

You need to set the font color and the background color for each cell. You are already setting the font to bold by:

ws.Range["A1", "A1"].EntireRow.Font.Bold = true;

Now the Font object has more properties. To set the font color use the Range.Font.Color property. To set background color for a cell, see the Range.Interior property. Specifically you want to set Pattern to xlPatternSolid and then set some color with Color property of the Interior object.

In VBA you can specify fonts with their RGB values:

range.Font.Color = RGB(255, 0, 0)

will set the font color of the range to red.

To change borders, use the Range.Borders property. The link has an example code on how to use it.