Excel Interop - Not New, But New To Me

If you're like me, you've managed to avoid using COM Interop libraries for a long time. When business users want rich text from a web app, you give them HTML. When they want to open something in Excel, you give them a CSV. They just copy/paste stuff, they'll never know the difference. Right?

Well, eventually this catches up with you. Eventually you're going to have to click on that COM tab in the Add Reference dialog and see all it has to offer. Some of it may even look interesting or exciting. Sure, it's always exciting at first. But then you find yourself waking up in a dumpster in Mexico with no useful intellisense on a function that takes 12 parameters, all of type object. You don't know what happened last night, all you have are some Google searches and vague memories of DLLs that had to be underage.

But when the business users specifically want multiple reports on multiple sheets in the same Excel file that they load into some other software, CSV just isn't going to cut it anymore. You're going to have to add a reference to Microsoft Excel 12.0 Object Library and you're going to have to get dirty.

Luckily, if you can convince the business to keep it simple, it won't be so bad. Here's some code I slapped together today to perform the simple task of writing to some worksheets (the very word makes me shudder) in an Excel file:

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelOutputting
{
  public class ExcelOutputter
  {
    public void WriteAFile(List report)
    {
      // Instantiate the workbook
      var xlApp = new Excel.ApplicationClass();
      // This will be important later
      xlApp.DisplayAlerts = false;
      // From reflection, you'll use it a lot when you have to pass
      // something but don't have anything to pass.
      // I guess COM is like that.
      var mis = Missing.Value;
      var xlWorkbook = xlApp.Workbooks.Add(mis);

      // Create an extra worksheet after the last one.  Remember, there
      // are always 3 already in there by default.  And it's 1-indexed.
      xlWorkbook.Worksheets.Add(
        mis,
        (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(3),
        mis,
        mis
      );

      // Name the worksheets
      ((Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1)).Name = "Jan 2010 Report";
      ((Excel.Worksheet)xlWorkbook.Worksheets.get_Item(2)).Name = "Feb 2010 Report";
      ((Excel.Worksheet)xlWorkbook.Worksheets.get_Item(3)).Name = "Mar 2010 Report";
      ((Excel.Worksheet)xlWorkbook.Worksheets.get_Item(4)).Name = "Apr 2010 Report";

      // Edit worksheet for January
      var xlWorksheet =
      (Excel.Worksheet)xlWorkbook.Worksheets.get_Item(1);
      for (var i = 0; i < report.Records.Count, i++)
      {
        // Remember, 1-indexed
        xlWorksheet.Cells[i+1, 1] = report.Records[i].SomeValue;
        xlWorksheet.Cells[i+1, 2] = report.Records[i].SomeOtherValue;
        //... and so on...
      }
      //... and so on...

      // Save the file
      xlWorkbook.SaveAs(
        report.DestinationFile,
        Excel.XlFileFormat.xlWorkbookNormal,
        mis,
        mis,
        false,
        false,
        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
        Excel.XlSaveConflictResolution.xlLocalSessionChanges,
        false,
        mis,
        mis,
        mis
      );
      // I told you you'd use that value a lot.  Now, there are many
      // ways you can go about saving.  This one, in conjunction with
      // that xlApp.DisplayAlerts = false setting from earlier will
      // save to the file name (fully qualified, it doesn't always
      // default to the current directory) in the first argument,
      // overwriting without prompt.
      // This is especially important in a web environment where the
      // user won't see the prompt on the server.  Yes, the application
      // will show a File Already Exists dialog asking you to overwrite
      // or cancel on the console of the machine running this.  You'll
      // want to handle your file saving conflicts yourself.

      xlWorkbook.Close(true, mis, mis);
      xlApp.Quit();

      releaseObject(xlWorksheet);
      releaseObject(xlWorkbook);
      releaseObject(xlApp);
    }

    // Found this online, might be a good idea to make sure
    // when using COM
    private static void releaseObject(object obj)
    {
      try
      {
        Marshal.ReleaseComObject(obj);
        obj = null;
      }
      catch (Exception ex)
      {
        obj = null;
        Console.WriteLine("Exception Occured while releasing object: " + ex.ToString());
      }
      finally
      {
        GC.Collect();
      }

    }
  }
}

You get the idea. There's actually a lot of formatting you can do hidden within the bowels of this library, but of course it'll never be as robust as the business users think it will be when they use the _actual_ Excel interface. You'll have to convince them that the pretty colors and effects aren't necessary. You can do some of it, sure, but not all of it.

Sure, this isn't a big deal. Excel interop has been around and used by many since the beginning of time in .NET. But, like I said, you may have managed to avoid it for as long as I have. And maybe all you're looking for is a quick example of how to get the damn thing working, rather than a search result where somebody else asks the question and the only answers they've received are "just use COM" or some other nonsense that, had they known what that meant, they wouldn't have asked the question in the first damn place.