Here it is how to do it using ODBC and ADO.Net
private void Form1_Load(object sender, EventArgs e)
{
try
{
string sheetName = "Sheet1$";
DataTable sheetTable = loadSingleSheet(@"C:\excelFile.xls", sheetName);
dataGridView1.DataSource = sheetTable;
string sheetNameWithRange = "Sheet1$A1:D10";
DataTable sheetTableWithRange = loadSingleSheet(@"C:\excelFile.xls",sheetNameWithRange);
dataGridView2.DataSource = sheetTableWithRange;
}
catch (Exception Ex)
{
MessageBox.Show(Ex.Message, "");
}
}
private OleDbConnection returnConnection(string fileName)
{
return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Jet OLEDB:Engine Type=5;Extended Properties=\"Excel 8.0;\"");
}
private DataTable loadSingleSheet(string fileName, string sheetName)
{
DataTable sheetData = new DataTable();
using (OleDbConnection conn = this.returnConnection(fileName))
{
conn.Open();
OleDbDataAdapter sheetAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
sheetAdapter.Fill(sheetData);
}
return sheetData;
}