复制代码 代码如下:

/// <summary>

/// EXCEL数据转换DataSet

/// </summary>

/// <param name="filePath">文件全路径</param>

/// <param name="search">表名</param>

/// <returns></returns>

private DataSet GetDataSet(string fileName)

{

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';";

OleDbConnection objConn = null;

objConn = new OleDbConnection(strConn);

objConn.Open();

DataSet ds = new DataSet();

//List<string> List = new List<string> { "收款金额", "代付关税", "垫付费用", "超期", "到账利润" };

List<string> List = new List<string> { };

DataTable dtSheetName = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

foreach (DataRow dr in dtSheetName.Rows)

{

if (dr["Table_Name"].ToString().Contains("$") && !dr[2].ToString().EndsWith("$"))

{

continue;

}

string s = dr["Table_Name"].ToString();

List.Add(s);

}

try

{

for (int i = 0; i < List.Count; i++)

{

ds.Tables.Add(List[i]);

string SheetName = List[i];

string strSql = "select * from [" + SheetName + "]";

OleDbDataAdapter odbcCSVDataAdapter = new OleDbDataAdapter(strSql, objConn);

DataTable dt = ds.Tables[i];

odbcCSVDataAdapter.Fill(dt);

}

return ds;

}

catch (Exception ex)

{

return null;

}

finally

{

objConn.Close();

objConn.Dispose();

}

}