Creating an Excel File from User Input

An ASP script can contain a form that is used for collecting user input. Assume that you need to gather information about patients visiting an urgent care center in your town. It's been requested that your data entry/display screen has a web interface. Normally when you collect data on a web page, the information is saved into some sort of a database, like SQL Server or Access. However, your client particularly requested that the data from the input fields be saved directly to an Excel file. Let's see how you can provide this interface.

Even though Excel is primarily a spreadsheet application, because of its layout (rows and columns), it can easily act as a database. It's not recommended to store a lot of records in a spreadsheet, but if the spreadsheet is currently the only container you have for storing the data, why not use it to your advantage? The following example will demonstrate how to use Excel as a database. You will learn here how to query your Excel spreadsheet in order to display data from it on a web page. In addition, you will learn how to take the information entered on a web page and save it to your Excel file. As an additional feature, your application will allow the clearing of existing data in your Excel database.

Step 1: Creating an Excel Spreadsheet File to Act as a Database

1. Open the Excel application and create a new workbook.

2. In cell A1, enter Patient. In cell B1, enter Phone. These labels will serve as headings for your two-field Excel database.

3. Select columns A:B. With columns A and B highlighted, choose Insert | Name | Create. When the Create Names dialog box appears with the Top Row check box selected, click OK. The performed tasks will result in creating two named ranges in your workbook: Patient and Phone. If you open the Define Name dialog box (choose Insert | Name | Define Name), you will see that Patient refers to cells =Sheet1!$A$2:$A$65536, and the Phone range name references cells =Sheet1!$B$2:$B$65536.

Define Name

HE 1

Narres in workbook:

Patsent

1 « i

Phone

O05C

Add 1

Delete |

J

Refers to:

)=Sheetl!$A$2;$AÎ65536

1

Defining named ranges in a workbook

Figure 16-33:

Defining named ranges in a workbook

This is all you need for your Excel database.

4. Save your Excel workbook file as C:\ExcelWithASP\WriteTo-Excel.xls. Notice that we are using the same virtual directory as in previous examples.

5. Close the Excel application.

Step 2: Creating an ASP Script to Provide User Interface (Form Input) and Excel Database Operations

1. Open Notepad.

2. Enter the ASP script shown below.

3. Save the ASP file as C:\ExcelWithASP\ExcelEntry.asp.

4. Close Notepad.

ExcelEntry.asp code

' Variable Declarations

Dim con Dim rst Dim strCon Dim strSQL Dim name Dim phone

The ADODB connection object The ADODB recordset object

Variable to hold connection string to Excel database Variable to hold SQL query string to perform the insert Variable to hold patient's name Variable to hold patient's phone

Dim key Dim GoAhead Dim myStr

' Iterator (dummy variable) in the For Each loop

' The flag to indicate whether we can proceed

' Variable to hold the message to display in the

' right-hand side table

' ADODB Constants

Const adOpenKeyset = 1

Const adLockPessimistic = 2

On Error Resume Next name=Request("txtPatientName")

phone=Request("txtPhone")

For Each key In Request.Form If Request.Form(key)= "" Then If key = "txtPatientName" Then

Response.Write "<FONT Color = 'Blue'>Please enter the Patient name.</Font>" Else

Response.Write "<FONT Color = 'Red'>Please enter the Phone number.</Font>" End If goAhead = False Exit For End If GoAhead=True Next

If goAhead = True Then name=Replace(Request("txtPatientName"), , )

If Len(name)<> 0 Or _ Len(phone)<>0 Then Set con = Server.CreateObject("ADODB.Connection") strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" strCon=strCon & server.MapPath("WriteToExcel.xls") & ";" strCon=strCon & "Extended Properties=Excel 8.0" If Request("cmdSubmit")="Enter Data in Excel" Then strSQL = "INSERT INTO [Sheet1$] (Patient, Phone)"

strSQL = strSQL & " VALUES ('" & name &

strSQL = strSQL & ",'" & Phone & "')" End If With con

.Open strCon

If Request("cmdDelete")<>"Delete Data" Then

.Execute(strSQL) Else set rst = Server.CreateObject("ADODB.Recordset")

rst.Open "Select * from [Sheet1$] Where Patient='" & name & & _

" AND phone ='"& phone & , con, adOpenKeyset, adLockPessimistic rst.fields(0).value = "" rst.fields(1).value = "" rst.Update rst.Close End If End With

If err.Number =3021 Then

Response.Write "The information you entered cannot be deleted." _ & "<BR>"

