Reading and Writing Excel files with .Net

It would be an unusual business application that did not have a requirement to export data in one form or another. Probably the most useful form of report to produce is one that can be opened by Excel, because then everybody and his dog can slice and dice and chart and pivot the data, and you don't have to implement any of those features in your application because Excel does it all.

So what options are there for reading and writing Excel files with .Net?

Plain Text

The easiest option is to use text files. Excel is quite happy to open a straight "txt" file, though it will make you tell it how to chop the lines into columns, and that's not terribly user-friendly. More useful are the CSV (Coma Separated Value) or TSV (Tab Separated Value) formats because these have structure built in. Excel will export its own data in any of these formats.

If you opt for this method, check out FileHelpers, an open-source project (released under the LGPL license) for reading and writing structured files. It lets you work a lot like the XML Serialization feature in the .Net framework. You can mark up classes with attributes, and FileHelpers will write data from your classes to structured files (CSV and TSV included), and will also do the reverse, turning structured files into objects. Nice!

The obvious limitation of plain text files is that you can't format the data or include things like charts. For that you need to work with one of the more sophisticated file types.

Native Excel Files

Native Excel files (the ones with the ".xls" extension) are stored in BIFF format - a binary format that is impenetrable to anybody without the specification. The good news is that, in February 2008, Microsoft released the spec for the Excel format, and other binary file formats used by the Office family. The bad news is that the spec for the Excel format alone weighs in at 349 pages, so good luck implementing that! If you're wondering why it is so complicated, Joel Spolsky (who used to work on the Excel team at Microsoft) has done some explaining.

Fortunately, there are quite a number of companies who saw the complexity of the task as an opportunity, and have created components in .Net to read and write native formats Excel files. Here are a few that I've come across (by no means an exhaustive list):

• XLSReadWrite
• ActiveXLS
• Syncfusion XlsIO
• Spreadsheet.Net
• GemBox.Spreadsheet
• SpreadsheetGear (this one also has Windows Forms controls for displaying and editing spreadsheets)

