Export to Excel is one of the most common functionality required in ASP.Net pages.
Users can download the data from the datagrid into an Excel spreadsheet for offline verification and/or computation. This article includes the source code for such functionality.
How it works
This main functionality to Export a datagrid from an ASP.Net Web Form to an Excel format is actually very simple. There are several solutions for this implementation and in this example we will convert the datagrid to excel format by manipulating the MIME type (media type or Content Type) of the Response. The RenderControl method available in the .Net Framework provides the server control content to an HtmlTextWriter which is subsequently written out to the Response Stream.
And just one more detail
There’s just one thing to take care of. A run-time error occurs if the DataGrid contains any controls other than the LiteralControl. This means that enabling Sorting, Paging or adding Template Columnns or Button columns to the datagrid can cause an error. There are several approaches to workaround this limitation. We will remove all the non-Literal controls in the DataGrid and replace the controls with a text representation , where possible. To do so, we will make use of Reflection. instead of querying each type of control and working out a replacement.
For all controls that have a SelectedItem property, we replace the control with the literal value of the SelectedItem property of the control. This covers most lists. For all controls that have a Text property, we replace the control with the literal value of the Text property of the control. This covers TextBox, Buttons, Button Columns, TemplateColumns. We make an exception only for TableCell controls. This takes care of most of the cases and you can add more checks and balances as required. The only drawback for this generalised formula is the order of the controls within a single cell could get changed.
Code Listing : Output the contents of the datagrid to Excel spreadsheet
In our sample web form, we connect to the Sample Pubs SQL Server database and display the data from the Employees table. The sample datagrid uses paging and a dummy Edit Column.
Note that you will need to have Excel 97 or later installed on the client. You can also add extra code for formatting the excel output.
Dipal Choksi is a Bachelor of Engineering (Computer Science). She has industry experience in team-effort projects and also as an individual contributor. She has worked on Visual Basic, Visual C++, Java, Directory Services, ASP projects
