Click here to Skip to main content
11,644,731 members (69,455 online)
Click here to Skip to main content

Tagged as

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

, 5 Jan 2009 CPOL 146.9K 5 58
Rate this:
Please Sign up or sign in to vote.
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
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
    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.
    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.
    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

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
    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.
    ExcelApp.Application.Workbooks.Add(Type.Missing);
  4. Change properties of the Workbook
    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.
    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.
    ExcelApp.ActiveWorkbook.SaveCopyAs(FileName);
    ExcelApp.ActiveWorkbook.Saved = true;
  7. Exit the Excel Application and free up the resources
    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)

Share

About the Author

Geek13
India India
Eat, Sleep and Drink Code!

You may also be interested in...

Comments and Discussions

 
Question'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded Pin
navidsoft1-Jun-12 6:38
membernavidsoft1-Jun-12 6:38 
QuestionDatagrid View to Excel Format Pin
sanatmondal3-Apr-12 22:25
membersanatmondal3-Apr-12 22:25 
SuggestionDon't use ApplicationClass (from Microsoft MSDN) Pin
Member 205250420-Nov-11 17:32
memberMember 205250420-Nov-11 17:32 
QuestionDatagridview to Excel.....using error (again) Pin
PDTUM22-Aug-11 8:21
memberPDTUM22-Aug-11 8:21 
AnswerRe: Datagridview to Excel.....using error (again) Pin
Constance R17-Dec-13 2:11
memberConstance R17-Dec-13 2:11 
QuestionMicrosoft Office 11.0 Object Libary Pin
Boosh30-Jun-11 11:17
memberBoosh30-Jun-11 11:17 
GeneralData Transfer Pin
FayazSurosh16-Apr-11 19:23
memberFayazSurosh16-Apr-11 19:23 
GeneralRe: Data Transfer Pin
DaveTheCaver22-Aug-11 17:13
memberDaveTheCaver22-Aug-11 17:13 
GeneralMy vote of 5 Pin
ZZ01473-Dec-10 17:49
memberZZ01473-Dec-10 17:49 
QuestionASP.NET or Winforms? Pin
Code4Life0124-Aug-10 16:02
memberCode4Life0124-Aug-10 16:02 
Generalvery usefull Pin
jonesberyl17-Aug-10 21:54
memberjonesberyl17-Aug-10 21:54 
Generalvery well description! thank you Pin
pclion10-Jun-10 21:08
memberpclion10-Jun-10 21:08 
GeneralUna correccion Pin
fquaino8-Apr-10 14:51
memberfquaino8-Apr-10 14:51 
GeneralError in code: "Old format or invalid type library" Pin
H.R17-Jan-10 0:27
memberH.R17-Jan-10 0:27 
GeneralRe: Error in code: "Old format or invalid type library" Pin
reborn07052-Jul-10 17:25
memberreborn07052-Jul-10 17:25 
GeneralRe: Error in code: "Old format or invalid type library" Pin
zvi12346720-Sep-10 0:46
memberzvi12346720-Sep-10 0:46 
GeneralRe: Error in code: "Old format or invalid type library" Pin
smah111-Mar-11 18:14
membersmah111-Mar-11 18:14 
Generalgiving it a 4 Pin
NightJammer7-Sep-09 7:11
memberNightJammer7-Sep-09 7:11 
QuestionTwo workbooks open Pin
Hardus Lombaard28-Jul-09 21:57
memberHardus Lombaard28-Jul-09 21:57 
AnswerRe: Two workbooks open Pin
Geek1330-Jul-09 20:38
memberGeek1330-Jul-09 20:38 
GeneralNice article, but a couple of suggestions... Pin
Sengo15-Jul-09 9:16
memberSengo15-Jul-09 9:16 
General‘Microsoft Office 11.0 Object Library’ Pin
kim macintyre8-Jun-09 10:25
memberkim macintyre8-Jun-09 10:25 
GeneralRe: ‘Microsoft Office 11.0 Object Library’ Pin
Geek139-Jun-09 21:13
memberGeek139-Jun-09 21:13 
GeneralRe: ‘Microsoft Office 11.0 Object Library’ Pin
smah111-Mar-11 18:09
membersmah111-Mar-11 18:09 
QuestionRe: ‘Microsoft Office 11.0 Object Library’ Pin
Boosh18-Jun-11 19:15
memberBoosh18-Jun-11 19:15 
GeneralToo Slow Pin
Nouman Bhatti22-May-09 23:36
memberNouman Bhatti22-May-09 23:36 
GeneralRe: Too Slow Pin
PeterMoon24-May-09 14:29
memberPeterMoon24-May-09 14:29 
GeneralRe: Too Slow Pin
gg423721-Aug-09 5:38
membergg423721-Aug-09 5:38 
GeneralShort and sweet Pin
peter_007_peter4-Mar-09 9:05
memberpeter_007_peter4-Mar-09 9:05 
GeneralRe: Short and sweet Pin
Geek134-Mar-09 17:18
memberGeek134-Mar-09 17:18 
GeneralThank you Pin
Member 454566217-Jan-09 14:15
memberMember 454566217-Jan-09 14:15 
GeneralMy vote of 1 Pin
Jerome Vibert5-Jan-09 5:58
memberJerome Vibert5-Jan-09 5:58 
GeneralMy vote of 2 Pin
HyperhaRd5-Jan-09 5:51
memberHyperhaRd5-Jan-09 5:51 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150731.1 | Last Updated 5 Jan 2009
Article Copyright 2009 by Geek13
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid