This is from VB.net but not sure how well it translates, returns a list of strings containing all sheet names:
OleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
MSDN Link
Once you have a list of sheet names you can do a simple For Each
loop to iterate.
Edit:
This should work in C#
Add function
static DataTable GetSchemaTable(string connectionString)
{
using (OleDbConnection connection = new
OleDbConnection(connectionString))
{
connection.Open();
DataTable schemaTable = connection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
return schemaTable;
}
}
Your code would change to:
DataSet ds = new DataSet();
var excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", path);
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;
DataTable sheets = GetSchemaTable(excelConnectionString);
foreach (dataRow r in sheets.rows)
{
string query = "SELECT * FROM [" + r.Item(0).ToString + "]";
ds.Clear();
OleDbDataAdapter data = new OleDbDataAdapter(query, connection);
data.Fill(ds);
}
Just be sure to do something with ds
after each iteration.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…