Source: Spreadsheet.js

  1. /*
  2.  * Copyright 2020-2022 Sleepdiary Developers <sleepdiary@pileofstuff.org>
  3.  *
  4.  * Permission is hereby granted, free of charge, to any person
  5.  * obtaining a copy of this software and associated documentation
  6.  * files (the "Software"), to deal in the Software without
  7.  * restriction, including without limitation the rights to use, copy,
  8.  * modify, merge, publish, distribute, sublicense, and/or sell copies
  9.  * of the Software, and to permit persons to whom the Software is
  10.  * furnished to do so, subject to the following conditions:
  11.  *
  12.  * The above copyright notice and this permission notice shall be
  13.  * included in all copies or substantial portions of the Software.
  14.  *
  15.  * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  16.  * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  17.  * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  18.  * NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS
  19.  * BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN
  20.  * ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
  21.  * CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
  22.  * SOFTWARE.
  23.  */
  25. "use strict";
  28. /**
  29.  * @typedef {{
  30.  *   member: string,
  31.  *   type: (string|undefined),
  32.  *   regexp: (RegExp|undefined)
  33.  * }} SpreadsheetHighLevelCellRule
  34.  *
  35.  * <p>Define a high-level rule for serialising/parsing a spreadsheet cell.</p>
  36.  *
  37.  * <p><tt>key</tt> should be the name of a member of the relevant data
  38.  * structure, <tt>type</tt> should be <tt>number</tt>, <tt>time</tt>,
  39.  * <tt>duration</tt> or <tt>string</tt> (the default).  If
  40.  * <tt>regexp</tt> is passed, the value is expected to match that
  41.  * pattern.</p>
  42.  */
  43. let SpreadsheetHighLevelCellRule;
  45. /**
  46.  * @typedef {{
  47.  *   members: Array<string>,
  48.  *   import: Function,
  49.  *   export: Function
  50.  * }} SpreadsheetLowLevelCellRule
  51.  *
  52.  * <p>Define a low-level rule for serialising/parsing a series of cells.</p>
  53.  *
  54.  * <p><tt>keys</tt> should be a non-empty array of member names that
  55.  * will be created.  <tt>import</tt> and <tt>export</tt> are functions
  56.  * that take three arguments (<tt>array_element</tt>, <tt>row</tt> and
  57.  * <tt>offset</tt>) and return a value that indicates whether the
  58.  * operation was a success.  <tt>import</tt> is expected to copy
  59.  * values from <tt>row</tt> to <tt>array_element</tt>, wheraes
  60.  * <tt>export</tt> copies data back to <tt>row</tt>.
  61.  *
  62.  */
  63. let SpreadsheetLowLevelCellRule;
  65. /**
  66.  * @typedef {{
  67.  *   sheet: string,
  68.  *   member: (string|undefined),
  69.  *   cells: Array<SpreadsheetHighLevelCellRule|SpreadsheetLowLevelCellRule>
  70.  * }} SpreadsheetSheetRule
  71.  *
  72.  * <p>Define the conversion to/from a single sheet in the spreadsheet.</p>
  73.  *
  74.  * <p><tt>sheet</tt> should be the name of the sheet in the spreadsheet.
  75.  * <tt>member</tt> should be the name of the associated member in the data
  76.  * structure (if different from <tt>sheet</tt>).</p>
  77.  */
  78. let SpreadsheetSheetRule;
  80. /**
  81.  * @typedef Array<SpreadsheetSheetRule> SpreadsheetRules
  82.  *
  83.  * <p>Define the conversion to/from a spreadsheet.</p>
  84.  *
  85.  */
  86. let SpreadsheetRules;
  88. const SpreadsheetNumberFormats = {
  89.     "number"  : "General",
  90.     "time"    : "YYYY-MM-DD\" \"HH:MM",
  91.     "duration":  "[h]:mm",
  92. };
  96. /**
  97.  * @class Interface to spreadsheet libraries
  98.  *
  99.  * <p>At the time of writing, this library uses ExcelJS to handle
  100.  * spreadsheets.  But we might replace this with a different library
  101.  * in future, or we might add more libraries to handle other
  102.  * spreadsheet formats.</p>
  103.  *
  104.  * <p>To reduce code complexity, the Spreadsheet class presents a
  105.  * high-level abstraction of spreadsheet functionality, which should
  106.  * remain unchanged no matter which underlying library we use.</p>
  107.  *
  108.  * <p>The most important part of a spreadsheet is the 2D array of
  109.  * cells in each sheet.  Each cell is an object with a <tt>value</tt>
  110.  * and a <tt>style</tt>.  The <tt>value</tt> is a number, string, date
  111.  * or <tt>null</tt>.  The style is a string containing the background
  112.  * colour, a comma, and the foreground colour
  113.  * (e.g. <TT>#FFFFFF00,#FF00FFFF</TT>).  Each colour is one of:
  114.  *
  115.  * <ul>
  116.  * <li> <TT>#AARRGGBB</TT> - alpha, red, green, blue; e.g. <TT>#FFFFFF00</TT> is yellow
  117.  * <li> <tt>iNNN</tt> - numeric indexed value; e.g. <tt>i1</tt> is index 1
  118.  * <li> <tt>tNNN</tt> - numeric theme; e.g. <tt>t1</tt> is theme 1
  119.  * </ul>
  120.  *
  121.  * <p>Implementations are encouraged to get Unix timestamps with the
  122.  * <tt>parse_timestamp()</tt> function in this class.  That function
  123.  * will attempt to decode various common time-like strings
  124.  * (e.g. <tt>noon</tt>).</p>
  125.  *
  126.  * @example
  127.  * console.log( spreadsheet["sheets"].length ); // number of sheets
  128.  * @example
  129.  * console.log( spreadsheet["sheets"][0]["name"] ); // name of the first sheet
  130.  * @example
  131.  * console.log( spreadsheet["sheets"][0]["cells"][0][1] ); // value of cell A2
  132.  * -> {
  133.  *      "value": new Date(...),
  134.  *      "style": "#FFFFFF00,#FF000000FF",
  135.  *    }
  136.  *
  137.  * @unrestricted
  138.  */
  139. class Spreadsheet {
  141.     /**
  142.      * @param {*=} value - contents of the cell
  143.      * @param {string=} style - cell formatting
  144.      */
  145.     static create_cell(value,style) {
  146.         return { "value": value, "style": style || "" };
  147.     }
  149.     /**
  150.      * @param {Object} associated - object to synchronise the spreadsheet with
  151.      * @param {SpreadsheetRules} rules - rules to convert between objects and spreadsheets
  152.      * @param {boolean=} convert_times_to_dates - guess the dates associated with times
  153.      *
  154.      * <p>The spreadsheet rules can include a <tt>formats</tt> member,
  155.      * specifying how numbers will be formatted in the associated
  156.      * member.  Each value can be <tt>null</tt> (no special formatting), an
  157.      * Excel number format code, or <tt>number</tt>/<tt>time</tt>/<tt>duration</tt>
  158.      * (aliases for common formats).</p>
  159.      *
  160.      * @example
  161.      * let spreadsheet = new Spreadsheet(
  162.      *   associated,
  163.      *   [
  164.      *     {
  165.      *       sheet: "...", // name of a sheet in the spreadsheet (e.g. "records")
  166.      *       member: "...", // name of an array in the diary object (default: same as "sheet")
  167.      *       //type: "dictionary", // indicates the member is an object containing key/value pairs
  168.      *       cells: [ // cells in the associated sheet
  169.      *
  170.      *         // most cells can be defined with the high-level format:
  171.      *         {
  172.      *           "member": "...", // required - name of the member (e.g. 'start')
  173.      *           "type": "text", // optional - can also be "number", "time" or "duration"
  174.      *           "regexp": /^[0-9]$/, // optional - values must match this pattern
  175.      *           //"optional": true, // allow this value to be missing
  176.      *         },
  177.      *
  178.      *         // sometimes you might need to use the low-level format:
  179.      *         {
  180.      *           "members": [ "foo", "bar" ], // names of members that will be returned by the read
  181.      *           "formats": [ null , "duration" ], // how numbers are formatted in this member (see above)
  182.      *           "export": (array_element,row,offset) => { // append cells to the current row
  183.      *             row[offset  ] = Spreadsheet.create_cell(array_element["foo"]);
  184.      *             row[offset+1] = Spreadsheet.create_cell(array_element["foo"]+array_element["bar"]);
  185.      *             return false; // indicates this value cannot be serialised
  186.      *           },
  187.      *           "import": (array_element,row,offset) => {
  188.      *             array_element["foo"] =                          row[offset]["value"];
  189.      *             array_element["bar"] = row[offset+1]["value"] - row[offset]["value"];
  190.      *             return false; // indicates this value cannot be parsed
  191.      *           },
  192.      *         },
  193.      *
  194.      *         ...
  195.      *
  196.      *       ]
  197.      *     },
  198.      *
  199.      *     ...
  200.      *
  201.      *   ]
  202.      * );
  203.      */
  204.     constructor(associated,rules,convert_times_to_dates) {
  206.         const debug = false;
  208.         function exporter(cell) {
  210.             const create_cell = Spreadsheet.create_cell;
  211.             const member = cell["member"];
  213.             let ret;
  214.             switch ( cell["type"] ) {
  215.             case "time"    : ret = (elem,row,offset) =>
  216.                 row[offset] = Spreadsheet.create_cell( ( elem[member] === undefined ) ? undefined : new Date( elem[member] ));
  217.                 break;
  218.             case "duration": ret = (elem,row,offset) =>
  219.                 row[offset] = Spreadsheet.create_cell( ( elem[member] === undefined ) ? undefined : elem[member] / (1000*60*60*24) );
  220.                 break;
  221.             default        : ret = (elem,row,offset) =>
  222.                 row[offset] = Spreadsheet.create_cell(elem[member]);
  223.             }
  225.             if ( cell["optional"] ) {
  226.                 const inner = ret;
  227.                 ret = (elem,row,offset) => ( elem[member] === undefined ) || inner(elem,row,offset);
  228.             }
  230.             if ( debug ) { // debugging use only
  231.                 const inner = ret;
  232.                 ret = (elem,row,offset) => {
  233.                     const result = inner(elem,row,offset);
  234.                     console.info(cell,row[offset],elem[member],result);
  235.                     return result;
  236.                 };
  237.             }
  239.             return ret;
  240.         }
  242.         function importer(cell,self) {
  244.             const member = cell["member"];
  246.             let ret;
  247.             switch ( cell["type"] ) {
  248.             case "time"    :
  249.                 ret = (
  250.                     convert_times_to_dates
  251.                     ? (elem,row,offset) => !isNaN( elem[member] = row[offset]. dated_timestamp )
  252.                     : (elem,row,offset) => !isNaN( elem[member] = row[offset].parsed_timestamp )
  253.                 );
  254.                 break;
  255.             case "duration": ret = (elem,row,offset) => !isNaN( elem[member] = row[offset]["value"].getTime() + self["epoch_offset"] ); break;
  256.             case "number"  : ret = (elem,row,offset) => !isNaN( elem[member] = parseFloat(row[offset]["value"]) ); break;
  257.             case "boolean" : ret = (elem,row,offset) => !isNaN( elem[member] = !!row[offset]["value"] ); break;
  258.             default: ret = (elem,row,offset) => { elem[member] = row[offset]["value"]; return true; };
  259.             }
  261.             if ( cell["regexp"] ) {
  262.                 const inner = ret;
  263.                 ret = (elem,row,offset) => cell["regexp"].test(row[offset]["value"]) && inner(elem,row,offset);
  264.             }
  266.             if ( cell["optional"] ) {
  267.                 const inner = ret;
  268.                 ret = (elem,row,offset) => (
  269.                     !row[offset]                  ||
  270.                      row[offset]["value"] == null ||
  271.                     inner(elem,row,offset)        ||
  272.                      row[offset]["value"] == ""
  273.                 );
  274.             }
  276.             if ( debug ) {
  277.                 const inner = ret;
  278.                 ret = (elem,row,offset) => {
  279.                     const result = inner(elem,row,offset);
  280.                     if ( result ) {
  281.                         console.info(result,cell,elem[member]);
  282.                     } else {
  283.                         console.error(result,cell,elem[member],row[offset]?row[offset]["value"]:"(no cell at this offset)");
  284.                     }
  285.                     return result;
  286.                 };
  287.             }
  289.             return ret;
  291.         }
  293.         /**
  294.          * Low-level spreadsheet library object.
  295.          * @type (Object|null)
  296.          * @private
  297.          */
  298.         this.raw = null;
  300.         /**
  301.          * Object associated with this spreadsheet
  302.          */
  303.         this.associated = associated;
  305.         /**
  306.          * Spreadsheet rules
  307.          * @private
  308.          */
  309.         this.rules = rules.map( rule => ({
  311.             "sheet"   : rule["sheet"] || rule["member"],
  312.             "member"  : rule["member"] || rule["sheet"],
  313.             "type"    : rule["type"] || "list",
  314.             "optional": !!rule["optional"],
  316.             // convert high-level definitions to low-level definitions:
  317.             "cells": rule["cells"].map( cell => {
  318.                 if ( cell["members"] ) {
  319.                     cell["formats"] = ( cell["formats"] || [] ).map(
  320.                         format => SpreadsheetNumberFormats[format] || format
  321.                     );
  322.                     while ( cell["formats"].length < cell["members"].length ) {
  323.                         cell["formats"].push("General");
  324.                     }
  325.                     return cell;
  326.                 } else {
  327.                     return {
  328.                         "members": [ cell["member"] ],
  329.                         "regexp": cell["regexp"] || new RegExp(""),
  331.                         "formats": [
  332.                             SpreadsheetNumberFormats[ cell["type"] ] || "General"
  333.                         ],
  335.                         "export": exporter(cell),
  336.                         "import": importer(cell,this),
  337.                     }
  338.                 }
  339.             }),
  341.         }));
  343.         /*
  344.          * Epoch used by dates created in this spreadsheet,
  345.          * in milliseconds relative to the Unix epoch
  346.          */
  347.         this["epoch_offset"] = 0;
  350.         /**
  351.          * Array of sheets
  352.          */
  353.         this["sheets"] = [];
  355.     }
  357.     ["get_sheet"](name,headers,number_formats) {
  359.         const expected = headers.join("\0");
  361.         const sheets = this["sheets"].filter(
  362.             sheet => sheet["cells"][0] && sheet["cells"][0].map( cell => cell["value"] ).join("\0") == expected
  363.         );
  365.         if ( sheets.length ) {
  367.             return [ false, sheets.find( sheet => sheet["name"] == name ) || sheets[0] ];
  369.         } else {
  371.             const ret = {
  372.                 "name": name,
  373.                 "number_formats": number_formats.map( type => type ? SpreadsheetNumberFormats[ type ] || type : "General" ),
  374.                 "cells": [
  375.                     headers.map( header => Spreadsheet.create_cell(header,"#FFEEEEEE,#FFEEEEEE") )
  376.                 ],
  377.             };
  378.             return [ true, ret ];
  380.         }
  382.     }
  384.     /**
  385.      * Convert a cell to a Unix timestamp
  386.      * @param {Object} value - cell or value to analyse
  387.      * @param {Object=} raw_spreadsheet - raw spreadsheet object from which the value was taken
  388.      * @return {number} Unix timestamp (if parseable)
  389.      */
  390.     static parse_timestamp(value,raw_spreadsheet) {
  391.         return DiaryBase.parse_timestamp(
  392.             (value||{}).hasOwnProperty("value") ? value["value"] : value,
  393.             raw_spreadsheet
  394.             ? (
  395.                 ( raw_spreadsheet["properties"] || {} ) ["date1904"]
  396.                 ? 2082844800000
  397.                 : 2209161600000
  398.             )
  399.             : 0
  400.         );
  401.     }
  403.     /**
  404.      * Calculate timestamps for all cells in a worksheet
  405.      * @param {Array} sheet - sheet to process
  406.      * @param {Object=} raw_spreadsheet - raw spreadsheet object from which the value was taken
  407.      */
  408.     static parse_all_timestamps(sheet,raw_spreadsheet) {
  409.         let prev_date = 0;
  410.         sheet.forEach( row => row.forEach( cell => {
  411.             let timestamp = cell.parsed_timestamp = Spreadsheet.parse_timestamp(cell,raw_spreadsheet),
  412.                 dated_timestamp
  413.                 = cell.dated_timestamp
  414.                 = ( isNaN(timestamp) || timestamp < 0 || timestamp > 86400000 )
  415.                 ? timestamp
  416.                 : timestamp + prev_date - (prev_date%86400000)
  417.             ;
  418.             if ( dated_timestamp < prev_date ) {
  419.                 cell.dated_timestamp = dated_timestamp += 86400000;
  420.             }
  421.             prev_date = dated_timestamp;
  422.         }));
  423.     }
  425.     /**
  426.      * Read data from a buffer (e.g. a file input)
  427.      */
  428.     static buffer_to_spreadsheet(buffer) {
  430.         function encode_style(style) {
  431.             if      ( style.hasOwnProperty("argb"   ) ) return '#' + style["argb"];
  432.             else if ( style.hasOwnProperty("indexed") ) return 'i' + style["indexed"];
  433.             else if ( style.hasOwnProperty("theme"  ) ) return 't' + style["theme"];
  434.             return '';
  435.         }
  437.         let spreadsheet;
  438.         try {
  439.             spreadsheet = new self["ExcelJS"]["Workbook"]();
  440.         } catch (e) {
  441.             spreadsheet = new ( require("exceljs")["Workbook"] )();
  442.         }
  444.         return spreadsheet["xlsx"]["load"](buffer).then(
  445.             () => {
  446.                 if ( spreadsheet["_worksheets"].length ) {
  447.                     let sheets = [];
  448.                     spreadsheet["eachSheet"]( (raw_worksheet, sheetId) => {
  449.                         let sheet = { "name": raw_worksheet["name"], "cells": [] };
  450.                         sheets.push(sheet);
  451.                         raw_worksheet["eachRow"]( { "includeEmpty": true }, (raw_row, row_number) => {
  452.                             let row = sheet["cells"][row_number-1] = [];
  453.                             raw_row["eachCell"]({ "includeEmpty": true }, function(cell, col_number) {
  454.                                 let style = "";
  455.                                 if ( cell["style"] && cell["style"]["fill"] ) {
  456.                                     style = (
  457.                                         encode_style(cell["style"]["fill"]["bgColor"]||{}) +
  458.                                         ',' +
  459.                                         encode_style(cell["style"]["fill"]["fgColor"]||{})
  460.                                     );
  461.                                 }
  462.                                 if ( style.length == 1 ) style = "";
  463.                                 // check for floating point errors:
  464.                                 if ( (cell["value"]||{}).getTime ) {
  465.                                     const time = cell["value"].getTime();
  466.                                     if ( ( time % 1000 ) ==  1 || ( time % 1000 ) == -999 ) {
  467.                                         cell["value"] = new Date( time-1 );
  468.                                     }
  469.                                     if ( ( time % 1000 ) == -1 || ( time % 1000 ) ==  999 ) {
  470.                                         cell["value"] = new Date( time+1 );
  471.                                     }
  472.                                 }
  473.                                 row[col_number-1] = Spreadsheet.create_cell(cell["value"],style);
  474.                             })
  475.                         });
  476.                     });
  478.                     sheets.forEach( sheet => Spreadsheet.parse_all_timestamps( sheet["cells"], spreadsheet ) );
  480.                     return {
  481.                         "file_format": "spreadsheet",
  482.                         "spreadsheet": spreadsheet,
  483.                         "sheets": sheets,
  484.                     }
  485.                 } else {
  486.                     throw Error( "empty spreadsheet" );
  487.                 }
  488.             }
  489.         );
  491.     }
  493.     /**
  494.      * Copy data from the parameter into this.sheets and this.associated
  495.      *
  496.      * @param {string} contents - CSV file to load from
  497.      * @return {Object|undefined} spreadsheet information
  498.      */
  499.     static parse_csv(contents) {
  501.         const value = "(?:[^\",\\r\\n]*|\"(?:[^\"]|\"\")*\")",
  502.               csv_re = new RegExp(`^${value}(?:,${value})*(?:\r\n|\r|\n)`);
  504.         // Excel requires a byte order mark, which we ignore:
  505.         if ( contents[0] == "\u{FEFF}" ) contents = contents.substr(1);
  506.         // reduce the complexity of the regexps by guaranteeing exactly one trailing newline:
  507.         contents = contents.replace(/[\r\n]*$/,'\n');
  509.         // does this look like a valid CSV file?
  510.         for ( let offset=0; offset!=contents.length; ) {
  511.             const m = contents.substr(offset).match(csv_re);
  512.             if ( !m ) return;
  513.             offset += m[0].length;
  514.         }
  516.         let spreadsheet;
  517.         try {
  518.             spreadsheet = new self["ExcelJS"]["Workbook"]();
  519.         } catch (e) {
  520.             spreadsheet = new ( require("exceljs")["Workbook"] )();
  521.         }
  523.         let raw_records  = spreadsheet["addWorksheet"]("Records"),
  524.             raw_settings = spreadsheet["addWorksheet"]("Settings"),
  525.             records = [],
  526.             row_number=0,
  527.             settings = [ [ Spreadsheet.create_cell("Setting"), Spreadsheet.create_cell("Value") ] ]
  528.         ;
  529.         // CSV can start with key=value lines...
  530.         while ( !contents.search(/^([A-Za-z_][A-Za-z0-9_]*)=[^,]*\n/) ) {
  531.             contents = contents.replace(
  532.                 /([^=]*?)=(.*)\n/,
  533.                 (_,key,value) => {
  534.                     settings.push([key,value].map( v => Spreadsheet.create_cell(v) ));
  535.                     raw_settings["addRow"]([ key, value ]);
  536.                     return '';
  537.                 }
  538.             );
  539.         }
  541.         contents.replace(
  542.             new RegExp(`${value}(?:,${value})*(?:\r\n|\r|\n)`, 'g'),
  543.             line_str => {
  544.                 let raw_row = raw_records["getRow"](++row_number),
  545.                     row = [],
  546.                     n=0
  547.                 ;
  548.                 records.push(row);
  549.                 line_str
  550.                     .replace( new RegExp(value+'[,\r\n]','g'), value => {
  551.                         let raw_cell = raw_row["getCell"](++n);
  552.                         if ( value[0] == '"' ) {
  553.                             raw_cell["value"] = value.substr(1,value.length-3).replace( /""/g, '"' );
  554.                         } else {
  555.                             raw_cell["value"] = value.substr(0,value.length-1);
  556.                         }
  557.                         row.push(Spreadsheet.create_cell(raw_cell["value"]));
  558.                     });
  559.             }
  560.         );
  562.         Spreadsheet.parse_all_timestamps(records);
  564.         return {
  565.             "spreadsheet": spreadsheet,
  566.             "sheets": [
  567.                 { "name": "Records" , "cells": records  },
  568.                 { "name": "Settings", "cells": settings }
  569.             ],
  570.         }
  572.     }
  574.     static escape_csv_component( value ) {
  575.         return (
  576.             ( value === undefined )
  577.             ? ''
  578.             : ( value["getTime"] )
  579.             ? DiaryBase.date(value.getTime())["format"]("yyyy-MM-ddTHH:mm:ss.SSS")+'Z'
  580.             : ( value.toString().search(/[",\n]/) == -1 )
  581.             ? value
  582.             : '"'+value.replace(/"/g, '""')+'"'
  583.         );
  584.     }
  586.     /**
  587.      * Copy data from the parameter into this.sheets and this.associated
  588.      *
  589.      * @param {Object} spreadsheet - spreadsheet to load from
  590.      * @return {boolean} whether the operation was successful
  591.      */
  592.     ["load"](spreadsheet) {
  594.         const debug = false;
  596.         const sheets = spreadsheet["sheets"];
  598.         if ( !sheets ) return false;
  600.         const old_offset = this["epoch_offset"];
  601.         this["epoch_offset"] = (
  602.             ( spreadsheet["spreadsheet"]["properties"] || {} ) ["date1904"]
  603.                 ? 2082844800000
  604.                 : 2209161600000
  605.         );
  607.         if ( !this.rules.every(
  609.             sheet_rule => sheets.some( sheet => {
  611.                 const cells = sheet["cells"];
  612.                 const is_dictionary = sheet_rule["type"] == "dictionary";
  614.                 if ( debug ) {
  615.                     console.info( "Checking whether sheet_rule describes sheet...", sheet_rule, sheet );
  616.                 }
  618.                 if (
  619.                     !cells.length ||
  620.                     ( is_dictionary && cells.length != 2 )
  621.                 ) {
  622.                     if ( debug ) {
  623.                         console.info( "sheet_rule describes a sheet with a different shape" );
  624.                     }
  625.                     return false;
  626.                 }
  628.                 // ensure that all headers are present:
  629.                 const header_row = cells[0].slice(0);
  630.                 if (
  631.                     !sheet_rule["cells"].every(
  632.                         cell => cell["members"].every( member => member == (header_row.shift()||{"value":NaN})["value"] )
  633.                     )
  634.                 ) {
  635.                     if ( debug ) {
  636.                         console.info( "sheet_rule describes a sheet with different headers" );
  637.                     }
  638.                     return false;
  639.                 }
  641.                 // calculate array and check the values actually match:
  642.                 let array = [];
  643.                 if ( !cells.slice(1).every( row => {
  644.                     if ( !row.length ) return true;
  645.                     let offset = 0;
  646.                     let array_element = {};
  647.                     const parse_sheet =
  648.                           cell => cell["import"](
  649.                               array_element,
  650.                               row,
  651.                               ( offset += cell["members"].length ) - cell["members"].length
  652.                           );
  653.                     array.push(array_element);
  654.                     return sheet_rule["cells"].every(
  655.                         debug
  656.                         ? function(cell,n) {
  657.                             if ( parse_sheet(cell) ) {
  658.                                 return true;
  659.                             } else {
  660.                                 console.info(
  661.                                     "sheet_rule describes a sheet with a different body",
  662.                                     n,
  663.                                     cell,
  664.                                     array_element,
  665.                                     row
  666.                                 );
  667.                                 return false;
  668.                             }
  669.                         }
  670.                         : parse_sheet
  671.                     );
  672.                 }) ) {
  673.                     return false;
  674.                 }
  676.                 if ( debug ) {
  677.                     console.info( "sheet_rule matches sheet - loading" );
  678.                 }
  680.                 const member = sheet_rule["member"];
  681.                 if ( is_dictionary ) {
  682.                     this.associated[member] = Object.assign( array[0], this.associated[member]||{} );
  683.                 } else {
  684.                     if ( !this.associated[member] ) this.associated[member] = [];
  685.                     let data = this.associated[member];
  686.                     if ( data.length >= cells.length ) data.splice( 0, cells.length - 1 );
  687.                     array.forEach( (array_element,n) => data[n] = Object.assign( array_element, data[n] ) );
  688.                 }
  690.                 let number_formats = [];
  691.                 sheet_rule["cells"].forEach( cell => number_formats = number_formats.concat( cell["formats"] ) );
  692.                 sheet["number_formats"] = number_formats;
  694.                 if ( debug ) {
  695.                     console.info( "sheet_rule matches sheet - done" );
  696.                 }
  698.                 return true;
  700.             })
  702.         ) ) {
  703.             this["epoch_offset"] = old_offset;
  704.             return false;
  705.         }
  707.         this.raw = spreadsheet["spreadsheet"];
  708.         this["sheets"] = sheets;
  709.         return true;
  710.     }
  712.     /**
  713.      * Copy data from this.associated to this.sheets
  714.      * @return {boolean}
  715.      */
  716.     ["synchronise"]() {
  717.         return this.rules.every( sheet_rule => {
  719.             const is_dictionary = sheet_rule["type"] == "dictionary";
  721.             let headers = [];
  722.             let number_formats = [];
  723.             sheet_rule["cells"].forEach( cell => {
  724.                 headers = headers.concat( cell["members"] )
  725.                 number_formats = number_formats.concat( cell["formats"] );
  726.             });
  728.             const added_sheet = this["get_sheet"](sheet_rule["sheet"],headers,number_formats);
  729.             const added = added_sheet[0];
  730.             const sheet = added_sheet[1];
  731.             let cells = sheet["cells"] = [
  732.                 headers.map(header => Spreadsheet.create_cell(header,"#FFEEEEEE,#FFEEEEEE"))
  733.             ];
  735.             const associated = this.associated[sheet_rule["member"] || sheet_rule["sheet"]];
  736.             function process_row(array_element) {
  737.                 let row = [], offset = 0;
  738.                 cells.push(row);
  739.                 return sheet_rule["cells"].every(
  740.                     cell => cell["export"]( array_element, row, ( offset += cell["members"].length ) - cell["members"].length )
  741.                 );
  742.             }
  744.             if ( is_dictionary ) {
  745.                 if ( !process_row(associated)       ) return false;
  746.             } else {
  747.                 if ( !associated.every(process_row) ) return false;
  748.             }
  750.             if ( added ) this["sheets"].push(sheet);
  751.             return true;
  753.         });
  754.     }
  756.     /**
  757.      * Generate a spreadsheet based on this.sheets
  758.      * @return {Promise}
  759.      */
  760.     ["serialise"]() {
  762.         function decode_style(style) {
  763.             switch ( style[0] ) {
  764.             case '#': return { "argb"   :          style.slice(1)     };
  765.             case 'i': return { "indexed": parseInt(style.slice(1),10) };
  766.             case 't': return { "theme"  : parseInt(style.slice(1),10) };
  767.             default : return {};
  768.             }
  769.         }
  771.         const fix_timestamps = !this.raw;
  773.         if ( !this.raw ) {
  774.             try {
  775.                 this.raw = new self["ExcelJS"]["Workbook"]();
  776.             } catch (e) {
  777.                 this.raw = new ( require("exceljs")["Workbook"] )();
  778.             }
  779.             this["epoch_offset"] = (
  780.                 ( this.raw["properties"] || {} ) ["date1904"]
  781.                 ? 2082844800000
  782.                 : 2209161600000
  783.             );
  784.         }
  786.         const epoch_offset = this["epoch_offset"];
  788.         // Remove deleted worksheets:
  789.         let raw_sheets = {};
  790.         this["sheets"].forEach( sheet => raw_sheets[sheet["name"]] = 0 );
  791.         this.raw["eachSheet"]( (worksheet, sheetId) => {
  792.             if ( raw_sheets.hasOwnProperty(worksheet["name"]) ) {
  793.                 raw_sheets[worksheet["name"]] = worksheet;
  794.             } else {
  795.                 this.raw.removeWorksheet(sheetId);
  796.             }
  797.         });
  799.         // Add/update worksheets:
  800.         this["sheets"].forEach( sheet => {
  801.             let raw_sheet = raw_sheets[sheet["name"]] || this.raw["addWorksheet"](sheet["name"],sheet["options"]);
  803.             // Remove deleted cells:
  804.             raw_sheet["eachRow"]( { "includeEmpty": true }, (raw_row, row_number) => {
  805.                 let row = sheet["cells"][row_number-1] || [];
  806.                 raw_row["eachCell"]({ "includeEmpty": true }, (raw_cell, col_number) => {
  807.                     if ( !row[col_number] ) {
  808.                         raw_cell["value"] = null;
  809.                         raw_cell["style"] = {};
  810.                     }
  811.                 })
  812.             });
  814.             // Add/update cells:
  815.             sheet["cells"].forEach( (row,n) => {
  816.                 let raw_row = raw_sheet["getRow"](n+1);
  817.                 row.forEach( (cell,n) => {
  818.                     let raw_cell = raw_row["getCell"](n+1);
  819.                     if ( fix_timestamps && (cell["value"]||{}).getTime && cell["value"].getTime() >= 0 && cell["value"].getTime() < 24 * 60 * 60 * 1000 ) {
  820.                         // reset times relative to the new epoch
  821.                         raw_cell["value"] = new Date( cell["value"].getTime() - epoch_offset );
  822.                     } else {
  823.                         raw_cell["value"] = cell["value"];
  824.                     }
  825.                     let style = cell["style"].split(','),
  826.                         raw_style = raw_cell["style"] = raw_cell["style"] || {},
  827.                         column_font = ((sheet["styles"]||[])[n]||{})["font"]
  828.                     ;
  829.                     if ( style[0] || style[1] || style[2] ) {
  830.                         // Note: we remove any existing style, in case it interacts with the style we add:
  831.                         raw_style["fill"] = raw_style["fill"] || {};
  832.                         raw_style["fill"]["type"] = "pattern";
  833.                         raw_style["fill"]["pattern"] = "solid";
  834.                         if ( style[0] ) {
  835.                             raw_style["fill"]["bgColor"] = decode_style(style[0]);
  836.                         } else {
  837.                             delete raw_style["fill"]["bgColor"];
  838.                         }
  839.                         if ( style[1] ) {
  840.                             raw_style["fill"]["fgColor"] = decode_style(style[1]);
  841.                         } else {
  842.                             delete raw_style["fill"]["fgColor"];
  843.                         }
  844.                         if ( style[2] ) {
  845.                             raw_style["font"] = raw_style["font"] || {};
  846.                             raw_style["font"]["color"] = decode_style(style[2]);
  847.                         } else {
  848.                             delete raw_style["font"];
  849.                         }
  850.                     } else {
  851.                         if ( raw_style && raw_style["fill"] ) {
  852.                             delete raw_style["fill"]["bgColor"];
  853.                             delete raw_style["fill"]["fgColor"];
  854.                         }
  855.                     }
  856.                     /*
  857.                      * Cell styles override column styles, so we need to copy them in.
  858.                      * "font" is the only property we actually use right now.
  859.                      */
  860.                     if ( column_font ) {
  861.                         Object.assign( raw_style["font"] = raw_style["font"] || {}, column_font );
  862.                     }
  863.                 });
  864.                 raw_row["commit"]();
  865.             });
  867.             if ( sheet["widths"] ) {
  868.                 sheet["widths"].forEach( (w,n) => raw_sheet["columns"][n]["width"] = w );
  869.             } else {
  870.                 raw_sheet["columns"].forEach( col => col["width"] = 18 );
  871.             }
  873.             if ( sheet["styles"] ) {
  874.                 sheet["styles"].forEach( (s,n) => raw_sheet["columns"][n]["style"] = s );
  875.             }
  877.             (sheet["number_formats"]||[]).forEach (
  878.                 (format,n) => raw_sheet["getColumn"](n+1)["numFmt"] = format
  879.             );
  881.         });
  883.         return this.raw["xlsx"]["writeBuffer"]();
  885.     }
  887.     output_csv() {
  889.         const sheet_rule = this.rules[0];
  891.         return (
  893.             // Settings:
  894.             (this.associated["settings"]||[])
  895.             .map( row => row["Setting"] + "=" + row["Value"] + "\n" )
  896.             .concat(
  898.                 // header:
  899.                 [sheet_rule["cells"].map( cell => cell["members"].join(',') ).join(',') + "\n"],
  901.                 // Records:
  902.                 this.associated[sheet_rule["member"]].map(
  903.                     r => {
  904.                         let row = [], offset = 0;
  905.                         sheet_rule["cells"].forEach(
  906.                             cell => cell["export"]( r, row, ( offset += cell["members"].length ) - cell["members"].length )
  907.                         );
  908.                         return row.map( v => Spreadsheet.escape_csv_component( v["value"] )).join(',') + "\n"
  909.                     }
  910.                 )
  912.             ).join("")
  914.         );
  916.     }
  918.     ["file_format"]() { return "Spreadsheet"; }
  920. }