Encrypt and decrypt file with DypsRC4 component

Techniques

This category is aimed to help programmers in order to find and share helpfull techniques, source code and more stuff.

•  Extract data from an Excel sheet:

This tutorial focuses on extracting data from a Microsoft Excel Spreadsheet. This tutorial will walk you through step-by-step to setup the spreadsheet and how to write the code in ASP to get the required data.

Data can be store in a Microsoft Excel Spreadsheet and then use ASP to extract the information.

The spreadsheet acts like a database and you can use standard SQL statements to query the data.

The process is fairly simple and I will break it down into three steps:

• STEP-1: Create an Excel Spreadsheet
• STEP-2: Define named ranges in the spreadsheet
• STEP-3: Write ASP code read the file

•  STEP-1 :

Lets get started with the spreadsheet. You MUST have Microsoft Excel installed on your computer to create and Excel Spreadsheet. I have created a folder called "excel" under C:\Inetpub\wwwroot\ and thats where I will create/save my Excel spreadsheet.

Open Excel and create a spreadsheet that looks like this:

In this sheet the SR, NAME and EMAIL are the column names. When we query the data from this spreadsheet, we can limit the results by selecting only one or two columns e.g. SELECT NAME FROM my_range;

•  STEP-2 :

Now that we have created a spreadsheet, its time to define a named range within Excel that will be treated as a table for our SQL statement. To create a named range, select all the fields that have data in them, with the column names, then go to INSERT > Name > Define... > Type in my_range > Press OK > Save your file in C:\inetpub\wwwroot\excel\excel.xls

I have saved my excel file as excel.xls.

•  STEP-3 :

Now that we have the excel file and the named range in place, we can start working on the ASP code. Here is the code to read this excel file using a DSN-LESS approach

<%
' Set Connection Params
Set oConn = Server.CreateObject("ADODB.connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};"&_
"DriverId=790;" &_
"DBQ=c:\Inetpub\wwwroot\excel\excel.xls;" &_
"DefaultDir = C:\Inetpub\wwwroot\excel\"

Set RS=Server.CreateObject("ADODB.recordset")

' Write the SQL Query
RS.open "SELECT * FROM my_range", oConn

do until RS.EOF
Response.Write ( RS("NAME") & " -- " & RS("EMAIL") & "")
RS.movenext
Loop

'Close the recordset/connection

RS.Close
oConn.Close
Set RS = Nothing
%>

To test the code, point your browser to http://127.0.0.1/excel/read_excel.asp and you will see the following output: