导出Excel的几种方式

1.DataGrid直接导出

  

private void Button1_Click(object sender, System.EventArgs e)
  {
   DataTable dt =  new DataTable();
   dt.Columns.Add(new DataColumn("id",typeof(string)));
   dt.Columns.Add(new DataColumn("name",typeof(string)));
   dt.Columns.Add(new DataColumn("sex",typeof(string)));

   DataRow dr  = null;
   for(int i=0;i<1000;i++)
   {
    dr = dt.NewRow();
    dr["id"] =i.ToString();
    dr["name"] = "ssss";
    dr["sex"] = "男";
    dt.Rows.Add(dr);
   }

   this.DataGrid1.DataSource = dt;

   this.DataGrid1.DataBind();

   HttpContext.Current.Response.Charset ="UTF-8";
   Response.Charset = "GB2312";
   HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
   HttpContext.Current.Response.ContentType ="application/ms-excel";
   HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=a.xls" );
   DataGrid1.Page.EnableViewState =false;
   System.IO.StringWriter  tw = new System.IO.StringWriter();
   System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
   DataGrid1.RenderControl(hw);
   HttpContext.Current.Response.Write(tw.ToString());
   HttpContext.Current.Response.End();
  }

2.DataTable导出

一.使用GC.Collect();释放对象

public string DataToExcel(DataTable dt,string path,string filename)
  {
   Excel.Application  excel = null;
   Excel.Workbook book = null;
   Excel.Worksheet sheet = null;
   try
   {
    GC.Collect();
    excel = new Excel.ApplicationClass();
   
    book = excel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
    sheet = (Excel.Worksheet)book.ActiveSheet;
    int rowIndex=1;
    int colIndex=1;
    //表头
    foreach(DataColumn col in dt.Columns)
    {
     excel.Cells[1,colIndex] = col.ColumnName.ToString();
     //处理科学记数和计算问题
     if(col.DataType == System.Type.GetType("System.Int16") || col.DataType == System.Type.GetType("System.int32") || col.DataType == System.Type.GetType("System.int64")  || col.DataType == System.Type.GetType("System.Decimal"))
     {
      sheet.get_Range(excel.Cells[1,colIndex],excel.Cells[dt.Rows.Count+1,colIndex]).NumberFormatLocal = "G/通用格式";
     }
     else
     {
      sheet.get_Range(excel.Cells[1,colIndex],excel.Cells[dt.Rows.Count+1,colIndex]).NumberFormatLocal = "@";
     }
     colIndex++;
    }
   
    foreach(DataRow row in dt.Rows)
    {
     rowIndex ++;
     colIndex = 1;
     foreach(DataColumn col in dt.Columns)
     {
      if(col.DataType == System.Type.GetType("System.String"))
      {
       excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
      }
      else
      {
       excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
      }
      colIndex ++;
     }
    }

   
    

    excel.DisplayAlerts = false;
    System.Random rnd = new Random();
    string fileName = filename + rnd.Next(100000000).ToString() + ".xls";
    this._path = path + @"Report/"+fileName;
    book.SaveCopyAs(this._path);
    return this._path;
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
    excel.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
    sheet = null;
    book = null;
    excel = null;
    GC.Collect();
   }
  }

 二.不使用GC.Collect();释放对象