用ADO向excel批量导入数

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


参考代码：

C# code

   
   

    
    
    
    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 * from sysobjects
    
    "
    
    ,
    
    @"
    
    c:/testADO.xls
    
    "
    
    ,
    
    "
    
    sysobjects
    
    "
    
    );
        }
         
        
    
    static
    
     
    
    void
    
     ExportDataToExcel(
    
    string
    
     connectionString,
    
    string
    
     sql,
    
    string
    
     fileName,
    
    string
    
     sheetName)
        {
            Excel.Application app 
    
    =
    
     
    
    new
    
     Excel.ApplicationClass();
            Excel.Workbook wb 
    
    =
    
     (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
            Excel.Worksheet ws 
    
    =
    
     wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) 
    
    as
    
     Excel.Worksheet;
            ws.Name 
    
    =
    
     sheetName;
            
    
    try
    
    
            {
                ADODB.Connection conn 
    
    =
    
     
    
    new
    
     ADODB.ConnectionClass();
                conn.Open(
    
    "
    
    driver={SQL Server};
    
    "
    
    +
    
    connectionString,
    
    ""
    
    ,
    
    ""
    
    ,
    
    0
    
    );
                ADODB.Recordset rs 
    
    =
    
     
    
    new
    
     ADODB.RecordsetClass();
                rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 
    
    0
    
    );
                Excel.Range range 
    
    =
    
     ws.get_Range(
    
    "
    
    A2
    
    "
    
    , Missing.Value);
                range.CopyFromRecordset(rs, 
    
    65535
    
    , 
    
    65535
    
    );
            }
            
    
    catch
    
     (Exception ex)
            {
                
    
    string
    
     str 
    
    =
    
     ex.Message;
            }
            
    
    finally
    
    
            {
                wb.Saved 
    
    =
    
     
    
    true
    
    ;
                wb.SaveCopyAs(fileName);
    
    //
    
    保存
    
    

    
                    app.Quit();
    
    //
    
    关闭进程
    
    

    
                }
        }
    }
}

   
   

Blog同步更新
http://blog.csdn.net/jinjazz/archive/2008/08/06/2775725.aspx
我的历史攒分帖子
http://blog.csdn.net/jinjazz/category/407229.aspx

希望大家支持我的blog