Skip to content

Instantly share code, notes, and snippets.

@Helmoes

Helmoes/Add rows and columns.EXCEL.yaml

Created October 6, 2023 13:37
Show Gist options
  • Save Helmoes/3130a67a9fce76b9bfd9969583390f71 to your computer and use it in GitHub Desktop.
Save Helmoes/3130a67a9fce76b9bfd9969583390f71 to your computer and use it in GitHub Desktop.
Download ZIP
Adds rows and columns to a table.
Raw
Add rows and columns.EXCEL.yaml
name: Add rows and columns
description: Adds rows and columns to a table.
host: EXCEL
api_set: {}
script:
content: |
$("#setup").click(() => tryCatch(setup));
$("#add-row").click(() => tryCatch(addRow));
$("#add-column").click(() => tryCatch(addColumn));
$("#add-calculated-column").click(() => tryCatch(addCalculatedColumn));
async function addRow() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const expensesTable = sheet.tables.getItem("ProjectTracker");
expensesTable.rows.add(null, [
["1/16/2017", "THE PHONE COMPANY", "Communications", "$120"],
["1/20/2017", "NORTHWIND ELECTRIC CARS", "Transportation", "$142"],
["1/20/2017", "BEST FOR YOU ORGANICS COMPANY", "Groceries", "$27"]
]);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
}
async function addColumn() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const expensesTable = sheet.tables.getItem("ExpensesTable");
expensesTable.columns.add(null, [
["Deductable?"],
["Yes"],
["Yes"],
["No"],
["No"],
["Yes"],
["Yes"],
["No"],
["Yes"],
["Yes"],
["No"]
]);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
}
async function addCalculatedColumn() {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getItem("Sample");
const expensesTable = sheet.tables.getItem("ExpensesTable");
const weekendFormula =
'=IF(OR((TEXT([@DATE], "dddd") = "Saturday"), (TEXT([@DATE], "dddd") = "Sunday")), "Weekend", "Weekday")';
expensesTable.columns.add(null, [
["Type of the Day"],
[weekendFormula],
[weekendFormula],
[weekendFormula],
[weekendFormula],
[weekendFormula],
[weekendFormula],
[weekendFormula],
[weekendFormula],
[weekendFormula],
[weekendFormula]
]);
sheet.getUsedRange().format.autofitColumns();
sheet.getUsedRange().format.autofitRows();
await context.sync();
});
}
/** Create a new table with sample data */
async function setup() {
await Excel.run(async (context) => {
context.workbook.worksheets.getItemOrNullObject("Sample").delete();
const sheet = context.workbook.worksheets.add("Sample");
const expensesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
expensesTable.name = "ExpensesTable";
expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];
expensesTable.rows.add(null /*add at the end*/, [
["1/1/2017", "The Phone Company", "Communications", "$120"],
["1/2/2017", "Northwind Electric Cars", "Transportation", "$142"],
["1/5/2017", "Best For You Organics Company", "Groceries", "$27"],
["1/10/2017", "Coho Vineyard", "Restaurant", "$33"],
["1/11/2017", "Bellows College", "Education", "$350"],
["1/15/2017", "Trey Research", "Other", "$135"],
["1/15/2017", "Best For You Organics Company", "Groceries", "$97"]
]);
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 add columns and rows to a table.</p>
</section>
<section class="setup ms-font-m">
<h3>Set up</h3>
<button id="setup" class="ms-Button">
<span class="ms-Button-label">Create table</span>
</button>
</section>
<section class="samples ms-font-m">
<h3>Try it out</h3>
<p>Press the following buttons in order, so rows and columns of appropriate sizes are added.</p>
<button id="add-row" class="ms-Button">
<span class="ms-Button-label">Add rows</span>
</button><p>
<button id="add-column" class="ms-Button">
<span class="ms-Button-label">Add a column</span>
</button><p>
<button id="add-calculated-column" class="ms-Button">
<span class="ms-Button-label">Add a calculated column</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
[email protected]/dist/css/fabric.min.css
[email protected]/dist/css/fabric.components.min.css
[email protected]/client/core.min.js
@types/core-js
[email protected]
@types/[email protected]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment