How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt
This is a common task for ASP.NET developers. You have a Web application where you expose data from a database, Web service, or third-party API and you need an "Export to Excel" button. I have used different approaches to export to Excel from a Web application. Here's some options and guidance of when to use each one.
- Generate a CSV file: If you open a CSV file in Excel, you can see a table with data. You can use this approach if you don't need control over formatting, styles, or workbook structure. I have used this approach when I pull data from a data source and I don't need to render it on the browser. For example, you are working on a stock options solution and you connect to a Web service that pulls stock market prices. Your users don't want to see detailed stock information on a browser and prefer that you generate and Excel file where they can pivot stock prices.
- Generate an XML file: (with or without an XSLT, depending if you want to control the schema). Have you opened an XML file in Excel? This is pretty cool. Excel allows you to open XML files with our without a schema. You can also see a table of data in Excel and can have some control on formatting if you use a schema. I have used this approach when I have a data source that is already in XML format.
- Generate from GridView: I like this one a lot and I think it's a common practice between ASP.NET developers. Some page forms in ASP.NET Web applications display data in data controls. The GridView is a popular control that displays data in a table format. You can use it to bind to data source controls, such as SqlDataSource. You can export to Excel from a GridView using a StringWriter and an HtmlTextWriter. You can use this approach if you already have a page with a GridView. You already did a round-trip to get the data from any given source, so why do it twice? The issue is that you have little control over formatting, style, or workbook structure.
- Generate an Excel file using the Open XML SDK 2.0: If you use this approach you gain absolute control of the spreadsheet format and content. For example, you can generate a worksheet with a table and another one with a chart based on the same data source. You can have control over formats, styles, content, and document structure. Zeyad has a great post where he provides a detailed sample for this approach: Document Assembly Solution for SpreadsheetML.
Note: I can't stop to mention the big no-no… using the Excel PIA to generate a spreadsheet server-side. This is not a good practice and it's not recommended or supported, so let's forget about this one.
Now, for those of you who are working with any of the first three approaches, you may have seen the prompt of file format differ each time you export to Excel.
You get this message because you are opening a file in Microsoft Office Excel 2007 that contains content that does not match the files extension.
I am not very annoyed about this, but I know some people are. By default, a user can decide whether to open the file when the warning message is displayed, but you can control user-notification either:
- Updating the registry if you need to control the user-notification on a few PCs.
- Using a Group Policy Setting if you need to control the user-notification on lots of PCs.
Here's a KB article that provides detailed steps for both options: When you open a file in Excel 2007, you receive a warning that the file format differs from the format that the file name extension specifies.
Here's a quick code sample in C# for approach 3 (Export from GridView) that you can use to export to Excel. I changed my registry following the steps explained in the previous article and it worked like a charm. No prompt!
<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" %>
<%@ Import Namespace="System.IO" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Export to Excel - GridView Sample</title>
</head>
<script language="C#" runat="server">
// Get files from selected path
private void BindFiles() {
DirectoryInfo di = new DirectoryInfo(tbPath.Text);
gvFiles.DataSource = di.GetFiles();
gvFiles.DataBind();
}
protected void btnExportToExcel_Click(object sender, EventArgs e) {
ExportToExcel();
}
//Export to Excel from a GridView
protected void ExportToExcel() {
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=MyFiles.xls");
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
gvFiles.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
protected void Page_Load(object sender, EventArgs e) {
BindFiles();
}
public override void VerifyRenderingInServerForm(Control control) {
}
</script>
<body>
<form id="form1" runat="server">
<div>
<h1>
My Files</h1>
<table border="0" cellpadding="0" cellspacing="0" style="width: 100%; height: 12%">
<tr>
<td>
Path:
</td>
<td>
<asp:TextBox ID="tbPath" runat="server" Width="600px" Text="C:/"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="btnExportToExcel" runat="server" Text="ExportToExcel"
onclick="btnExportToExcel_Click" />
</td>
</tr>
</table>
</div>
<asp:GridView ID="gvFiles" runat="server">
</asp:GridView>
<br />
</form>
</body>
</html>
Happy Friday!
