Created
November 18, 2022 17:38
-
-
Save jakobpn/f4696048bd749fcca316fd999a0fc6bc to your computer and use it in GitHub Desktop.
Registers event handlers that run when a worksheet is added, activated, or deactivated, or when the settings of a workbook are changed.
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: Workbook and worksheet collection events | |
description: >- | |
Registers event handlers that run when a worksheet is added, activated, or | |
deactivated, or when the settings of a workbook are changed. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: > | |
$("#register-on-add-handler").click(() => tryCatch(registerOnAddHandler)); | |
$("#add-worksheet").click(() => tryCatch(addWorksheet)); | |
$("#register-on-activate-handler").click(() => | |
tryCatch(registerOnActivateHandler)); | |
$("#register-on-deactivate-handler").click(() => | |
tryCatch(registerOnDeactivateHandler)); | |
$("#delete-worksheet").click(() => tryCatch(deleteWorksheet)); | |
$("#register-on-namechanged-handler").click(() => | |
tryCatch(registerOnNameChangedHandler)); | |
$("#create-setting").click(() => tryCatch(createSetting)); | |
$("#change-setting").click(() => tryCatch(changeSetting)); | |
$("#register-settings-changed-handler").click(() => | |
tryCatch(registerSettingsChangedHandler)); | |
async function registerOnAddHandler() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets; | |
sheet.onAdded.add(onWorksheetAdd); | |
await context.sync(); | |
console.log("A handler has been registered for the OnAdded event."); | |
}); | |
} | |
async function addWorksheet() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.add(); | |
sheet.load("name, position"); | |
await context.sync(); | |
console.log('Added worksheet named "${sheet.name}" in position ${sheet.position}'); | |
}); | |
} | |
async function onWorksheetAdd(event) { | |
await Excel.run(async (context) => { | |
console.log( | |
"Handler for worksheet onAdded event has been triggered. Newly added worksheet Id : " + event.worksheetId | |
); | |
}); | |
} | |
async function registerOnActivateHandler() { | |
await Excel.run(async (context) => { | |
let sheets = context.workbook.worksheets; | |
sheets.onActivated.add(onActivate); | |
await context.sync(); | |
console.log("A handler has been registered for the OnActivate event."); | |
}); | |
} | |
async function onActivate(args) { | |
await Excel.run(async (context) => { | |
console.log("The activated worksheet Id : " + args.worksheetId); | |
}); | |
} | |
async function registerOnDeactivateHandler() { | |
await Excel.run(async (context) => { | |
let sheets = context.workbook.worksheets; | |
sheets.onDeactivated.add(onDeactivate); | |
await context.sync(); | |
console.log("A handler has been registered for the OnDeactivate event."); | |
}); | |
} | |
async function onDeactivate(args) { | |
await Excel.run(async (context) => { | |
console.log("The deactivated worksheet Id : " + args.worksheetId); | |
}); | |
} | |
async function deleteWorksheet() { | |
await Excel.run(async (context) => { | |
// Deleting the current worksheet triggers the deactivate event and | |
// the activate event for the preceding worksheet. | |
let sheets = context.workbook.worksheets; | |
sheets.load("items/name"); | |
await context.sync(); | |
if (sheets.items.length > 1) { | |
let lastSheet = sheets.items[sheets.items.length - 1]; | |
lastSheet.delete(); | |
console.log(`Deleted worksheet named "${lastSheet.name}"`); | |
} else { | |
console.log("Unable to delete worksheet."); | |
} | |
}); | |
} | |
async function registerOnNameChangedHandler() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets; | |
sheet.onNameChanged.add(onWorksheetNameChanged); | |
await context.sync(); | |
console.log("A handler has been registered for the OnNameChanged event."); | |
}); | |
} | |
async function onWorksheetNameChanged(event) { | |
await Excel.run(async (context) => { | |
console.log( | |
"Handler for worksheet onNameChanged event has been triggered. Name changed for worksheet Id : " + event.worksheetId | |
); | |
}); | |
} | |
async function createSetting() { | |
await Excel.run(async (context) => { | |
const settings = context.workbook.settings; | |
settings.add("NeedsReview", true); | |
const needsReview = settings.getItem("NeedsReview"); | |
needsReview.load("value"); | |
await context.sync(); | |
console.log("Setting value is: " + needsReview.value); | |
}); | |
} | |
async function registerSettingsChangedHandler() { | |
await Excel.run(async (context) => { | |
const settings = context.workbook.settings; | |
settings.onSettingsChanged.add(onChangedSetting); | |
await context.sync(); | |
console.log("Settings changed handler registered."); | |
}); | |
} | |
async function onChangedSetting(args: Excel.SettingsChangedEventArgs) { | |
try { | |
await Excel.run(async (context) => { | |
const changedSetting = args.settings.getItem("NeedsReview"); | |
changedSetting.load("value"); | |
// Must sync with the context in the EventArgs object, | |
// not the context that Office passes to Excel.run. | |
await args.settings.context.sync(); | |
console.log("Setting value is: " + changedSetting.value); | |
await context.sync(); | |
}); | |
} catch (error) { | |
console.error(error); | |
} | |
} | |
async function changeSetting() { | |
// The settings.add method is also how you change a | |
// setting. There is no settings.setItem or setting.set | |
// method. For example: | |
// await Excel.run(async (context) => { | |
// const settings = context.workbook.settings; | |
// settings.add("NeedsReview", false); | |
// await context.sync(); | |
// }); | |
// However, a bug prevents the SettingsChanged event | |
// from firing when a setting is changed with the | |
// Excel 1.4 Settings APIs. So we must use the Settings | |
// object from the Office shared APIs: | |
Office.context.document.settings.set("NeedsReview", false); | |
await Office.context.document.settings.saveAsync(); | |
} | |
/** 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\">\n <p>This sample shows how to register and use handlers for when a worksheet is added, activated, or deactivated, or when the settings of a workbook are changed.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n <h3>Try it out</h3>\n\n <p><b>Added</b></p>\n <section class=\"samples ms-font-m\">\n <button id=\"register-on-add-handler\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Register onAdded event handler</span>\n </button>\n <button id=\"add-worksheet\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add worksheet</span>\n </button>\n </section>\n\n <p><b>Activated/Deactivated</b></p>\n <button id=\"register-on-activate-handler\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Register onActivated event handler</span>\n </button>\n <button id=\"register-on-deactivate-handler\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Register onDeactivated event handler</span>\n </button> \n <button id=\"delete-worksheet\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Delete worksheet</span>\n </button>\n\n\t\t<p><b>NameChanged</b></p>\n\t\t<button id=\"register-on-namechanged-handler\" class=\"ms-Button\">\n\t\t <span class=\"ms-Button-label\">Register onNameChanged event handler</span>\n\t\t</button>\n\n <p><b>Settings</b></p>\n <button id=\"create-setting\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create setting</span>\n </button>\n <button id=\"register-settings-changed-handler\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Register settings-changed handler</span>\n </button>\n <button id=\"change-setting\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Change setting</span>\n </button>\n</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