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

王朝mssql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有  導航