sql server 與 excel 互導以及在asp.net中從DataTable匯出到excel

sql server 與 excel 互導以及在asp.net中從DataTable匯出到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();