In this article, we will see how to display data from an Excel spreadsheet using ASP.NET. We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a GridView. Let us get started.
Step 1: Open Visual Studio > File > New >Website > Under Templates, click ASP.NET WebSite and choose either Visual C# or Visual Basic as the language. Select a location and click Ok.
Step 2: We will create two excel sheets and add them to the project. One excel sheet will be created in Office 2003(.xls) and the other one using Office 2007(.xlsx). Add 4 columns called EID, EName, Age and City to the Sheet1. Also add some data into the columns. Once these excel files are created, add them to your project. To add them to the project, right click project > Add Existing Item > Add the two excel files.
Step 3: We will now create a web.config file to store the connection string information. Right click project > Add New Item > Web.config. Add the following entries to the file
As you can observe, the connection string for xlsx (Excel 2007) contains Microsoft.ACE.OLEDB.12.0 as the provider. This is the new Access database engine OLE DB driver and is also capable of reading Excel 2003.
Step 4: Add a GridView to the Default.aspx page. We will extract data from the excel file and bind it to the GridView.
Step 5: Let us now create a connection to the excel file and extract data from it. Before that add a reference toSystem.Data.OleDb;
C#protected void Page_Load(object sender, EventArgs e){string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;// Create the connection objectOleDbConnection oledbConn = new OleDbConnection(connString);try{// Open connectionoledbConn.Open();
// Create OleDbCommand object and select data from worksheet Sheet1OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
// Create new OleDbDataAdapterOleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
// Create a DataSet which will hold the data extracted from the worksheet.DataSet ds = new DataSet();
// Fill the DataSet from the data extracted from the worksheet.oleda.Fill(ds, "Employees");
// Bind the data to the GridViewGridView1.DataSource = ds.Tables[0].DefaultView;GridView1.DataBind();}catch{}finally{// Close connectionoledbConn.Close();}
}
VB.NET
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)Dim connString As String = ConfigurationManager.ConnectionStrings("xls").ConnectionString' Create the connection objectDim oledbConn As OleDbConnection = New OleDbConnection(connString)Try' Open connectionoledbConn.Open()
' Create OleDbCommand object and select data from worksheet Sheet1Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)
' Create new OleDbDataAdapterDim oleda As OleDbDataAdapter = New OleDbDataAdapter()
oleda.SelectCommand = cmd
' Create a DataSet which will hold the data extracted from the worksheet.Dim ds As DataSet = New DataSet()
' Fill the DataSet from the data extracted from the worksheet.oleda.Fill(ds, "Employees")
' Bind the data to the GridViewGridView1.DataSource = ds.Tables(0).DefaultViewGridView1.DataBind()CatchFinally' Close connectionoledbConn.Close()End Try
End Sub
Note:if you want to select a particular column from the excel sheet then you have to assign the name of the column at top of every column. Do not use the excel column name like A1, B1 etc...
All set!! Run the application and see the data getting displayed in the GridView. If you want to target the Excel 2007 sheet, just change xls to xlsx in the ConfigurationManager.ConnectionString.
