转载 ADO从RecordSet对象向Excel批量插入数据

原文位于：http://topic.csdn.net/u/20080806/12/3B3BD793-B28C-447B-B5D2-F4E06F51DC43.html

 

需要引用ADO和Excel的com对象

Microsoft ActiveX Data Objects 2.8 Library
Microsoft Excel 11.0 Object Library

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();//关闭进程
            }
        }
    }
}