.NET页面表格转成Excel文件

页面表格转成Excel文件(当我们点击图一的打印的时候会生成下载页面生成图二对应的Excel文件)

                                                                                    <图一>

                                                                       <图二>

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

<a class="button_2" onclick="javaScript:window.printReport()" style="cursor:hand">打 印</a>

<Script language=javascript>

var winIndex
= 0;

//打印
function printReport()
{

var width
= "600";
var height
= "400";
var urlVar
= "../Report/PmsAccidentStatisticsReportExcel.aspx?formyear=<%=_flag %>";
var top
= (screen.availHeight - height) / 2;
var left
= (screen.availWidth - width) / 2;
var feather
= "toolbar=no,location=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=" + width + "px,height=" + height
+ "px,top=" + top + ",left=" + left + ",titlebar=no";
var t
= window.open(urlVar, "pmsReport_Allpms19"+winIndex, feather);
t.focus();
winIndex
++;
}
</script>

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

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

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script language="javascript">
    window.resizeTo(10,10);
    window.close();
</script>

<html>
<head>
    <title>青岛市交通报表信息</title>
    <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
    <meta content="C#" name="CODE_LANGUAGE">
    <meta content="JavaScript" name="vs_defaultClientScript">
    <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</head>
<body>
    <form id="Form1" method="post" runat="server">
        <table cellspacing="0" cellpadding="0" border="1" width="98%" align="center">
            <tr>
                <td>
                    <table class="table" cellspacing="1" cellpadding="5" width="100%" border="1" style="font-size: 11px;">
                        <tr>
                            <td class="tr_biaoti2" colspan="7" style=" font-size: 14px; text-align: center">
                                <h3>道路运输行业行车事故统计表 </h3>
                            </td>
                        </tr>
                        <tr>
                            <td class="td_right" colspan='5' width='72%'>
                                 
                            </td>
                            <td class="td_left" colspan='2' width='28%'>
                                表    号:<%=_FormNo%> 
                            </td>
                        </tr>
                        <tr>
                            <td class="td_right" colspan='5'>
                                 
                            </td>
                            <td class="td_left" colspan='2'>
                                制表机关:<%=_MadeDep%> 
                            </td>
                        </tr>
                        <tr>
                            <td class="td_right" colspan='5'>
                                 
                            </td>
                            <td class="td_left" colspan='2'>
                                批准机关:<%=_CheckDep%> 
                            </td>
                        </tr>
                        <tr>
                            <td class="td_right" colspan='5'>
                                 
                            </td>
                            <td class="td_left" colspan='2'>
                                批准文号:<%=_CheckNo%> 
                            </td>
                        </tr>
                        <tr>
                            <td class="td_left" colspan='2' align="center">
                                填报单位
                            </td>
                            <td class="td_left" style="text-align: left" colspan='2'>
                                  <%=_SendUnit%>
                            </td>
                            <td class="td_left" colspan='1' align='center' style="text-align: center; height: 25px">
                                <b>
                                    <%=_FormYear1%>
                                </b>
                            </td>
                            <td class="td_left" colspan='2'>
                                有效期至:<%=_FormY%> 
                            </td>
                        </tr>
                        <tr>
                            <td class="td_left" style="text-align: center" rowspan='2'>
                                  指标
                            </td>
                            <td class="td_left" colspan='4' style="text-align: center">
                                  事故次数(次)
                            </td>
                            <td class="td_left" colspan='2' style="text-align: center">
                                  死伤人数(人)
                            </td>
                        </tr>
                        <tr>
                            <td class="td_left" style="text-align: center; height: 25px;" width='13%'>
                                  合计
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                  一次造成死亡10人以上
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                  一次造成死亡3至9人
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                  一次造成死亡1至2人
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                  死亡
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                  受伤
                            </td>
                        </tr>
                        <tr>
                            <td class="td_left" style="text-align: center; height: 27px;">
                                  本月合计
                            </td>
                            <td class="td_left" style="text-align: center; height: 27px;">
                                <%=_label1%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 27px;">
                                <%=_label2%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 27px;">
                                <%=_label3%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 27px;">
                                <%=_label4%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 27px;">
                                <%=_label5%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 27px;">
                                <%=_label6%>
                            </td>
                        </tr>
                        <tr>
                            <td class="td_left" style="text-align: center">
                                  客车
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label7%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label8%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label9%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label10%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label11%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label12%>
                            </td>
                        </tr>
                        <tr>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                  货车
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                <%=_label13%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                <%=_label14%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                <%=_label15%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                <%=_label16%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                <%=_label17%>
                            </td>
                            <td class="td_left" style="text-align: center; height: 25px;">
                                <%=_label18%>
                            </td>
                        </tr>
                        <tr>
                            <td class="td_left" style="text-align: center">
                                 危险化学品<br />
                                 运输车辆
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label19%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label20%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label21%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label22%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label23%>
                            </td>
                            <td class="td_left" style="text-align: center">
                                <%=_label24%>
                            </td>
                        </tr>
                        <tr>
                            <td class="td_right" colspan='7' width='100%' align="center" style="font-size: 11px;">
                                 单位负责人:
                                <%=_UnitManager%>
                                 统计负责人:
                                <%=_StarUser%>
                                 填表人:<%=_FillUser%>
                                 联系电话:<%=_FormPhone%>
                                 报出日期:<%=_FormDate%>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
        <br>
    </form>
</body>
</html>

 第三步 在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<string> arryLabel = new List<string>();
    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<string> 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();
        }
    }
}
posted @ 2011-06-28 15:29  隆中小屋  阅读(1452)  评论(1编辑  收藏  举报