|
马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。
您需要 登录 才可以下载或查看,没有帐号?立即注册
x
在经过全球个人PC市场占有90%的微软对asp.net不断优化与整合后,asp.net与微软自身平台的动用上更加的高效,加上asp.net在应用上非常容易上手,相信asp.net仍会是最多客户选用的脚本语言,并会在未来几年继续领跑。复制代码代码以下:
publicpartialclassExcelHelper:IDisposable
{
#regionFileds
privatestring_excelObject="Provider=Microsoft.{0}.OLEDB.{1};DataSource={2};ExtendedProperties="Excel{3};HDR={4};IMEX={5}"";
privatestring_filepath=string.Empty;
privatestring_hdr="No";
privatestring_imex="1";
privateOleDbConnection_con=null;
#endregion
#regionCtor
publicExcelHelper(stringfilePath)
{
this._filepath=filePath;
}
#endregion
#regionProperties
///<summary>
///猎取毗连字符串
///</summary>
publicstringConnectionString
{
get
{
stringresult=string.Empty;
if(String.IsNullOrEmpty(this._filepath))
returnresult;
//反省文件格局
FileInfofi=newFileInfo(this._filepath);
if(fi.Extension.Equals(".xls"))
{
result=string.Format(this._excelObject,"Jet","4.0",this._filepath,"8.0",this._hdr,this._imex);
}
elseif(fi.Extension.Equals(".xlsx"))
{
result=string.Format(this._excelObject,"Ace","12.0",this._filepath,"12.0",this._hdr,this._imex);
}
returnresult;
}
}
///<summary>
///猎取毗连
///</summary>
publicOleDbConnectionConnection
{
get
{
if(_con==null)
{
this._con=newOleDbConnection();
this._con.ConnectionString=this.ConnectionString;
}
returnthis._con;
}
}
///<summary>
///HDR
///</summary>
publicstringHdr
{
get{returnthis._hdr;}
set{this._hdr=value;}
}
///<summary>
///IMEX
///</summary>
publicstringImex
{
get{returnthis._imex;}
set{this._imex=value;}
}
#endregion
#regionMethods
///<summary>
///Getsaschema
///</summary>
///<returns>Schema</returns>
publicDataTableGetSchema()
{
DataTabledtSchema=null;
if(this.Connection.State!=ConnectionState.Open)this.Connection.Open();
dtSchema=this.Connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null,null,null,"TABLE"});
returndtSchema;
}
privatestringGetTableName()
{
stringtableName=string.Empty;
DataTabledt=GetSchema();
for(inti=0;i<dt.Rows.Count;i++)
{
tableName+=dt.Rows[2].ToString().Trim();
}
returntableName.Substring(0,tableName.Length-1);
}
publicDataTableReadTable()
{
returnthis.ReadTable(GetTableName(),ExcelHelperReadTableMode.ReadFromWorkSheet);
}
///<summary>
///Readalltablerows
///</summary>
///<paramname="tableName">TableName</param>
///<returns>Table</returns>
publicDataTableReadTable(stringtableName)
{
returnthis.ReadTable(tableName,ExcelHelperReadTableMode.ReadFromWorkSheet);
}
///<summary>
///Readtable
///</summary>
///<paramname="tableName">TableName</param>
///<paramname="mode">Readmode</param>
///<returns>Table</returns>
publicDataTableReadTable(stringtableName,ExcelHelperReadTableModemode)
{
returnthis.ReadTable(tableName,mode,"");
}
///<summary>
///Readtable
///</summary>
///<paramname="tableName">TableName</param>
///<paramname="mode">Readmode</param>
///<paramname="criteria">Criteria</param>
///<returns>Table</returns>
publicDataTableReadTable(stringtableName,ExcelHelperReadTableModemode,stringcriteria)
{
if(this.Connection.State!=ConnectionState.Open)
{
this.Connection.Open();
}
stringcmdText="Select*From[{0}]";
if(!string.IsNullOrEmpty(criteria))
{
cmdText+="Where"+criteria;
}
stringtableNameSuffix=string.Empty;
if(mode==ExcelHelperReadTableMode.ReadFromWorkSheet)
tableNameSuffix="$";
OleDbCommandcmd=newOleDbCommand(string.Format(cmdText,tableName+tableNameSuffix));
cmd.Connection=this.Connection;
OleDbDataAdapteradpt=newOleDbDataAdapter(cmd);
DataSetds=newDataSet();
adpt.Fill(ds,tableName);
if(ds.Tables.Count>=1)
{
returnds.Tables[0];
}
else
{
returnnull;
}
}
///<summary>
///Droptable
///</summary>
///<paramname="tableName">TableName</param>
publicvoidDropTable(stringtableName)
{
if(this.Connection.State!=ConnectionState.Open)
{
this.Connection.Open();
}
stringcmdText="DropTable[{0}]";
using(OleDbCommandcmd=newOleDbCommand(string.Format(cmdText,tableName),this.Connection))
{
cmd.ExecuteNonQuery();
}
this.Connection.Close();
}
///<summary>
///Writetable
///</summary>
///<paramname="tableName">TableName</param>
///<paramname="tableDefinition">TableDefinition</param>
publicvoidWriteTable(stringtableName,Dictionary<string,string>tableDefinition)
{
using(OleDbCommandcmd=newOleDbCommand(this.GenerateCreateTable(tableName,tableDefinition),this.Connection))
{
if(this.Connection.State!=ConnectionState.Open)this.Connection.Open();
cmd.ExecuteNonQuery();
}
}
///<summary>
///Addnewrow
///</summary>
///<paramname="dr">DataRow</param>
publicvoidAddNewRow(DataRowdr)
{
stringcommand=this.GenerateInsertStatement(dr);
ExecuteCommand(command);
}
///<summary>
///Executenewcommand
///</summary>
///<paramname="command">Command</param>
publicvoidExecuteCommand(stringcommand)
{
using(OleDbCommandcmd=newOleDbCommand(command,this.Connection))
{
if(this.Connection.State!=ConnectionState.Open)this.Connection.Open();
cmd.ExecuteNonQuery();
}
}
///<summary>
///Generatescreatetablescript
///</summary>
///<paramname="tableName">TableName</param>
///<paramname="tableDefinition">TableDefinition</param>
///<returns>Createtablescript</returns>
privatestringGenerateCreateTable(stringtableName,Dictionary<string,string>tableDefinition)
{
StringBuildersb=newStringBuilder();
boolfirstcol=true;
sb.AppendFormat("CREATETABLE[{0}](",tableName);
firstcol=true;
foreach(KeyValuePair<string,string>keyvalueintableDefinition)
{
if(!firstcol)
{
sb.Append(",");
}
firstcol=false;
sb.AppendFormat("{0}{1}",keyvalue.Key,keyvalue.Value);
}
sb.Append(")");
returnsb.ToString();
}
///<summary>
///Generatesinsertstatementscript
///</summary>
///<paramname="dr">Datarow</param>
///<returns>Insertstatementscript</returns>
privatestringGenerateInsertStatement(DataRowdr)
{
StringBuildersb=newStringBuilder();
boolfirstcol=true;
sb.AppendFormat("INSERTINTO[{0}](",dr.Table.TableName);
foreach(DataColumndcindr.Table.Columns)
{
if(!firstcol)
{
sb.Append(",");
}
firstcol=false;
sb.Append(dc.Caption);
}
sb.Append(")VALUES(");
firstcol=true;
for(inti=0;i<=dr.Table.Columns.Count-1;i++)
{
if(!object.ReferenceEquals(dr.Table.Columns.DataType,typeof(int)))
{
sb.Append("");
sb.Append(dr.ToString().Replace("",""));
sb.Append("");
}
else
{
sb.Append(dr.ToString().Replace("",""));
}
if(i!=dr.Table.Columns.Count-1)
{
sb.Append(",");
}
}
sb.Append(")");
returnsb.ToString();
}
///<summary>
///Dispose[完成IDispose接口]
///</summary>
publicvoidDispose()
{
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
}
我有个同学,他是搞Java的,他给我说“Java不是效率低,而是速度慢。”,我不是搞Java的,我实在想不透这句话的含义,难道执行速度不就是效率低吗?难道执行速度慢还成效率高了? |
|