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

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
}