TDDing an Excel Spreadsheet

Last week a renewed interest came up on the Fitnesse list about getting a .NET port running. Several people had started various bits, and all decided to try to come together to get it running. As part of that, I threw in my hat and got assigned to get FolderRunner (meaning SpreadsheetRunner) working. Basically this allows one to run FIT against a Spreadsheet. It uses Poi to parse the spreadsheet and figure out what is test and what is content.

The thought was that in .NET this would be much easier, since, after all, Excel *is* a Microsoft product. I decided to keep a running log of my efforts to get this to work, TDD style.

Once I had my environment setup, I created two projects. SpreadsheetReader and SpreadsheetReaderTests. I got to work right away making sure I could find the file:

[TestFixture]
public class SpreadsheetRunnerTests
{  
  public const string TEST_SPREADSHEET = @"C:\SpreadsheeRunner\test.xls";

  [Test]
  public void ThisShouldWork()
  {
    Assert.IsTrue(true);
  }

  [Test]
  public void TestSpreadsheetShouldExist()
  {
    SpreadsheetReader.SpreadsheetReader reader =
      new SpreadsheetReader.SpreadsheetReader();
    reader.LoadSpreadsheet(TEST_SPREADSHEET);
    Assert.AreEqual(TEST_SPREADSHEET, reader.SpreadsheetFileName);
  }
}

This of course wouldn’t compile. So I added the SpreadsheetRunner classes:

public class SpreadsheetReader
{
  private string spreadsheetFileName = String.Empty;

  public SpreadsheetReader(){}

  public void LoadSpreadsheet(string filename)
  {
    if(File.Exists(filename))
    {
      FileInfo fileInfo = new FileInfo(filename);
      spreadsheetFileName = fileInfo.FullName;
    }
    else
    {
      throw new FileNotFoundException();
    }
  }

  public string SpreadsheetFileName
  {
    get{return spreadsheetFileName;}
  }
}

Ok, green bars all the way around. Time to get to some real work. The Excel file I’m using came straight from the FitLibrary’s test source. I figured the next easiest step would be to just read in the text from the first column of the first row. From the test spreadsheet, that text should read “Calculate the discount with Fit:”.

Simple enough, really. But how does one open Excel from C#?

I had done some preliminary research, and remembered seeing something like Application.Excel(). But my intellisense didn’t seem to “sense” that, so to Google I went. The first article I stumbled across was “How to Open and Read an Excel Spreadsheet into a ListView in .NET.” Aha. I have to add the Excel Object Library from the Com references. However, the article said it was 9.0, and mine had 11.0:

I added it anyway. I then set up my test:

public const string TEST_SPREADSHEET_ROW1_COL1_TEXT = "Calculate the discount with Fit:";

[Test]
public void RowOneColumnOneShouldContainText()
{
  string row1Column1Text = reader.GetTextFor(1,1);
  Assert.AreEqual(TEST_SPREADSHEET_ROW1_COL1_TEXT, row1Column1Text);
}

Which didn’t compile, so I added some code to my SpreadsheetRunner:

public string GetTextFor(int row, int column)
{
  return "Calculate the discount with Fit:";
}

Ok, green bar. But now I have to make this pass for real. Looking at the article, the first thing they do is private Excel.Application ExcelObj = new Excel.Application();. I try that, but Intellisense tells me Excel.Application is an interface. There is an Excel.ApplicationClass which looks promising, and I spike down that road for a bit with very little luck. I do some googling and find that ApplicationClass was the right thing to use, and that it has a Workbooks.Open() method that looks like (wait for it…):

WorkBooks.open(string Filename,
object UpdateLinks,
object ReadOnly,
object Format,
object Password,
object WriteResPassword,
object ReadOnlyRecommend,
object Origin,
object Delimiter,
object Editable,
object Notify,
object Converter,
object AddToMru,
object Local,
object CorruptLoad )

I look back at my article, and it seems like I’ve caught back up to it. It says I can get the sheets by using something like Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);. So I give that a shot. But then I see my first mistake. Excel doesn’t think of the columns as 1-n, it thinks of them as A-Z. Actually, it thinks of row/columns as A1 for column A of row 1. I change the GetTextFor method signature to account for this. So let’s see if we can get some text:

public string GetTextFor(int row, string column)
{
  Excel.Application excelApp = new Excel.ApplicationClass();
  try
  {
    excelApp.Visible = false;
    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(spreadsheetFileName,
      0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
      true, false, 0, true, false, false);
    Excel.Sheets sheets = excelWorkbook.Worksheets;
    Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
    Excel.Range range = worksheet.get_Range(column + row.ToString(),
      column + row.ToString());
    return (string)range.Text;
  }
  finally
  {
    excelApp.Quit();
  }

  return string.Empty;
}

You’ll notice the try/catch block. I found that if I didn’t make sure I called Quit() the app would hang around in memory, and since I set it’s visibilty to false, I couldn’t close it without going to the Task Manager. So, I hit run and:

Never felt so good to get a green bar!

Now that the hard part is out of the way (actually loading the app and reading from it), the next step is to begin going through the FIT tests and getting them to work here. But not tonight, I like to end on a good note, and this is a fine spot for that!