asp.net中操作Excel助手相关代码

时间:2022-06-01 16:15:38
代码如下:

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