原文链接
30 Jan 2009 5:25 PM
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>
Blog Commentator IIBlog Party StarterBlogger III
- 大小: 83.7 KB
分享到:
相关推荐
How to- Migrate an ASP.NET 1.1 Web Page Using the CodeBehind Attribute to ASP.NET 2.0
开源框架完美组合之Spring.NET + NHibernate + ASP.NET MVC + jQuery + easyUI 中英文双语言小型企业网站Demo
It's only been a year since I wrote ASP.NET Core and Angular 2, but it definitely feels a lot more, at least from a web developer's perspective. Modern web technologies are still improving at ...
system, of a migration from ASP.NET to ASP.NET Core neatly organized in easy- to- follow steps. While the book won’t be an authoritative guide on ASP.NET Core (there are plenty of excellent books ...
To make your ASP.NET MVC applications the best they can be, you need to fully understand the platform features and know how they can be used to build effective and elegant MVC framework applications....
ASP.NET Web API 2 Recipes gives you an in-depth explanation for each of these scenarios and shows you how to use Web API with a vast array of .NET application development tools and external libraries...
A sample application used throughout the book is an enterprise level ASP.NET website with multi–tiered, SOA design techniques that can be applied to your future ASP.NET projects. Read about each ...
Contains the comprehensive guide to the latest technology additions to ASP.NET 4.5 Shows how to build basic ASP.NET web pages and configure their server Includes information on how to add features ...
How To Automate Excel to Create And Format a New Workbook Using C++ .NET and MFC
This one hundred page book focuses exclusively on how you can best use the ASP.NET MVC 4 Framework to build world-class REST services using the Web API. It sets aside much of what the ASP.NET MVC ...
In the following two sections, you learn how ASP.NET fits within the .NET framework, and you learn about the languages you can use in your ASP.NET pages. The .NET Framework Class Library Imagine that...
The book includes practical examples to show you how to use open source plugins with Bootstrap and ASP.NET MVC and guides you through building an ASP.NET MVC website using Bootstrap, utilizing layout...
How to create templated ASP.NET pages using the master page feature * How to work with data from enterprise databases including SQL Server® * Ways to debug, package, and deploy ASP.NET ...
* Discover the intricacies of ADO.NET and how to perform data binding to many sources, from databases to file streams to XML. * Learn the various forms of security available and how to best apply ...
Starting with an overview of the MVC pattern, you will quickly dive into the aspects that you need to know to get started with ASP.NET. You will learn about the core architecture of model, view, and ...
The book includes practical examples to show you how to use open source plugins with Bootstrap and ASP.NET MVC and will guide you through building an ASP.NET MVC website using Bootstrap, utilizing ...
This edition is updated with everything you need to master up to version 4 of ASP.NET, including coverage of ASP.NET MVC, ASP.NET AJAX 4, ASP.NET Dynamic Data, and Silverlight 3. Seasoned .NET ...
VB is aimed at beginner ASP.NET developers wanting to develop their skills, web designers wanting to move into server-side programming, and experienced developers making the leap from ASP to .NET. ...
The Model-View-Controller pattern—universally known as MVC—provides a stable, testable approach to web application development by separating the major functions—or concerns—of an application into ...
Starting with an overview of the MVC pattern, we quickly dive into the aspects that you need to know to get started with ASP.NET. You will learn about the core architecture of model, view, and ...