Developing (and deploying) Excel Addins in C#

I recently had the “joy” of developing an Excel plugin. This pulled data off one worksheet, sent it off to a web service, wrote some values back to multiple worksheets and created a report that was a worksheet designed to be printed on A4. I know Excel is the least hip development thing at the moment, but let’s face it, it’s used by everyone in business for everything (even though it shouldn’t), so it makes sense being able to develop for it properly. Also, plugins let us turn Excel into a cloud enabled application which let’s us do cloud through the “back door”. Now that’s cool.

While doing this, I learned a load of good practices and ways to avoid potential pitfalls. Also, all the information I needed was scattered across the web, so I though this would be a good place to bring all the strands together, as well as share tips and tricks if ever you have the same “joy” imposed on you.

Getting Started

Visual Studio 2008 and 2010 make getting started very easy. Just create the correct project type. This seems like a useful resource for getting started: http://msdn.microsoft.com/en-us/office/hh128771.aspx

A good starting point for playing with functionality is to create a “ribbon” (add / new item / office) and stick some buttons on it. You can then double click these buttons to add on-click events (al-la Windows forms). From that point you’re free to mess about.

One thing to mention that isn’t initially clear is that the moment you compile your add-in, when you start Excel, your add-in will start too (even when you don’t start debugging). Still not entirely sure how this is done. Note that if you change code and re-compile you have to fully re-start Excel (not jut close and re-open the workbook) before it picks up the changes. That means closing all instances of Excel and re-opening.

Anything you actually do with Excel will be via the COM interop library (Microsoft.Office.Interop.Excel) and will all seem remarkably untyped in the C# auto completion. You’ll be asked to pass in and receive “objects” back from most functions. Don’t be fooled into thinking this is some easy going scripting type scenario though, exact types are required, which can only be found through (rather sparse) examples.

Reading and writing to cells / rows / ranges

The easiest way to get data from and insert data into cells is to use the get_Range() function, then to use it’s “Value2” property (no, I don’t know why it’s 2).

For example, to get and set the value in cell “A1”, use the following call (don’t worry about the Excel prefix, that’s a shortened namespace that’s added automatically when you’re developing add-ins):

Excel.Worksheet activeWorksheet = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet);
object cellValue = activeWorksheet.get_Range("A1", System.Type.Missing).Value2;
activeWorksheet.get_Range("A1", System.Type.Missing).Value2 = "My new value";

To get the value of the cell as a string (which it usually is), stick a ToString() on the end of the Value2. But be careful in this, if the cell is blank, Value2 will be null, so ToString() will blow up. Also, note, that because I’m picking a single cell, I pass “System.Type.Missing” as the second parameter. I came unstuck a few times, passing in null before I discovered this.

You’re not limited to selecting a single cell, you can select a range (as the name implies). Value2 get’s abit more tricky when you do this. Value2 can be a 2 dimensional array of objects when you get it, and all cells in the range will be set to Value2. This example gets the square of cells A1-B2, then writes B2 to all of A3-B4 (it’s worth mentioning at this point that Excel is generally 1 indexed, rather than 0):

Excel.Range range1 = activeWorksheet.get_Range("A1", "B2");
object[,] s = (object[,]) range1.Value2;
Excel.Range range2 = activeWorksheet.get_Range("A3", "B4");
range2.Value2 = (string)s[1, 2];

If you then want to tweak individual characters in cells, you can call get_Characters() on a range, for example, to make the first character of all the cells A3-B4 to bold (italics, underline etc are all accessible this way):

range2.get_Characters(1, 1).Font.Bold = true;

To get whole rows and columns (as ranges) use the following:

(Excel.Range)reportWorksheet.Columns["A", System.Type.Missing]
(Excel.Range)reportWorksheet.Rows[0, System.Type.Missing]

Notice that Rows are zero indexed, just to confuse things. Lastly, to get more formatting options, use the Style attribute on you’re range, for example, setting horizontal alignment in a range:

((Excel.Style)(range1.Style).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

The style should let you get at fonts, colours and pretty much anything else you’d want.

All that should get you started. To add formulas, just assign a string with an =’s in front of it to Value2 (like you would typing into a cell in Excel), simple.

Using images from project resources

First thing you’ll be asked to do, is have some sort of image being placed in your worksheet. This will have to come from the resources of your project, not somewhere on the client machines disk. Annoyingly, Excel’s functions don’t support this, so you could waste a lot of time figuring out this trick.

The idea is to place the image from the project resources into a temporary location, then read that into Excel. The CreateTemplateImage() function takes a passed in (System.Drawing) image saves it to the disk and passes back the path of the image ready for Excel to use. I’ve also created a singleton MyLogo, that does this writing to disk and stores the path so it doesn’t have to do it again. Finally, I use the MyLogo property to pass into the Excel function that creates the image on the worksheet:

private static string CreateTempImage(System.Drawing.Bitmap image, System.Drawing.Imaging.ImageFormat format, string fileName)
{
    //create temp file path
    StringBuilder path = new StringBuilder(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData));
    path.Append("\\" + fileName);
    //save resource into temp location in HD
    image.Save(path.ToString(), format);
    return path.ToString();
}

