ASP.NET Charting Using Office Web Components (OWC)By Olav Lerflaten
A common requirement when developing applications is to provide some interactive charting. For example, you may be developing an application that helps managers maintain sales or production data. This data might be stored in a SQL database, and your application might allow for users to add new information and update existing information. In addition to these order-entry features, management might request that they be able to visualize this data graphically using pie, bar, and/or scatter (XY) diagrams.
From a Windows desktop application, this has never been a problem, as there are many graphical libraries and charting components available. But in Web applications, things get more tricky. Providing graphing capabilities for a Web application can be done either one of two ways:
- Client-side: Using ActiveX components, it is possible to achieve "rich" Windows client functionality within a Web browser. The downside is more complex client setup, requiring distribution of client-side software, and usually license costs per client. Non-MS Windows/Internet Explorer clients will not function.
- Server-side: Using server-side code on the Web server, dynamically generate a chart which is "streamed" to the Web client as a GIF or JPG image. On the plus side, this requires just a standard Web browser on the client. The downside (compared to client-side technologies) is less interactivity with the graphical image (no zooming or scrolling, unless you submit a new request to the Web server). Map sites (such as Mapquest.com) use this technique extensively. Please remember, the map images are not stored on the Web server, they are generated from a map database whenever the user requests a view.
This article focuses on providing graphing capabilities in an ASP.NET Web page via server-side charting. More specific, this article examines how to generate a scatter (XY) chart from data stored in a MSSQL database.
Selecting Office Web Components (OWC) as a "Charting Engine"
When generating charts for an ASP.NET Web application, we need a suitable "charting engine". ASP.NET has a built-in graphics library (GDI+, in the
System.Drawingnamespace). Using graphics primitives, it is possible to create simple pie, bar, and line charts. For example, the ASP.NET Reports Starter Kit contains code examples of generating charts using GDI+ on the server-side; similarly, Scott Mitchell's article, Create Snazzy Web Charts and Graphics On the Fly with ASP.NET, examines how to create pie charts on the fly using GDI+. Despite such articles and example applications, I've found the GDI+ library to be too low-level to classify as a usable charting engine, especially for more complex graphs and graph types.
There are several commercial add-on charting libraries available for ASP.NET. A quick Google search turned up several:
- Mycos Charts .NET Web Forms Edition
- Dundas Chart for ASP.NET
- Charting Controls at the ASP.NET Control Gallery
However, many of these products are quite expensive, and suffer from a "foreign" look, compared to the world's most used charting application: Microsoft Excel. MS Excel has a quite powerful charting engine, supporting many chart types and functions to "tweak" the layout just the way you want.
All of this leads up to the featured product in this article: OWC, or "Office Web Components". According to Microsoft, OWC is "a Microsoft technology aimed at bringing Office-like functionality to the Web". It is used as a client-side technology when saving Excel worksheets as Web pages, making it easy to publish interactive spreadsheet models and charts on a Web page. However, it is also an excellent server-side charting engine, using the same powerful charting engine as MS Excel.
Licensing Issues with Office Web Components
If you have had experience with earlier version of Office Web Components, you've likely run into Microsoft's licensing issues, which have, in the past, been quite confusing. Originally, Microsoft required an Office license on the server and on every client PC. In practice, this limited the use of OWC to intranets, where the client licenses could be verified. But Microsoft has now softened their attitude: You still need a Office license on the server, but not on the clients, as long as the charts are used "non-interactively", as in server-side charting. In practice, you don't need a full MS Office license on the server, MS Excel 2002 or FrontPage 2002 is sufficient, making OWC a very cheap server-side charting engine.
But wait a minute: Install MS Office on a server? No, I did not say that. The licensing makes OWC part of MS Office, but technically, it is a separate product. Therefore, on a Web server, you just need to install the OWC package, not MS Office itself.
OWC first appeared in MS Office 2000, as OWC Version 9. With MS Office XP, the OWC programming model was revised, making "OWC XP" (also known as OWC10) not 100% compatible with OWC9. There are some OWC9 usage from "classic" ASP, but OWC10 is required in an ASP.NET environment. Therefore, you need to install the OWC10 package on your ASP.NET server.
The next logical question is, "Where can I find the OWC10 package?" Surprisingly, it can be downloaded free of charge from Microsoft, but remember you need some kind of MS Office 2002 license on the Web server to legally use it.
Understanding How OWC Works
OWC is a collection of COM (ActiveX) controls, covering spreadsheet, charting, and "PivotTable" functions. It is mostly used as a "client-side" technology, where the COM controls are installed on the PC client. Used "server-side", it is mainly the charting functions that are of interest. Programmatically, you can generate a chart in memory on the ASP.NET Web server, and then "stream" the chart to the web client as a GIF image file. The client just sees an ordinary image file, but "behind the scenes" the image file is generated dynamically by ASP.NET on the server, as a response to a HTTP request. Thus, there are no special requirements to the Web client, apart from the ability to show GIF image files. Both Netscape and Opera will work fine.
So, how come this excellent Microsoft technology is not widely adapted within the ASP.NET developer community? Microsoft certainly has tried to scare developers away, by non-existent marketing and confusing licensing issues. There is a possibility that Microsoft considers the product not quite ready, and that the upcoming Office 2003 version with OWC11 has some changes in the programming model. But it is also possible that Microsoft is afraid extensive use of OWC technology could reduce MS Office sales.
Also, there are very few programming examples available. The Microsoft KnowledgeBase contains some client-side and "classic" ASP server-side examples, but nothing about using server-side OWC10 in an ASP.NET environment. Other ASP.NET developer sites also turn up zilch. The OWC newsgroup, microsoft.public.office.developer.web.components, mainly discuss client-side use of OWC. Therefore, you are very much on your own. Hopefully, this article contributes to wider use of the technology.
Installing OWC10 on the Web Server
Before the ASP.NET Web server is ready to serve OWC charts, some software needs to be installed, and configuration files changed:
First, and foremost, An ASP.NET runtime environment must of course exist on the Web server.
This means the .NET Framework Redistributable, but you also need the GACUTIL
program (part of .NET Framework SDK) to configure the OWC controls. Therefore,
the .NET Framework SDK utilities should also be installed.
You must somehow be able to start a command prompt on the Web server, with a
PATH to the .NET Framework utilities. With .NET Framework 1.1 Redistributable
and SDK installed to the standard directories, the PATH should refer to these
I recommend changing the permanent
PATH environment variable on the Web
server. Alternatively, you may change the
PATH temporarily by running this
NETPATH.BAT, from the command prompt:
Then, you install the OWC10 package on the Web server. OWC10 can be downloaded from Microsoft. Just accept the default installation choices.
As OWC10 is COM technology, you then need to install the Office XP "Primary
Interop Assembly" (PIA) on the Web server, to make the OWC10 components
available from .NET code. Just download
OXPPIA.EXE file from Microsoft.
OXPPIA.EXE is just some zipped files, which should be unpacked to a permanent
directory on the Web server (I recommend
C:\oxppia). Then you should start the
command prompt (with the
PATH correctly set), change to the
directory, and run
REGISTER.BAT. This command file imports the Office XP PIAs
to the Global Assembly Cache, and changes the registry settings. Please check
that the GACUTIL command runs, by studying the output from the
command. If the
PATH is wrong, the PIAs will not be imported. The README file
says you should use the "Visual Studio .NET Command Prompt", but as VS.NET
probably is not installed on the Web server, you must make the
yourself (it has the same effect).
Finally, you should add this line to the
machine.config file on the Web
server, under the
<add assembly="Microsoft.Office.Interop.Owc, Version=10.0.4504.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
(You should find
machine.config for .NET Framework 1.1 in the directory
In Part 2 we'll look at the OWC programming model and some source code for creating charts from an ASP.NET Web page.