Exporting DataGrid to Excel workbook in ASP.NET

For those whose bosses are accustomed to using Excel to analyze table data, ASP.NET offers a very powerful tool to save relational data into an Excel workbook. In this article, I am going to demonstrate how this can be done,in addition to serving as a highlight for the ASP.NET runtime model. To start off, we need to have some basic concepts about how ASP.NET runtime works, so that we can better understand why this can be done. Below is a graph of what the ASP.NET runtime threading model looks like: The HTTP request (the red rectangle outside the main yellow panel) comes in, then the HTTPHandler for that particular URL space will be invoked, after output caching, authentication, and etc. are done. The actual work for exporting Excel takes place inside the Handler, like most ASP.NET page handlers do. As a matter of fact, the ASP.NET page implements the IHTTPHandler interface, which also makes it an HTTPHandler, which is why it is possible to do a lot of processing in the ASP.NET pages. Here is a screenshot of what the object hierarchy looks like for ASP.NET page class: To output Excel format data from ASP.NET, we need a different kind of content type than "text/html", which is the default when you create a new web form in Visual Studio.Net. We need a content type called "application/vnd.ms-excel". The browser knows how to handle it if the computer has an association between Excel files and MS Excel application. If you are dumping an MS Word document over the wire to client browser, you would use content type "application/msword"; and if you are outputting binary files such as .exe files, then use "application/octet-stream". Lastly, let the datagrid call its super or base class System.Web.UI.Control's RenderControl method with the underlying System.Web.UI.HtmlTextWriter object as the parameter. , which in turn sits on top of a System.IO.StringWriter object. The figure below shows where the RenderControl method comes from: Ok, enough talk. Here comes the code for doing the trick. You usually wrap the codes in some sort of event handler, so that when desired event fires up, such as a button click or page load, your Excel worksheet will appear in the browser, then you can do "Save As" from the browser's File menu. void myEventHandler(object sender, System.EventArgs e) { Response.Clear(); Response.Buffer= true; Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); dg.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End(); } If your DataGrid does not have any add on features such as allow sorting, paging, which is usually the case, the code above will be all that you need. We will not cover the situation wherein you have some controls in the DataGrid itself. In that case, you would have to use reflection at runtime to remove those controls or substitute them with literal controls, otherwise, a runtime error will occur. Here is the line by line explanations of the key codes above: Response.Clear(): Clears all content output from the buffer stream. Response.Buffer: a value indicating whether to buffer output and send it after the entire response is finished processing. In our case, we want everything ready before send it off the wire. Response.Charset: the HTTP character set of the output stream. We use the default. this.EnableViewState: value indicating whether the page maintains its view state, and the view state of any server controls it contains, when the current page request ends. Since we are outputting only and not expecting any postback, so there is no need to enable the ViewState property which basically saves the control properties in a hidden field and sent back and forth between client and server.