设计能够导出所有记录到excel的分页（使用AspNetPager）页面
1、新建页面ShowAllWithExcel.aspx，把ShowWithExcel.aspx的页面和背后代码都复制过去， 注意：页面的首行不复制，因为有代码文件的指示；
页面主要包括：一个Gridview、一个Button按钮、一个AspNetPager
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowAllWithExcel.aspx.cs" Inherits="Reportxyq_ShowAllWithExcel" %>
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
<!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 id="Head1" runat="server">
<title>showxAspNetPager</title>
<meta http-equiv="Pragma" content="no-cache" />
<meta http-equiv="Cache-Control" content="no-cache" />
<meta http-equiv="Expires" content="0" />
<link href="../Style/css.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="../js/UART.js"></script> <!-- UART:Universal Asynchronous Receiver/Transmitter，通用异步接收/发送装置 -->
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td height="30" background="../Images/tab_05.gif">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="12" height="30">
<img src="../Images/tab_03.gif" width="12" height="30" />
</td>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="46%" valign="middle">
<table border="0" cellspacing="0" cellpadding="0" style="width: 100%">
<tr>
<td>
<div align="center">
<img src="../images/tb.gif" width="16" height="16" style="float: left" />
</div>
</td>
<td width="95%" style="text-align: left"><span>
<asp:Button ID="Button1" runat="server" οnclick="Button1_Click" Text="导出所有记录到Excel" />
</span>
<span class="titleBold12px">ShowAllWithExcel.aspx</span>
</td>
</tr>
</table>
</td>
<td width="54%" align="right">
</td>
</tr>
</table>
</td>
<td width="16" height="30">
<img src="../Images/tab_07.gif" width="16" height="30" />
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="8" background="../images/tab_12.gif">
</td>
<td align="center">
<!-- 放入进度显示的地方 <br /> -->
<asp:GridView ID="GridView1" runat="server">
<HeaderStyle Wrap="False" />
<RowStyle Wrap="False" />
</asp:GridView>
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" FirstPageText="首页" LastPageText="尾页"
NextPageText="下一页" PrevPageText="上一页"
OnPageChanged="AspNetPager1_PageChanged" >
</webdiyer:AspNetPager>
</td>
<td width="8" background="../images/tab_15.gif">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td height="35" background="../images/tab_19.gif">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="12" height="35">
<img src="../images/tab_18.gif" width="12" height="35" />
</td>
<td align="center">
<!-- 这个表格的行是放了几个图片，用作页面的底部 -->
</td>
<td width="16">
<img src="../images/tab_20.gif" width="16" height="35" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
2、背后的代码，主要是数据源从gridview改为使用dataset变换成DataTable，然后逐行逐条写写入excel。（已解决excel文件名中文乱码问题，解决方法见下）
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;
public partial class Reportxyq_ShowAllWithExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
//调用绑定分页和GridView
BindGridView();
}
}
//DataSet创建函数
private DataSet CreatDataSet() {
//获取数据表格
String sqlconn = "Server=.; DataBase=zongdiao; Integrated Security=SSPI ";
//string sql = "select top 500 [部门],[月度],[日期],[催装次数],[催装分母数],[催装率(≤3%)],[掌调使用次数] from gk_kpi where 部门='接入维护中心'";
string sql = "select top 500 * from gk_kpi where 部门='接入维护中心'";
SqlConnection myConnection = new SqlConnection(sqlconn);// 创建数据库连接实例
myConnection.Open(); //打开数据库
SqlDataAdapter myda = new SqlDataAdapter(sql, sqlconn);//创建一个sql数据适配器
DataSet myDs = new DataSet(); //创建数据集实例
myda.Fill(myDs);
return myDs;
}
用DataSet作为PagedDataSource的数据源，再用PagedDataSource作为GridView的数据源
private void BindGridView()
{
//用自定义的函数创建DataSet
DataSet myDs = CreatDataSet();
//初始化分页数据源实例
PagedDataSource pds = new PagedDataSource();
//设置总行数
AspNetPager1.RecordCount = myDs.Tables[0].Rows.Count;
//设置分页的数据源
pds.DataSource = myDs.Tables[0].DefaultView;
//设置当前页
pds.CurrentPageIndex = AspNetPager1.CurrentPageIndex - 1;
//设置每页显示页数
pds.PageSize = AspNetPager1.PageSize;
//启用分页
pds.AllowPaging = true;
//设置GridView的数据源为分页数据源
GridView1.DataSource = pds;
//绑定GridView
GridView1.DataBind();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
//调用绑定分页和GridView
BindGridView();
}
//DataSet导成Excel的方法
private void DataSetToExcel(string FileName)
{
//创建DataSet
DataSet ds = CreatDataSet();
//解决文件名乱码
FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
HttpResponse resp;
resp = HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-disposition", "attachment;filename=" + FileName + ".xls");
resp.ContentType = "application/octet-stream";//默认
//resp.ContentType = "application/x-xls";
//变量定义
string colHeaders = null;
string Is_item = null;
StringWriter sfw = new StringWriter(); //需要：using System.IO;
//定义表对象与行对象，同时用DataSet对其值进行初始化
System.Data.DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
//if(i==(cl-1)) //最后一列，加\n
// colHeaders+=dt.Columns[i].Caption.ToString();
//else
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
}
sfw.WriteLine(colHeaders);
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前数据写入
for (i = 0; i < cl; i++)
{
//if(i==(cl-1))
// Is_item+=row[i].ToString()+"\n";
//else
Is_item += row[i].ToString() + "\t";
}
sfw.WriteLine(Is_item);
//sw.WriteLine(Is_item);
Is_item = null;
}
resp.Write(sfw);
resp.End();
}
//点击事件，生成excel
protected void Button1_Click(object sender, EventArgs e)
{
DataSetToExcel("测试的cxcel");
}
}
完成后测试OK，满足分页和全部记录导出到excel的目的，但是excel的中文文件名乱码，解决方案：
//解决文件名乱码，重命名文件名
FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
1161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
