• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
Worf

TypeScript objects and Excel tables

Excel Version
  1. 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:

  • 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.
On the Excel side, some interesting stuff is:

  • The table contents are written without a loop.
  • A slicer is added programmatically.
  • A formula performs the same sum calculation done with TS.
Below you can find the resulting table and the TypeScript source code. Since I could not find TS tags to format the code and considering that TS is typed JavaScript, that is the tag I used.

TS table.png


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);   
  }
  • Like
Reactions: PW327
Author
Worf
Views
1,580
First release
Last update

Ratings

0.00 star(s) 0 ratings

More Excel articles from Worf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top