Reader Level:
ARTICLE

Export DataGridView to Excel in C#

Posted by Hiren Soni Articles | Office Development August 24, 2010
This code example demonstrates how to export data from a DataGridView control to an Excel document using C#.
  • 2
  • 0
  • 164124

My application looks like Figure 1 where you can see I have some data in a DataGridView control on a Windows Forms application. I also have a Button called Export to Excel. When you click Export To Excel button, the application will export DataGridView data to an Excel document.

ExportDGVToExcelImg1.jpg
Figure 1

The Excel document will look like Figure 2.

ExportDGVToExcelImg2.jpg
Figure 2

Before you write code, you must add a reference to the Microsoft Excel object library.

Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library.

Now here is my Button click event handler where I create Excel object and document, get data from DataGridView and add rows and columns to the document.

Sample Code:

     private void button1_Click_1(object sender, EventArgs e)

        {

 

            // 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 = "Exported from gridview";

 

           

            // storing header part in Excel

            for(int i=1;i<dataGridView1.Columns.Count+1;i++)

            {

    worksheet.Cells[1, i] = dataGridView1.Columns[i-1].HeaderText;

            }

 

 

 

            // 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++)

                {

                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();

                }

            }

 

 

            // save the application

            workbook.SaveAs("c:\\output.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();
        }

 

   

Note this part of code gets data from DataGridView and fills cells.

            // 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++)

                {

                    worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();

                }

            }

 

I have taken dataGridView1.Rows.Count-1, because in datagridview it contains empty row at the last. (See in the figure of datagridview.)

I hope you like this article. Feel free to post questions or comments.

Article Extensions
Contents added by Jaydeo Dharpure on Jul 11, 2013
Contents added by vahid heidari on Mar 12, 2013
Contents added by moo nacup on Oct 18, 2012
Thank
Contents added by Duong on Dec 02, 2010
// 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
            //Funny
            app.Visible = true;
            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            try
            {
                 //Fixed:(Microsoft.Office.Interop.Excel.Worksheet)
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                // changing the name of active sheet
                worksheet.Name = "Exported from Ketoan";
                // storing header part in Excel
                for (int i = 1; i < DataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = DataGridView1.Columns[i - 1].HeaderText;
                }
                // 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++)
                    {
                        worksheet.Cells[i + 2, j + 1] = DataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }

                // save the application
                string fileName = String.Empty;
                //SaveFileDialog saveFileDialog1 = new SaveFileDialog();

                saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*";
                saveFileExcel.FilterIndex = 2;
                saveFileExcel.RestoreDirectory = true;

                if (saveFileExcel.ShowDialog() == DialogResult.OK)
                {
                    fileName = saveFileExcel.FileName;
                    //Fixed-old code :11 para->add 1:Type.Missing
                    workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                }
                else
                    return;               

                // Exit from the application
                //app.Quit();
            }
            catch (System.Exception ex)
            {
                
            }
            finally
            {
                app.Quit();
                workbook = null;
                app = null;
            }
COMMENT USING

Trending up