- Excel Version
- 365
Certain Excel 365 licenses offer Office scripts, which is an implementation of the TypeScript language to automate Excel, just like VBA does.
I am planning a small series of articles focusing on relevant TS features and their application to the Excel object model; this first one will deal with TS objects and Excel tables.
On the TS side, here are some of the topics covered:
I am planning a small series of articles focusing on relevant TS features and their application to the Excel object model; this first one will deal with TS objects and Excel tables.
On the TS side, here are some of the topics covered:
- Two different objects are defined, with the final table type being a union type of those two.
- The style type is a nested type.
- The contents property is a bidimensional array.
- The code sums all the numeric elements that appear in the table.
- The table contents are written without a loop.
- A slicer is added programmatically.
- A formula performs the same sum calculation done with TS.
JavaScript:
function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();
type XLTable = {
kind: "numeric";
nrows?: number; // optional
header: string[];
format: Style;
contents: string[][]
}
type Style = {
tstyle: string[];
hasslicer: boolean
}
type TextTable = {
kind: "text";
region: string
}
type FinalTable = XLTable | TextTable;
let table1: FinalTable;
table1 = {
kind: "numeric",
nrows: 5,
header: ["Table", "created", "with", "TypeScript"],
format: {
tstyle: ["TableStyleMedium6", "TableStyleLight2", "TableStyleDark5"],
hasslicer: true
},
contents: [["40", "Jones", "Salazar", "Mu"],
["60", "95", "Jacobs", "Barnard"],
["80", "last", "row", "Richardson"]]
}
let rng = sheet.getRange("a15");
for (let i = 0; i < table1.header.length; i++) {
let dyn = rng.getOffsetRange(0, i + 1);
dyn.setValue(table1.header[i]);
}
if (table1.kind === "numeric") { table1.format.tstyle[1] }
rng = rng.getOffsetRange(1, 1)
rng.getAbsoluteResizedRange(3, 4).setValue(table1.contents)
let newTable = workbook.addTable(rng.getOffsetRange(0, 1).getSurroundingRegion(), true)
newTable.setPredefinedTableStyle(table1.format.tstyle[2]);
newTable.setShowBandedColumns(true);
if (table1.format.hasslicer) {
const mys: ExcelScript.Slicer = workbook.addSlicer(newTable, newTable.getColumn(table1.header[1]), sheet)
}
let tables = sheet.getTables();// how to get all tables
let tab2 = tables[0]
const tn = tab2.getName();
tab2.setName(tn + "2");
rng.getOffsetRange(6, 5).setFormulaLocal("=sum(" + tab2.getName() + ")")
let rowCount = 0;
tables.forEach((table) => {
rowCount = table.getRangeBetweenHeaderAndTotal().getRowCount();
console.log(table.getName() + " - " + rowCount + " rows " + "- " + table.getId());
});
var ssum = 0;
table1.contents.forEach((row) => {
row.forEach((element) => {
if (isNaN(Number(element))) { // do nothing
}
else { ssum += Number(element); }
});
});
console.log("Sum is "+ssum);
}
