C# how to add Excel Worksheet programatically Office XP / 2003

Question

Hi all,

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 the 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 a COM Exception and my googling has not given me an answer.

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

Thanks

Answer 1

If you don't already, you must have 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 example code above 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 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

You can use OLEDB to create and manipulate Excel files. See this question for links and samples.

Answer 3

Another "Up Tick" for AR..., but if you don't have to use interop I would avoid it altogether. This product is actually quite interesting: http://www.clearoffice.com/ and it provides a very intuitive, fully managed, api for manipulation excel files and seems to be free. (at least for the time being) SpreadSheetGear is also excellent but pricey.

my two cents.

Answer 4

Hi all,

Would like to thank you for some excelent replies. AR, your a star and it works perfectly. I had noticed last night that the Excel.exe was not closing so did some research and found out about having 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 :)

Answer 5

Here are a couple things I figured out:

1)You can't open more than one instance of the same object at the same time. For Example if you instanciate a new excel sheet object called 'xlsheet1' you have to release it before creating another excel sheet object ex 'xlsheet2'. It seem as COM looses track of the object and leaves a zombie process on the server.

2) Using the open method associated with excel.workbooks also becomes difficult to close if you have multiple users accesing the same file. Use the Add method instead it words just as good without locking the file. eg. xlBook = xlBooks.Add("C:\location\XlTemplate.xls")

3) Place your garbage collection in a separate block or method after releasing the COM objects.