Displaying records from an excel file

Aim of this articles is to display records from an Microsoft Excel in an ASP. We could use general SQL command while retreving data from an excel sheet. All we need to know how to connect Excel file.

Here is the excel database connection string :

Connection String for Excel

strConnection = "DBQ=" & Server.MapPath("customer-list.xls") & "; DRIVER={Microsoft Excel Driver (*.xls)};"

We will use a specific driver to connect to Excel files. ODBC plays a major role in coding.

An Example :


strConnection = "DBQ=" & Server.MapPath("customer-list.xls") & "; DRIVER={Microsoft Excel Driver (*.xls)};"

Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
cn.open strConnection


sql="select * from sales_in_2005;"

rs.Open sql, cn, adOpenStatic, adLockPessimistic


do while not rs.eof
response.write rs("customerName") & " : " & rs("soldPrice") & "
"
rs.movenext
loop


rs.close
Set rs = nothing
cn.close
Set cn = nothing


In this example, we have used an example Excel files that holds some data containing customerName and soldPrice cells. We have displayed all cells with sample data on our page.

Happy Coding