用ADO向Excel批量导入数据

最新推荐文章于 2014-03-15 19:37:01 发布

iteye_18688

最新推荐文章于 2014-03-15 19:37:01 发布

阅读量147

点赞数

和前面一篇用OleDB的方法类似，我们可以用ADO从RecordSet对象向Excel批量插入数据，这个方法无法自动复制字段名。
我们需要引用ADO和Excel的com对象

参考代码如下

 
  using System; 
 using System.Collections.Generic; 
 using System.Text; 
 using System.Reflection; 
 using Excel=Microsoft.Office.Interop.Excel; 
 namespace ConsoleApplication18 
 { 
  class Program 
 { 
  static  void Main( string []args) 
 { 
 ExportDataToExcel( "server=(local);uid=sa;pwd=sqlgis;database=master" , 
  "select*fromsysobjects" ,@ "c:\testADO.xls" , "sysobjects" ); 
 } 
  
  static  void ExportDataToExcel( string connectionString, string sql, string fileName, string sheetName) 
 { 
 Excel.Applicationapp= new Excel.ApplicationClass(); 
 Excel.Workbookwb=(Excel.WorkbookClass)app.Workbooks.Add(Missing.Value); 
 Excel.Worksheetws=wb.Worksheets.Add(Missing.Value,Missing.Value,Missing.Value,Missing.Value) as Excel.Worksheet; 
 ws.Name=sheetName; 
  try 
 { 
 ADODB.Connectionconn= new ADODB.ConnectionClass(); 
 conn.Open( "driver={SQLServer};" +connectionString, "" , "" ,0); 
 ADODB.Recordsetrs= new ADODB.RecordsetClass(); 
 rs.Open(sql,conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockReadOnly,0); 
 Excel.Rangerange=ws.get_Range( "A2" ,Missing.Value); 
 range.CopyFromRecordset(rs,65535,65535); 
 } 
  catch (Exceptionex) 
 { 
  string str=ex.Message; 
 } 
  finally 
 { 
 wb.Saved= true ; 
 wb.SaveCopyAs(fileName); //保存 
 app.Quit(); //关闭进程 
 } 
 } 
 } 
 } 
 
 