@divorcemate/word-calc

A library of helper functions for calculating data from Word documents.

Works in Word add in side panel apps with an Office JS reference (usually in index.html)

<script type="text/javascript" src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js"></script>

Installation

npm i @divorcemate/word-calc

Simple Usage

import { calc } from '@divorcemate/word-calc';

// async function calculate...

  await calc.initialize();

  let incomeValues = await calc.getTableValues('c_incomeStartMark'); // [123, 22, 0]

  let results = {
    calc_totalIncome: calc.sum$(incomeValues), // '$145.00'
  }

  await calc.populateResults(results);

  return calc.result(); // { success: true } or { success: false, errors: ['Could not find c_incomeStartMark'] }

Calculator

Properties

bookmarks

Populated on initialize

Array of bookmarks names in the document.

calc.bookmarks
// ['PartAIncome', 'PartBExpenses']

checkboxValues

Populated on initialize

Object containing all checkboxes (tag value) in document (that start with 'c_') and their values.

eg Document contains checked checkbox with tag c_myVal and unchecked checkbox with tag c_myVal2

calc.checkboxValues
// {
//   c_myVal: true,
//   c_myVal2: false,
// }

errors

Populated by the calculation routine as/if it encounters errors

An array of string error message encountered during the calculaton routine. Check after running calculation to determine whether to display error message to user.

Methods

bookmarkExists

Does bookmark exist in the document

Shorthand function for calc.bookmarks.includes(bookmarkName)

calc.bookmarkExists('PartAIncome')
// true
calc.bookmarkExists('ScheduleA')
// false

checkForBrackets

Looks for a negative number indicated by brackets. If the value argument is sandwiched between () brackets, it will remove brackets and prepend - (minus sign).

 calc.checkForBrackets('(45)')
 // -45
 calc.checkForBrackets('12')
 // 12

errorLog

Adds an error message (string) to array of errors

calc.errorLog('Totals table is missing!');

fmt

Formats a number value to 2 decimal places (currency wothout the $). Adds brackets for negative numbers.

calc.fmt(12) // 12.00
calc.fmt(-5) // (5.00)

fmt$

Formats a number value to dollar currency value $x.xx. Adds brackets for negative numbers.

calc.fmt$(12) // $12.00
calc.fmt(-5) // ($5.00)

getBookmarkValues

Takes an array of bookmark names, an optional options argument and returns an object with name/value pairs.

Bookmark must be in a table cell and the value of the text in the cell is returned.

Value is retrieved/formatted in accordance with the ICellValueOptions specified as the secod argument.

// in doc: bmName1 cell: '12', bmName2 cell: 'TBD'

calc.getBookmarkValues(['bmName1', 'bmName2']);
// returns { bmName1: 12, bmName2: 0 }

calc.getBookmarkValues(['bmName1', 'bmName2'], { format$: true, alignCell: true });

// returns { bmName1: 12, bmName2: 0 }
// in doc bmName1 cell now has '$12.00' and is right aligned
// bmName2 cell has TBD and is left aligned 

calc.getBookmarkValues(['bmName1', 'bmName2'], { rawValuesOnly: true });
// returns { bmName1: '12', bmName2: 'TBD' }

getTableValues

Gets the values of an expandable table. Takes the name of the start bookmark and an optional ITableGetOptions object

Returns an array of numbers for each column.

By default the fetch starts at the first row after the start bookmark and grabs rows until the first row before the end bookmark.

|{myTableStart} |          |
| item a desc   |       22 |
| item b desc   |      325 |
| item c desc   |      TBD |
| {myTableEnd}  |  [$0.00] |

let values = calc.getTableValues('myTableStart') // [22, 325, 0]
let ttl = calc.sum$(values);
calc.populateResults( { myTableTotal: ttl } );

|{myTableStart} |          |
| item a desc   |   $22.00 |
| item b desc   |  $325.00 |
| item c desc   | TBD      |  // cell is aligned left bc not a number
| {myTableEnd}  |[$347.00] |

initialize

Call at the start of your calculate routine. Sets bookmarks, checkboxValues and resets errors;

calc.initialize(); // void - no return value

isNum

Takes a string argument and returns a boolean indicating whether the value is a number. Ignores $ and ,.

calc.isNum('23.0') // true
calc.isNum('$2,123') // true
calc.isNum('~23') // false

isTableValid

Takes and array of arrays and returns whether each row has the same number of columns (ie ensure no merged cells)

calc.isTableValid([[1, 4, 5], [54, 22, 0], [0, 0, 99]]) // true
calc.isTableValid([[1, 4, 5], [54, 0], [0, 0, 99]]) // false

populateResults

Takes an object with name vaue pairs, and populates content controls in the document with the value based on tag name.

calc.populateResults({
  calc_totalA: '$23.00',
  calc_totalB: '$44.00',
  calc_grandTotal: '$67.00',
})

result

Call after your calculation routine is done to see how it went. Returns an object with a success boolean and an array of errors if applicable.

calc.result();
// { success: false, errors: ['Schedule A table not found'] }

sum

Takes an array of numbers and returns the sum.

calc.sum([2, 3, 4]) // 9

sum$

Takes an array of numbers and returns the sum as a formatted currency string.

calc.sum([2, 3, 4]) // $9.00

sumTable

Calls getTableValues and sums the result.

Takes startTableBookmark and ITableGetOptions as auruments.

calc.sumTable('myTableStart') // 67

sumTable$

Calls getTableValues and sums the result, returns as $ formatted string.

Takes startTableBookmark and ITableGetOptions as auruments.

calc.sumTable$('myTableStart') // '$67.00'

setTableValues

await calc.setTableValues(startTableBookmark: string, options: ITableSetOptions): void

Takes a startTableBookmark and an array of values and populates a column.

Also takes an optional ITableSetOptions argument.

val(v: string): number

let s = calc.val('($4.00)') // -4
let s = calc.val('$1,234.00') // 1234
let s = calc.val('TBD') // 0

Interfaces

ICellValueOptions

interface ICellValueOptions {
  alignCell?: boolean;
  format$?: boolean;
  readRawValuesOnly?: boolean;
  convertTo?: string;   // weekly / monthly / annual
}

ITableGetOptions

interface ITableGetOptions {
  endMark?: string,
  startOffset?: number,
  endOffset?: number,
  numCols?: number,
  startColIdx?: number,
  alignCells?: boolean,
  format$?: boolean,
  readRawValuesOnly?: boolean,
  skipMergedCellCheck?: boolean,
  convertTo?: string, // weekly / monthly / annual
}

ITableSetOptions

interface ITableSetOptions {
  endMark?: string,
  startOffset?: number,
  endOffset?: number,
  colIdx?: number,
  format$?: boolean,
}

Transfer

Properties

errors

Populated by the transfer routine as/if it encounters errors

An array of string error message encountered during the transfer routine. Check after running your transfer to determine whether to display error message to user.

Methods

getTableData

async function getTableData(startMark: string, options?: ITableDataOptions): Promise

  await transfer.getTableData(`c_p4${ l }Start`, { startOffset: 2, columns: [0, 1, 4] });

lettersUpTo

let letters = transfer.lettersUpTo('d') // ['a', 'b', 'c', 'd']
let letters = transfer.lettersUpTo('C') // ['A', 'B', 'C']

setTableData

Takes a start bookmark name and array of arrays (table data). Adds rows to destination table and injects data.

await transfer.setTableData('c_Table5Start', data);

Interfaces

ITableDataOptions

interface ITableDataOptions {
  endMark?: string,
  startOffset?: number,
  endOffset?: number,
  columns?: Array<number>, // only for get
}