Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
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.

More...

by Mathias 5. March 2009 15:54

My first reaction when I learnt that the keyword var had been introduced in C# 3.0 was horror. It comes in part from my past as a VBA developer, which has a similar-looking and named "variant" type. "Variant" is pretty much the equivalent of "object" in C#, which explains my worry: on the surface, a statement like the one below looks like you gave up on all the goodness of type-safety.

var myInstance = new MyClass();

I quickly learnt that var != variant, and that var IS type safe - and after reading the Handbook from the Department of Declaration Redundancy Department, I started using var to instantiate new objects:

EncoderReplacementFallbackBuffer buffer = new EncoderReplacementFallbackBuffer();
var buffer = new EncoderReplacementFallbackBuffer();

More...

by Mathias 6. January 2009 19:05

I am currently working on an application which requires reading the contents of an Excel worksheet into a 2-dimensional array. I want to avoid loading the entire contents of the worksheet, and want to read only the upper-left quadrant, and leave out all the empty cells on the left and the bottom of the sheet. Problem is, how do you find out the last cell that contains something, that is, the cell such that no cell below it or on its right has content?

Everything Google turned up looked pretty nasty - either brute force, or acrobatic usage of Excel functions, until I stumbled across this little gem:

var lastCell = xlWorksheet.Cells.SpecialCells(
    Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, 
    Type.Missing);

You learn everyday.

by Mathias 13. July 2008 18:42

I was just reading this post on Juan Carlos Méndez-García's blog, where he describes when and how to use harmonic averages. I hadn't seen that average in a long while, and thought his example provided a good illustration as to why this seemingly odd way to compute averages would make sense.
Practically, there is one issue, though: Excel doesn't come up with a built-in Harmonic Average function. I thought I would give a shot at writing a user-defined function that does just that. The function I wrote mimics SUMPRODUCT(), but is called HarmonicAverage, and takes 2 ranges as arguments. The first range is the weight of each observation, the second the value of the observation.

More...

Comments

Comment RSS