Office Add-Ins: Working with Tables in Word. Part 3: Two methods for getting existing tables

In this article I will demonstrate a method for getting to a Table using the prescribed methods in the API context.body.

Background

In previous articles we jhave seen how to create a table from scratch using the API or using OOXML. In this article we will look at how to reference an existing table and get a handle on it. We are going to look at two methods, one direct and one indirect, both have advantages and disadvantages.

Getting a table via the API directly

Within the the API model you can access all tables through the context.document.body.tables

The Methods (at leave in v1.3) are rather limiting and you cannot get a Table directly by name or id.

        const tableCollection = context.document.body.tables;

and from there you can reference each table individually through the index.

            theTable = tableCollection.items[i]

What this does not however facilitate is getting the table you want, directly. Let’s say I want to get the Insects table from the image below – how do I get that?

Well if you KNOW it is the nth table in the document then you are fine – but the chances are you don’t or at best can’t be sure.

Testing each table

What you can do is cycle through all the tables in the collection and test the first cell. I have specifically created these tables in such a manner that the first cell in the table is testable. Cycling through all the tables allows me to do a simple test of the first cell and if it is Insects then I know I am in the right table.

In this example we get all tables and then once we have the Insects table I insert a row:

async function runAllTables() {
    await Word.run(async (context) => {

        const tableCollection = context.document.body.tables;
        // Queue a commmand to load the results.
        context.load(tableCollection);
        await context.sync()
        //cycle through the tbale collection and test the first cell of each table looking for insects
        for (var i = 0; i < tableCollection.items.length; i++) {
            var theTable = null;
            theTable = tableCollection.items[i];
            var cell1 = theTable.values[0][0];
            if (cell1 == "Insects") {
                //once found, load the table in memory and add a row
                context.load(theTable, '');
                await context.sync();
                let numRows = theTable.rowCount.toString()
                theTable.addRows("End", 1, [[numRows, "Lightning Bug"]])
            }
        }
    });
}

As the number of tables in the document increase this gets slower and slower (understandably). There is also a limitation on the fact that you might not be able to identify the Table with a value in the first cell as easily (depends on requirements). As you can see from this example in the console.log I added the length of time to complete the addRow and it is 77ms on three tables. If I copy and past the first two tables and make a lot more of them you can see the speed gets slower (124ms) which might not seem like a lot, but in a large document with lots of large tables it can become significant to the end user.

Searching for a word in the document, and then getting the table parent

If you are able to identify a string in your table, you can search for that string and then get the parentTable. That method for finding your table is much more efficient and faster than getting all tables in a collection as per the previous method.

In this example we search for Insects, get the parentTable and then simply insert a row with a new value

async function runSearch() {
    await Word.run(async (context) => {
        const body = context.document.body 
        const insectRangeCollection = body.search("Insects");
        // Queue a commmand to load the results.
        context.load(insectRangeCollection);
        await context.sync()
        //get the parent table from the search result range
        const table1 = insectRangeCollection.getFirst().parentTable
        context.load(table1, '');
        await context.sync()
        let numRows2 = table1.rowCount.toString()
        table1.addRows("End", 1, [[numRows2, "Butterfly"]])
    });
}

As with the previous example – if I copy and paste the first two tables and make a lot more of them, you will see that this search and add row process remains approx the same speed as before.

 

Conclusion

While neither of these methods are ideal, if you know what is possible before you start to create your solution you can work in your options. Not having an obvious getTableById method is somewhat limiting.

Advertisements

Office Add-Ins: Working with Tables in Word. Part 2: Creation from OOXML

In this long overdue article I will discuss how to create a custom table, get the OOXML from it, clean it and then be able to insert the table anywhere into a Word document.

Background

In the previous article we looked at how to create a table from  scratch using range.insert table which is nice but limiting in that the formatting of the created table leaves a lot to be desired. It would be much easier to create the table we want first, get the OOXML and then be able to re-insert it pre-formmated so to speak.

