ASp.NET: suggest a best option to allow the user to download the data in the datagrid in the client side in Excel format.

Question

Hi,

Please suggest the best option to allow the user to download the data in the datagridView from the page in excel format to the client machine.

I have a dataset that is binded to the datagrid view.Even it can be downloading the data in the dataset to an excel in the client side.

PLease suggest whether this it is possible to generate an excel and allow the user to download it in client side ?

I am using asp.Net,C#,SQL server,Excel.

Please help !

Answer 1

It is possible to code a solution using the MS Office Interop libraries.

Here's a few examples that may help you:

http://www.codeproject.com/KB/office/datasettoexcel.aspx

http://www.eggheadcafe.com/articles/20050404.asp

http://support.microsoft.com/kb/319180

http://www.c-sharpcorner.com/UploadFile/Globalking/datasettoexcel02272006232336PM/datasettoexcel.aspx

This last one is real nifty; It just converts the DataGrid into an Excel Spreadsheet... http://tim.mackey.ie/HowtoExportADatasetToExcelCAspnet.aspx

Answer 2

Excel can read HTML tables directly into a spreadsheet. You just need to send the same data down which is displayed in the web page (minus e.g. hyperlinks) with a Content-Disposition header set to attachment and a suitable mime-type which causes the client to open Excel. Excel then reads the downloaded HTML into a worksheet.

Answer 3

I suggest you output a Comma Separated Values (CSV) file. They are relatively easy to code up a solution for, you can write them out directly without having to do interop, and they open directly in Excel.

Answer 4

We do that in a few of our applications. It's really not difficult: We use the same aspx page (we send it a parameter to indicate that we want an excel file generated from the aspx), with a few lines of code that make the aspx be generated as an xls file.

1. The first lines of code in the Page_Load method should be:

   //open as excell file
   Response.Buffer = true;
   Response.ContentType = "application/vnd.ms-excel";
   string FileName = this.Page.ToString().Replace("ASP.", "") + ".xls"; // so the file can be saved as excel
   Response.AppendHeader("content-disposition", "filename=\"" + FileName + "\";attachment");
   

2. It's important that when in this mode, you make sure to disable ViewState. Otherwise it could completely confuse Excel and generate errors.

That's all we do, and it works wonderfully.

I should add that the Microsoft recommendation is to write the GridView into a HtmlTextWriter when exporting it to Excel, in order to make sure that the HTML generated is one that Excel will understand. It's easy to do (except for an exception that is generated, but is easy to overcome), and is explained nicely in these 2 articles: Gridview Export to Excel, and Extensive Study of GridView Export to Excel