Read from Excel in ASP
I always had a hard time finding correct documentation on the old ASP (active server pages) language and I needed it one more time in the last few days to create a script that read from an excel file.
As I believe that it might be handy for a handful of people around the world (is there actually anybody else still developing in ASP these days?), I hereby share my little piece of code with you guys.
Any suggestions or comments are always welcome.
In my example we read from an excel file that has 1 small table that has 1000 lines and columns from A until G.
The first row contains all the column names.
'initialize variables Dim objConn, strSQL Dim x Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_ "Excel 8.0; DBQ=" & Server.MapPath("filename.xls") & "; " strSQL = "SELECT * FROM A1:G1000" Response.Write("<table border=""1"">") Response.Write("<tr>") 'write all columnNames For x=0 To objRS.Fields.Count-1 Response.Write("<th>" & objRS.Fields(x).Name & "</th>") Next Response.Write("</tr>") Do Until objRS.EOF ' write as much columns as there are in your excel file Response.Write("<td>" & objRS.Fields(0).Value & "</td>") Response.Write("<td>" & objRS.Fields(1).Value & "</td>") Response.Write("<td>" & objRS.Fields(2).Value & "</td>") Response.Write("<td>" & objRS.Fields(3).Value & "</td>") Response.Write("<td>" & objRS.Fields(4).Value & "</td>") Response.Write("<td>" & objRS.Fields(5).Value & "</td>") Response.Write("<td>" & objRS.Fields(6).Value & "</td>") objRS.Close Response.Write("</table>") Set objRS=Nothing
Comments