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

作者：袖梨 2022-06-25

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

打印第二步: 新建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();
        }
    }
}

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

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

相关文章

精彩推荐