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

How to read data from excel file in ASP.NET

Author: Alanna Kremer
Download Source Code :

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">

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

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

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

    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);
        string cmdText = "Select * From [Sheet1$]";
        OleDbCommand cmd = new OleDbCommand(cmdText, conn);
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        DataTable dt = new DataTable();
        return dt;
    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)
        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()
        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

    protected void Page_Load(object sender, EventArgs e) {
        GridView1.DataSource = GetDataFromExcelFile();
    Protected Sub Page_load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        GridView1.DataSource = GetDataFromExcel()
    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