• 将数据导出为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 == nullreturn;
                
    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>

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

  • 相关阅读:
    XML 约束
    XML 高级
    XML 基础
    XML系列【目录】
    Java11 新特性
    Java10 新特性
    Java9 新特性 (二)语法改进
    Java9 新特性 (一)新增特性
    第一章:Class 文件结构
    java面试题全集(上)--java基础
  • 原文地址:https://www.cnblogs.com/luluping/p/1499557.html
Copyright © 2020-2023  润新知