Insert an external Excel file and populate it with JSON data

Summary

This sample shows how to insert an existing template from an external Excel file into the currently open Excel file. Then it retrieves data from a JSON web service and populates the template for the customer.

Diagram showing the sample inserting an external spreadsheet into the existing spreadsheet.

Note: If you don't already have an Microsoft 365 subscription, get one by joining the Office 365 Developer Program.

Features

  • Use insertWorksheetsFromBase64 to insert a worksheet from another Excel file into the open Excel file.
  • Get JSON data and add it to the worksheet.

Applies to

  • Excel on Windows, Mac, and on the web.

Prerequisites

To use this sample, you'll need to join Office Insider.

Solution

Solution Author(s)
Insert an external Excel file and populate it with JSON data Microsoft

Version history

Version Date Comments
1.0 5-18-2021 Initial release

Run the sample

To run the sample you just need to sideload the manifest. The add-in web files are served from this repo on GitHub.

  1. Download the manifest.xml and SalesTemplate.xlsx files from this sample to a folder on your computer.

  2. Open Office on the web.

  3. Choose Excel, and then open a new document.

  4. Select the Insert tab, and choose Office Add-ins.

  5. On the Office Add-ins dialog, select MY ADD-INS , choose the Manage My Add-ins drop-down, and then choose Upload My Add-in.

    The Office Add-ins dialog with a drop-down in the upper right reading "Manage my add-ins" and a drop-down below it with the option "Upload My Add-in"

  6. Browse to the add-in manifest file, and then select Upload.

    The upload add-in dialog with buttons for browse, upload, and cancel.

  7. Verify that the add-in loaded successfully. You'll see a PnP Insert Excel file button on the Home tab.

Once the add-in is loaded, use the following steps to try out the functionality.

  1. On the Home tab, choose PnP Insert Excel file.
  2. In the task pane, select the Choose file button.
  3. In the dialog box that opens, select the SalesTemplate.xlsx file that you downloaded previously, and choose Open.

A Contoso Sales Report will be inserted with a table and chart populated with data.

Key parts of this sample

When you select the SalesTemplate.xlsx file, the following code in index.js inserts the template. It sets up an object named options to identify the sheet by name (Template). Then it calls the Office.js insertWorksheetsFromBase64 API to insert the template into the current worksheet.

// STEP 1: Insert the template into the workbook.
const workbook = context.workbook;

// Set up the insert options.
var options = {
  sheetNamesToInsert: ["Template"], // Insert the "Template" worksheet from the source workbook.
  positionType: Excel.WorksheetPositionType.after, // Insert after the `relativeTo` sheet.
  relativeTo: "Sheet1",
}; // The sheet relative to which the other worksheets will be inserted. Used with `positionType`.

// Insert the external worksheet.
workbook.insertWorksheetsFromBase64(workbookContents, options);

Next, it gets the JSON which is in the data.json file in this repo.

// STEP 2: Add data from the "Service".
const sheet = context.workbook.worksheets.getItem("Template");

  // Get data from your REST API. For this sample, the JSON is fetched from a file in the repo.
  let response = await fetch(dataSourceUrl + "/data.json");
  if (response.ok) {
    var json = await response.json();
  } else {
    console.error("HTTP-Error: " + response.status);
  }

Finally, it adds the JSON to the table.

// Map JSON to table columns.
  const newSalesData = json.salesData.map((item) => [
    item.PRODUCT,
    item.QTR1,
    item.QTR2,
    item.QTR3,
    item.QTR4
  ]);

  // We know that the table in this template starts at B5, so we start with that.
  // Next, we calculate the total number of rows from our sales data.
  const startRow = 5;
  var address = "B" + startRow + ":F" + (newSalesData.length + startRow - 1);
      
  // Write the sales data to the table in the template.
  var range = sheet.getRange(address);
  range.values = newSalesData;
  sheet.activate();

Run the sample on localhost

If you prefer to host the web server for the sample on your computer, follow these steps:

  1. Open the index.js file.

  2. Edit line 4 to refer to the localhost:3000 endpoint as shown in the following code.

    const dataSourceUrl = "https://localhost:3000";
    
  3. Save the file.

  4. You need http-server to run the local web server. If you haven't installed http-server, you can do this with the following command:

    npm install --global http-server
    
  5. Use a tool such as openssl to generate a self-signed certificate that you can use for the web server. Move the cert.pem and key.pem files to the webworker-customfunction folder for this sample.

  6. From a command prompt, go to the web-worker folder and run the following command:

    http-server -S --cors . -p 3000
    
  7. To reroute to localhost run office-addin-https-reverse-proxy. If you haven't installed this proxy, you can do it with the following command:

    npm install --global office-addin-https-reverse-proxy
    

    To reroute run the following in another command prompt:

    office-addin-https-reverse-proxy --url http://localhost:3000
    
  8. Follow the steps in Run the sample, but upload the manifest-localhost.xml file for step 6.

Copyright (c) 2021 Microsoft Corporation. All rights reserved.

This project has adopted the Microsoft Open Source Code of Conduct. For more information, see the Code of Conduct FAQ or contact opencode@microsoft.com with any additional questions or comments.