用ADO向Excel批量导入数据

来源：互联网发布：听音乐软件哪个好编辑：程序博客网时间：2024/05/16 05:17

和前面一篇用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 * 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();//关闭进程
            }
        }
    }
}

相关文章：
用QueryTable向excel批量导入数据