Created
November 3, 2023 03:08
-
-
Save jipyua/1f1bdd5ac7b8f94066fc8f710b9899fb to your computer and use it in GitHub Desktop.
Inserts worksheets from another workbook into the current workbook.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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. | |
workbook.insertWorksheetsFromBase64(externalWorkbook, options); | |
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment