ASP.NET通过流方式导出EXCEL并且单元格换行

时间：2010-09-08 来源：云天

关键是EXCEL单元格换行，折腾了好久，终于出来了，呵呵，做个记录

.cs源代码

代码 using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ExportExcel
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.GridView1.DataSource = CreateDataSource();
                this.GridView1.DataBind();
            }
        }

        public override void VerifyRenderingInServerForm(Control control)
        { }

        protected void Button1_Click(object sender, EventArgs e)
        {
            //设置文件名
            string fileName = DateTime.Now.ToString("yyyyMMddHHmmss");

            //HTTP请求内容
            System.Web.HttpContext curContext = System.Web.HttpContext.Current;

            // 输出文件流
            System.IO.StringWriter strWriter = null;

            //服务端控件输出流
            System.Web.UI.HtmlTextWriter htmlWriter = null;

            // 清空当前 Response
            HttpContext.Current.Response.Clear();

            //设置缓冲方式输出
            HttpContext.Current.Response.Buffer = true;

            //添加HTTP标头到输出流
            HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xls", fileName));
            curContext.Response.ContentType = "application/vnd.ms-excel"; //设置输出文件类型为xls文件

            /**如果要输出doc文件,请用下面这段代码**/
            //HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.doc", fileName));
            //curContext.Response.ContentType = "application/ms-word"; //设置输出文件类型为word文件

            //设置编码方式
            curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            curContext.Response.Charset = "";

            // 导出文件
            strWriter = new System.IO.StringWriter();
            htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

            htmlWriter.Write("测试标题1");

            // 返回客户端
            GridView1.RenderControl(htmlWriter);

            //设置换行  <br/>是html的换行样式替换成<br style='mso-data-placement:same-cell;'/> EXCEL换行
            curContext.Response.Write(strWriter.ToString().Replace("<br/>", "<br style='mso-data-placement:same-cell;'/> "));

            curContext.Response.End();
        }

        System.Data.DataView CreateDataSource()
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            System.Data.DataRow dr;
            dt.Columns.Add(new System.Data.DataColumn("测试1"));
            dt.Columns.Add(new System.Data.DataColumn("测试2"));
            dt.Columns.Add(new System.Data.DataColumn("测试3"));
            dt.Columns.Add(new System.Data.DataColumn("测试4"));
            dt.Columns.Add(new System.Data.DataColumn("测试5"));
            dt.Columns.Add(new System.Data.DataColumn("测试6"));

            for (int i = 0; i < 5; i++)
            {
                System.Random rd = new System.Random(Environment.TickCount * i); ;
                dr = dt.NewRow();
                dr[0] = "编号" + i.ToString();
                dr[1] = "换行测试：" + i.ToString() + "<br/>" + "第二行开始" + i.ToString() + "<br/>" + "第三行开始" + i.ToString() + "<br/>" + "第四行开始" + i.ToString() + "<br/>" + "第五行开始" + i.ToString();
                dr[2] = "测试:" + i.ToString();
                dr[3] = "测试:" + i.ToString();
                dr[4] = "测试:" + i.ToString();
                dr[5] = "测试:" + i.ToString();
                dt.Rows.Add(dr);
            }

            System.Data.DataView dv = new System.Data.DataView(dt);
            return dv;
        }
    }
}

.aspx源

代码 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportExcel._Default" %>

<!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" AutoGenerateColumns="false" >
    <Columns>
                <asp:BoundField HtmlEncode="false" DataField="测试1" HeaderText="测试字段" />
                <asp:BoundField HtmlEncode="false" DataField="测试2" HeaderText="测试字段" />
                <asp:BoundField HtmlEncode="false" DataField="测试3" HeaderText="测试字段" />
                <asp:BoundField HtmlEncode="false" DataField="测试4" HeaderText="测试字段" />
                <asp:BoundField HtmlEncode="false" DataField="测试5" HeaderText="测试字段" />
                <asp:BoundField HtmlEncode="false" DataField="测试6" HeaderText="测试字段" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="导出" />
        <br />
    </div>
    </form>
</body>
</html>

用VS2008写的DEMO:/Files/tqlin/ExportExcel.rar