Solved

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

Posted on 2004-04-26
3
193 Views
Last Modified: 2010-04-06
Hi,

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 ?

Thanks,
Desperate...
0
Comment
Question by:nchai
3 Comments
 
LVL 17

Expert Comment

by:dorward
ID: 10916765
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.
0
 
LVL 4

Accepted Solution

by:
lpzCoville earned 500 total points
ID: 10916915
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:

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

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
rst.Open


If rst.EOF Then
    Response.Redirect "login.html"
Else
    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!








0
 
LVL 4

Expert Comment

by:kssaran
ID: 10934999
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.

Rgds
Sara
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now