Click here to Skip to main content
15,861,168 members
Articles / Programming Languages / C#

Exporting a DataGridView to Excel in .NET 2.0 (C# code)

Rate me:
Please Sign up or sign in to vote.
4.37/5 (17 votes)
5 Jan 2009CPOL3 min read 200.2K   60   32
This article explains how to export the contents of a .NET Datagridview control to an excel file

Introduction

Hi everyone! In this article I would be telling you how to export the contents of a .NET 2.0 DataGridView control to an excel file. This is a requirement often in many projects as users may want to save some data generated in applications for future reference. And as most other things .NET makes it really easy for the programmers.

Background

Most Microsoft Office products such as Word, Outlook etc are available to a .NET programmer as classes. Similarly Excel is also available as an Application class. To use it

  1. Add a reference to the ‘Microsoft Office 11.0 Object Library’ to your project from COM components.
  2. Create an object of the ApplicationClass in the Excel namespace
C#
Excel.ApplicationClass ExcelApp = new Excel.ApplicationClass();

Excel application calls an excel file as a WorkBook. To export data to an excel file we need to create a new workbook in the application that is just created. Also more than one workbook can be opened in the application at a time (there is a collection called WorkBooks in the Excel application object). Only one of them will be the ActiveWorkBook (which can be access by the ActiveWorkBook property of the object). All the workbook related operations done to the application object will be directed to the ActiveWorkBook. We will need only one workbook. So that, by default, will be the ActiveWorkBook.

Using the Code

  1. Add a new workbook to the object of the Application class. The parameter to the Add method below is the name of the workbook. We are going to provide the name later (while saving the workbok). So Type.Missing can be used here. ExcelApp.Application.Workbooks.Add(Type.Missing);

    This is the stage where you can set general properties of the workbook. For example we can set the width of all the columns of the Workbook if you are not happy with the default column width.

  2. Change properties of the Workbook
    C#
    ExcelApp.Columns.ColumnWidth = 30;

    Next step is to copy the contents of the DataGridView to the Excel workbook. The cells of the WorkBook in the Excel application object can be accessed like a matrix. But one thing that you have to be careful about is that here the row and column numbering starts from 1. If you start from 0 you will get some weird exceptions. Iterate through each cell of the DataGridView object and copy the contents to the cells of the Excel Application Object (which goes to its active work book).

  3. Copy the contents of the DataGridView object to the cells of the Excel Application object. In the code below ReportDataGrodView is the name of my DataGridView object.
    C#
    for (int i = 0; i < ReportDataGridView.Rows.Count; i++)
    {
        DataGridViewRow row = ReportDataGridView.Rows[i];
        for(int j=0; j< row.Cells.Count; j++)
        {
            ExcelApp.Cells[i+1, j+1] = row.Cells[j].ToString();
        }
    }

    Then we have to save the workbook. There are 3 functions provided for that. Save, SaveAs and SaveCopyAs. I am using SaveCopyAs since I know that this is a new file generated and it cannot exist before. Probe on the other functions if you have a different requirement.

  4. Save the workbook at any suitable location. In the code below FileName is a string representing full path to the name of the file. This can be obtained from a SaveFileDialog if you want.
    C#
    ExcelApp.ActiveWorkbook.SaveCopyAs(FileName);
    ExcelApp.ActiveWorkbook.Saved = true;

Finally you have to close the Excel Application to free up the resources.

Exit the Excel Application and free up the resources

C#
ExcelApp.Quit();

Summary

I have given below the summary of the seven steps required.

  1. Add a reference to the ‘Microsoft Office 11.0 Object Library’ to your project from COM components.
  2. Create an object of the ApplicationClass in the Excel namespace
    C#
    Excel.ApplicationClass ExcelApp = new Excel.ApplicationClass();
  3. Add a new workbook to the object of the Application class. The parameter to the Add method below is the name of the workbook. We are going to provide the name later (while saving the file). So Type.Missing can be used here.
    C#
    ExcelApp.Application.Workbooks.Add(Type.Missing);
  4. Change properties of the Workbook
    C#
    ExcelApp.Columns.ColumnWidth = 30;
  5. Copy the contents of the DataGridView object to the cells of the Excel Application object. In the code below ReportDataGrodView is the name of my DataGridView object.
    C#
    for (int i = 0; i < ReportDataGridView.Rows.Count; i++)
    {
        DataGridViewRow row = ReportDataGridView.Rows[i];
        for(int j=0; j< row.Cells.Count; j++)
        {
            ExcelApp.Cells[i+1, j+1] = row.Cells[j].ToString();
        }
    }
  6. Save the workbook at any suitable location. In the code below FileName is a string representing full path to the name of the file. This can be obtained from a SaveFileDialog if you want.
    C#
    ExcelApp.ActiveWorkbook.SaveCopyAs(FileName);
    ExcelApp.ActiveWorkbook.Saved = true;
  7. Exit the Excel Application and free up the resources
    C#
    ExcelApp.Quit();

History

5/1/2009 - Created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
Eat, Sleep and Drink Code!

Comments and Discussions

 
Question'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded Pin
navidsoft1-Jun-12 6:38
professionalnavidsoft1-Jun-12 6:38 
QuestionDatagrid View to Excel Format Pin
sanatmondal3-Apr-12 22:25
sanatmondal3-Apr-12 22:25 
SuggestionDon't use ApplicationClass (from Microsoft MSDN) Pin
Member 205250420-Nov-11 17:32
Member 205250420-Nov-11 17:32 
QuestionDatagridview to Excel.....using error (again) Pin
PDTUM22-Aug-11 8:21
PDTUM22-Aug-11 8:21 
AnswerRe: Datagridview to Excel.....using error (again) Pin
Constance R17-Dec-13 2:11
Constance R17-Dec-13 2:11 
QuestionMicrosoft Office 11.0 Object Libary Pin
Boosh30-Jun-11 11:17
Boosh30-Jun-11 11:17 
GeneralData Transfer Pin
FayazSurosh16-Apr-11 19:23
FayazSurosh16-Apr-11 19:23 
GeneralRe: Data Transfer Pin
DaveTheCaver22-Aug-11 17:13
DaveTheCaver22-Aug-11 17:13 
GeneralMy vote of 5 Pin
ZZ01473-Dec-10 17:49
ZZ01473-Dec-10 17:49 
QuestionASP.NET or Winforms? Pin
Code4Life0124-Aug-10 16:02
Code4Life0124-Aug-10 16:02 
Generalvery usefull Pin
jonesberyl17-Aug-10 21:54
jonesberyl17-Aug-10 21:54 
Generalvery well description! thank you Pin
pclion10-Jun-10 21:08
pclion10-Jun-10 21:08 
GeneralUna correccion Pin
fquaino8-Apr-10 14:51
fquaino8-Apr-10 14:51 
GeneralError in code: "Old format or invalid type library" Pin
H.R17-Jan-10 0:27
H.R17-Jan-10 0:27 
GeneralRe: Error in code: "Old format or invalid type library" Pin
reborn07052-Jul-10 17:25
reborn07052-Jul-10 17:25 
GeneralRe: Error in code: "Old format or invalid type library" Pin
zvi12346720-Sep-10 0:46
zvi12346720-Sep-10 0:46 
GeneralRe: Error in code: "Old format or invalid type library" Pin
smah111-Mar-11 18:14
smah111-Mar-11 18:14 
Generalgiving it a 4 Pin
CalvinHobbies7-Sep-09 7:11
CalvinHobbies7-Sep-09 7:11 
QuestionTwo workbooks open Pin
Hardus Lombaard28-Jul-09 21:57
Hardus Lombaard28-Jul-09 21:57 
AnswerRe: Two workbooks open Pin
Geek1330-Jul-09 20:38
Geek1330-Jul-09 20:38 
GeneralNice article, but a couple of suggestions... Pin
Sengo15-Jul-09 9:16
Sengo15-Jul-09 9:16 
General‘Microsoft Office 11.0 Object Library’ Pin
kim macintyre8-Jun-09 10:25
kim macintyre8-Jun-09 10:25 
GeneralRe: ‘Microsoft Office 11.0 Object Library’ Pin
Geek139-Jun-09 21:13
Geek139-Jun-09 21:13 
GeneralRe: ‘Microsoft Office 11.0 Object Library’ Pin
smah111-Mar-11 18:09
smah111-Mar-11 18:09 
QuestionRe: ‘Microsoft Office 11.0 Object Library’ Pin
Boosh18-Jun-11 19:15
Boosh18-Jun-11 19:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.