不废话,直接上代码。
public DataSet GetTable(string fileName)
{
OleDbConnection Conn = null;
DataTable dt = null;
string connString = string.Empty;
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dataTable = new DataTable();
DataSet DS = new DataSet();
try
{
string FileType = fileName.Substring(fileName.LastIndexOf("."));
if (FileType == ".xls")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
else//.xlsx
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
// 创建连接对象
Conn = new OleDbConnection(connString);
// 打开数据库连接
Conn.Open();
//获取Excel工作薄中Sheet页(工作表)名集合
DataTable ss = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
string sql_F = "Select * FROM [数据$]";
//for (int i = 0; i < ss.Rows.Count; i++)
//{
da.SelectCommand = new OleDbCommand(sql_F, Conn);
da.Fill(dataTable);
DS.Merge(dataTable);
return DS;
}
catch (Exception ex)
{
return new DataSet();
}
finally
{
// 释放
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}