商业源码 ASP.NET用AspNetPager将table内容分页，客户端如何将的table下Repeater绑定的datable无分页的导出为excel
/// <summary>
/// 将datatable转换成excel提供下载
/// </summary>
/// <param name="dt">需要下载成excel的datatable</param>
public static void ToExcel(DataTable dt)
{
if (SystemWebHttpContextCurrent == null)
{
throw new Exception("只有web程序才能调用此方法。");
}
SystemIOStringWriter sw = new SystemIOStringWriter();
SystemTextStringBuilder sb = new SystemTextStringBuilder();
foreach (SystemDataDataColumn col in dtColumns)
{
sbAppend(colColumnName + "\t");
}
swWriteLine(sbToString());
int cols = dtColumnsCount;
foreach (SystemDataDataRow row in dtRows)
{
sbClear();
for (int c = 0; c < cols; c++)
{
sbAppend(row[c] + "\t");
}
swWriteLine(sbToString());
}
swClose();
string fileName = (stringIsNullOrEmpty(dtTableName) "data" : dtTableName) + "xls";
SystemWebHttpResponse response = SystemWebHttpContextCurrentResponse;
responseClear();
responseBuffer = true;
responseCharset = "gb2312";
responseAddHeader("Content-Disposition", "attachment;filename=" + fileName);
responseContentType = "application/ms-excel";
responseContentEncoding = SystemTextEncodingGetEncoding("gb2312");
responseWrite(sw);
responseEnd();
}
这种情况可以用ViewState。
ds刚读到值时就存到ViewState[""]中，添加时再更改ViewState。
grv的datasouce=ViewState[""]。
因为用户刷新页面，ViewState是保存原来的数据的，直到用户关闭页面。
ViewState详细用户自己上网搜搜
----------------------------
默认情况向，选择页时，GridView都会向服务器回发，所以会刷新的。
一般写个bind（）方法（我就是这样用的），去执行Gridview的绑定。gridview重新绑定数据原时，可以直接bind（)；
也可以用Viewstate：我写了个简单的
protected void Button1_Click(object sender, EventArgs e)
{
string str=ConfigurationManagerConnectionStrings["BlogDBConnectionString"]ConnectionString;
string sql = "select from Admin";
SqlConnection conn = new SqlConnection(str);
connOpen();
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
sdaFill(ds,"myTable");
ViewState["data"] = (DataTable)dsTables[0];
GridView1DataSource = (DataTable)ViewState["data"];
GridView1DataBind();
}
protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
GridView1PageIndex = eNewPageIndex;
GridView1DataSource = (DataTable)ViewState["data"];
GridView1DataBind();
}
我建议用bind（），如果数据很多的话，ViewState会很大的。
大致思路有两种
1在客户端传递页码到服务端 服务端根据页码在数据库中取出相应的数据记录
生成datase或通过我前面一片日志里面推荐的使用LoadTemplate方法生成string返回给客户端
然后客户端更新html即可，这里需要分页的sql语句，对于access 我参考了博客园李洪根推荐的select top语句
详细见他的blog
2对于小数据量的分页 如果你sql不熟 也可以将数据一次性读取到dataset
根据页码 计算当前页的记录编号 生成子dataset 返回同上的结果即可
这样做只涉及到简单的数学计算 利用datatable的rowscount很容易实现
附：datatable的复制
etc：
DataTable dt2=dtClone();
for(int i=0;i<dtRowsCount;i++)
{
dt2ImportRow(dtRows[i]);
}
第二种方法稍微简单 只讨论第一种方法
需要注意的是 我们在客户端传递页码
那么我们必须在客户端生成相应的分页代码
js代码参考
strHtml += '<span class="count">Pages: ' + thispage + ' / ' + thispageCount + '</span>';
strHtml += '<span class="number">';
if (prevPage < 1) {
strHtml += '<span title="First Page">«</span>';
strHtml += '<span title="Prev Page"></span>';
} else {
strHtml += '<span title="First Page"><a href="javascript:loadpage(1)">«</a></span>';
strHtml += '<span title="Prev Page"><a href="javascript:loadpage('+prevPage+')"></a></span>';
}
if (thispage % 10 ==0) {
var startPage = thispage - 9;
} else {
var startPage = thispage - thispage % 10 + 1;
}
if (startPage > 10) strHtml += '<span title="Prev 10 Pages"><a href="javascript:loadpage('+ (startPage - 1) +')"></a></span>';
for (var i = startPage; i < startPage + 10; i++) {
if (i > thispageCount) break;
if (i == thispage) {
strHtml += '<span title="Page ' + i + '">[' + i + ']</span>';
} else {
strHtml += '<span title="Page ' + i + '"><a href="javascript:loadpage('+i+')">[' + i + ']</a></span>';
}
}
if (thispageCount >= startPage + 10) strHtml += '<span title="Next 10 Pages"><a href="javascript:loadpage('+(startPage + 10)+')"></a></span>';
if (nextPage > thispageCount) {
strHtml += '<span title="Next Page"></span>';
strHtml += '<span title="Last Page">»</span>';
} else {
strHtml += '<span title="Next Page"><a href="javascript:loadpage('+nextPage+')"></a></span>';
strHtml += '<span title="Last Page"><a href="javascript:loadpage('+thispageCount+')">»</a></span>';
}
strHtml += '</span><br />';
break;
上面的代码是生成分页的核心代码 这样 通过自定义的loadpage(obj)事件[obj就是点击的页码]
我们可以利用ajax向服务端传递当前页码以获取返回结果
服务端首先需要的是一个Access的分页类 我自己写了一个
public class AccessPager
{
//internal member
private string table=stringEmpty;
private int pagesize=10;
private int pageindex=1;
private string orderkey = stringEmpty;
/// <summary>
/// contrustors
/// </summary>
/// <param name="tablename"></param>
/// <param name="curpage"></param>
/// <param name="size"></param>
public AccessPager(string tablename, int curpage, int size,string key)
{
table = tablename;
pagesize = size;
pageindex = curpage;
orderkey = key;
}
/// <summary>
/// get record count
/// </summary>
/// <returns></returns>
public int recCount()
{
string sql = "select count() from " + table;
return (int)AccessHelperExecuteScalar(SystemDataCommandTypeText, sql, null);
}
/// <summary>
/// get all page count
/// </summary>
/// <returns></returns>
public int pageCount()
{
if (recCount() % pagesize == 0)
return recCount() / pagesize;
else
return (recCount() / pagesize) + 1;
}
/// <summary>
/// get dataset
/// </summary>
/// <returns></returns>
public SystemDataDataSet ExecuteDataSet()
{
string sql = stringEmpty;
if (pageCount() > 1)
{
if (pageindex == 1)
sql = "select top " + pagesizeToString() + " from " + table + " order by " + orderkey + " desc";
else if (pageindex < pageCount())
{
int endcount = pagesize (pageindex-1);
sql = "select top " + pagesizeToString() + " from " + table + " where (" + orderkey + "<(select min(" + orderkey + ") from (select top "+endcountToString()+" "+orderkey+" from "+table+" order by "+orderkey+" desc) as t)) order by "+orderkey+" desc";
}
else if(pageindex>=pageCount())
{
if (recCount() % pagesize == 0)
{
sql = "select top " + pagesizeToString() + " from " + table + " order by " + orderkey + " desc";
}
else
{
int pLeft=recCount()%pagesize;
int endcount = pagesize (pageindex - 1);
sql = "select top " +pLeft+ " from " + table + " where (" + orderkey + "<(select min(" + orderkey + ") from (select top " + endcountToString() + " " + orderkey + " from " + table + " order by " + orderkey + " desc) as t)) order by " + orderkey + " desc";
}
}
}
else
sql = "select from " + table+" order by "+orderkey+" desc";
return AccessHelperExecuteDataset(sql, SystemDataCommandTypeText, null);
}
}
这样就可以根据表名称 排序id（我这个类只针对有编号且编号为主键的情况）页码 每页大小取出dataset了
然后 在页面的ajax方法中 我们 首先 默认情况下载入第一页的数据
<div id="main">
<script language="javascript" type="text/javascript">
Blog_indexgetData(1,2,_callback);
function _callback(res){
$('main')innerHTML=resvalue; }
</script>
</div>
当点击js生成的分页控件上的页码时 触发以下事件
<script language="javascript" type="text/javascript">
function loadpage(obj){
$('main')innerHTML='<img src="/images/indicatorgif" alt="" />数据载入中';
$('txtPageCount')value=obj;
var pg2 = new showPages('pg');
pg2pageCount =<%=getCount(2)%>;
pg2page= $('txtPageCount')value;
pg2printHtml(1);
Blog_indexgetData(obj,2,loadpage_callback);
}
function loadpage_callback(res){
$('main')innerHTML=resvalue;
}
</script>
下面的是js生成分页控件的代码
<div id="page_container">
<div id="Pages"></div>
<input id="txtPageCount" type="text" style="display:none"/>
<script language="javascript" type="text/javascript">
var pg = new showPages('pg');
pgpageCount =<%=getCount(2)%>;
pgpage=1;
pgprintHtml(1);
</script>
</div>
可以用存储过程分页
/
函数名称: GetRecordFromPage
函数功能: 获取指定页的数据
参数说明: @tblName 包含数据的表名
@fldName 关键字段名
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件 (注意: 不要加 where)
/
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(2000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
GO
DataReader不支持分页，使用DataSet
protected void GridView1_PageindexChanging(object sender, GridViewPageEventArgs e)
{
thisGridView1PageIndex = eNewPageIndex;
BindData();
}
private void BindData()
{
SqlConnection conn = new SqlConnection();
connConnectionString = "Data Source=17212178;Initial Catalog=FamilyFinanceSystem;Integrated Security=TRUE";
SqlCommand comm = new SqlCommand("Select from rdParentItem", conn);
SqlDataAdapter da = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
connOpen();
daFill(ds);
connClose();
GridView1DataSource = dsTables[0]DefaultView;
GridView1DataBind();
}
private void indexListBind()
{
try
{
string parID = RequestQueryString["id"]ToString();
int curPage = ConvertToInt32(thislb_pageIndexText) - 1;//获取当前页码,我用的是一个lable当了中间变量
con = Admin_DBgetConn();
conOpen();
DataSet ds = new DataSet();
string Path = "~";
OleDbDataAdapter sda = new OleDbDataAdapter("select ID,'" + Path + "'+imagePath as imagePath,introduce from D_Picture where parID=@parID", con);
sdaSelectCommandParametersAdd(new OleDbParameter("@parID", parID));
sdaFill(ds, "index");
SystemWebUIWebControlsPagedDataSource ps = new PagedDataSource();//实例化分页数据源
psDataSource = dsTables["index"]DefaultView;//将要绑定在datalist上的datatable给分页数据源
psAllowPaging = true;
psPageSize = 8;//每页显示几条记录
psCurrentPageIndex = curPage;//设置当前页的索引(当前页码减1就是)
thisbtn_upEnabled = true;
thisbtn_nextEnabled = true;
thisbtn_fristEnabled = true;
thisbtn_endEnabled = true;
endPage = psPageCount;
if (curPage == 0)//当是第一页是上一页和首页的按钮不可用
{
thisbtn_upEnabled = false;
thisbtn_fristEnabled = false;
}
if (curPage == psPageCount - 1)//当是最后一页时下一页和最后一页的按钮不可用
{
thisbtn_nextEnabled = false;
thisbtn_endEnabled = false;
}
thisindexListDataSource = ps;
thisindexListDataKeyField = "ID";
thisindexListDataBind();
conClose();
}
catch (Exception ex)
{
ConsoleWriteLine(exMessage);
}
}
protected void btn_frist_Click(object sender, EventArgs e)
{
thislb_pageIndexText = "1";
thisindexListBind();
}
protected void btn_up_Click(object sender, EventArgs e)
{
int page = intParse(thislb_pageIndexText) - 1;
thislb_pageIndexText = pageToString();
thisindexListBind();
}
protected void btn_next_Click(object sender, EventArgs e)
{
int page = intParse(thislb_pageIndexText) + 1;
thislb_pageIndexText = pageToString();
thisindexListBind();
}
protected void btn_end_Click(object sender, EventArgs e)
{
thislb_pageIndexText = endPageToString();
thisindexListBind();
}
查一次数据库得到N行数据快一点，还是从静态DataTable中得到一次数据快一点 --- 必然是后者快，因为直接操作内存
分页代码的意义何在 --- 哪里秀？我没看到，而且别人秀你管他作甚
仅仅是为了避免大数据库加载查询吗 --- 主要是为了加快查询速度和减少内存占用，想一想百度搜索如果把上亿数据放一页会怎样
何必去一页一页查询，有这个必要吗 --- 你说百度怎么办吧
真的有人会一页一页浏览吗 --- 即便不会，你也不必像3那样真加载百万条到内存吧
那SQL存储过程分页就有存在意义了 --- 能用当然好，但不是所有数据库都有存储过程，而且人家说分页也不一定就是数据库作数据存储
为什么不一次性加载好，一页一页地显示呢 --- 就像你说的，既然我们浏览百度基本只看前几页，那你把100万页加载到内存，浪费了网络带宽和服务器务器资源，等着老板炒鱿鱼吧
网站模板库 » ASP.NET用AspNetPager将table内容分页，客户端如何将的table下Repeater绑定的datable无分页的导出为excel
0条评论