How to access Microsoft Office 2007 Excel files using ADO.NET

using System.Data;
using System.Data.OleDb;

namespace Sample.Business
{
public class ExcelDataAccess : IDisposable
{
public DataSet GetData(string FilePath)
{
string excelConnectionstring = "Provider = \"Microsoft.ACE.OLEDB.12.0\";"
+ "Data Source = \"" + FilePath +
"\";Extended Properties = \"Excel 12.0;HDR = YES\""
;
string excelCommand = "SELECT * FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection(excelConnectionstring);
DataSet ds = new DataSet();
OleDbDataAdapter excelDA = new OleDbDataAdapter(excelCommand, conn);
try
{
conn.Open();
excelDA.Fill(ds);
}
catch
{
}
finally
{
conn.Close();
excelDA.Dispose();
}

return ds;
}

public void Dispose()
{
}
}
}

I have created a simple Default.aspx page to display the contents of the Excel file.

And here's the C# code for the code behind page. Do not forget to include the assembly that contains the GetData() method on your Default.aspx.cs page.

using Sample.Business;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if( Page.IsPostBack)
{
string filePath = TextBox1.Text.Trim();
ExcelDataAccess excelData = new ExcelDataAccess();
GridView1.DataSource = excelData.GetData(filePath);
GridView1.DataBind();

}
}
}

And you can then compile and run your application to see the results.

Note, however, if the above code gives a run time error, while attemtping to connect to the xslx file, I would recommend you do one additional step.

Install the data access component for Microsoft Office 2007 from msdn. Here's the link to it:

And make sure you copy the executable on your local hard drive ( C:\ for example) and run it from there. After the installation is complete, go to Control Panel -> Add/Remove programs and see if the Office 2007 Data Access component is installed successfully.

That's all, re compile your code and now you can access Microsoft Office 2007 excel files ( *.xslx ) from your ASP.NET Web application.

Cheers !

-dhumil