Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 26. May 2010 06:55

I finally got to reviewing and scrubbing the code for the part 2 of my Excel 2007 VSTO tutorial; you can download the code here. Next chapter, we will venture into the joys of deployment.

In the meanwhile, please feel free to let me know in the comments what you think, like and dislike, and how I can make this better!

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.


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);
by Mathias 9. April 2009 10:54

In my previous post, I described how to programmatically add an embedded chart to an Excel worksheet, and what issues I encountered in the process. I was not very happy with the solution, and figured out a much better way to do this, inspired largely by this. Rather than add a chart to the workbook, and then set its location, it is possible to directly add the chart to a worksheet, by using the following syntax:

public static Excel.Chart AddEmbeddedChart(Excel.Worksheet worksheet, 
Excel.XlChartType chartType, Excel.Range dataRange, Excel.XlRowCol byRowOrCol, 
string title, double left, double top, double width, double height)
    Excel.ChartObjects chartObjects = (Excel.ChartObjects)(worksheet.ChartObjects(Missing.Value));
    Excel.ChartObject chartObject = chartObjects.Add(left, top, width, height);
    Excel.Chart embeddedChart = chartObject.Chart;
    embeddedChart.ChartType = chartType;
    embeddedChart.SetSourceData(dataRange, byRowOrCol);

    embeddedChart.HasTitle = true;
    embeddedChart.ChartTitle.Text = title;

    return embeddedChart;

Instead of creating a chart sheet first, and then re-locating (a copy of) the chart into the target worksheet, this version directly creates the chart in the right place. The cherry on the cake: you can set the location and size of the chart immediately, instead of having to perform acrobatics to retrieve the chart in the sheet...

So why did I go the wrong path first? Because of my best but not always reliable friend, the Macro Recorder.


by Mathias 11. December 2008 16:58

Apparently, VSTO Power Tools have been around for a while (Feb 02), but if I had not read this article, I would have missed them - which would be too bad, because they are awesome. The Power Tools consist of a few dlls, which, while not officially supported, have been released by Microsoft developers.

I started playing with the Excel Extensions, and I love it; it is a "very thin wrapper to the Office primary interop assemblies", which essentially gives you cleaner methods to access the Excel object, with type safety, and without the clumsy "Missing.Value" arguments. More...

by Mathias 16. November 2008 12:26

Someone commented to my post on add-in menus asking if it was possible to add sub-menus to menus - the answer is yes. The code is essentially the same, with one small difference. When you add a menu item to a menu, you will add a CommandBarControl (the menu item) to the controls of a CommandBarPopup, the menu container. If you want to add a "nested" menu to the menu, instead of adding a CommandBarControl, you will add a CommandBarPopup, which can then receive menu items (or more nested menus!).

In code, it would look something like this:

// Add the sub-menu to parentMenu, which is a CommandBarPopup

CommandBarPopup parentCommandBarControl = (CommandBarPopup)parentMenu.Controls.Add(
MsoControlType.msoControlPopup, Type.Missing,
Type.Missing, Type.Missing, true);

parentCommandBarControl.Caption = "Sub-Menu";
parentCommandBarControl.Visible = true;

// Add the menu item to the sub-menu

CommandBarControl commandBarControl = parentCommandBarControl.Controls.Add(
MsoControlType.msoControlButton, Type.Missing,
Type.Missing, Type.Missing, true);

commandBarControl.Caption = menuItemCaption;
commandBarControl.Visible = true;


Comment RSS