/// <summary>
/// 读取Execl 返回Dataset
/// </summary>
/// <param name="strConnection">Execl物理路径</param>
/// <returns></returns>
public static DataSet ExeclToDataset(string sConnection)
{
if (sConnection != "")
{
//string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strConnection + ";Extended Properties=Excel 8.0;";
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sConnection + ";Extended Properties= 'Excel 8.0;HDR=YES;IMEX=1;TypeGuessRow=100';";
using (OleDbConnection objConn = new OleDbConnection(sConnectionString))
{
try
{
objConn.Open();
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
DataTable dtSheetName = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//excel中表名的数组
ArrayList array = new ArrayList();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
string strTableName =dtSheetName.Rows[i]["TABLE_NAME"].ToString().Substring(0,dtSheetName.Rows[i]["TABLE_NAME"].ToString().IndexOf("$"));
if (!array.Contains(strTableName))
{
array.Add(strTableName);
}
}
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
DataSet ds = new DataSet();//表集合
for (int k = 0; k < array.Count; k++)
{
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + array[k].ToString() + "$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, array[k].ToString());
objDataset1.Tables[array[k].ToString()].Columns[0].ColumnName = "F1";
ds.Tables.Add(objDataset1.Tables[0].Copy());
//释放资源
objDataset1.Dispose();
objCmdSelect.Dispose();
}
if (ds != null)
{
return ds;
}
return null;
}
catch (System.Data.OleDb.OleDbException E)
{
objConn.Close();
throw new Exception(E.Message);
}
}
}
else
{
return null;
}
}
卡号 卡类型 旧状态 当前状态 变动时间
aa a b A 2012-1-10
返回的dataset 中 变动时间为 40918 不是正确的2012-1-10
求解释
