用C#读取Excel返回DataSet

不添加任何 excel dll 引用，代码很简单，就不做解释了。

复制代码

1. using System;

using System.Collections.Generic;

复制代码

1. 03 using System.Linq;
2. 04 using System.Text;
3. 05 using System.Data;
4. 06 using System.Data.Common;
5. 07
6. 08 namespace OCXMLCreater.ExcelProvider
7. 09 {
8. 10 public class ExcelHelper
9. 11 {
10. view sourceprint?01 //唯一需要解释的一点是这个连接字符串中，HDR=YES 表示此Excel表第一行用于显示字段名称（Header），如果没有字段名，则应 HDR=NO
11. 02 public static string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/WorkSpace/MyDocument/Samples2.xlsx;Extended Properties=""Excel 8.0;HDR=YES;""";
12. 03
13. 04 /// <summary>
14. 05 /// 读取 Excel 返回 DataSet
15. 06 /// </summary>
16. 07 /// <param name="connectionString">Excel 连接字符串</param>
17. 08 /// <param name="commandString">查询语句, for example:"SELECT ID,userName,userAddress FROM [Sheet1$]" </param>
18. 09 /// <returns></returns>
19. 10 public static DataSet GetExcelDataSet(string connectionString, string commandString)
20. 11 {
21. 12 DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
22. 13
23. 14 DbDataAdapter adapter = factory.CreateDataAdapter();
24. 15
25. 16 DbCommand selectCommand = factory.CreateCommand();
26. 17 selectCommand.CommandText = commandString; //commandString例如:"SELECT ID,userName,userAddress FROM [Sheet1$]"
27. 18
28. 19 DbConnection connection = factory.CreateConnection();
29. 20 connection.ConnectionString = connectionString;
30. 21
31. 22 selectCommand.Connection = connection;
32. 23 adapter.SelectCommand = selectCommand;
33. 24
34. 25 DataSet cities = new DataSet();
35. 26 adapter.Fill(cities);
36. 27
37. 28 connection.Close();
38. 29 return cities;
39. 30 }
40. 31 }
41. 32 }

原文地址：http://www.dotnetbbs.com/read.php?tid-55.html