C#
A neat and tidy, minimal version that provides a dataset with tables named as per the sheets (without the trailing dollar):
private static OleDbConnection GetConnection(string filename, bool openIt)
{
// if your data has no header row, change HDR=NO
var c = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{filename}';Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");
if (openIt)
c.Open();
return c;
}
private static DataSet GetExcelFileAsDataSet(OleDbConnection conn)
{
var sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new[] { default, default, default, "TABLE" });
var ds = new DataSet();
foreach (DataRow r in sheets.Rows)
ds.Tables.Add(GetExcelSheetAsDataTable(conn, r["TABLE_NAME"].ToString()));
return ds;
}
private static DataTable GetExcelSheetAsDataTable(OleDbConnection conn, string sheetName)
{
using (var da = new OleDbDataAdapter($"select * from [{sheetName}]", conn))
{
var dt = new DataTable() { TableName = sheetName.TrimEnd('$') };
da.Fill(dt);
return dt;
}
}
Use it like:
DataSet ds;
using(c = GetConnection(@"C:\path\to\your\xl.xlsx", true)
ds = GetExcelFileAsDataSet(c);
Or if you only want one table and you know all the exact sheet name you want (remember that they have a dollar on the end):
DataTable dt;
using(c = GetConnection(@"C:\path\to\your\xl.xlsx", true)
dt = GetExcelSheetAsDataTable(c, "Sheet1$");
VB.NET
Bonus! Note: needs a modern version of VB that understands things like string interpolation, With etc
Private Shared Function GetConnection(filename As String, openIt As Boolean) As OleDbConnection
'if your data has no header row, change HDR=NO
Dim c = New OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{filename}';Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"" ")
If openIt Then c.Open()
Return c
End Function
Private Shared Function GetExcelFileAsDataSet(conn As OleDbConnection) As DataSet
Dim sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, {Nothing, Nothing, Nothing, "TABLE"})
Dim ds = New DataSet()
For Each r As DataRow In sheets.Rows
ds.Tables.Add(GetExcelSheetAsDataTable(conn, r("TABLE_NAME").ToString()))
Next
Return ds
End Function
Private Shared Function GetExcelSheetAsDataTable(conn As OleDbConnection, sheetName As String) As DataTable
Using da = New OleDbDataAdapter($"select * from [{sheetName}]", conn)
Dim dt = New DataTable() With {.TableName = sheetName.TrimEnd("$"c)}
da.Fill(dt)
Return dt
End Using
End Function
Use it like:
Dim ds As DataSet
Using c = GetConnection("C:\path\to\your\xl.xlsx", True)
ds = GetExcelFileAsDataSet(c)
End Using 'closes connection
Or for a single sheet (Remember sheet names end in a dollar) whose name you know:
Dim dt As DataTable
Using c = GetConnection("C:\path\to\your\xl.xlsx", True)
dt = GetExcelSheetAsDataTable(c, "Sheet1$")
End Using 'closes connection
