Note: Using this method you do not have to worry about the format in Excel. The procedure will extract and import Excel data, as it is, and bind it to a GridView control.
How to import data from Ms-Word tables to a GridView in Asp.Net C# and VB
The data binding process is very simple. We’ll first extract the data from an Excel file using methods and properties provided by the OleDb class. The namespace System.Data.OleDb provides the methods and you will need to add it in your code behind section. It provides simple methods to set connection with the Excel file, extract data from the sheet and later using a datareader you can read or share the data with other objects.
Once the datareader gets the data, we’ll add a DataTable object and load the extracted data to the DataTable. Finally, all you have to do is, bind the DataTable object to the GridView control.
In the markup section, we will add a FileUpload control, a button and a GridView control. The Grid is bound to nothing, since we will bind it with data from Excel using code behind procedures. The button’s click event will call a procedure to extract and populate data to the grid.
<!DOCTYPE html> <html> <head> <title>Import Data from Excel and Bind Data to GridView</title> </head> <body> <form runat="server"> <div> <div> <div> <!-- ADD A FILE UPLOAD CONTROL AND A BUTTON TO EXECUTE. --> Select a file: <asp:FileUpload ID="FileUpload" runat="server" /> <p> <input type="button" onserverclick="PopulateGrid" value="Populate Grid" runat="server" /> </p> </div> <!-- ADD A GRIDVIEW CONTROL. --> <div> <asp:GridView ID="GridView1" CssClass="Grid" runat="server"> </asp:GridView> <p><asp:Label id="lblConfirm" runat="server"></asp:Label></p> </div> </div> </div> </form> </body> </html>
using System; using System.Data; // FOR "DataTable". using System.Data.OleDb; public partial class SiteMaster : System.Web.UI.MasterPage { protected void PopulateGrid(object sender, EventArgs e) { // CHECK IF A FILE HAS BEEN SELECTED. if ((FileUpload.HasFile)) { if (!Convert.IsDBNull(FileUpload.PostedFile) & FileUpload.PostedFile.ContentLength > 0) { // SAVE THE SELECTED FILE IN THE ROOT DIRECTORY. FileUpload.SaveAs(Server.MapPath(".") + "\\" + FileUpload.FileName); // SET A CONNECTION WITH THE EXCEL FILE. OleDbConnection myExcelConn = new OleDbConnection ("Provider=Microsoft.ACE.OLEDB.12.0; " + "Data Source=" + Server.MapPath(".") + "\\" + FileUpload.FileName + ";Extended Properties=Excel 12.0;"); try { myExcelConn.Open(); // GET DATA FROM EXCEL SHEET. OleDbCommand objOleDB = new OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn); // READ THE DATA EXTRACTED FROM THE EXCEL FILE. OleDbDataReader objBulkReader = null; objBulkReader = objOleDB.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(objBulkReader); // FINALLY, BIND THE EXTRACTED DATA TO THE GRIDVIEW. GridView1.DataSource = dt; GridView1.DataBind(); lblConfirm.Text = "DATA IMPORTED TO THE GRID, SUCCESSFULLY."; lblConfirm.Attributes.Add("style", "color:green"); } catch (Exception ex) { // SHOW ERROR MESSAGE, IF ANY. lblConfirm.Text = ex.Message; lblConfirm.Attributes.Add("style", "color:red"); } finally { // CLEAR. myExcelConn.Close(); myExcelConn = null; } } } } }
How to Export Data from a Paging Enabled GridView to Excel in Asp.Net C# and Vb.Net
Option Explicit On Imports System.Data ' FOR "DataTable". Imports System.Data.OleDb Partial Class Site Inherits System.Web.UI.MasterPage Protected Sub PopulateGrid(sender As Object, e As EventArgs) If (FileUpload.HasFile) Then ' CHECK IF ANY FILE HAS BEEN SELECTED. If Not IsDBNull(FileUpload.PostedFile) And _ FileUpload.PostedFile.ContentLength > 0 Then ' SAVE THE SELECTED FILE IN THE ROOT DIRECTORY. FileUpload.SaveAs(Server.MapPath(".") & "\" & FileUpload.FileName) ' SET A CONNECTION WITH THE EXCEL FILE. Dim myExcelConn As OleDbConnection = _ New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ Server.MapPath(".") & "\" & FileUpload.FileName() & _ ";Extended Properties=Excel 12.0;") Try myExcelConn.Open() ' GET DATA FROM EXCEL SHEET. Dim objOleDB As New OleDbCommand("SELECT *FROM [Sheet1$]", myExcelConn) ' READ THE DATA EXTRACTED FROM THE EXCEL FILE. Dim objBulkReader As OleDbDataReader objBulkReader = objOleDB.ExecuteReader Dim dt As DataTable = New DataTable dt.Load(objBulkReader) ' FINALLY, BIND THE EXTRACTED DATA TO THE GRIDVIEW. GridView1.DataSource = dt GridView1.DataBind() lblConfirm.Text = "DATA IMPORTED TO THE GRID, SUCCESSFULLY." lblConfirm.Attributes.Add("style", "color:green") Catch ex As Exception ' SHOW ERROR MESSAGE, IF ANY. lblConfirm.Text = ex.Message lblConfirm.Attributes.Add("style", "color:red") Finally ' CLEAR. myExcelConn.Close() : myExcelConn = Nothing End Try End If End If End Sub End Class
Well, that’s it. Once you have populated the extracted data to the GridView, you can now perform CRUD operations using the data in the grid.