【经验分享】将数据导出为Excel、Html、Word

本方法使用模板页的方式导出数据集。

优点：自定义样式、在排版风格上灵活

缺点：需要定义模板

思路：在模板上打上标识，用数据集替换标识。生成新的文件。

主要程序代码：

1.引用文件操作相关的程序集

using System.IO;
using System.Text;

2.获取数据集，替换模板，生成输出文件

    #region 导出客户资料
        protected void btnExport_Click(object sender, EventArgs e)
        { 
            //取得数据
            Aiwei.DAL.Supplier dalSupplier = new Aiwei.DAL.Supplier();
            DataSet dsSupplier = new DataSet();
            dsSupplier = dalSupplier.GetExportList("");
            DataTable dtExportItemLy = dsSupplier.Tables[0];

            if (dtExportItemLy == null) return;
            if (dtExportItemLy.Rows.Count <= 0)
            {
                AlertShow(this.Page, "暂无数据可以导出!");
                return;
            }

            string m_Target = "";
            string m_Source = "";
            string m_Temp = "";
            string m_content = "";
            int m_start = 0, m_end = 0;

            string[] format = new string[13];//定义和htmlTemplate 标记数目一致的数组 

            try
            {
                using (StreamReader sr = new StreamReader(Server.MapPath("ExcepTemplate/UserTemplete.htm")))
                {
                    string line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        m_Source += line;
                    }
                    sr.Close();
                }
            }
            catch (System.Exception ex)
            {
                string strErr = ex.Message;
                AlertShow(this.Page, "读取文件错误！");
                return;
            }

            m_start = m_Source.IndexOf("<!--content start-->");
            m_end = m_Source.IndexOf("<!--content end-->");
            m_content = m_Source.Substring(m_start, m_end - m_start);
            m_Source = m_Source.Replace(m_content, "<!--tempitem-->");
            m_content = m_content.Replace("<!--content start-->", "");
            m_content = m_content.Replace("<!--content end-->", "");


            //---------------------给标记字段赋值------------ 

            for (int i = 0; i < dtExportItemLy.Rows.Count; i++)
            {
                format[0] = dtExportItemLy.Rows[i]["SupplierID"].ToString();//客户编号
                format[1] = dtExportItemLy.Rows[i]["SupplierName"].ToString();    //客户名称
                format[2] = dtExportItemLy.Rows[i]["Address"].ToString();        //地址
                format[3] = dtExportItemLy.Rows[i]["PostCode"].ToString();//邮政编码
                format[4] = dtExportItemLy.Rows[i]["HomePage"].ToString();//主页
                format[5] = dtExportItemLy.Rows[i]["ArtiPerson"].ToString();//负责人
                format[6] = dtExportItemLy.Rows[i]["ContactMan"].ToString();//联系人
                format[7] = dtExportItemLy.Rows[i]["ContactMail"].ToString();//电子邮箱
                format[8] = dtExportItemLy.Rows[i]["Remark"].ToString();        //备注
                format[9] = dtExportItemLy.Rows[i]["CreateTime"].ToString();//创建时间
                format[10] = dtExportItemLy.Rows[i]["LastModifyTime"].ToString();//最后修改时间
                format[11] = dtExportItemLy.Rows[i]["ContractWay"].ToString();//联系电话

                //----------替换htm里的标记为你想加的内容 
                m_Temp = m_content;
                for (int j = 0; j < 12; j++)
                {
                    m_Temp = m_Temp.Replace("$htmlformat[" + j + "]", format[j]);
                }
                m_Target += m_Temp;
            }

            m_Target = m_Source.Replace("<!--tempitem-->", m_Target);

            m_Target = m_Target.Replace("$htmlformat[12]", "客户总数: " + dtExportItemLy.Rows.Count + "位");

            //----------生成htm文件------------------
            try
            {
                using (StreamWriter sw = new StreamWriter(Server.MapPath("ExcepTemplate/UserInfo.htm"), false, System.Text.Encoding.GetEncoding("GB2312")))
                {
                    sw.WriteLine(m_Target);
                    sw.Flush();
                    sw.Close();
                }

                System.IO.FileStream fs = new FileStream(Server.MapPath("ExcepTemplate/UserInfo.htm"), FileMode.Open, FileAccess.Read);
                byte[] buf = new byte[Convert.ToInt32(fs.Length)];
                fs.Read(buf, 0, Convert.ToInt32(fs.Length));
                fs.Close();
                File.Delete(Server.MapPath("ExcepTemplate/UserInfo.htm"));

                Response.Clear();
                Response.ClearHeaders();
                Response.Buffer = true;

                string strFormat = "excel";
                switch (strFormat)
                {
                    case "html":
                        Response.AddHeader("Content-Disposition", "attachment; filename= File.htm");
                        //Response.ContentType = "application/ms-html"; 
                        Response.ContentType = "application/octet-stream";

                        break;
                    case "word":
                        Response.AddHeader("Content-Disposition", "attachment; filename= File.doc");
                        //Response.ContentType = "application/msword"; 
                        Response.ContentType = "application/octet-stream";
                        break;
                    case "excel":
                        dtExportItemLy.Columns[0].ColumnName = "用户编号";
                        dtExportItemLy.Columns[1].ColumnName = "用户名称";
                        dtExportItemLy.Columns[2].ColumnName = "地址";
                        dtExportItemLy.Columns[3].ColumnName = "邮政编码";
                        dtExportItemLy.Columns[4].ColumnName = "主页";
                        dtExportItemLy.Columns[5].ColumnName = "负责人";
                        dtExportItemLy.Columns[6].ColumnName = "联系人";
                        dtExportItemLy.Columns[7].ColumnName = "电子邮箱";
                        dtExportItemLy.Columns[8].ColumnName = "联系电话";
                        dtExportItemLy.Columns[9].ColumnName = "添加时间";
                        dtExportItemLy.Columns[10].ColumnName = "最后修改时间";
                        dtExportItemLy.Columns[11].ColumnName = "备注";

                        this.CreateExcel(dtExportItemLy, "UserInfo");
                        break;
                    default:
                        AlertShow(this.Page,"目前未支持该类型！");
                        break;
                }

                Response.Buffer = true;
                Response.Clear();
                Response.AddHeader("Content-Length", buf.Length.ToString());
                Response.BinaryWrite(buf);
                Response.Flush();
                AlertShow(this.Page,"导出成功！");
            }
            catch(Exception ex)
            {
                AlertShow(this.Page, ""+ex.Message+"导出失败！");
                return;
            }
            finally
            {
                Response.End();
            }
        }

        public void CreateExcel(DataTable dt, string FileName)
        {
            System.Web.UI.WebControls.DataGrid m_Datagrid = new DataGrid();
            m_Datagrid.DataSource = dt;
            m_Datagrid.DataBind();

            Response.Buffer = false;
            Response.Charset = "gb2312";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
            //Response.ContentType = "application/ms-excel";  
            Response.ContentType = "application/octet-stream";
            this.EnableViewState = false;
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            m_Datagrid.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            AlertShow(this.Page, "导出成功！");
            Response.End();
        }
        #endregion

