Copy CodeThe code is as follows:
public partial class Excelhelper:idisposable
{
#region Fileds
private string _excelobject = "Provider=microsoft." {0}. OLE DB. {1};D ata 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>
Get connection string
</summary>
public string ConnectionString
{
Get
{
string result = String. Empty;
if (String.IsNullOrEmpty (This._filepath))
return result;
Check file format
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>
Get connection
</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 [Implement IDispose interface]
</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
}
