How to set active sheet with Open XML SDK 2.5

Question

using the example here How to Copy a Worksheet within a Workbook

I have successfully been able to clone/copy sheets in my excel file, however when I open the excel the 2nd sheet is the active(visible) sheet. I haven't been able to locate a property that could do thins.....Is there any way to specify what sheet is active?

I've tried to force it by opening and editing the first sheet in the file thinking it was the last edited sheet that was active but that didn't work either.

any help would be great. TIA

update: looking at the workbook.xml created when renaming the .xlsx to .zip I came accross the 'activeTab' property. made a quick change to my code and seems to work just fine

public void SetFirstSheetInFocus(String xlsxFile)
    {
        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(xlsxFile, true))
        {
            //Get a reference to access the main Workbook part, which contains all references
            WorkbookPart _workbookPart = spreadSheet.WorkbookPart;
            if (_workbookPart != null)
            {
                WorkbookView _workbookView = spreadSheet.WorkbookPart.Workbook.BookViews.ChildElements.First<WorkbookView>();
                if (_workbookView != null)
                {
                    _workbookView.ActiveTab = 0; // 0 for first or whatever tab you want to use
                }      
                // Save the workbook.
                _workbookPart.Workbook.Save();
            }

        }
    }

Desideratum

Not sure how far you are with your implementation but OpenXML is horrible to work with. I was able to redo an OpenXML implementation much faster then it took for me to initially create it, and it was more readable and removed hundreds of lines of code. If it is not to late check this out it may save your sanity. Check out ClosedXML => closedxml.codeplex.com – Mizzen

thanks Tony I'll take a look at that. unfortunately the current requirement is to use the Open XML SDK (no 3rd party library is allowed) for now but it definitely looks promising for the future. I actually was able to set the active tab, I was looking in the wrong area – Desideratum

instead of open xlsx file as zip file and looking into the xmls, use the productivity tool. Basically you can draft a new file or editing an existing one and the tool use reflection to generate all the code you need for building an instance of the file. XML sdk 2.5 – Concomitance

Answer 1

If the name of your sheet is in the variable

sheetName  

you can set the sheet with that name active like this:

using (var spreadsheetDoc = SpreadsheetDocument.Open(emptyHIPTemplatePath, true /* isEditable */, new OpenSettings { AutoSave = false }))
{        
    var workbookPart = spreadsheetDoc.WorkbookPart;
    var workBook = spreadsheetDoc.WorkbookPart.Workbook;
    var sheet = workBook.Descendants<Sheet>().FirstOrDefault(s => s.Name == sheetName);     

    var sheetIndex = workBook.Descendants<Sheet>().ToList().IndexOf(sheet);
    var workBookView = workBook.Descendants<WorkbookView>().First();
    workBookView.ActiveTab = Convert.ToUInt32(sheetIndex);

    ...
    workBook.Save();            
}

Eastertide

Note that you'll want to call workbook.Close() after you've called workbook.Save() – Nuptial

Answer 2

From Vincent Tan's book:

The SheetId property doesn't determine the order. The order of appending the Sheet classes to the Sheets class, does.

When you add a sheet, it gets the next index, but a single sheet does not have an index. OpenXML gives it an index when you are done adding sheets. Again, from Vincent Tan's book:

Let's say you have 3 worksheets named Sheet1, Sheet2 and Sheet3. However, when you appended the corresponding Sheet classes, you did it as Sheet2, Sheet3 and Sheet1, in that order.

Burress