Color a particular row in excel C#

Question

I am creating an excel work book of my daily report. I want the header part to coloured to yellow. All links that are posted either requires a link of the workbook to be opened or are not specific to probelm. I am posting my code here, please suggest how to make Row 6 in yellow color.

    string workBookName;

        // creating Excel Application
        Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();


        // creating new WorkBook within Excel application
        Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);


        // creating new Excelsheet in workbook
        Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

        // see the excel sheet behind the program
        app.Visible = true;

        // get the reference of first sheet. By default its name is Sheet1.
        // store its reference to worksheet
        worksheet = workbook.Sheets["Sheet1"];
        worksheet = workbook.ActiveSheet;

        // changing the name of active sheet
        workBookName = DateTime.Now.ToString("ddMMMyyyy-HHmmss");
        worksheet.Name = workBookName;

        worksheet.Cells[1, 1] = "Logistics";
        worksheet.Cells[2, 1] = "Tracking Number";

        worksheet.Cells[4, 2] = "Date - ";
        worksheet.Cells[4, 3] = dateTimePicker1.Value.ToString("dd/MMM/yyyy");


        // storing header part in Excel
        for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
        {
            worksheet.Cells[6, i] = dataGridView1.Columns[i - 1].HeaderText;
           // worksheet.get_Range(worksheet.Cells[6, i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
        }



        // storing Each row and column value to excel sheet
        for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
        {
            for (int j = 0; j < dataGridView1.Columns.Count; j++)
            {
                //if (!string.IsNullOrWhiteSpace(dataGridView1.Rows[i].Cells[j].Value))
                {
                    worksheet.Cells[i + 8, j + 1] = dataGridView1.Rows[i].Cells[j].Value;


                }
            }
        }


        // save the application
        workbook.SaveAs("Tracking Number Report " + workBookName + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

        // Exit from the application
        app.Quit();

I want row 6, which is the header part in my case to be colored.

Possible duplicate of [c# excel how to change a color of a particular row](https://stackoverflow.com/questions/12725927/c-sharp-excel-how-to-change-a-color-of-a-particular-row) — wp78de, Oct 24 '17 at 06:00
I referred this link but that didnt help, as in my case I am creating the excel and then I want to color the row. In their case the are opening a file from a selected path. I cant use that system of choosing from a path. — Subhodeep Bhattacharjee, Oct 24 '17 at 06:13
Possible duplicate of [Cell color changing In Excel using C#](https://stackoverflow.com/questions/2452417/cell-color-changing-in-excel-using-c-sharp) — rmjoia, Oct 24 '17 at 06:40

score 0 · Accepted Answer · answered Oct 26 '17 at 12:02

if you run this code you will end up with an Excel instance still running because you have not released resources in interop using the .ReleaseComObject call - much more info on this here

I MUCH prefer to use one of the Open XML libraries such as EPPlus for this type of thing. It makes things a lot simpler and makes it easy to color a row.

Try something like this:

using (var excel = new ExcelPackage())
{
    var workBookName = DateTime.Now.ToString("ddMMMyyyy-HHmmss");
    var worksheet = excel.Workbook.Worksheets.Add(workBookName);


    worksheet.Cells[1, 1].Value = "Logistics";
    worksheet.Cells[2, 1].Value = "Tracking Number";

    worksheet.Cells[4, 2].Value = "Date - ";
    worksheet.Cells[4, 3].Value = dateTimePicker1.Value.ToString("dd/MMM/yyyy");

    for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
    {
        worksheet.Cells[6, i].Value = dataGridView1.Columns[i - 1].HeaderText;
    }
    worksheet.Cells["6:6"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
    worksheet.Cells["6:6"].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
    //
    //..etc
    //
    excel.SaveAs(new FileInfo("Tracking Number Report " + workBookName + ".xlsx"));
}

thanks for the suggestion.. It worked, also its much light and easy. Thanks — Subhodeep Bhattacharjee, Oct 28 '17 at 07:28

Color a particular row in excel C#

1 Answers1