本站地址:http://www.bajiao123.com
sql server 与 excel 互导以及在asp.net中从DataTable导出到excel
1.从excel直接读入数据库www.yestar2000chinai tp ow er.comFWF7EC9
insert into t_test ( 字段 ) www.yestar2000chinai tp ow er.comFWF7EC9
select 字段 www.yestar2000chinai tp ow er.comFWF7EC9
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\\test.xls";
User ID=Admin;Password=;
Extended properties=Excel 8.0')...[sheet1$]www.yestar2000chinai tp ow er.comFWF7EC9
2.从数据库直接写入excelwww.yestar2000chinai tp ow er.comFWF7EC9
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" ' 注意参数的大小写，另外这种方法写入数据www.yestar2000chinai tp ow er.comFWF7EC9
的时候没有标题www.yestar2000chinai tp ow er.comFWF7EC9
3.从DataTable导出到excelwww.yestar2000chinai tp ow er.comFWF7EC9
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;; www.yestar2000chinai tp ow er.comFWF7EC9
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();www.yestar2000chinai tp ow er.comFWF7EC9
本站地址:http://www.bajiao123.com
