C#資料匯出到excel

1.從excel直接讀入資料庫

insert into t_test ( 欄位 )

select 欄位

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\test.xls";
User ID=Admin;Password=;
Extended properties=Excel 8.0')...[sheet1$]

2.從資料庫直接寫入excel

exec master..xp_cmdshell ' bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout c:\test.xls -c -S"soa" -U"sa" -P"sa" '   注意參數的大小寫，另外這種方法寫入資料

的時候沒有標題

3.從DataTable匯出到excel

  StringWriter stringWriter = new StringWriter();
   HtmlTextWriter htmlWriter = new HtmlTextWriter( stringWriter );
   DataGrid excel = new DataGrid();
   System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
   System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
   System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
   AlternatingStyle.BackColor = System.Drawing.Color.LightGray;
   headerStyle.BackColor =System.Drawing.Color.LightGray;
   headerStyle.Font.Bold = true;
   headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
   itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;;

   excel.AlternatingItemStyle.MergeWith(AlternatingStyle);
   excel.HeaderStyle.MergeWith(headerStyle);
   excel.ItemStyle.MergeWith(itemStyle);
   excel.GridLines = GridLines.Both;
   excel.HeaderStyle.Font.Bold = true;
   excel.DataSource = dt.DefaultView;   //輸出DataTable的內容
   excel.DataBind();
   excel.RenderControl(htmlWriter);
 
   string filestr = "d:\\data\\"+filePath;  //filePath是檔案的路徑
   int pos = filestr.LastIndexOf( "\\");
   string file = filestr.Substring(0,pos);
   if( !Directory.Exists( file ) )
   {
    Directory.CreateDirectory(file);
   }
   System.IO.StreamWriter sw = new StreamWriter(filestr);
   sw.Write(stringWriter.ToString());
   sw.Close();

   //直接輸出
   //Response.ContentType = "application/vnd.ms-excel";
   //Response.ContentEncoding = System.Text.Encoding.UTF8;
   //Response.Charset = "";
   //Response.Write(stringWriter.ToString()); 
   //Response.End();