Getting the OOXML of a table

One of the examples posted in the Add-in API documention uses the range.getOOXML to show how to extract the OOXML from a highlighted range in a word document. Using the Add-in Script Lab we can easily simulate this in a word document. I took the basic API call example and inserted the code from the API documentation.

 

  1. I created a custom table with hashed borders (formatting) and highlighted it
  2. Click the run code button to execute the sample code
  3. The OOXML was generated in the console below

Inserting OOXML

Using this process in reverse you can then copy the OOXML and using the range.insertOOXML method you can click anywhere in a word document and click a button to insert the preformatted table.

 

Conclusion

While we can format tables manually with code – as we get more complicated with out tables this may not be an option. Inserting as OOXML will guarantee the formatting we want and then allow us to manipulate as a table as we will see next

Create custom functions in Excel

As of MS Build 2018 custom functions in Excel are available for all developers to use and implement.

https://dev.office.com/blogs/azure-machine-learning-javascript-custom-functions-and-power-bi-custom-visuals-further-expand-developers-capabilities-with-excel

For more information on how to create a custom function in Excel using the JavaScript Add-In model check out this link.

https://aka.ms/customfunctions

“Custom functions (similar to user-defined functions, or UDFs), enable developers to add any JavaScript function to Excel using an add-in. Users can then access custom functions like any other native function in Excel (such as =SUM()).”

This is actually a huge deal because one of the limitations of the Add-In model in the past is this ability to create a custom function without having to add it to the tool bar as an icon which has to be clicked. With this release it means that develoipers can create their own functions in excel in an analogous manner to VBA. The simple example below adds the two numbers and for no good reason adds 42 as well. But you get the idea.

custom functions

 

 

 

Slides from MS Ignite Office Education day – Office Add-Ins Script Lab

Last month I was invited by the Office development team to present during the Office Education day at MS Ignite.

This presentation was given as part of the Office education day September 24th 2017. The presentation focused on Office Add-Ins and specifically how users could use the Script Lab Add-In to be able to get started with Office Add-Ins.

There are examples in the presentation of some of the Add-In samples available in the Script Labs and then a challenge

 

 

Office Add-Ins: Working with Tables in Word. Part 1: Creation

In this article I will show how the Word JavaScript API can be utilized to add tables to your word document using an Office Add-In. This will be a multi part blog post as there are a lot of nuances and interesting ways in which you can play with tables in Word.

Introduction

In previous articles I have written about how to interact with a Word document to search and replace and even save the word document to Salesforce. Going back to a more basic level we are going to look at how to build tables in word.

We will be using the new Script Lab  which is a new Playground Add-In which can be used for development and general tinkering with Add-Ins. This and other articles on the topic are for demonstration purposes and are not hardened for production use.

The reference

For more information on Tables and how what methods/properties are available check out the documentation page – Table Object (JavaScript API for Word)

Creating a table 

At the most basic level a table is created by instantiating the table object and adding values to it.

insertTable(rowCount: number, columnCount: number, insertLocation: string, values: string[][])

This can be done from a number of different parents:

  • The body
  • A range
  • A contentControl
  • A paragraph

The method requires the following:

  • rowCount – a number
  • columnCount – a number
  • insertLocation – Depends on parent – either (body: Start/End/Replace) or (range: Before/After)
  • values: 2 dimentional Array – [[“This is”, “a table”], [“this is”, “a new row”]]

Using these parameters we can create a table from within the Script lab using the following code:

$("#run").click(run);

async function run() {
    try {
        await Word.run(async (context) => {

            var body = context.document.body;
            var range = context.document.getSelection();
            var myArray = [["a", "b"], ["c", "d"]];
            var table = range.insertTable(2, 2, "Before", myArray);            

            // Synchronize the document state by executing the queued commands,
            // and return a promise to indicate task completion.
                await context.sync().then(function () {
                    console.log('Table added before the start of the range.');
                });;
        });
    }
    catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
    }
}

 

 

