注意excel里格式的问题,如0开头的会自动去掉,长数字会用科学记数法表示(不管你的数据是什么类型的)。解决的方法代码里都包含了。
.aspx:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ToExcel.aspx.cs" Inherits="Excel_ToExcel" %>
<!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 runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound"
Width="233px">
</asp:GridView>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="ExportToExcel" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:connectionString %>"
SelectCommand="SELECT * FROM [test]"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
.cs:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
public partial class Excel_ToExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
public override void VerifyRenderingInServerForm(Control control)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
string style = @"<style> .text { mso-number-format:\@; } </script> ";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
this.GridView1.RenderControl(htw);
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(style);
Response.Write(sw);
Response.End();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Cells[1].Attributes.Add("class", "text");
}
}
}
如果导出的文件将会出现乱码,试着将Response.ContentEncoding = System.Text.Encoding.UTF7;
或Encoding.UTF8。