sql server 与 excel 互导以及在asp.net中从DataTable导出到excel

来源：互联网发布：安卓卸载瞬间知乎编辑：程序博客网时间：2024/04/28 10:31

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();

4.从Excel读入DataTable

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c://test.xls;Extended Properties=Excel 8.0;";

sqlstr = "select * from [ExcelSheet$]";

可以用DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);来获取ExcelSheet$的集合

5.从DataGrid导出Excel

HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+System.Web.HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.Charset = System.Text.Encoding.Default.WebName;
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType ="application/vnd.ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
//DG_Student.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();