和前面一篇用OleDB的方法類似，我們可以用ADO從RecordSet對象向Excel批量插入數據，這個方法無法自動復制字段名。
我們需要引用ADO和Excel的com對象
參考代碼如下
1.using System;
2.using System.Collections.Generic;
3.using System.Text;
4.using System.Reflection;
5.using Excel = Microsoft.Office.Interop.Excel;
6.namespace ConsoleApplication18
7.{
8. class Program
9. {
10. static void Main( string [] args)
11. {
12. ExportDataToExcel( "server=(local);uid=sa;pwd=sqlgis;database=master" ,
13. "select * from sysobjects" ,@ "c:/testADO.xls" , "sysobjects" );
14. }
15.
16. static void ExportDataToExcel( string connectionString, string sql, string fileName, string sheetName)
17. {
18. Excel.Application app = new Excel.ApplicationClass();
19. Excel.Workbook wb = (Excel.WorkbookClass)app.Workbooks.Add(Missing.Value);
20. Excel.Worksheet ws = wb.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Excel.Worksheet;
21. ws.Name = sheetName;
22. try
23. {
24. ADODB.Connection conn = new ADODB.ConnectionClass();
25. conn.Open( "driver={SQL Server};" +connectionString, "" , "" ,0);
26. ADODB.Recordset rs = new ADODB.RecordsetClass();
27. rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0);
28. Excel.Range range = ws.get_Range( "A2" , Missing.Value);
29. range.CopyFromRecordset(rs, 65535, 65535);
30. }
31. catch (Exception ex)
32. {
33. string str = ex.Message;
34. }
35. finally
36. {
37. wb.Saved = true ;
38. wb.SaveCopyAs(fileName); //保存
39. app.Quit(); //關閉進程
40. }
41. }
42. }
43.}
44.
