How to add an excel ListObject to a given workshee

Question

I am currently developing an excel add-in in C# with several methods (table valued functions) that will be available to excel users and programmers (VBA).

How can I write a method which adds a new ListObject (excel table) to the given excel worksheet, and bind the given DataTable as data source? Something like the following:

using Excel = Microsoft.Office.Interop.Excel;
...
[ClassInterface(ClassInterfaceType.AutoDual)]
public class TableFunctions {
...

public Excel.ListObject CreateListObject(Excel.Worksheet ws, string TableName, DataTable dt, string CellStr = "A1")
{
...
}

This approach, sending the Worksheet object as parameter apparently doesn't work. Or could it?

Answer 1

After some research I found an answer to my question, how to add a ListObject (excel table) to a worksheet programmatically in C#:

public Excel.ListObject WriteToExcelTable(Excel.Worksheet WSheet, string TableName, string CellStr = "A1", bool ClearSheetContent = false)
{
    Excel.Range range;

    if (ClearSheetContent)
        WSheet.Cells.ClearContents();  // clear sheet content

    // get upper left corner of range defined by CellStr
    range = (Excel.Range)WSheet.get_Range(CellStr).Cells[1, 1];   //

    // Write table to range
    HelperFunc.WriteTableToExcelSheet(WSheet, this._tbl, range.Address);

    // derive range for table, +1 row for table header
    range = range.get_Resize(this.RowCount + 1, this.ColumnCount);

    // add ListObject to sheet

    // ListObjects.AddEx Method 
    // http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.listobjects.addex%28v=office.14%29.aspx

    Excel.ListObject tbl = (Excel.ListObject)WSheet.ListObjects.AddEx(
        SourceType: Excel.XlListObjectSourceType.xlSrcRange,
        Source: range,
        XlListObjectHasHeaders: Excel.XlYesNoGuess.xlYes);

    // set name of excel table
    tbl.Name = TableName;

    // return excel table (ListObject)
    return (Excel.ListObject)tbl;
}

See my article about this and other related code for excel and .net integration.