Congratulations, Milan. You did a great job on the artical.

I think there is a minor type. Instead of switch to the COM tab and find Microsoft Office 11.0 Object Library: , it shoudl be switch to the COM tab and find Microsoft Office 11.0 Web Component: .

cheers,

Jay

I downloaded and succesfully ran your application. It's awesome. I carefully converted it to VB, but it doesn't run there. Stepping through the debuggers, everything look similar. I'm stumped. Do you have a running VB version?

Paul, I don't have a VB.NET version yet. But since you asked... I'll give it a try too.

Paul, I've added a sample VB.NET project to the downloadable code. Have fun. ;)

Any word on how to set up an interactive chart?

Well, it's a whole different story that involves ActiveX controls and stuff like that. If you download the OWC10/11 redistributable you'll find a bunch of help files in it: one for each component and one for everything. They provide samples of how to use these components interactively.

I'm very interested in getting this to work on my development machine, but I keep getting the same error over and over:

System.InvalidCastException: QueryInterface for interface OWC11.ChChartSpace failed.

The error occurs on this line of the VB sample app:
oChartSpace.Border.Color = "white"

Any ideas? It must have something to do with how the Office components are installed on my machine, but I have no idea what it could be. I'd enormously appreciate any help you can give.

Well, the samples I provided used an interop assembly with OWC11, which is Office 2003. Is there a chance you installed OWC10 (Office XP) or OWC9 (Office 2000)?

Also expand "References" in your project and see if there's a yellow "warning" icon next to "OWC11". If there's one I suggest you re-add a reference to Microsoft Office Web Components 11.0 as explained in the article.

All in all, it makes little difference whether you use OWC11, OWC10 or OWC9. I haven't noticed any changes among these versions. You'll need to simply change references throughout your code.

That should be pretty much it. Let me know how it goes.

I trying to use OWC11. On the statement 'Set pTable = frm.PivotTable', I get a Type mismatch error 13. If I define pTable as an Object, I don't get this error. Can you tell me why OWC11 is not working for me. I'm using MS Access 2003. The references I have are Visual Basic for Applications, Microsoft Access 11.0 Object Library, OLE Automation, Microsoft Office Web Components 11.0 and Microsoft ActiveX Data Objects 2.8 library.

Dim frm As Access.Form
Dim pTable As OWC11.PivotTable
Dim pFieldset As OWC11.PivotFieldSet
Dim pField As OWC11.PivotField
CustomerCountProdID = "Allocate"
RecordSource = "SELECT ProdID, Date, Total, Comments FROM CustCounts WHERE (ProdID = '" & CustomerCountProdID & "')"
Set frm = Forms("CustCounts")
Set pTable = frm.PivotTable

Thank you,

Rebecca

Rebecca, quite honestly, I don't know why it's happening. I haven't worked with Access and VBA for a very long time now.

Great article Milan.

I am having the same problem as Neil on my development machine, but the code works fine on 2 other servers. Stumps me.

Hey, Neil: were you able to get around the "System.InvalidCastException: QueryInterface for interface OWC11.ChChartSpace failed." error on your dev. box.

Anyone else having similar problem and then fixed it, I am curious to know too. Thx.

I have the same type of problem.
But when I am running the application in my local machine, it is working fine , but when puting the files(dll, *aspx) into real web server then I am getting the error.
Any idea ...............

Is your real web server managed by a hosting company? Usually, they are reluctant about installing any COM objects. My hosting company has refused so I can't run a live sample. :(

I wanted to change the category values (from honda types) to dates. Still wrapped in double quotes and the array declared as a string I didn't think there would be a problem. The format is....
{"dd/mm/yy","dd/mm/yy".... etc}
However when I view the page the dates aren't what is expected.
The following values:
Dim chartCategoriesArr As String() = {"17/09/04", "18/09/04", "19/09/04", "20/09/04"}

shows on the image as:
01/01/25 01/01/32 01/01/39 01/01/46
Notice the pattern? +7 each time.
The chart type is
chartType = ChartChartTypeEnum.chChartTypeLineStacked

