Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 5 Jan 2009 CPOL
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!

Comments and Discussions

 
Question'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded Pinmembernavidsoft1-Jun-12 6:38 
QuestionDatagrid View to Excel Format Pinmembersanatmondal3-Apr-12 22:25 
SuggestionDon't use ApplicationClass (from Microsoft MSDN) PinmemberMember 205250420-Nov-11 17:32 
QuestionDatagridview to Excel.....using error (again) PinmemberPDTUM22-Aug-11 8:21 
AnswerRe: Datagridview to Excel.....using error (again) PinmemberConstance R17-Dec-13 2:11 
QuestionMicrosoft Office 11.0 Object Libary PinmemberBoosh30-Jun-11 11:17 
GeneralData Transfer PinmemberFayazSurosh16-Apr-11 19:23 
GeneralRe: Data Transfer PinmemberDaveTheCaver22-Aug-11 17:13 
GeneralMy vote of 5 PinmemberZZ01473-Dec-10 17:49 
QuestionASP.NET or Winforms? PinmemberCode4Life0124-Aug-10 16:02 
Generalvery usefull Pinmemberjonesberyl17-Aug-10 21:54 
Generalvery well description! thank you Pinmemberpclion10-Jun-10 21:08 
GeneralUna correccion Pinmemberfquaino8-Apr-10 14:51 
GeneralError in code: "Old format or invalid type library" PinmemberH.R17-Jan-10 0:27 
GeneralRe: Error in code: "Old format or invalid type library" Pinmemberreborn07052-Jul-10 17:25 
GeneralRe: Error in code: "Old format or invalid type library" Pinmemberzvi12346720-Sep-10 0:46 
GeneralRe: Error in code: "Old format or invalid type library" Pinmembersmah111-Mar-11 18:14 
Generalgiving it a 4 PinmemberNightJammer7-Sep-09 7:11 
QuestionTwo workbooks open PinmemberHardus Lombaard28-Jul-09 21:57 
AnswerRe: Two workbooks open PinmemberGeek1330-Jul-09 20:38 
GeneralNice article, but a couple of suggestions... PinmemberSengo15-Jul-09 9:16 
General‘Microsoft Office 11.0 Object Library’ Pinmemberkim macintyre8-Jun-09 10:25 
GeneralRe: ‘Microsoft Office 11.0 Object Library’ PinmemberGeek139-Jun-09 21:13 
GeneralRe: ‘Microsoft Office 11.0 Object Library’ Pinmembersmah111-Mar-11 18:09 
QuestionRe: ‘Microsoft Office 11.0 Object Library’ PinmemberBoosh18-Jun-11 19:15 

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 | Mobile
Web02 | 2.8.141022.2 | Last Updated 5 Jan 2009
Article Copyright 2009 by Geek13
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid