How do I create an Excel table using a Word Add-In?

Question

I'm trying to create an Excel table and then insert it into a Word document using a C# Word Add-In. I've created a brand new Word 2010 Add-In and referenced the Microsoft.Office.Interop.Excel. As a quick test, I try to create a new Excel.Worksheet and populate it with a few values. I then insert it into the word document using the InsertDatabase method:

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
      Excel.Worksheet testWrkSht = new Excel.Worksheet();
      for(int i = 1; i < 5; i++)
      {
           testWrkSht.Range["A" + i.ToString()].Value = i ^ (i + 1);
      }
      Word.Document curDoc = this.Application.ActiveDocument;
      curDoc.Paragraphs[1].Range.InsertDatabase(testWrkSht);

}

My problem is that when I try to execute this, I get the following error:

An exception of type 'System.InvalidCastException' occurred in MyCustomAddIn.dll but was not handled in user code

Additional information: Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.WorksheetClass' to interface type 'Microsoft.Office.Interop.Excel._Worksheet'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D8-0000-0000-C000-000000000046}' failed due to the following error: The interface is unknown. (Exception from HRESULT: 0x800706B5).

How do I create an Excel spreadsheet table, and insert it into the Word Doc?

Answer 1

Since you're creating a Word Add-In, you'll need to open another instance of Excel, and then create the Excel Worksheet. Here's a quick example:

    Excel.Application xlApp = new Excel.Application();

    if (xlApp == null)
    {
        Console.WriteLine("Excel could not be started.  Check that your office installation and project references are correct.");
        return;
    }
    xlApp.Visible = true;

    Excel.Workbook wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];

    if (ws == null)
    {
        Console.WriteLine("Worksheet could not be created.  Check that you office installation and project references are correct.");
    }

    for(int i = 1; i < 5; i++)
    {
        ws.Range["A" + i.ToString()].Value = i * (i + 1);
    }

    wb.SaveAs(Filename: "C:\\temp\\test.xlsx");

This also assumes that you've added the appropriate using statement: using Excel = Microsoft.Office.Interop.Excel; as well as added a reference to the Excel Interop.