Click here to Skip to main content
16,021,810 members
Articles / Web Development / ASP.NET
Article

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

Rate me:
Please Sign up or sign in to vote.
4.62/5 (157 votes)
12 Apr 20074 min read 1.9M   53.2K   364   306
Free source code and tutorials for exporting data to CSV/Excel file from Web/Win Forms

Screenshot - ExportCode_ScreenShot.jpg

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:
    C#
    RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Web")
  • For WinForms:
    C#
    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:

  • C#
    public void ExportDetails(DataTable DetailsTable, 
      ExportFormat FormatType, string FileName)
  • C#
    public void ExportDetails(DataTable DetailsTable, int[] 
      ColumnList, ExportFormat FormatType, string FileName)
  • C#
    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.

C#
[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.

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Architect NYC DEP
United States United States
I'm a solutions architect focused on cloud centric applications, Microservices, DevOps, Bots, IoT and Azure.

Comments and Discussions

 
GeneralExcellent... Thanks... Pin
Suman Gadikrindi20-Mar-08 2:28
Suman Gadikrindi20-Mar-08 2:28 
Generalopen csv file in excel Pin
Member 258385910-Dec-07 22:51
Member 258385910-Dec-07 22:51 
AnswerRe: open csv file in excel Pin
Member 428635215-May-08 23:31
Member 428635215-May-08 23:31 
GeneralThanx ! Pin
AnuragBajpai28-Nov-07 2:10
AnuragBajpai28-Nov-07 2:10 
Generalbutton inside update panel Pin
rx78gp0327-Nov-07 12:40
rx78gp0327-Nov-07 12:40 
GeneralRe: button inside update panel Pin
est08154-Apr-08 8:43
est08154-Apr-08 8:43 
GeneralRe: button inside update panel Pin
dave.dolan13-Aug-08 7:29
dave.dolan13-Aug-08 7:29 
Generalthx + autoshow Pin
xor.be14-Nov-07 3:42
xor.be14-Nov-07 3:42 
Thx for this code,very useful.

It can however be useful to open it at once too (as the people at my work want).

so just FYI you can add this to acheive that :

System.Diagnostics.Process.Start("C:\\"+header+".csv");
GeneralActually this library only produces CSV file instead of Excel file Pin
thuongshoo2-Nov-07 0:53
thuongshoo2-Nov-07 0:53 
GeneralCreating New row on Period Pin
Ajwaka1-Nov-07 6:01
Ajwaka1-Nov-07 6:01 
GeneralNeed some help in csv export Pin
samamun00129-Oct-07 2:59
samamun00129-Oct-07 2:59 
GeneralRe: Need some help in csv export Pin
dave.dolan13-Aug-08 7:36
dave.dolan13-Aug-08 7:36 
GeneralThank you very much Pin
Strifter25-Oct-07 7:19
Strifter25-Oct-07 7:19 
Questionturkish char problem Pin
sdfxcvcxvx24-Oct-07 3:29
sdfxcvcxvx24-Oct-07 3:29 
GeneralRe: turkish char problem Pin
osmansonmez23-Apr-08 13:47
osmansonmez23-Apr-08 13:47 
GeneralRe: turkish char problem Pin
W4Rl0CK4725-Oct-09 23:08
W4Rl0CK4725-Oct-09 23:08 
Generalneed help Pin
Jagdish aher24-Sep-07 9:37
Jagdish aher24-Sep-07 9:37 
Generalnew line Pin
User 17912923-Sep-07 8:25
professionalUser 17912923-Sep-07 8:25 
Generalrklib code Pin
BillJam1120-Sep-07 7:26
BillJam1120-Sep-07 7:26 
GeneralData Row limitation Pin
y2kzone731-Aug-07 2:59
y2kzone731-Aug-07 2:59 
GeneralThank You!!! Pin
farndawg122115-Aug-07 9:24
farndawg122115-Aug-07 9:24 
QuestionModifying Export Pin
thepixel8-Aug-07 4:16
thepixel8-Aug-07 4:16 
GeneralExcel writing takes more time Pin
meeram3954-Jul-07 16:58
meeram3954-Jul-07 16:58 
Generali get this error Pin
roula merheb3-Jul-07 3:07
roula merheb3-Jul-07 3:07 
GeneralRe: i get this error Pin
DaveAce1520-Dec-07 9:07
DaveAce1520-Dec-07 9:07 

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.