在项目中经常涉及到基础数据的导入的问题，很多时候就需要从Excel中获取数据，下面提供一个从Excel文件提取数据到DataSet中方法：
/// <summary>
/// 根据excel的文件的路径提取其中表的数据
/// </summary>
/// <param name="Path">Excel文件的路径</param>
/// <returns></returns>
public static DataSet GetDataFromExcel(string Path)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn;
DataSet ds = new DataSet();
try
{
conn = new OleDbConnection(strConn);
conn.Open();
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
string[] strTables = new string[dtSheetName.Rows.Count];
int k;
string temp;
string[] ids = new string[dtSheetName.Rows.Count];
for (k = 0; k < dtSheetName.Rows.Count; k++)
{
temp = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
strTables[k] = temp.Trim('$');
strTableNames[k] = temp;
}
OleDbDataAdapter myCommand = null;
for (k = 0; k < dtSheetName.Rows.Count; k++)
{
DataTable dt = new DataTable();
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[k] + "]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(dt);
dt.TableName = strTables[k];
ds.Tables.Add(dt);
}
conn.Close();
}
catch (Exception ex)
{
//写错误日志...
string strOuput = string.Format("获取文件中数据失败,错误信息:{0},异常{1}\n",ex.Message,ex.InnerException);
//将信息写入到日志输出文件
DllComm.TP_WriteAppLogFileEx(DllComm.g_AppLogFileName, strOuput);
}
return ds;
}