Export DataGridView Data To Excel In C#

In this blog, we will learn how to export DataGridView data to Excel in C#.

In this code example, we will learn how to export a DataGridView data to an Excel file and save to a folder using C# code.
 

In this program, first, we have connected to a database and fetched data from a database, and shown data in a DataGridView control something like this.

 

 

If you're new to DataGridView control, I recommend read this: DataGridView in C#

 

Before we go to the coding section, we must add Microsoft Excel object library reference to our project.
To do that right click on project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library.

Project --> Add Reference --> COM tab --> Microsoft Excel 12.0 object library then press OK.

Now go to the page load event and fetch employee data and bind with the DataGridView.

private void FrmExport_Load(object sender, EventArgs e)
{
    SqlConnection sqlCon;
    string conString = null;
    string sqlQuery = null;

    conString = "Data Source=.;Initial Catalog=DemoTest;Integrated Security=SSPI;";
    sqlCon = new SqlConnection(conString);
    sqlCon.Open();
    sqlQuery = "SELECT * FROM tblEmployee";
    SqlDataAdapter dscmd = new SqlDataAdapter(sqlQuery, sqlCon);
    DataTable dtData = new DataTable();
    dscmd.Fill(dtData);
    dataGridView1.DataSource = dtData;
}

Now, on a Button click event handler, we have created an Excel object and document, get data from the DataGridView and add rows and columns to the document based on the data.

private void btnExcel_Click(object sender, EventArgs e)
{
    if (dataGridView1.Rows.Count > 0)
    {
        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Filter = "Excel (.xlsx)|  *.xlsx";
        sfd.FileName = "Output.xlsx";
        bool fileError = false;
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            if (File.Exists(sfd.FileName))
            {
                try
                {
                    File.Delete(sfd.FileName);
                }
                catch (IOException ex)
                {
                    fileError = true;
                    MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message);
                }
            }
            if (!fileError)
            {
                try
                {
                    Excel.Application XcelApp = new Excel.Application();
                    Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing);
                    Excel._Worksheet worksheet = null;

                    worksheet = workbook.Sheets["Sheet1"];
                    worksheet = workbook.ActiveSheet;
                    worksheet.Name = "Output";
                    worksheet.Application.ActiveWindow.SplitRow = 1;
                    worksheet.Application.ActiveWindow.FreezePanes = true;

                    for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
                    {
                        worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
                        worksheet.Cells[1, i].Font.NAME = "Calibri";
                        worksheet.Cells[1, i].Font.Bold = true;
                        worksheet.Cells[1, i].Interior.Color = Color.Wheat;
                        worksheet.Cells[1, i].Font.Size = 12;
                    }

                    for (int i = 0; i < dataGridView1.Rows.Count; i++)
                    {
                        for (int j = 0; j < dataGridView1.Columns.Count; j++)
                        {
                            worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
                        }
                    }

                    worksheet.Columns.AutoFit();
                    workbook.SaveAs(sfd.FileName);
                    XcelApp.Quit();

                    ReleaseObject(worksheet);
                    ReleaseObject(workbook);
                    ReleaseObject(XcelApp);

                    MessageBox.Show("Data Exported Successfully !!!", "Info");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error :" + ex.Message);
                }
            }
        }
    }
    else
    {
        MessageBox.Show("No Record To Export !!!", "Info");
    }
}

private void ReleaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occured while releasing object " + ex.Message, "Error");
    }
    finally
    {
        GC.Collect();
    }
}

After creating the excel file, make sure to release the excel objects through ReleaseObject() method.

Now run the application and when we click on "Export To Excel" button, it will ask where to save the file. Put a file name and click on Ok and it will generate an excel file.

I hope this code will help all readers. Happy Coding.