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( "http://");
   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();