ClosedXML

Remember when I needed a way to write to an Excel file?  COM sure is ugly, but it worked and I was happy with the results.  Turns out I needed to do something similar again.  And, well, COM is still ugly.  And it was getting really ugly this time.

In my original code, it was a thick application running on a workstation.  So working with the already-installed Excel instance was fine.  This time, however, it was going to be in a web application.  It needed to open a known template Excel file (which contains VB macro code), write some data to a known sheet within the file, and deliver the file to the user.  (NOT save it.)

So the problems included:

• Excel has to be on the server.  (Which also presents potential licensing issues.)
• If there's an error, Excel might show an error on the server.  Which is terrible.
• The COM code is designed to work with files, and to save those files.  I couldn't find a way to turn an in-memory unsaved "file" into a stream with COM.
• etc.

The first version worked, but it was still using COM which still had the above problems.  I could open the file, write to it, save it to the host's temp directory, then read it into a stream from the file system and serve it to the user.  Since the file was in the temp directory, the host would eventually clean it out anyway.  So I didn't need to care about leftover files.

But seriously, writing it to a file?  It felt like it got ugly fast.  But this cloud had a silver lining.  The Excel file, even though it was polluted with VB macro code, was an OpenXML file.  So, following the advice of a colleague, I looked into using OpenXML to manipulate the file in-memory and hopefully try to return it as a stream.

Or so I thought.  Have you ever used the OpenXML API?  I use the term "API" very loosely here.  You need to know a lot about the structure of the XML to do anything.  If you don't, it'll just break.  You'll write data to the file, save, etc.  But when you try to open the file, Excel will just yell at you and then remove anything in the XML that it didn't like.  Just look at Microsoft's examples.  All of that code is needed just to write a single value to a single cell.

So after spinning my wheels on OpenXML for a while, and cursing XML in general, what I ended up with was pretty much the same thing.  Better in some ways, worse in others.  The server no longer needed to rely on Excel, which was fantastic.  But the code was huge and ugly and would be damn near impossible to support, especially for the junior developers who are going to be supporting it after I'm done with this project.

Then, out of nowhere, one of my Google searches turned up something wonderful... ClosedXML.  This is an API.  It basically abstracts OpenXML into a nice, simple, more object-oriented interface.  So you don't have to intuitively know the placements of the XML tags in the document format; You don't have to remember the difference between a Worksheet and a WorksheetPart and WorksheetData or whatever; You don't have to know to put strings in a special place in the document and then reference them elsewhere (seriously, what the hell was that?); You can just open the document and write to it.

And you could save it to a stream.  So I didn't have to write it back out to the file system temporarily, introducing another point of failure.  Bonus.

So now my code to open a file, write a value to a cell, and return it as a stream is simply...

public byte[] GetExcelApplication(Guid sessionKey)
{
  var workbook = new XLWorkbook(_excelFile);
  var worksheet = workbook.Worksheets.Where(w => w.Name == "Session Key").Single();
  var cell = worksheet.Cell("A1").Value = sessionKey.ToString();

  using (var ms = new MemoryStream())
  {
    workbook.SaveAs(ms);
    return ms.ToArray();
  }
}

Much better.  Now I'll throw in some input and output checking, test for resource leaks and add "using" blocks accordingly, and we're good to go.