How To : Load Excel file and read it in a dataset in asp.net

Introduction

Now a days we have data into excel sheet and maintain data into file itself. Now we need to display those data into Dataset .and display into Gridview , Repeater or Datalist.

In this article I will show you how to read excel file. also I m covering caching concept in this article. so I will do file dependency caching. whenever you modify the excel file cache getting reset and load fresh data.

For that need to Place Gridview on aspx page. right now all the columns available in xls sheet will be available in Gridview

<asp:GridView id="gvProducts" runat="server" AutoGenerateColumns="true">   
</asp:GridView>

Now we have to place conncetion string as below. if excel file is .xls or .xlsx extenstion both connection string available below.

<connectionStrings>
   <add name="xls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=D:\my_products.xls;Extended Properties=Excel 8.0" />    
   <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;
       Data Source=D:\my_products.xlsx;Extended Properties=Excel 12.0;HDR=YES" />    
</connectionStrings>

Then place below code in code behind file

C#.NET

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindProducts();
        }
    }

    public void BindProducts()
    {
      
        DataSet ds = new DataSet();

        if (Cache["PRODUCTS"] == null)
        {
            ds = GetDataSet();
        }
        else
        {
            ds = (DataSet)Cache["PRODUCTS"];
        }

        gvProducts.DataSource = ds;
        gvProducts.DataBind(); 
    }

   public DataSet GetDataSet()
    {
        // Create a DataSet which will hold the data extracted from the worksheet.
        DataSet ds = new DataSet();

        string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        // Create the connection object
        OleDbConnection oledbConn = new OleDbConnection(connString);
        try
        {
            // Open connection
            oledbConn.Open();

            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);

            // Create new OleDbDataAdapter
            OleDbDataAdapter oleda = new OleDbDataAdapter();

            oleda.SelectCommand = cmd;


            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Customer");

            System.Web.Caching.CacheDependency objdep = new System.Web.Caching.CacheDependency(@"D:\my_products.xls");
            Cache.Insert("PRODUCTS", ds, objdep);


        }
        catch
        {
        }
        finally
        {
            // Close connection
            oledbConn.Close();
        }

        return ds;
    }

VB.NET

Protected Sub Page_Load(sender As Object, e As EventArgs)
    If Not Page.IsPostBack Then
        BindProducts()
    End If
End Sub

Public Sub BindProducts()

    Dim ds As New DataSet()

    If Cache("PRODUCTS") Is Nothing Then
        ds = GetDataSet()
    Else
        ds = DirectCast(Cache("PRODUCTS"), DataSet)
    End If

    gvProducts.DataSource = ds
    gvProducts.DataBind()
End Sub
 
Public Function GetDataSet() As DataSet
    ' Create a DataSet which will hold the data extracted from the worksheet.
    Dim ds As New DataSet()

    Dim connString As String = ConfigurationManager.ConnectionStrings("xls").ConnectionString
    ' Create the connection object
    Dim oledbConn As New OleDbConnection(connString)
    Try
        ' Open connection
        oledbConn.Open()

        ' Create OleDbCommand object and select data from worksheet Sheet1
        Dim cmd As New OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn)

        ' Create new OleDbDataAdapter
        Dim oleda As New OleDbDataAdapter()

        oleda.SelectCommand = cmd


        ' Fill the DataSet from the data extracted from the worksheet.
        oleda.Fill(ds, "Customer")

        Dim objdep As New System.Web.Caching.CacheDependency("D:\my_products.xls")


        Cache.Insert("PRODUCTS", ds, objdep)
    Catch
    Finally
        ' Close connection
        oledbConn.Close()
    End Try

    Return ds
End Function

I hope this article was useful and I thank you for viewing it. keep visiting blog , you can get more stuff