c#读取excel文件的代码,支持多工作表
public DataSet ExcelToDS(string fileName)
{
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ fileName +";"+"Extended Properties=Excel 8.0;";
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable tblSchema = null;
ArrayList tblNames = new ArrayList();
// 初始化连接,并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in tblSchema.Rows)
{
tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
}
// 初始化适配器
da = new OleDbDataAdapter();
// 准备数据,导入DataSet
DataSet ds = new DataSet();
foreach (string tblName in tblNames)
{
da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
try
{
da.Fill(ds, tblName);
}
catch
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
throw;
}
}
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return ds;
}
{
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ fileName +";"+"Extended Properties=Excel 8.0;";
string sql_F = "Select * FROM [{0}]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable tblSchema = null;
ArrayList tblNames = new ArrayList();
// 初始化连接,并打开
conn = new OleDbConnection(connStr);
conn.Open();
// 获取数据源的表定义元数据
tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
foreach (DataRow row in tblSchema.Rows)
{
tblNames.Add((string)row["TABLE_NAME"]); // 读取表名
}
// 初始化适配器
da = new OleDbDataAdapter();
// 准备数据,导入DataSet
DataSet ds = new DataSet();
foreach (string tblName in tblNames)
{
da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);
try
{
da.Fill(ds, tblName);
}
catch
{
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
throw;
}
}
// 关闭连接
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
return ds;
}