Solved

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

Posted on 2004-04-26
3
195 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

839 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