Erika Ehrli - Adventures with Office Products & Technologies
MSDN & TechNet: Releasing Office, SharePoint, Exchange & Lync Centers and content for developers and IT professionals.

How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt

How to Export Data to Excel from an ASP.NET Application + Avoid the File Format Differ Prompt

Rate This
  • Comments 52

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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:

  1. Updating the registry if you need to control the user-notification on a few PCs.
  2. 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!

Leave a Comment
  • Please add 8 and 7 and type the answer here:
  • Post
  • PingBack from http://www.clickandsolve.com/?p=2418

  • Thank you for submitting this cool story - Trackback from DotNetShoutout

  • Hi,

           Thanks for your Article. Its really nice one. Can you tell me how to export a dataset containing multiple tables to excel. I think this refers to your second approach.

  • Combine/Compress/MinifyJSandCSSfilesinASP.NETMVCHowtoExportDatatoExcelfromanASP.NET...

  • Combine/Compress/Minify JS and CSS files in ASP.NET MVC How to Export Data to Excel from an ASP.NET Application

  • Nicely written. Thanks for the article.

  • Hi,

      My DataSet contains value 002 whenever i download in DataSet to Excel. It shows only 2 but i need the following format 002 in Excel download file(Using ASP.Net-05, C#).

      Is it possible. Please help me.

  • Nice article. Thanks for sharing it. Here's another very easy way how to export data to excel:

    http://www.gemboxsoftware.com/GBSpreadsheet.htm

    I've used this Gemox component for a while an it's really great component.

  • Great, but can we export 2 tables(gridviews) in to 1 excel file to 2 sheets?

  • Your code produces the following error:

    RegisterForEventValidation can only be called during Render();

  • Hi,

    Nice article. Thanks. I do have a question on this. In our application we want users to be able to download a list of securities, their price dates and price (empty when downloading) in excel which I am able to do using grid view method above. But then the users are suppose to enter price of each row and upload it back to server where we insert them into DB. The problem is when we type a few prices and try to upload the file, it breaks saying invalid file format. We are using Jet oledb 4.0 to read the the uploaded excel file. The upload and database insert code works if I manually create an excel and type in the security, price date and price.

    Any help would be appreciated.

  • i wann to know how to insert the image in to the excel sheet once i click the button .............

  • Hi,

    when exported to excel, it shows a pop-up with an 'Open' and 'Save' option. Click open - this opens the excel sheet with the content. Go to Files - 'Save as' option - you get to see 'Save as type' defaulted to

    'Web Page(*.htm; *.html)'

    How do we change the Default File Type to Excel?

    Thanks in advance...

  • What is the purpose of

    this.EnableViewState = false;

    in the ExportToExcel() method?

  • Hi,

    Can an Excel file generated using the Open XML SDK 2.0 be sent to the client browser using 'Open/save' dialog box WITHOUT saving that file on the web server hard drive? (I create these files dynamically and aren't required on the server.)

    Thank you.

Page 1 of 4 (52 items) 1234