public partial class ExcelHelper : IDisposable
{
#region Fileds
private string _excelObject = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties=\"Excel {3};HDR={4};IMEX={5}\"";
private string _filepath = string.Empty;
private string _hdr = "No";
private string _imex = "1";
private OleDbConnection _con = null;
#endregion
#region Ctor
public ExcelHelper(string filePath)
{
this._filepath = filePath;
}
#endregion
#region Properties
/// <summary>
/// 获取连接字符串
/// </summary>
public string ConnectionString
{
get
{
string result = string.Empty;
if (String.IsNullOrEmpty(this._filepath))
return result;
//检查文件格式
FileInfo fi = new FileInfo(this._filepath);
if (fi.Extension.Equals(".xls"))
{
result = string.Format(this._excelObject, "Jet", "4.0", this._filepath, "8.0", this._hdr, this._imex);
}
else if (fi.Extension.Equals(".xlsx"))
{
result = string.Format(this._excelObject, "Ace", "12.0", this._filepath, "12.0", this._hdr, this._imex);
}
return result;
}
}
/// <summary>
/// 获取连接
/// </summary>
public OleDbConnection Connection
{
get
{
if (_con == null)
{
this._con = new OleDbConnection();
this._con.ConnectionString = this.ConnectionString;
}
return this._con;
}
}
/// <summary>
/// HDR
/// </summary>
public string Hdr
{
get { return this._hdr; }
set { this._hdr = value; }
}
/// <summary>
/// IMEX
/// </summary>
public string Imex
{
get { return this._imex; }
set { this._imex = value; }
}
#endregion
#region Methods
/// <summary>
/// Gets a schema
/// </summary>
/// <returns>Schema</returns>
public DataTable GetSchema()
{
DataTable dtSchema = null;
if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
dtSchema = this.Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
return dtSchema;
}
private string GetTableName()
{
string tableName = string.Empty;
DataTable dt = GetSchema();
for (int i = 0; i < dt.Rows.Count; i++)
{
tableName += dt.Rows[i][2].ToString().Trim();
}
return tableName.Substring(0, tableName.Length - 1);
}
public DataTable ReadTable()
{
return this.ReadTable(GetTableName(), ExcelHelperReadTableMode.ReadFromWorkSheet);
}
/// <summary>
/// Read all table rows
/// </summary>
/// <param name="tableName">Table Name</param>
/// <returns>Table</returns>
public DataTable ReadTable(string tableName)
{
return this.ReadTable(tableName, ExcelHelperReadTableMode.ReadFromWorkSheet);
}
/// <summary>
/// Read table
/// </summary>
/// <param name="tableName">Table Name</param>
/// <param name="mode">Read mode</param>
/// <returns>Table</returns>
public DataTable ReadTable(string tableName, ExcelHelperReadTableMode mode)
{
return this.ReadTable(tableName, mode, "");
}
/// <summary>
/// Read table
/// </summary>
/// <param name="tableName">Table Name</param>
/// <param name="mode">Read mode</param>
/// <param name="criteria">Criteria</param>
/// <returns>Table</returns>
public DataTable ReadTable(string tableName, ExcelHelperReadTableMode mode, string criteria)
{
if (this.Connection.State !