.net把html页面表格转换成excel文件方法

作者：袖梨 2022-06-25

.net把html页面表格转换成excel文件方法

一步:在页面前台加上如下代码

打  印第二步:   新建PmsAccidentStatisticsReportExcel.aspx文件.这个页面用来输出Excel文件.所以这个页面根据传过来的参数得出全部所需要下载的数据

PmsAccidentStatisticsReportExcel.aspx前台代码 (主要是建一个Table其中的每个TD用<%=_formyear%>绑定后如的string字段,用Label控件不行)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PmsAccidentStatisticsReportExcel.aspx.cs"
    Inherits="Web_PmsReport_Report_PmsAccidentStatisticsReportExcel" %>



    青岛市交通报表信息
   
   
   
   


   


       
           
               
           
       

                   
                       
                           
                       
                       
                           
                           
                       
                       
                           
                           
                       
                       
                           
                           
                       
                       
                           
                           
                       
                       
                           
                           
                           
                           
                       
                       
                           
                           
                           
                       
                       
                           
                           
                           
                           
                           
                           
                       
                       
                           
                           
                           
                           
                           
                           
                           
                       
                       
                           
                           
                           
                           
                           
                           
                           
                       
                       
                           
                           
                           
                           
                           
                           
                           
                       
                       
                           
                           
                           
                           
                           
                           
                           
                       
                       
                           
                       
                   

                               

道路运输行业行车事故统计表


                           

                                
                           
                                表    号：<%=_FormNo%>
                           

                                
                           
                                制表机关：<%=_MadeDep%>
                           

                                
                           
                                批准机关：<%=_CheckDep%>
                           

                                
                           
                                批准文号：<%=_CheckNo%>
                           

                                填报单位
                           
                                  <%=_SendUnit%>
                           
                               
                                    <%=_FormYear1%>
                               
                           
                                有效期至：<%=_FormY%>
                           

                                  指标
                           
                                  事故次数(次)
                           
                                  死伤人数(人)
                           

                                  合计
                           
                                  一次造成死亡10人以上
                           
                                  一次造成死亡3至9人
                           
                                  一次造成死亡1至2人
                           
                                  死亡
                           
                                  受伤
                           

                                  本月合计
                           
                                <%=_label1%>
                           
                                <%=_label2%>
                           
                                <%=_label3%>
                           
                                <%=_label4%>
                           
                                <%=_label5%>
                           
                                <%=_label6%>
                           

                                  客车
                           
                                <%=_label7%>
                           
                                <%=_label8%>
                           
                                <%=_label9%>
                           
                                <%=_label10%>
                           
                                <%=_label11%>
                           
                                <%=_label12%>
                           

                                  货车
                           
                                <%=_label13%>
                           
                                <%=_label14%>
                           
                                <%=_label15%>
                           
                                <%=_label16%>
                           
                                <%=_label17%>
                           
                                <%=_label18%>
                           

                                 危险化学品

                                 运输车辆
                           
                                <%=_label19%>
                           
                                <%=_label20%>
                           
                                <%=_label21%>
                           
                                <%=_label22%>
                           
                                <%=_label23%>
                           
                                <%=_label24%>
                           

                                 单位负责人：
                                <%=_UnitManager%>
                                 统计负责人：
                                <%=_StarUser%>
                                 填表人：<%=_FillUser%>
                                 联系电话：<%=_FormPhone%>
                                 报出日期：<%=_FormDate%>
                           

               

       

   


第三步 在PmsAccidentStatisticsReportExcel.aspx后台cs文件里对以上各绑定的string字段定义和赋值再加上如下代码

        Response.AddHeader("Content-Disposition", "attachment; filename=ConsistencyAppraiseReportExcel.xls");        Response.Charset = "gb2312";        Response.ContentType = "application/msexcel";完整的代码如下

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 Bms.Common.Util;

//业务声明
using Pms.PmsReport.BLL;
using Pms.PmsReport.Model;

