Trying to add multiple sheets to excel

Question

i'm trying to programmatically add sheets to a new excel document.

my expected output is sheets named 'test1-20' but instead i get 'Sheet1-19, test20'.

why doesn't this work?

Workbook workbook;
Application objExcel;

objExcel = new Application();
objExcel.Visible = false;
objExcel.DisplayAlerts = false;

for (var i = 0; i < worksheets.Count; i++)
{
    workbook= objExcel.Workbooks.Add(Missing.Value);
    var worksheet = (Worksheet)workbook.Worksheets.get_Item(i + 1);
    worksheet.Name = string.Format("test{0}", i + 1);
}

Solution

Try this:

using System;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

void MyMethod()
{
    try
    {
        var _excel = new Excel();

        var wb = _excel.Workbooks.Add();
        var collection = new Microsoft.Office.Interop.Excel.Worksheet[20];

        for (var i = 19; i >= 0; i--)
        {
            collection[i] = wb.Worksheets.Add();
            collection[i].Name = String.Format("test{0}", i + 1);
        }

        for (var i = 0; i < 3; i++)
        {
            wb.Worksheets[21].Delete();
        }

        //collection is an array of worksheet objects,
        //the worksheet objects in your workbook.
        //You can access each individual worksheet and
        //work with it in the same way you access any object in an array

        var thisWorksheet = collection[9];
        var thisRange = thisWorksheet.Range["A1"];
        thisRange.Value = "Hello World";

        wb.SaveAs(@"c:\test\whatever.xlsx");
        wb.Close();
    }
    finally
    {
        Marshal.ReleaseComObject(_excel);
    }
}

Your visible property is set to false by default, so it is not neccessary to do this explicitly, no alerts are displayed in the above code so this isn't neccessary either. I have tested the above code and can confirm it works.

Comments

[CurlyFro]

thanks for all your help! this works better. but it's still not right. i'm getting sheets 'test19-test1, Sheet1-20'.

[Jon Crowell]

I'd add the sheets at the end: collection[i] = wb.Worksheets.Add(After: wb.Sheets[wb.Sheets.Count]);

[CurlyFro]

thanks Head. now the sheets are in the right order but i'm still getting sheets 'Sheet1-20, test1-20'. why are there 'Sheet1-20'?

[Jon Crowell]

I wonder if it has something to do with the way Excel is configured on your machine. When I run @JMK's code, I get 20 sheets added with the names I'd expect: Test1-1 through Test1-20. (I added the dash in his String.Format.)

[JMK]

I have updated my answer, if you copy/paste it will now do exactly what you want