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.
<html
xmlns="
http://www.w3.org/1999/xhtml">
<head
runat="server">
<title></title>
</head>
<body>
<form
id="form1"
runat="server">
<div>
<asp:Label
ID="Label1"
runat="server"
Text="Please enter the Excel 2007 file path here."></asp:Label>
<br />
<asp:TextBox
ID="TextBox1"
runat="server"></asp:TextBox> <asp:Button
ID="Button1"
runat="server"
Text="Submit" />
<br />
<asp:GridView
ID="GridView1"
runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
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