Created
February 2, 2021 19:20
-
-
Save p15martin/21f0dcddb3370df5d7107a4c5745d2d1 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: money-add-row | |
description: Registers event handlers that run when a table is changed or selected. | |
host: EXCEL | |
api_set: {} | |
script: | |
content: "$(\"#setup\").click(() => tryCatch(setup));\n$(\"#register-on-changed-handler\").click(() => tryCatch(registerOnChangedHandler));\n$(\"#change-data\").click(() => tryCatch(changeData));\n\nasync function registerOnChangedHandler() {\n await Excel.run(async (context) => {\n let table = context.workbook.tables.getItemAt(0);\n table.onChanged.add(onChange);\n\n await context.sync();\n console.log(\"A handler has been registered for the onChanged event\");\n });\n}\n\nasync function changeData() {\n await Excel.run(async (context) => {\n let sheet = context.workbook.worksheets.getItem(\"Sample\");\n let range = sheet.getRange(\"B2\");\n range.values = [[\"test\"]];\n range.format.autofitColumns();\n\n await context.sync();\n console.log(\"B2 value has been changed.\");\n });\n\n await Excel.run(async (context) => {\n let sheet = context.workbook.worksheets.getItem(\"Sample\");\n let range = sheet.getRange(\"B3\");\n range.values = [[\"Expense\"]];\n range.format.autofitColumns();\n\n await context.sync();\n console.log(\"B3 value has been changed.\");\n });\n}\n\nasync function onChange(event) {\n await Excel.run(async (context) => {\n console.log(\"onChange!\")\n\n const sheet = context.workbook.worksheets.getItemOrNullObject(\"Sample\")\n\n await context.sync()\n\n if (sheet.isNullObject) {\n console.log(\"sheet not found \U0001F62D\")\n return\n }\n \n console.log(\"got sheet!\")\n\n const table = sheet.tables.getItemOrNullObject(\"SalesTable\")\n\n await context.sync()\n\n if (table.isNullObject) {\n console.log(\"table not found \U0001F62D\")\n return\n }\n\n console.log(\"got table!\")\n\n const range = event.getRangeOrNullObject(context)\n\n const tableRange = table.getDataBodyRange()\n tableRange.load('rowIndex')\n range.load('rowIndex')\n\n await context.sync()\n\n const tableRowIndex = tableRange.rowIndex\n const rangeRowIndex = range.rowIndex\n\n const index = rangeRowIndex - tableRowIndex\n\n console.log(`row index: ${index}`)\n\n range.load('rowCount')\n table.load('rows')\n\n await context.sync()\n\n const rowCount = range.rowCount\n const allRows = table.rows\n\n const rows: Excel.TableRow[] = []\n\n for (let i = index; i < index + rowCount; i++) {\n rows.push(allRows.getItemAt(i))\n }\n\n console.log(`row count: ${rows.length}`)\n\n for (let i = 0; i < rows.length; i++) {\n console.log(`row: ${i}`)\n\n const row = rows[i]\n row.load('values')\n await context.sync()\n\n console.log(`row ${i} values: ${row.values}`)\n }\n\n // let changedCell = event.getRange(context);\n // changedCell.load(\"values\");\n // await context.sync();\n // let value = changedCell.values;\n // console.log(\n // \"Handler for table onChanged event has been triggered. The request address:\" +\n // event.address +\n // \",data: \" +\n // value[0][0]\n // );\n });\n}\n\nasync function setup() {\n await Excel.run(async (context) => {\n context.workbook.worksheets.getItemOrNullObject(\"Sample\").delete();\n const sheet = context.workbook.worksheets.add(\"Sample\");\n\n let salesTable = sheet.tables.add(\"A1:B1\", true);\n salesTable.name = \"SalesTable\";\n\n salesTable.getHeaderRowRange().values = [[\"Category Name\", \"Category Type\"]];\n\n salesTable.rows.add(null, [\n [\"foo\", \"Expense\"],\n [\"bar\", \"Transfer\"],\n [\"baz\", \"Transfer\"],\n [\"qux\", \"Expense\"],\n [\"xyz\", \"Transfer\"]\n ]);\n\n sheet.getUsedRange().format.autofitColumns();\n sheet.getUsedRange().format.autofitRows();\n\n sheet.activate();\n await context.sync();\n });\n}\n\n/** Default helper for invoking an action and handling errors. */\nasync function tryCatch(callback) {\n try {\n await callback();\n } catch (error) {\n // Note: In a production add-in, you'd want to notify the user through your add-in's UI.\n console.error(error);\n }\n}\n" | |
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> | |
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