reading data from excel

Question

Ugene 0 Newbie Poster

16 Years Ago

Any 1 whom have done excel with asp help me pls..
i currently have these codes
i need to plot a zig zag graph out,i have the graph setting out then now i have to read the data from a excel file ..i don't know how to set the range - stuck
--------------------------------------------------------------------------

<!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

asp

1 Contributor
2 Replies
138 Views
1 Day Discussion Span
Latest Post 16 Years Ago Latest Post by Ugene

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Ugene 0 Newbie Poster · Answer 1 · 2008-09-23T08:28:15+00:00

<% 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_Data.xls;"

strSQL = "SELECT * FROM D1:D170"
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>

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

this is the code to read the data from excel and put it in a browser. if any one wants it can just use and play around with my code ..

Now my another concern is to use this code which display on the browser to plot it on a graph..i already have to applet work but how to put it inside?any 1 konws?

Ugene 0 Newbie Poster · Answer 2 · 2008-09-23T09:21:45+00:00

this is the code for the working applet.
---------------------------------

<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>

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