Testing in Excel

There are two ways to test visuals in Excel:

  • side loading the custom visual developer tool (best for debugging and examining code changes in real-time)
  • side loading your packaged .pbiviz file (best for checking the end-to-end user experience)

In either case, you will start by setting up Excel for Side Loading Add-Ins.

If you just want to try the example visuals, you still need to set up Excel for Side Loading Add-ins. Additional instructions for trying example visuals follows.

Set Up Excel for Side Loading Add-Ins

Office add-ins use an .xml file known as a “manifest” to specify metadata about itself, including a URL that points Office Apps to the location of the Add-in (SourceLocation).
Learn more about Office Add-in manifests here.

Sideloading an Office Add-in begins by pointing to a Shared Folder Catalog of manifests. The catalog can be a folder on your local machine and does not have to be the same folder where your visual code is stored.

  1. Share a Folder
    1. On the Windows computer where you want to host your manifest, go to the parent folder, or drive letter, of the folder you want to use as your shared folder catalog.
    2. Open the context menu for the folder (right-click) and choose Properties.
    3. Open the Sharing tab.
    4. On the Choose people … page, add yourself and anyone else with whom you want to share your add-in. If they are all members of a security group, you can add the group. You will need at least Read/Write permission to the folder.
    5. Choose Share > Done > Close
  2. Specify the Shared Folder as a Trusted Catalog
    1. Open a new document in any Excel, Word, or PowerPoint.
    2. Choose the File menu and then choose Options.
    3. Choose Trust Center, and then choose the Trust Center Settings button.
    4. Choose Trusted Add-in Catalogs.
    5. In the Catalog URL box, enter the full network path to the shared folder catalog, and then choose Add Catalog.
    6. Select the Show in Menu check box, and then choose OK.
    7. Close and re-open Excel so your changes will take effect.
  3. If you would like try out some example visuals in Excel, skip ahead to the Try Example Visuals section.

Sideload the Custom Visual Developer Tool

  1. Save this manifest to your Add-In Shared Folder Catalog. The SourceLocation of this manifest points to a specialized developer service that will look for the locally hosted visual you will run in step 2.
  2. In Power Shell, navigate to your visual’s project folder and run 
    pbiviz start

    as if you were testing your visual in Power BI (see this page for testing visuals in Power BI).

  3. In Excel, go to Insert > My Add-Ins to launch the Office Add-Ins dialog window.
    Insert Add-Ins
  4. Navigate to the Shared Folder tab, select the custom visual developer tool, and click Add.
    Note: If the custom visual developer tool does not appear, click the Refresh link in the upper-right.
    Developer Tool Insert
  5. You can now attach a debugger to your visual as you test the Excel experience.
    Recommendation: Set your debugger to break on caught exceptions. This will stop code execution wherever an error happens and allow you to debug from there.
    This page provides instructions for debugging Add-Ins with Window’s built-in F12 developer tools

Sideload a .pbiviz File

You can package your visual as a .pbiviz file by running

pbiviz package

The package is output to the dist folder of your visual project.
The .pbiviz file is the final format used for publishing visuals to the store. It can also be used for easily distributing visuals with others and testing the full Excel user experience.

After creating your .pbiviz file:

  1. Save your .pbiviz to a secure server location (HTTPS) that is configured to enable cross-origin resource sharing (CORS).
  2. Save this manifest to your Add-In Shared Folder Catalog
  3. Modify the SourceLocation of the manifest to the URL of your .pbiviz file
  4. If you would like, modify additional metadata for your visual.
    Learn more about Office Add-in manifests here.
    • DisplayName is the name used for your visual in Excel.
    • IconUrl is the icon used for your visual in Excel.
  5. In Excel, go to Insert > Custom Visuals > More Custom Visuals to launch the “Custom Visuals” tab of the Insert Chart dialog window
    Insert Ribbon
  6. Select your visual and click OK
    Note: If your visual does not appear, close and reopen the Insert Chart dialog window.

Try Example Visuals

  1. If you have not already, follow the Set Up Excel for side loading Add-Ins instructions above.
  2. Save the below manifests to your Add-In Shared Folder Catalog
  3. In Excel, select some spreadsheet data and go to Insert > Custom Visuals > More Custom Visuals to launch the “Custom Visuals” tab of the Insert Chart dialog window.
    Download this file to get sample data sets well suited for these example custom visuals
    Insert Ribbon
  4. Select an example visual and click OK
    Note: If an example visual does not appear, close and reopen the Insert Chart dialog window.
    Test Visual Insert

Next: Best Practices

COMMENTS