• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 206
  • Last Modified:

A web Application to Login and Search an information on an excel spreadsheet


I'm very new to web application and were task to implement a simple web app that allows users to login through the web browser. Once login, prompt for some identification number from the users and do a search on a excel spreadsheet located on my servers and display some information based on the identification number. Any sample for me work on ?

1 Solution
There are two issues here.

1. Authentication

2. Parsing Excel files

Both depend on your webserver (and which languages you can write server side processes in).

Assuming you use Perl, perhaps the most common language for server side processing, you might find http://cgi.resourceindex.com/Programs_and_Scripts/Perl/Password_Protection/ and http://search.cpan.org/search?query=Excel&mode=all to be of use.
OK, I'm going to assume that you are using ASP for this.  You would (much) be better off using an Access database, but if you have to use Excel, it can be done.  We're going to assume an Excel spreadsheet named "myfile.xls", with columns named "LoginName", "LoginPW", and "Info" in the first row, on Sheet1; that there's nothing else on the Sheet; that your excel file is located on the same machine as the web server; and that the physical path is "C:\Inetpub\wwwroot\excel\".

First of all, you will need (well, you don't NEED, but you should use) two pages to do this:  a login page, and a page that responds to the use input. Let's call them login.html and test.asp  In your login.html file, create a form, like so:

<form action="main.asp" method="post">
<input type="text" name="uid">
<input type="password" name="pwd">

Here is what your test.asp file would look like:

<% @ LANGUAGE = "VBScript" %>
<% Option Explicit %>

Dim cnn
Dim rst
Dim strSQL
Dim uid
Dim pwd

uid = Trim(Request.Form("uid"))
pwd = Trim(Request.Form("pwd"))

If uid = "" Or pwd = "" Then Response.Redirect "login.html"

Set cnn = Server.CreateObject("ADODB.Connection")

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Inetpub\wwwroot\excel\myfile.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=Yes"""

' from  http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJetExcel:
' Where "HDR=Yes" means that there is a header row in the cell range
' (or named range), so the provider will not include the first row of the
' selection into the recordset.  If "HDR=No", then the provider will include
' the first row of the cell range (or named ranged) into the recordset.
' For more information, see:  http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

Set rst = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM [Sheet1$] WHERE LoginName = '" & uid & "' AND LoginPW = '" & pwd & "';"
rst.ActiveConnection = cnn
rst.CursorType = 3                    'Static cursor.
rst.LockType = 2                      'Pessimistic Lock.
rst.Source = strSQL

If rst.EOF Then
    Response.Redirect "login.html"
    Response.Write "Hello, " & uid & "!"
    Response.Write "<p>Here is your information: " & rst("Info")
End If

rst.Close: Set rst = Nothing
cnn.Close: Set cnn = Nothing


Anyway, that's just touching the surface, but it should get you started.  Good luck!

Hi you can make use of ASP.NET very well for this type of requirement.

Excel sheets can be read by the ADO.NET very well and the basic authentication can be managed with a small access database user master listing

If you wanna know how to read excel here is a sample article on the same. http://www.dotnetjohn.com/articles/articleid54.aspx.


Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now