none
Excel 2007 - How to make a specific sheet the active sheet

    Question

  • Hi folks,

    This is my first foray into using Open XML - and its overwhelming!

    I'm trying to enhance some existing C# code that manipulates the contents of an existing Excel 2007 document - all I'm trying to do is to make a sheet (whose name is specified in an input parameter) the active sheet before I save the document.

    I've downloaded the SDK and tools and have used the Compare Files tool to compare two Workbooks with different active sheets so I can see which elements and attributes need to be manipulated. 

    It looks as if I need to do the following:
    1. Find and de-activate the currently active sheet
    2. Set my desired sheet to be the active sheet
    3. Set the workbook activetab attribute to the index of my desired work sheet

    But I have no idea how to achieve this!

    Thanks in advance for any help offered
    stevebee77
    Tuesday, February 16, 2010 4:14 PM

Answers

  • After much tinkering, the following method seems to do the job!

    stevebee77

    private static void SetActiveSheet(string workingDocument, string activeSheetName)
    {
        IEnumerable<Sheet> sheets;
        WorksheetPart worksheetPart;
        SheetViews sheetViews1;
        SheetView sheetView1;
    
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(workingDocument, true, MCMode.Office2007, true))
        {
            //Check if sheet exists
            sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == activeSheetName);
            if (sheets.Count() == 0)
            {
                return;
            }
    
            //Loop through all sheets
            sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
            byte tabIndex = 0;
            byte idxCount = 0;
            foreach (Sheet s in sheets)
            {
                worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(s.Id);
                sheetViews1 = worksheetPart.Worksheet.GetFirstChild<SheetViews>();
                sheetView1 = sheetViews1.GetFirstChild<SheetView>();
    
                if (s.Name == activeSheetName)
                {
                    sheetView1.TabSelected = true;
                    sheetView1.TopLeftCell = "A1"; //Scroll to top left
                    tabIndex = idxCount;
                }
                else
                {
                    sheetView1.TabSelected = null;
                }
                idxCount++;
            }
    
            //Select the tab
            Workbook workbook1 = document.WorkbookPart.Workbook;
            BookViews bookViews1 = workbook1.GetFirstChild<BookViews>();
            WorkbookView workbookView1 = bookViews1.GetFirstChild<WorkbookView>();
            workbookView1.ActiveTab = (UInt32Value)tabIndex;
        }
    }
    


    • Marked as answer by stevebee77 Wednesday, February 17, 2010 2:18 PM
    Wednesday, February 17, 2010 2:17 PM

All replies

  • Here are the steps you need to take:
    1. Open workbook with the Open XML SDK
    2. Access the main workbook part
    3. Figure out the index (remember that Excel starts with index 0) of the sheet you want to be active
    4. Look for workbookView object 
    5. set activeTab property to the index you found in step #3 (cache the old value - default is 0)
    6. Open the worksheet that was the old active tab (based on cache value found in step #5)
    7. In the worksheet found in step #6, delete tabSelected attribute from sheetView
    8. Open the worksheet that you want to be the new active tab
    9. Add tabSelected attribute to sheetView element

    At that point you should be all set. The Open XML SDK Productivity Tool can auto generate code for you based on comparing two files.


    Zeyad Rajabi (MS)
    Tuesday, February 16, 2010 6:37 PM
  • After much tinkering, the following method seems to do the job!

    stevebee77

    private static void SetActiveSheet(string workingDocument, string activeSheetName)
    {
        IEnumerable<Sheet> sheets;
        WorksheetPart worksheetPart;
        SheetViews sheetViews1;
        SheetView sheetView1;
    
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(workingDocument, true, MCMode.Office2007, true))
        {
            //Check if sheet exists
            sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == activeSheetName);
            if (sheets.Count() == 0)
            {
                return;
            }
    
            //Loop through all sheets
            sheets = document.WorkbookPart.Workbook.Descendants<Sheet>();
            byte tabIndex = 0;
            byte idxCount = 0;
            foreach (Sheet s in sheets)
            {
                worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(s.Id);
                sheetViews1 = worksheetPart.Worksheet.GetFirstChild<SheetViews>();
                sheetView1 = sheetViews1.GetFirstChild<SheetView>();
    
                if (s.Name == activeSheetName)
                {
                    sheetView1.TabSelected = true;
                    sheetView1.TopLeftCell = "A1"; //Scroll to top left
                    tabIndex = idxCount;
                }
                else
                {
                    sheetView1.TabSelected = null;
                }
                idxCount++;
            }
    
            //Select the tab
            Workbook workbook1 = document.WorkbookPart.Workbook;
            BookViews bookViews1 = workbook1.GetFirstChild<BookViews>();
            WorkbookView workbookView1 = bookViews1.GetFirstChild<WorkbookView>();
            workbookView1.ActiveTab = (UInt32Value)tabIndex;
        }
    }
    


    • Marked as answer by stevebee77 Wednesday, February 17, 2010 2:18 PM
    Wednesday, February 17, 2010 2:17 PM
  • Thanks a lot for posting this. It helped me a lot.

    One issue I'm experiencing is when I've hidden sheets. In this case, when clicking on a sheet tab(in Excel) it activates another sheet. Or it doesn't active any sheet (may be it's activating a hidden) sheet. I'm guessing the tabIndex or some index is off by the number of hidden sheets. If I've only one hidden sheet, then clicking on the first sheet doesn't activate anything, clicking on the second activates the first, clicking on the third activates the second, and so on. Below is my code that hides some of the sheets from a spreadsheet.

     

    Dim mySpreadsheetDoc As SpreadsheetDocument = SpreadsheetDocument.Open("mutlipleSheets.xlsx", True)

     

                    For Each sheetNameToHide As String In listOfSheetsToHide

                        Dim sheetsCollection As IEnumerable(Of Sheet) = mySpreadsheetDoc.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = sheetNameToHide)

                        If sheetsCollection.Count() = 0 Then

                            ' The specified worksheet does not exist.

                            Continue For

                        End If

                        Dim sheetToHide As Sheet = sheetsCollection(0)

                        sheetToHide.State = SheetStateValues.Hidden

     

                        mySpreadsheetDoc.WorkbookPart.Workbook.Save()

                    Next

     

    Thursday, March 25, 2010 8:38 PM