3.模板文件的定制UserTemplete.htm

<!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>
    <title>用户资料信息</title>
    <meta content="text/html; charset=gb2312" http-equiv="Content-Type">
    <style type="text/css"> <!-- body { margin-left: 0px; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; background-color: #FDFDEE; }
    td{ font-size:12px; line-height:normal; color:#5e5e5e; text-decoration: none; background-color: #FFFFFB; }
    .border_1 { border: 1px solid #9FAB7A; }
    .zi1 { font-size: 15px; text-decoration: none; color: #798554; font-weight: bold; }
    --></style>
</head>
<body bgcolor="#ffffff">
    <table>
        <tr>
            <td height="2">
            </td>
        </tr>
    </table>
    <table align="center" border="0" cellpadding="0" cellspacing="0" width="95%">
        <tr>
            <th scope="col">
                <span class="zi1">用户资料信息</span></th>
        </tr>
    </table>
    <table>
        <tr>
            <td height="2">
            </td>
        </tr>
    </table>
    <table align="center" border="0" cellpadding="0" cellspacing="0" width="95%">
        <tr>
            <th align="right" scope="col">
                <font size="2"><strong>$htmlformat[12]</strong></font></th>
        </tr>
    </table>
    <table id="Table2" align="center" bgcolor="#9fab7a" border="0" cellpadding="0" cellspacing="1"
        class="tabl" width="95%">
        <tr align="center">
            <td>
                <strong>用户编号</strong></td>
            <td>
                <strong>用户名称</strong></td>
            <td>
                <strong>地址</strong></td>
            <td>
                <strong>邮政编码</strong></td>
            <td>
                <strong>主页</strong></td>
            <td>
                <strong>负责人</strong></td>
            <td>
                <strong>联系人</strong></td>
            <td>
                <strong>电子邮箱</strong></td>
            <td>
                <strong>联系电话</strong></td>
            <td>
                <strong>添加时间</strong></td>
            <td>
                <strong>最后修改时间</strong></td>
            <td>
                <strong>备注</strong></td>
        </tr>
        <!--content start-->
        <tr>
            <td align="center">
                $htmlformat[0]</td>
            <td align="center">
                $htmlformat[1]</td>
            <td align="left">
                $htmlformat[2]</td>
            <td align="center">
                $htmlformat[3]</td>
            <td align="left">
                $htmlformat[4]</td>
            <td align="center">
                $htmlformat[5]</td>
            <td align="center">
                $htmlformat[6]</td>
            <td align="left">
                $htmlformat[7]</td>
            <td align="left">
                $htmlformat[8]</td>
            <td align="left">
                $htmlformat[9]</td>
            <td align="left">
                $htmlformat[10]</td>
            <td align="left">
                $htmlformat[11]</td>
        </tr>
        <!--content end-->
    </table>
</body>
</html>

 另外需要注意一下，因为会读取模板，所以要给模板文件相应的权限。