excel & ASP

Ugene- · #1 (**permalink**) 09-11-08, 22:04

Anyone here know how to set the connection to read the figures on microsoft excel?i am able to set the and see the graph only when i hardcode in the figure...help !

gvee · #2 (**permalink**) 09-12-08, 08:52

Without more information I can only point you to this link: http://connectionstrings.com/?carrier=excel2007

Your problem (and therefore question) is not clear.

Ugene- · #3 (**permalink**) 09-14-08, 04:00

hey georgev, thanks for the link i will try it out. and get back to you thanks !

Ugene- · #4 (**permalink**) 09-18-08, 05:34

hi georgev,i currently have these codes
--------------------------------------------------------------------------
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">

<%@ Language=VBScript %>
<%
' Set Connection Params
Set oConn = Server.CreateObject("ADODB.connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790;" &_
"DBQ=C:\Inetpub\wwwroot\InfoWeb\14Apr08_NUS_hr_Dat a.xls;" &_
"DefaultDir = C:\Inetpub\wwwroot\InfoWeb\"

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

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

do until RS.EOF
Response.Write ( RS("Item No") & " -- " & RS("Item Description") & "<br>")
RS.movenext
Loop

'Close the recordset/connection

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

<html>
<head>
<title>Pressure Chart</title>
</head>
<body>

<applet codebase="new" code="linegraph.class" height=220 width=360>
<param name="title" value="Sales">
<param name="NumberOfVals" value="<%=sqlQuery.RecordCount%>">
<param name="NumberOfLabs" value="<%=sqlQuery.RecordCount%>">
<PARAM NAME=ymax VALUE=<%=(Fix(sqlQuery("M")/10000)+1)*10000%>>
<PARAM NAME=ymin VALUE=0>
<PARAM NAME=mode VALUE=0>
<PARAM NAME=border VALUE="20"> <PARAM NAME=Grid VALUE="true">
<PARAM NAME=LineColor_R_L1 VALUE=53>
<PARAM NAME=LineColor_G_L1 VALUE=153>
<PARAM NAME=LineColor_B_L1 VALUE=51>
<%
i = 1
while (not sqlQuery.EOF) %>
<param name="VAL<%=i%>_L1" value="<%=sqlQuery("SalesTotal")%>">
<param name="LAB<%=i%>" value="<%=MonthName(Month(sqlQuery("Date")))%>">
<%
i = i + 1
sqlQuery.MoveNext
wend %>

</APPLET><p>

</body>
</html>
--------------------------------------------------------------------------
So how do i set my range which i underline? there are error in the codes could you point out to me ?

this is the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not find the object 'my_range'. Make sure the object exists and that you spell its name and the path name correctly.

/Infoweb/pressure.asp, line 15

gvee · #5 (**permalink**) 09-22-08, 08:12

Apologies mate, completely missed your reply - will try take a look today

gvee · #6 (**permalink**) 09-22-08, 08:36

Sounds like you've not defined your range in the Excel spreadsheet correctly; have a look at the following KB article (specifically the bit about creating MyRange1) http://support.microsoft.com/kb/195951 and see if it helps

Ugene- · #7 (**permalink**) 09-22-08, 23:06

No worries mate...Now i have got the connection correct..and now its able to display the data in the excel file on a browser in a table form..so now i have to plot it i alraedy got the applet working but now is how to get the connect between the applet with the data ?

below is the correct data for the connection between excel and ASP..so if any 1 need it can play around with it..
-------------------------------------------------------------------------
<% Option Explicit %>
<html>
<body>

<%
Dim objConn, objRS, strSQL
Dim x, curValue

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DRIVER={Microsoft Excel Driver (*.xls)}; IMEX=1; HDR=NO; "&_
"DBQ=C:\Inetpub\wwwroot\InfoWeb\14Apr08_NUS_hr_Dat a.xls;"

strSQL = "SELECT * FROM D1

170"
Set objRS=objConn.Execute(strSQL)
Response.Write("<table border=""1"">")
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
Response.Write("<th>" & objRS.Fields(x).Name & "</th>")
Next
Response.Write("</tr>")
Do Until objRS.EOF
Response.Write("<tr>")
For x=0 To objRS.Fields.Count-1
curValue = objRS.Fields(x).Value
If IsNull(curValue) Then
curValue="N/A"
End If
curValue = CStr(curValue)
Response.Write("<td>" & curValue & "</td>")
Next
Response.Write("</tr>")
objRS.MoveNext
Loop
objRS.Close
Response.Write("</table>")

objConn.Close
Set objRS=Nothing
Set objConn=Nothing
%>

</body>
</html>
--------------------------------------------------------------------------

Below is the applet to plot the graph how to connect it?
-------------------------------------------------------------------------
<APPLET CODE="linegraph.class" HEIGHT=220 WIDTH=360>
<PARAM NAME=NumberOfVals VALUE=36>
<PARAM NAME=NumberOfLabs VALUE=4>
<PARAM NAME=KeyWidth VALUE=70>

<PARAM NAME=LineColor_R_L1 VALUE=0>
<PARAM NAME=LineColor_G_L1 VALUE=0>
<PARAM NAME=LineColor_B_L1 VALUE=200>

<PARAM NAME=LineColor_R_L2 VALUE=200>
<PARAM NAME=LineColor_G_L2 VALUE=0>
<PARAM NAME=LineColor_B_L2 VALUE=0>

<PARAM NAME=yMax VALUE=30>
<PARAM NAME=yMin VALUE=-10>
<PARAM NAME=Mode VALUE=0>
<PARAM NAME=Lines VALUE=2>
<PARAM NAME=Title VALUE="NET PROFIT (K$)">
<PARAM NAME=Border VALUE="30">
<PARAM NAME=Grid VALUE="true">

<PARAM NAME=VAL1_L1 VALUE=10.3>
<PARAM NAME=VAL2_L1 VALUE=11.0>
<PARAM NAME=VAL3_L1 VALUE=14.5>
<PARAM NAME=VAL4_L1 VALUE=12.20>
<PARAM NAME=VAL5_L1 VALUE=15.0>
<PARAM NAME=VAL6_L1 VALUE=12.5>
<PARAM NAME=VAL7_L1 VALUE=20.5>
<PARAM NAME=VAL8_L1 VALUE=22.5>
<PARAM NAME=VAL9_L1 VALUE=18.8>
<PARAM NAME=VAL10_L1 VALUE=24>
<PARAM NAME=VAL11_L1 VALUE=27.8>
<PARAM NAME=VAL12_L1 VALUE=29>
<PARAM NAME=VAL13_L1 VALUE=26.9>
<PARAM NAME=VAL14_L1 VALUE=22.4>
<PARAM NAME=VAL15_L1 VALUE=18.0>
<PARAM NAME=VAL16_L1 VALUE=14.8>
<PARAM NAME=VAL17_L1 VALUE=7.5>
<PARAM NAME=VAL18_L1 VALUE=3.2>
<PARAM NAME=VAL19_L1 VALUE=-4.6>
<PARAM NAME=VAL20_L1 VALUE=-2.0>
<PARAM NAME=VAL21_L1 VALUE=1.8>
<PARAM NAME=VAL22_L1 VALUE=2.5>
<PARAM NAME=VAL23_L1 VALUE=-1.1>
<PARAM NAME=VAL24_L1 VALUE=2.5>
<PARAM NAME=VAL25_L1 VALUE=11.9>
<PARAM NAME=VAL26_L1 VALUE=16.6>
<PARAM NAME=VAL27_L1 VALUE=12.6>
<PARAM NAME=VAL28_L1 VALUE=18.7>
<PARAM NAME=VAL29_L1 VALUE=23.9>
<PARAM NAME=VAL30_L1 VALUE=19.9>
<PARAM NAME=VAL31_L1 VALUE=19.5>
<PARAM NAME=VAL32_L1 VALUE=23.5>
<PARAM NAME=VAL33_L1 VALUE=19.0>
<PARAM NAME=VAL34_L1 VALUE=22.5>
<PARAM NAME=VAL35_L1 VALUE=22.9>
<PARAM NAME=VAL36_L1 VALUE=24.6>

<PARAM NAME=VAL1_L2 VALUE=14.3>
<PARAM NAME=VAL2_L2 VALUE=12.0>
<PARAM NAME=VAL3_L2 VALUE=11.5>
<PARAM NAME=VAL4_L2 VALUE=17.20>
<PARAM NAME=VAL5_L2 VALUE=12.0>
<PARAM NAME=VAL6_L2 VALUE=13.5>
<PARAM NAME=VAL7_L2 VALUE=25.5>
<PARAM NAME=VAL8_L2 VALUE=21.5>
<PARAM NAME=VAL9_L2 VALUE=11.8>
<PARAM NAME=VAL10_L2 VALUE=14>
<PARAM NAME=VAL11_L2 VALUE=17.8>
<PARAM NAME=VAL12_L2 VALUE=19>
<PARAM NAME=VAL13_L2 VALUE=16.9>
<PARAM NAME=VAL14_L2 VALUE=22.4>
<PARAM NAME=VAL15_L2 VALUE=28.0>
<PARAM NAME=VAL16_L2 VALUE=24.8>
<PARAM NAME=VAL17_L2 VALUE=9.5>
<PARAM NAME=VAL18_L2 VALUE=6.2>
<PARAM NAME=VAL19_L2 VALUE=4.6>
<PARAM NAME=VAL20_L2 VALUE=12.0>
<PARAM NAME=VAL21_L2 VALUE=8.8>
<PARAM NAME=VAL22_L2 VALUE=9.5>
<PARAM NAME=VAL23_L2 VALUE=11.1>
<PARAM NAME=VAL24_L2 VALUE=7.5>
<PARAM NAME=VAL25_L2 VALUE=14.9>
<PARAM NAME=VAL26_L2 VALUE=15.6>
<PARAM NAME=VAL27_L2 VALUE=16.6>
<PARAM NAME=VAL28_L2 VALUE=11.7>
<PARAM NAME=VAL29_L2 VALUE=21.9>
<PARAM NAME=VAL30_L2 VALUE=16.9>
<PARAM NAME=VAL31_L2 VALUE=13.5>
<PARAM NAME=VAL32_L2 VALUE=25.5>
<PARAM NAME=VAL33_L2 VALUE=19.9>
<PARAM NAME=VAL34_L2 VALUE=22.9>
<PARAM NAME=VAL35_L2 VALUE=22.1>
<PARAM NAME=VAL36_L2 VALUE=24.1>

<PARAM NAME=LAB1 VALUE="1999">
<PARAM NAME=LAB2 VALUE="2000">
<PARAM NAME=LAB3 VALUE="2001">
<PARAM NAME=LAB4 VALUE="2002">

<PARAM NAME=Key_L1 VALUE="ABC Corp">
<PARAM NAME=Key_L2 VALUE="XYZ Corp">

</APPLET>
-----------------------------------------------------------------------

gvee · #8 (**permalink**) 09-23-08, 07:24

So you got it writing the data out to page? Superb

I'm assuming that by "connect it", you mean, use the data from the excel spreadsheet as parameter values for your <applet>, right?

Ugene- · #9 (**permalink**) 09-24-08, 22:32

Yes mate. now its displaying it in a table form.so now i want to use the data to plot it on a graph. the applet is to a graph ..so i want to insert in for me instand of me hardcoding it in myself. its there any method ?

gvee · #10 (**permalink**) 09-25-08, 03:29

Yessir... Apply the same theory, using the .Write method to populate the applet values

Code:

Start loop

    Response.Write("<PARAM NAME=SomeName VALUE='")
    Response.Write(objRS.Fields(1).Value)
    Response.Write("'>")

Next in loop

Ugene- · #11 (**permalink**) 09-25-08, 21:25

Quote:

Originally Posted by georgev

Yessir... Apply the same theory, using the .Write method to populate the applet values

Code:

Start loop

    Response.Write("<PARAM NAME=SomeName VALUE='")
    Response.Write(objRS.Fields(1).Value)
    Response.Write("'>")

Next in loop

hey georgev i can't seem to get it...

gvee · #12 (**permalink**) 09-26-08, 03:26

That's some pseduo code giving an example of how to use the recordset to populate the VALUE of the PARAM.

Ok, let's step back a mo.

What fields from your recordset are you wanting to use to populate your applet?

Ugene- · #13 (**permalink**) 09-28-08, 21:20

Hey george, this is the values that i want to populate. but all the value below is hardcoded inside, thats why i want to read the data from my excel and display it on the graph. i don't konw how to put the retrieved data inside the applet - VALUE="retrieved data from excel".

<PARAM NAME=LAB1 VALUE="1999">
<PARAM NAME=VAL36_L1 VALUE=24.6>
<PARAM NAME=VAL1_L2 VALUE=14.3>
----------------------------------------------------------------------

and this is the actual applet without hardcoding inside..all the values inside like title=sales is not what i want as i copy from the actual code..hope u understand what i meant..

<applet codebase="new" code="linegraph.class" height=220 width=360>
<param name="title" value="Sales">
<param name="NumberOfVals" value="<%=sqlQuery.RecordCount%>">
<param name="NumberOfLabs" value="<%=sqlQuery.RecordCount%>">
<PARAM NAME=ymax VALUE=<%=(Fix(sqlQuery("M")/10000)+1)*10000%>>
<PARAM NAME=ymin VALUE=0>
<PARAM NAME=mode VALUE=0>
<PARAM NAME=border VALUE="20">
<PARAM NAME=Grid VALUE="true">
<PARAM NAME=LineColor_R_L1 VALUE=53>
<PARAM NAME=LineColor_G_L1 VALUE=153>
<PARAM NAME=LineColor_B_L1 VALUE=51>
<%
i = 1
while (not sqlQuery.EOF) %>
<param name="VAL<%=i%>_L1" value="<%=sqlQuery("SalesTotal")%>">
<param name="LAB<%=i%>" value="<%=MonthName(Month(sqlQuery("Date")))%>">
<%
i = i + 1
sqlQuery.MoveNext
wend %>

------------------------------------------------------------------

gvee · #14 (**permalink**) 09-30-08, 03:26

It looks like you've got it right in your code above... Is there still a problem?
apolgies, having a slow morning

Ugene- · #15 (**permalink**) 09-30-08, 04:19

i am unable to place the retieved data into the applet ...can you teach me how do i pass the value inside ?and where should i place the applet ?after the connection ? or within the connection ?

Internet Services

Web Development

Digital Marketing

Consumer Tech