ASP.NET 显示 Excel 文件的分页方法

作者：孟宪会阅读：1391 发表于：2010-08-20 13:58:05

ASPX 代码

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

  protected void Page_Load(object sender, EventArgs e)
  {
    string xlsConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Book1.xls") + ";Extended Properties='Excel 8.0;HDR=No;'";
    OleDbConnection cn = new OleDbConnection(xlsConnStr);
    cn.Open();
    String sql = "select Count(*) From [Sheet1$]";
    OleDbCommand cmd = new OleDbCommand(sql, cn);

    // 总的记录数
    int TotalCount = Convert.ToInt32(cmd.ExecuteScalar());

    //当前页的序号
    int PageIndex = 1;
    String page = Request.QueryString["Page"];
    if (page == null) page = "1";
    Int32.TryParse(page, out PageIndex);
    if (PageIndex < 1) PageIndex = 1;

    //每页显示的数量
    int PageItem = 5;
    int startRecord = (PageIndex - 1) * PageItem;

    sql = "Select * From [Sheet1$]";
    OleDbDataAdapter da = new OleDbDataAdapter(sql, cn);
    DataSet ds = new DataSet();
    da.Fill(ds, startRecord, PageItem, "Sheet");

    GridView1.DataSource = ds.Tables[0].DefaultView;
    GridView1.DataBind();

    BuildPagers(TotalCount, PageIndex, PageItem);
  }

  private void BuildPagers(int TotalCountRecord, int CurrentPage, int PageItem)
  {
    int Step = 6;
    int LeftNum = 0;
    int RightNum = 0;
    String PageUrl = "?";
    int PageCount = (int)Math.Ceiling((double)(TotalCountRecord) / PageItem);
    if (CurrentPage - Step < 1)
    {
      LeftNum = 1;
    }
    else
    {
      LeftNum = CurrentPage - Step;
    }

    if (CurrentPage + Step > PageCount)
    {
      RightNum = PageCount;
    }
    else
    {
      RightNum = CurrentPage + Step;
    }
    string OutPut = "";
    for (int i = LeftNum; i <= RightNum; i++)
    {
      if (i == CurrentPage)
      {
        OutPut += "<span style='color:red'>" + i.ToString() + "</span> ";
      }
      else
      {
        OutPut += "<a href=\"" + PageUrl + "Page=" + i.ToString() + "\">" + i.ToString() + "</a> ";
      }
    }
    if (CurrentPage > 1)
    {
      OutPut = "<a href='" + PageUrl + "Page=1'>首页</a>  <a href=\"" + PageUrl + "Page=" + (CurrentPage - 1) + "\">上一页</a> " + OutPut;
    }

    if (CurrentPage < PageCount)
    {
      OutPut += " <a href=\"" + PageUrl + "Page=" + (CurrentPage + 1) + "\">下一页</a> <a href='" + PageUrl + "Page=" + PageCount + "'>末页</a>";
    }
    Pager.InnerHtml = OutPut ;
  }
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>孟宪会的Excel分页测试</title>
</head>
<body>
  <form id="form1" runat="server">
  <asp:GridView ID="GridView1" runat="server" ShowHeader="false">
  </asp:GridView>
  <div id="Pager" runat="server"></div>
  </form>
</body>
</html>

【发表评论】【打印文章】【收藏本页】