Inserts worksheets from another workbook into the current workbook.

Insert external worksheets (11).EXCEL.yaml

name: Insert external worksheets (11)
description: Inserts worksheets from another workbook into the current workbook.
host: EXCEL
api_set: {}
script:
  content: |
    $("#file").change(getBase64);
    $("#insert-sheets").click(() => tryCatch(insertSheets));

    let externalWorkbook;

    async function getBase64() {
      // Retrieve the file and set up an HTML FileReader element.
      const myFile = <HTMLInputElement>document.getElementById("file");
      const reader = new FileReader();

      reader.onload = (event) => {
        // Remove the metadata before the base64-encoded string.
        const startIndex = reader.result.toString().indexOf("base64,");
        externalWorkbook = reader.result.toString().substr(startIndex + 7);
      };

      // Read the file as a data URL so that we can parse the base64-encoded string.
      reader.readAsDataURL(myFile.files[0]);
    }

    async function insertSheets() {
      await Excel.run(async (context) => {
        // Retrieve the source workbook.
        const workbook = context.workbook;

        // Set up the insert options.
        const options = {
          sheetNamesToInsert: [], // Insert all the worksheets 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 new worksheets.
        var sheets = workbook.insertWorksheetsFromBase64(externalWorkbook, options);
        await context.sync();
        //var worksheet = workbook.worksheets.getItem(sheets.value[0]);
        //var ranges = worksheet.getUsedRange();
        //ranges.load();
        //await context.sync();
        //console.log(ranges.formulas); // pleee
        //ranges.getCell(0, 0).formulas = [["=SUM(Table2)"]];
        //await context.sync();
      });
    }

    /** Default helper for invoking an action and handling errors. */
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: |-
    <section class="ms-font-m">
        <p>This sample shows how to copy the worksheets from an existing workbook into the current workbook.</p>
    </section>

    <section class="samples ms-font-m">
        <h3>Try it out</h3>
        <p>Select an Excel workbook to copy its worksheets into the current workbook.</p>
        <form>
            <input type="file" id="file" />
        </form>
        <br>
        <p>Insert the worksheets from the selected workbook.</p>
        <button id="insert-sheets" class="ms-Button">
            <span class="ms-Button-label">Insert sheets</span>
        </button>
    </section>
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }

    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1