Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 3. August 2009 10:16

I just completed a fun project a few days ago, a C# application which performed lots of reading from and writing to Excel. One small problem got me stumped: I know how to  add a standard chart, but I couldn’t figure out how to add charts from the Custom Types selection. Most of these are utterly useless (There is an “Outdoors Bar” type, which is “A bar chart with an outdoor look”. I am not making this up.), except for one: the “Line – Column” chart type, and its variations on 2 axes.

OutdoorBars 

I like Outdoorsy charts

The VBA macro recorder spat out this:

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

So I check the Chart object in C# and sure enough it has a method ApplyCustomType(Excel.XlChartType CharType, object TypeName). Problem: the enumeration Excel.XlChartType does not contain anything like XlBuiltIn. Damn.

Long story short: xlBuiltIn is to be found in the enum XlChartGallery, and the type name is passed as a good old magic string. This code does the job:

Edit, August 20, 2009: this code works for Excel 2003, but not for Excel 2007. Check this post for an updated version of the code which follows the suggestion of Jon Peltier, Master of Charts.

// create your chart first
string builtInType = "Line - Column";
Excel.XlChartType customChartType = (XlChartType)XlChartGallery.xlBuiltIn;
chart.ApplyCustomType(customChartType, builtInType);

Comments

7/28/2009 1:07:33 PM #

Jon Peltier

The xlBuiltIn chart types are really pretty useless. You are better off making a combination chart from scratch: make a column chart with all of the data, for example, then convert one or more series to lines, and if necessary move some to the secondary axis.

Why not use the line-column built-in custom chart type ("built-in custom" - what a great oxymoron)? Here's why. Create a line-column built-in chart with one line and one column. Add two series. The chart now has two lines and two columns, and the second series in the original chart has changed from one type to another. Maybe this is what you expected, but probably not.

Jon Peltier United States | Reply

7/28/2009 1:54:00 PM #

Mathias

Jon,
I like your suggestion. The fact that the built-in type is a straight string, and not a full-fledged enum, suggests half-baked design... I am working on a set of utilities for C# developers who want to interact easily with Excel, I'll definitely follow your idea for combination charts!

Mathias United States | Reply

8/10/2009 12:54:08 AM #

Dalvir Singh

Mathias
Are you able to combine line-column chart like jon said?? if yes please post code here .....

Thanks

Dalvir Singh India | Reply

8/10/2009 11:57:28 AM #

Mathias

@Dalvir: done! See the post:
www.clear-lines.com/.../...art-in-C-revisited.aspx

Mathias United States | Reply

4/16/2010 11:03:55 AM #

trackback

WPF, Silverlight Toolkit supports stacked series

WPF, Silverlight Toolkit supports stacked series

Clear Lines Blog | Reply

5/17/2010 8:14:22 PM #

pingback

Pingback from itbiancheng.com

IT编程之家  » Blog Archive   » WPF, Silverlight Toolkit supports stacked series

itbiancheng.com | Reply

5/20/2010 4:18:31 AM #

pingback

Pingback from 42.1fh.org

Fashion Toyota Yaris, Yaris Part Auto

42.1fh.org | Reply

12/23/2010 9:31:51 AM #

pingback

Pingback from cheaphomeideas.com

Swimwear For Me | Cheaphomeideas.com

cheaphomeideas.com | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS