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