Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
3. February 2010 13:25

Activating a worksheet is a fairly common task in Office automation; however, in a VSTO project, if you call:

myWorksheet.Activate();

… building the project will give you the following warning:

Ambiguity between method 'Microsoft.Office.Interop.Excel._Worksheet.Activate()' and non-method 'Microsoft.Office.Interop.Excel.DocEvents_Event.Activate'. Using method group.

I don’t like to have warnings in my projects when I can avoid it, but I never got to look into it. After all, it was “just a warning”, so I let it go.

The answer came to me via the Carter & Lippert VSTO book (aka “The Brick”), which I finally started reading through, and highly recommend. The gist of it is that the Worksheet interface implements 2 interfaces, _Worksheet and DocEvents_Event. _Worksheet contains the properties and methods that correspond to the Worksheet, including the Activate() method, while DocEvents_Event owns the events, including Activate, and these two names collide.

To disambiguate the call, you just need to cast the Workbook to the appropriate interface, the one which owns the method you are interested in. In my case, I want to Activate the workbook, and therefore use the following code:

((Excel._Worksheet)myWorksheet).Activate();

And sure enough, the warning is gone.

6. November 2009 15:48

When I decided to have a 2-level horizontal menu for my professional webpage in ASP.NET, it came as a surprise to me that this wasn’t completely straightforward. I expected the standard  ASP menu control to support this, but found out that this wasn’t the case.

Fortunately, I came across a post by Peter Kellner, describing how he implemented that for the Silicon Valley Code Camp website, which was pretty much what I envisioned.

The one issue I had with his implementation, however, was that the second level menu uses multiple data sources. The Master Page handles the top-level menu, but each page contains a reference to the specific datasource used to populate the sub-menu. As a result, if you decide to add a page, you need to manually add to that page some code to define what sub-menu should show up, which is cumbersome.

The ideal solution for a lazy developer like me would be to have all the menus handled in the Master Page, so that when you add a new page to your website, you just need to add it to the Sitemap, and the right menu and sub-menu shows up.

More...

20. October 2009 10:46

When working with Excel workbooks with C#, I often need to retrieve the entire contents of a particular worksheet, so that I can process the data within C# code. By “the entire contents”, I mean the content of every cell between cell A1 and the last cell of the sheet, that is, the cell such that there is no cell on its right or below it that contains anything.

To do this, I use the following code, where excelWorksheet is a Worksheet (duh):

Excel.Range firstCell = excelWorksheet.get_Range("A1", Type.Missing);
Excel.Range lastCell = excelWorksheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);

object[,] cellValues;
object[,] cellFormulas;

Excel.Range worksheetCells = excelWorksheet.get_Range(firstCell, lastCell);
cellValues = worksheetCells.Value2 as object[,];
cellFormulas = worksheetCells.Formula as object[,];

The 2 resulting arrays of objects, cellValues and cellFormulas, contain the values and formulas, or null if the cell has no content.

However, while I was working on Akin recently, I realized 2 interesting things I had never noted before. First, the resulting array is 1-based, even though “C# arrays are zero indexed; that is, the array indexes start at zero”. Then, this code will fail if your spreadsheet contains only one value, in cell A1.More...

6. October 2009 06:09

Silicon Valley Code Camp version 4.0 took place this week-end, and was a big success, judging by the numbers and the happy faces. Congratulations to Peter Kellner and the team for a tremendous organization!

Personally, I wanted to give a big thank-you to the people who attended my session on Test-Driven Development – and for bearing with my voice, which was pretty shaky. I got sick this week and wasn’t sure until Saturday evening if I could do it, because on Thursday my voice was totally gone. I think I had more herbal tea with honey this week than in my entire life, but you guys made it all worth it: I had a great time giving my presentation, and you guys rocked!

As I said during the session, the theory behind TDD is pretty succinct, so there isn’t much in the slides themselves worth posting. Instead, I thought I would list a few pointers:

NUnit: you can find it here. I recommend checking out the Quick Start page, which covers most of what you need to start writing unit tests. I have written a post on data-driven tests here.

While we are talking about tools, I haven’t presented it during the session, but I really like TestDriven.Net. There is a free community version for your personal use. It’s a Visual Studio add-on which allows you to run and debug your tests from Visual Studio.

Even though it’s a Java book, and this session was for .NET developers, I really recommend Kent Beck’s book Test-Driven Development by Example. It’s very easy to read, and will get you started on the right foot. It’s also very well written – one of my favorite books!

The other book I recommend is the Art of Unit Testing, by Roy Osherove. I just finished it, and I wish I had it with me a few years ago, when I began writing tests seriously :) The book is technically about unit testing and not TDD, and it is a .NET book. I highly recommend it, it is chock-full of good advice, and covers way more than just testing.

That’s it! If you are interested in either the slides or code, let me know, and I’ll gladly post them, too. In the meanwhile, thanks again for coming, and… happy testing!

20. August 2009 05:55

I wrote a post a few days ago describing how to generate a Line – Column chart in Excel through C#. And then a few things happened. Jon Peltier proposed a much nicer approach, I realized that my code worked for Excel 2003 but not Excel 2007, and someone asked for my code, “Jon-Peltier style”. So here we go: assuming your chart has more than one series, and you want the second series to be formatted as a line, all the rest as columns, you would do something like this:

// Create your chart object first
// formatted as column
Chart chart = ExcelCharts.AddChart(targetWorkbook, “my chart”, “the chart title”, XlChartType.xlColumnClustered, dataRange, XlRowCol.xlRows);
// Select the second series and make it a line
Series series = (Series)chart.SeriesCollection(2);
series.ChartType = XlChartType.xlLine;

Here is a simplified version of my AddChart method, which creates the base chart. Nothing fancy, but gets the job done.

public static Excel.Chart AddChart(Workbook workbook, string chartSheetName, string title, XlChartType chartType, Range dataRange, XlRowCol byRowOrCol)
{
Excel.Chart chart;
chart = (Excel.Chart)workbook.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
chart.ChartType = chartType;
chart.Location(XlChartLocation.xlLocationAsNewSheet, chartSheetName);
chart.SetSourceData(dataRange, byRowOrCol);
chart.HasTitle = true;
chart.ChartTitle.Text = title;
return chart;
}

As an aside, I was not happy with myself when I realized the code didn’t run on Excel 2007. I tend to write Excel-related code against Excel 2003 first, assuming it is the smallest common denominator and will likely work with Excel 2007 – but this is a perfect illustration that while it will typically be correct, it will sometimes fail, sometimes in very unexpected and trivial places, like in this example. Moral of the story: as Lenin allegedly said, “Trust is good, control is better”…