.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>
<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";
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();
}
}
}
