4

I am using Microsoft.Office.Interop.Excel in a winform where I am reading one excel file, processing the data, and outputting a new excel file. However I am having trouble writing to the cells -- specifically to add column headings. Here's the code:

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = (Worksheet)wb.Worksheets[1];
for (int i = 0; i < dt.Columns.Count; i++)
{
    for (int j = 0; j < dt.Rows.Count; j++)
    {
        ws.Cells[j + 1, i] = dt.Rows[j][i].ToString();
    }
}
ws.Cells[0, 0] = "Ticket Number";
ws.Cells[0, 1] = "Transit";
ws.Cells[0, 2] = "Outage Start Date";
ws.Cells[0, 3] = "Outage End Date";
ws.Cells[0, 4] = "Business Impact";
wb.Worksheets.Add(ws);

where "dt" is my DataTable. The nested for-loop doesn't throw a runtime error but the code following it does. The error just says: COM Exception was unhandled, Exception from HRESULT: 0x800A03EC.

Any advice is appreciated.

Regards.

Kevin
  • 1,252
  • 6
  • 32
  • 50
  • I just discovered that if I comment out the offending lines the program runs and outputs a file. However when I try to open it, it closes immediately and I can't delete it afterwards because Windows claims that Excel is using it even though I can't find the Excel app in the Task Manager. – Kevin Jun 28 '12 at 17:51
  • When you say in the Task Manager, you've checked the processes tab as well as the applications tab? It's easy to end up with spare excel.exe processes hanging around. You are closing the workbook in your excel app object, or at least shutting down the Excel app object too? – Rup Jun 28 '12 at 17:52
  • I was only looking in the App tab. – Kevin Jun 28 '12 at 18:00
  • 1
    Cells[] is 1-based, not zero-based... – Tim Williams Jun 28 '12 at 18:03
  • That's ERROR_INVALID_FLAGS. IIRC you actually want to set the .Value2 property of the cells - although in discussion beneath my deleted answer you note this doesn't help. However I'd recommend you use [NPOI](http://npoi.codeplex.com/) or [EPPlus](http://epplus.codeplex.com/) instead to read and write Excel spreadsheets - they're easier and often quicker than using Excel automation. – Rup Jun 28 '12 at 18:04
  • @TimWilliams D'oh, yes. That's probably the answer if you want to post it as one. – Rup Jun 28 '12 at 18:05
  • @TimWilliams: Changing the indices did the trick. Thanks. In the library I was using before Cells were zero based. However I am still having the problem that program doesn't seem to let go of the file and I have an extraneous process still accessing it. – Kevin Jun 28 '12 at 18:10
  • Actually, I just checked the Processes tab of the Task Manager and I had probably two dozen Excel processes running. How do I avoid this? – Kevin Jun 28 '12 at 18:15
  • You need to call `wb.Close()` and `xlApp.Quit()`, and [perhaps a few COM methods too](http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects-in-c-sharp) – Rup Jun 28 '12 at 18:17

1 Answers1

8

Cells[] is 1-based, not zero-based.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125