不添加任何 excel dll 引用,代码很简单,就不做解释了。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
namespace OCXMLCreater.ExcelProvider
{
public class ExcelHelper
{
//唯一需要解释的一点是这个连接字符串中,HDR=YES 表示此Excel表第一行用于显示字段名称(Header),如果没有字段名,则应 HDR=NO
public static string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\WorkSpace\MyDocument\Samples2.xlsx;Extended Properties=""Excel 8.0;HDR=YES;""";
/// <summary>
/// 读取 Excel 返回 DataSet
/// </summary>
/// <param name="connectionString">Excel 连接字符串</param>
/// <param name="commandString">查询语句, for example:"SELECT ID,userName,userAddress FROM [Sheet1$]" </param>
/// <returns></returns>
public static DataSet GetExcelDataSet(string connectionString, string commandString)
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = commandString; //commandString例如:"SELECT ID,userName,userAddress FROM [Sheet1$]"
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;
DataSet cities = new DataSet();
adapter.Fill(cities);
connection.Close();
return cities;
}
}
}