C# - How to add an Excel Worksheet programmatically - Office XP / 2003

Question

I am just starting to fiddle with Excel via C# to be able to automate the creation, and addition to an Excel file.

I can open the file and update its data and move through the existing worksheets. My problem is how can I add new sheets?

I tried:

Excel.Worksheet newWorksheet; newWorksheet = (Excel.Worksheet)excelApp.ThisWorkbook.Worksheets.Add(                 Type.Missing, Type.Missing, Type.Missing, Type.Missing); 

But I get below COM Exception and my googling has not given me any answer.

Exception from HRESULT: 0x800A03EC Source is: "Interop.Excel"

I am hoping someone maybe able to put me out of my misery.

Answer 1

You need to add a COM reference in your project to the "Microsoft Excel 11.0 Object Library" - or whatever version is appropriate.

This code works for me:

private void AddWorksheetToExcelWorkbook(string fullFilename,string worksheetName) {     Microsoft.Office.Interop.Excel.Application xlApp = null;     Workbook xlWorkbook = null;     Sheets xlSheets = null;     Worksheet xlNewSheet = null;      try {         xlApp = new Microsoft.Office.Interop.Excel.Application();          if (xlApp == null)             return;          // Uncomment the line below if you want to see what's happening in Excel         // xlApp.Visible = true;          xlWorkbook = xlApp.Workbooks.Open(fullFilename, 0, false, 5, "", "",                 false, XlPlatform.xlWindows, "",                 true, false, 0, true, false, false);          xlSheets = xlWorkbook.Sheets as Sheets;          // The first argument below inserts the new worksheet as the first one         xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);         xlNewSheet.Name = worksheetName;          xlWorkbook.Save();         xlWorkbook.Close(Type.Missing,Type.Missing,Type.Missing);         xlApp.Quit();     }     finally {         Marshal.ReleaseComObject(xlNewSheet);         Marshal.ReleaseComObject(xlSheets);         Marshal.ReleaseComObject(xlWorkbook);         Marshal.ReleaseComObject(xlApp);         xlApp = null;     } } 

Note that you want to be very careful about properly cleaning up and releasing your COM object references. Included in that StackOverflow question is a useful rule of thumb: "Never use 2 dots with COM objects". In your code; you're going to have real trouble with that. My demo code above does NOT properly clean up the Excel app, but it's a start!

Some other links that I found useful when looking into this question:

• Opening and Navigating Excel with C#
• How to: Use COM Interop to Create an Excel Spreadsheet (C# Programming Guide)
• How to: Add New Worksheets to Workbooks

According to MSDN

To use COM interop, you must have administrator or Power User security permissions.

Hope that helps.

Answer 2

Would like to thank you for some excellent replies. @AR., your a star and it works perfectly. I had noticed last night that the Excel.exe was not closing; so I did some research and found out about how to release the COM objects. Here is my final code:

using System; using System.Collections.Generic; using System.Text; using System.Reflection; using System.IO; using Excel;  namespace testExcelconsoleApp {     class Program     {         private String fileLoc = @"C:\temp\test.xls";          static void Main(string[] args)         {             Program p = new Program();             p.createExcel();         }          private void createExcel()         {             Excel.Application excelApp = null;             Excel.Workbook workbook = null;             Excel.Sheets sheets = null;             Excel.Worksheet newSheet = null;              try             {                 FileInfo file = new FileInfo(fileLoc);                 if (file.Exists)                 {                     excelApp = new Excel.Application();                     workbook = excelApp.Workbooks.Open(fileLoc, 0, false, 5, "", "",                                                         false, XlPlatform.xlWindows, "",                                                         true, false, 0, true, false, false);                      sheets = workbook.Sheets;                      //check columns exist                     foreach (Excel.Worksheet sheet in sheets)                     {                         Console.WriteLine(sheet.Name);                         sheet.Select(Type.Missing);                          System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);                     }                      newSheet = (Worksheet)sheets.Add(sheets[1], Type.Missing, Type.Missing, Type.Missing);                     newSheet.Name = "My New Sheet";                     newSheet.Cells[1, 1] = "BOO!";                      workbook.Save();                     workbook.Close(null, null, null);                     excelApp.Quit();                 }             }             finally             {                 System.Runtime.InteropServices.Marshal.ReleaseComObject(newSheet);                 System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);                 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);                 System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);                  newSheet = null;                 sheets = null;                 workbook = null;                 excelApp = null;                  GC.Collect();             }         }     } } 

Thank you for all your help.