Building Dazzling Charts With Office Web Components

By Milan Negovan

Print Download

Any time you need to add killer 3D charts to your web application you probably jump online and start looking for component vendors. Or you open up an issue of the MSDN Magazine (which looks more like a JC Penney catalog these days, no less) and do the same. I think charting components are way overpriced and overrated. I'd like to know how many people actually use triple nested datagrids? No, really? I'm not talking about MSDN articles which do it for show-and-tell. I mean real-life solutions. Before you blow the bank take a close look at the Microsoft Office Web Components.

Ever since Microsoft Office 2000 every version of Office shipped with a set of charting web components: Office 2000 Web Components (OWC9), Office XP Web Components (OWC10) and finally Office Web Components (OWC11). By definition,

Microsoft Office Web Components are a collection of Component Object Model (COM) controls for publishing spreadsheets, charts, and databases to the Web.

Web Components work in two modes:

• Interactive (dubbed "Design-Time and Run-Time Interactive")
• Static (dubbed "Run-Time Static")

You see, if you run a copy of Office on your computer and access a web page that has web components as ActiveX controls you can interact with pivot tables, spreadsheets, charts, etc. The functionality of these controls in the interactive mode surpasses that of many component vendors out there! I won't cover this mode here. Check out help files to learn more.

In the static mode

Installation and use of the Office XP Web Components does not require a valid Office XP license. However, components installed in this manner can only be accessed in view-only mode.