any ideas what is going on?
These are simply meant to be labels along the bottom of the graph

In the article I mentioned you could only use integers. As I figured out later, it's not quite correct. You can use just about anything as long as you indicate their types. I need to dig through documentation and find it.

I think in your example the dates are treated simply as strings which is incorrect.

Apparently .net/excel(???) was trying to treat them as dates even though they were dimmed and supplied as strings. A colleague of mine gave me this line:
oChartSpace.Charts(0).Axes(0).GroupingType() = oChartSpace.Constants.chAxisGroupingNone

and it stopped the problem. Got a new problem now!
For some reason I now get invalid parameter errors (vb.net version) seems there is no value (according to debug/command window) for oChartSpace.Charts(0).SeriesCollection(0)

In the command window I type
? oChartSpace.Charts(0).SeriesCollection.count
and it returns 0

I'm sure I haven't changed anything (in this area) does the series collection need to be manually added to the charts(0)?

Oops ignore the previous msg!
I'd commented out
oChartSpace.Charts(0).SeriesCollection.Add(0)
oChartSpace.Charts(0).SeriesCollection(0).DataLabelsCollection.Add()
for some reason (blush!).

Last question (I promise!)
How can I use "x"s to plot on the chart rather than the actual values?

Quite honestly, I don't know. Anyone?

I think it may be a subset of
oChartSpace.Charts(0).SeriesCollection(0).DataLabelsCollection(0).

but I can't make anything out of the MSDN/VS.Net documentation!

Milan,
I have downloaded your sample code.
It's very easy to read.
BUT!!!!!!
the chart comes out as a 2D and not 3D chart.
Your article shows 3D pictures.
Am I missing anything?
I didnot modify your dowloaded code at all.

Please, repond.

Thanks,
Marina

Marina, I had that happen to me when my DirectX was way out of sync with the latest codebase. Go to Microsoft's update site and pull down the latest DirectX. That's what OWC controls bank on for 3D effects.

Hello Milan,

Great article I am able to make it generic and it is working cool.

One query, wot is the Series mean? wot the define? wot is the purpose? And does this xample gonna work with "Stock Chart?"

Take Care,

Mudassir

San Jose, CA

Mudassir, check out the help files I mentioned in the article. They have nice charts there that visually explain all these notions.

Excelent article Milan.
Because I've Office 2002 I added OWC 9.0 but when I tried to compile project it was giving me several errors that I presume are because I'm nout using OWC11, I commented such code lines (e.g
oChartSpace.Charts[0].SeriesCollection[0].DataLabelsCollection[0].Font.Name = "verdana";)
When I arrive at this code line:
byte[] byteArr = (byte[]) oChartSpace.GetPicture ("jpg", 500, 500);
I was not able to continue, the error: OWC.ChartSpaceClass doesn't contain a definition to GetPicture.
Do you know how can I solve it?
Remember I'm using OWC 9.0
I'll appreciate your help

A.L.
Mexico City

Adolfo, quite honestly I don't know. It seems they didn't have this method back then or named it differently. I suggest you look up the ChartSpace class in documentation and see what the proper method and syntax are.

Hello Milan:
I modified my project to better use OWC11 instead OWC.
In chart.ashx.cs and default.aspx I changed png for jpg, but when I run project omage imgHondaLineup apprear as this weren't therein (only shows a red "X").
What have I wrong?
Please reply me.
A.L.

Hmmm... The original sample code uses PNGs for images. The only two things to watch out for are the proper content type (ctx.Response.ContentType = "image/png") and actually creating a PNG image (GetPicture ("png", 500, 500)).

On my development version my 3d charts works fine. However when I place the code on another server 3d charts appear as 2d graph types. I am currently using oWC10 and on the second server I have downloaded the current version on directX.

Does anyone know what is missing from my real web server.

