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 != ConnectionState.Open) 
{ 
this.Connection.Open(); 
} 
string cmdText = "Select * From [{0}]"; 
if (!string.IsNullOrEmpty(criteria)) 
{ 
cmdText += " Where " + criteria; 
} 
string tableNameSuffix = string.Empty; 
if (mode == ExcelHelperReadTableMode.ReadFromWorkSheet) 
tableNameSuffix = "$"; 
OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName + tableNameSuffix)); 
cmd.Connection = this.Connection; 
OleDbDataAdapter adpt = new OleDbDataAdapter(cmd); 
DataSet ds = new DataSet(); 
adpt.Fill(ds, tableName); 
if (ds.Tables.Count >= 1) 
{ 
return ds.Tables[0]; 
} 
else 
{ 
return null; 
} 
} 
/// <summary> 
/// Drop table 
/// </summary> 
/// <param name="tableName">Table Name</param> 
public void DropTable(string tableName) 
{ 
if (this.Connection.State != ConnectionState.Open) 
{ 
this.Connection.Open(); 
} 
string cmdText = "Drop Table [{0}]"; 
using (OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName), this.Connection)) 
{ 
cmd.ExecuteNonQuery(); 
} 
this.Connection.Close(); 
} 
/// <summary> 
/// Write table 
/// </summary> 
/// <param name="tableName">Table Name</param> 
/// <param name="tableDefinition">Table Definition</param> 
public void WriteTable(string tableName, Dictionary<string, string> tableDefinition) 
{ 
using (OleDbCommand cmd = new OleDbCommand(this.GenerateCreateTable(tableName, tableDefinition), this.Connection)) 
{ 
if (this.Connection.State != ConnectionState.Open) this.Connection.Open(); 
cmd.ExecuteNonQuery(); 
} 
} 
/// <summary> 
/// Add new row 
/// </summary> 
/// <param name="dr">Data Row</param> 
public void AddNewRow(DataRow dr) 
{ 
string command = this.GenerateInsertStatement(dr); 
ExecuteCommand(command); 
} 
/// <summary> 
/// Execute new command 
/// </summary> 
/// <param name="command">Command</param> 
public void ExecuteCommand(string command) 
{ 
using (OleDbCommand cmd = new OleDbCommand(command, this.Connection)) 
{ 
if (this.Connection.State != ConnectionState.Open) this.Connection.Open(); 
cmd.ExecuteNonQuery(); 
} 
} 
/// <summary> 
/// Generates create table script 
/// </summary> 
/// <param name="tableName">Table Name</param> 
/// <param name="tableDefinition">Table Definition</param> 
/// <returns>Create table script</returns> 
private string GenerateCreateTable(string tableName, Dictionary<string, string> tableDefinition) 
{ 
StringBuilder sb = new StringBuilder(); 
bool firstcol = true; 
sb.AppendFormat("CREATE TABLE [{0}](", tableName); 
firstcol = true; 
foreach (KeyValuePair<string, string> keyvalue in tableDefinition) 
{ 
if (!firstcol) 
{ 
sb.Append(","); 
} 
firstcol = false; 
sb.AppendFormat("{0} {1}", keyvalue.Key, keyvalue.Value); 
} 
sb.Append(")"); 
return sb.ToString(); 
} 
/// <summary> 
/// Generates insert statement script 
/// </summary> 
/// <param name="dr">Data row</param> 
/// <returns>Insert statement script</returns> 
private string GenerateInsertStatement(DataRow dr) 
{ 
StringBuilder sb = new StringBuilder(); 
bool firstcol = true; 
sb.AppendFormat("INSERT INTO [{0}](", dr.Table.TableName); 
foreach (DataColumn dc in dr.Table.Columns) 
{ 
if (!firstcol) 
{ 
sb.Append(","); 
} 
firstcol = false; 
sb.Append(dc.Caption); 
} 
sb.Append(") VALUES("); 
firstcol = true; 
for (int i = 0; i <= dr.Table.Columns.Count - 1; i++) 
{ 
if (!object.ReferenceEquals(dr.Table.Columns[i].DataType, typeof(int))) 
{ 
sb.Append("'"); 
sb.Append(dr[i].ToString().Replace("'", "''")); 
sb.Append("'"); 
} 
else 
{ 
sb.Append(dr[i].ToString().Replace("'", "''")); 
} 
if (i != dr.Table.Columns.Count - 1) 
{ 
sb.Append(","); 
} 
} 
sb.Append(")"); 
return sb.ToString(); 
} 
/// <summary> 
/// Dispose [实现IDispose接口] 
/// </summary> 
public void Dispose() 
{ 
if (this._con != null && this._con.State == ConnectionState.Open) 
this._con.Close(); 
if (this._con != null) 
this._con.Dispose(); 
this._con = null; 
this._filepath = string.Empty; 
} 
#endregion 
} 

 
 




下载本文