Excel chart in ASP.NET

How to display an excel chart in an ASP.NET webpage

You can use Office Web Components (OWC)

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)

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:

Microsoft Office XP Web Components

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();
}
}
}
}

Advertisements

%d bloggers like this: