C#导出Excel数据的方法与实现代码

SqlConnection conn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn);

DataSet ds=new DataSet();
da.Fill(ds,"table1");
DataTable dt=ds.Tables["table1"];

string name=System.Configuration.ConfigurationSettings.AppSettings["downloadurl"].ToString()+DateTime.Today.ToString("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next(10000).ToString()+".csv";//存放到web.config中downloadurl指定的路径，文件格式为当前日期+4位随机数
FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write);
StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("gb2312"));
sw.WriteLine("自动编号,姓名,年龄");
foreach(DataRow dr in dt.Rows)
{
    sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]);
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name));
Response.ContentType = "application/ms-excel";// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.WriteFile(name); // 把文件流发送到客户端
Response.End();

public void Out2Excel(string sTableName,string url)
 {
 Excel.Application oExcel=new Excel.Application();
 Workbooks oBooks;
 Workbook oBook;
 Sheets oSheets;
 Worksheet oSheet;
 Range oCells;
 string sFile="",sTemplate="";
 //
 System.Data.DataTable dt=TableOut(sTableName).Tables[0];
 
sFile=url+"myExcel.xls";
 sTemplate=url+"MyTemplate.xls";
 //
 oExcel.Visible=false;
 oExcel.DisplayAlerts=false;
 //定义一个新的工作簿
 oBooks=oExcel.Workbooks;
 oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing, Type.Missing, Type.Missing);
 oBook=oBooks.get_Item(1);
 oSheets=oBook.Worksheets;
 oSheet=(Worksheet)oSheets.get_Item(1);
 //命名该sheet
 oSheet.Name="Sheet1";
 
oCells=oSheet.Cells;
 //调用dumpdata过程，将数据导入到Excel中去
 DumpData(dt,oCells);
 //保存
 oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
 oBook.Close(false, Type.Missing,Type.Missing);
 //退出Excel，并且释放调用的COM资源
 oExcel.Quit();
 
GC.Collect();
 KillProcess("Excel");
 }
 
private void KillProcess(string processName)
 {
 System.Diagnostics.Process myproc= new System.Diagnostics.Process();
 //得到所有打开的进程
 try
 {
 foreach (Process thisproc in Process.GetProcessesByName(processName))
 {
 if(!thisproc.CloseMainWindow())
 {
 thisproc.Kill();
 }
 }
 }
 catch(Exception Exc)
 {
 throw new Exception("",Exc);
 }
 }

protected void ExportExcel()
{
    gridbind();
   if(ds1==null) return;
 
   string saveFileName="";
 //   bool fileSaved=false;
    SaveFileDialog saveDialog=new SaveFileDialog();
    saveDialog.DefaultExt ="xls";
    saveDialog.Filter="Excel文件|*.xls";
    saveDialog.FileName ="Sheet1";
    saveDialog.ShowDialog();
    saveFileName=saveDialog.FileName;
    if(saveFileName.IndexOf(":")<0) return; //被点了取消
 //   excelapp.Workbooks.Open   (App.path & 工程进度表.xls)
  
   Excel.Application xlApp=new Excel.Application();
    object missing=System.Reflection.Missing.Value;
 

   if(xlApp==null)
    {
     MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
     return;
    }
    Excel.Workbooks workbooks=xlApp.Workbooks;
    Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
    Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
    Excel.Range range;
    
 
   string oldCaption=Title_label .Text.Trim ();
    long totalCount=ds1.Tables[0].Rows.Count;
    long rowRead=0;
    float percent=0;
 
   worksheet.Cells[1,1]=Title_label .Text.Trim ();
    //写入字段
    for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
    {
     worksheet.Cells[2,i+1]=ds1.Tables[0].Columns.ColumnName; 
    range=(Excel.Range)worksheet.Cells[2,i+1];
     range.Interior.ColorIndex = 15;
     range.Font.Bold = true;
  
   }
    //写入数值
    Caption .Visible = true;
    for(int r=0;r<ds1.Tables[0].Rows.Count;r++)
    {
     for(int i=0;i<ds1.Tables[0].Columns.Count;i++)
     {
      worksheet.Cells[r+3,i+1]=ds1.Tables[0].Rows[r];    
    }
     rowRead++;
     percent=((float)(100*rowRead))/totalCount;   
    this.Caption.Text= "正在导出数据["+ percent.ToString("0.00")  +"%]...";
     Application.DoEvents();
    }
    worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing);
   
    this.Caption.Visible= false;
    this.Caption.Text= oldCaption;
 
   range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds1.Tables[0].Rows.Count+2,ds1.Tables[0].Columns.Count]);
    range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
   
   range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
    range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
 
   if(ds1.Tables[0].Columns.Count>1)
    {
     range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex=Excel.XlColorIndex.xlColorIndexAutomatic;
     }
    workbook.Close(missing,missing,missing);
    xlApp.Quit();
   }