Response.Write "Either name or phone number is incorrect. " & "<P>" Else name = "" phone = ""

set rst = Server.CreateObject("ADODB.Recordset") rst.Open "Select * from [Sheet1$]", con Response.Write "<TABLE Border=""1"">"

For Each fld in rst.Fields

<% Next rst.MoveFirst

Do While Not rst.EOF

</TD> <% Next %> </TR> <% rst.MoveNext

Loop

rst.Close Set rst=Nothing con.Close Set con=Nothing End If End If End If

<TITLE>Patient Data Entry Screen</TITLE> </HEAD> <BODY>

<FORM Action="ExcelEntry.asp" Method = "POST" Name="form1"> <P>

<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="4"> <TR>

<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="3"> <TR>

<INPUT Type="text1" Name="txtPatientName" Value="<%=name%>" Size= "30"> </TD> </TR> <TR> <TD>Phone: </TD> <TD>

<INPUT Type="text2" Name="txtPhone" Value="<%=phone %>"> </TD> </TR>

<INPUT Type="Submit" Name="cmdSubmit" Value="Enter Data in Excel"> <INPUT Type="Submit" Name="cmdDelete" Value="Delete Data"> </TABLE> </TD>

If err.number = 0 Then

If (Request("cmdSubmit")="Enter Data in Excel" or _ Request("cmdDelete") = "Delete Data") and Request.Form(key) <>"" Then myStr = "The following data has been successfully " If Request("cmdSubmit")="Enter Data in Excel" Then

Response.Write "<I><FONT Color = 'Green'>" & _ myStr & "added:</I></FONT><HR>" ElseIf Request("cmdDelete") = "Delete Data" Then Response.Write "<I><FONT Color = 'Green'>" & _ myStr & "deleted:</I></FONT><HR>" End If End If

If Request("txtPatientName") <>"" or Request("txtPhone") <>"" Then Response.Write "Patient Name: <B>" & Request("txtPatientName") & "</B></P>" Response.Write "Phone Number: <B>" & Request("txtPhone") & "</b>" End If End If

</TD> </TR> </TABLE> </FORM> </BODY> </HTML>

Because forms are used to gather information from users, you will often want to place the information from the form's fields into variables. Instead of constantly calling the Request.Form (variablename) to get the content of each variable, you can use an iterator (dummy variable) in a For. . . Each loop. The ExcelEntry.asp script shown above uses the following code to display an appropriate message when a form's input field has been left empty:

For Each key In Request.Form If Request.Form(key)= "" Then

If key = "txtPatientName" Then Response.Write "<FONT Color = 'Blje'>Please enter the Patient _ name.</Font>"

Else

Response.Write "<FONT Color = 'Red'>Please enter the Phone _ number.</Font>"

End If goAhead = False Exit For End If GoAhead=True Next

The above code fragment checks for any blanks in the form.

Next, if the user has filled in the two text boxes, the code uses the Microsoft Jet database engine to access data in other database file formats, such as Excel workbooks. Notice that to connect to a Microsoft Excel file (WriteToExcel.xls) that serves as our database, you need to specify the database type in the extended properties for the connection. You should use the Excel 8.0 source database type for Microsoft Excel 8.0 and higher. Therefore, the connection string looks like this:

strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

strCon=strCon & server.MapPath("WriteToExcel.xls") & ";" strCon=strCon & "Extended Properties=Excel 8.0"

Note: When you use Excel as a database, the first row is considered the header, unless you specify HDR=No in the extended properties in your connection string.

Depending on which button the user has clicked, an SQL INSERT INTO statement or the Recordset's Update method are executed. When inserting data into an Excel spreadsheet, we use the sheet name followed by a dollar sign (Sheet1$):

If Reqjest("cmdSubmit")="Enter Data in Excel" Then strSQL = "INSERT INTO [Sheet1$] (Patient, Phone)"

strSQL = strSQL & " VALUES ('" & name &

strSQL = strSQL & ",'" & Phone & "')" End If

It is also possible to reference data in a range with a defined name or a specific address. For example, if your spreadsheet contains the Patient list in cells A1:B15, you can use the following statement to select data based on what the user has entered in the web form's text boxes:

rst.Open "Select * from [Sheet1$A1:B15] Where Patient='" & name & & _

" AND phone ='"& phone & , con, adOpenKeyset, adLockPessimistic

Or, if you assigned the name PatientList to cells A1:B15, you can refer to the named range as follows:

rst.Open "Select * from PatientList Where Patient='" & name & & _

" AND phone ='"& phone & , con, adOpenKeyset, adLockPessimistic