Hi guys!
I've a problem with the examples: doesn't work! :o(
I open the VB.NET example and I click on "Run" and appear this error message:

"Error while trying to run project: Unable to start debugging on the web server. The project is not configured to be debugged."

What is this nightmare???

Thanks to all

Open up web.config, find the "compilation" tag and set debug="true".

It's already set to true! :o(

Ok! I've understand where is my problem!
The project path doesn't work 'cause it's not configured into IIS!
I've Windows Italian version and I'm not sure to translate correctly:

Follow this steps:
Open "Control panel" -> "Administrative tools" -> "Internet Information Services"

On left the're a tree with your computer name -> Web sites

Right click on "Default Web Sites" -> "New" -> "Virtual directory"

I hope that help!

Now it's fully functional! Great work Milan!

Hi all,

Milan, thanks for the great article. I am trying to create the chart using your example and I am getting an error. Can you please inform me on what could be wrong. As far as i can tell, i have copied all code correctly. The code in the web form runs smoothly. I have downloaded the most recent version of OCX11.dll. I am getting an error with the chart.ashx file. When i run the web form, i get a broken image icon and the following error when i try to load the .ashx file directly in IE:

Parser Error
Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately.

Parser Error Message: Could not create type 'OwcCharting.Owc.ChartHandler'.

Source Error:
Line 1: <% @ WebHandler language="vb" class="OwcCharting.Owc.ChartHandler" codebehind="chart.ashx.vb" %>

Source File: c:\inetpub\wwwroot\OwcCharting\chart.ashx Line: 1
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032

I tried to provide samples for educational purposes so you could simply pull in the code into your projects and go from there. Perhaps, I should've included a compiled assembly.

This is exactly what's happening. The page tries to reference a compiled DLL which is not there. You either need to compile the sample or copy and paste the code into your own app.

Too funny. That was exactly the problem. Gotta say, the article is awesome. Thanks for writing it and thanks also for the super fast response!

Gregg

I am trying to create a line chart that will display multiple lines. I have successfully created one line, but would like to have multiple lines. I hope this question isn't too newbie, but I haven't used Office Components before. Thanks!

I answered my own question. I just needed to add a new Series Collection to the Chart object and then set the data for it.

// add the new series collection
oChartSpace.Charts[0].SeriesCollection.Add(1);

// add the data for the new series collection
oChartSpace.Charts[0].SeriesCollection[1].SetData(OWC11.ChartDimensionsEnum.chDimValues, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), the_new_data_array);

I'm back. Never did get that puppy to run. I've tried using both the OWC10 and OWC11 libraries, and I've tried it in C# and VB ... I always get the same error:

QueryInterface for interface OWC11.ChChartSpace failed

on the line
oChartSpace.Charts.Add(0);

Guess it's just one of those things. I can't figure out why it comes out "ChChartSpace" - what's with the extra "Ch"? (Sigh.)

For some reason the code just can't find the right COM objects. Strange indeed. I think something is up with the installation.

To answer the second part of your question: the COM-callable wrapper (CCW), created for Office web components, has its own majic and changes naming around a bit. If you refer to the help files provided with OWC, you'll see that ojbect and method names differ from those inferred for the CCW. That's just the nature of the beast. I had to hack it because there's no .NET documentation for OWC.

Great article!

I have a couple of funny ones to ask. Firstly when i publish on my webserver with office 2000(OWC9) on it and there are many columns on it does not write the column names vertically like it does on my development box with OWC11, It instead breaks up the column names into small fragments. Is this a known problem? Will it be solved by installing OWC 11?

Secondly when I try and create a graph of multiple types ie bar graphs with line graphs over it, it stops me from having more than one graph of the second type. Does OWC support this and if so can you point me in the write direction?

If you feed too many columns/rows, the OWC chops descriptions with an ellipsis so it can fit them all. You just need to play with different types of charts and see which one works. Or try to plot against a smaller set.

I'm not sure how to answer your second question. I don't think you can combine multiple charts in one. You'd have to build each chart separately. Now, you can still build "clustered" charts which are "more of the same", i.e. they will stack up on top of each other, or behind each other.

