用C#读取Excel返回DataSet

最新推荐文章于 2024-09-23 14:52:29 发布

kfzco2

最新推荐文章于 2024-09-23 14:52:29 发布

阅读量493

点赞数 1

文章标签： dataset excel c# string properties header

本文链接：https://blog.csdn.net/kfzco2/article/details/6567669

版权

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

复制代码

using System;

using System.Collections.Generic;

复制代码

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