I faced this same problem recently...I wasn't able to really find an out-of-the-box solution that would do this for me, so I ended up writing some code by hand.
I actually have two different solutions in my code library: one uses OLEDB and the other uses Excel Interop. From your question, I'm guessing you've been attempting to use an interop solution, and it's too slow. If all you want to do is read tables directly from Excel, where there is one table per worksheet, the OLEDB solution is probably cleaner and faster. If you ever get to the point where you need to write to an Excel file, though, you may find that OLEDB is just too limited. That was my experience for one project.
Anyway, I'll post a few bits and pieces from my OLEDB solution that will hopefully get you started:
public void FillDataTables(DataSet dataSet)
{
var worksheetNames = GetWorksheetNames();
foreach (DataTable table in dataSet.Tables)
{
if (!worksheetNames.Contains(table.TableName + "$")) continue;
FillDataTable(table);
}
}
public void FillDataTable(DataTable table)
{
using (var connection = ConnectionProvider.GetNew())
{
var adapter = GetNewDataAdapter(table, connection);
adapter.Fill(table);
}
}
private IEnumerable<string> GetWorksheetNames()
{
return GetSchemaTable()
.AsEnumerable()
.Select(dr => dr.Field<string>("TABLE_NAME"));
}
public DataTable GetSchemaTable()
{
using (var connection = (OleDbConnection)ConnectionProvider.GetNew())
{
connection.Open();
return connection.GetSchema("Tables");
}
}
Here is the code for the ExcelConnectionProvider:
public class ExcelConnectionProvider : IConnectionProvider
{
private readonly string _connectionString;
private readonly string _workbookPath;
public string ConnectionString
{
get { return _connectionString; }
}
public string WorkbookPath
{
get { return _workbookPath; }
}
public ExcelConnectionProvider(string workbookPath)
{
_workbookPath = workbookPath;
_connectionString = GetConnectionString();
}
private string GetConnectionString()
{
var builder = new OleDbConnectionStringBuilder { Provider = "Microsoft.ACE.OLEDB.12.0", DataSource = _workbookPath };
builder.Add("Extended Properties", "Excel 12.0 Xml;HDR=Yes");
return builder.ConnectionString;
}
public DbConnection GetNew()
{
return new OleDbConnection(_connectionString);
}
}
If you run into any problems with this compiling, try including the "System.Data.DataSetExtensions" assembly in your project.
EDIT: : IConnectionProvider is not needed -- it's an interface I added to my library because I have other connection providers besides Excel.