harting and reporting can involve a lot of work. Numerous vendors have made a business out of selling their third party charting and reporting tools. High-end vendors such as Microstrategy, SoftArtisan, or Crystal Reports carry both a hefty price tag and a hefty learning curve. Other vendors offer VBA, ActiveX controls, Java applets, etc that all solve your reporting headaches to some degree or another. But you already
have one of the most sophisticated charting and reporting tools at your disposal. This article focuses solely on the extent to which you can leverage the existing capabilities of Excel to deliver powerful, highly flexible charting and reporting solutions.
1. Excel Chart Publishing
Excel Chart Publishing is an interesting way to publish live, or almost live data to a web page using Excel's powerful charting solutions. This solution is best for generic charts that need not be interactive or personalized in any way, for example, stock charts, web site performance, or survey results).
- You'll be able to use the full range of chart types and customization features offered by Excel charting.
- This requires only minimal VBA coding skills, most of which is here in this article.
Procedure for Publishing Excel Charts
- It is very difficult (or impossible) to create charts or reports that are personalized for each client.
- Likewise, it is very difficult to achieve any level of interactivity using this method.
- You must be able to run Excel somewhere that allows it to write to your web root, or you must be able to transfer the chart image to your web server with some regularity.
- Excel must remain running for as long as you want chart data updated and published automatically.
Here's the overview of the steps involved in publishing Excel charts. I'll explain each of the steps in more detail below.
Import your chart data into your spreadsheet.
Create your chart based on that data.
Add a macro to publish the chart periodically.
Import Your Chart Data
|Figure 1: Use Excel to create almost live charts.|
Once you've created a new spreadsheet, you'll need to tell Excel how to get your chart data. You have three options:
- Get your data from the web using Excel's web query. To do this, use Data> Get External Data> New Web Query and add the URL from which you'll get your data. Note that this works best if the web page from which you are retrieving your data lays out the data using HTML tables. For an example, see Figure 2 and Listing 1.
- Get your data from a database. To do this, use Data> Get External Data> New Database Query and follow the prompts. You may need to set up an ODBC data source in windows if your database is not Access (or FoxPro if you still use that).
- You can place static data in Excel and create your charts from that.
If you want to automatically refresh your data every few minutes, select the "Refresh every
" option under Data> Get External Data> Data Range Properties. The top left cell of your web query or database query must be selected for this option to appear on the menu.
Create Your Chart
This is the fun part. Start with the chart wizard (Insert> Chart) and get the chart contents as close as you can to exactly what you'd like to publish. After completing that, you can customize the color schema, formatting, fonts, et cetera to your heart's content. Excel's charting capabilities are quite powerful, and you'll be able to design something that really stands out if you invest the time. A quick hint for the uninitiated: you can right-click just about anything in an Excel chart to adjust its color or formatting.
Add a Macro to Publish Your Chart
To add a macro, you'll need to open the Visual Basic editor in Excel; select Tools> Macros> Visual Basic Editor. With a little editing, the following sub routine will do the trick for you:
, "Chart1", "", xlHtmlStatic, "DevXSample_16365", _
"Chart Title Goes Here").Publish (True)
Application.OnTime Now + TimeValue("00:05:00"), _
You will need to change the filename "H:\timetest\ResponseDist.htm
" in the preceding code fragment to the path and name of your web document root (for example F:\inetpub\wwwroot
). Likewise, you should change "Chart1" to the name of the sheet you're exporting to the web.
is a special name for a subroutine that will automatically run when the workbook is opened. In this example, Excel will re-publish the chart when the spreadsheet is opened and then every five minutes because of the OnTime
Don't feel obliged to use the .htm
file that Excel generated for you. You can create your own HTML and include an image tag where you want to display the chart. For example: