Create Excel Line - Column Chart from C#

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

Have a comment or a question? Ping me on Twitter, or use the comments section!