Live ASP.NET Charts with Office Web Components
By Peter A. Bromberg, Ph.D.
Printer - Friendly Version
Peter Bromberg

There are a number of new .NET "Charting" graphics server controls, and except for a few freebie ones that are kind of rough around the edges, they are pretty much all in the $200 to $400 range - or higher. But what about Office Web Components? OWC XP is now free-threaded. Yes, its COM. But Visual Studio.NET can handle the creation of your RCW COM wrappers very handily. Since OWC is a free download , (you are supposed to own Office XP, so check the licensing requirements), I thought it might be a good start, especially since I thought I'd be able to build on my successes with my previous article, "Streaming Excel in ASP.NET with OWC" . We need to create charts over the web where I work, and they don't like to authorize purchases of expensive components (surprise?).



Unfortunately, nobody's written an article that I could find about how to do it. And the documentation on using OWC Chart control in MSDN is downright ugly! Ugly as a bulldog chewing on a wasp! They want you to do it client side with an <object .. tag and XML data islands. The only other solution they had was to transform Dataset Xml into an ADO Dataset representation. Ouch! Even UGLIER! I don't want to do it client side. I wanted to do it server side, save the gif to the filesystem, and stream it with an image control.

After reading some of the COM - based documentation, which was the only thing I could really find besides the ugly code, I finally figured out an elegant way to do it. One of the acceptable data sources for the control is a tab-delimited string ( It also accepts an object array, but I gave up on that). So by getting a regular .NET DataSet, you can use the WriteXml method and zap it into a StringWriter. Load that into an XmlDocument instance, and then you can iterate the nodelist with your data and convert it into a tab - delimited string and the control will be happy!

To use the OWC Chart Component, all you need to to is set a COM reference to the installed library in ASP.NET. Fire up a new ASP.NET WebForms application, and stick a button and an Image control on the design surface.

Click "Add Reference" from the Solution Explorer, choose the COM tab, and find the library, which will look like this:

 

Click "OK" and VS.NET will build a COM Interop wrapper for you, called appropriately, "OWC10". Add the following using statements to your WebForm codebehind class:
using OWC10;
   using System.IO;

What I'll show next is the code to get a DataSet from the SQL Server Northwind Database, populate the control with your data as I described above, save the file as a GIF, and populate your Image Control's ImageURL property so that when the postback occurs, you have your chart! I've also included a helper method that checks the file system every time a user requests a chart and cleans up any GIF files older than 2 minutes:

private void btnChart_Click(object sender, System.EventArgs e)
{
SqlConnection cn =
new SqlConnection("Server=(local);DataBase=Northwind;user id=sa;password=;");
cn.Open();
DataSet ds = new DataSet("Chart");
SqlDataAdapter da = new SqlDataAdapter("CustorderHist 'ALFKI'", cn);
da.Fill(ds);
OWC10.ChartSpaceClass oChartSpace = new OWC10.ChartSpaceClass();
System.IO.StringWriter sw = new System.IO.StringWriter();
XmlDocument xDoc = new XmlDocument();
ds.WriteXml(sw);
// clean up
cn.Close();
da.Dispose();
ds.Dispose();
xDoc.LoadXml(sw.ToString());
sw.Close();

System.Xml.XmlNodeList nodes;
nodes = xDoc.ChildNodes.Item(0).ChildNodes;
int nCount = nodes.Count;
string[] aNames = new string[nCount];
string[] aTotals = new string[nCount];
string names=String.Empty;
string totals =String.Empty;
int i = 0;
for(i=1;i<nCount;i++)
{
aNames[i]= nodes.Item(i-1).ChildNodes.Item(0).InnerText;
aTotals[i]= nodes.Item(i-1).ChildNodes.Item(1).InnerText;
}
//Chart control accepts tab-delimited string of values
names= String.Join("\t", aNames);
totals= String.Join("\t", aTotals);
oChartSpace.Charts.Add(0);
oChartSpace.Charts[0].SeriesCollection.Add(0);
oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC10.ChartDimensionsEnum.chDimCategories,
Convert.ToInt32(OWC10.ChartSpecialDataSourcesEnum.chDataLiteral),names );
oChartSpace.Charts[0].SeriesCollection[0].SetData(OWC10.ChartDimensionsEnum.chDimValues,
Convert.ToInt32(OWC10.ChartSpecialDataSourcesEnum.chDataLiteral),totals );
string strFullPathAndName=Server.MapPath(System.DateTime.Now.Ticks.ToString() +".gif");
oChartSpace.ExportPicture( strFullPathAndName, "gif", 800, 600);
Image1.ImageUrl=strFullPathAndName;
Image1.Visible =true;
RemoveFiles(Server.MapPath("."));
}


private void RemoveFiles(string strPath)
{
System.IO.DirectoryInfo di = new DirectoryInfo(strPath);
FileInfo[] fiArr = di.GetFiles();
foreach (FileInfo fi in fiArr)
{
if(fi.Extension.ToString() ==".gif" )
{
// if file is older than 2 minutes, we'll clean it up
TimeSpan min = new TimeSpan(0,0,0,2,0);
if(fi.CreationTime < DateTime.Now.Subtract(min))
{
fi.Delete();
}
}
}


}

Presto! Dynamic ASP.NET web -based charts from your data, and the price is right. (And the code ain't ugly, either!). You can download the sample solution below.

Download the Code that accompanies this article

 


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.