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);
}

Wait a second. Are you trying to add worksheets or workbooks? Workbooks.Add() seems odd here. — lc., Sep 14 '12 at 13:53
@lc. Not if you are creating a new Excel application object. Instead of opening an existing workbook, you can add a new one, add some Worksheets to it, do what you need to do with the worksheets and then save it. — JMK, Sep 14 '12 at 14:05
@JMK but this is adding worksheets.Count new workbooks (in the plural)... Probably not intended? Or I'm just missing something — lc., Sep 14 '12 at 14:19
@lc. Sorry, you are of course correct, this shouldn't be in the for loop, in this case you are creating 20 workbooks and adding one worksheet to each one. — JMK, Sep 14 '12 at 14:21

JMK · Accepted Answer · 2012-09-14T15:02:03.997

4

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.

edited Sep 14 '12 at 15:02

answered Sep 14 '12 at 14:02

JMK

27,273
52
163
280

thanks for all your help! this works better. but it's still not right. i'm getting sheets 'test19-test1, Sheet1-20'. – CurlyFro Sep 14 '12 at 14:46
1

I'd add the sheets at the end: `collection[i] = wb.Worksheets.Add(After: wb.Sheets[wb.Sheets.Count]);` – Jon Crowell Sep 14 '12 at 14:51
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'? – CurlyFro Sep 14 '12 at 14:55
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.) – Jon Crowell Sep 14 '12 at 15:00
I have updated my answer, if you copy/paste it will now do exactly what you want – JMK Sep 14 '12 at 15:02

score 0 · Answer 2 · edited Feb 14 '13 at 22:55

Here is my code that does this:

' first worksheet
If oExcel.Application.Sheets.Count() < 1 Then
    oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
    oSheet = oExcel.Worksheets(1)
End If
oSheet.Name = "one"
oSheet.Range("B1").Value = "First One"

' second
If oExcel.Application.Sheets.Count() < 2 Then
    oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
    oSheet = oExcel.Worksheets(2)
End If
oSheet.Name = "two"
oSheet.Range("B1").Value = "Second one"

' third
If oExcel.Application.Sheets.Count() < 3 Then
    oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
    oSheet = oExcel.Worksheets(3)
End If
oSheet.Name = "three"
oSheet.Range("B1").Value = "Thrid"

' next
If oExcel.Application.Sheets.Count() < 4 Then
    oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
Else
    oSheet = oExcel.Worksheets(4)
End If
oSheet.Name = "four"
oSheet.Range("B1").Value = "Four"

Trying to add multiple sheets to excel

2 Answers2

Linked