SqlServer求匯入匯出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(); 

方法二：匯出到csv檔案，不存放到伺服器，直接給瀏覽器輸出檔案流 

優點： 
1、隨時產生，不需要佔用資源 
2、可以結合身份認證 
3、同樣利於資料交換 

實現方法： 
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"]; 
  StringWriter sw=new StringWriter(); 
  sw.WriteLine("自動編號,姓名,年齡"); 
  foreach(DataRow dr in dt.Rows) 
  { 
  sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); 
  } 
  sw.Close(); 
  Response.AddHeader("Content-Disposition", "attachment; filename=test.csv"); 
  Response.ContentType = "application/ms-excel"; 
  Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312"); 
  Response.Write(sw); 
  Response.End(); 

對方法一，二補充一點，如果你希望匯出的是xls檔案分隔字元用\t就可以了，不要用逗號 

代碼修改如下： 
sw.WriteLine("自動編號\t姓名\t年齡"); 
  foreach(DataRow dr in dt.Rows) 
  { 
  sw.WriteLine(dr["ID"]+"\t"+dr["vName"]+"\t"+dr["iAge"]); 
  } 
另外，修改輸出的副檔名為xls即可。 

方法三：從datagrid匯出html代碼，產生excel檔案，給用戶端下載 

優點： 
1、有固定的格式，樣子好看（datagrid的樣子） 

局限性： 
1、不適合資料交換，裡面有html代碼，比較亂，沒有固定格式 
2、datagrid不能有分頁、排序等，否則出錯 

實現方法： 
Response.Clear(); 
  Response.Buffer= false; 
  Response.Charset="GB2312"; 
  Response.AppendHeader("Content-Disposition","attachment;filename=test.xls"); 

  Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
this.EnableViewState = false; 
  System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); 
  System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); 
  this.DataGrid1.RenderControl(oHtmlTextWriter); 
  Response.Write(oStringWriter.ToString()); 
  Response.End(); 

在這裡說明一點：有的網友反映代碼出現“沒有dr["id"]”之類的錯誤，這個代碼是按照我的資料結構來寫的，到時候相關的欄位要換成你自己的才是。 

還
有就是如果檔案名稱需要中文的話，這麼修改Response.AddHeader("Content-Disposition",
"attachment;
filename="+System.Web.HttpUtility.UrlEncode("中
文",System.Text.Encoding.UTF8)+".xls");