[攒分贴]用ADO向excel批量导入数

2008-08-06 12:12:14 1.8k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

将Excel数据快速大批量导入数据库的代码 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + [EXCEL文件，含路径] + ";"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataTable dtSchema = conn.GetOleDbSchemaTab 阅读详情

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

            }

        }

    }

}