Excel Automation From .NET - creating a new worksheet

I am attempting what seems like a simple task: using C# to create a new Excel document containing new worksheets.

For some reason, I am getting a strange COM error (0x800A03EC)

Has anyone managed to get this to work? Does anyone have suggestions as to how to troubleshoot this开发者_StackOverflow中文版?

I've isolated this into the minimum amount of code:

using Microsoft.Office.Interop.Excel;
using System.Diagnostics;

namespace ExcelAutomation
{
    public static class ExcelTests
    {
        public static void CreateWorksheet()
        {
            try
            {
                var app = new Microsoft.Office.Interop.Excel.Application();
                app.Visible = true;
                var workBooks = app.Workbooks;
                var newWorkbook = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                Worksheet existingWorksheet = (Worksheet)newWorkbook.Sheets[1];

                Worksheet workSheet = (Worksheet)newWorkbook.Sheets.Add
                        (
                            null, // before
                            existingWorksheet,
                            null, // 1,
                            null //XlSheetType.xlWorksheet
                        );
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                Trace.WriteLine(string.Format("Caught COMException. Message: \"{0}\"", ex.Message));  
            }
        }
    }
}

The output window now says:

Caught COMException. Message: "Exception from HRESULT: 0x800A03EC"

The mistake I'm making is to use null for optional values that I don't want to set.

Instead I should use System.Reflection.Missing.Value

        Worksheet workSheet = (Worksheet)newWorkbook.Sheets.Add
                (
                    existingWorksheet, // before
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value, // 1,
                    System.Reflection.Missing.Value //XlSheetType.xlWorksheet
                );

I Think this will help you

Visit http://www.aspose.com/docs/display/cellsnet/Adding+New+Worksheets+to+Workbook+and+Activating+a+Sheet