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.



  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!




Comments (54)

  1. Thank you for submitting this cool story – Trackback from DotNetShoutout

  2. vinay says:

    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.

  3. Bolik says:

    Combine/Compress/MinifyJSandCSSfilesinASP.NETMVCHowtoExportDatatoExcelfromanASP.NET…

  4. Bolik says:

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

  5. Alankar says:

    Nicely written. Thanks for the article.

  6. Ganesh says:

    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.

  7. John says:

    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.

  8. Jack says:

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

  9. Jakob Flygare says:

    Your code produces the following error:

    RegisterForEventValidation can only be called during Render();

  10. Girish says:

    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.

  11. meghana says:

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

  12. vinarja says:

    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…

  13. Ali says:

    What is the purpose of

    this.EnableViewState = false;

    in the ExportToExcel() method?

  14. Saurabh says:

    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.

  15. Ashwani says:

    Hi,

    Thanks for this useful post. It is working fine when Ms-Office is installed but when Ms-Office is not installed it is asking for program to open this. Please reply to solve this problem.

    Thanks

  16. KK says:

    NICE ARTICLE KEEP IT IN UR ASS HOLE USE LESS FELLOW

  17. RAMYA says:

    NA MADDA KATLA VUNDI RA POOKA NICE

  18. Hengry says:

    No lah. It's still prompt even after i tested with you code!. Please help me out as i faced with same problem 'Prompt Message' in excel after export from grid view. Thanks

  19. Murtaza says:

    It's still shows the prompt message "The file you are trying to open in different format…..".Please help me about this.

  20. John says:

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

    http://www.devtriogroup.com/ExcelJetcell

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

  21. RalphZero says:

    Option 2 is the best for ASP.Net Web Apps.

    Options 1 and 3 are easy.  However, there are no formatting options and they don’t support multiple worksheets.

    Option 4 is the most powerful.  However, it requires saving files to the server, granting privileges to the Excel  Interop object and killing the Excel process on the server.  For me, this became a big mess.

    Option 2 allows for formatting and supports multiple worksheets.  However, it requires the creation and maintenance of an xslt file.

    The requirement was to display a dataset with 3 data tables in an Excel workbook and have each data table in its own worksheet.

    Option 2 worked perfectly.

  22. Power Ranger says:

    Is there a way to export to excel 2007 instead of doing registry edit to suppress the prompt?

  23. Power Ranger says:

    It's not good to ask users to do registry edits.

  24. phool Singh says:

    I want the file will save from a perticuler place. In short I don't want the pop up will show .The Excel will save in some place automatically.

    Can any 1 suggest me how to do that ?

    Awaiting for reply.

    Thanks

  25. aron says:

    yet another nice library to export to excel in nice formatted way: officehelper.codeplex.com

  26. Sarang says:

    my problem is i cannot export more than 0.5 mb of data to excel.i have used same code as above.

    Please reply if u have any solution.

  27. Deepesh says:

    Hi,

    Any idea how i can export just a simple DIV into excel?

  28. Zeeshan Umar says:

    nice post, simple and easy to understand

  29. Nicos says:

    for exporting data to excel from an ASP.NET

    Best way to using Spire.XLS, easy and fast.

    http://www.e-iceblue.com/…/excel-for-net-introduce.html

  30. Zeeshan Umar says:

    Thanks for sharing this, it is really useful for me, also have a look at this link <a href='zeeshanumardotnet.blogspot.com/…/creating-reports-in-excel-2007-using.html& Reports in Excel 2007 using EPPlus (Header, Footer, Comments, Image, Formatting, Shape and Formula)</a>

  31. khop says:

    I have a 15 digit number that automatically appears in an exponential formate when exported to excel from an ASP.NET app.  Is there a way to show the number in a text format or even a numerical format that isn't exponential?

  32. Siva says:

    Thanks for this article. I have requirement like. I have 4 gridview in a page. I want to export all of them in a excel workbook. Each grid should be exported in seperate sheet. How can I achieve this??

  33. Naomi Gledhill says:

    Alternatively, you can use the OpenXML libraries, and export to an Excel 2007 file.  

    The following page shows how to do it, for free, with full source code.  

    http://www.mikesknowledgebase.com/…/ExportToExcel.htm

    In answer to a question from Feb 2009, this library will create one Excel worksheet of data per DataTable in your DataSet.

    It even shows how to then open the Excel file, from your ASP.Net C# code.   Very cool.

  34. DrClaire says:

    Great article for outlining the various ways of Exporting to Excel.  I tried the sample code but got the following error when running:

    RegisterForEventValidation can only be called during Render();

    This can be solved by either setting the EnableEventValidation flag to FALSE in the Web.Config in the following way

    <pages enableEventValidation ="false"></pages>

    This will apply to all the pages in your website.

    Otherwise you can also set it in the @Page Directive of the page on which you are experiencing the above error.

    <%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation = "false"

    see http://www.aspsnippets.com/…/RegisterForEventValidation-can-only-be-called-during-Render.aspx

  35. Betu says:

    private void Export()

       {

           Response.Clear();

           Response.AddHeader("content-disposition", "attachment;filename=OrderList.xls");

           Response.Charset = "";

           Response.ContentType = "application/vnd.xls";

           StringWriter StringWriter = new System.IO.StringWriter();

           HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);

           gdLIst.RenderControl(HtmlTextWriter);

           Response.Write(StringWriter.ToString());

           Response.End();

       }

  36. Aron.Sinoai says:

    This is just light wrapper library around the Open XML SDK:

    officehelper.codeplex.com/documentation

  37. mchandradubey says:

    superb work  Erika Ehrli..fan of uu

  38. Shreya says:

    how to export complete div tag including charts.

  39. Rimi says:

    Good article for beginners. But there one problem. It does not write into ".xlsx" files, which are newer version. Try the below link for exporting data into all versions of excel.

    http://www.encodedna.com/…/asp.net-export-to-excel.htm

    Thank you

  40. subbu says:

    how to export to excel by using ASP.NET application?

  41. check here some more automations with asp.net excel

    asp.net-informations.com/…/asp-excel.htm

    gail

  42. Eurekha says:

    in my web application i need to export data to excel from datatable in a particular format

    this has been done

    i need to lock some cells range to prevent editing in excel sheet

    i tried  but it doesnot get locked

    ExcApp = New Excel.Application

    ExcWS = ExcApp.Worksheets("SHEET1")

    ExcWS.Range("A1", "C3").Locked = True

    ExcWS.Protect(False, False, False, False, False, False, False, False, False, False, False, False, False, False, False, False)

    i have above

    2) i need to display checkbox against each supplier no displayed in the sheet

    & when few of the suppliers are checked , need to import the checked data in the database

  43. Rohan says:

    This code produces excel sheet, but if we try to generate in Fire Fox browser, sheet not opened.

    Please give us suggestion.

  44. find this easy way of import  asp.net-informations.com/…/asp-excel-export.htm  asp.net excel export

    lev.

  45. Steward says:

    Export GridView data to excel from .NET applications,

    http://www.kettic.com/…/gridview_data_export.shtml

  46. doesn't work says:

    Getting this error :"RegisterForEventValidation can only be called during Render(); ". Im using vs 2008

  47. Mike Gledhill says:

    Check out my free C# library, which lets you export your DataSet, DataTable or List<> data directly into an Excel 2007 .xlsx, using OpenXML.

    http://www.mikesknowledgebase.com/…/ExportToExcel.htm

    For example, one line of code, and you can write a file directly to the HttpResponse:

    protected void btnExportToExcel_Click(object sender, EventArgs e)

    {

       // It doesn't get much easier than this…

       CreateExcelFile.CreateExcelDocument(listOfEmployees, "Employees.xlsx", Response);

    }

  48. ayesha says:

    very informative. thanks

    I have found another example for the same to excel refer to  http://www.etechpulse.com/…/exporting-div-dynamic-content-to-excel.html

  49. Duong Oriole says:

    Thanks for your article.:)

  50. jems says:

    what is gvFiles,

    in gvFiles.RenderControl(htw);

  51. Stephen says:

    Thanks for the good article. Keep up the good work.

  52. Jody says:

    The code here for exporting from grid view does not work for me.  I am getting HTML output into the excel file, rather than just column headings and data and Excel fails to open it.