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