WordPress.com



;(function($, window, document, undefined) {

'use strict'

var TOKEnum = {

TOK_TYPE_NOOP :"noop",

TOK_TYPE_OPERAND :"operand",

TOK_TYPE_FUNCTION :"function",

TOK_TYPE_SUBEXPR :"subexpression",

TOK_TYPE_ARGUMENT :"argument",

TOK_TYPE_OP_PRE :"operator-prefix",

TOK_TYPE_OP_IN :"operator-infix",

TOK_TYPE_OP_POST :"operator-postfix",

TOK_TYPE_WSPACE :"white-space",

TOK_TYPE_UNKNOWN :"unknown",

TOK_SUBTYPE_START :"start",

TOK_SUBTYPE_STOP :"stop",

TOK_SUBTYPE_TEXT :"text",

TOK_SUBTYPE_NUMBER :"number",

TOK_SUBTYPE_LOGICAL :"logical",

TOK_SUBTYPE_ERROR :"error",

TOK_SUBTYPE_RANGE :"range",

TOK_SUBTYPE_MATH :"math",

TOK_SUBTYPE_CONCAT :"concatenate",

TOK_SUBTYPE_INTERSECT:"intersect",

TOK_SUBTYPE_UNION :"union"

}

function ExcelFormula(inp, sel, tulTip, list) {

this.inp = inp;

this.sel = sel;

this.tulTip = tulTip;

this.list = list;

this.arrFun = [];

}

function f_token(value, type, subtype) {

this.value = value;

this.type = type;

this.subtype = subtype;

}

function f_tokens() {

this.items = new Array();

this.add = function(value, type, subtype) { if (!subtype) subtype = ""; var token = new f_token(value, type, subtype); this.addRef(token); return token; };

this.addRef = function(token) { this.items.push(token); };

this.index = -1;

this.reset = function() { this.index = -1; };

this.BOF = function() { return (this.index = (this.items.length - 1)); };

this.moveNext = function() { if (this.EOF()) return false; this.index++; return true; };

this.current = function() { if (this.index == -1) return null; return (this.items[this.index]); };

this.next = function() { if (this.EOF()) return null; return (this.items[this.index + 1]); };

this.previous = function() { if (this.index < 1) return null; return (this.items[this.index - 1]); };

}

function f_tokenStack() {

this.items = new Array();

this.push = function(token) { this.items.push(token); };

this.pop = function() { var token = this.items.pop(); return (new f_token("", token.type, TOKEnum.TOK_SUBTYPE_STOP)); };

this.token = function() { return ((this.items.length > 0) ? this.items[this.items.length - 1] : null); };

this.value = function() { return ((this.token()) ? this.token().value : ""); };

this.type = function() { return ((this.token()) ? this.token().type : ""); };

this.subtype = function() { return ((this.token()) ? this.token().subtype : ""); };

}

ExcelFormula.prototype = {

init: function() {

this.insertAfter(this.inp, this.tulTip);

this.insertAfter(this.tulTip, this.sel);

this.addEvents();

$(this.sel).hide();

},

addEvents: function() {

var self = this;

this.inp.addEventListener('keyup', function(e) {

if (this.value.trim().charAt(0) !== '=') {

return false;

}

var caret = self.getCaretPosition();

self.parseFormula(caret.end);

return;

var strVal = $.trim(this.value);

var currFunc = self.GetCurrentFunction(strVal, caret.end);

if (currFunc == undefined) {

return false;

}

var tmpstr = self.getStringWithoutInternalFunArg(strVal, currFunc.fName, currFunc.startIndx)

var currArgNumber = tmpstr.substring(currFunc.startIndx, caret.end).split(',').length

if (true || strVal.substring(currFunc.startIndx, caret.end).indexOf(',') == -1) {

currArgNumber--;

}

if (this.value.trim().length > 1) {

self.RenderFormulaTooltip(currFunc.fName, currArgNumber);

}

});

this.inp.addEventListener('blur', function(e) {

$(self.tulTip).hide();

$(self.sel).hide();

});

this.inp.focus();

},

RenderFormulaDrop: function(str) {

var ulist, rgxp;

// optimization

//debugger;

if (0 === str.length) {

//ulist = this.list;

$(this.sel).hide();

return false;

} else {

ulist = [];

for (var i = this.list.length - 1; i > -1; --i) {

if (this.list[i].value.toLowerCase().indexOf(str) == 0) {

ulist.push(this.list[i]);

}

}

}

this.updateSelect(this.sortByKey(ulist, 'value'));

},

sortByKey: function(array, key) {

return array.sort(function(a, b) {

var x = a[key];

var y = b[key];

return ((x < y) ? -1 : ((x > y) ? 1 : 0));

});

},

insertAfter: function(referenceNode, newNode) {

referenceNode.parentNode.insertBefore(newNode, referenceNode.nextSibling);

},

updateSelect: function(arr) {

var self, opts;

self = this;

this.sel.options.length = 0;

opts = this.buildOpts(arr);

opts.forEach(function(opt, idx) {

self.sel.options[idx] = opt;

});

opts.length > 0 ? $(self.sel).css('display','block'):$(self.sel).hide();

},

buildOpts: function(arr) {

var opts;

opts = [];

arr.forEach(function(val) {

opts.push(new Option(val.value));

})

return opts;

},

getCaretPosition: function() {

var start, end, ctrl = this.inp;

if (ctrl.setSelectionRange) {

start = ctrl.selectionStart;

end = ctrl.selectionEnd;

} else if (document.selection && document.selection.createRange) {

var range = document.selection.createRange();

start = 0 - range.duplicate().moveStart('character', -100000);

end = start + range.text.length;

}

return {

start: start,

end: end

}

},

BoldArgument: function(tipItem, llcurrArgNumber) {

var toBold = tipItem.argsTip.split(",")[llcurrArgNumber];

var nowBold = "" + toBold + ""

$(this.tulTip).html(tipItem.value + "(" + tipItem.argsTip.replace(toBold, nowBold) + ")");

},

GetFormula: function(formula) {

var obj = null;

for (var i = 0; i < this.list.length; i++) {

if (formula == this.list[i].value.toLowerCase()) {

obj = this.list[i];

break;

}

}

return obj;

},

GetCurrentFunction: function(lstrVal, lCurrIndx) {

this.arrFun = null;

this.arrFun = []

var subStrLen = 0;

var remStr = lstrVal;

var funObj = {};

// get function names and start index

for (var i = 1; i < remStr.length; i++) {

if (remStr.indexOf('(') != -1) {

var funName = remStr.substring(i, remStr.indexOf('('))

funObj = {};

funObj.fName = funName;

funObj.startIndx = remStr.indexOf('(') + subStrLen;

funObj.endIndx = -1;

subStrLen += funObj.startIndx + 1;

this.arrFun.push(funObj)

remStr = remStr.substring(remStr.indexOf('(') - 1 + funName.length)

i = -1;

}

}

// set end index of function

remStr = lstrVal;

subStrLen = 0;

for (var j = this.arrFun.length - 1; j >= 0; j--) {

if (remStr.indexOf(')') != -1) {

this.arrFun[j].endIndx = remStr.indexOf(')') + subStrLen;

subStrLen += funObj.endIndx + 1;

remStr = remStr.substring(remStr.indexOf(')') + 1)

}

}

// Get current function

for (var k = this.arrFun.length - 1; k >= 0; k--) {

if (lCurrIndx > this.arrFun[k].startIndx && lCurrIndx this.arrFun[l].startIndx && (lCurrIndx < this.arrFun[l].endIndx ||this.arrFun[l].endIndx == -1) ) {

retFun = this.arrFun[l];

}

}

return retFun;

},

RenderFormulaTooltip_old: function(currentFormula, lcurrArgNumber) {

$(this.tulTip).show();

var item = this.GetFormula(currentFormula);

this.BoldArgument(item, lcurrArgNumber);

},

RenderFormulaTooltip: function() {

if(typeof this.currFunction === 'undefined') {

$(this.tulTip).hide();

}

else {

this.tulTip.style.display = "block";

for (var i = this.list.length - 1; i > -1; --i) {

if (this.list[i].value.toLowerCase() == this.currFunction) {

this.tulTip.textContent = (this.list[i].value + "(" + this.list[i].argsTip + ")");

break;

}

}

}

},

getStringWithoutInternalFunArg: function(lstrVal, lFuntoSkip, lfunStart) {

for (var p = 0; p < this.arrFun.length; p++) {

if (this.arrFun[p].fName != lFuntoSkip && this.arrFun[p].startIndx > lfunStart) {

var strToReplace = lstrVal.substring(this.arrFun[p].startIndx, this.arrFun[p].endIndx + 1);

lstrVal = lstrVal.replace(strToReplace, strToReplace.replace(",", "A"))

}

}

return lstrVal;

},

parseFormula: function(caretPos) {

var indentCount = 0;

var indent = function() {

var s = "|";

for (var i = 0; i < indentCount; i++) {

s += "   |";

}

return s;

};

var formulaControl = this.inp;

var formula = formulaControl.value;

formula = formula.substring(0, caretPos);

var tokens = this.getTokens(formula);

var tokensHtml = "";

tokensHtml += "";

tokensHtml += "";

tokensHtml += "index";

tokensHtml += "type";

tokensHtml += "subtype";

tokensHtml += "token";

tokensHtml += "token tree";

this.RenderFormulaTooltip();

var opValue = '';

while (tokens.moveNext()) {

var token = tokens.current();

if(token.type == TOKEnum.TOK_TYPE_OPERAND) {

opValue = token.value;

}

if (token.subtype == TOKEnum.TOK_SUBTYPE_STOP)

indentCount -= ((indentCount > 0) ? 1 : 0);

tokensHtml += "";

tokensHtml += "" + (tokens.index + 1) + "";

tokensHtml += "" + token.type + "";

tokensHtml += "" + ((token.subtype.length == 0) ? " " : token.subtype) + "";

tokensHtml += "" + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "";

tokensHtml += "" + indent() + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "";

tokensHtml += "";

if (token.subtype == TOKEnum.TOK_SUBTYPE_START) {

indentCount += 1;

}

}

debugger;

this.RenderFormulaDrop(opValue);

tokensHtml += "";

document.getElementById("tokens").innerHTML = tokensHtml;

//formulaControl.select();

formulaControl.focus();

},

getTokens: function(formula) {

var tokens = new f_tokens();

var tokenStack = new f_tokenStack();

var offset = 0;

var currentChar = function() { return formula.substr(offset, 1); };

var doubleChar = function() { return formula.substr(offset, 2); };

var nextChar = function() { return formula.substr(offset + 1, 1); };

var EOF = function() { return (offset >= formula.length); };

var token = "";

var inString = false;

var inPath = false;

var inRange = false;

var inError = false;

while (formula.length > 0) {

if (formula.substr(0, 1) == " ")

formula = formula.substr(1);

else {

if (formula.substr(0, 1) == "=")

formula = formula.substr(1);

break;

}

}

var regexSN = /^[1-9]{1}(\.[0-9]+)?E{1}$/;

while (!EOF()) {

// state-dependent character evaluation (order is important)

// double-quoted strings

// embeds are doubled

// end marks token

if (inString) {

if (currentChar() == "\"") {

if (nextChar() == "\"") {

token += "\"";

offset += 1;

} else {

inString = false;

tokens.add(token, TOKEnum.TOK_TYPE_OPERAND, TOKEnum.TOK_SUBTYPE_TEXT);

token = "";

}

} else {

token += currentChar();

}

offset += 1;

continue;

}

// single-quoted strings (links)

// embeds are double

// end does not mark a token

if (inPath) {

if (currentChar() == "'") {

if (nextChar() == "'") {

token += "'";

offset += 1;

} else {

inPath = false;

}

} else {

token += currentChar();

}

offset += 1;

continue;

}

// bracked strings (range offset or linked workbook name)

// no embeds (changed to "()" by Excel)

// end does not mark a token

if (inRange) {

if (currentChar() == "]") {

inRange = false;

}

token += currentChar();

offset += 1;

continue;

}

// error values

// end marks a token, determined from absolute list of values

if (inError) {

token += currentChar();

offset += 1;

if ((",#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,").indexOf("," + token + ",") != -1) {

inError = false;

tokens.add(token, TOKEnum.TOK_TYPE_OPERAND, TOKEnum.TOK_SUBTYPE_ERROR);

token = "";

}

continue;

}

// scientific notation check

if (("+-").indexOf(currentChar()) != -1) {

if (token.length > 1) {

if (token.match(regexSN)) {

token += currentChar();

offset += 1;

continue;

}

}

}

// independent character evaulation (order not important)

// establish state-dependent character evaluations

if (currentChar() == "\"") {

if (token.length > 0) {

// not expected

tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);

token = "";

}

inString = true;

offset += 1;

continue;

}

if (currentChar() == "'") {

if (token.length > 0) {

// not expected

tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);

token = "";

}

inPath = true;

offset += 1;

continue;

}

if (currentChar() == "[") {

inRange = true;

token += currentChar();

offset += 1;

continue;

}

if (currentChar() == "#") {

if (token.length > 0) {

// not expected

tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);

token = "";

}

inError = true;

token += currentChar();

offset += 1;

continue;

}

// mark start and end of arrays and array rows

if (currentChar() == "{") {

if (token.length > 0) {

// not expected

tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);

token = "";

}

tokenStack.push(tokens.add("ARRAY", TOKEnum.TOK_TYPE_FUNCTION, TOKEnum.TOK_SUBTYPE_START));

tokenStack.push(tokens.add("ARRAYROW", TOKEnum.TOK_TYPE_FUNCTION, TOKEnum.TOK_SUBTYPE_START));

offset += 1;

continue;

}

if (currentChar() == ";") {

if (token.length > 0) {

tokens.add(token, TOKEnum.TOK_TYPE_OPERAND);

token = "";

}

tokens.addRef(tokenStack.pop());

tokens.add(",", TOKEnum.TOK_TYPE_ARGUMENT);

tokenStack.push(tokens.add("ARRAYROW", TOKEnum.TOK_TYPE_FUNCTION, TOKEnum.TOK_SUBTYPE_START));

offset += 1;

continue;

}

if (currentChar() == "}") {

if (token.length > 0) {

tokens.add(token, TOKEnum.TOK_TYPE_OPERAND);

token = "";

}

tokens.addRef(tokenStack.pop());

tokens.addRef(tokenStack.pop());

offset += 1;

continue;

}

// trim white-space

if (currentChar() == " ") {

if (token.length > 0) {

tokens.add(token, TOKEnum.TOK_TYPE_OPERAND);

token = "";

}

tokens.add("", TOKEnum.TOK_TYPE_WSPACE);

offset += 1;

while ((currentChar() == " ") && (!EOF())) {

offset += 1;

}

continue;

}

// multi-character comparators

if ((",>=, ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download