To insert data into the Excel spreadsheet, the code uses the Execute method of the ADO connection:

If Request("cmdDelete")<>"Delete Data" Then

.Execute(strSQL) Else set rst = Server.CreateObject("ADODB.Recordset")

rst.Open "Select * from [Sheet1$] Where Patient='" & name & & _

" AND phone ='"& phone & , con, adOpenKeyset, adLockPessimistic rst.fields(0).value = "" rst.fields(l).value = "" rst.Update rst.Close End If

The Else clause in the code fragment above locates the data in an Excel file based on the user's input. Once found, the data is cleared from the spreadsheet cells using the Recordset's Value method, and the change is saved with the Recordset's Update method. Note that when using ADO from the ASP you are not allowed to delete entire rows in a spreadsheet. The SQL statement DELETE FROM will not work. To get rid of the existing data in a spreadsheet, you can only blank it out. This of course will cause empty lines within your data range. To get rid of the empty lines, you can write some code in the Open event for the workbook.

If the data the user wants to remove from the Excel file cannot be located, error 3021 will occur; therefore, we display the user-friendly message:

Response.Write "The information you entered cannot be deleted." & "<BR>" Response.Write "Either name or phone number is incorrect. " & "<P>"

Every time the user clicks any of the provided buttons, we want to keep him posted about the data currently contained in the Excel database by building a table on the fly:

set rst = Server.CreateObject("ADODB.Recordset") rst.Open "Select * from [Sheet1$]", con Response.Write "<TABLE Border=""1"">"

For Each fld in rst.Fields

<% Next rst.MoveFirst

Do While Not rst.EOF

<% rst.MoveNext

Loop

rst.Close Set rst=Nothing con.Close Set con=Nothing

The above code fragment writes the data contained in an Excel spreadsheet to a table. Notice that the first For.. .Each loop iterates through the Fields collection to write out the names of column headings, while the second For...Each loop places the actual data in table cells.

Finally, the remaining part of the ASP script creates an HTML table within another HTML table to provide a user interface. The table on the right-hand side will serve to advise the user whether the requested operation (insert or delete) was successfully completed.

Step 3: Running the ASP Script—ExcelEntry.asp

To try out the above script, you may want to perform the following steps:

1. Open your Internet browser.

2. Enter the following address: http://localhost/accessDB/Excel-Entry.asp. You should see the following form:

Figure 16-34:

An ASP form can be used for collecting data from a user

Figure 16-34:

An ASP form can be used for collecting data from a user

3. Enter any name and phone number in the provided text boxes and press the Enter Data in Excel button. After entering data, my screen looked like Figure 16-35.

Figure 16-35:

Entering data into Excel via a user form

Figure 16-35:

Entering data into Excel via a user form

4. Add data for another patient.

5. Remove the data for the patient that you entered in step 3 by typing it in the text boxes and pressing the Delete Data button. You should see a screen similar to the one in Figure 16-36.

Figure 16-36:

Deleting data from an Excel database via an ASP form

Figure 16-36:

Deleting data from an Excel database via an ASP form

6. Try to delete the data entered in step 4 by supplying only the patient name. The screen should prompt you to enter the phone number.

Figure 16-37:

ASP form with data validation during the data entry

Figure 16-37:

ASP form with data validation during the data entry

7. Try to delete the data that does not exist. The result is shown in Figure 16-38.

3 Patient Data Eirtty Screen

Fite Edfe View Favorfces Tools Hsfc [¡jJj|

Back - £ ¿^S&arch fèj Favorites

■jHHury

Address http JJhcdhcst^essDBiKceErtry.asp

The information you entered cannot be deleted.

A

.Either name or phone number is incorrect.

Enter Dato in Excel | DeleieDoto |

Passent Name: ||MarkOwBn

Phone: ||222-3345

ie

¡¿^ Lend Intranet

Figure 16-38:

ASP form with data validation during the delete operation

Figure 16-38:

ASP form with data validation during the delete operation

8. Open the spreadsheet file C:\ExcelWithASP\WriteToExcel.xls and view the results.

[^WrileToEHcel.Kls

JflJ.

X

A

B

c-

1

Piàûer;!

Phone

2

3

[Mark Owen !

143-9789

4

5

6

7

8

N 4

► H \Sheetl / 5heec2 / Sheet3 |

«I

J MJ

Excel spreadsheet after removing the first entry (in row 2)

Figure 16-39:

Excel spreadsheet after removing the first entry (in row 2)

0 0

Post a comment