/*
 * (c) Copyright Ascensio System SIA 2010-2024
 *
 * This program is a free software product. You can redistribute it and/or
 * modify it under the terms of the GNU Affero General Public License (AGPL)
 * version 3 as published by the Free Software Foundation. In accordance with
 * Section 7(a) of the GNU AGPL its Section 15 shall be amended to the effect
 * that Ascensio System SIA expressly excludes the warranty of non-infringement
 * of any third-party rights.
 *
 * This program is distributed WITHOUT ANY WARRANTY; without even the implied
 * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR  PURPOSE. For
 * details, see the GNU AGPL at: http://www.gnu.org/licenses/agpl-3.0.html
 *
 * You can contact Ascensio System SIA at 20A-6 Ernesta Birznieka-Upish
 * street, Riga, Latvia, EU, LV-1050.
 *
 * The  interactive user interfaces in modified source and object code versions
 * of the Program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU AGPL version 3.
 *
 * Pursuant to Section 7(b) of the License you must retain the original Product
 * logo when distributing the program. Pursuant to Section 7(e) we decline to
 * grant you any rights under trademark law for use of our trademarks.
 *
 * All the Product's GUI elements, including illustrations and icon sets, as
 * well as technical writing content are licensed under the terms of the
 * Creative Commons Attribution-ShareAlike 4.0 International. See the License
 * terms at http://creativecommons.org/licenses/by-sa/4.0/legalcode
 *
 */


