EasyCFM.COM ColdFusion Forums / ASP to CFML / Please don't laugh

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Please don't laugh

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

BClegger
05-28-2004 @ 11:26 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 5
Joined: May 2004

This is probably the stupidest code you will ever see, but could someone help with changing this to CFML?

%>

<%
' Selected constants from adovbs.inc
Const adOpenStatic = 3
Const adLockPessimistic = 2

Dim cnnExcel
Dim rstExcel
Dim I
Dim iCols

' This is all standard ADO except for the connection string.
' You can also use a DSN instead, but so it'll run out of the
' box on your machine I'm using the string instead.
Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "DBQ=" & Server.MapPath("xl_data.xls") & ";" & _
     "DRIVER={Microsoft Excel Driver (*.xls)};"

' Same as any other data source.
' FYI: TestData is my named range in the Excel file
Set rstExcel = Server.CreateObject("ADODB.Recordset")
rstExcel.Open "SELECT * FROM innov;", cnnExcel, _
     adOpenStatic, adLockPessimistic

' Get a count of the fields and subtract one since we start
' counting from 0.
iCols = rstExcel.Fields.Count
%>
<table border="1">
     <thead>
          <%
          ' Show the names that are contained in the first row
          ' of the named range.  Make sure you include them in
          ' your range when you create it.
          For I = 0 To iCols - 1
               Response.Write "<th>"
               Response.Write rstExcel.Fields.Item(I).Name
               Response.Write "</th>" & vbCrLf
          Next 'I
          %>
     </thead>
     <%
     rstExcel.MoveFirst

     ' Loop through the data rows showing data in an HTML table.
     Do While Not rstExcel.EOF
          Response.Write "<tr>" & vbCrLf
          For I = 0 To iCols - 1
               Response.Write "<td>"
               Response.Write rstExcel.Fields.Item(I).Value
               Response.Write "</td>" & vbCrLf
          Next 'I
          Response.Write "</tr>" & vbCrLf

          rstExcel.MoveNext
     Loop
     %>
</table>

<%
rstExcel.Close
Set rstExcel = Nothing

cnnExcel.Close
Set cnnExcel = Nothing
%>



"Jack of all trades, Master of None." ~R.S.

pmontero
06-12-2004 @ 3:48 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 2
Joined: Jun 2004

BClegger,

I don't know anything about ASP, but I know a little of Visual Basic. My English is not so good also, but I hope this code helps:

FIRST:
Create an ODBC System DSN with the Microsoft Excel Driver and add a connection to it in the CF Administrator page (type ODBC datasource). Use 97/2000 version for the ODBC driver and set off "Mantain Connection Across Clients" (this helps CF to release the use of the file when a query ends, and its specially important for Excel files). Now you have set the connection and are ready to use it.

SECOND,
Retrieve the column names (there are better ways, but I'm assuming you're looking for the easiest method).

<cfquery name="yourquery" datasource="yourdatasource">
SELECT * FROM $yoursheet ($ is required)
</cfquery>

THIRD,
Show the field names as table headers:

<table width="100%">
<tr>
<cfset FieldName = "">
<cfset Delimiter = ",">
<cfloop index="FieldName" list="#yourquery.ColumnList#" delimiters="#Delimiter#">
<cfoutput>
<td>#FieldName#</td>
</cfoutput>
</cfloop>
</tr>

FOURTH,
Now, you're ready to simply extract the data (I'm using the "ColumnList" object again to ensure the data corresponds exactly to the field header).

<tr>
<cfloop index="FieldName" list="#yourquery.ColumnList#" delimiters="#Delimiter#">
<cfoutput>
<td>#Evaluate("yourquery." & FieldName)#</td>
</cfoutput>
</cfloop>
</tr>
</table>

Now you're listing the entire table with their corresponding headers.

The entire code should be something like this:

<html>
<body>
<cfquery name="yourquery" datasource="yourdatasource">
SELECT * FROM $yoursheet ($ is required)
</cfquery>
<table width="100%">
     <tr>
     <cfset FieldName = "">
     <cfset Delimiter = ",">
     <cfloop index="FieldName" list="#yourquery.ColumnList#" delimiters="#Delimiter#">
          <cfoutput>
               <td>#FieldName#</td>
          </cfoutput>
     </cfloop>
</tr>
<tr>
     <cfloop index="FieldName" list="#yourquery.ColumnList#" delimiters="#Delimiter#">
     <cfoutput>
          <td>#Evaluate("yourquery." & FieldName)#</td>
     </cfoutput>
     </cfloop>
</tr>
</table>
</body>
</html>


Hope this help.

Please keep me aware about your progress.


Pedro D. Montero<br>
<b>Dolphin Interactive S.A. de C.V.</b><br>
Tel. 52 + (55) 5563 9090<br>
pmontero@dolphininteractive.com.mx


Website Designed and Developed by Pablo Varando.