C#: Export DataGridView to Microsoft Excel

person Jason Huangfolder_openC#, Code, HOW-TOlocal_offer, , , , access_time February 24, 2017

It’s fairly easy to export all the records that are on your Data Grid View to Excel. The code below shows you how to Run Microsoft Excel Interop and go through each rows and columns in data grid view, and write manually to an Excel worksheet, save the excel file.

The good thing is that you can export anything that you can load into the Data Grid View. The bad thing is that it’s manual dumping of each values in data grid into Excel cells, if you have a lot of data in the grid, it could take a while to populate.



Pre-requisite: to use this, you need to add a new reference to the assembly Microsoft Excel.
Go to Add reference >> COM >> Add “Microsoft Excel 12” or Microsoft Excel 15 depends on the version of Office you have installed.

The dgv_SQL is a DataGridView Control.


if (dgv_SQL.DataSource != null)
            {
                try
                {
                    //this code requires Excel object
                    // go to add reference, COM, Microsoft Excel 12 or 15 object
 
                    // 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
                    worksheet.Name = "Any worksheet name here is good";
 
 
                    // storing header part in Excel
                    for (int i = 1; i < dgv_SQL.Columns.Count + 1; i++)
                    {
                        worksheet.Cells[1, i] = dgv_SQL.Columns[i - 1].HeaderText;
                    }
 
                    // storing Each row and column value to excel sheet
                    for (int i = 0; i < dgv_SQL.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dgv_SQL.Columns.Count; j++)
                        {
                            worksheet.Cells[i + 2, j + 1] = dgv_SQL.Rows[i].Cells[j].Value.ToString();
                        }
                    }
 
 
                    // save the application
                    workbook.SaveAs("c:\\output.xlsx", 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() will close Excel
                    //app.Quit();
                }catch (System.Exception ex)
                {
 
                }

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>