This is exactly what we need—to build a chart and display it as a jpg/gif/png. In this case no license is needed and you can redistribute Web Components for free (unless I'm misreading the license agreement). I'll dedicate the rest of this article to the static mode.

What's In The Web Component Pack?

A couple of very slick components: Chart, PivotTable, and Spreadsheet components. We're going to talk about charts.

Where Do I Find Help Files?

Before we move on I want to draw your attention to the documentation. I won't be able to explain every term and notion related to Web Components. The documentation covers all this in great details with lots of pictures and explanations. Provided you installed the latest version of Web Components (i.e. the Office 2003 ones) you can find help files in \Program Files\Common Files\Microsoft Shared\Web Components\11\1033. The charting component help file is OWCDCH11.CHM.

Where Do I Start?

First you install Web Components. I suggest the latest ones. If you run a copy of Office chances are they are already installed. Is it safe to install them on a server? Yes. You need them where your web code will be running. In the days of Office 2000 people used Word and Excel COM objects to create server-side "reports" and stream them to the client's browser. There are ample articles out there urging against this practice. Neither the Word nor Excel object hierarchy was meant for server-side report generation. Start with KB 257757, Considerations for Server-Side Automation of Office, and follow cross-references to other KB articles. There are plenty of them. This short disclaimer says it all:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when run in this environment.

Office Web components address this problem by accommodating server-side deficiencies of Office Automation controls.

Remember though: Office Web components are COM servers. This means COM interop once we start using them in ASP.NET. Keep this is mind.

Add A Reference to OWC11

Fire up Visual Studio.NET, create a new web project, right click Add References..., switch to the COM tab and find Microsoft Office 11.0 Object Library:

Make sure it appears in Selected Components and click Ok . Next, add the following line at the top of your code-behind file:

using OWC11;

Build Categories And Values

The discussion of nuts and bolts of building a chart can grow boring really fast. I'll only outline the basic steps and let you download and play with a sample.

I decided to build a sample chart of some 2004 Honda cars (being a Honda addict myself). I wanted to plot 3D columns with car names and their base model prices. First off, I defined "categories" (model names) and "values" (car prices). Basically, these two become the X and Y axis of a chart.

string[] chartCategoriesArr = new string [] 
     {"Accord Coupe", "Accord Sedan", 
      "Civic Coupe", "Civic Sedan", "Civic Si"};

string[] chartValuesArr = new string []
     {"19400", "15900", "13710", "13010", "19070"};

string chartCategoriesStr = String.Join ("\t", chartCategoriesArr);
string chartValuesStr = String.Join ("\t", chartValuesArr)

I figured the charting component wanted its categories and values TAB delimited which is exactly what the last two lines do. I need to warn you up front: category values should not contain commas because the charting component splits them on commas. Also, values should be convertable to integers. No commas or periods in values. Plain numbers work best.

Next, you create a chart workspace which is the top-level chart container. The workspace can contain more than one chart.

OWC11.ChartSpaceClass oChartSpace = new OWC11.ChartSpaceClass ();

At this point you set the chart type. The ChartChartTypeEnum enumeration contains 67 chart types! Even the most picky developer will find an appealing chart. Among these are 3D and 2D areas, bars, columns, stacked bars, clustered columns, 3D pies, doughnuts, radar lines, polar markers, and so forth. For this sample I chose a 3D column chart:

chartType = ChartChartTypeEnum.chChartTypeColumn3D;

The rest is beautification. You set the chart border color, aspect ration, chart depth, chart caption, location of the legend, etc. You also feed the chart those "categories" and "values" you defined previously.

In the end you have the chart component actually produce an image:

byte[] byteArr = (byte[]) oChartSpace.GetPicture ("png", 500, 500);

I chose PNG but feel free to choose GIF or JPG.

How Do We Send The Chart To A User?

This is where a lot of faithful folks drop the ball when they talk about Web Components. You have a byte array with a built chart on your hands. How do you stream it? The easiest (and dirtiest) solution is to store it on the hard drive and put a link to it on the page. Let the browser download it. This technique turns your hard drive into a junk yard and hurts performance on IO operations. Neither will this work in a web farm environment.

This is where an HttpHandler in tandem with the MemoryStream class work their magic. Let me explain. You can write an HttpHanler, put it in an ASHX file and refer to it as if it were a plain page. I described this technique in my blog post, Code-Behind For HTTP Handlers. So here's what we do: we put our byte array in the Session, identify it by a random GUID and pass this GUID on to the HttpHandler.

HttpContext ctx = HttpContext.Current;
string chartID = Guid.NewGuid ().ToString ();

ctx.Session [chartID] = byteArr;
imgHondaLineup.ImageUrl = string.Concat ("chart.ashx?", chartID);

In its turn the handler reads the byte array back, sets the correct Content-Type of the response and streams the chart image. Remember, a browser downloads each image separately which is why this trick is needed.

public void ProcessRequest (HttpContext ctx)  {

string chartID = ctx.Request.QueryString[0];
Array arr = (Array) ctx.Session [chartID];

ctx.ClearError ();
ctx.Response.Expires = 0;
ctx.Response.Buffer = true;
ctx.Response.Clear ();

MemoryStream memStream = new MemoryStream ((byte[])arr);
memStream.WriteTo (ctx.Response.OutputStream);
memStream.Close ();

ctx.Response.ContentType = "image/png";
ctx.Response.StatusCode = 200;
ctx.Response.End ();
}

That's it. We're done. One sticky point is whether to delete the image from Session or not. If you delete it right away and the user refreshes the page only a placeholder will appear in its place. Not pretty.

Hey, look what we've built:

Or:

Change the chart type to chChartTypeBar3D and you get this:

Gotcha #1

I noticed this one by accident. Apparently the charting component heavily relies on DirectX. These days the Windows Update service will nag the heck out of you to upgrade to the latest version of DirectX so I wouldn't worry about this too much.

Gotcha #2

I honestly don't know if any hosting companies have Office Web Components installed and whether they will agree to if they don't. My host (CrystalTech) refuses so far, therefore I can't run the sample live. To make matters simple for them I hacked the installation and figured that registering OWC11.DLL alone is sufficient. They refused to register even that one DLL. See if you have better luck.

Conclusion

I hope this article introduced you to the power of Microsoft Office Components. I don't quite understand why they are so little advertised by Microsoft itself. Right at your fingertips you have functionality that most component vendors can't even match, and this functionality comes for free. Explore, experiment and build dazzling charts without destroying your budget!

July 4, 2005, update: OWC object model and terminology banner is now available.

Discuss

Liked it? Hated it? Discuss this article