[Update: I've now written up my first impressions of several of these components]

Using one of these libraries is generally the fastest way of dealing with native excel files (fastest both in terms of performance of the code, and how long you take to write that code!). But there is one other option to consider, if for some reason you can't purchase a library, or you want absolute compatibility with Excel: automating Excel itself using COM Interop. This has the advantage of letting you export files in any of the formats that Excel supports. Suresh Behera has put together a list of Microsoft HowTos to get you started with this technique.

You should be aware that this comes with some limitations. The most obvious is that Excel has to be installed on a computer along with your software, and you have to be careful that the version is compatible with the one you developed with. You also can't use this method to access Excel files on a Server since Excel is a component with a UI (perhaps I should say shouldn't, rather than can't, because some people have made this work). Finally, it's pretty slow if you're dealing with large workbooks because of the overhead the COM imposes on method calls.

Open XML Format Excel files

When Microsoft released Office 2007, they changed the default file format for Word, Excel and Powerpoint to new, XML based formats. They've published the specifications for these under the Office Open XML label - the format for Excel is called SpreadsheetML (you might have heard of the controversy as Microsoft went about to get these formats approved as standards, first by the ECMA, and then by ISO). It's not just Office 2007 that supports these formats; Microsoft have provided an add-in to add support to all versions of Office right back to Office 2000.

An XML-based format. That sounds like it should be easy to write. Right? Well it is much easier than working with the binary formats, but the format has still got to support Excel's huge feature list, so it's never going to be simple. You probably want to start by poking around inside some files in the new format. The main thing you need to know is that they are basically zip files stuffed full of xml files. You can change the extension of a ".docx" file (the new Word file format) or ".xlsx" (the new Excel file format) to ".zip", then open it with your favourite Zip extractor. Some other places you might want to check out:

• OpenXmlDeveloper.org: Microsoft's portal site for everything to do with Open XML.
• Doug Mahugh's blog: he regularly links to Open XML resources.
• Eric White's blog: excellent examples of reading the new formats with C# and LINQ to XML. (He also has a very nice Functional Programming Tutorial)
• The Open XML Package Explorer is a graphical tool for exploring the organisation of any Open XML format file
• VSTO Power Tools contains an Add-in for Visual Studio similar to the Package Explorer. There's more about that on Mauricio Ordonez's blog.

If you're not up for the challenge of reading and writing the format yourself, many of the libraries I listed above have support for it, to varying degrees. And this time, Microsoft themselves are working on components to read and write the formats. Have a look at the Open XML Format SDK. The first version has already been released with some basic support for the formats; the SDK development roadmap indicates that increased functionality will be available later this year.

POLICE Follow this blog

The other day I was stuck behind a security van on the motorway (you know, the ones that carry money and other valuables from place to place) and, as you do, I started reading the stickers that were plastered over its rear end. This one caught my eye:

"POLICE Follow this Van". I'd seen it on several similar vans before and, once again, I got to wondering what it could mean.

Perhaps it is a warning to all persons with nefarious intent that police are following the van? But a quick glance over my shoulder didn't show me any officers of the law - though maybe they were driving a plain-clothes police car.

Then I hit on it. It is an instruction to passing Police vehicles that they should follow the van, I guess on the supposition that it has been stolen. But surely it's rather indiscriminate. Imagine that the police obeyed the order without question. Then every security van would gradually accumulate a small convoy of panda cars, and over time bank robbers and other criminals would be able to go about their business undisturbed, safe in the knowledge that the only people who could stop them were otherwise engaged in uselessly following legitimate cash couriers. It would be the reverse of what happened when this policeman accidentally turned on the "Follow Me" sign atop his car, and acquired a following of bemused motorists on his routine patrol.

So how are police to know when they should really follow the van? Here's my solution. It's obvious. Replace the useless sign on the back of the van with a Matrix display fitted to the top, like those on police cars, with the words "Police follow me". Then, inside, fit a button to activate the sign, with the label "Thieves, press here".

Footnotes

• I'm not the only one wondering about this. The question has been asked on yahoo answers and on UK Business forums - and there's no consensus on what the sign means. One forum poster suggested an alternative sign for lonely van drivers: "Please follow this van".
• Found it! A press release from Devon and Cornwall Constabulary stating that the sign means police should follow the van if they've got nothing better to do. But surely the Constabulary have more efficient means of communicating orders to their constables than sticking signs on the back of vans?

WPF PasswordBox and Data binding

Like Charles Petzold, I am something of a Xamlholic: I'll try for hours to find a way of expressing my UI in pure XAML, rather than pollute its purity with C# code, even if the code could go in a code-behind file. This addiction is largely driven by WPF's fantastic support for Data Binding which lets anything in the UI be data-bound to anything else. Well, almost anything in the UI. Just occasionally I find myself having to fall back on property setters and getters, poking data into controls then sucking it back again, the way Windows Forms made me earn my living. One control which nearly had me beat was PasswordBox.

PasswordBox is what you need whenever you want to mask the characters as a user types them: for some reason, WPF's standard TextBox doesn't support that scenario. Now PasswordBox has, as you'd expect, a Password property that allows you to get and set the password. However, it's a plain CLR property rather than a Dependency Property, so it doesn't support being the target of a Data binding.

Ben Westbrook of Microsoft explains in a forum post that it was not exposed as a Dependency Property for security reasons. The values of Dependency Properties are managed centrally by the Dependency Property sub-system so that it can handle all the data-binding and cool animation stuff (animating a password - now that would be interesting!); the consequence of this is that the data effectively becomes public property. With something as sensitive as a password it's reasonable that a control (like PasswordBox) would want to keep the data closer to its chest. So internally the PasswordBox stores the password in a good old field, and in fact holds it using a SecureString - a string that is automatically encrypted in memory and obliterated when no longer needed; if you Reflector PasswordBox you'll see that the Password property setter stuffs any value you set into a SecureString, and the getter reads it out for you; PasswordBox appends characters one by one to the SecureString as you type them, so the password is never stored as clear text until you retrieve it through the Password property.

As an aside, one thing I don't understand about the design is why PasswordBox doesn't provide a property to access the password as a SecureString, and so preserve the encryption? The act of retrieving the password through the Password property (as you'll have to do at some point in order to use the password) will turn it into clear text, and then you'll surely loose most of the benefit of ever having it encrypted? Perhaps someone from Microsoft can explain?

Update: As Ray pointed out in the comments, Microsoft have now (as from .Net 3.5 SP1) added a SecurePassword property to PasswordBox; it's still not a DependencyProperty though.

Anyhow, let's get to the point. Sometimes your main interest in using a PasswordBox is to have the password text masked as it's typed, and encryption isn't really an issue. In this case its a shame not to be able to use data binding.

Fortunately, WPF has the concept of Attached Properties that allow controls to be extended very easily. I've knocked up a class that extends PasswordBox with an Attached Property that lets you data bind the password. You use it as shown below (note particularly the additional xmlns element you need to add to the root element in your XAML):

<Page xmlns:ff="clr-namespace:FunctionalFun.UI">
  <!-- [Snip] -->
  <PasswordBox x:Name="PasswordBox"
      ff:PasswordBoxAssistant.BindPassword="true"  ff:PasswordBoxAssistant.BoundPassword="{Binding Path=Password, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}">

</Page>

And here's the code. Very simple; setting the BindPassword property causes the code to start listening to the PasswordChanged event on the PasswordBox: whenever there is a change the code pushes the new value into the Attached Property BoundPassword which you can specify a binding for. It also takes care of the other direction, going from BoundPassword (whenever your the bound source property changes) into the PasswordBox. The only complication is making sure the whole thing doesn't get recursive and blow up: that's what the private attached property UpdatingPassword is for.

using System.Windows;
using System.Windows.Controls;

namespace FunctionalFun.UI
{
  public static class PasswordBoxAssistant
  {
      public static readonly DependencyProperty BoundPassword =
          DependencyProperty.RegisterAttached("BoundPassword", typeof(string), typeof(PasswordBoxAssistant), new PropertyMetadata(string.Empty, OnBoundPasswordChanged));

      public static readonly DependencyProperty BindPassword = DependencyProperty.RegisterAttached(
          "BindPassword", typeof (bool), typeof (PasswordBoxAssistant), new PropertyMetadata(false, OnBindPasswordChanged));

      private static readonly DependencyProperty UpdatingPassword =
          DependencyProperty.RegisterAttached("UpdatingPassword", typeof(bool), typeof(PasswordBoxAssistant), new PropertyMetadata(false));

      private static void OnBoundPasswordChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
      {
          PasswordBox box = d as PasswordBox;

          // only handle this event when the property is attached to a PasswordBox
          // and when the BindPassword attached property has been set to true
          if (d == null || !GetBindPassword(d))
          {
              return;
          }

          // avoid recursive updating by ignoring the box's changed event
          box.PasswordChanged -= HandlePasswordChanged;

          string newPassword = (string)e.NewValue;

          if (!GetUpdatingPassword(box))
          {
              box.Password = newPassword;
          }

          box.PasswordChanged += HandlePasswordChanged;
      }

      private static void OnBindPasswordChanged(DependencyObject dp, DependencyPropertyChangedEventArgs e)
      {
          // when the BindPassword attached property is set on a PasswordBox,
          // start listening to its PasswordChanged event

          PasswordBox box = dp as PasswordBox;

          if (box == null)
          {
              return;
          }

          bool wasBound = (bool)(e.OldValue);
          bool needToBind = (bool)(e.NewValue);

          if (wasBound)
          {
              box.PasswordChanged -= HandlePasswordChanged;
          }

          if (needToBind)
          {
              box.PasswordChanged += HandlePasswordChanged;
          }
      }

      private static void HandlePasswordChanged(object sender, RoutedEventArgs e)
      {
          PasswordBox box = sender as PasswordBox;

          // set a flag to indicate that we're updating the password
          SetUpdatingPassword(box, true);
          // push the new password into the BoundPassword property
          SetBoundPassword(box, box.Password);
          SetUpdatingPassword(box, false);
      }

      public static void SetBindPassword(DependencyObject dp, bool value)
      {
          dp.SetValue(BindPassword, value);
      }

      public static bool GetBindPassword(DependencyObject dp)
      {
          return (bool)dp.GetValue(BindPassword);
      }

      public static string GetBoundPassword(DependencyObject dp)
      {
          return (string)dp.GetValue(BoundPassword);
      }

      public static void SetBoundPassword(DependencyObject dp, string value)
      {
          dp.SetValue(BoundPassword, value);
      }

      private static bool GetUpdatingPassword(DependencyObject dp)
      {
          return (bool)dp.GetValue(UpdatingPassword);
      }

      private static void SetUpdatingPassword(DependencyObject dp, bool value)
      {
          dp.SetValue(UpdatingPassword, value);
      }
  }
}

Update: If you're trying to weigh up the security risks of using this method, take a look at Daniel D'Agostino's article where he shows how passwords in memory can be snooped.

Project Euler, Problem 13: Summing big integers

Today we'll look at a Project Euler problem that takes us right back to primary school. Problem 13 asks us to sum together 100 big integers - each with 50 digits. 50 digits puts the numbers right outside the range of the Int32 datatype, and even long can't handle numbers that big; double obviously could, but not with the precision that we need. It looks like we'll need to dig deeper to solve this one - which I'm sure was Euler's intention in setting the problem!

We could use one of the BigInteger implementations that are available (in the J# library with .Net, or on CodeProject - but not the one that failed to make it into .Net 3.5!). However, since my goal with this series is to learn functional programming techniques with C#, and oil my rusty mathematics, I'm going to show how to do the addition from scratch. Don't expect amazing performance or a reference implementation for long integer addition in what I show here: this is a functional programming tutorial, not an unsafe C# tutorial.

So how do we sum up numbers? Please excuse me for a moment whilst I imagine myself back to school, to the tiny desks and the classrooms smelling of disinfectant, in front of the squeaky blackboard where Mrs Jones is teaching us how to do long hand addition.

It all comes flooding back:

1. Write the digits of the numbers in columns. Draw a line underneath (neatly, with a ruler!). Write a plus sign to the left.
2. Start at the right hand column (the "tens column"), and add up all its digits
3. Write the last digit of the column's sum at the bottom of the column; the other digits should be written in the column to the left (the "hundreds column")- this is the carry over.
4. Move to the next column. Sum up its digits, and also add the carry over from the previous column. Split up the sum as before: the last digit is written to the column, the first digits in the column to the left.
5. Carry on till all the columns have been added up, or Darren spills paint over my workbook.
6. Read off the answer from underneath the line.

Let's code up a solution that works in the same way.

For convenience, we'll just paste in the block of numbers from the Project Euler website as a big string; as in a previous solution, a LINQ query effortlessly chops and changes the string into a form we can work with. Since the 50 digit numbers are too long to be stored as numbers, we'll represent them as sequences of digits - and to make things easier later on, we'll store them with the least-significant digit (the digit representing the "units") first. I'm storing each digit as an int32 which is horribly inefficient, but helps keeps the code simple. The LINQ query gives us a sequence of these sequences of digits, as you can see:

const string Input = @"37107287533902102798797998220837590246510135740250
                     46376937677490009712648124896970078050417018260538
                        ...
   ";

// split the input string block into lines
var numbers = Input.Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries)
    // convert each line of the strings to a sequence of digits:
    .Select(line =>
        // process the line as individual characters
        line.ToCharArray()
        // ignore any whitespace characters
        .Where(c => char.IsDigit(c))
        .Select(c => int.Parse(c.ToString()))
        // reverse the sequence putting least significant digits first; this makes
        // subsequent processing easier
        .Reverse()
        );

That effectively completes step 1 above, if you imagine all the sequences of digits laid out with columns aligning, and are prepared to forgo the neat underline that Mrs Jones insisted on in my workbook.

Now things begin to get interesting, because for step 2 we need to process one column of digits at a time - which means taking one element from each of the sequences of digits before moving on to the next position in the sequence. This is different from anything we've done before, where we have always processed sequences sequentially - one entire sequence after another. To handle sequences in this new way we need to introduce another staple of functional programming, the Zip function.

Zip in this context has nothing to do with compression. Instead you need to be thinking pants - or trousers, as we in the UK call them. Specifically, think of the zipper, the status of which some gentlemen get so paranoid about. As you pull the zipper up, it pulls together the two separate halves of the fly and joins them. The Zip function works just like that: it draws together two (or more) sequences, creating a new sequence where each element is made up of the elements from one point of all the independent sequences.

This is my implementation of Zip in C# 3.0:

public static IEnumerable<IEnumerable<T>> Zip<T>(this IEnumerable<IEnumerable<T>> sequences)
{
    // get the enumerators for each sequence
    var enumerators = sequences.Select(sequence => sequence.GetEnumerator()).ToList();

    // make a list of the enumerators that still have elements to yield
    var activeEnumerators = new List<IEnumerator<T>>();
    var items = new List<T>();

    while (enumerators.Count > 0)
    {
        items.Clear();
        activeEnumerators.Clear();

        // take an element from each iterator
        // if the iterator doesn't have any more elements/
        // don't add it to the activeEnumerators list
        foreach (var enumerator in enumerators)
        {
            if (enumerator.MoveNext())
            {
                items.Add(enumerator.Current);
                activeEnumerators.Add(enumerator);
            }
        }

        yield return items;

        enumerators.Clear();
        enumerators.AddRange(activeEnumerators);
    }
}

This implementation differs from others you might see around because it keeps going while any of the independent sequences have elements - it's greedy; you can easily change it to match the usual, frugal, implementations which terminate the output sequence as soon as any of the input sequences dry up by putting a yield break in an else clause after if (enumerator.MoveNext()).

If we apply this Zip method to our list of digit sequences it will produce for us a sequence where each element is effectively a column of digits in our sum, which is just what we need.

The next step is to add up each of the columns, doing the carry-over from one to the next. This is just the kind of job that Aggregate likes to do. Each iteration of the Aggregate method will add up the digits of one column of numbers, push the appropriate digit onto a stack that we'll build up representing the digits of the result, and record the carry-over ready for the next iteration. What do we do with the carry-over when we get to the last column? Well, we need to stick its digits in front of the other digits we've calculated, just as when we're doing long-hand division. The Aggregate method has an overload that allows us to apply an operation to the final accumulation variable; in our case the final accumulation variable will contain all the digits of the sum, and a separate record of the final carry-over as a number, so we'll supply a method that will break the carry-over into its component digits, and append it to the sequence of sum digits:

// calculate the sum of the numbers, returning the result as a sequence of integers
// representing the digits of the sum.
var sumDigits = numbers
                // process the entire list of numbers column by column
                // ie, process least significant digits of all numbers first, then the next column of digits, etc.
                .Zip()
                // work through each column, calculating which digit represents the sum for that column,
                // and what the carryover is
                .Aggregate(
                    // initialise an annoymous data structure to hold our workings out as we
                    // move column-by-column.
                    // Digits holds the digits of the sum that we've calculated so far
                    // CarryOver holds whatever has been carried over from the previous column
                    new { Digits = Stack<int>.Empty, CarryOver = 0},
                    (previousResult, columnDigits)=>
                        {
                            var columnSum = columnDigits.Sum() + previousResult.CarryOver;
                            var nextDigit = columnSum % 10;
                            var carryOver = columnSum / 10;
                            return new { Digits = previousResult.Digits.Push(nextDigit), CarryOver = carryOver };
                        },
                    // to complete the aggregation, we need to add the digits of the final carry-over
                    // to the digits of the sum;
                    // note that because completeSum.Digits is a stack, when it is enumerated
                    // we get the items back in reverse order - ie most significant digit first; hence the call to Reverse()
                    // to make the order of the digits in the sum we return consistent with the input digits
                    (completeSum) => completeSum.CarryOver == 0 ? completeSum.Digits.Reverse() : completeSum.CarryOver.Digits().Concat(completeSum.Digits).Reverse()
                    );

Note that I'm making use of Eric Lippert's Immutable Stack implementation to store the digits of the sum as we accumulate them.

The problem actually asked us to find the first ten digits of this sum. A simple query will extract those for us:

// as an answer, we're asked to give the 10 most significant digits of the sum
return sumDigits.Reverse()
    .Take(10)
    .Aggregate(new StringBuilder(), (sb, digit) => sb.Append(digit), sb => sb.ToString())
    .ToLong();

The full code is available on my Project Euler page on MSDN Code Gallery

Footnotes:

• Here's another implementation of the Zip method - with diagrams.
• It looks like .Net will be getting a Zip method when the Parallel Extensions are released.

[Update 23/6/2008] Corrected the implementation of the "(completeSum) => ..." lambda

Recipe: Wrapuloos

Whilst on my travels in Belgium I made a remarkable gastronomic discovery. Please leave aside the fact that I only needed to travel to the local hypermarket, not half way round the world, while I present to you, in the spirit of Cortes bringing Chocolate to Europe, Pâte de Speculoos.

Any visitor to Northern France or Belgium has probably sampled Speculoos - thin biscuits with a delicious cinnamon and caramelised sugar flavour, often served with Coffee - under the awning of the many Cafés they surely visited. But you needn't go travelling to taste these flavoursome confections: they're sold in the UK and Europe under the Lotus brand, or in the US by Biscoff.

Now imagine those very same biscuits, crushed and blended into a rich paste and you have Pâte de Speculoos - also made by Lotus; the flavour is no longer locked in a biscuit chained to a coffee cup - only let your watering taste buds guide your imagination as you concoct and confect.

Guided by my taste buds, I tried spreading the paste on a Tortilla wrap. When eaten straight after crisping the wrap under a hot grill, the result was delicious. The wrap gives a crisp, firm bite from which the warm cinnamon flavour oozes.

Such a creation surely deserves a name; I've settled on Wrapuloos.

How to make them

1. Lay out a Tortilla wrap, and spread Pâte de Speculoos liberally over the centre. Don't spread right up to the edges - leave a border of about 2 inches.
2. Fold over two opposite sides of the wrap to make a long rectangle with rounded ends. Now roll up the wrap like a Swiss Roll, starting with one of the rounded ends. Once rolled, stick the other end down with a little more paste.
3. Spread a little light brown sugar on the flat side of the roll.
4. Place under a hot grill under the sugar begins to caramelise.
5. Eat immediately.

So next time you visit the Belgium or France, buy yourself a jar of Pâte de Speculoos. And bring me one back too: they don't sell it at my supermarket yet.

[Update]

From the web, a recipe for Muffins made with Speculoos paste, in French though; here's an almost intelligible English translation by google.

[Update: 12/11/2008]

I've now found an online retailer that will ship all over the world. Order your Pâte de Speculoos from Waffle Cafe today! As of now, you'll pay around £3 per jar, with shipping in the UK about £4 per order.

Extending Stopwatch: Making it easier to time your code

If ever you look on the Project Euler forums (which, cleverly, you can't access until you've submitted your own answer to a problem), you'll notice a fair bit of bragging about how fast each contributor's solution is. Now I'm not aiming to arrive at the quickest solution (that's my excuse anyway!), but it would be interesting to know if any of my code comes anywhere close.

Since .Net 2.0, we've had the Stopwatch class, which makes it very easy to time code. No more totalTime = DateTime.Now - timeThen! Under the covers, Stopwatch can use native high-resolution performance counters, so it gives pretty accurate results.

You'd think that you couldn't get easier-to-use than the Stopwatch API: Stop(), Start(), Reset(), Elapsed give you pretty much everything you need. But now, thanks to Extension methods and lambda expressions, we can go one better. Here are some extensions I knocked up for Stopwatch which make it even easier to time code. The first one, Time is an example of what you might call a Polo method - a method with a hole in! The "hole" is the bit where your code goes - in this case, the code you want to time. The place of the mint is taken by the generic code around the hole that does the timing. Of course, the "hole" is implemented using delegates, in this case an Action delegate, that takes no arguments and returns no result.

public static class StopwatchExtensions
{
    /// <summary>
    /// Extends the Stopwatch class with a method to time an Action delegate over a specified number of iterations
    /// </summary>
    public static Stopwatch Time(this Stopwatch stopwatch, Action action, long numberOfIterations)
    {
        stopwatch.Reset();
        stopwatch.Start();

        for (int i = 0; i < numberOfIterations; i++)
        {
            action();
        }

        stopwatch.Stop();
        return stopwatch;
    }

    /// <summary>
    /// Extends the Stopwatch class with a method to time an Action delegate
    /// </summary>
    /// <param name=\"stopwatch\"></param>
    /// <param name=\"action\"></param>
    public static Stopwatch Time(this Stopwatch stopwatch, Action action)
    {
        return stopwatch.Time(action, 1);
    }

    public static double TimeInFractionalSeconds(this Stopwatch stopwatch)
    {
        // figure out how much of a second a Stopwatch tick represents
        double secondsPerTick = (double)1 / Stopwatch.Frequency;

        return stopwatch.ElapsedTicks*secondsPerTick;
    }
}

This makes timing a method as easy as:

double time = new Stopwatch().Time(MyMethod).TimeInFractionalSeconds();

or

double time = new Stopwatch().Time(()=> Math.Sqrt(int.MaxValue)).TimeInFractionalSeconds();

As you know, in order to get truly representative timings, it's usually necessary to repeat the measurement several times and then take an average - things like JITting the code, your computer getting distracted by a YouTube video, or the phase of the moon could all contribute to individual timings being out.

For my Project Euler project, I've put together a little helper method that takes care of the averaging over several iterations. The ActionTimer first runs a delegate once to get an idea for how quick it is. You can specify how long you think the code needs to run for in total to get an accurate time. If a single iteration takes longer than this it just reports that as the time - this is useful for long-running code where you assume that the actual run-time exceeds any overheads; otherwise it calculates how many iterations are needed to make the code run for the target time you specified. Once it has done those, it returns the average per iteration.

/// <summary>
/// Times how long a given delegate takes to run
/// </summary>
public static class ActionTimer
{
    /// <summary>
    /// Times the given delegate
    /// </summary>
    /// <param name=\"action\">The delegate to time</param>
    /// <param name=\"targetRunTime\">The target amount of time to average over (in seconds)</param>
    /// <returns>The running time, in fractional seconds</returns>
    public static double AverageTime(this Action action, double targetRunTime)
    {
        if (targetRunTime <= 0)
        {
            throw new ArgumentException(\"targetRunTime must be greater than 0.\", \"targetRunTime\");
        }

        Stopwatch timer = new Stopwatch();
      

        // time the action once to see how fast it is
        timer.Time(action);

        // if the action took more than half of the targetRunTime time, we\'re not going to
        // fit in a second iteration
        double firstIterationTime = timer.TimeInFractionalSeconds();
        if (firstIterationTime > targetRunTime/2)
        {
            return firstIterationTime;
        }

        // otherwise, repeat the action to get an accurate timing
        // aim for targetRunTime seconds of total running time
        long numberOfIterations = (long) (targetRunTime/firstIterationTime);

        // the number of iterations should be at least 1 because firstIterationTime is less than half of
        // targetRunTime
        Debug.Assert(numberOfIterations > 0);

        timer.Time(action, numberOfIterations);
      
        // calculate the length of time per iteration
        return timer.TimeInFractionalSeconds() / numberOfIterations ;
    }

    /// <summary>
    /// Times how long the given delegate takes to complete, averaging over up to 5 seconds of running time
    /// </summary>
    /// <param name=\"action\"></param>
    /// <returns></returns>
    public static double AverageTime(this Action action)
    {
        return AverageTime(action, 5);
    }
}

This is how you might use it, using lambda syntax:

double time = ActionTimer.Time(
    () => Enumerable.Range(1, 999)
            .Where(x => x % 5 == 0  x % 3 == 0)
            .Sum()
)

Discrimination at the heart of the EU!

I've just got back from a two-week visit to Belgium. Did you wonder why things had gone at bit quiet? I felt very relaxed whilst I was there; now I've come back to my desk to find 526 posts waiting to be read in my feed aggregator -  a lot of them about  the TechEd conference that Microsoft have gone and held in my absence and the announcements of  a whole load of new stuff that I'll feel compelled to read up on.

Enough of my post-holiday blues; on to more important matters. Whilst away I came across a shocking example of unfair discrimination. Have a look at this sign that I saw in a shop, posted on a refrigerator stocked with alcoholic beverages:

Can you believe it? I couldn't. Only 100 km from Brussels, the HQ of the EU, that champion of Human Rights, and I find such blatant discrimination against inoffensive, hard working members of the population. What's more, this was in the heart of the Flemish Polder - flat lands, reclaimed from the sea by dykes - a more unlikely place to be overrun by marauding miners, I can't imagine.

What a difference a letter makes, eh? ;-)

P.S. I have nothing but gratitude for the kind people that ignored my ignorance of their language, and spoke to me in mine. And sheer admiration for our tour guide in Bruges, who not only provided commentary in four different languages, one after the other (including off-the-cuff remarks about the cute baby swans), but also piloted the boat through Bruges' miniscule bridges at the same time!