Created
May 1, 2022 00:30
-
-
Save rickdg/207be1c8be55a2a6479c8ef7f440ff4c to your computer and use it in GitHub Desktop.
Registers event handlers that run when a table is changed or selected.
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: Table events | |
description: Registers event handlers that run when a table is changed or selected. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: > | |
$("#setup").click(() => tryCatch(setup)); | |
$("#register-on-changed-handler").click(() => | |
tryCatch(registerOnChangedHandler)); | |
$("#change-data").click(() => tryCatch(changeData)); | |
$("#register-on-selection-changed-handler").click(() => | |
tryCatch(registerOnSelectionChangedHandler)); | |
$("#change-selection").click(() => tryCatch(changeSelection)); | |
async function registerOnChangedHandler() { | |
await Excel.run(async (context) => { | |
let table = context.workbook.tables.getItemAt(0); | |
table.onChanged.add(onChange); | |
await context.sync(); | |
console.log("A handler has been registered for the onChanged event"); | |
}); | |
} | |
async function changeData() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.getItem("Sample"); | |
let range = sheet.getRange("B7"); | |
range.values = [[900]]; | |
range.format.autofitColumns(); | |
await context.sync(); | |
console.log("B7 value has been changed."); | |
}); | |
} | |
async function onChange(event) { | |
await Excel.run(async (context) => { | |
console.log("Handler for table onChanged event has been triggered. Data changed address: " + event.address); | |
}); | |
} | |
async function registerOnSelectionChangedHandler() { | |
await Excel.run(async (context) => { | |
let table = context.workbook.tables.getItemAt(0); | |
table.onSelectionChanged.add(onSelectionChange); | |
await context.sync(); | |
console.log("A handler has been registered for table onSelectionChanged event"); | |
}); | |
} | |
async function changeSelection() { | |
await Excel.run(async (context) => { | |
let sheet = context.workbook.worksheets.getActiveWorksheet(); | |
let range = sheet.getRange("B3:C3"); | |
range.select(); | |
await context.sync(); | |
}); | |
} | |
async function onSelectionChange(args) { | |
await Excel.run(async (context) => { | |
console.log("Handler for table onSelectionChanged event has been triggered. The new selection is: " + args.address); | |
}); | |
} | |
async function setup() { | |
await Excel.run(async (context) => { | |
context.workbook.worksheets.getItemOrNullObject("Sample").delete(); | |
const sheet = context.workbook.worksheets.add("Sample"); | |
let salesTable = sheet.tables.add("A1:E1", true); | |
salesTable.name = "SalesTable"; | |
salesTable.getHeaderRowRange().values = [["Product", "Qtr1", "Qtr2", "Qtr3", "Qtr4"]]; | |
salesTable.rows.add(null, [ | |
["Frames", 5000, 7000, 6544, 4377], | |
["Saddles", 400, 323, 276, 651], | |
["Brake levers", 12000, 8766, 8456, 9812], | |
["Chains", 1550, 1088, 692, 853], | |
["Mirrors", 225, 600, 923, 544], | |
["Spokes", 6005, 7634, 4589, 8765] | |
]); | |
sheet.getUsedRange().format.autofitColumns(); | |
sheet.getUsedRange().format.autofitRows(); | |
sheet.activate(); | |
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 register and use event handlers for table onChanged and onSelectionChanged events.</p> | |
</section> | |
<section class="setup ms-font-m"> | |
<h3>Set up</h3> | |
<button id="setup" class="ms-Button"> | |
<span class="ms-Button-label">Add sample data</span> | |
</button> | |
</section> | |
<section class="samples ms-font-m"> | |
<h3>Try it out</h3> | |
<button id="register-on-changed-handler" class="ms-Button"> | |
<span class="ms-Button-label">Register onChanged event handler</span> | |
</button> | |
</section> | |
<section class="samples ms-font-m"> | |
<p>Changing data in a table triggers the data changed event. You can change the data manually or programmatically.</p> | |
<button id="change-data" class="ms-Button"> | |
<span class="ms-Button-label">Change data</span> | |
</button> | |
</section> | |
<section class="samples ms-font-m"> | |
<button id="register-on-selection-changed-handler" class="ms-Button"> | |
<span class="ms-Button-label">Register onSelectionChanged event handler</span> | |
</button> | |
</section> | |
<section class="samples ms-font-m"> | |
<p>Changing a range selection in a table triggers the table onSelectionChanged event. You can change selection manually or programmatically.</p> | |
<button id="change-selection" class="ms-Button"> | |
<span class="ms-Button-label">Change range selection</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