4

I'm trying to create and save a new excel document in C# from a bunch of aggregate data. Right now, I'm just trying to get the basics working as far as editing the table cells, etc. Here's me just trying to set the contents of one cell:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Microsoft.Office.Interop.Excel;

namespace DebugReport
{
    class MonumentDebugReport
    {
        static void Main(string[] args)
        {    
            Worksheet spreadsheet = new Worksheet();

            spreadsheet.Cells[0, 0] = "stuff";
       }
    }
}

This returns:

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: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

on the line in which I'm setting the cell. To me, this looks exactly like most of the examples / tutorials I've seen, and I can't find anything in the C# documentation to indicate that this wouldn't work.

Nightmare Games
  • 2,205
  • 6
  • 28
  • 46
  • 3
    COM errors can be confuzzling, the more you get it wrong the worse they get. You cannot create a WorkSheet object, it requires using a factory method, Application.WorkSheets.Add(). There is a *ton* of example code out there to show you how to do this the right way. The kind you google with "c# how to create a worksheet". Don't ignore it. – Hans Passant Nov 12 '14 at 00:38
  • Thanks for the tip, but I think you're overestimating my knowledge of excel terminology. My search was "how to create an excel file in c#", and the distinction between worksheet, workbook, etc. was never made clear in those results. I didn't realize that hierarchy was necessary until I saw vba4all's answer. – Nightmare Games Nov 12 '14 at 19:37

1 Answers1

6

Like Hans already said you need to create an Excel application first, then you add a Workbook to it, then you add a Worksheet (or try to access an already existing Sheets[1])

Give using Microsoft.Office.Interop.Excel and alias

using Excel = Microsoft.Office.Interop.Excel

and then

Excel.Application xlApp = new Excel.Application();
xlApp.Visible = true;
Excel.Workbook xlWb = xlApp.Workbooks.Add() as Excel.Workbook;
Excel.Worksheet xlSheet = xlWb.Sheets[1] as Excel.Worksheet;
Excel.Range range = xlSheet.get_Range("A1");

range.Value = "hello world!";

and then have a look at How to properly clean up Excel interop objects

Community
  • 1
  • 1
  • This works beautifully. Definitely a bit more work (lines of code) than I was expecting, but I got an excel file generated using Worksheet.SaveAs with a test cell filled in. Thanks for detailed explanation! – Nightmare Games Nov 12 '14 at 19:33