Hi Milan,
I found your article very helpful.I have just started to learn about web components.I wanted to create a stacked column chart and i was trying to figure how i can stack the columns.Can you advice.
Thanks.

In the source code try setting the chartType variable to chChartTypeBarClustered, chChartTypeColumnClustered, or chChartTypeLineStacked enumuration and see which one works better for you.

This is in reference with the previous question i asked and your answer.What i wanted to know was,in your example your use two arrays one array lists the name or Categories,and the other list values.And you add it to the chart space using oChartSpace.Charts[0].SeriesCollection[0].SetData (some arguments).In case of stacked i have three arrays.1-categories(car names),2- values(% of good cars),3-values(% of bad cars).how can i stack values from array three on array three.For ex.Accord Coupe had 75% good cars and 20%defective.I want 75% in green,over that 25% in red on my stack graph.Same case for all other cars,good cars in green and defective in red.I hope you understand what i am trying to tell.
Thanks!

Well, if I understand your question correctly, you can use the chChartTypeColumnStacked1003D chart type to stack values on top of each other. Each range within a column will be colored differently, and all values will add up to 100%. Try this setting and let me know if this is what you're after.

Hi Milan,
The article was very helpful to me. I am also trying to display data from backend(access, sql server) on to a web page(intranet) and need to do that in the form of a excel sheet. Also need to export the same to an excel sheet to the client pc. Have seen the toolpack for office xp web component, but could not make the code snippets work. Would you suggest any good books for the same which has some helpful code samples so that i can get up to speed.
Any help is appreciated...
Thanks
vinod

Unfortunately, there's almost no documentation on OWC short of help files shipped with them by Microsoft.

If you need reports in Excel form, I suggest you look into using them as ActiveX controls because the "static" view I described here would hardly cut it.

Milan,
I am trying to generate charts with OWC9 not owc11 and there is not getPicture method in OWC9,DO you know what i have to use in OWC9 to do same functionality as getPicture of owc11.
Thanks.

Zameer, no, I've never worked with OWC9. Sorry.

Currently using OWC11 for web based reporting (returning data from a database and charting it)...

Various reports of mine have drill down functionality and it would be nice to be able to link from each section in a graph...

For Example...

Bar Chart - Bar 1 links to Page 1, Bar 2 links to page 2 etc...

I have had a look and I can only see a way to link a whole series of data to one particular page...

Any thoughts?

PS. Thanks for the article, it was very helpful...

Thanks

Giles

Hi Milan,
i have been trying this for few days hope you could help.I am building a stacked chart,if one value in stack is zero,i dont want it to display it as zero but as blank value.for example the array is(5,3,0,2,0) DO you know how i can do it.One bar which is stacked one on top of other.
Thanks.

I wish I could help, but I've never dealt with blanks in my charts. Have you tried setting it to something very, very small, such as 0.01?

Giles, what you're talking about should happen client-side and has little to do with image generation per se.

You would need to create a map or something for people to click on. I don't know if this can even be done because you would have to have exact coordinates of each slice in a pie chart, for example.

I suggest you look into a Flash-based approach if you need interactivity with a chart.

Thanks for looking into it, i shall take a look at the flash approach, I believe swiff chart is good for this kind of thing...

Many thanks

Giles

Very nice article. I was researching OWC all day yesterday and this is the best article I found on the web. There is a book being released on OWC11&.net:http://books.lulu.com/content/82300 which looks decent. I will probably wait to buy it until I read some reviews though.

Milan,

Fantastic article. I have developed a site with charts using OWC on the server side. Do you or anyone know of a hosting company that supports them?

Thanks in advance,

Mauricio

One of my readers said he convinced his ISP, CrystalTech, to install OWC11 on their server. I'm with CrystalTech too, but I haven't approached them yet. I don't think any providers have them pre-installed.

All you have to do is ask your ISP's tech support if they would install OWC11. Give them links to Microsoft's site so they can see it's a piece of perfectly legit software.

