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
- To clean up the formatting and export data only.
- The header row in Excel always contained the database column names which
were sometimes unintelligible to an ordinary user.
- 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.
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)
{
try
{
DataTable dtEmployee = ((DataSet)
Session["dsEmployee"]).Tables["Employee"].Copy();
int[] iColumns = {1,2,3,5,6};
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)
{
try
{
lblMessage.Text = "";
DataTable dtEmployee = dsEmployee.Tables["Employee"].Copy();
int[] iColumns = {1,2,3,5,6};
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
- Extract the ExportDemo_CSharpNet.zip file to a designated directory.
- Create a virtual directory in IIS with the name
"ExportDemo_CSharpNet".
- Open up the demo solution ExportDemo_CSharpNet.sln in Visual
Studio 2003.
To work with WebForms Project in VB.NET
- Extract the ExportDemo_VBNet.zip file to a designated directory.
- Create a virtual directory in IIS with the name "ExportDemo_VBNet".
- Open up the demo solution ExportDemo_VBNet.sln in Visual Studio
2003.
To work with WindowsForms Project in C#.NET
- Extract the ExportDemo_WindowsForms.zip file to a designated directory.
- 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.
I'm a solutions architect focused on cloud centric applications, Microservices, DevOps, Bots, IoT and Azure.