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

by

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:

According to MSDN

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

Hope that helps.

More Related Contents:

  1. Reading Datetime value From Excel sheet
  2. How to read an excel file in C# without using Microsoft.Office.Interop.Excel libraries
  3. Excel interop: _Worksheet or Worksheet?
  4. Get instance of Excel application with C# by Handle
  5. Why does Microsoft.Office.Interop.Excel.Application.Quit() leave the background process running?
  6. The quest for the Excel custom function tooltip
  7. How to make correct date format when writing data to Excel
  8. Importing Excel into a DataTable Quickly
  9. How to save workbook without showing save dialog with Excel interop?
  10. Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#
  11. Accessing an open Excel Workbook in C#
  12. As of today, what is the right way to work with COM objects? [duplicate]
  13. How to read data of an Excel file using C#?
  14. A Simple C# DLL – how do I call it from Excel, Access, VBA, VB6?
  15. Fastest method to remove Empty rows and Columns From Excel Files using Interop
  16. unable to cast COM object of type ‘microsoft.Office.Interop.Excel.ApplicationClass’ to ‘microsoft.Office.Interop.Excel.Application'”
  17. Export DataTable to Excel File
  18. Fastest function to generate Excel column letters in C#
  19. Active Directory COM Exception – An operations error occurred (0x80072020)
  20. 64 bit C# with a 32 bit VB6 COM object
  21. What options are available for Shell32.Folder.GetDetailsOf(..,..)?
  22. Optimal way to Read an Excel file (.xls/.xlsx)
  23. Starting and stopping IIS Express programmatically
  24. Cell styles in OpenXML spreadsheet (SpreadsheetML)
  25. Exposing .NET events to COM?
  26. Get the Column Index of a Cell in Excel using OpenXML C#
  27. Using ExcelDataReader to read Excel data starting from a particular cell
  28. Proper way of releasing COM objects?
  29. EPPlus – LoadFromCollection – Text converted to number
  30. .Net Excel Interop Deleting a worksheet

Leave a Comment