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

NW Chicago JavaScript Meetup: Overview of E2E Testing with Cypress – June 21

On June 21st PSCGroup will be hosting the NorthWest Chicago JavaScript Meetup in our offices from 6-8:30pm

We are very happy to announce a second speaker – Steve Schwarz !

In addition to the Firebase presentation this should make for a really interesting event 🙂

For more information check out the Meetup site

https://www.meetup.com/Northwest-Chicago-JavaScript/events/250983909/

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

PSC Tech Talk: AI in Action – Azure Cognitive Language Services

In this short but interesting presentation Mark Roden and Jalal Benali talked about how they had used Azure Cognitive Language Services (Translation API) to elegantly solve a client’s need to have their intranet available in multiple languages across multiple territories.

Background

One of PSC’s clients has sites in multiple countries and as part of their intranet consolidation they wanted to provide a manner by which their corporate messages could be translated for the various countries/languages. The intranet was hosted on DotNetNuke CMS system and needed to be configurable, flexible and above all easy to use for the business. The business had looked at publicly available services like Google translate, but determined that on a private intranet site, they did not want any corporate information being taken outside of their control.

Azure Cognitive Language Services Translation API was selected as the solution because it is secure, private, easy to use and surprisingly flexible when it comes to converting web based content.

Solution

PSC created a custom module for DotNetNuke (DNN) which allowed content managers to create translated versions of the data at the click of a button. The solution was tied into the out of the box language capabilities of DNN whereby the languages available to the user for translation were those enabled in the DNN core configuration. In this manner if a new site, in a new country was purchased, the administrators need only turn on the new language for it to become available.

Because the Azure translations need to be reviewed for accuracy by a local admin in-country, PSC created the ability to have the new message held back for administrative approval. Once approved it is then published on the appropriate language version of the intranet.

Once the translations were created the global administrators would be able to monitor which ones were then subsequently modified by the local content manager. In this manner content corrections from the original English would not necessarily be translated and overwritten onto the newly corrected translated versions.

Limitations

  • The number of characters which can be translated at any one time is 10,000
  • No automated translation will be perfect, but for normal conversational English we found it to be better than we expected. For technical documentation the results were not as successful.
  • Some languages were better than others on the accuracy when they were reviewed by the testing teams in-country

Pricing

Depending on which service you use and usage, the pricing varies from free to $4.50 per million characters translated (as of May 2018).

Retention of HTML formatting

One surprising, significant, benefit of using the Translation API what that when fed an HTML string, the HTML tags were ignored. This meant that the formatting of the translation returned was identical to the original. While this does increase the size and number of characters translated, this would not approach the limits necessary for this effort.

Conclusion

The solution PSC implemented allowed the client to securely translate sections of their intranet and then manage the translated pages once they were published. Overall our experience with the Translation API was a very good one. We found it very easy to set up and simple the implement.

Office Add-ins community call

“The Office Add-ins community call is our quarterly event for developers to connect in real-time with the folks who are building the Office Add-ins platform and JavaScript APIs.”

The fact that we have a general office Add-in community call now is fantastic and shows how significant the community is to the product group.

The call was blogged and posted here: https://dev.office.com/blogs/office-add-ins-community-call-april-11-2018

The next Office Add-ins community call will be on Wednesday, July 11, 2018. You can download a recurring quarterly calendar invite at https://aka.ms/officeaddinscommunitycall.

 

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