//使用泛型
using System.Collections.Generic;

public partial class Web_PmsReport_Report_PmsAccidentStatisticsReportExcel : System.Web.UI.Page
{
    private PmsReportBll1 _bll = new PmsReportBll1();
    private string _flag;
    private DataTable dt1 = new DataTable();
    private DataTable dt2 = new DataTable();
    private DataTable dt3 = new DataTable();
    private DataTable dt4 = new DataTable();
    private DataTable dt5 = new DataTable();
    protected string _label1="0";
    protected string _label2="0";
    protected string _label3 = "0";
    protected string _label4 = "0";
    protected string _label5 = "0";
    protected string _label6 = "0";
    protected string _label7 = "0";
    protected string _label8 = "0";
    protected string _label9 = "0";
    protected string _label10 = "0";
    protected string _label11 = "0";
    protected string _label12 = "0";
    protected string _label13 = "0";
    protected string _label14 = "0";
    protected string _label15 = "0";
    protected string _label16 = "0";
    protected string _label17 = "0";
    protected string _label18 = "0";
    protected string _label19 = "0";
    protected string _label20 = "0";
    protected string _label21 = "0";
    protected string _label22 = "0";
    protected string _label23 = "0";
    protected string _label24 = "0";
    protected string _FormNo;
    protected string _MadeDep;
    protected string _CheckDep;
    protected string _CheckNo;
    protected string _SendUnit;
    protected string _FormYear1;
    protected string _FormY;
    protected string _UnitManager;
    protected string _StarUser;
    protected string _FillUser;
    protected string _FormPhone;
    protected string _FormDate;
    int m = 0;
    int n = 0;
    int k = 0;
    List arryLabel = new List();
    protected void Page_Load(object sender, EventArgs e)
    {

        _FormYear1 = Request.Params["formyear"].ToString().Substring(0, 4) + "年" + Request.Params["formyear"].ToString().Substring(5, 2)+"月";
        dt1 = _bll.SelectMonth(Request.Params["formyear"].ToString(), "大型客车");
        dt2 = _bll.SelectMonth(Request.Params["formyear"].ToString(), "中型客车");
        dt3 = _bll.SelectMonth(Request.Params["formyear"].ToString(), "乘用车");
        dt4 = _bll.SelectMonth(Request.Params["formyear"].ToString(), "货车");
        dt5 = _bll.SelectMonth(Request.Params["formyear"].ToString(), "危险品运输车");
        //Id.Text = _bll.IsExist("Allpms19", Request.Params["formyear"].ToString());

 
        DataTable dt = _bll.SelectCountModel(Request.Params["formyear"].ToString());
        _FormNo = dt.Rows[0]["FormNo"].ToString();
        _MadeDep = dt.Rows[0]["MadeDep"].ToString();
        _CheckDep = dt.Rows[0]["CheckDep"].ToString();
        _CheckNo = dt.Rows[0]["CheckNo"].ToString();
        _SendUnit = dt.Rows[0]["SendUnit"].ToString();   
        _FormY = dt.Rows[0]["FormY"].ToString();
        _UnitManager = dt.Rows[0]["UnitManager"].ToString();
        _StarUser = dt.Rows[0]["StarUser"].ToString();
        _FillUser = dt.Rows[0]["FillUser"].ToString();
        _FormPhone = dt.Rows[0]["FormPhone"].ToString();
        _FormDate = dt.Rows[0]["FormDate"].ToString();
     
        //计算事故死亡人数大于10人的事故次数
        arryLabel.Add(_label8);
        arryLabel.Add(_label14);
        arryLabel.Add(_label20);
        Count(arryLabel, "max");
        _label8 = arryLabel[0];
        _label14 = arryLabel[1];
        _label20 = arryLabel[2];
        arryLabel.Clear();
        _label2 = Convert.ToString(Convert.ToInt32(_label8) + Convert.ToInt32(_label14) + Convert.ToInt32(_label20));
        //计算事故死亡人数在3人到9人之间的事故次数
        arryLabel.Add(_label9);
        arryLabel.Add(_label15);
        arryLabel.Add(_label21);
        Count(arryLabel, "middle");
        _label9 = arryLabel[0];
        _label15 = arryLabel[1];
        _label21 = arryLabel[2];
        arryLabel.Clear();
        _label3 = Convert.ToString(Convert.ToInt32(_label9) + Convert.ToInt32(_label15) + Convert.ToInt32(_label21));
        //计算事故死亡人数小于3人的事故次数
        arryLabel.Add(_label10);
        arryLabel.Add(_label16);
        arryLabel.Add(_label22);
        Count(arryLabel, "min");
        _label10 = arryLabel[0];
        _label16 = arryLabel[1];
        _label22 = arryLabel[2];
        arryLabel.Clear();
        _label4 = Convert.ToString(Convert.ToInt32(_label10) + Convert.ToInt32(_label16) + Convert.ToInt32(_label22));
        //计算事故死亡人数
        arryLabel.Add(_label11);
        arryLabel.Add(_label17);
        arryLabel.Add(_label23);
        Count(arryLabel, "deadtotal");
        _label11 = arryLabel[0];
        _label17 = arryLabel[1];
        _label23 = arryLabel[2];
        arryLabel.Clear();
        _label5 = Convert.ToString(Convert.ToInt32(_label11) + Convert.ToInt32(_label17) + Convert.ToInt32(_label23));
        //计算事故受伤人数
        arryLabel.Add(_label12);
        arryLabel.Add(_label18);
        arryLabel.Add(_label24);
        Count(arryLabel, "hurttotal");
        _label12 = arryLabel[0];
        _label18 = arryLabel[1];
        _label24 = arryLabel[2];
        arryLabel.Clear();
        _label6 = Convert.ToString(Convert.ToInt32(_label12) + Convert.ToInt32(_label18) + Convert.ToInt32(_label24));
        //各行的统计
        _label1 = Convert.ToString(Convert.ToInt32(_label2) + Convert.ToInt32(_label3) + Convert.ToInt32(_label4));
        _label7 = Convert.ToString(Convert.ToInt32(_label8) + Convert.ToInt32(_label9) + Convert.ToInt32(_label10));
        _label13 = Convert.ToString(Convert.ToInt32(_label14) + Convert.ToInt32(_label15) + Convert.ToInt32(_label16));
        _label19 = Convert.ToString(Convert.ToInt32(_label20) + Convert.ToInt32(_label21) + Convert.ToInt32(_label22));
        Response.AddHeader("Content-Disposition", "attachment; filename=ConsistencyAppraiseReportExcel.xls");
        Response.Charset = "gb2312";
        Response.ContentType = "application/msexcel";
    }
    private void Count(List arryLabel, string val)
    {
        if (dt1.Rows.Count > 0 && dt1.Rows[0][val].ToString() != "")
        {
            m = Convert.ToInt32(dt1.Rows[0][val].ToString());
        }
        if (dt2.Rows.Count > 0 && dt2.Rows[0][val].ToString() != "")
        {
            n = Convert.ToInt32(dt2.Rows[0][val].ToString());
        }
        if (dt3.Rows.Count > 0 && dt3.Rows[0][val].ToString() != "")
        {
            k = Convert.ToInt32(dt3.Rows[0][val].ToString());
        }
        arryLabel[0] = Convert.ToString(m + n + k);
        if (dt4.Rows.Count > 0 && dt4.Rows[0][val].ToString() != "")
        {
            arryLabel[1] = dt4.Rows[0][val].ToString();
        }
        if (dt5.Rows.Count > 0 && dt5.Rows[0][val].ToString() != "")
        {
            arryLabel[2] = dt5.Rows[0][val].ToString();
        }
    }
}

相关文章

精彩推荐