Export DataGridVeiw to excel in C# with cell background colors

1
votes

I have made a Shift Roster in DataGridView in winform C#. This is the excel sheet screenshot which I have exported from DataGridView.

I need to export DataGridView as it is to Excel Sheet maintaining the font color and background color also I want not to export first column of DataGridView to excel which is invisible.

I have used the following code to export DataGridView to Excel.

using (ExcelPackage pck = new ExcelPackage(file))
{
    // Add a new worksheet to the empty workbook
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
    // Load data from DataTable to the worksheet
    ws.Cells["A1"].LoadFromDataTable(((DataTable)gvShift.DataSource), true);
    ws.Cells.AutoFitColumns();

    // Add some styling
    using (ExcelRange rng = ws.Cells[1, 1, 1, gvShift.Columns.Count])
    {
        rng.Style.Font.Bold = true;
        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
        rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
        rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
    }

Kindly Help Please....

2
c#excelwinformsdatagridviewexport-to-excel
What library or dll you are using for excel exporting? – Kira
I am using Microsoft.Office.Interop.Excel – Mian Salman Nasir
@MianSalmanNasir Do take a look at EPPlus too. – Yahya
That is EPPlus. – Gogu CelMare

2 Answers

0
votes

you can use cells.interior.color

Range range = 
            oSheet.get_Range("A" + redRows.ToString(), "J"
                                    + redRows.ToString());

    range.Cells.Interior.Color = System.Drawing.Color.Red;

for more info check Microsoft interop excel to format excel cells

0
votes

I got the solution, I just went though the source from where I was setting the color, put each cell or DataGridView in loop and through range, applied the colors.

Thanks To All For Your Help