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:
Collapse | Copy Code
RKLib.ExportData.Export objExport = new RKLib.ExportData.Export("Web")•For WinForms:
Collapse | Copy Code
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:
• Collapse | Copy Code
public void ExportDetails(DataTable DetailsTable,
ExportFormat FormatType, string FileName)• Collapse | Copy Code
public void ExportDetails(DataTable DetailsTable, int[]
ColumnList, ExportFormat FormatType, string FileName)• Collapse | Copy Code
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.
Collapse | Copy Code
[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.
Collapse | Copy Code
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
From:http://www.codeproject.com/KB/aspnet/ExportClassLibrary.aspx
No comments:
Post a Comment