In your article you mention: In this case no license is needed and you can redistribute Web Components for free (unless I'm misreading the license agreement).


This is not correct. It is a violation of the terms of licensing to redistribute the Web Components. Please update this information. The licensing agreement may be found in the toolpack or on the Microsoft office website. Alternatively, you may open a support incident with Microsoft to rectify this issue. Licensing is complex enough to require its own chapter. Fortunately, I explore licensing of the Office Web Components in complete detail in my new book : The Microsoft Office Web Components Black Book with .NET available at www.lulu.com/owc. I hope this helps.

Alvin, unfortunately, my reading list is very long right now. Can you point out what is incorrect about that assertion so I can change it?

I understood Milan's comment about the Web Component redistribution to mean that, if the user has no Office 2003 license, OWC 10 and 11 allow the use of OWC "with no reservation in view-only mode" (MS kb 555075).

I don't think Milan was saying that he advocated distributing the OWC libraries themselves, instead just that a developer could create charts and redistribute them with no legal worries; possibly this is where the confusion arose? Indeed, that same kb states "Redistribution of the Office Web Components is strictly forbidden and runs contrary to the terms of use". That is, downloads of OWC must come from the MS website, not the developed application. However, as we know, we don't need the user to install OWC to view the chart images that we generate.

Perhaps, Milan, rewording to "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 generate and output charts to the user for free, without the user needing to install OWC themselves." would keep everyone happy?

Legalities aside, thank you for the article, Milan. The real gem for me was the location of the help files, which I had pretty much given up on finding on the web, and hadn't thought out of the box enough to look on my own workstation :$

On a more philosophical bent; I think it is a shame that the OWC components are so under-appreciated and seemingly little-understood and under-supported. Excel must be one of the most powerful and widely used generic business tools, OWC provides a lot of that power, and really focuses it on the way that technology is pushing, and has been moving for a long time: distributed applications on the web. There cannot be a single developer out there who uses OWC in .NET applications who has not had to reinvent the wheel by themselves. Surely there is a need for a good collaboration between OWC+.NET developers to pool techniques, build up an FAQ list and a list of articles as good as this one to start to hold OWC to account?

Andy, the FAQ idea is a good one! I thought about it a while ago, but besides OWC's documentation (which is VBScript and therefore doesn't have a 1-to-1 translation to .NET languages) it's hard to find good reading material. It'll definitely take a bit of hacking to document OWC for .NET devs.

I agree that these components are underappreciated. In the interactive mode, i.e. when used as ActiveX controls on desktops with Office XP/2003, it's mind boggling what they can do!

Is there a newsgroup (or perhaps there *should* be one) that is dedicated to addressing office web components? or even a forum would be handy... i am just starting to develop an asp.net application which will rely heavily on the use of the charting component, and would really like to see some more real world examples of it put in use, as well as addressing some of the more obscure applications.. i think we could all benefit from this sort of brainstorming and knowledge sharing.

I've never seen such a newsgroup.

Nothing dedicated as yet that I've seen, but try microsoft.public.office.developer.web.components
DbForums.com access

Andy

Hi everybody and thanks for the great post,

i'm creating some charts (VS.Net / C# / OCW11) and i'm trying to use the SetSata Method with a DataSet filled with many records from a Database.

Is it possible to do it directly without iterating on it ( sVal += '\t' + row[0].ToString();) ?

Can i bind my dataset in the setdata function ?

and what are the different uses of the parameter ChartSpecialDataSourcesEnum ?

Thanks for help

Hi,
Thanks for the article. When displaying data as a BarChart ( chChartTypeBarStacked) the categories seem to appear on the Y-AXIX and the values on the X-AXIS ..Is there a way to change this so that the chart does the reverse i.e categories on X-AXIS

Thanks,

Mike

i need fully functional owc line chart with dynamic lable on chart line

My apologies, folks, but I have to close comments. This thread turned into a forum of itself. :) Besides, I published the article quite some time ago.

If time allows, I'll compile a big fat OWC FAQ.