Asp.net操作Excel的例子包括添加 修改 检索 导出 已调试通过可以直接使用

Asp.net操作Excel的例子包括添加 修改 检索 导出 已调试通过可以直接使用

private void Data_Bind()
  {//检索程式
   //比如我们要检索A1到D20的所有数据，可以这样检索：SELECT * FROM [SampleSheet$A1:D20]，这里，使用A1:D20来限制检索范围。
   //如果数据表第一行已经定义数据列的名字，我们可以选择需要显示的数据列来检索：SELECT 姓名 FROM [First$A1:B2]
   OleDbConnection conn=new OleDbConnection();
   conn.ConnectionString="provider=Microsoft.jet.OLEDB.4.0;Data Source="+Server.MapPath("DataBase.xls")+";Extended Properties=Excel 8.0;";
   string sql="SELECT * FROM [Sheet1$]";
   OleDbDataAdapter cmd=new OleDbDataAdapter(sql,conn);
   DataSet ds=new DataSet();
   cmd.Fill(ds,"DataTable1");
   xlsData.DataSource=ds.Tables["DataTable1"];
   xlsData.DataBind();
  } 

  private void show_Click(object sender, System.EventArgs e)
  {//检索
   Data_Bind();
  }

  private void add_Click(object sender, System.EventArgs e)
  {//添加
   Add_Xls();
  }
  private void Add_Xls(){//添加程式
  OleDbConnection conn=new OleDbConnection();
   conn.ConnectionString="provider=Microsoft.jet.oledb.4.0;Data Source="+Server.MapPath("DataBase.xls")+";Extended Properties=Excel 8.0;";
   conn.Open();
   string sql="insert into [Sheet1$](用户名,密码,ID)values('金勋男','11111111','6')";
   OleDbCommand cmd=new OleDbCommand(sql,conn);
   cmd.ExecuteNonQuery();
   cmd.Dispose();
   conn.Close();
   conn.Dispose();
   Data_Bind();
  }

  private void modify_Click(object sender, System.EventArgs e)
  {//修改
  Modify_Xls();
  }
  private void Modify_Xls(){
  //修改程式
   OleDbConnection conn=new OleDbConnection();
   conn.ConnectionString="provider=Microsoft.jet.oledb.4.0;Data Source="+Server.MapPath("DataBase.xls")+";Extended Properties=Excel 8.0;";
   conn.Open();
   string sql="update [Sheet1$] set 密码=88888888 where 用户名='宋倩'";
   OleDbCommand cmd=new OleDbCommand(sql,conn);
   cmd.ExecuteNonQuery();
   cmd.Dispose();
   conn.Close();
   conn.Dispose();
   Data_Bind();
  }
  private void emport_Click(object sender, System.EventArgs e)
  {//导出   
   ToExcel(xlsData);
  }
  
  public void ToExcel(System.Web.UI.Control ctl)
  { //通过DataGrid导出Xls程式
   HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
   HttpContext.Current.Response.Charset ="UTF-8";
   HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
   HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
   ctl.Page.EnableViewState =false;
   System.IO.StringWriter tw = new System.IO.StringWriter() ;
   System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
   ctl.RenderControl(hw);
   HttpContext.Current.Response.Write(tw.ToString());
   HttpContext.Current.Response.End();
  }