GridView中分页和导出数据

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.IO;

public partial class GridView : System.Web.UI.Page {

protected string connString = ConfigurationSettings.AppSettings[\"ConnectionString\"]; protected int rowCount = 0; protected int pageCount = 0; protected int pageSize = 0; protected int pageNow = 0;

protected void Page_Load(object sender, EventArgs e) {

if (!Page.IsPostBack) {

//connString = ConfigurationSettings.AppSettings[\"ConnectionString\"]; //获取记录的总条数

SqlConnection con = new SqlConnection(); con.ConnectionString = connString; con.Open();

SqlCommand cmd = new SqlCommand();

/********************************修改其中的数据表名称**************************/ cmd.CommandText = \"select count(*) from BMSK_GB\"; cmd.Connection = con;

rowCount = Convert.ToInt32(cmd.ExecuteScalar()); con.Close();

//设置和获取每页显示几条记录 DropDownList1.Items.Clear(); for (int i = 1; i <=rowCount; i++) {

DropDownList1.Items.Add(i.ToString()); }

if (rowCount < 5) {

DropDownList1.SelectedIndex = rowCount - 1; } else {

DropDownList1.SelectedIndex = 4; }

pageSize = Convert.ToInt32(DropDownList1.SelectedValue); if ((rowCount % pageSize) == 0) {

pageCount = Convert.ToInt32(rowCount / pageSize); } else {

pageCount = Convert.ToInt32(rowCount / pageSize) + 1; }

//获取跳转到哪一页的信息 DropDownList2.Items.Clear(); for (int j = 1; j <= pageCount; j++) {

DropDownList2.Items.Add(j.ToString()); }

Label1.Text = rowCount.ToString(); Label3.Text = pageCount.ToString(); //保存控件数据

ViewState[\"RowCount\"] = rowCount; ViewState[\"PageSize\"] = pageSize; ViewState[\"PageCount\"] = pageCount; //当前页 pageNow = 1;

ViewState[\"PageNow\"] = pageNow; }

rowCount = Convert.ToInt32(ViewState[\"RowCount\"]); pageCount = Convert.ToInt32(ViewState[\"PageCount\"]); pageSize = Convert.ToInt32(ViewState[\"PageSize\"]); pageNow = Convert.ToInt32(ViewState[\"PageNow\"]); if (pageCount < 0 || rowCount < 0) {

Response.Write(\"发生错误\"); Response.End(); }

if (pageNow <= 1) {

pageNow = 1;

btnFirst.Enabled = false;

btnPrevious.Enabled = false; }

Label2.Text = pageNow.ToString(); if (!Page.IsPostBack) {

BindData(); } }

//进行数据绑定 public void BindData() {

/********************************修改其中的数据表名称**************************/ String sql = \"select SKBM as 水库编码,SKMC as 水库名称,SZS as 所在市,SZX as 所在县,LX as 类型,SFWX as 是否危险 from BMSK_GB\"; SqlConnection con = new SqlConnection(); con.ConnectionString = connString; con.Open();

SqlDataAdapter da = new SqlDataAdapter(sql, con); DataSet ds = new DataSet();

/********************************修改其中的数据表名称**************************/ da.Fill(ds, (pageNow - 1) * pageSize, pageSize, \"BMSK_GB\"); GridView1.DataSource = ds; GridView1.DataBind(); ds.Clear(); con.Close();

Label2.Text = pageNow.ToString(); }

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) {

pageSize = Convert.ToInt32(DropDownList1.SelectedValue); if(Convert.ToInt32(rowCount%pageSize)==0) {

pageCount = Convert.ToInt32(rowCount/pageSize); } else {

pageCount = Convert.ToInt32(rowCount/pageSize); }

DropDownList2.Items.Clear();

for(int i=1;i<=pageCount;i++) {

DropDownList2.Items.Add(i.ToString()); }

Label3.Text = pageCount.ToString();

ViewState[\"PageSize\"] = pageSize; ViewState[\"PageCount\"] = pageCount; if (pageNow > pageCount) {

pageNow = 1;

ViewState[\"PageNow\"] = pageNow.ToString(); }

btnFirst.Enabled = true; btnPrevious.Enabled = true; btnNext.Enabled = true; btnLast.Enabled = true; if (pageNow == 1) {

btnFirst.Enabled = false; btnPrevious.Enabled = false; }

if (pageNow == pageCount) {

btnNext.Enabled = false; btnLast.Enabled = false; }

BindData(); }

protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e) {

pageNow = Convert.ToInt32(DropDownList2.SelectedValue); ViewState[\"PageNow\"] = pageNow.ToString(); BindData(); }

//显示第一页

private void moveFirst() {

pageNow = 1;

ViewState[\"PageNow\"] = pageNow.ToString(); BindData();

btnFirst.Enabled = false;

btnPrevious.Enabled = false; btnNext.Enabled = true; btnLast.Enabled = true; }

//显示上一页

private void movePrevious() {

pageNow = pageNow - 1;

ViewState[\"PageNow\"] = pageNow.ToString(); BindData(); if (pageNow==1) {

btnFirst.Enabled = false; btnPrevious.Enabled = false; }

btnNext.Enabled = true; btnLast.Enabled = true; }

//显示下一页

private void moveNext() {

pageNow = pageNow + 1;

ViewState[\"PageNow\"] = pageNow.ToString(); BindData();

btnFirst.Enabled = true; btnPrevious.Enabled = true; if (pageNow == pageCount) {

btnNext.Enabled = false; btnLast.Enabled = false; } }

//显示尾页

private void moveLast() {

pageNow = pageCount;

ViewState[\"PageNow\"] = pageNow.ToString(); BindData();

btnFirst.Enabled = true; btnPrevious.Enabled = true; btnNext.Enabled = false;

btnLast.Enabled = false; }

protected void btnFirst_Click(object sender, EventArgs e) {

moveFirst(); }

protected void btnPrevious_Click(object sender, EventArgs e) {

movePrevious(); }

protected void btnNext_Click(object sender, EventArgs e) {

moveNext(); }

protected void btnLast_Click(object sender, EventArgs e) {

moveLast(); }

//excel导入 gridview完整实例

//--------------源代码--------------------

protected void btnUpload_Click(object sender, EventArgs e)//上传文件 {

if (FileUpload1.HasFile) { //try //{

string path = Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath.ToString()) + \"xls\\\\\";//获取程序根目录

path += Path.GetFileName(FileUpload1.FileName); FileUpload1.PostedFile.SaveAs(path); // BindGrid(lblCurrentPath.Text);

//string xlsPath = path; // 绝对物理路径

string dbName=Path.GetFileName(FileUpload1.FileName).Replace(\"xls\ // 查询语句 string

xlsPath

=

AppDomain.CurrentDomain.BaseDirectory.ToString()

+

\"xls\\\\\"

+

Path.GetFileName(FileUpload1.FileName);

this.GvData.DataSource = GetExcelContent(xlsPath, dbName);

this.GvData.DataBind(); //} //catch //{

//ClientScript.RegisterStartupScript(typeof(Page), \"aa\文件上传失败！')\ //} } }

private DataSet GetExcelContent(string filepath, string dbName) {

string strCon = \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" + filepath + \";Extended Properties='Excel 8.0;HDR=No;IMEX=1'\";

System.Data.OleDb.OleDbConnection myConn = new System.Data.OleDb.OleDbConnection(strCon); string strCom = \"SELECT * FROM [\" + dbName + \"$]\"; myConn.Open();

System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, myConn);

//创建一个DataSet对象

DataSet myDataSet = new DataSet(); //得到自己的DataSet对象 myCommand.Fill(myDataSet); //关闭此数据链接 myConn.Close(); return myDataSet; }

先上传EXCEL文件，然后读入DATASET放入GridView，然后从GridView提交到数据库，这样就没有问题。 文件上传控件: Form里不需要enctype=\"multipart/form-data\" 文件上传代码:

if (myFile.PostedFile.FileName != \"\") {

//上传文件的绝对路径

string sFile = myFile.PostedFile.FileName; //获取文件全名

sFile = sFile.Substring(sFile.LastIndexOf(\"\\\\\") + 1); //获取后缀名

sFile = sFile.Substring(sFile.LastIndexOf(\".\")); if (sFile.ToLower() != \".xls\") {

Response.Write(\"请选择Excel文件!\"); Response.End(); }

//为了防止重名，获得日期为文件名年月日时分秒毫秒

string datatime = System.DateTime.Now.ToString(\"yyyMMddHHmmssffff\"); //上传后文件的新名 sFile = datatime + sFile;

//AppDomain.CurrentDomain.BaseDirectory.ToString() 获取此项目的根目录 //sPath 获取上传后的路径

string sPath = AppDomain.CurrentDomain.BaseDirectory.ToString() + \"ExcelFiles\\\\\" + sFile; //上传文件

myFile.PostedFile.SaveAs(sPath);

this.myGridView.DataSource = GetExcelContent(sPath); this.myGridView.DataBind(); }

读取EXCEL到DATASET代码:

private DataSet GetExcelContent(string filepath) {

string strCon = \"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\" + filepath + \";Extended Properties='Excel 8.0;HDR=No;IMEX=1'\";

System.Data.OleDb.OleDbConnection System.Data.OleDb.OleDbConnection(strCon);

string strCom = \"SELECT F1 as resno,F2 as resname FROM [Sheet1$]\"; myConn.Open();

System.Data.OleDb.OleDbDataAdapter System.Data.OleDb.OleDbDataAdapter(strCom, myConn); //创建一个DataSet对象 DataSet myDataSet = new DataSet(); //得到自己的DataSet对象 myCommand.Fill(myDataSet); //关闭此数据链接 myConn.Close(); return myDataSet; }

最后是数据提交到数据库代码： string stresno = \"\"; string stresname = \"\";

foreach (GridViewRow row in this.myGridView.Rows) {

Label txtesno = (Label)row.FindControl(\"labresno\");

stresno += txtesno.Text.ToString().Trim().Replace(\"'\

Label txtresname = (Label)row.FindControl(\"labresname\");

stresname += txtresname.Text.ToString().Trim().Replace(\"'\

myCommand

=

new

myConn

=

new

}

Response.Write(stresno + \"
\" + stresname); Response.End();

asp.net导出excel

//将页面数据导出到excel表格中去

//导出按钮单击事件

protected void btndaochu_Click(object sender, EventArgs e) {

Export(\"application/ms-excel\教务维护表.xls\"); }

private void Export(string FileType, string FileName) {

myGridView.AllowPaging = false; //清除分页 myGridView.AllowSorting = false; //清除排序

this.myGridView.Columns[1].Visible =false ;//隐藏编辑列 this.myGridView.Columns[0].Visible = false ;//隐藏删除列 bind();//绑定数据源

Response.Clear(); Response.Buffer = true; Response.Charset = \"GB2312\";

Response.ContentEncoding = System.Text.Encoding.UTF7; Response.AppendHeader(\"Content-Disposition\HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString()); Response.ContentType = FileType; this.EnableViewState = false; StringWriter tw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); myGridView.RenderControl(hw); Response.Write(tw.ToString()); Response.Flush(); Response.End();

\"attachment;filename=\"

+

this.myGridView.Columns[1].Visible = true ;//显示编辑列 this.myGridView.Columns[0].Visible = true ;//显示删除列 myGridView.AllowPaging = true; //恢复分页 myGridView.AllowSorting = true; //恢复排序 bind();//绑定数据源

//这个事件必须加

public override void VerifyRenderingInServerForm(Control control) {

//base.VerifyRenderingInServerForm(control); }

//导出到Word

protected void btnWord_Click(object sender, EventArgs e) {

ExportToExcel(\"application/ms-word\", \"水库信息.doc\"); } }