$(function () {

	var cDate = Asc.cDate;

	function toFixed(n) {
		return n;//.toFixed( AscCommonExcel.cExcelSignificantDigits ) - 0;
	}

	function difBetween(a, b) {
		return Math.abs(a - b) < dif
	}

	function _getPMT(fZins, fZzr, fBw, fZw, nF) {
		var fRmz;
		if (fZins == 0.0) {
			fRmz = (fBw + fZw) / fZzr;
		} else {
			var fTerm = Math.pow(1.0 + fZins, fZzr);
			if (nF > 0) {
				fRmz = (fZw * fZins / (fTerm - 1.0) + fBw * fZins / (1.0 - 1.0 / fTerm)) / (1.0 + fZins);
			} else {
				fRmz = fZw * fZins / (fTerm - 1.0) + fBw * fZins / (1.0 - 1.0 / fTerm);
			}
		}

		return -fRmz;
	}

	function _getFV(fZins, fZzr, fRmz, fBw, nF) {
		var fZw;
		if (fZins == 0.0) {
			fZw = fBw + fRmz * fZzr;
		} else {
			var fTerm = Math.pow(1.0 + fZins, fZzr);
			if (nF > 0) {
				fZw = fBw * fTerm + fRmz * (1.0 + fZins) * (fTerm - 1.0) / fZins;
			} else {
				fZw = fBw * fTerm + fRmz * (fTerm - 1.0) / fZins;
			}
		}

		return -fZw;
	}

	function _getDDB(cost, salvage, life, period, factor) {
		var ddb, ipmt, oldCost, newCost;
		ipmt = factor / life;
		if (ipmt >= 1) {
			ipmt = 1;
			if (period == 1) {
				oldCost = cost;
			} else {
				oldCost = 0;
			}
		} else {
			oldCost = cost * Math.pow(1 - ipmt, period - 1);
		}
		newCost = cost * Math.pow(1 - ipmt, period);

		if (newCost < salvage) {
			ddb = oldCost - salvage;
		} else {
			ddb = oldCost - newCost;
		}
		if (ddb < 0) {
			ddb = 0;
		}
		return ddb;
	}

	function _getIPMT(rate, per, pv, type, pmt) {
		var ipmt;

		if (per == 1) {
			if (type > 0) {
				ipmt = 0;
			} else {
				ipmt = -pv;
			}
		} else {
			if (type > 0) {
				ipmt = _getFV(rate, per - 2, pmt, pv, 1) - pmt;
			} else {
				ipmt = _getFV(rate, per - 1, pmt, pv, 0);
			}
		}
		return ipmt * rate
	}

	function _diffDate(d1, d2, mode) {
		var date1 = d1.getDate(), month1 = d1.getMonth(), year1 = d1.getFullYear(), date2 = d2.getDate(), month2 = d2.getMonth(), year2 = d2.getFullYear();

		switch (mode) {
			case 0:
				return Math.abs(GetDiffDate360(date1, month1, year1, date2, month2, year2, true));
			case 1:
				var yc = Math.abs(year2 - year1), sd = year1 > year2 ? d2 : d1, yearAverage = sd.isLeapYear() ? 366 : 365, dayDiff = Math.abs(d2 - d1);
				for (var i = 0; i < yc; i++) {
					sd.addYears(1);
					yearAverage += sd.isLeapYear() ? 366 : 365;
				}
				yearAverage /= (yc + 1);
				dayDiff /= c_msPerDay;
				return dayDiff;
			case 2:
				var dayDiff = Math.abs(d2 - d1);
				dayDiff /= c_msPerDay;
				return dayDiff;
			case 3:
				var dayDiff = Math.abs(d2 - d1);
				dayDiff /= c_msPerDay;
				return dayDiff;
			case 4:
				return Math.abs(GetDiffDate360(date1, month1, year1, date2, month2, year2, false));
			default:
				return "#NUM!";
		}
	}

	function _yearFrac(d1, d2, mode) {
		var date1 = d1.getDate(), month1 = d1.getMonth() + 1, year1 = d1.getFullYear(), date2 = d2.getDate(), month2 = d2.getMonth() + 1, year2 = d2.getFullYear();

		switch (mode) {
			case 0:
				return Math.abs(GetDiffDate360(date1, month1, year1, date2, month2, year2, true)) / 360;
			case 1:
				var yc = /*Math.abs*/(year2 - year1), sd = year1 > year2 ? new cDate(d2) : new cDate(d1), yearAverage = sd.isLeapYear() ? 366 : 365,
					dayDiff = /*Math.abs*/(d2 - d1);
				for (var i = 0; i < yc; i++) {
					sd.addYears(1);
					yearAverage += sd.isLeapYear() ? 366 : 365;
				}
				yearAverage /= (yc + 1);
				dayDiff /= (yearAverage * c_msPerDay);
				return dayDiff;
			case 2:
				var dayDiff = Math.abs(d2 - d1);
				dayDiff /= (360 * c_msPerDay);
				return dayDiff;
			case 3:
				var dayDiff = Math.abs(d2 - d1);
				dayDiff /= (365 * c_msPerDay);
				return dayDiff;
			case 4:
				return Math.abs(GetDiffDate360(date1, month1, year1, date2, month2, year2, false)) / 360;
			default:
				return "#NUM!";
		}
	}

	function _lcl_GetCouppcd(settl, matur, freq) {
		matur.setFullYear(settl.getFullYear());
		if (matur < settl) {
			matur.addYears(1);
		}
		while (matur > settl) {
			matur.addMonths(-12 / freq);
		}
	}

	function _lcl_GetCoupncd(settl, matur, freq) {
		matur.setFullYear(settl.getFullYear());
		if (matur > settl) {
			matur.addYears(-1);
		}
		while (matur <= settl) {
			matur.addMonths(12 / freq);
		}
	}

	function _getcoupdaybs(settl, matur, frequency, basis) {
		_lcl_GetCouppcd(settl, matur, frequency);
		return _diffDate(settl, matur, basis);
	}

	function _getcoupdays(settl, matur, frequency, basis) {
		_lcl_GetCouppcd(settl, matur, frequency);
		var n = new cDate(matur);
		n.addMonths(12 / frequency);
		return _diffDate(matur, n, basis);
	}

	function _getdiffdate(d1, d2, nMode) {
		var bNeg = d1 > d2;

		if (bNeg) {
			var n = d2;
			d2 = d1;
			d1 = n;
		}

		var nRet, pOptDaysIn1stYear;

		var nD1 = d1.getDate(), nM1 = d1.getMonth(), nY1 = d1.getFullYear(), nD2 = d2.getDate(), nM2 = d2.getMonth(), nY2 = d2.getFullYear();

		switch (nMode) {
			case 0:			// 0=USA (NASD) 30/360
			case 4:			// 4=Europe 30/360
			{
				var bLeap = d1.isLeapYear();
				var nDays, nMonths/*, nYears*/;

				nMonths = nM2 - nM1;
				nDays = nD2 - nD1;

				nMonths += (nY2 - nY1) * 12;

				nRet = nMonths * 30 + nDays;
				if (nMode == 0 && nM1 == 2 && nM2 != 2 && nY1 == nY2) {
					nRet -= bLeap ? 1 : 2;
				}

				pOptDaysIn1stYear = 360;
			}
				break;
			case 1:			// 1=exact/exact
				pOptDaysIn1stYear = d1.isLeapYear() ? 366 : 365;
				nRet = d2 - d1;
				break;
			case 2:			// 2=exact/360
				nRet = d2 - d1;
				pOptDaysIn1stYear = 360;
				break;
			case 3:			//3=exact/365
				nRet = d2 - d1;
				pOptDaysIn1stYear = 365;
				break;
		}

		return (bNeg ? -nRet : nRet) / c_msPerDay / pOptDaysIn1stYear;
	}

	function _getprice(nSettle, nMat, fRate, fYield, fRedemp, nFreq, nBase) {

		var fdays = AscCommonExcel.getcoupdays(new cDate(nSettle), new cDate(nMat), nFreq, nBase),
			fdaybs = AscCommonExcel.getcoupdaybs(new cDate(nSettle), new cDate(nMat), nFreq, nBase), fnum = AscCommonExcel.getcoupnum(new cDate(nSettle), (nMat), nFreq, nBase),
			fdaysnc = (fdays - fdaybs) / fdays, fT1 = 100 * fRate / nFreq, fT2 = 1 + fYield / nFreq, res = fRedemp / (Math.pow(1 + fYield / nFreq, fnum - 1 + fdaysnc));

		/*var fRet = fRedemp / ( Math.pow( 1.0 + fYield / nFreq, fnum - 1.0 + fdaysnc ) );
        fRet -= 100.0 * fRate / nFreq * fdaybs / fdays;

        var fT1 = 100.0 * fRate / nFreq;
        var fT2 = 1.0 + fYield / nFreq;

        for( var fK = 0.0 ; fK < fnum ; fK++ ){
            fRet += fT1 / Math.pow( fT2, fK + fdaysnc );
        }

        return fRet;*/

		if (fnum == 1) {
			return (fRedemp + fT1) / (1 + fdaysnc * fYield / nFreq) - 100 * fRate / nFreq * fdaybs / fdays;
		}

		res -= 100 * fRate / nFreq * fdaybs / fdays;

		for (var i = 0; i < fnum; i++) {
			res += fT1 / Math.pow(fT2, i + fdaysnc);
		}

		return res;
	}

	function _getYield(nSettle, nMat, fCoup, fPrice, fRedemp, nFreq, nBase) {
		var fRate = fCoup, fPriceN = 0.0, fYield1 = 0.0, fYield2 = 1.0;
		var fPrice1 = _getprice(nSettle, nMat, fRate, fYield1, fRedemp, nFreq, nBase);
		var fPrice2 = _getprice(nSettle, nMat, fRate, fYield2, fRedemp, nFreq, nBase);
		var fYieldN = (fYield2 - fYield1) * 0.5;

		for (var nIter = 0; nIter < 100 && fPriceN != fPrice; nIter++) {
			fPriceN = _getprice(nSettle, nMat, fRate, fYieldN, fRedemp, nFreq, nBase);

			if (fPrice == fPrice1) {
				return fYield1;
			} else if (fPrice == fPrice2) {
				return fYield2;
			} else if (fPrice == fPriceN) {
				return fYieldN;
			} else if (fPrice < fPrice2) {
				fYield2 *= 2.0;
				fPrice2 = _getprice(nSettle, nMat, fRate, fYield2, fRedemp, nFreq, nBase);

				fYieldN = (fYield2 - fYield1) * 0.5;
			} else {
				if (fPrice < fPriceN) {
					fYield1 = fYieldN;
					fPrice1 = fPriceN;
				} else {
					fYield2 = fYieldN;
					fPrice2 = fPriceN;
				}

				fYieldN = fYield2 - (fYield2 - fYield1) * ((fPrice - fPrice2) / (fPrice1 - fPrice2));
			}
		}

		if (Math.abs(fPrice - fPriceN) > fPrice / 100.0) {
			return "#NUM!";
		}		// result not precise enough

		return fYieldN;
	}

	function _getyieldmat(nSettle, nMat, nIssue, fRate, fPrice, nBase) {

		var fIssMat = _yearFrac(nIssue, nMat, nBase);
		var fIssSet = _yearFrac(nIssue, nSettle, nBase);
		var fSetMat = _yearFrac(nSettle, nMat, nBase);

		var y = 1.0 + fIssMat * fRate;
		y /= fPrice / 100.0 + fIssSet * fRate;
		y--;
		y /= fSetMat;

		return y;

	}

	function _coupnum(settlement, maturity, frequency, basis) {

		basis = (basis !== undefined ? basis : 0);

		var n = new cDate(maturity);
		_lcl_GetCouppcd(settlement, n, frequency);
		var nMonths = (maturity.getFullYear() - n.getFullYear()) * 12 + maturity.getMonth() - n.getMonth();
		return nMonths * frequency / 12;

	}

	function _duration(settlement, maturity, coupon, yld, frequency, basis) {
		var dbc = AscCommonExcel.getcoupdaybs(new cDate(settlement), new cDate(maturity), frequency, basis),
			coupD = AscCommonExcel.getcoupdays(new cDate(settlement), new cDate(maturity), frequency, basis),
			numCoup = AscCommonExcel.getcoupnum(new cDate(settlement), new cDate(maturity), frequency);

		if (settlement >= maturity || basis < 0 || basis > 4 || (frequency != 1 && frequency != 2 && frequency != 4) || yld < 0 || coupon < 0) {
			return "#NUM!";
		}

		var duration = 0, p = 0;

		var dsc = coupD - dbc;
		var diff = dsc / coupD - 1;
		yld = yld / frequency + 1;


		coupon *= 100 / frequency;

		for (var index = 1; index <= numCoup; index++) {
			var di = index + diff;

			var yldPOW = Math.pow(yld, di);

			duration += di * coupon / yldPOW;

			p += coupon / yldPOW;
		}

		duration += (diff + numCoup) * 100 / Math.pow(yld, diff + numCoup);
		p += 100 / Math.pow(yld, diff + numCoup);

		return duration / p / frequency;
	}

	function numDivFact(num, fact) {
		var res = num / Math.fact(fact);
		res = res.toString();
		return res;
	}

	function testArrayFormula(assert, func, dNotSupportAreaArg) {

		var getValue = function (ref) {
			oParser = new parserFormula(func + "(" + ref + ")", "A2", ws);
			assert.ok(oParser.parse());
			return oParser.calculate().getValue();
		};

		//***array-formula***
		ws.getRange2("A100").setValue("1");
		ws.getRange2("B100").setValue("3");
		ws.getRange2("C100").setValue("-4");
		ws.getRange2("A101").setValue("2");
		ws.getRange2("B101").setValue("4");
		ws.getRange2("C101").setValue("5");


		oParser = new parserFormula(func + "(A100:C101)", "A1", ws);
		oParser.setArrayFormulaRef(ws.getRange2("E106:H107").bbox);
		assert.ok(oParser.parse());
		var array = oParser.calculate();
		if (AscCommonExcel.cElementType.array === array.type) {
			assert.strictEqual(array.getElementRowCol(0, 0).getValue(), getValue("A100"));
			assert.strictEqual(array.getElementRowCol(0, 1).getValue(), getValue("B100"));
			assert.strictEqual(array.getElementRowCol(0, 2).getValue(), getValue("C100"));
			assert.strictEqual(array.getElementRowCol(1, 0).getValue(), getValue("A101"));
			assert.strictEqual(array.getElementRowCol(1, 1).getValue(), getValue("B101"));
			assert.strictEqual(array.getElementRowCol(1, 2).getValue(), getValue("C101"));
		} else {
			if (!dNotSupportAreaArg) {
				assert.strictEqual(false, true);
			}
			consoleLog("func: " + func + " don't return area array");
		}

		oParser = new parserFormula(func + "({1,2,-3})", "A1", ws);
		oParser.setArrayFormulaRef(ws.getRange2("E106:H107").bbox);
		assert.ok(oParser.parse());
		array = oParser.calculate();
		assert.strictEqual(array.getElementRowCol(0, 0).getValue(), getValue(1));
		assert.strictEqual(array.getElementRowCol(0, 1).getValue(), getValue(2));
		assert.strictEqual(array.getElementRowCol(0, 2).getValue(), getValue(-3));
	}

	//returnOnlyValue - те функции, на вход которых всегда должны подаваться массивы и которые возвращают единственное значение
	function testArrayFormula2(assert, func, minArgCount, maxArgCount, dNotSupportAreaArg, returnOnlyValue) {

		var getValue = function (ref, countArg) {
			var argStr = "(";
			for (var j = 1; j <= countArg; j++) {
				argStr += ref;
				if (i !== j) {
					argStr += ",";
				} else {
					argStr += ")";
				}
			}
			oParser = new parserFormula(func + argStr, "A2", ws);
			assert.ok(oParser.parse());
			return oParser.calculate().getValue();
		};


		//***array-formula***
		ws.getRange2("A100").setValue("1");
		ws.getRange2("B100").setValue("3");
		ws.getRange2("C100").setValue("-4");
		ws.getRange2("A101").setValue("2");
		ws.getRange2("B101").setValue("4");
		ws.getRange2("C101").setValue("5");

		//формируем массив значений
		var randomArray = [];
		var randomStrArray = "{";
		var maxArg = 4;
		for (var i = 1; i <= maxArg; i++) {
			var randVal = Math.random();
			randomArray.push(randVal);
			randomStrArray += randVal;
			if (i !== maxArg) {
				randomStrArray += ",";
			} else {
				randomStrArray += "}";
			}
		}

		for (var i = minArgCount; i <= maxArgCount; i++) {
			var argStrArr = "(";
			var randomArgStrArr = "(";
			for (var j = 1; j <= i; j++) {
				argStrArr += "A100:C101";
				randomArgStrArr += randomStrArray;
				if (i !== j) {
					argStrArr += ",";
					randomArgStrArr += ",";
				} else {
					argStrArr += ")";
					randomArgStrArr += ")";
				}
			}

			oParser = new parserFormula(func + argStrArr, "A1", ws);
			oParser.setArrayFormulaRef(ws.getRange2("E106:H107").bbox);
			assert.ok(oParser.parse());
			var array = oParser.calculate();
			if (AscCommonExcel.cElementType.array === array.type) {
				assert.strictEqual(array.getElementRowCol(0, 0).getValue(), getValue("A100", i));
				assert.strictEqual(array.getElementRowCol(0, 1).getValue(), getValue("B100", i));
				assert.strictEqual(array.getElementRowCol(0, 2).getValue(), getValue("C100", i));
				assert.strictEqual(array.getElementRowCol(1, 0).getValue(), getValue("A101", i));
				assert.strictEqual(array.getElementRowCol(1, 1).getValue(), getValue("B101", i));
				assert.strictEqual(array.getElementRowCol(1, 2).getValue(), getValue("C101", i));
			} else {
				if (!(dNotSupportAreaArg || returnOnlyValue)) {
					assert.strictEqual(false, true);
				}
				consoleLog("func: " + func + " don't return area array");
			}

			oParser = new parserFormula(func + randomArgStrArr, "A1", ws);
			oParser.setArrayFormulaRef(ws.getRange2("E106:H107").bbox);
			assert.ok(oParser.parse());
			array = oParser.calculate();
			if (AscCommonExcel.cElementType.array === array.type) {
				assert.strictEqual(array.getElementRowCol(0, 0).getValue(), getValue(randomArray[0], i));
				assert.strictEqual(array.getElementRowCol(0, 1).getValue(), getValue(randomArray[1], i));
				assert.strictEqual(array.getElementRowCol(0, 2).getValue(), getValue(randomArray[2], i));
			} else {
				if (!returnOnlyValue) {
					assert.strictEqual(false, true);
				}
				consoleLog("func: " + func + " don't return array");
			}
		}
	}

	function testArrayFormulaEqualsValues(assert, str, formula, isNotLowerCase) {
		//***array-formula***
		ws.getRange2("A1").setValue("1");
		ws.getRange2("B1").setValue("3.123");
		ws.getRange2("C1").setValue("-4");
		ws.getRange2("A2").setValue("2");
		ws.getRange2("B2").setValue("4");
		ws.getRange2("C2").setValue("5");

		oParser = new parserFormula(formula, "A1", ws);
		oParser.setArrayFormulaRef(ws.getRange2("E6:H8").bbox);
		assert.ok(oParser.parse());
		var array = oParser.calculate();

		var splitStr = str.split(";");

		for (var i = 0; i < splitStr.length; i++) {
			var subSplitStr = splitStr[i].split(",");
			for (var j = 0; j < subSplitStr.length; j++) {
				var valMs = subSplitStr[j];
				var element;
				if (array.getElementRowCol) {
					var row = 1 === array.array.length ? 0 : i;
					var col = 1 === array.array[0].length ? 0 : j;
					if (array.array[row] && array.array[row][col]) {
						element = array.getElementRowCol(row, col);
					} else {
						element = new window['AscCommonExcel'].cError(window['AscCommonExcel'].cErrorType.not_available);
					}
				} else {
					element = array;
				}
				var ourVal = element && undefined != element.value ? element.value.toString() : "#N/A";
				if (!isNotLowerCase) {
					valMs = valMs.toLowerCase();
					ourVal = ourVal.toLowerCase();
				}
				assert.strictEqual(valMs, ourVal, "formula: " + formula + " i: " + i + " j: " + j)
			}
		}
	}

	function _getValue(from, row, col) {
		var res;
		if (from.type === AscCommonExcel.cElementType.array) {
			res = from.getElementRowCol(row !== undefined ? row : 0, col !== undefined ? col : 0).getValue();
		} else if (from.type === AscCommonExcel.cElementType.cellsRange || from.type === AscCommonExcel.cElementType.cellsRange3D) {
			res = from.getValueByRowCol(row !== undefined ? row : 0, col !== undefined ? col : 0).getValue();
		} else if (from.type === AscCommonExcel.cElementType.cell || from.type === AscCommonExcel.cElementType.cell3D) {
			res = from.getValue().getValue();
		} else {
			res = from.getValue();
		}
		return res;
	}

	function consoleLog(val) {
		//console.log(val);
	}

	var newFormulaParser = false;

	var c_msPerDay = AscCommonExcel.c_msPerDay;
	var parserFormula = AscCommonExcel.parserFormula;
	var GetDiffDate360 = AscCommonExcel.GetDiffDate360;
	var fSortAscending = AscCommon.fSortAscending;
	var g_oIdCounter = AscCommon.g_oIdCounter;

	var oParser, wb, ws, dif = 1e-9, sData = AscCommon.getEmpty(), tmp, array;
	if (AscCommon.c_oSerFormat.Signature === sData.substring(0, AscCommon.c_oSerFormat.Signature.length)) {

		Asc.spreadsheet_api.prototype._init = function() {
			this.isLoadFullApi = true;
		};

		
		let api = new Asc.spreadsheet_api({
			'id-view': 'editor_sdk'
		});
		api.FontLoader = {
			LoadDocumentFonts: function () {
			}
		};

		let docInfo = new Asc.asc_CDocInfo();
		docInfo.asc_putTitle("TeSt.xlsx");
		api.DocInfo = docInfo;
		api.initCollaborativeEditing({});

		window["Asc"]["editor"] = api;

		AscCommon.g_oTableId.init();
		wb = new AscCommonExcel.Workbook(new AscCommonExcel.asc_CHandlersList(), api, true);
		AscCommon.History.init(wb);
		wb.maxDigitWidth = 7;
		wb.paddingPlusBorder = 5;

		api.wbModel = wb;

		if (this.User) {
			g_oIdCounter.Set_UserId(this.User.asc_getId());
		}

		AscCommonExcel.g_oUndoRedoCell = new AscCommonExcel.UndoRedoCell(wb);
		AscCommonExcel.g_oUndoRedoWorksheet = new AscCommonExcel.UndoRedoWoorksheet(wb);
		AscCommonExcel.g_oUndoRedoWorkbook = new AscCommonExcel.UndoRedoWorkbook(wb);
		AscCommonExcel.g_oUndoRedoCol = new AscCommonExcel.UndoRedoRowCol(wb, false);
		AscCommonExcel.g_oUndoRedoRow = new AscCommonExcel.UndoRedoRowCol(wb, true);
		AscCommonExcel.g_oUndoRedoComment = new AscCommonExcel.UndoRedoComment(wb);
		AscCommonExcel.g_oUndoRedoAutoFilters = new AscCommonExcel.UndoRedoAutoFilters(wb);
		AscCommonExcel.g_DefNameWorksheet = new AscCommonExcel.Worksheet(wb, -1);
		g_oIdCounter.Set_Load(false);

		var oBinaryFileReader = new AscCommonExcel.BinaryFileReader();
		oBinaryFileReader.Read(sData, wb);
		ws = wb.getWorksheet(wb.getActive());
		AscCommonExcel.getFormulasInfo();
	}

	wb.dependencyFormulas.lockRecal();

	QUnit.module("External reference");

	QUnit.test("Test: \"test relative reference from absolute\"", function (assert) {
		//by test external reference
		//use when insert external link from clipboard

		let path1 = "C:/test1/testInside/testinside12/testInsied21/test1.xlsx";
		let path2 = "C:/test1/testInside/testInsied11/testinsied22/test2.xlsx";
		let need = "/test1/testInside/testinside12/testInsied21/test1.xlsx";
		let real = AscCommonExcel.buildRelativePath(path1, path2);
		assert.strictEqual(need, real);

		// "/root/from1.xlsx"
		path1 = "C:/root/test.xlsx";
		path2 = "C:/root/inside/inside2/inseide3/inside4/test.xlsx";
		need = "/root/test.xlsx";
		real = AscCommonExcel.buildRelativePath(path1, path2);
		assert.strictEqual(need, real);

		// "inside/inside2/inseide3/inside4/from2.xlsx"
		path1 = "C:/root/inside/inside2/inseide3/inside4/test.xlsx";
		path2 = "C:/root/test.xlsx";
		need = "inside/inside2/inseide3/inside4/test.xlsx";
		real = AscCommonExcel.buildRelativePath(path1, path2);
		assert.strictEqual(need, real);


		path1 = "D:/root/inside/inside2/inseide3/inside4/test.xlsx";
		path2 = "C:/root/test.xlsx";
		need = "file:///D:\\root\\inside\\inside2\\inseide3\\inside4\\test.xlsx";
		real = AscCommonExcel.buildRelativePath(path1, path2);
		assert.strictEqual(need, real);
	});

	let initReference = function (eR, sheetName, range, val, needUpdateExternalWs) {
		range = AscCommonExcel.g_oRangeCache.getAscRange(range);
		let externalSheetDataSet = eR.getSheetDataSetByName(sheetName);
		for (let i = range.r1; i <= range.r2; i++) {
			let row = externalSheetDataSet.getRow(i + 1, true);
			for (let j = range.c1; j <= range.c2; j++) {
				let cell = row.getCell(j, true);
				cell.CellValue = val[i][j];
			}
		}
		if (needUpdateExternalWs) {
			//update temporary worksheet from external reference structure
			eR.initWorksheetFromSheetDataSet(sheetName);
		}
	};

	let initDefinedName = function (eR, sheetName, range, name, shortLink) {
		let RealDefNameWorksheet = AscCommonExcel.g_DefNameWorksheet;
		AscCommonExcel.g_DefNameWorksheet = eR.worksheets[sheetName];
		wb.dependencyFormulas.initOpen();
		let _obj = {
			value: name,
			ws: {sName: sheetName},
			shortLink: shortLink
		};
		eR.initDefinedName(_obj);
		AscCommonExcel.g_DefNameWorksheet = RealDefNameWorksheet;
	};

	let createExternalWorksheet = function (name) {
		let externalWs = new AscCommonExcel.Worksheet(wb);
		externalWs.sName = name;
		return externalWs;
	};

	QUnit.test("Test: \"External reference test: importRange function\"", function (assert) {

		let tempLink = '"http://localhost/editor?fileName=new%20(51).xlsx"';
		let parseResult = new AscCommonExcel.ParseResult([]);
		oParser = new parserFormula('IMPORTRANGE(' + tempLink + ',"Sheet1!A1")', 'A2', ws);
		assert.ok(oParser.parse(null, null, parseResult), 'IMPORTRANGE(' + tempLink + ',"Sheet1!A1")');
		let res = oParser.calculate().getValue();
		assert.strictEqual(res, "#REF!", 'IMPORTRANGE_1');

		assert.strictEqual(wb.externalReferences.length, 0, 'IMPORTRANGE_1_external_reference_length_before_add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'IMPORTRANGE_1_external_reference_length_after_add');

		res = oParser.calculate();
		let dimension = res.getDimensions();
		assert.strictEqual(dimension.row, 1, 'IMPORTRANGE_1_after_add_references_row_count');

		initReference(wb.externalReferences[0], "Sheet1", "A1", [[1000]]);
		res = oParser.calculate();
		assert.strictEqual(res.getElementRowCol(0, 0).getValue(), 1000, 'IMPORTRANGE_1_AFTER_INIT');

		assert.strictEqual(wb.externalReferences.length, 1, 'IMPORTRANGE_1_external_reference_length_before_add_clone_2');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'IMPORTRANGE_1_external_reference_length_after_add_clone_2');

		//check remove on setValue
		ws.getRange2("A2").setValue('=importrange(\"http://localhost/editor?fileName=new%20(51).xlsx\",\"Sheet1!A1\"');
		assert.strictEqual(wb.externalReferences.length, 1, 'IMPORTRANGE_1_external_reference_length_before_add_clone_3');

		ws.getRange2("A2").setValue('=importrange(\"http://localhost/editor?fileName=new%20(51).xlsx\",\"Sheet1!A2\"');
		assert.strictEqual(wb.externalReferences.length, 1, 'IMPORTRANGE_1_external_reference_length_after_remove_value');

		// change source of reference

		let fromER = wb.externalReferences[0];
		let fromERWorksheetName = fromER.SheetNames && fromER.SheetNames[0];
		let fromERWorksheet = fromER.worksheets && fromER.worksheets[fromERWorksheetName];
		let fromERId = fromER.Id;
		let toER = fromER.clone(true);
		let index = wb.getExternalLinkIndexByName(fromERId);
		toER.setId("new (104).xlsx");

		ws.getRange2("A2").setValue('=importrange(\"http://localhost/editor?fileName=new%20(51).xlsx\",\"Sheet1!A2\"');
		ws.getRange2("B2").setValue('=importrange(\"http://localhost/editor?fileName=new%20(51).xlsx\",\"A3\"');
		ws.getRange2("C2").setValue('=importrange(\"http://localhost/editor?fileName=new%20(51).xlsx\",\"A4\"');

		assert.strictEqual(ws.getRange2("A2").getValueForEdit(), '=IMPORTRANGE("http://localhost/editor?fileName=new%20(51).xlsx","Sheet1!A2")', 'Import range function in A2 before source change');
		assert.strictEqual(ws.getRange2("B2").getValueForEdit(), '=IMPORTRANGE("http://localhost/editor?fileName=new%20(51).xlsx","A3")', 'Import range function in B2 before source change');
		assert.strictEqual(ws.getRange2("C2").getValueForEdit(), '=IMPORTRANGE("http://localhost/editor?fileName=new%20(51).xlsx","A4")', 'Import range function in C2 before source change');
		assert.ok(Object.keys(wb.dependencyFormulas.sheetListeners[fromERWorksheet.Id].areaMap).length > 0, 'Listeners count in dep. formula before change source');


		wb.changeExternalReference(index, toER);
		assert.ok(Object.keys(wb.dependencyFormulas.sheetListeners[fromERWorksheet.Id].areaMap).length === 0, 'Listeners count in dep. formula after change source');

		assert.strictEqual(ws.getRange2("A2").getValueForEdit(), '=IMPORTRANGE("new (104).xlsx","Sheet1!A2")', 'Import range function after source change');
		assert.strictEqual(ws.getRange2("B2").getValueForEdit(), '=IMPORTRANGE("new (104).xlsx","A3")', 'Import range function in B2 after source change');
		assert.strictEqual(ws.getRange2("C2").getValueForEdit(), '=IMPORTRANGE("new (104).xlsx","A4")', 'Import range function in C2 after source change');

		// remove changed reference
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0, 'IMPORTRANGE_1_external_reference_length_after_remove_er');
	});

	QUnit.test("Test: \"add/remove external reference\"", function (assert) {
		// 1.Ref
		//'[new.xlsx]Sheet1'!A1
		let tempLink = '[new.xlsx]';
		let parseResult = new AscCommonExcel.ParseResult([]);
		let cellWithFormula = new AscCommonExcel.CCellWithFormula(ws, 1, 0);

		oParser = new parserFormula("SUM(" + "'" + tempLink + "Sheet1" + "'" + "!A1)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM(" + "'" + tempLink + "Sheet1" + "'" + "!A1)");
		assert.strictEqual(oParser.calculate().getValue(), "#NAME?", '#NAME!');

		assert.strictEqual(wb.externalReferences.length, 0, 'SUM_1_external_reference_length_before_add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'SUM_1_external_reference_length_after_add');

		oParser.isParsed = false;
		assert.ok(oParser.parse(true, null, parseResult), "SUM(" + "'" + tempLink + "Sheet1" + "'" + "!A1)");
		assert.strictEqual(oParser.calculate().getValue(), "#REF!", 'result after add reference');

		//update external reference structure
		initReference(wb.externalReferences[0], "Sheet1", "A1", [["1000"]], true);
		assert.strictEqual(oParser.calculate().getValue(), 1000, 'EXTERNAL_1_AFTER_INIT');

		//create new ws and put date
		let externalWs = createExternalWorksheet("Sheet1");
		externalWs.getRange2("A1").setValue("2000");
		//such as update from portal
		wb.externalReferences[0].updateData([externalWs]);
		assert.strictEqual(oParser.calculate().getValue(), 2000, 'EXTERNAL_2_AFTER_UPDATE');

		//remove external reference
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0, 'external_reference_length_after_delete');

		// 2.Area
		//'[new.xlsx]Sheet1'!A1:A2
		parseResult = new AscCommonExcel.ParseResult([]);
		oParser = new parserFormula("SUM(" + "'" + tempLink + "Sheet1" + "'" + "!A1:A2)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM(" + "'" + tempLink + "Sheet1" + "'" + "!A1:A2)");
		assert.strictEqual(oParser.calculate().getValue(), "#NAME?", '#NAME!');

		assert.strictEqual(wb.externalReferences.length, 0, 'SUM_2_external_reference_length_before_add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'SUM_2_external_reference_length_after_add');

		oParser.isParsed = false;
		assert.ok(oParser.parse(true, null, parseResult), "SUM(" + "'" + tempLink + "Sheet1" + "'" + "!A1:A2)");
		assert.strictEqual(oParser.calculate().getValue(), 0, 'result after add area');

		//update external reference structure
		initReference(wb.externalReferences[0], "Sheet1", "A1:A2", [["1000"],["2000"]], true);
		assert.strictEqual(oParser.calculate().getValue(), 3000, 'EXTERNAL_AREA_1_AFTER_INIT');

		//create new ws and put date
		externalWs = createExternalWorksheet("Sheet1");
		externalWs.getRange2("A1").setValue("2000");
		externalWs.getRange2("A2").setValue("4000");
		//such as update from portal
		wb.externalReferences[0].updateData([externalWs]);
		assert.strictEqual(oParser.calculate().getValue(), 6000, 'EXTERNAL_AREA_2_AFTER_UPDATE');

		//remove external reference
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0, 'external_area_length_after_delete');

		// 3. Name
		//'[new.xlsx]Sheet1'!test
		oParser = new parserFormula("SUM(" + "'" + tempLink + "Sheet1" + "'" + "!test)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM(" + "'" + tempLink + "Sheet1" + "'" + "!test)");
		assert.strictEqual(oParser.calculate().getValue(), "#NAME?", '#NAME!');

		assert.strictEqual(wb.externalReferences.length, 0, 'SUM_2_external_reference_length_before_add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'SUM_2_external_reference_length_after_add');

		//update external reference structure
		let externalWb = wb.externalReferences[0].getWb();
		let exWs = wb.externalReferences[0].worksheets["Sheet1"];
		externalWb.insertWorksheet(0, exWs);
		//on parse name3d use g_DefNameWorksheet
		let RealDefNameWorksheet = AscCommonExcel.g_DefNameWorksheet;
		AscCommonExcel.g_DefNameWorksheet = exWs;
		let oDefName = new Asc.asc_CDefName("test", "Sheet1!" + "$A$1:$A$2");
		externalWb.editDefinesNames(null, oDefName);
		AscCommonExcel.g_DefNameWorksheet = RealDefNameWorksheet;


		oParser.isParsed = false;
		oParser.outStack = [];
		assert.ok(oParser.parse(true, null, parseResult), "SUM(" + "'" + tempLink + "Sheet1" + "'" + "!test)");
		assert.strictEqual(oParser.calculate().getValue(), 0, 'result after add name');

		initDefinedName(wb.externalReferences[0], "Sheet1", "A1:A2", "test");
		initReference(wb.externalReferences[0], "Sheet1", "A1:A2", [["1000"],["2000"]], true);
		assert.strictEqual(oParser.calculate().getValue(), 3000, 'EXTERNAL_NAME_1_AFTER_INIT');

		//create new ws and put date
		externalWs = createExternalWorksheet("Sheet1");
		externalWs.getRange2("A1").setValue("2000");
		externalWs.getRange2("A2").setValue("4000");
		//such as update from portal
		wb.externalReferences[0].updateData([externalWs]);
		assert.strictEqual(oParser.calculate().getValue(), 6000, 'EXTERNAL_NAME_2_AFTER_UPDATE');

		//remove external reference
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0, 'external_name_length_after_delete');
		
		// 4. Multiple reference in one string
		//'[new.xlsx]Sheet1'!A1+'[new2.xlsx]Sheet1'!A1
		let secondLink = '[new2.xlsx]';
		oParser = new parserFormula("SUM(" + "'" + tempLink + "Sheet1" + "'" + "!A1+" + "'" + secondLink + "Sheet22" + "'" + "!A1" +")", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM(" + "'" + tempLink + "Sheet1" + "'" + "!test)");
		assert.strictEqual(oParser.calculate().getValue(), "#NAME?", '#NAME!');

		assert.strictEqual(wb.externalReferences.length, 0, 'SUM_2_external_reference_length_before_add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 2, 'SUM_2_external_reference_length_after_add');

		//remove two external reference
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0, 'external_name_length_after_delete');
	});

	QUnit.test("Test: \"parse external reference tests\"", function (assert) {
		let cellWithFormula = new AscCommonExcel.CCellWithFormula(ws, 1, 0);
		let parseResult = new AscCommonExcel.ParseResult([]);
		oParser = new parserFormula("'[book.xlsx]Sheet 1'!A1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[book.xlsx]Sheet 1'!A1");

		oParser = new parserFormula("'[book.xlsx]Sheet1'!A1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[book.xlsx]Sheet 1'!A1");

		// for bug 69677
		oParser = new parserFormula("'[book.xlsx]Sheet1'!A1:B2", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[book.xlsx]Sheet1'!A1:B2");
		assert.strictEqual(oParser.calculate().type, AscCommonExcel.cElementType.error, 'Result before add reference');
		assert.strictEqual(wb.externalReferences.length, 0, 'Reference length before add');

		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'Reference length after add');
		
		oParser = new parserFormula("'[book.xlsx]Sheet1'!A1:B2", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[book.xlsx]Sheet1'!A1:B2");
		assert.strictEqual(oParser.calculate().type, AscCommonExcel.cElementType.cellsRange3D, 'Result after add reference');

		// clear eR
		wb.externalReferences.length = 0;
	});

	QUnit.test("Test: \"Change external reference tests\"", function (assert) {
		let fLink = '[new.xlsx]';
		let sLink = '[new(1).xlsx]';
		let parseResult = new AscCommonExcel.ParseResult([]);
		let cellWithFormula = new AscCommonExcel.CCellWithFormula(ws, 1, 0);
		
		oParser = new parserFormula("'" + fLink + "Sheet1" + "'" + "!A1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'" + fLink + "Sheet1" + "'" + "!A1");
		assert.strictEqual(oParser.calculate().getValue(), "#NAME?", '#NAME!');

		assert.strictEqual(wb.externalReferences.length, 0, 'Reference length before add the first link');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'Reference length before add the second link');

		oParser.isParsed = false;
		assert.ok(oParser.parse(true, null, parseResult), "'" + fLink + "Sheet1" + "'" + "!A1");
		assert.strictEqual(oParser.calculate().getValue().getValue(), "#REF!", 'result after add reference');

		//update external reference structure
		initReference(wb.externalReferences[0], "Sheet1", "A1", [["1000"]], true);
		assert.strictEqual(oParser.calculate().getValue().getValue(), 1000, 'EXTERNAL_AFTER_INIT');

		let externalWs = createExternalWorksheet("Sheet1");
		externalWs.getRange2("A1").setValue("2000");
		wb.externalReferences[0].updateData([externalWs]);
		assert.strictEqual(oParser.calculate().getValue().getValue(), 2000, 'EXTERNAL_AFTER_UPDATE');

		// add the second link
		oParser = new parserFormula("'" + sLink + "Sheet1" + "'" + "!A1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'" + sLink + "Sheet1" + "'" + "!A1");
		assert.strictEqual(oParser.calculate().getValue(), "#NAME?", '#NAME!');

		assert.strictEqual(wb.externalReferences.length, 1, 'Reference length before add the second link');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 2, 'Reference length after add the second link');

		oParser.isParsed = false;
		assert.ok(oParser.parse(true, null, parseResult), "'" + sLink + "Sheet1" + "'" + "!A1");
		assert.strictEqual(oParser.calculate().getValue().getValue(), "#REF!", 'result after add reference');

		initReference(wb.externalReferences[1], "Sheet1", "A1", [["1111"]], true);
		assert.strictEqual(oParser.calculate().getValue().getValue(), 1111, 'EXTERNAL_AFTER_INIT');

		let secondExternalWs = createExternalWorksheet("Sheet1");
		secondExternalWs.getRange2("A1").setValue("2222");
		wb.externalReferences[1].updateData([secondExternalWs]);
		assert.strictEqual(oParser.calculate().getValue().getValue(), 2222, 'EXTERNAL_AFTER_UPDATE');


		ws.getRange2("A100").setValue("='[new.xlsx]Sheet1'!A1");
		assert.strictEqual(wb.externalReferences.length, 2, 'Amount of references before changing a cell');
		ws.getRange2("A100").setValue("1");
		assert.strictEqual(wb.externalReferences.length, 1, 'Amount of references after changing a cell with the reference');

	});

	QUnit.test("Test: \"Access to external reference tests\"", function (assert) {
		// for bug 69792
		let cellWithFormula = new AscCommonExcel.CCellWithFormula(ws, 1, 0);
		let parseResult = new AscCommonExcel.ParseResult([]);
		oParser = new parserFormula("'[book.xlsx]Sheet 1'!A1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[book.xlsx]Sheet 1'!A1");

		oParser = new parserFormula("'[book.xlsx]Sheet1'!A1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[book.xlsx]Sheet1'!A1");

		// set extrefs to 0
		wb.externalReferences.length = 0;

		assert.strictEqual(wb.externalReferences.length, 0, 'External reference length before add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'External reference length after add');

		oParser = new parserFormula("'[book.xlsx]Sheet2'!A1", cellWithFormula, ws);
		// todo fix bug 71020 breaks this check
		// assert.strictEqual(oParser.parse(true, null, parseResult), false, "Trying to access not existed sheet in existed externalRef");

		assert.strictEqual(wb.externalReferences.length, 1);

		//remove external reference
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0);
	});

	QUnit.test("Test: \"Check short links parse\"", function (assert) {
		// create ext link
		// check parser formula - simulate reading a string like [linkIndex] + "SheetName" + "!" + "ReferenceTo"
		let fileName = window["Asc"]["editor"].DocInfo && window["Asc"]["editor"].DocInfo.get_Title();
		let fullLinkLocal = "'[book.xlsx]Sheet1'!A1",
			fullLinkDefnameLocal = "'[book.xlsx]Sheet1'!_s1",
			fullLink = "'[1]Sheet1'!A1",
			fullLinkDefname = "'[1]Sheet1'!_s1",
			shortLinkLocal = "'[book.xlsx]'!A1",
			shortLinkDefnameLocal = "[book.xlsx]!_s1",
			shortLinkDefnameLocalWithoutBrackets = "book.xlsx!_s1",
			shortLinkDefnameLocalWithoutBrackets2 = "'book.xlsx'!_s1",
			shortLink = "[1]!A1",
			shortLinkDefname = "[1]!_s1",
			shortLinkDefname2 = "'[1]'!_s1",
			shortLinkDefnameWithoutBrackets = "'1'!_s1",
			externalWs;
		
		let elemInStack;
		// create external link
		let cellWithFormula = new AscCommonExcel.CCellWithFormula(ws, 1, 0);
		let parseResult = new AscCommonExcel.ParseResult([]);
		oParser = new parserFormula(fullLinkDefnameLocal, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), fullLinkDefnameLocal);

		// set extrefs to 0
		wb.externalReferences.length = 0;

		assert.strictEqual(wb.externalReferences.length, 0, 'External reference length before add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'External reference length after add');
		initDefinedName(wb.externalReferences[0], "Sheet1", "A1:A2", "_s1");

		externalWs = createExternalWorksheet("Sheet1");
		externalWs.getRange2("A1").setValue("10");
		externalWs.getRange2("A2").setValue("20");

		wb.externalReferences[0].updateData([externalWs]);
		// defNames.wb[this.Name].getRef();
		// wb.externalReferences[0].addDefName()

		// add defname to current workbook([0] tests)
		wb.dependencyFormulas.addDefName("currentDef", "Sheet2!$A$1:$B$2");
		wb.createWorksheet(0, "Sheet2");

		ws.getRange2("A1:A1000").cleanAll();

		// local = false. Read/open file with formulas. Try to parse string to external ref similiar as read the file
		oParser = new parserFormula(fullLink, cellWithFormula, ws);
		assert.ok(oParser.parse(false/*isLocal*/, null, parseResult), "Full link. isLocal = false. " + fullLink);

		oParser = new parserFormula(fullLinkDefname, cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "Full link to defname. isLocal = false. " + fullLinkDefname);

		oParser = new parserFormula(shortLink, cellWithFormula, ws);
		assert.ok(!oParser.parse(false, null, parseResult), "Short link. isLocal = false. " + shortLink);

		oParser = new parserFormula(shortLinkDefname, cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "Short link to defname. isLocal = false. " + shortLinkDefname);
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "_s1");
			assert.ok(elemInStack.ws);
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, "Sheet1");
		}

		oParser = new parserFormula("[1]!_s223", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "Short link to defname that not exist. isLocal = false. " + "[1]!_s223");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "_s223");
			assert.ok(elemInStack.ws);
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, "Sheet1");
		}

		// inside the formula tests
		oParser = new parserFormula("SUM([1]!_s1)", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "SUM([1]!_s1). isLocal = false");

		oParser = new parserFormula("SUM('[1]'!_s1)", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult) === false, "SUM('[1]'!_s1). isLocal = false");

		oParser = new parserFormula("SUM([1]!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "SUM([1]!_s1,2,3). isLocal = false");

		oParser = new parserFormula("SUM('[1]'!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult) === false, "SUM('[1]'!_s1,2,3). isLocal = false");

		/* Links to current file check. Formula in file have format "[0]!defname" */
		oParser = new parserFormula("[0]!currentDef", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "[0]!currentDef. isLocal = false. Link to existing defname in current wb");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "currentDef");
			assert.ok(elemInStack.ws);
			assert.ok(elemInStack.shortLink);
			assert.strictEqual(elemInStack.externalLink, "0");
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, "Sheet2", "Defname location");
			assert.ok(wb.getDefinesNames(elemInStack.value), "Defname exist on second sheet");
		}

		oParser = new parserFormula("SUM([0]!currentDef,2,3,4)", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "SUM([0]!currentDef,2,3,4). isLocal = false. Link to existing defname in current wb inside the formula");

		oParser = new parserFormula("[0]!_nonExistentDefname", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "[0]!_nonExistentDefname. isLocal = false. Link to non-existent defname in current wb");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "_nonExistentDefname");
			assert.ok(elemInStack.ws);
			assert.ok(elemInStack.shortLink);
			assert.strictEqual(elemInStack.externalLink, "0");
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, wb.getActiveWs() &&  wb.getActiveWs().getName(), "Location for WS in cName3D by default");
			assert.ok(!wb.getDefinesNames(elemInStack.value), "Defname doesn't exist");
		}

		oParser = new parserFormula("SUM([0]!_nonExistentDefname,2,3,4)", cellWithFormula, ws);
		assert.ok(oParser.parse(false, null, parseResult), "SUM([0]!_nonExistentDefname,2,3,4). isLocal = false. Link to non-existent defname in current wb inside the formula");

		// local = true. Manual input. Try parse string to external ref similiar as writing a string manually
		/* Links to current file check. Formula is typed as "filename.xlsx!defname" */
		oParser = new parserFormula(fileName + "!currentDef", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), fileName +"!currentDef. isLocal = true. Link to existing defname in current wb");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "currentDef");
			assert.ok(elemInStack.ws);
			assert.ok(elemInStack.shortLink);
			assert.strictEqual(elemInStack.externalLink, "0");
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, "Sheet2", "Defname location");
			assert.ok(wb.getDefinesNames(elemInStack.value), "Defname exist on the second sheet");
		}

		oParser = new parserFormula(fileName + "!_nonExistentDefname", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), fileName +"!_nonExistentDefname. isLocal = true. Link to non-existent defname in current wb");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "_nonExistentDefname");
			assert.ok(elemInStack.ws);
			assert.ok(elemInStack.shortLink);
			assert.strictEqual(elemInStack.externalLink, "0");
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, wb.getActiveWs() && wb.getActiveWs().getName(), "Location for WS in cName3D by default");
			assert.ok(!wb.getDefinesNames(elemInStack.value), "Defname doesn't exist");
		}

		// same wb, full link tests
		oParser = new parserFormula("'[" + fileName + "]Sheet1'!currentDef", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[" + fileName + "]Sheet1'!currentDef. isLocal = true. Link to existing defname in current wb");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "currentDef");
			assert.ok(elemInStack.ws);
			assert.ok(elemInStack.shortLink);
			assert.strictEqual(elemInStack.externalLink, "0");
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, "Sheet2", "Defname location");
			assert.ok(wb.getDefinesNames(elemInStack.value), "Defname exist on the second sheet");
		}

		oParser = new parserFormula("'[" + fileName + "]Sheet1'!_nonExistentDefname", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[" + fileName + "]Sheet1'!_nonExistentDefname. isLocal = true. Link to non-existent defname in current wb");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.name3D)) {
			assert.strictEqual(elemInStack.value, "_nonExistentDefname");
			assert.ok(elemInStack.ws);
			assert.ok(elemInStack.shortLink);
			assert.strictEqual(elemInStack.externalLink, "0");
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, wb.getActiveWs() && wb.getActiveWs().getName(), "Location for WS in cName3D by default");
			assert.ok(!wb.getDefinesNames(elemInStack.value), "Defname doesn't exist");
		}

		oParser = new parserFormula("'[" + fileName + "]Sheet1'!A1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[" + fileName + "]Sheet1'!A1. isLocal = true. Link to A1 cell");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.cell3D)) {
			assert.strictEqual(elemInStack.value, "A1");
			assert.ok(elemInStack.ws);
			assert.ok(elemInStack.externalLink == null);
			assert.strictEqual(elemInStack.ws && elemInStack.ws.sName, "Sheet1", "Location for WS in cRef3D");
		}

		oParser = new parserFormula("'[" + fileName + "]Sheet1'!A1:A2", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[" + fileName + "]Sheet1'!A1:A2. isLocal = true. Link to A1:A2 range");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.cellsRange3D)) {
			assert.strictEqual(elemInStack.value, "A1:A2");
			assert.ok(elemInStack.wsFrom);
			assert.ok(elemInStack.externalLink == null);
			assert.strictEqual(elemInStack.wsFrom && elemInStack.wsFrom.sName, "Sheet1", "Location for WS in cArea3D");
		}

		oParser = new parserFormula("'[" + fileName + "]Sheet1'!$A$1:$B$2", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[" + fileName + "]Sheet1'!$A$1:$B$2. isLocal = true. Link to $A$1:$B$2 range");
		elemInStack = oParser.outStack && oParser.outStack[0];
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.cellsRange3D)) {
			assert.strictEqual(elemInStack.value, "$A$1:$B$2");
			assert.ok(elemInStack.wsFrom);
			assert.ok(elemInStack.externalLink == null);
			assert.strictEqual(elemInStack.wsFrom && elemInStack.wsFrom.sName, "Sheet1", "Location for WS in cArea3D");
		}

		oParser = new parserFormula("'[" + fileName + "]Sheet1'!A:A", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[" + fileName + "]Sheet1'!A:A. isLocal = true. Link to A:A range");
		elemInStack = oParser.outStack && oParser.outStack[0];
		assert.strictEqual(elemInStack && elemInStack.type, AscCommonExcel.cElementType.cellsRange3D, "Type of single elem in outstack");
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.cellsRange3D)) {
			assert.strictEqual(elemInStack.value, "A:A");
			assert.ok(elemInStack.wsFrom);
			assert.ok(elemInStack.externalLink == null);
			assert.strictEqual(elemInStack.wsFrom && elemInStack.wsFrom.sName, "Sheet1", "Location for WS in cArea3D");
		}

		oParser = new parserFormula("'[" + fileName + "]Sheet1'!$A:$A", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'[" + fileName + "]Sheet1'!$A:$A. isLocal = true. Link to A:A range");
		elemInStack = oParser.outStack && oParser.outStack[0];
		assert.strictEqual(elemInStack && elemInStack.type, AscCommonExcel.cElementType.cellsRange3D, "Type of single elem in outstack");
		if (elemInStack && (elemInStack.type === AscCommonExcel.cElementType.cellsRange3D)) {
			assert.strictEqual(elemInStack.value, "$A:$A");
			assert.ok(elemInStack.wsFrom);
			assert.ok(elemInStack.externalLink == null);
			assert.strictEqual(elemInStack.wsFrom && elemInStack.wsFrom.sName, "Sheet1", "Location for WS in cArea3D");
		}
		
		oParser = new parserFormula("SUM('[" + fileName + "]Sheet1'!A:A,0)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('[" + fileName + "]Sheet1'!A:A,0). isLocal = true. Link to A:A range inside formula");
		assert.strictEqual(oParser.calculate().getValue(), 0, "SUM('[" + fileName + "]Sheet1'!A:A,0)");

		oParser = new parserFormula("SUM('[" + fileName + "]Sheet1'!$A:$A,0)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('[" + fileName + "]Sheet1'!$A:$A,0). isLocal = true. Link to $A:$A range inside formula");
		assert.strictEqual(oParser.calculate().getValue(), 0, "SUM('[" + fileName + "]Sheet1'!$A:$A,0)");

		// todo sheets with names like "S!he!et!25", ",;", ",; ,; !ds!'ds!" they will not parse with the current scheme with a link to the current file 
		// for example: '[filename]S!he!et!25'!A1 or '[filename],; ,; !ds!'ds!'!A1. MS works correctly with such names.

		oParser = new parserFormula("'[" + fileName + "]Sheet222'!currentDef", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "'[" + fileName + "]Sheet222'!currentDef. isLocal = true. Link to existing defname on a non-existent sheet");

		oParser = new parserFormula("'[" + fileName + "]Non!'!@ExistentSheet'!currentDef", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "'[" + fileName + "]Non!'!@ExistentSheet'!currentDef. isLocal = true. Link to existing defname on a non-existent sheet");

		oParser = new parserFormula(fullLinkLocal, cellWithFormula, ws);
		assert.ok(oParser.parse(true/*isLocal*/, null, parseResult), "Full link. isLocal = true. " + fullLinkLocal);

		oParser = new parserFormula(fullLinkDefnameLocal, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "Full link to defname. isLocal = true. " + fullLinkDefnameLocal);
		
		oParser = new parserFormula(shortLinkLocal, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "Short link. isLocal = true. " + shortLinkLocal);

		oParser = new parserFormula(shortLinkDefnameLocal, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "Short link to defname. isLocal = true. " + shortLinkDefnameLocal);

		oParser = new parserFormula(shortLinkDefnameLocalWithoutBrackets, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "Short link to defname without brackets. isLocal = true. " + shortLinkDefnameLocalWithoutBrackets);

		oParser = new parserFormula(shortLinkDefnameLocalWithoutBrackets2, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "Short link to defname without brackets and with single quotes. isLocal = true. " + shortLinkDefnameLocalWithoutBrackets2);

		oParser = new parserFormula(shortLink, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "Short link from file as local. isLocal = true. " + shortLink);

		oParser = new parserFormula(shortLinkDefname, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "Short link to defname from file as local. isLocal = true. " + shortLinkDefname);

		oParser = new parserFormula(shortLinkDefname2, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "Short link to defname with quotes from file as local. isLocal = true. " + shortLinkDefname2);

		oParser = new parserFormula(shortLinkDefnameWithoutBrackets, cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "Short link to defname with quotes & without brackets from file as local. isLocal = true. " + shortLinkDefnameWithoutBrackets);

		oParser = new parserFormula("book(20).xlsx!_s1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "book(20).xlsx!_s1. isLocal = true");

		oParser = new parserFormula("'book(20).xlsx'!_s1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'book(20).xlsx'!_s1. isLocal = true");

		oParser = new parserFormula("123book(20).xlsx!_s1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "123book(20).xlsx!_s1. isLocal = true");

		oParser = new parserFormula("'123book(20).xlsx'!_s1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'123book(20).xlsx'!_s1. isLocal = true");

		// inside the formula tests
		oParser = new parserFormula("SUM(test.xlsx!_s1)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM(test.xlsx!_s1). isLocal = true");

		oParser = new parserFormula("SUM('test.xlsx'!_s1)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('test.xlsx'!_s1). isLocal = true");

		oParser = new parserFormula("SUM(test.xlsx!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM(test.xlsx!_s1,2,3). isLocal = true");

		oParser = new parserFormula("SUM('test.xlsx'!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('test.xlsx'!_s1,2,3). isLocal = true");

		oParser = new parserFormula("SUM(test(20).xlsx!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "SUM(test(20).xlsx!_s1,2,3). isLocal = true");

		oParser = new parserFormula("SUM('test(20).xlsx'!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('test(20).xlsx'!_s1,2,3). isLocal = true");

		oParser = new parserFormula("SUM(123test(20).xlsx!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "SUM(123test(20).xlsx!_s1,2,3). isLocal = true");

		oParser = new parserFormula("SUM('123test(20).xlsx'!_s1,2,3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('123test(20).xlsx'!_s1,2,3). isLocal = true");

		// todo on the desktop, the file selection window opens three times, one after another
		oParser = new parserFormula("SUM(book.xlsx!_s1,book2.xlsx!_s2,book3.xlsx!_s3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM(book.xlsx!_s1,book2.xlsx!_s2,book3.xlsx!_s3). isLocal = true");

		oParser = new parserFormula("SUM('book.xlsx'!_s1,book2.xlsx!_s2,book3.xlsx!_s3)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('book.xlsx'!_s1,book2.xlsx!_s2,book3.xlsx!_s3). isLocal = true");

		oParser = new parserFormula("SUM('123test(20).xlsx'!_s1, 123test(20).xlsx!_s1)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult) === false, "SUM('123test(20).xlsx'!_s1, 123test(20).xlsx!_s1). isLocal = true");

		oParser = new parserFormula("SUM('123test(20).xlsx'!_s1, '123test(20).xlsx'!_s1)", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "SUM('123test(20).xlsx'!_s1, '123test(20).xlsx'!_s1). isLocal = true");

		// clear er
		wb.externalReferences.length = 0;

		/* create new eR with temporary ws, which will be deleted */
		cellWithFormula = new AscCommonExcel.CCellWithFormula(ws, 1, 0);
		parseResult = new AscCommonExcel.ParseResult([]);
		oParser = new parserFormula("'book.xlsx'!_s22", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "'book.xlsx'!_s22 - local short link with refernce to non existed defname");

		assert.strictEqual(wb.externalReferences.length, 0, 'External reference length before add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'External reference length after add');

		let ER = wb.externalReferences[0];
		initDefinedName(ER, "book.xlsx", "A1:A2", "_s22", true);

		externalWs = createExternalWorksheet("Sheet1");
		externalWs.getRange2("A1").setValue("10");
		externalWs.getRange2("A2").setValue("20");

		let externalWb = ER.getWb();
		externalWb.insertWorksheet(0, externalWs);
		ER.addSheet(externalWs);

		assert.strictEqual(ER.SheetNames.length, 2, "Amount of sheets before updateData");
		assert.strictEqual(ER.SheetDataSet.length, 2, "Amount of SheetDataSet before updateData");
		assert.strictEqual(ER.SheetDataSet[0].SheetId, 0, "SheetDataSet id before updateData");
		assert.strictEqual(ER.SheetDataSet[1].SheetId, 1, "SheetDataSet id before updateData");

		ER.updateData([externalWs], null, null, wb);

		assert.strictEqual(ER.SheetNames.length, 1, "Amount of sheets after updateData `received` data");
		assert.strictEqual(ER.SheetDataSet.length, 1, "Amount of SheetDataSet after updateData `received` data");
		assert.strictEqual(ER.SheetDataSet[0].SheetId, 0, "SheetDataSet id after updateData and shift id's");

		//remove external reference
		wb.removeExternalReferences([wb.externalReferences[0].getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0);
	});


	QUnit.test("Test: \"Read and init external reference data\"", function (assert) {
		// create external link
		let cellWithFormula = new AscCommonExcel.CCellWithFormula(ws, 1, 0);
		let parseResult = new AscCommonExcel.ParseResult([]);

		oParser = new parserFormula("[book.xlsx]Sheet1!_s1", cellWithFormula, ws);
		assert.ok(oParser.parse(true, null, parseResult), "book.xlsx!_s1");

		// set extrefs to 0
		wb.externalReferences.length = 0;

		assert.strictEqual(wb.externalReferences.length, 0, 'External reference length before add');
		wb.addExternalReferencesAfterParseFormulas(parseResult.externalReferenesNeedAdd);
		assert.strictEqual(wb.externalReferences.length, 1, 'External reference length after add');
		
		let eR = wb.externalReferences[0];
		let externalWb = eR.getWb();
		let externalWs;

		initDefinedName(eR, "Sheet1", "A1:A2", "_s1");
		initReference(eR, "Sheet1", "A1:A2", [["10"],["20"]], true);

		let newDefname = eR.DefinedNames[0].clone();
		newDefname.RefersTo = "='Sheet1'!$A$1:$A$2";
		newDefname.SheetId = null;

		eR.DefinedNames[0] = newDefname;

		externalWs = createExternalWorksheet("Sheet1");
		externalWs.getRange2("A1").setValue("10");
		externalWs.getRange2("A2").setValue("20");

		eR.updateData([externalWs]);

		// defname listeners check
		assert.strictEqual(Object.keys(wb.dependencyFormulas.defNameListeners).length, 0, 'Defname listeners before setValue into cell');
		ws.getRange2("A1").setValue("='book.xlsx'!_s1");
		assert.strictEqual(Object.keys(wb.dependencyFormulas.defNameListeners).length, 1, 'Defname listeners after setValue into cell');
		
		// defnames in external wb check
		assert.strictEqual(Object.keys(externalWb.dependencyFormulas.defNames.wb).length, 0, 'Defnames before init');
		eR.initPostOpen();
		assert.strictEqual(Object.keys(externalWb.dependencyFormulas.defNames.wb).length, 1, 'Defnames after init');


		/* add second sheet with '!' in name */
		externalWs = createExternalWorksheet(" 'Sheet!'!1");
		eR.addSheet(externalWs);

		// initReference(eR, " 'Sheet!'!1", "A1", [[""]], true);
		initDefinedName(eR, " 'Sheet!'!1", "A1:A2", "_s2");
		initReference(eR, " 'Sheet!'!1", "A1:A2", [["40"],["80"]], true);

		newDefname = eR.DefinedNames[1].clone();
		newDefname.RefersTo = "=' 'Sheet!'!1'!$A$1:$A$2";
		newDefname.SheetId = null;

		eR.DefinedNames[1] = newDefname;

		// externalWs.getRange2("A1").setValue("400");
		// externalWs.getRange2("A2").setValue("800");

		eR.updateData([externalWs]);

		assert.strictEqual(Object.keys(externalWb.dependencyFormulas.defNames.wb).length, 1, 'Defnames before second init');
		eR.initPostOpen();
		assert.strictEqual(Object.keys(externalWb.dependencyFormulas.defNames.wb).length, 2, 'Defnames after second init');

		assert.strictEqual(Object.keys(wb.dependencyFormulas.defNameListeners).length, 1, 'Defname listeners before setValue into cell');
		ws.getRange2("A2").setValue("='book.xlsx'!_s2");
		assert.strictEqual(Object.keys(wb.dependencyFormulas.defNameListeners).length, 2, 'Defname listeners after setValue into cell');

		//remove external reference and clear cells
		ws.getRange2("A1:A2").setValue("");

		wb.removeExternalReferences([eR.getAscLink()]);
		assert.strictEqual(wb.externalReferences.length, 0);
	});

	// Mocks for API Testing
	Asc.spreadsheet_api.prototype._init = function () {
		this._loadModules();
	};
	Asc.spreadsheet_api.prototype._loadFonts = function (fonts, callback) {
		callback();
	};
	AscCommonExcel.WorkbookView.prototype._calcMaxDigitWidth = function () {
	};
	AscCommonExcel.WorkbookView.prototype._init = function () {
	};
	AscCommonExcel.WorkbookView.prototype._isLockedUserProtectedRange = function (callback) {
		callback(true);
	};
	AscCommonExcel.WorkbookView.prototype._onWSSelectionChanged = function () {
	};
	AscCommonExcel.WorkbookView.prototype.showWorksheet = function () {
	};
	AscCommonExcel.WorkbookView.prototype.recalculateDrawingObjects = function () {
	};
	AscCommonExcel.WorkbookView.prototype.restoreFocus = function () {
	};
	AscCommonExcel.WorksheetView.prototype._init = function () {
	};
	AscCommonExcel.WorksheetView.prototype.updateRanges = function () {
	};
	AscCommonExcel.WorksheetView.prototype._autoFitColumnsWidth = function () {
	};
	AscCommonExcel.WorksheetView.prototype.cleanSelection = function () {
	};
	AscCommonExcel.WorksheetView.prototype._drawSelection = function () {
	};
	AscCommonExcel.WorksheetView.prototype._scrollToRange = function () {
	};
	AscCommonExcel.WorksheetView.prototype.draw = function () {
	};
	AscCommonExcel.WorksheetView.prototype._prepareDrawingObjects = function () {
	};
	AscCommonExcel.WorksheetView.prototype._initCellsArea = function () {
	};
	AscCommonExcel.WorksheetView.prototype.getZoom = function () {
	};
	AscCommonExcel.WorksheetView.prototype._prepareCellTextMetricsCache = function () {
	};

	AscCommon.baseEditorsApi.prototype._onEndLoadSdk = function () {
	};
	AscCommonExcel.WorksheetView.prototype._isLockedCells = function (oFromRange, subType, callback) {
		callback(true);
		return true;
	};
	AscCommonExcel.WorksheetView.prototype._isLockedAll = function (callback) {
		callback(true);
	};
	AscCommonExcel.WorksheetView.prototype._isLockedFrozenPane = function (callback) {
		callback(true);
	};
	AscCommonExcel.WorksheetView.prototype._updateVisibleColsCount = function () {
	};
	AscCommonExcel.WorksheetView.prototype._calcActiveCellOffset = function () {
	};

	AscCommon.baseEditorsApi.prototype._onEndLoadSdk = function () {
	};
	Asc.ReadDefTableStyles = function () {
	};

	wb.dependencyFormulas.unlockRecal();
});