Exporting data from a datatable to Excel or CSV is one of the most common functionality required in C# Projects. Users can download the data from the DataGrid into an Excel spreadsheet or CSV file for verification and/or computation.
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.
Points of Interest
• This uses the XML features of dataset and XLT 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 DataGrid as required.
• You can specify the column list to export.
• Even you can customize the headers of the export file.
Steps to Create for Excel Export Functionality
Step 1: Create an excel template which consists of required columns.
Step 2: Save the file with respective meaningful name
Step 3: It is better to save the file with an extension ".xlt" which specifies Microsoft Excel 1997 - 2003 version.
Step 4: Use the following code in an expression for the excel export functionality.
if (dgrData.Rows.Count > 0)
ExcelExport export = new ExcelExport(ExcelAppType.MicrosoftExcel, false);
DataTable data1 = null;
if (dgrData.DataSource is BindingSource)
data1 = (DataTable) ((BindingSource) dgrData.DataSource).DataSource;
else if (dgrData.DataSource is DataTable)
data1 = (DataTable) dgrData.DataSource;
int pos = 2;
int i = 0;
while (i < data1.Rows.Count)
export.SetCellValue(1, pos, data1.Rows[i]);
export.SetCellValue(2, pos, data1.Rows[i]);
export.SetCellValue(3, pos, data1.Rows[i]);
export.SetCellValue(4, pos, data1.Rows[i]);
MessageBox.Show("Done", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
MessageBox.Show("No Data to Export", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
The Parameters in the above code are as follows:
1. Data Grid
2. Excel Sheet Filename
3. Code to write data in excel sheet
Add the Excel Template file in the reports folder of the project.
Associate the expression to the export button in the form