HanengCharts - Easy to use Charts

Questions? Email us: sales@haneng.com

HanengCharts Sample Code HanengCharts with ASP and Excel

Introduction
If you have not read the HanengCharts & ASP whitepaper I recommend that you do so before looking at this sample. This sample code will show you how to use ASP to get data from an Excel sheet and use that data to create a chart. That means that to update the charts with new data all you have to do is to edit the Excel sheet, save it and the changes will be reflected in the chart immediately. This can be very useful for non-technical people that want to edit the charts. Example: The manager of a sales division can update a chart on the company intranet showing each sales person's performance the last week just by using MS Excel to edit an Excel file.

Download:
You can download the sample scripts and the Excel file mentioned in this sample below:
Download

How to install this script

1. Download & Unzip the Sample files
Download the sample scripts and the Excel file mentioned in this sample:
Download

Unzip the files (e.g. using WinZip) and place them in a folder of your ASP enabled webserver (e.g. PWS or IIS).

2. Open the Excel file
To do this you need to have Excel installed on your machine. We need to name the part of the Excel sheet you want to retrive. To do so mark the area you want to use, then go to the menu and choose Insert -> Name -> Define.... Give the area the name Sales. Save the changes and close Excel.

3. Upload all files to your server
It is important that the HanengCharts_Excel.asp, HanengCharts_Excel_Test.asp, HanengCharts_ExcelSheet.xls and HanengCharts3.jar are in the same directory.

4. Run the HanengCharts_Excel_Test.asp file to see the result
Make sure that your ASP server is running and that you run the file through a browser with the URL being: http://[something]/HanengCharts_Excel_Test.asp and NOT file:///[something]/HanengCharts_Excel_Test.asp

If you have done it right it should show up like this:

Product ID	Product Name	Sold
1	Light Edition	75
2	Standard Edition	42
3	PRO Edition	78
4	Deluxe Edition	32
5	Enterprise Edition	110

If it doesn't look like the table above you have made a mistake when selecting and naming the area in the Excel area or you might not have Excel ODBC Drivers installed on the server.

5. Making Charts
When you have HanengCharts_Excel_Test.asp working we can go on to the next step: making charts. Do so by running HanengCharts_Excel.asp the same way you ran HanengCharts_Excel_Test.asp. It should now show up as:

6. The code for HanengCharts_Excel.asp
<HTML>
<BODY>
<%
'This locates where the Excel file is. The default is in the same
'directory as this script, but you can change it to be anywhere by
'specifying the full path. E.g. Path = "C:\Data\HanengCharts_ExcelSheet.xls"
Path = Server.MapPath("HanengCharts_ExcelSheet.xls")

'This is the name you gave to the area of the Excel sheet that
'you want access to
Table="Sales"

'What field would you like to use for the texts?
TextField = "Product Name"

'What filed would you like to use for the values?
ValueField = "Sold"

'The default SQL query gets all the data from the selected area,
'but you can modify it to suit your needs
SQL_Query = "SELECT * FROM " & TABLE

'Now we create the connection string from the variables given above
Driver ="{Microsoft Excel Driver (*.xls)}"
DataSource = "DBQ=" & Path & ";"
DataSource = DataSource & "DefaultDir=" & Path & ";"
DataSource = DataSource & "Driver=" & Driver & ";"
DataSource = DataSource & "FIL=excel 5.0;ReadOnly=1;"

'Now we connect to the Excel sheet
Set MyConn =Server.CreateObject("ADODB.Connection")
MyConn.ConnectionTimeout = 15
MyConn.CommandTimeout = 30
MyConn.Open DataSource

'Lets loop through the data and make a chart
Set RS = MyConn.Execute(SQL_Query)

IF RS.EOF THEN
  'There is no data to show
  %>No data found<%
ELSE

  'We have data so lets make a chart
  %>
  
  <CENTER>
  <APPLET CODE="HanengCharts.class" ARCHIVE="HanengCharts3.jar" WIDTH=460 HEIGHT=260>
  <PARAM NAME="LicenseKey" VALUE="Cut & Paste Your License Key Here">
  <PARAM NAME="ChartType" VALUE="3dbar">
  <%
  'We need to label each Parameter with it's own number, this
  'variable will start at 1 and increase by 1 for each parameter pair we output
  ParameterCounter = 1

  'Now it is time to loop through the Excel data and output them in
  'a format that HanengCharts understands
  WHILE NOT RS.EOF
    %>
    <PARAM NAME="Text_<%=ParameterCounter%>" VALUE="<%=TRIM(RS(TextField))%>">
    <PARAM NAME="Value_<%=ParameterCounter%>" VALUE="<%=TRIM(RS(ValueField))%>">
    <%
    ParameterCounter = ParameterCounter + 1
    RS.MoveNext
  WEND
  %>
  </APPLET>
  </CENTER>
  
  <%
END IF

'We then close the results and the database connection
RS.Close
Set RS = nothing
MyConn.Close
Set MyConn = nothing
%>

</BODY>
</HTML>

Troubleshooting
In this example we extract the data we use from an MS Excel file. To be able to do this you need Excel ODBC drivers installed on the machine that runs the script (the web server machine). To check if you have this on your server go to the servers Start menu, then Control Panel and then open Data Sources (ODBC). (Windows 2000 users: Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC)). Under the Drivers tab you should see Microsoft Excel Driver:

There is no need to create an Excel data source, we just want to verify that it is there. If you can not find it try reinstalling ODBC and make sure Excel drivers are installed.

Back to the Developers Zone