用ADO向excel批量导入数

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

参考代码：

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.****.net/jinjazz/archive/2008/08/06/2775725.aspx
我的历史攒分帖子
http://blog.****.net/jinjazz/category/407229.aspx

希望大家支持我的blog

用ADO向excel批量导入数

相关推荐