Tip: Format an Excel Range as a Table Programatically

Sometimes, you have a range of cells and you want to choose the Format As Table button to format the cells as a nice table.

Here's how to do that using Excel interop:

public void FormatAsTable(Excel.Range SourceRange, string TableName, string TableStyleName)
{
    SourceRange.Worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
    SourceRange, System.Type.Missing, XlYesNoGuess.xlYes, System.Type.Missing).Name =
        TableName;
    SourceRange.Select();
    SourceRange.Worksheet.ListObjects[TableName].TableStyle = TableStyleName;
}

That's it! To apply a table style, e.g. TableStyleMedium15, to a range of cells, you say:

Excel.Range SourceRange = (Excel.Range)oSheet.get_Range("A6","X10"); // or whatever range you want here
FormatAsTable(SourceRange, "Table1", "TableStyleMedium15");

The "Table1" is just a random name for the table; it's arbitrary, but every table you format must be a unique range. The table style names you can find out by recording a macro, applying the formatting by hand, and then reading off of the VBA module what style name Excel filled in once you've stopped the recording.