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(); |
|
转载于:https://www.cnblogs.com/laoxu/archive/2005/08/09/210668.html