Reading Microsoft Excel data in ASP.Net

Reading Microsoft Excel sheet is quite simple in ASP.net. There are many ways to do CSV file reading in .Net. In this article I�m going to show an example which will get the data from excel sheet and display them in a Data GridView control.

First create a front-end design using your IDE.

Default.aspx



Code:
<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Read Excel Sheet</title>
</head>
<body>
    <form id="form1" runat="server">
     <div>
         <asp:GridView ID="GridView1" runat="server">
         </asp:GridView>         
     </div>
    </form>
</body>
</html>


Now we will add code for pulling the excel data to grid.

Following are the steps involved in pulling Excel sheet.


  1. Open a connection
  2. Create a command object
  3. Create an OleDBDatareader object
  4. Execute the command object executereader and assign the data to OldDBdatareader object


Default.aspx.vb


Code:
Imports System.Data
Imports System.Data.OleDb

Partial Class _Default
    Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   

Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.xls;Extended Properties=""Excel 8.0;"""

     Dim oConn As New OleDbConnection(sConn)
     oConn.Open()  ' open the connection here

     Dim oComm As New OleDbCommand("Select * From [Sheet1$]", oConn)

     ' Execute reader
     Dim oDataReader As OleDbDataReader
     oDataReader = oComm.ExecuteReader()
     ' Bind the data on to Grid
     GridView1.DataSource = oDataReader
     GridView1.DataBind()


End Sub
End Class

In Case if you need to loop through the data, you can try something like this.

Code:

While oDataReader.Read
      ' F1 is first colum if you havent added one
      Response.Write(oDataReader("F1").ToString & "<br />")         
End While



This will get you all the excel data to Gridview control. Another method to pull the excel data is using the OleDBDataadaptor and which is straight forward as well.

I hope this article will help you in someway. Please post your feed back if you have any.

Thanks

Commentaires

Enregistrer un commentaire

Posts les plus consultés de ce blog

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'ad...
Lire la suite

XAJAX with PHP – The future of web development

XAJAX is a class library or framework written purely on PHP that allows you to easily create powerful, Web-based, Ajax applications or plugins. You can download xajax framework from http://sourceforge.net/projects/xajax/ XAJAX is easy to impement. You need to just include XAJAX library, instantiate the xajax object, and register the name of function (or an object or class method) which you want to call from XAJAX framework. xajaxResponse() is an object to return the results of our requests as XML formats. Then call that particular function you want from xajax framework through your HTML or javascript. That's it. XAJAX takes care of most everything else. You just need to write the PHP functions and returning XAJAX XML responses from them, which is made extremely easy by the xajaxResponse class. Code: <?php include './xajax/xajax_core/xajax.inc.php'; $xajax = new xajax(); $rqstAdd =& $xajax->register(XAJAX_FUNCTION, 'a...
Lire la suite

Database connection pooling in ADO.Net

Imagine a situation that multiple users are connecting the same database through our application. In an application establishing a database connection and logging into it is a time consuming activity. It needs several seconds for every time it establishes a connection. So opening and maintaining a database connection for each user is not an ideal solution. When the number of users increases, and if the system�s memory set is limit, probably it leads to a system crash. This problem can solve through connection pooling. A connection pool is a cache or pool of database connections maintained by the application so that the connections can be reused when the database receives future requests for data. Establishing once and reserving it for future request can essentially improve the overall performance of the application. In connection pooling, after a connection is created, the connection object is placed in the pool. When a simultaneous connection request is made later, ...
Lire la suite