C# class library for exporting data to CSV/Excel file

• Download demo project for WinForms in C#.NET - 50.7 KB
• Download demo project for WebForms in C#.NET - 46.7 KB
• Download demo project for WebForms in VB.NET - 35.7 KB
• Download source - 17.3 KB

Introduction

Exporting data from a datatable to Excel or CSV is one of the most common functionality required in ASP.NET pages. Users can download the data from the datagrid into an Excel spreadsheet or CSV file for offline verification and/or computation. This article includes the source code for such functionality.

The common problems in exporting data from datagrid are

1. To clean up the formatting and export data only.
2. The header row in Excel always contained the database column names which were sometimes unintelligible to an ordinary user.
3. Often the Datasets contain data, like Internal Reference ID, necessary for the datagrid generation, which we didn't want the end user to see.

To remedy all these issues I came up with the a simpler and more adaptable way to export the datagrid itself to Excel or CSV file.

I kept the concept to a dll so that it could easily be used throughout an application.

Using the ExportData class library

This class library is fully implemented in C#.NET. This dll doesn't need to be registered. Simply copy the assembly "RKLib.ExportData.dll" into your project folder and add it to references. You can also include the "ExportData" project and reference it in your project.

This can be used in WebForms as well as in WinForms by simply passing a parameter to the export object's constructor.

• For WebForms:

  RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Web")

• For WinForms:

  RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Win")

The ExportDetails method has three types of overloads. You can call whichever best suits your requirement.

The following are the overload types:

• public void ExportDetails(DataTable DetailsTable, 
    ExportFormat FormatType, string FileName)

• public void ExportDetails(DataTable DetailsTable, int[] 
    ColumnList, ExportFormat FormatType, string FileName)

• public void ExportDetails(DataTable DetailsTable, int[] 
    ColumnList, string[] Headers, ExportFormat FormatType, string FileName)

Have a glance at the parameters

• DetailsTable - DataTable to be exported.
• FormatType - Export File Format. Use Export.ExportFormat.CSV for the CSV file or Export.ExportFormat.Excel for the Excel file.
• FileName - Export File Name.
  - For WebForms simply pass the filename. e.g. EmployeeInfo.xls
  - For WinForms pass the filename along with path. e.g. C:\\EmployeeInfo.csv
• ColumnList - DataFields of the DataTable to be exported. Specify their ordinals in an integer array.
• Headersrs - Custom Headers List for the specified columns in the export file. Specify the names in a string array.

You can call this method in the: ASPButton_Click event, LinkButton_Click event or Page_Load event or wherever you want. All you need to do is set the values to parameters and give a simple call to the required overload type of ExportDetails method.

The following is the code block that demonstrates "exporting specified columns" of a DataTable as an Excel file from WebForm.

[Code Behind]

private void btnExport2_Click(object sender, System.EventArgs e)
{
    // Export the details of specified columns
    try
    {
        // Get the datatable to export
        DataTable dtEmployee = ((DataSet) 
             Session["dsEmployee"]).Tables["Employee"].Copy();

        // Specify the column list to export
        int[] iColumns = {1,2,3,5,6};

        // Export the details of specified columns to Excel
        RKLib.ExportData.Export objExport = new 
            RKLib.ExportData.Export("Web");
        objExport.ExportDetails(dtEmployee, 
             iColumns, Export.ExportFormat.Excel, "EmployeesInfo2.xls");
    }
    catch(Exception Ex)
    {
        lblError.Text = Ex.Message;
    }
}

The following is the code block that demonstrates "exporting specified columns" of a DataTable as a CSV file from WindowsForm.

private void btnExportCSV_Click(object sender, System.EventArgs e)
{
    // Export the details of specified columns
    try
    {
        lblMessage.Text = "";
        
        // Get the datatable to export
        DataTable dtEmployee = dsEmployee.Tables["Employee"].Copy();

        // Specify the column list to export
        int[] iColumns = {1,2,3,5,6};

        // Export the details of specified columns to CSV
        RKLib.ExportData.Export objExport = new 
            RKLib.ExportData.Export("Win");
        objExport.ExportDetails(dtEmployee, 
            iColumns, Export.ExportFormat.CSV, 
            "C:\\EmployeesInfo.csv");
        lblMessage.Text = "Successfully exported to 
            C:\\EmployeesInfo.csv";
    }
    catch(Exception Ex)
    {
        lblMessage.Text = Ex.Message;
    }
}

Working with Demo Projects

To work with the WebForms Project in C#.NET

1. Extract the ExportDemo_CSharpNet.zip file to a designated directory.
2. Create a virtual directory in IIS with the name "ExportDemo_CSharpNet".
3. Open up the demo solution ExportDemo_CSharpNet.sln in Visual Studio 2003.

To work with WebForms Project in VB.NET

1. Extract the ExportDemo_VBNet.zip file to a designated directory.
2. Create a virtual directory in IIS with the name "ExportDemo_VBNet".
3. Open up the demo solution ExportDemo_VBNet.sln in Visual Studio 2003.

To work with WindowsForms Project in C#.NET

1. Extract the ExportDemo_WindowsForms.zip file to a designated directory.
2. Open up the demo solution ExportDataGrid.sln in Visual Studio 2003.

Points of Interest

• This doesn't require any stuff like page register tags.
• This can be used in WebForms as well as WinForms.
• This uses the XML features of dataset and XSLT for the export functionality.
• There is no looping through data elements.
• Use the export object as many times as required and however you want since export functionality needs a single and simple call to Export object.
• You can export as many datagrids as required on a single page.
• You can specify the column list to export.
• Even you can customize the headers of the export file.

History

• Posted on 29th Sep, 2004.
• Updated on 11th Oct, 2004. Updated files addresses export functionality from WinForms.
• Updated on 10th Apr, 2007. Updated files' addresses regarding the problem of special characters in column names.