grammar.peg

// Excel-calc Grammar
// ==========================
//
// Accepts expressions like "=2 * (3 + 4)" or "=SUM(A2,27)" and computes their value.
// Partial spec
// https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a
// https://support.microsoft.com/en-us/office/data-types-in-data-models-e2388f62-6122-4e2b-bcad-053e3da9ba90
// http://dmcritchie.mvps.org/excel/sorting.htm

{
    // const excelAlphabeticalOrder = ["0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "'", "-", " ", "!", "\"", "#", "$", "%", "&", "(", ")", "*", ",", ".", "/", ":", ";", "?", "@", "[", "", "]", "^", "_", "`", "{", "|", "}", "~", "+", "<", "=", ">", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
    const mayBeComp = (() => {
        return undefined;
        if(typeof Intl === "undefined" || typeof Intl.Collator === "undefined") {
            return undefined;
        }
        const _intlcoll = new Intl.Collator(undefined, { sensitivity: "base", numeric: false });
        const compare = _intlcoll.compare.bind(_intlcoll);
        return compare;
    })();
    const compare = mayBeComp || ((x, y) => {
        if (x.toLowerCase() === y.toLowerCase()) {
            return 0;
        }
        return x.toLowerCase() > y.toLowerCase() ? 1 : -1;
        /*
        const a = ca.toUpperCase();
        const b = cb.toUpperCase();
        for(let idx = 0; idx < a.length; idx++) {
            if(idx >= b.length) {
                return 1;
            }
            const idxA = excelAlphabeticalOrder.indexOf(a[idx]), idxB= excelAlphabeticalOrder.indexOf(b[idx]);
            const diff = idxA - idxB;
            if(diff !== 0) {
                return diff;
            }
        }
        return b.length > a.length ? -1 : 0;
        */
    });
    
    const typeOrError = (type, e, conversionToType) => (t) => {
        if(t.value === "" && t.type === NULL) {
            return t;
        }
        if(t.type === LIST) {
            // maybe use Object.getOwnPropertySymbols;
            return {
                [CELL_TRACE]: t[CELL_TRACE],
                ...t,
                value: t.value.map(typeOrError(type, e, conversionToType))
            };
        }
        if(t.type === MATRIX) {
            return {
                [CELL_TRACE]: t[CELL_TRACE],
                ...t,
                value: t.value.map(v => v.map(typeOrError(type, e, conversionToType)))
            }
        }
        if(conversionToType === STRING && t.type === NUMBER) {
            return Object.assign(t, {
                type: STRING,
                value: `${t.value}`
            });
        }
        if(conversionToType === STRING && t.type === BOOLEAN) {
            return Object.assign(t, {
                type: STRING,
                value: t.value ? "TRUE" : "FALSE"
            });
        }
        if(conversionToType === NUMBER && t.type === BOOLEAN) {
            return Object.assign(t, {
                type: NUMBER,
                value: t.value ? 1 : 0
            });
        }
        if(conversionToType === NUMBER && t.type === STRING && t.value && t.value.trim() && /^[ \t\n\r]*-?[0-9]*(\.[0-9]*)?[ \t\n\r]*$/.test(t.value)) {
            return Object.assign(t, {
                type: NUMBER,
                value: parseFloat(t.value)
            });
        }
        return (type === t.type || t.type === ERROR) ? t : { type: ERROR, value: e || VVALUE, meta: t, [CELL_TRACE]: t[CELL_TRACE], tree: t.tree };
    };
    const stdop = (a,b,unsafeop,returntype,operation) => {
        const op = (...args) => {
            try {
                return { value: unsafeop(...args) };
            } catch(e) {
                return { value: e, type: ERROR };
            }
        };
    	const {value:left,type:tleft,meta:{type:lstype}={}} = a;
        const {value:right,type:tright,meta:{type:rstype}={}} = b;
        const tree = { type: OPERATION, value: [a,operation,b] };
        if(tleft === ERROR) {
            const e = new Error(left.description);
            e.tree = tree;
            e.type = ERROR;
            e.value = left;
            e.meta = { ...a, [CELL_TRACE]: a[CELL_TRACE], tree };
            return e;
        }
        if(tright === ERROR) {
            const e = new Error(right.description);
            e.tree = tree;
            e.type = ERROR;
            e.value = right;
            e.meta = { ...b, [CELL_TRACE]: b[CELL_TRACE], tree };
            return e;
        }
        const Cell_Trace = [...(a[CELL_TRACE] || []),...(b[CELL_TRACE] || [])];
        const meta = {
        	left: a.meta||{},
            right: b.meta||{},
            operation
        };
        if(tleft === LIST && tright === LIST) {
            return {
                type: LIST,
                tree,
                value: left.map(({value,type,...rest},idx) => ({
                    ...rest,
                    ...right[idx],
                    meta: {
                        ...meta,
                        item: {
                            left: rest.meta||{},
                            right: right[idx].meta
                        },
                        idx,
                    },
                    type: returntype,
                    ...op(value,right[idx].value,type,right[idx].type),
                    [CELL_TRACE]: Cell_Trace
                }))
            }
            // todo: Maybe distiguish by natural vs inline list?
        }
        if(tleft === LIST) {
            return { type: LIST, tree, value: left.map(({
                value,
                meta:m = {},
                type,
                ...rest
            }, idx) => ({
                ...rest,
                type: returntype,
                ...op(value,right,type,tright),
                meta: Object.assign(meta,{item: m,idx}),
                [CELL_TRACE]: Cell_Trace
            })) }
        }
        if(tright === LIST) {
            return { type: LIST, tree, value: right.map(({
                value,
                meta:m = {},
                type,
                ...rest
            },idx) => ({
                ...rest,
                type: returntype,
                ...op(left,value,tleft,type),
                meta: Object.assign(meta,{item: m,idx}),
                [CELL_TRACE]: Cell_Trace
            })) }
        }
        return { ...a, ...b, tree, type: returntype, ...op(left,right,tleft,tright), meta, [CELL_TRACE]: Cell_Trace };
    };
    
    const functions = functionDefinitions;
    // dynamic, from the outside
    let xcontext = options._context;
    let currentcell = options._currentcell || {};
    let calledBy = options._calledBy || [];
    let parser = options._self;
    const allowUnsafe = options.allowUnsafe || false;
    
	// static, internal
    const ccd = calledBy.concat([{ row: currentcell.row + 1, col: currentcell.col + 1, sheet: currentcell.sheet, workbook: currentcell.workbook }]);
    const ctx = { calledBy: ccd };

    const context = {
    	getWorkbook: wb => {
            const maybeWorkbook = xcontext.getWorkbook(wb)
            return {
                getSheet: (sh) => {
                    const sheet = workbook.getSheet(sh);
                    const maybeSheet = sh;
                    return {
                        getRow: (row, ...rest1) => ({
                                getCol: (col, ...rest2) => {
                                    if(ccd.some(({ row: r, col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1) && col === (c-1))) {
                                        return { type: ERROR, value: CIRCULAR, chain: [{col, row}].concat(ccd), meta: {} };
                                    }
                                    return sheet.getRow(row, ...rest1).getCol(col, ...rest2);
                                },
                                all: (...rest2) => {
                                    const find = ccd.find(({ row: r, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1));
                                    if(find) {
                                    return { type: ERROR, value: CIRCULAR, chain: [find].concat(ccd), meta: {} };
                                    }
                                    return sheet.getRow(row, ...rest1).all(...rest2);
                                }
                            }),
                        getCol: (col, ...rest1) => ({
                            getRow: (row, ...rest2) => {
                                if(ccd.some(({ row: r, col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1) && col === (c-1))) {
                                    return { type: ERROR, value: CIRCULAR, chain: [{col, row}].concat(ccd), meta: {} };
                                }
                                return sheet.getCol(col, ...rest1).getRow(row, ...rest2);
                            },
                            all: (...rest2) => {
                                const find = ccd.find(({ col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && col === (c-1));
                                if(find) {
                                    return { type: ERROR, value: CIRCULAR, chain: [find].concat(ccd), meta: {} };
                                } 
                                return sheet.getCol(col, ...rest1).all(...rest2);
                            }
                        }),
                    };
                },
            };
        },
    	getSheet: (sh, maybeWorkbook = currentcell.workbook) => {
            const sheet = xcontext.getSheet(sh);
            const maybeSheet = sh;
            return {
                getRow: (row, ...rest1) => ({
                        getCol: (col, ...rest2) => {
                            if(ccd.some(({ row: r, col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1) && col === (c-1))) {
                                return { type: ERROR, value: CIRCULAR, chain: [{col, row}].concat(ccd), meta: {} };
                            }
                            return sheet.getRow(row, ...rest1).getCol(col, ...rest2);
                        },
                        all: (...rest2) => {
                            const find = ccd.find(({ row: r, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1));
                            if(find) {
                             return { type: ERROR, value: CIRCULAR, chain: [find].concat(ccd), meta: {} };
                            }
                            return sheet.getRow(row, ...rest1).all(...rest2);
                        }
                    }),
                getCol: (col, ...rest1) => ({
                    getRow: (row, ...rest2) => {
                        if(ccd.some(({ row: r, col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1) && col === (c-1))) {
                            return { type: ERROR, value: CIRCULAR, chain: [{col, row}].concat(ccd), meta: {} };
                        }
                        return sheet.getCol(col, ...rest1).getRow(row, ...rest2);
                    },
                    all: (...rest2) => {
                        const find = ccd.find(({ col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && col === (c-1));
                        if(find) {
                            return { type: ERROR, value: CIRCULAR, chain: [find].concat(ccd), meta: {} };
                        } 
                        return sheet.getCol(col, ...rest1).all(...rest2);
                    }
                }),
            };
        },
    	getRow: (row, ctx, maybeSheet = currentcell.sheet, maybeWorkbook = currentcell.workbook) =>
          ({ 
            getCol: (col) => {
              if(ccd.some(({ row: r, col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1) && col === (c-1))) {
               return { type: ERROR, value: CIRCULAR, chain: [{col, row}].concat(ccd), meta: {} };
              } 
              return xcontext.getRow(row, ctx).getCol(col, ctx)
            }, 
            all: () => {
              const find = ccd.find(({ row: r, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1));
              if(find) {
               return { type: ERROR, value: CIRCULAR, chain: [find].concat(ccd), meta: {} };
              } 
              return xcontext.getRow(row, ctx).all(ctx);
            }
          }),
        getCol: (col, ctx, maybeSheet = currentcell.sheet, maybeWorkbook = currentcell.workbook) => 
          ({ 
            getRow: (row) => {
              if(ccd.some(({ row: r, col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && row === (r-1) && col === (c-1))) {
               return { type: ERROR, value: CIRCULAR, chain: [{col, row}].concat(ccd), meta: {} };
              } 
              return xcontext.getCol(col, ctx).getRow(row, ctx);
            }, 
            all: () => {
              const find = ccd.find(({ col: c, workbook: wb = currentcell.workbook, sheet: sh = currentcell.sheet }) => wb === maybeWorkbook && sh === maybeSheet && col === (c-1));
              if(find) {
               return { type: ERROR, value: CIRCULAR, chain: [find].concat(ccd), meta: {} };
              } 
              return xcontext.getCol(col, ctx).all(ctx);
            } 
          })
    }
    
}

Start = Equasion / Escaped / Num / Basebool / Static / Empty

Basebool = "TRUE" { return { type: BOOLEAN, value: true, tree: { type: BOOLEAN, value: [true] } } } / "FALSE" { return { type: BOOLEAN, value: false, tree: { type: BOOLEAN, value: [false] } } }

Empty = "" { return { type: NULL, value: null, tree: { type: STR_PLAIN, value: [""] } } }

Equasion = "=" exp:Expression { return { ...exp, [CELL_TRACE]: exp[CELL_TRACE], tree: { type: OPERATION, value: ["=", exp.tree] } }; }

Escaped = "'" str:String { return { type: STRING, value: text().substring(1), meta: { type: STR_ESCAPED }, tree: { type: STR_ESCAPED, value: [text()] } }; }

Static = [^'=+\-*/&<>\^] String? { return { type: STRING, value: text(), meta: { type: STR_PLAIN }, tree: { type: STR_PLAIN, value: [text()] } } }

String = .+

Expression
  = head:YExpression tail:(_ ("=" / "<>" / "<=" / ">=" / "<" / ">") _ YExpression)* {
      /*
      https://support.microsoft.com/en-us/office/data-types-in-data-models-e2388f62-6122-4e2b-bcad-053e3da9ba90
      In comparison expressions Boolean values are considered greater than string values and string values are considered 
      greater than numeric or date/time values; numbers and date/time values are considered to have the same rank. 
      No implicit conversions are performed for Boolean or string values; 
      BLANK or a blank value is converted to 0/""/false depending on the data type of the other compared value.
      */
      const rank = [[NUMBER, DATE, TIME, DATETIME], [STRING], [BOOLEAN]];
      const nullConversions = { NUMBER: 0, DATE: 0, TIME: 0, STRING: "", BOOLEAN: false };
      return tail.reduce(function(result, element) {



        // we have lots of special cases for booleans (Excel handels these differently to js)
        if (element[1] === "=") { return stdop(result, element[3], (x,y)=>{
            return x===y;
        }, BOOLEAN, "=") }
        if (element[1] === "<") { return stdop(result, element[3], (x,y,tx,ty)=>{
            if(tx !== ty) {
                if(tx === NULL) { return nullConversions[ty] < x; }
                if(ty === NULL) { return nullConversions[tx] < y; }
                const r1 = rank.findIndex(rk => rk.includes(tx));
                const r2 = rank.findIndex(rk => rk.includes(ty));
                if(r1 === -1 || r2 === -1) {
                    throw new Error(`Unknown comparison between ${String(tx)} and ${String(ty)}! Check the implementation!`);
                }
                return r1 < r2;
            }
            return tx === STRING && ty === STRING ? compare(x, y) < 0 : x < y;
        }, BOOLEAN, "<") }
        if (element[1] === ">") { return stdop(result, element[3], (x,y,tx,ty)=>{
            if(tx !== ty) {
                if(tx === NULL) { return nullConversions[ty] > x; }
                if(ty === NULL) { return nullConversions[tx] > y; }
                const r1 = rank.findIndex(rk => rk.includes(tx));
                const r2 = rank.findIndex(rk => rk.includes(ty));
                if(r1 === -1 || r2 === -1) {
                    throw new Error(`Unknown comparison between ${String(tx)} and ${String(ty)}! Check the implementation!`);
                }
                return r1 > r2;
            }
            return tx === STRING && ty === STRING ? compare(x, y) > 0 : x > y;
        }, BOOLEAN, ">") }
        if (element[1] === "<=") { return stdop(result, element[3], (x,y,tx,ty)=>{
            if(tx !== ty) {
                if(tx === NULL) { return nullConversions[ty] <= x; }
                if(ty === NULL) { return nullConversions[tx] <= y; }
                const r1 = rank.findIndex(rk => rk.includes(tx));
                const r2 = rank.findIndex(rk => rk.includes(ty));
                if(r1 === -1 || r2 === -1) {
                    throw new Error(`Unknown comparison between ${String(tx)} and ${String(ty)}! Check the implementation!`);
                }
                return r1 < r2;
            }
            return tx === STRING && ty === STRING ? compare(x, y) <= 0 : x <= y;
        }, BOOLEAN, "<=") }
        if (element[1] === ">=") { return stdop(result, element[3], (x,y,tx,ty)=>{
            if(tx !== ty) {
                if(tx === NULL) { return nullConversions[ty] >= x; }
                if(ty === NULL) { return nullConversions[tx] >= y; }
                const r1 = rank.findIndex(rk => rk.includes(tx));
                const r2 = rank.findIndex(rk => rk.includes(ty));
                if(r1 === -1 || r2 === -1) {
                    throw new Error(`Unknown comparison between ${String(tx)} and ${String(ty)}! Check the implementation!`);
                }
                return r1 > r2;
            }
            return tx === STRING && ty === STRING ? compare(x, y) >= 0 : x >= y;
        }, BOOLEAN, ">=") }
        if (element[1] === "<>") { return stdop(result, element[3], (x,y,tx,ty)=>{
            return x!==y;
        }, BOOLEAN, "<>") }
      }, head);
    }

YExpression
  = head:XExpression tail:(_ "&" _ XExpression)* {
      return tail.reduce(function(result, element) {
        const checkType = typeOrError(STRING, undefined, STRING);
        if (element[1] === "&") { return stdop(checkType(result), checkType(element[3]), (x,y)=>`${x}${y}`, STRING, "&") }
      }, head);
    }

XExpression
  = head:XTerm tail:(_ ("+" / "-") _ XTerm)* {
      return tail.reduce(function(result, element) {
        const checkType = typeOrError(NUMBER, undefined, NUMBER);
        if (element[1] === "+") { return stdop(checkType(result), checkType(element[3]), (x,y)=>(x||0)+(y||0), NUMBER, "+") }
        if (element[1] === "-") { return stdop(checkType(result), checkType(element[3]), (x,y)=>(x||0)-(y||0), NUMBER, "-") }
      }, head);
    }

XTerm
  = head:Term tail:(_ ("*" / "/") _ Term)* {
      return tail.reduce(function(result, element) {
        const checkType = typeOrError(NUMBER, undefined, NUMBER);
        if (element[1] === "*") { return stdop(checkType(result), checkType(element[3]), (x,y)=>(x||0)*(y||0), NUMBER, "*") }
        if (element[1] === "/") { return stdop(checkType(result), checkType(element[3]), (x,y)=>{
            if(y === 0) {
                throw DIV0;
            }
            return (x||0)/(y||0);
        }, NUMBER, "/") }
      }, head);
    }

Term
  = head:Percent tail:(_ "^" _ Percent)* {
      return tail.reduce(function(result, element) {
        if (element[1] === "^") {
            const checkType = typeOrError(NUMBER, undefined, NUMBER);
        	const v = stdop(checkType(result), checkType(element[3]), (x,y)=>{
                if(x === 0 && y === 0) {
                    throw NUMVALUE;
                }
                if(x === 0 && y < 0) {
                    throw DIV0;
                }
                return Math.pow((x||0),y);
            }, NUMBER, "^");
            return v;
        }
      }, head);
    }

Percent
  = head:Negative isPercent:(_ "%")? {
      if(!isPercent) {
          return head;
      }
      const checkType = typeOrError(NUMBER, undefined, NUMBER);
      const headInNumber = checkType(head);
      const tree = { type: OPERATION, value: [head, "%"] };
      const meta = { left: head, [CELL_TRACE]: head[CELL_TRACE] };
      if(headInNumber.type === ERROR) {
          return { ...head, ...headInNumber, meta, tree, [CELL_TRACE]: head[CELL_TRACE] }
      }
      return { ...head, tree, type: NUMBER, value: headInNumber.value * 0.01, meta, [CELL_TRACE]: head[CELL_TRACE] }
    }

Negative
  = isNegative:(("-" / "+") _)? head:Factor {
      if(!isNegative) {
          return head;
      }
      const [sign] = isNegative;
      if(sign === "+") {
          // weirdness in excel: the "+"-operator does not cast, while the "-"-operator does
          return head;
      }
      const checkType = typeOrError(NUMBER, undefined, NUMBER);
      const headInNumber = checkType(head);
      
      const tree = { type: OPERATION, value: [sign, head] };
      const meta = { left: head, [CELL_TRACE]: head[CELL_TRACE] };
      if(headInNumber.type === ERROR) {
          return { ...head, ...headInNumber, meta, tree, [CELL_TRACE]: head[CELL_TRACE] }
      }
      if(headInNumber.type === LIST) {
          const listValue = headInNumber.value.map(({ value, ...rest }) => ({
              ...rest,
              value: value * -1
          }));
          return { ...head, tree, type: headInNumber.type || NUMBER, value: listValue , meta, [CELL_TRACE]: head[CELL_TRACE] }
      }
      if(headInNumber.type === MATRIX) {
          const listValue = headInNumber.value.map(entry => entry.map(({ value, ...rest }) => ({
              ...rest,
              value: value * -1
          })));
          return { ...head, tree, type: headInNumber.type || NUMBER, value: listValue , meta, [CELL_TRACE]: head[CELL_TRACE] }
      }
      return { ...head, tree, type: headInNumber.type || NUMBER, value: headInNumber.value * -1, meta, [CELL_TRACE]: head[CELL_TRACE] }
    }

List = _ "{" inner:InnerList rows:(";" x:InnerList { return x; })* "}" _ {
    const Cell_Trace = inner.map(i => i[CELL_TRACE] || []);
    if(!rows.length) {
        return { type: LIST, tree: { type: LIST, value: inner.reduce((p, i) => [...p, i, ","], []) }, value: inner, meta: { type: INLINE }, [CELL_TRACE]: [].concat(...Cell_Trace) };
    }
    const treevalue = [inner.reduce((p, i) => [...p, i, ","], []), ...rows.map(r => r.reduce((p, i) => [...p, i, ","], []))].reduce((p, i) => [...p, i, ";"], []);
    return { type: MATRIX, tree: { type: MATRIX, value: treevalue }, value: [inner, ...rows], meta: { type: INLINE }, [CELL_TRACE]: [].concat(...Cell_Trace) };
}

InnerList = _ member:(True / False / Str / Num)? _ members:("," _ x:(True / False / Str / Num) _ {return x;})* { return [member || { type: NULL }, ...members]; }

Factor
  = "(" _ expr:Expression _ ")" { return { ...expr, [CELL_TRACE]: expr[CELL_TRACE], tree: { type: OPERATION, value: ["(", expr.tree, ")"] } }; }
   / List / Function / IdentifyFunction / Intersection / True / False / FiniteRangeR1C1 / R1C1Cell / InfiniteRangeAZ / InfiniteRange09 / FiniteRange / Cell / Str / Num

True = _ "TRUE" _ { return { type: BOOLEAN, value: true, [CELL_TRACE]: [], tree: { type: BOOLEAN, value: ["TRUE"] } } } 

False = _ "FALSE" _ { return { type: BOOLEAN, value: false, [CELL_TRACE]: [], tree: { type: BOOLEAN, value: ["FALSE"] } } }

CellSheetWorkbook
	= Workbooksheetquote / Sheetquote / Workbooksheet / Sheet
    
Sheet = t:([a-zA-Z][a-zA-Z0-9_]* { return text(); }) "!" { return { sheet: t, sheetraw: [t, "!"] }; }
Workbooksheet = "[" t:([a-zA-Z][a-zA-Z0-9_.]* { return text(); })  "]" s:Sheet { return { ...s, workbook: t, workbookraw: ["[", t, "]"] } }
Sheetquote = "'" t:( ("\\'" / [^'])+ { return text().replace(/\\'/g, "'"); } ) "'" "!" { return { sheet: t, sheetraw: [text().slice(0, -1), "!"] }  }
Workbooksheetquote = 
	"'"
    	"[" t1:( ("\\]" / [^\]])+ { return { v: text().replace(/\\\]/g, "]"), r: text() }; } ) "]"
		t2:( ("\\'" / [^'])+ { return { v: text().replace(/\\'/g, "'"), r: text() }; } ) 
    "'" "!" { return { sheet: t2.v, workbook: t1.v, absraw: ["'[", t1.r, "]", t2.r, "'!"] }  }
Cell
    = _ c:CellNoSpace _ { return c; }

CellNoSpace
	= loc:(CellSheetWorkbook)? abscol:"$"? col:[A-Z]+ absrow:"$"? row:([1-9][0-9]* { return parseInt(text(), 10); }) {
    const c = col.reduce((sum, char) => sum*26+char.charCodeAt(0)-64, 0);
    const { row: crow, col: ccol } = currentcell;
    const locx = loc || {};
    let contextu = context;
    if(locx.workbook) {
    	contextu = contextu.getWorkbook(locx.workbook);
    }
    if(locx.sheet) {
    	contextu = contextu.getSheet(locx.sheet);
    }
    const workbookandsheettreevalue = locx.absraw || locx.sheetraw ? locx.workbookraw ? [...locx.workbookraw, ...locx.sheetraw] : ["", "", "", ...locx.sheetraw] : ["", "", "", "", ""] || ["", "", "", "", ""];
    const carg = { ...locx, row: row-1, col: c-1, type: NATURALREF };
    const v = contextu.getRow(row-1, carg).getCol(c-1, carg) || { value: null, type: "ERROR" };
    const inheritedCellTrace = v[CELL_TRACE] || [];
    const result = {
    	...v,
        tree: { type: CELL, value: [...workbookandsheettreevalue, abscol ? "$" : "", col.join(""), absrow ? "$" : "", row] },
    	type: v.type,
        meta: { ...locx, type: CELL, row: row, col: c, absrow: absrow === "$", abscol: abscol === "$", workbook: locx.workbook, sheet: locx.sheet },
        value: v.value,
        [CELL_TRACE]: inheritedCellTrace.concat([{row: row, col: c, workbook: locx.workbook, sheet: locx.sheet}]),
    };
    return result;
}

R1C1Cell
	= _ loc:(CellSheetWorkbook)? "R" row:R1C1CellAbs "C" col:R1C1CellAbs _ {
    const { row: crow, col: ccol } = currentcell;
    let r = row.absolute ? row.cell : crow + row.cell;
    let c = col.absolute ? col.cell : ccol + col.cell;
    
    // loc:(CellSheetWorkbook)?
    const locx = loc || {};
    let contextu = context;
    if(locx.workbook) {
    	contextu = contextu.getWorkbook(locx.workbook);
    }
    if(locx.sheet) {
    	contextu = contextu.getSheet(locx.sheet);
    }
    const workbookandsheettreevalue = locx.absraw || locx.sheetraw ? locx.workbookraw ? [...locx.workbookraw, ...locx.sheetraw] : ["", "", "", ...locx.sheetraw] : ["", "", "", "", ""] || ["", "", "", "", ""];
    const carg = { ...locx, row: r-1, col: c-1, type: NATURALREF };
    const v = contextu.getRow(r-1, carg).getCol(c-1, carg) || { value: null, type: "ERROR" };
    const inheritedCellTrace = v[CELL_TRACE] || [];
    return {
    	...v,
        tree: { type: CELL, value: [...workbookandsheettreevalue, "R", row, "C", col] },
    	type: v.type,
        meta: { ...locx, type: CELL, row: r, col: c, absrow: row.absolute, abscol: col.absolute, notation: "R1C1", workbook: locx.workbook, sheet: locx.sheet },
        value: v.value,
        [CELL_TRACE]: inheritedCellTrace.concat([{row: row, col: c, workbook: locx.workbook, sheet: locx.sheet}]),
    };
}

R1C1CellAbs = "[" cell:([1-9][0-9]* { return parseInt(text(), 10); }) "]" {
				return { cell, absolute: true, tree: { type: R1C1PARTIAL, value: ["[", cell, "]"] } };
			} / cell:([1-9][0-9]* { return parseInt(text(), 10); }) {
				return { cell, absolute: false, tree: { type: R1C1PARTIAL, value: ["", cell, ""] } };
			}

FiniteRangeR1C1
	= _ loc:(CellSheetWorkbook)? start:R1C1Cell ":" end:R1C1Cell _ {
    
        const locx = loc || {};
        let contextu = context;
        if(locx.workbook) {
            contextu = contextu.getWorkbook(locx.workbook);
        }
        if(locx.sheet) {
            contextu = contextu.getSheet(locx.sheet);
        }
        const workbookandsheettreevalue = locx.absraw || locx.sheetraw ? locx.workbookraw ? [...locx.workbookraw, ...locx.sheetraw] : ["", "", "", ...locx.sheetraw] : ["", "", "", "", ""] || ["", "", "", "", ""];
        const meta = { ...locx, type: NATURALREF };
    	const results = [];
        const cellTrace = []
    	for(var row = start.meta.row; row <= end.meta.row; row++) {
          const t = [];
          for(var col = start.meta.col; col <= end.meta.col; col++) {
              const v = contextu.getRow(row-1,{...meta, row: row-1, col: col-1}).getCol(col-1,{...meta, row: row-1, col: col-1}) || { value: null, type: "ERROR" };
              cellTrace.push(...(v[CELL_TRACE] || []));
              cellTrace.push({row: row, col: col, workbook: locx.workbook, sheet: locx.sheet});
              t.push({ ...v, meta: { ...locx, type: CELL, row, col, workbook: locx.workbook, sheet: locx.sheet } });
          }
          results.push(t);
        }
        
        if(results.length === 1) {
        	return { type: LIST, value: results[0], meta, [CELL_TRACE]: cellTrace, tree: { type: RANGE, value: [...workbookandsheettreevalue, start, ":", end] } }
        }
        if(results.every(r => r.length === 1)) {
        	return { type: LIST, value: results.map(i => i[0]), meta, [CELL_TRACE]: cellTrace, tree: { type: RANGE, value: [...workbookandsheettreevalue, start, ":", end] } }
        }
        return { type: MATRIX, value: results, meta, [CELL_TRACE]: cellTrace, tree: { type: RANGE, value: [...workbookandsheettreevalue, start, ":", end] } }
    }

IdentifyFunction = _ "(" arg0:Arg args:(_ "," _ t:(y:Arg { return y; } / "" { return { type: NULL, value: undefined } }) _ { return t; })* ")" _ { return { type: ARGUMENTS, value: [arg0, ...args], tree: { type: ARGUMENTS, value: [arg0, ...args.reduce((p, i) => [...p, ",", i], [])] } } }

Function
	= _ name:([a-zA-Z][a-zA-Z0-9.]* { return text() }) _ "(" _ arg0:Arg? args:(_ "," _ t:(y:Arg { return y; } / _ "" { return { type: NULL, value: undefined } }) _ { return t; })* ")" {
    const allargs = [arg0, ...args].map(i => i || { type: NULL, [CELL_TRACE]: [] });
    const cellTrace = allargs.reduce((p, i) => p.concat(i[CELL_TRACE] || []), []);
    const argumenttree = [arg0 || "", ...args.reduce((p, i) => [...p, ",", i || ""], [])];
    if(functions[name]) {
        try {
        	const func = functions[name];
            
        	const v = func(allargs, { ...ctx, allowUnsafe, currentcell, name, raw: text(), functions, table: context, parser: parser, [CELL_TRACE]: cellTrace });
            if(!v) {
            	throw new Error("Function '"+name+"' did not return a result! Check the implementation!");
            }
            v[TRACE] = {
                function: name,
            	arguments: allargs
            };
            v.tree = { type: FUNCTIONCALL, value: [name, "(", ...argumenttree, ")"] };
            v[CELL_TRACE] = cellTrace.concat(v[CELL_TRACE] || []);
            return v;
        } catch(e) {
        	const initialError = allargs.find((e) => e && (e.type === ERROR));
            const r = { type: ERROR, value: (initialError && initialError.value) || e.name+' in '+name+': "'+e.message+'"', meta: {  error_value: initialError, internal_error: e } }
            r.tree = { type: FUNCTIONCALL, value: [name, "(", ...argumenttree, ")"] };
            r[CELL_TRACE] = cellTrace;
            return r;
        }
    }
    if(functions[name.toUpperCase()]) {
    	return { 
            type: ERROR, value: NAMEE, meta: { error: "Unknown function "+name+". Did you mean "+name.toUpperCase()+"?" },
            tree: { type: FUNCTIONCALL, value: [name, "(", ...argumenttree, ")"] }
        };
    }
    const simiar = Object.keys(functions).find(fnc => fnc.toUpperCase() === name.toUpperCase());
    if(simiar) {
    	return {
            type: ERROR, value: NAMEE, meta: { error: "Unknown function "+name+". Did you mean "+simiar+"?" },
            tree: { type: FUNCTIONCALL, value: [name, "(", ...argumenttree, ")"] }
        };
    }
    // todo find close names
    return {
        type: ERROR, value: NAMEE, meta: { error: "Unknown function "+name }, meta: { name },
        tree: { type: FUNCTIONCALL, value: [name, "(", ...argumenttree, ")"] }
    };
}

Arg
	= Expression

Intersection
	= _ a:XFiniteRange " " b:XFiniteRange _ {
    if(a.type !== LIST || b.type !== LIST) {
    	throw new Error("An intersection between anything else but LISTs is currently not supported. Expected LIST, LIST, found "+a.type+", "+b.type); 
    }
    const v = a.value.filter(i => b.value.some(j => j.meta.row === i.meta.row && j.meta.col === i.meta.col));
    const cellTrace = [].concat(...v.map(i => i[CELL_TRACE] || []));
    return {
    	type: LIST,
    	value: v,
        tree: { type: OPERATION, value: [a, " ", b] },
        meta: { type: NATURALREF },
        [CELL_TRACE]: cellTrace
    }
}

InfiniteRangeAZ
	= _ loc:(CellSheetWorkbook)? start:([A-Z]+ { return text(); }) ":" end:([A-Z]+ { return text(); }) _ {
    	if(start !== end) {
        	throw new Error("Infinite row-ranges can't span multiple columns \""+start+":"+end+"\"")
        }
        const locx = loc || {};
        let contextu = context;
        if(locx.workbook) {
            contextu = contextu.getWorkbook(locx.workbook);
        }
        if(locx.sheet) {
            contextu = contextu.getSheet(locx.sheet);
        }
        const workbookandsheettreevalue = locx.absraw || locx.sheetraw ? locx.workbookraw ? [...locx.workbookraw, ...locx.sheetraw] : ["", "", "", ...locx.sheetraw] : ["", "", "", "", ""] || ["", "", "", "", ""];
        const c = start.split("").reduce((sum, char) => sum*26+char.charCodeAt(0)-64, 0);
        const meta = { ...locx, type: NATURALREF, workbook: locx.workbook, sheet: locx.sheet };
        const v = contextu.getCol(c-1,meta).all({ col: c-1 }, meta);
        const cellTrace = [{ col: c, row: "*", workbook: locx.workbook, sheet: locx.sheet }].concat(...v.map(i => i[CELL_TRACE] || []));
        return { type: LIST, value: v, meta, [CELL_TRACE]: cellTrace, tree: { type: RANGE, value: [...workbookandsheettreevalue, start, ":", end] } };
        
    }
  
InfiniteRange09  
	= _ loc:(CellSheetWorkbook)? start:([1-9][0-9]* { return text(); }) ":" end:([1-9][0-9]* { return text(); }) _ {
    	if(start !== end) {
        	throw new Error("Infinite row-ranges can't span multiple columns \""+start+":"+end+"\"")
        }
        // loc:(CellSheetWorkbook)?
        const locx = loc || {};
        let contextu = context;
        if(locx.workbook) {
            contextu = contextu.getWorkbook(locx.workbook);
        }
        if(locx.sheet) {
            contextu = contextu.getSheet(locx.sheet);
        }
        const workbookandsheettreevalue = locx.absraw || locx.sheetraw ? locx.workbookraw ? [...locx.workbookraw, ...locx.sheetraw] : ["", "", "", ...locx.sheetraw] : ["", "", "", "", ""] || ["", "", "", "", ""];
        const r = parseInt(start, 10);
        const meta = { ...contextu, type: NATURALREF, workbook: locx.workbook, sheet: locx.sheet };
        const v = contextu.getRow(r-1,meta).all({ row: r-1 },meta);
        const cellTrace = [{ row: r, col: "*", workbook: locx.workbook, sheet: locx.sheet }].concat(...v.map(i => i[CELL_TRACE] || []));
        return { type: LIST, value: v, meta, [CELL_TRACE]: cellTrace, tree: { type: RANGE, value: [...workbookandsheettreevalue, start, ":", end] } };
    }

FiniteRange = _ x:XFiniteRange _ { return x; }

XFiniteRange
	= unsafestart:CellNoSpace ":" unsafeend:CellNoSpace {
    	const results = [];
        let start = unsafestart;
        if(unsafestart.type === ERROR && unsafestart.value === CIRCULAR) {
        	start = {...unsafestart.meta, meta: unsafestart.meta, tree: unsafestart.tree};
        }
        let end = unsafeend;
        if(unsafeend.type === ERROR && unsafeend.value === CIRCULAR) {
        	end = {...unsafeend.meta, meta: unsafeend.meta, tree: unsafeend.tree};
        }
        let contextu = context;
        const loc = {};
        if(start.meta.workbook) {
            contextu = contextu.getWorkbook(start.meta.workbook);
            loc.workbook = start.meta.workbook; // Avoid keys with undefined values
        }
        if(start.meta.sheet) {
            contextu = contextu.getSheet(start.meta.sheet);
            loc.sheet = start.meta.sheet; // Avoid keys with undefined values
        }
        const cellTrace = [];
 		let circularFlag = false;
    	for(let row = start.meta.row; row <= end.meta.row; row++) {
          const t = [];
          for(let col = start.meta.col; col <= end.meta.col; col++) {
              const v = contextu.getRow(row-1, {...loc,type: NATURALREF}).getCol(col-1, {...loc,type: NATURALREF}) || { value: null, type: "ERROR" };
              cellTrace.push(...(v[CELL_TRACE] || []));
              cellTrace.push({ row, col, workbook: start.meta.workbook, sheet: start.meta.sheet });
              if(v.type === ERROR && v.value === CIRCULAR) {
              	circularFlag = true;
              }
              t.push({ ...v, meta: { ...start.meta, type: CELL, row, col } });
              
          }
          results.push(t);
        }
        
        const tree = { type: RANGE, value: [...start.tree.value.slice(0,5), start.tree, ":", end.tree] };
        if(results.length === 1) {
        	const result = { type: LIST, value: results[0], rowspan: 1, colspan: results[0].length, meta: { ...loc, type: NATURALREF }, [CELL_TRACE]: cellTrace, tree };
        	if(circularFlag) {
                return { type: ERROR, value: CIRCULAR, meta: result, [CELL_TRACE]: cellTrace, tree };
            }
        	return result;
        }
        if(results.every(r => r.length === 1)) {
        	const result = { type: LIST, value: results.map(i => i[0]), rowspan: results.length, colspan: 1, meta: { ...loc, type: NATURALREF }, [CELL_TRACE]: cellTrace, tree };
        	if(circularFlag) {
                return { type: ERROR, value: CIRCULAR, meta: result, [CELL_TRACE]: cellTrace, tree };
            }
        	return result;
        }
        const result = {
        	type: MATRIX,
            value: results,
            tree,
            get rowspan() { return results.length }, 
            get colspan() { return results[0].length },
            meta: { ...loc, type: NATURALREF },
            [CELL_TRACE]: cellTrace
       };
       if(unsafestart.type === ERROR && unsafestart.value === CIRCULAR) {
        	return { ...unsafestart, type: ERROR, value: CIRCULAR, meta: result, [CELL_TRACE]: cellTrace, tree };
        }
       if(circularFlag) {
       	return { type: ERROR, value: CIRCULAR, meta: result, [CELL_TRACE]: cellTrace, tree };
       }
       
        if(unsafeend.type === ERROR && unsafeend.value === CIRCULAR) {
        	return { ...unsafeend, type: ERROR, value: CIRCULAR, meta: result, [CELL_TRACE]: cellTrace, tree };
        }
        return result;
    }

Str
	 = "\"\"" {return { type: STRING, value: "", meta: { empty: true }, tree: { type: STRING, value: ['""'] } }} /  _ "\"" t:( ("\\\"" / [^"])* { return { v: text().replace(/\\\"/g, "\""), r: text() }; } ) "\"" _ {return { type: STRING, value: t.v, tree: { type: STRING, value: ['"', t.r, '"'] } } }

Num "number"
  = _ num:Number _ { return num; }

Number = sign:("+" / "-" / "") int:([0-9]+) dec:("." [0-9]+)? percent:"%"? { 
    return { 
        type: NUMBER, 
        value: (percent === "%" ? 0.01 : 1) * parseFloat(text(), 10), 
        tree: [sign, int.join(""), dec ? "." : "", dec ? dec.join("").substring(1) : "", percent || ""] 
    }; }

_ "whitespace"
  = [ \t\n\r]*