private static string myLogoPath = null;
public static string MyLogoPath
{
    get
    {
        if (myLogoPath == null)
        {
            myLogoPath = CreateTempImage(Properties.Resources.myLogo, System.Drawing.Imaging.ImageFormat.Png, "myLogo.png");
        }
        return myLogoPath;
    }
}

activeWorksheet.Shapes.AddPicture(MyLogoPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 1, 1, 100, 100);

This way, you can monkey about with the images to your hearts content and the main code just uses the property.

Placing images and charts

When you place images and charts (a full listing of a customised chart is available in Appendix 1), you have to supply: top; left; height and width, as you would expect. However, the values you supply aren’t rows and columns, they aren’t pixels, they’re some other whacky measure I still don’t understand (pixels * 0.7 or so). To save myself having to worry about this and having to re-jig all my values when I entered a new row or piece of text, I tied my image and chart sizes and locations to ranges. All I have to do now is say that an image is going to fill a certain range (with a margin perhaps) and then I can play with that range on the worksheet.

I start with a class that holds the definition of a rectangle as floats:

public class FloatRect
{
    public float Top { get; set; }
    public float Left { get; set; }
    public float Height { get; set; }
    public float Width { get; set; }

    public FloatRect(double top, double left, double height, double width)
    {
    	Top = (float) top;
    	Left = (float) left;
    	Height = (float) height;
    	Width = (float) width;
    }
}

To populate this I use the following function that takes a worksheet, the “top left” and “bottom right” definitions of a range and an optional margin parameter (null for none) .

private static FloatRect GetRangeBoundaries(Excel.Worksheet worksheet, object rangeTopLeft, object rangeBottomRight, float? margin)
{
    Excel.Range range = worksheet.get_Range(rangeTopLeft, rangeBottomRight);

    FloatRect rect = new FloatRect((double) range.Top,
                                   (double) range.Left,
                                   (double) range.Height,
                                   (double) range.Width);

    if (margin.HasValue)
    {
        rect.Top += margin.Value;
        rect.Left += margin.Value;
        rect.Height -= (margin.Value * 2);
        rect.Width -= (margin.Value * 2);
     }

     return rect;
}

With the image path trick used above, I can make the following two calls to place the MyLogo image in cells A1-B2 with a small margin:

FloatRect imageArea = GetRangeBoundaries(reportWorksheet, "A1", "B2", 4);
activetWorksheet.Shapes.AddPicture(MyLogoPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue,
                                   imageArea.Left, imageArea.Top, imageArea.Width, imageArea.Height);

IMPORTANT:

The order in which you construct a worksheet is vital to avoid being driven bonkers by things re-sizing randomly. From experience, use the following order:

  • Worksheet content
  • Size columns in reverse order
  • Place images and charts

Modifying portions of text

One of the requirements I had was that portions of text had to be underlined in certain cells. The first time I did this, I just counted the number of characters for use in the offset of get_Characters(). I did this again the first few times the client changed the text and what was underlined, then I got sick of it and worked out a better way. I used regular expressions to match and perform operations on text. The example below is to underline text, but it would be very simple to generalise this and to have it act on a range, rather than just one cell.

private static void UnderlineText(Excel.Worksheet worksheet, object cell, string toUnderline)
{
    Excel.Range range = worksheet.get_Range(cell, System.Type.Missing);

    if (range.Value2 != null)
    {
        string strValue = range.Value2.ToString();

        Regex regex = new Regex(@toUnderline);
        MatchCollection matches = regex.Matches(strValue);

        foreach (Match match in matches)
        {
            // + 1 because characters are 1 indexed
            range.get_Characters(match.Index + 1, toUnderline.Length).Font.Underline = true;
        }
    }
}

Events

