Buck Hicks Buck Hicks - 3 months ago 22
C# Question

How do I set the order of Worksheets as I add them to Excel Programmatically?

I have an Excel work book that I am adding work sheets using a loop. In the sample code below I am using a fixed array but the real code uses a database to get the names from and there can be one or many (less than 10 though).

The issue that I am having (with my code) is the worksheets get added in the reverse order of the array. So the excel sheet will open with the tabs ordered like this 1103 1102 1101 Sheet1. I know I can reorder the results from the database but that feels like a hack and I am sure there is a way to accomplish this in code. What needs to be done to set the order of tabs correctly inside my loop?

private static void SetWorksheet()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlApp = new Excel.Application();
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Add(1);


string[] storeArray = { "1101", "1102", "1103" };

foreach (string s in storeArray)
{
xlWorkBook.Worksheets.Add();
xlWorkBook.Worksheets.Move(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count]);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[1];
xlWorkSheet.Name = s;
releaseObject(xlWorkSheet);
}
releaseObject(xlWorkBook);
releaseObject(xlApp);
}

Answer

You can add new worksheet after the last sheet by put parameter 'After' in Add function.

xlWorkBook.Worksheets.Add(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count]);

Then you do not need to move it by using Move function.
And in the line

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[1];

It always get first sheet to change the name.
You need to change '1' to xlWorkBook.Sheets.Count

xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[xlWorkBook.Sheets.Count];

Finally your code should be as below

private static void SetWorksheet()
{
    Excel.Application xlApp;
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;
    object misValue = System.Reflection.Missing.Value;

    xlApp = new Excel.Application();
    xlApp.Visible = true;
    xlWorkBook = xlApp.Workbooks.Add(1);


    string[] storeArray = { "1101", "1102", "1103" };

    foreach (string s in storeArray)
    {
        xlWorkBook.Worksheets.Add(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count]);
        //xlWorkBook.ActiveSheet(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count]);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets[xlWorkBook.Sheets.Count];
        xlWorkSheet.Name = s;
        releaseObject(xlWorkSheet);
    }
    releaseObject(xlWorkBook);
    releaseObject(xlApp);  
}