Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to read data from excel file in ASP.NET

Author: Alanna Kremer
Download Source Code : 1283_ReadDataFromExcel.zip

In this article, we will see how to read data from excel file and display it in a GridView Control. You can download complete source code file.

 

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Now Drag and Drop a “GridView” from Data tab in Toolbox or add code below in Default.aspx page.

    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>

     
  6. Import following namespaces at the top in code behind file

    C#
     
    using System.Data;
    using System.Data.OleDb;
     
    VB.NET

    Imports System.Data
    Imports System.Data.OleDb
     
  7. Write below function in code behind file

    C#
     
    private DataTable GetDataFromExcelFile() {
        string conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\My Data\\My File.xlsx;Extended Properties=Excel 12.0";
     
        OleDbConnection conn = new OleDbConnection(conString);
        conn.Open();
           
        string cmdText = "Select * From [Sheet1$]";
        OleDbCommand cmd = new OleDbCommand(cmdText, conn);
           
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        conn.Close();
        return dt;
    }
     
    VB.NET
     
    Private Function GetDataFromExcel() As DataTable
        Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\My Data\My File.xlsx;Extended Properties=Excel 12.0"
     
        Dim conn As New OleDbConnection(conString)
        conn.Open()
     
        Dim cmdText As String = "Select * From [Sheet1$]"
        Dim cmd As New OleDbCommand(cmdText, conn)
        Dim da As New OleDbDataAdapter(cmd)
     
        Dim dt As New DataTable()
        da.Fill(dt)
        conn.Close()
        Return (dt)
    End Function

    GetDataFromExcel() takes no argument and returns DataTable.We are getting data from excel file in this function.
    You have to mention the connection string to get data from excel file. You can provide “Microsoft.Jet.OLEDB” as provider if you are getting data only from excel 2003 but you must have to give provider “Microsoft.ACE.OLEDB” for Excel 2007. You can also get data from excel 2003 by using “ACE” Provider. Make sure that file path is correct in the connection string which you have given as Data Source. I have a file “My File.xlsx”  in “My Data” folder in System Drive “D”. Create a new OleDbConnection and open that connection to read data. I have written a SQL like “SELECT” statement to get data from excel “Sheet1”. Create a new OleDbCommand object by passing command text and connection string. Create a new OleDbDataAdapter object by passing OleDbCommand object. Create a new DataTable object, fill it with OleDbDataAdapter object and return DataTable.
     
  8.  Now write cade below in Page Load method

    C#
     
    protected void Page_Load(object sender, EventArgs e) {
        GridView1.DataSource = GetDataFromExcelFile();
        GridView1.DataBind();
    }
     
    VB.NET
     
    Protected Sub Page_load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        GridView1.DataSource = GetDataFromExcel()
        GridView1.DataBind()
    End Sub
     
  9.  Press F5 to see result in browser

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to check/uncheck all items in CheckBoxList ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net