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");
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.