I didn’t use events much, but I did come across an annoying ambiguity in names that took me a while to resolve. Basically, “Calculate” is a method on a worksheet and also a delegate. The delegate is kicked off when a worksheet is forced to re-calculate a formula (for example when one of the input values is changed. Anyway, in the end I had to cast the worksheet to access the delegate when assigning a new one in what seemed a most un-natural way:

((Microsoft.Office.Interop.Excel.DocEvents_Event)dataWorksheet).Calculate += new Microsoft.Office.Interop.Excel.DocEvents_CalculateEventHandler(MyProjectRibbon_Calculate);

with the associated handler:

void KnowledgeBaseRibbon_Calculate() 
{ 
    ... 
}

Creating a setup project

There is an ability to do some sort of one click publishing of AddIns, but almost inevitably in an organisation there’ll be some reason why you have to produce an MSI and setup package that also install all pre-requisites. This section pulls together a whole bunch of articles and solutions that should actually let you do this without p***ing away days.

Background Information

A background of how to create a setup project for an Excel plugin can be found here:

http://www.clear-lines.com/blog/post/create-excel-2007-vsto-add-in-msi-installation.aspx

Getting information on plugin errors

If (and when) the Excel plugin fails to load (ie your new menus etc do not appear), try the following in order to get more detailed information.

Open a command prompt and type:

set VSTO_SUPPRESSDISPLAYALERTS=0 
“c:\Program Files\Microsoft Office\Office12\Excel.exe”

This should popup error dialogs with details for failing plugins.

Setup project pre-requisites

All prerequisites for the setup project may not exist initially. To see the pre-requisites, right click on the setup project and click “properties”, then click the “prerequisites” button. Some of the pre-requisites may have an exclamation mark next to them meaning that these “bootstrapper” packages are not on your machine.

Some are common that can be copied from other peoples/build machine (find & copy them from/to C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages), but VSTOR30SP1 is a custom bootstrapper package that was created to deliver the msi located at: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=1132

To create a bootstrapper package around this file, we had to use the Bootstrapper Maifest Generator (http://archive.msdn.microsoft.com/bmg). The setup file used for this (VSTORuntime.3.0.SP1.VSBootstrapperManifes) has been placed in Source Safe under the CatoAddIn root directory. When you build this file using the BMG, a new folder will appear in your bootstrapper packages directory (C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages). Once the package is there, it should also appear in Visual Studio (restart VS?).

Other issues

These are other issues that I encountered while creating the deployment project that might also help.

To get the plugin working with Excel, the setup project will add the following registry key containing values: HKCU\Software\Microsoft\Office\Excel\Addins\Atkins.MyAddIn

It is important that the name is Atkins.MyAddIn, not just MyAddIn, because in the latter case the “Manifest” registry entry is automatically overwritten when the project is built (lost the URL for this problem).

The “Manifest” must contain the file:/// prefix (http://blogs.msdn.com/b/vsod/archive/2011/06/14/vsto-4-0-sp1-will-cause-a-vsto-addin-to-not-find-its-config-file.aspx), only settings embedded in the project, not from the .config file will be used. This means that the service references will not be picked up correctly.

Appendix1: Example of a customised chart

Examples of customising your charts are abit thin on the ground. This should give you a start.

A list of the variables from elsewhere:

  • dataWorksheet: the worksheet that contains the data that will be rendered in the chart
  • first/lastChartRow: the start and end of the rows I want the chart to cover
  • DataWorksheetItems: the number of  data points in my dataWorksheet
  • Chart style and layout (35 & 2): these can be found by hovering over the style and layout in Excel
If you change something and it crashes, it may well be because some options (eg: where the labels go) are incompatible with some styles and layouts of charts.
FloatRect chartArea = GetRangeBoundaries(reportWorksheet, "A" + firstChartRow, "B" + lastChartRow, imageMargin);

Excel.Range chartRange;

Excel.ChartObjects xlCharts = (Excel.ChartObjects)reportWorksheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(chartArea.Left, chartArea.Top, chartArea.Width, chartArea.Height);
Excel.Chart chartPage = myChart.Chart;
chartRange = dataWorksheet.get_Range("A1", "B" + (DataWorksheetItems + 1).ToString());
chartPage.SetSourceData(chartRange, System.Type.Missing);
chartPage.ChartType = Excel.XlChartType.xlBarClustered;
chartPage.ChartStyle = 35;
chartPage.ApplyLayout(2, chartPage.ChartType);

chartPage.ChartTitle.Text = "My Chart";

chartPage.ChartArea.Font.Name = "Arial";
chartPage.ChartArea.Font.Size = 8;

chartPage.ChartTitle.Font.Name = "Arial";
chartPage.ChartTitle.Font.Size = 9;
myChart.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
chartPage.PlotArea.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
chartPage.Legend.Delete();
chartPage.ApplyDataLabels(Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowValue,
	Type.Missing /*legend key */,
	Type.Missing /*autotext*/,
	Type.Missing /*header lines */,
	Type.Missing /*series name*/,
	Type.Missing /*cat name*/,
	Type.Missing /*show val*/,
	Type.Missing /*show %*/,
	Type.Missing /* show bubble size*/,
	Type.Missing /*separator*/);

Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
for (int seriesIndex = 1; seriesIndex <= oSeriesCollection.Count; seriesIndex++)
{
	Excel.Series oSeries = (Excel.Series)oSeriesCollection.Item(seriesIndex);
	Excel.Points points = (Excel.Points)oSeries.Points(Type.Missing);

	//oSeries.MarkerBackgroundColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red); // for other types of graphs
	oSeries.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(0, 145, 207));

	System.Collections.IEnumerator iptEn = points.GetEnumerator();

	while (iptEn.MoveNext())
	{
		Excel.Point p = (Excel.Point)iptEn.Current;

		if (p.HasDataLabel)
		{
			p.DataLabel.HorizontalAlignment = Excel.Constants.xlRight;
			p.DataLabel.Position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionOutsideEnd;
			p.DataLabel.NumberFormat = "#,##0.00;[Red]#,##0.00";
			//p.DataLabel.Left = p.DataLabel.Left; // Blows up because the chart is not yet rendered.
		}
	}
}
Advertisements
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: