GST Billing Software

header image

EXCEL EXPORT

Erachana Line

Introduction

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);

export.OpenWorkBookTemplate("Reports\\BillAppExport.xlt");

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][0]);

export.SetCellValue(2, pos, data1.Rows[i][1]);

export.SetCellValue(3, pos, data1.Rows[i][2]);

export.SetCellValue(4, pos, data1.Rows[i][3]);

pos++;

i++;

}

MessageBox.Show("Done", "", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

else

{

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

Step 5:

Add the Excel Template file in the reports folder of the project.


Step 6:

Associate the expression to the export button in the form