Exercise 3: Adding Navigation capabilities to a workbook using ECMAScript

In this exercise you will be taking a look at how you can recreate navigation implemented with VBA within a Microsoft Excel workbook using the Excel Services JSOM with an Excel Web Access web part.

Task 1 – Examining Navigation with Macros within Microsoft Excel and Excel Web Access

In this task you will be taking a look how the navigation is handled within Microsoft Excel and what functionality cannot be ported directly over to Excel Web Access without the use of ECMAScript.

1. In Excel open up Fabrikam_Reporting.xlsm in rExcelJSOM\Started.
   1. Click on Balance Sheets
   2. Click on Return to get back to the Menu sheet.
   3. Click View Data

   4. Click on Return to get back to the Menu sheet.

      Note:

      Notice how you managed to navigate throughout the document with these embedded buttons. In the background there are macros that handle this functionality. Since VBA does not run in Excel Services, when you open the document on the server, the navigation functionality will not work.

      Navigation is a common task in complex workbooks that consist of numerous worksheets. By providing basic navigation, you can make complex workbooks easier to use and understand to casual users of the workbook.

2. Click on the File tab
3. Click on Save & Send
4. Click Save to SharePoint and double-click Browse for a location
5. Click in the File name box and enter in
6. https://intranet.contoso.com/Shared%20Documents/Fabrikam_Reporting.xlsm
7. Click Save (Note: if there are any issues saving the document from the client to SharePoint, then close the client and upload the document from the Shared Docuements library in SharePoint.)
8. If a new SharePoint window opens with the new Fabrikam document, close the web page.
9. Create a new web part page identical to that in exercise 1, task 1. Make sure to set the Excel Web Access web part to Fabrikam_Reporting.xlsm instead of EwaTest.xlsx (exercise 1 – task 1) and name the page FabrikamReporting.

10. Click on the nagivation links that you were using in Excel (steps 1.a-d)

    Note:

    As you’ve noticed, the navigation no longer works once the document has been loaded on the SharePoint site. The macros that were once handling navigation, are no longer functional. You can easily reimplement this functionality using the Excel Services JSOM.

Task 2 – Adding Funtionality to the Buttons Using the Excel Services JSOM

1. In this task you will be restoring navigation to the Fabrikam_Reporting.xlsm workbook displayed on the server using an Excel Web Access web part.
2. Open up FabriKamReporting.js in Visual Studio.

   1. Add the following line of code after var logItems = true;

      var currentSheetName = "";

   2. Begin by adding the next piece of code to the end of function GetEwa(). You are now adding another event handler and retrieving the value at the named range LOG_JSOM in the Inputs sheet.

      om.add_workbookChanged(onWorkbookChange); om.getActiveWorkbook().getRangeA1Async('\'Inputs\'!LOG_JSOM', getInputsComplete);

   3. Add the following callback function getRangeComplete() after the end of function GetEwa(). This will handle changing the cell focus for navigation changes.

      function getRangeComplete(asyncResult) { var range = asyncResult.getReturnValue(); writelog('getRangeComplete'); range.activateAsync(); }

   4. Add the next callback function showLogging()after the end of getRangeComplete(). This will handle toggling the logging on and off that appears in the Content Editor web part.

      function showLogging(asyncResult) { writelog('showLogging',1); var value = asyncResult.getReturnValue(); if(value == 'no') { writelog('showLogging: Show Logging FALSE'); logItems = false; } else { logItems = true; writelog('showLogging: Show Logging TRUE'); } }

   5. Add the next callback function getInputsComplete() after the end of showLogging(). This purpose of this callback function is to handle retrieving the Log JSOM values from the Inputs sheet.

      function getInputsComplete(asyncResult) { writelog('getInputsComplete: Entered'); var format = null; var range = asyncResult.getReturnValue(); range.getValuesAsync(format, showLogging); writelog('getInputsComplete: Exited'); }

   6. Add the following event handler onWorkbookChange() after the end of getInputsComplete(). Excel Services triggers this event handler every time a cell is changed. The purpose of this event handler is to check whether the script should continue logging or not.

      function onWorkbookChange(workbookArgs) { if(workbookArgs.getWorkbook().getActiveSheet().getName()=='Inputs') { om.getActiveWorkbook().getRangeA1Async('\'Inputs\'!LOG_JSOM', getInputsComplete); } }

   7. Add this remaining code to the end of onSelectionChange(). This will be handling all the navigation. The function is going to check which cell was selected. If the selected cell was one that contains a button, it will set the focus of the workbook to the appropriate place.

      if(rangeArgs.getRange().getSheet().getName() == 'Menu' && currentSheetName == 'Menu') { if(rangeArgs.getRange().getAddressA1() == '\'Menu\'!C11:D12') { currentSheetName = 'Inputs'; om.getActiveWorkbook().getRangeA1Async( '\'Inputs\'!INPUTS', getRangeComplete); }else if(rangeArgs.getRange().getAddressA1() == '\'Menu\'!C14:D15') { currentSheetName = 'Financial Statements'; om.getActiveWorkbook().getRangeA1Async( '\'Financial Statements\'!BALANCE_SHEET', getRangeComplete); }else if(rangeArgs.getRange().getAddressA1() == '\'Menu\'!C17:D18') { currentSheetName = 'Financial Statements'; om.getActiveWorkbook().getRangeA1Async( '\'Financial Statements\'!CASHFLOW', getRangeComplete); }else if(rangeArgs.getRange().getAddressA1() == '\'Menu\'!C20:D21') { currentSheetName = 'Financial Statements'; om.getActiveWorkbook().getRangeA1Async( '\'Financial Statements\'!INCOME_STATEMENT', getRangeComplete); }else if(rangeArgs.getRange().getAddressA1() == '\'Menu\'!C23:D24') { currentSheetName = 'Supporting Data'; om.getActiveWorkbook().getRangeA1Async( '\'Supporting Data\'!A1', getRangeComplete); } } else if (rangeArgs.getRange().getSheet().getName() == 'Menu' && currentSheetName != 'Menu' ) { currentSheetName = 'Menu'; } else { var values = rangeArgs.getFormattedValues(); if (values.length == 1) { if(values[0][0].trim() == 'Return') { om.getActiveWorkbook().getSheets() .getItemByName('Menu').activateAsync(); } } }

3. Save and upload FabriKamReporting.js to the SharePoint site making sure to overwrite the original.

4. Load FabrikamReporting.aspx to view the changes.

   Figure 7

   FabrikamReporting.aspx view

Exercise 3 Verification

To verify that the content was loaded correctly for the FabriKam Reports, do the following:

1. Click the Modify Inputs button. This should take you to the Input sheet.
   1. Change the Log JSOM cell from yes to no.
   2. Change the cell selection and you should see a message to the left stating that logging has been disabled (Show Logging FALSE).
   3. Re-enable it by changing the value back to yes.
2. Click the Return button to return to the main menu.
3. Click on the remaining links to ensure that the rest of the navigation is working correctly.