Class: Spreadsheet

Spreadsheet(associated, rules, convert_times_to_datesopt)

Interface to spreadsheet libraries

At the time of writing, this library uses ExcelJS to handle spreadsheets. But we might replace this with a different library in future, or we might add more libraries to handle other spreadsheet formats.

To reduce code complexity, the Spreadsheet class presents a high-level abstraction of spreadsheet functionality, which should remain unchanged no matter which underlying library we use.

The most important part of a spreadsheet is the 2D array of cells in each sheet. Each cell is an object with a value and a style. The value is a number, string, date or null. The style is a string containing the background colour, a comma, and the foreground colour (e.g. #FFFFFF00,#FF00FFFF). Each colour is one of:

  • #AARRGGBB - alpha, red, green, blue; e.g. #FFFFFF00 is yellow
  • iNNN - numeric indexed value; e.g. i1 is index 1
  • tNNN - numeric theme; e.g. t1 is theme 1

Implementations are encouraged to get Unix timestamps with the parse_timestamp() function in this class. That function will attempt to decode various common time-like strings (e.g. noon).

Constructor

new Spreadsheet(associated, rules, convert_times_to_datesopt)

Parameters:
Name Type Attributes Description
associated Object object to synchronise the spreadsheet with
rules SpreadsheetRules rules to convert between objects and spreadsheets
convert_times_to_dates boolean <optional>
 guess the dates associated with times

The spreadsheet rules can include a formats member, specifying how numbers will be formatted in the associated member. Each value can be null (no special formatting), an Excel number format code, or number/time/duration (aliases for common formats).
Source:
Examples
console.log( spreadsheet["sheets"].length ); // number of sheets
console.log( spreadsheet["sheets"][0]["name"] ); // name of the first sheet
console.log( spreadsheet["sheets"][0]["cells"][0][1] ); // value of cell A2
-> {
     "value": new Date(...),
     "style": "#FFFFFF00,#FF000000FF",
   }

Members

"sheets"

Array of sheets
Source:

associated

Object associated with this spreadsheet
Source:

Methods

(static) buffer_to_spreadsheet()

Read data from a buffer (e.g. a file input)
Source:

(static) create_cell(valueopt, styleopt)

Parameters:
Name Type Attributes Description
value * <optional>
 contents of the cell
style string <optional>
 cell formatting
Source:

(static) parse_all_timestamps(sheet, raw_spreadsheetopt)

Calculate timestamps for all cells in a worksheet
Parameters:
Name Type Attributes Description
sheet Array sheet to process
raw_spreadsheet Object <optional>
 raw spreadsheet object from which the value was taken
Source:

(static) parse_csv(contents) → {Object|undefined}

Copy data from the parameter into this.sheets and this.associated
Parameters:
Name Type Description
contents string CSV file to load from
Source:
Returns:
spreadsheet information
Type
Object | undefined

(static) parse_timestamp(value, raw_spreadsheetopt) → {number}

Convert a cell to a Unix timestamp
Parameters:
Name Type Attributes Description
value Object cell or value to analyse
raw_spreadsheet Object <optional>
 raw spreadsheet object from which the value was taken
Source:
Returns:
Unix timestamp (if parseable)
Type
number

load(spreadsheet) → {boolean}

Copy data from the parameter into this.sheets and this.associated
Parameters:
Name Type Description
spreadsheet Object spreadsheet to load from
Source:
Returns:
whether the operation was successful
Type
boolean

serialise() → {Promise}

Generate a spreadsheet based on this.sheets
Source:
Returns:
Type
Promise

synchronise() → {boolean}

Copy data from this.associated to this.sheets
Source:
Returns:
Type
boolean