The Excel assistant code in asp.net

Copy codeThe Code is as follows:
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>
/// Obtain the connection string
/// </Summary>
Public string ConnectionString
{
Get
{
String result = string. Empty;
If (String. IsNullOrEmpty (this. _ filepath ))
Return result;
// Check the 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>
/// Obtain the 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, "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 plain text = "Select * From [{0}]";
If (! String. IsNullOrEmpty (criteria ))
{
Plain text + = "Where" + criteria;
}
String tableNameSuffix = string. Empty;
If (mode = ExcelHelperReadTableMode. ReadFromWorkSheet)
TableNameSuffix = "$ ";
OleDbCommand cmd = new OleDbCommand (string. Format (plain text, 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 literal text = "Drop Table [{0}]";
Using (OleDbCommand cmd = new OleDbCommand (string. Format (plain text, 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 the 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
}