excel-insert-file

page_type	urlFragment	products	languages	extensions	description
sample	excel-add-in-insert-external-file	office-excel office	javascript	contentType technologies createdDate samples Add-ins 5/18/2021 10:00:00 AM	This sample shows how to insert a template from an external Excel file and populate it with JSON data.

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.

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 the Microsoft 365 Insider program.

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.

   

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

   

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.
const 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) {
    const 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;
  const address = "B" + startRow + ":F" + (newSalesData.length + startRow - 1);
      
  // Write the sales data to the table in the template.
  const 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.

Questions and feedback

• Did you experience any problems with the sample? Create an issue and we'll help you out.
• We'd love to get your feedback about this sample. Go to our Office samples survey to give feedback and suggest improvements.
• For general questions about developing Office Add-ins, go to Microsoft Q&A using the office-js-dev tag.

Copyright

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.