导入-导出Excel表格-优快云博客

博客介绍了Excel表格相关操作的代码实现。包括以Page或DataGrid为媒介导出Excel表格或Word文档的方法，从Excel文件获取数据到DataSet的方法，以及将Excel数据导入并填充到新Excel文件的方法，涉及数据的导出、显示和导入操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

简单的导出

/// <summary>
  /// <本方法是以Page或DataGrid为媒介导出Excel表格或者Word文档>
  /// </summary>
  public void ToExcel(System.Web.UI.Control ctl)
  {
   HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls"); //把filename里的.xls换成.doc就可以导出Word文档
   HttpContext.Current.Response.Charset ="GB2312";
   HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
   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();
  }

简单的显示

/// <summary>
  /// Path:Excel 的路径
  /// ss:Excel 的表名
  /// </summary>

  public DataSet ExcelToDS(string Path,string ss)
  {
   string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;";
   OleDbConnection conn = new OleDbConnection(strConn);
   conn.Open();
   string strExcel = "";
   OleDbDataAdapter myCommand = null;
   DataSet ds = null;
   strExcel="select * from ["+ss +"$]";
   myCommand = new OleDbDataAdapter(strExcel, strConn);
   ds = new DataSet();
   myCommand.Fill(ds,"tablevalue");
   if (conn != null)
   {
    conn.Close();
   }
   return ds;
  }

//导入excel
  private void Button1_Click(object sender, System.EventArgs e)
  {
   string aa=System.Web.HttpContext.Current.Server.MapPath(TextBox2.Text.Trim()); //得到服务器位置
   string pathsave=System.Web.HttpContext.Current.Server.MapPath(TextBox1.Text.Trim()); //得到服务器位置

   DataSet ds= ExcelToDS(aa,"a");//取得绑定的DataSet 可改DataSet为你的DataSet了.

   Excel.Application ExcelApp=new Excel.ApplicationClass();
   Excel.Workbook ExcelWorkBook=ExcelApp.Application.Workbooks.Add(true);
   Excel.Worksheet ExcelWorkSheet=(Excel.Worksheet)ExcelWorkBook.Worksheets[1];
   int rowcount,columncount;
   rowcount=(int)ds.Tables["tablevalue"].Rows.Count;
   columncount=(int)ds.Tables["tablevalue"].Columns.Count;
   //开始填充 //随意写内容了...
   int ii,jj;
   ExcelApp.Cells[1,1]="公司";
   for ( ii=0;ii<rowcount;ii++)
   {
    for ( jj=0;jj<columncount;jj++)
    { ExcelApp.Cells[ii+4,jj+1]=ds.Tables["tablevalue"].Rows[ii][jj].ToString().Trim(); }
   }
   ExcelWorkBook.SaveCopyAs(pathsave);    ExcelWorkSheet=null;
   ExcelWorkBook.Close(false,null,null);
   ExcelApp.Quit();
   System.GC.Collect();