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

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

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 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.

4. 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).

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();
       }
   }

   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.

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;

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

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