asp.net 读取Excel数据到DataTable的代码

/// <summary>
/// 获取指定路径、指定工作簿名称的excel数据:取第一个sheet的数据
/// </summary>
/// <param name="filepath">文件存储路径</param>
/// <param name="worksheetname">工作簿名称</param>
/// <returns>如果争取找到了数据会返回一个完整的table，否则返回异常</returns>
public datatable getexceldata(string astrfilename)
{
string strsheetname = getexcelworksheets(astrfilename)[0].tostring();
return getexceldata(astrfilename, strsheetname);
}

/// <summary>
/// 返回指定文件所包含的工作簿列表;如果有worksheet，就返回以工作簿名字命名的arraylist，否则返回空
/// </summary>
/// <param name="strfilepath">要获取的excel</param>
/// <returns>如果有worksheet，就返回以工作簿名字命名的arraylist，否则返回空</returns>
public arraylist getexcelworksheets(string strfilepath)
{
arraylist altables = new arraylist();
oledbconnection odn = new oledbconnection(getexcelconnection(strfilepath));
odn.open();
datatable dt = new datatable();
dt = odn.getoledbschematable(oledbschemaguid.tables, null);
if (dt == null)
{
throw new exception("无法获取指定excel的架构。");
}
foreach (datarow dr in dt.rows)
{
string tempname = dr["table_name"].tostring();
int idolarindex = tempname.indexof('$');
if (idolarindex > 0)
{
tempname = tempname.substring(0, idolarindex);
}
//修正了excel2003中某些工作薄名称为汉字的表无法正确识别的bug。
if (tempname[0] == '\'')
{
if (tempname[tempname.length - 1] == '\'')
{
tempname = tempname.substring(1, tempname.length - 2);
}
else
{
tempname = tempname.substring(1, tempname.length - 1);
}
}
if (!altables.contains(tempname))
{
altables.add(tempname);
}
}
odn.close();
if (altables.count == 0)
{
return null;
}
return altables;
}

/// <summary>
/// 获取指定路径、指定工作簿名称的excel数据
/// </summary>
/// <param name="filepath">文件存储路径</param>
/// <param name="worksheetname">工作簿名称</param>
/// <returns>如果争取找到了数据会返回一个完整的table，否则返回异常</returns>
public datatable getexceldata(string filepath, string worksheetname)
{
datatable dtexcel = new datatable();
oledbconnection con = new oledbconnection(getexcelconnection(filepath));
oledbdataadapter adapter = new oledbdataadapter("select * from [" + worksheetname + "$]", con);
//读取
con.open();
adapter.fillschema(dtexcel, schematype.mapped);
adapter.fill(dtexcel);
con.close();
dtexcel.tablename = worksheetname;
//返回
return dtexcel;
}

/// <summary>
/// 获取链接字符串
/// </summary>
/// <param name="strfilepath"></param>
/// <returns></returns>
public string getexcelconnection(string strfilepath)
{
if (!file.exists(strfilepath))
{
throw new exception("指定的excel文件不存在！");
}
return "provider=microsoft.jet.oledb.4.0;data source=" + strfilepath + ";extended properties=\"excel 8.0;imex=1;hdr=yes;\"";
//@"provider=microsoft.jet.oledb.4.0;" +
//@"data source=" + strfilepath + ";" +
//@"extended properties=" + convert.tochar(34).tostring() +
//@"excel 8.0;" + "imex=1;hdr=yes;" + convert.tochar(34).tostring();
}