ASP.Net Component: Excel Export

Excel Export is a component which converts data from several sources into a true Microsoft Excel 2007 XLSX file.

Why Use Excel Export

The most common method used to export data to an Excel file relies on ASP.Net rendering the GridView control to a file with an XLS file extension rather than to the browser. However, this does not create a true Excel file. Instead, it produces a text file containing HTML markup to produce a table. This is because the GridView control is intended to display data on a web page and, therefore, it produces HTML which, in this case, is simply output to a file rather than to the browser.

Opening the file in Excel will display the HTML table as cells in the spreadsheet, however, with Excel 2007 a message is displayed warning the user that the content of the file is different to the file extension and may contain harmful data. This is not great for users to see.

An alternative method of creating an Excel file relies on ASP.Net creating an Excel object and manipulating the object by adding the data. This means that Excel must be installed on the web server, which is generally not the case.

Our Solution

Excel Export takes a different approach. It creates a true Microsoft Excel 2007 file which users can open without any warning messages. The component does not simply re-render a GridView control to a file and does not require Excel to be installed on the server.

The component is not just used to export data from GridView controls. It can also export data from a DataRow, DataReader, DataSet, DataTable or just a simple comma-separated string. IntelliSense support and comments are provided to give assistance when using the component.

Download & Licence

The Excel Export component is available to download and use free of charge, providing that it is not sold to third-parties without prior permission from Sunwright Computing.

Download the Excel Export ASP.Net component

Using Excel Export

To use the component, simply copy ExcelExport.dll and ExcelExport.XML to the bin folder of your project or website.

The following line should be added to your form or page:

using Sunwright;

To create a new instance of the component, use:

ExcelExport myExcel = new ExcelExport();

Or you can specify a filename to be used for the file:

Excel myExcel = new ExcelExport("c:\myFile.xlsx");

You will need to create a new worksheet within the Excel file using either:

myExcel.CreateWorksheet(SheetName)

where SheetName is the name to be given to the worksheet. Or alternatively, allow the component to generate the worksheet name. The generate name will be returned for you to use when populating the worksheet:

string mySheetName = myExcel.CreateWorksheet();

Use one of the PopulateFrom methods to pass the data to the component for exporting:

PopulateFromCSV()
PopulateFromDataReader()
PopulateFromDataRow()
PopulateFromDataSet()
PopulateFromDataTable()
PopulateFromGridView()

To save the Excel file, call the SaveToFile method:

myExcel.SaveToFile();

Or if you didn't specify a filename when you created the instance of the component, then use:

myExcel.SaveToFile("c:\myFile.xlsx");

The parameters for each method will be displayed using IntelliSense.

Basic Example

The following example will create an Excel file containing 2 worksheets, 1 containing the data from a GridView control, the other from a comma-separated string.

ExcelExport myExcel = new ExcelExport("c:\myFile.xlsx");
myExcel.CreateWorksheet("mySheet");
myExcel.PopulateFromGridView("mySheet", GridView1);
string mySheet2 = myExcel.CreateWorksheet();
myExcel.PopulateFromCSV(mySheet2, "The,cat,sat,on,the,mat");
myExcel.SaveToFile();

This code could be placed in the OnClick event of a button called Export.

Version History

1.0 - 7th March 2009
Initial release. This version does not allow for any formatting of the cells in the worksheets. Formatting will be added in a later release.

Bug Reports

If you have any problems using the Excel Export component or if you experience any bugs, please contact .