Created
October 19, 2022 09:07
-
-
Save webartoli/86a19ffa0e683aa1d204eabaaeb09dd1 to your computer and use it in GitHub Desktop.
This snippet is trying to find out a way to identify row changes
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: Identify row univocally | |
description: This snippet is trying to find out a way to identify row changes | |
host: EXCEL | |
api_set: {} | |
script: | |
content: >+ | |
// | |
################################################################################ | |
// #### UTILS and Handlers | |
// | |
################################################################################ | |
async function tryCatch(callback) { | |
try { | |
await callback(); | |
} catch (error) { | |
console.error(error); | |
} | |
} | |
async function onBindingDataChanged(eventArgs: | |
Excel.BindingDataChangedEventArgs) { | |
await Excel.run(async (context) => { | |
var id = eventArgs.binding.id; | |
const binding = context.workbook.bindings.getItem(id); | |
var range = binding.getRange(); | |
range.load("values"); | |
await context.sync(); | |
console.log("Data changed | Binding Id: " + id + " Values: " + range.values); | |
}); | |
} | |
async function onTableChanged(eventArgs: Excel.TableChangedEventArgs) { | |
await Excel.run(async (context) => { | |
const details = eventArgs.details; | |
const address = eventArgs.address; | |
console.log(JSON.stringify(eventArgs)) | |
console.log( | |
`Change at ${address}: was ${details.valueBefore}(${details.valueTypeBefore}),` + | |
` now is ${details.valueAfter}(${details.valueTypeAfter})` | |
); | |
}); | |
} | |
async function onSortChanged(eventArgs: Excel.WorksheetRowSortedEventArgs) { | |
await Excel.run(async (context) => { | |
console.log("Row sorted: " + JSON.stringify(eventArgs)); | |
}); | |
} | |
// | |
################################################################################ | |
// #### DATA | |
// | |
################################################################################ | |
const rows = [ | |
["1", "The Phone Company", "Communications", "$120"], | |
["2", "Northwind Electric Cars", "Transportation", "$142"], | |
["3", "Best For You Organics Company", "Groceries", "$27"], | |
["4", "Coho Vineyard", "Restaurant", "$33"], | |
["5", "Bellows College", "Education", "$350"], | |
["6", "Trey Research", "Other", "$135"], | |
["7", "Best For You Organics Company", "Groceries", "$97"] | |
]; | |
// | |
################################################################################ | |
// #### APPROACH 1 - Range Binding | |
// | |
################################################################################ | |
$("#range-binding").click(() => tryCatch(rangeBinding)); | |
async function rangeBinding() { | |
await Excel.run(async (context) => { | |
// Create table | |
context.workbook.worksheets.getItemOrNullObject("Sample").delete(); | |
const sheet = context.workbook.worksheets.add("Sample"); | |
const table = sheet.tables.add("A1:D1", true); | |
table.name = "ExpensesTable"; | |
table.getHeaderRowRange().values = [["ID", "Merchant", "Category", "Amount"]]; | |
table.rows.add(null, rows); | |
// Set a binding for each row | |
table.rows.load("items"); | |
await context.sync(); | |
let i = 0; | |
for (const element of table.rows.items) { | |
i++; | |
let id = "id-" + i; | |
let range = element.getRange(); | |
range.load("address"); | |
await context.sync(); | |
const binding = context.workbook.bindings.add(range.address, "Range", id); | |
// Set row handler | |
binding.onDataChanged.add(onBindingDataChanged); | |
console.log("Binding Id: " + id + " Values: " + element.values); | |
} | |
// Set table handlers | |
table.onChanged.add(onTableChanged); | |
sheet.onRowSorted.add(onSortChanged); | |
sheet.activate(); | |
await context.sync(); | |
}); | |
} | |
// | |
################################################################################ | |
// #### APPROACH 2 - Table Binding | |
// | |
################################################################################ | |
$("#table-binding").click(() => tryCatch(tableBinding)); | |
async function tableBinding() { | |
await Excel.run(async (context) => { | |
// Create table | |
context.workbook.worksheets.getItemOrNullObject("Sample").delete(); | |
const sheet = context.workbook.worksheets.add("Sample"); | |
const table = sheet.tables.add("A1:D1", true); | |
table.name = "ExpensesTable"; | |
const binding = context.workbook.bindings.add(table.getRange(), "Table", "myTable"); | |
Office.context.document.bindings.getByIdAsync("myTable", async function(result) { | |
const binding = result.value as Office.TableBinding; | |
await binding.addRowsAsync(rows); | |
}); | |
sheet.getUsedRange().format.autofitColumns(); | |
sheet.getUsedRange().format.autofitRows(); | |
sheet.activate(); | |
// Set handlers | |
binding.onDataChanged.add(onBindingDataChanged); | |
table.onChanged.add(onTableChanged); | |
sheet.onRowSorted.add(onSortChanged); | |
await context.sync(); | |
}); | |
} | |
language: typescript | |
template: | |
content: "<section class=\"samples ms-font-m\">\n\t<h2>Identify row</h2>\n\t<p>With this snippet several approach can be tried</p>\n\t<p>Usage: for each try, press a create table button than try one approach.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Approaches</h3>\n\t<button id=\"range-binding\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Approach 1 - range binding</span>\n\t</button>\n\t<button id=\"table-binding\" class=\"ms-Button\">\n\t\t<span class=\"ms-Button-label\">Approach 2 - table binding</span>\n\t</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