Click here to Skip to main content
15,868,016 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.8M   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

 
QuestionEncoding problem Pin
Тимофей Левендеев13-Aug-21 9:45
Тимофей Левендеев13-Aug-21 9:45 
Questioni want all column should be as a text formaet Pin
suldip12-Feb-18 0:06
suldip12-Feb-18 0:06 
QuestionRKLIB export data Pin
Erolcap23216-Aug-17 6:47
Erolcap23216-Aug-17 6:47 
QuestionExport to excel from rklib showing warning at the time of open in excel & did not open in kings office android phone Pin
Kailash_Singh13-Oct-14 5:17
professionalKailash_Singh13-Oct-14 5:17 
Questionexport to excel Pin
NekoNao21-Sep-14 20:00
NekoNao21-Sep-14 20:00 
Questionproblem with header in excel file Pin
Member 1013068214-Aug-14 17:50
Member 1013068214-Aug-14 17:50 
QuestionGrid View to excel in c# Pin
Member 995422418-Mar-14 2:37
Member 995422418-Mar-14 2:37 
QuestionMultiple Sheets in One Excel Pin
Member 400208315-Jan-14 0:58
Member 400208315-Jan-14 0:58 
AnswerRe: Multiple Sheets in One Excel Pin
Michael Gledhill10-Jun-14 2:15
Michael Gledhill10-Jun-14 2:15 
QuestionCompliments Pin
Member 1026888612-Sep-13 0:59
Member 1026888612-Sep-13 0:59 
Questionunable to evaluate expression because the code is optimized or native frame is on top of call stack Pin
lun07329-May-13 0:41
lun07329-May-13 0:41 
QuestionThe type or namespace name 'RKLib' could not be found (are you missing a using directive or an assembly reference?) Pin
searching for solution.....5-Sep-13 21:35
searching for solution.....5-Sep-13 21:35 
QuestionWhat modification i can do in your code method[CreateStylesheet] to let header as bold Pin
KamranShahid24-May-13 0:31
KamranShahid24-May-13 0:31 
BugXSLT Error Pin
Kamruzzaman Titu3-Feb-13 18:36
Kamruzzaman Titu3-Feb-13 18:36 
Questionoffice2007 打开文件提示不兼容 Pin
xiajun612@163.com27-Jan-13 16:10
xiajun612@163.com27-Jan-13 16:10 
QuestionExcel cannot open the file because the file format or file extension is not valid. Pin
Jamaludheen VV15-Nov-12 23:54
Jamaludheen VV15-Nov-12 23:54 
QuestionExport to Excel Pin
Malikarjuna Rao Pendem8-Oct-12 19:54
Malikarjuna Rao Pendem8-Oct-12 19:54 
QuestionPersian Language Problem Pin
hossein reisi16-Jun-12 21:34
hossein reisi16-Jun-12 21:34 
AnswerRe: Persian Language Problem Pin
hossein reisi16-Jun-12 21:43
hossein reisi16-Jun-12 21:43 
GeneralRe: Persian Language Problem Pin
claudiocas29-Oct-12 3:58
claudiocas29-Oct-12 3:58 
GeneralRe: Persian Language Problem Pin
hasanNAGIYEV24-Aug-14 19:18
hasanNAGIYEV24-Aug-14 19:18 
Questionsource for RKLib.ExportData.dll Pin
z01krh17-May-12 9:21
z01krh17-May-12 9:21 
QuestionExporting from a DataSet into a genuine Excel 2007 file, in one line of code. Pin
Michael Gledhill1-Dec-11 1:48
Michael Gledhill1-Dec-11 1:48 
GeneralSimple and easy to use Pin
Nitesh Kejriwal10-Nov-11 15:44
professionalNitesh Kejriwal10-Nov-11 15:44 
GeneralMy vote of 5 Pin
lovejun8-Oct-11 1:27
lovejun8-Oct-11 1:27 

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.