Conclusion

Using the Script Lab we have seen how we can easily insert a table into a Word document using the Office Add-In API. In future articles we will look at looking for the table we want to modify and then manipulating tables.

 

 

Office Add-Ins – JavaScript control over the Content Control lock in a Word document

In this article I will show how easy it is to programmatically lock and release the lock on a content control in a word document. This is very helpful when you are populating regions of a document but do not want users to mess with the format of the contents.

Introduction

In the Word 1.3 release of the office.js model, Microsoft release the new “cannotEdit” property of a content control. This is a get and settable property. More information on the properties available are found here in the documentation

https://github.com/OfficeDev/office-js-docs/blob/WordJs_1.3_Openspec/reference/word/contentcontrol.md

Unlocking a content control for editing

Here is my locked content control called “Checklist”. I am going to use the code to get it by Tagname and then unlock it.

In your JavaScript code when you are about to update the control you need to execute the following as a minimum. It seems like a lot of code but it is due to the Promised based architecture used for the Office Add-In APIs.

    Word.run(function (context) {

        var contentControlsWithTag = context.document.contentControls.getByTag('Checklist');
        // Queue a command to load the tag property for all of content controls.
        context.load(contentControlsWithTag, 'tag');

        // Synchronize the document state by executing the queued commands,
        // and return a promise to indicate task completion.
        return context.sync().then(function () {
            if (contentControlsWithTag.items.length === 0) {
                console.log('No content control found.');
            }
            else {
                return context.sync()
                    .then(function () {
                        //the contentControlsWithTag is always returned as an array of items
                        contentControlsWithTag.items[0].cannotEdit = false;
                        contentControlsWithTag.items[0].insertHtml("<b>Hello World</b>", 'Replace');
                    });
            }
        });
    })
    .catch(function (error) {
        console.log('Error: ' + JSON.stringify(error));
        if (error instanceof OfficeExtension.Error) {
            console.log('Debug info: ' + JSON.stringify(error.debugInfo));
        }
    });    

Once you have unlocked the control your code can be inserted and the control is editable. In a real application you just have to make sure you lock averything again with

   contentControlsWithTag.items[0].cannotEdit = false;
   contentControlsWithTag.items[0].insertHtml("<b>Hello World</b>", 'Replace');
   contentControlsWithTag.items[0].cannotEdit = true

Conclusion

Nice, simple to use locking control. Yes the users cant unlock this manually and mess with the document, but if they are going there, then it is their own fault. This way no changes can be made “by mistake”.

 

Azure Machine Learning Studio Office Add-In

I am currently in the process of learning more about Azure Machine Learning Studio . Within there you can create and train predictive models. As I am going through some of the examples I came across a great example of an Office Add-In.

I am creating a simple weather predicting example based on data I downloaded from an API service. More on that later. From my model I created a sample Web Service with the click of a button. This exposes an End point for me to send data to be “predicted on”.

a2

When you create a Web Service from your trained model you can access a test screen through the application.

a1

 

From there I spotted a link to an Excel App and I figure I would see what was going on there. Turns out it is an Office Add-In… !!

When the Excel sheet opened I was presented with the option to test the web service

a3

Once you load your test data into the sheet you can then use the Add-In to select the data to test – and designate where the answer will be added back to the sheet.

a4

And you push Predict !!!

Looking at the application using the F12 tool we can see the hosted Office Add-In location and also see the prediction in action.

a7

a5

The web service is called and the response is a JSON string with all the answers and the scored confidence in the answer.

The Add-In then inserts those answers back into the Excel spreadsheet for you

a6

As you can see, my predicting needs some work but the Add-In and sample web service worked like a charm !!!

Poking around the site I can see it is built using Knockout.js and some jQuery – very cool 🙂

a8

Once I have properly figured out how this all Machine Learning lark works I am going to create some blog posts on the predictive side of this – but I wanted to share how cool this out of the box for free Add-In is

GREAT USE CASE !!