//将指定excel文件中的数据转换成DataTable
/// <summary>
/// 将指定excel文件中的数据转换成DataTable对象,供应用程序进一步处理
/// </summary>
/// <param name="filepath">文件路径</param>
/// <returns></returns>
public static DataTable import(string filepath)
{
DataTable rs = new DataTable();
bool canopen = false;
// 数据连接 HDR=Yes;IMEX=1;
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Persist Security Info=False;" +
"Data Source=" + filepath.ToString() + ";" +
"Extended Properties='Excel 8.0;IMEX=1'");
try//尝试数据连接是否可用
{
conn.Open();
conn.Close();
canopen = true;
}
catch { }
if (canopen)
{
try//如果数据连接可以打开则尝试读入数据
{
OleDbCommand myoledbcommand = new OleDbCommand("select * from [sheet1$]", conn);
OleDbDataAdapter mydata = new OleDbDataAdapter(myoledbcommand);
mydata.Fill(rs);
conn.Close();
}
catch//如果数据连接可以打开但是读入数据失败,则从文件中提取出工作表的名称,再读入数据
{ //获取文件的工作表名
string sheetname = getsheetname(filepath);
if (sheetname.Length > 0)
{
OleDbCommand myoledbcommand = new OleDbCommand("select * from [" + sheetname + "$]", conn);
OleDbDataAdapter mydata = new OleDbDataAdapter(myoledbcommand);
mydata.Fill(rs);
conn.Close();
}
}
}
else
{ // 如果oledb不能连接,就读取文件到流
StreamReader tmpstream;//=File.OpenText(filepath);
tmpstream = new StreamReader(filepath, System.Text.Encoding.GetEncoding("gb2312"));
string tmpstr = tmpstream.ReadToEnd();
tmpstream.Close();
// 过滤表中内容的html字符
rs = getdatatablefromstring(tmpstr);
tmpstr = "";
}
return rs;
}