用ADO向Excel批量导入数据
和前面一篇用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(); //关闭进程
- }
- }
- }
- }