Connecting
to a Microsoft Excel Workbook (ADO.NET)
You can connect to a Microsoft Excel
workbook using the OLE DB .NET data provider. The OLE DB connection uses the
Microsoft.ACE.OLEDB.12.0,
which is the new Access database engine OLE DB driver that can also read previous versions of
Microsoft Excel workbooks. The Jet OLE DB driver cannot access Microsoft Excel 2007 workbooks.
Following example shows how to connect to an Excel Workbook and fill data into
a DataSet.
using System;
using System.Data.OleDb;
using System.Data;
namespace ExcelDataSource
{
class Program
{
static void Main(string[] args)
{
string Filepath = @"..\..\ExcelDb.xlsx";
string conxString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Filepath + ";Extended Properties=Excel 12.0 Xml";
using (OleDbConnection connection = new OleDbConnection(conxString))
{
connection.Open();
DataTable ExcelSheets = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
DataSet objDataSet = new DataSet();
for (int i = 0; i < ExcelSheets.Rows.Count; i++)
{
objDataSet = (DataSet)FillDataSet(connection, objDataSet, "[" + ExcelSheets.Rows[i]["TABLE_NAME"].ToString() + "]", ExcelSheets.Rows[i]["TABLE_NAME"].ToString());
}
}
}
private static object FillDataSet(OleDbConnection objConnection, DataSet objDataSet, string strSheetName, string strTableName)
{
try
{
string strCommand = "Select * from " + strSheetName;
OleDbDataAdapter objExcelData = new OleDbDataAdapter(strCommand, objConnection);
objExcelData.Fill(objDataSet, strTableName);
return objDataSet;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
}
}
}
|
In Debugging mode your DataSet will look like this.
