name:
dist/Formulas/Text.js
-rw-r--r--
45755
1"use strict";
2exports.__esModule = true;
3var ArgsChecker_1 = require("../Utilities/ArgsChecker");
4var TypeConverter_1 = require("../Utilities/TypeConverter");
5var Errors_1 = require("../Errors");
6var Filter_1 = require("../Utilities/Filter");
7var MoreUtils_1 = require("../Utilities/MoreUtils");
8/**
9 * Computes the value of a Roman numeral.
10 * @param text - The Roman numeral to format, whose value must be between 1 and 3999, inclusive.
11 * @returns {number} value in integer format
12 * @constructor
13 */
14var ARABIC = function (text) {
15 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "ARABIC");
16 if (typeof text !== "string") {
17 throw new Errors_1.ValueError('Invalid roman numeral in ARABIC evaluation.');
18 }
19 var negative = false;
20 if (text[0] === "-") {
21 negative = true;
22 text = text.substr(1);
23 }
24 // Credits: Rafa? Kukawski
25 if (!/^M*(?:D?C{0,3}|C[MD])(?:L?X{0,3}|X[CL])(?:V?I{0,3}|I[XV])$/.test(text)) {
26 throw new Errors_1.ValueError('Invalid roman numeral in ARABIC evaluation.');
27 }
28 var r = 0;
29 text.replace(/[MDLV]|C[MD]?|X[CL]?|I[XV]?/g, function (i) {
30 r += { M: 1000, CM: 900, D: 500, CD: 400, C: 100, XC: 90, L: 50, XL: 40, X: 10, IX: 9, V: 5, IV: 4, I: 1 }[i];
31 });
32 if (negative) {
33 return r * -1;
34 }
35 return r;
36};
37exports.ARABIC = ARABIC;
38/**
39 * Convert a number into a character according to the current Unicode table.
40 * @param value - The number of the character to look up from the current Unicode table in decimal format.
41 * @returns {string} character corresponding to Unicode number
42 * @constructor
43 */
44var CHAR = function (value) {
45 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "CHAR");
46 var n = TypeConverter_1.TypeConverter.firstValueAsNumber(value);
47 if (n < 1 || n > 1114112) { //limit
48 throw new Errors_1.NumError("Function CHAR parameter 1 value " + n + " is out of range.");
49 }
50 return String.fromCharCode(n);
51};
52exports.CHAR = CHAR;
53/**
54 * Returns the numeric Unicode map value of the first character in the string provided.
55 * @param value - The string whose first character's Unicode map value will be returned.
56 * @returns {number} number of the first character's Unicode value
57 * @constructor
58 */
59var CODE = function (value) {
60 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "CODE");
61 var text = TypeConverter_1.TypeConverter.firstValueAsString(value);
62 if (text === "") {
63 throw new Errors_1.ValueError("Function CODE parameter 1 value should be non-empty.");
64 }
65 return text.charCodeAt(0);
66};
67exports.CODE = CODE;
68/**
69 * Divides text around a specified character or string, and puts each fragment into a separate cell in the row.
70 * @param text - The text to divide.
71 * @param delimiter - The character or characters to use to split text.
72 * @param splitByEach - [optional] Whether or not to divide text around each character contained in
73 * delimiter.
74 * @returns {Array<string>} containing the split
75 * @constructor
76 * TODO: At some point this needs to return a more complex type than Array. Needs to return a type that has a dimension.
77 */
78var SPLIT = function (text, delimiter, splitByEach) {
79 ArgsChecker_1.ArgsChecker.checkLengthWithin(arguments, 2, 3, "SPLIT");
80 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
81 delimiter = TypeConverter_1.TypeConverter.firstValueAsString(delimiter);
82 splitByEach = splitByEach === undefined ? false : TypeConverter_1.TypeConverter.firstValueAsBoolean(splitByEach);
83 if (splitByEach) {
84 var result = [text];
85 for (var i = 0; i < delimiter.length; i++) {
86 var char = delimiter[i];
87 var subResult = [];
88 for (var x = 0; x < result.length; x++) {
89 subResult = subResult.concat(result[x].split(char));
90 }
91 result = subResult;
92 }
93 return result.filter(function (val) {
94 return val.trim() !== "";
95 });
96 }
97 else {
98 return text.split(delimiter);
99 }
100};
101exports.SPLIT = SPLIT;
102/**
103 * Appends strings to one another.
104 * @param values - to append to one another. Must contain at least one value
105 * @returns {string} concatenated string
106 * @constructor
107 */
108var CONCATENATE = function () {
109 var values = [];
110 for (var _i = 0; _i < arguments.length; _i++) {
111 values[_i] = arguments[_i];
112 }
113 ArgsChecker_1.ArgsChecker.checkAtLeastLength(values, 1, "CONCATENATE");
114 var string = '';
115 for (var i = 0; i < values.length; i++) {
116 if (values[i] instanceof Array) {
117 if (values[i].length === 0) {
118 throw new Errors_1.RefError("Reference does not exist.");
119 }
120 string += CONCATENATE.apply(this, arguments[i]);
121 }
122 else {
123 string += TypeConverter_1.TypeConverter.valueToString(values[i]);
124 }
125 }
126 return string;
127};
128exports.CONCATENATE = CONCATENATE;
129/**
130 * Converts a numeric value to a different unit of measure.
131 * @param value - the numeric value in start_unit to convert to end_unit.
132 * @param startUnit - The starting unit, the unit currently assigned to value.
133 * @param endUnit - The unit of measure into which to convert value.
134 * @returns {number}
135 * @constructor
136 * TODO: Looking up units is not efficient at all. We should use an object instead of iterating through an array.
137 */
138var CONVERT = function (value, startUnit, endUnit) {
139 ArgsChecker_1.ArgsChecker.checkLength(arguments, 3, "CONVERT");
140 var n = TypeConverter_1.TypeConverter.firstValueAsNumber(value);
141 var fromUnit = TypeConverter_1.TypeConverter.firstValueAsString(startUnit);
142 var toUnit = TypeConverter_1.TypeConverter.firstValueAsString(endUnit);
143 // NOTE: A lot of the code for this method is from https://github.com/sutoiku/formula.js. I'm relying on them to have
144 // gotten it right, but I'm spot checking some of their work against GS, MSE, LibreOffice, OpenOffice.
145 // List of units supported by CONVERT and units defined by the International System of Units
146 // [Name, Symbol, Alternate symbols, Quantity, ISU, CONVERT, Conversion ratio]
147 var units = [
148 ["a.u. of action", "?", null, "action", false, false, 1.05457168181818e-34],
149 ["a.u. of charge", "e", null, "electric_charge", false, false, 1.60217653141414e-19],
150 ["a.u. of energy", "Eh", null, "energy", false, false, 4.35974417757576e-18],
151 ["a.u. of length", "a?", null, "length", false, false, 5.29177210818182e-11],
152 ["a.u. of mass", "m?", null, "mass", false, false, 9.10938261616162e-31],
153 ["a.u. of time", "?/Eh", null, "time", false, false, 2.41888432650516e-17],
154 ["admiralty knot", "admkn", null, "speed", false, true, 0.514773333],
155 ["ampere", "A", null, "electric_current", true, false, 1],
156 ["ampere per meter", "A/m", null, "magnetic_field_intensity", true, false, 1],
157 ["ångström", "Å", ["ang"], "length", false, true, 1e-10],
158 ["are", "ar", null, "area", false, true, 100],
159 ["astronomical unit", "ua", null, "length", false, false, 1.49597870691667e-11],
160 ["bar", "bar", null, "pressure", false, false, 100000],
161 ["barn", "b", null, "area", false, false, 1e-28],
162 ["becquerel", "Bq", null, "radioactivity", true, false, 1],
163 ["bit", "bit", ["b"], "information", false, true, 1],
164 ["btu", "BTU", ["btu"], "energy", false, true, 1055.05585262],
165 ["byte", "byte", null, "information", false, true, 8],
166 ["candela", "cd", null, "luminous_intensity", true, false, 1],
167 ["candela per square metre", "cd/m?", null, "luminance", true, false, 1],
168 ["coulomb", "C", null, "electric_charge", true, false, 1],
169 ["cubic ångström", "ang3", ["ang^3"], "volume", false, true, 1e-30],
170 ["cubic foot", "ft3", ["ft^3"], "volume", false, true, 0.028316846592],
171 ["cubic inch", "in3", ["in^3"], "volume", false, true, 0.000016387064],
172 ["cubic light-year", "ly3", ["ly^3"], "volume", false, true, 8.46786664623715e-47],
173 ["cubic metre", "m?", null, "volume", true, true, 1],
174 ["cubic mile", "mi3", ["mi^3"], "volume", false, true, 4168181825.44058],
175 ["cubic nautical mile", "Nmi3", ["Nmi^3"], "volume", false, true, 6352182208],
176 ["cubic Pica", "Pica3", ["Picapt3", "Pica^3", "Picapt^3"], "volume", false, true, 7.58660370370369e-8],
177 ["cubic yard", "yd3", ["yd^3"], "volume", false, true, 0.764554857984],
178 ["cup", "cup", null, "volume", false, true, 0.0002365882365],
179 ["dalton", "Da", ["u"], "mass", false, false, 1.66053886282828e-27],
180 ["day", "d", ["day"], "time", false, true, 86400],
181 ["degree", "°", null, "angle", false, false, 0.0174532925199433],
182 ["degrees Rankine", "Rank", null, "temperature", false, true, 0.555555555555556],
183 ["dyne", "dyn", ["dy"], "force", false, true, 0.00001],
184 ["electronvolt", "eV", ["ev"], "energy", false, true, 1.60217656514141],
185 ["ell", "ell", null, "length", false, true, 1.143],
186 ["erg", "erg", ["e"], "energy", false, true, 1e-7],
187 ["farad", "F", null, "electric_capacitance", true, false, 1],
188 ["fluid ounce", "oz", null, "volume", false, true, 0.0000295735295625],
189 ["foot", "ft", null, "length", false, true, 0.3048],
190 ["foot-pound", "flb", null, "energy", false, true, 1.3558179483314],
191 ["gal", "Gal", null, "acceleration", false, false, 0.01],
192 ["gallon", "gal", null, "volume", false, true, 0.003785411784],
193 ["gauss", "G", ["ga"], "magnetic_flux_density", false, true, 1],
194 ["grain", "grain", null, "mass", false, true, 0.0000647989],
195 ["gram", "g", null, "mass", false, true, 0.001],
196 ["gray", "Gy", null, "absorbed_dose", true, false, 1],
197 ["gross registered ton", "GRT", ["regton"], "volume", false, true, 2.8316846592],
198 ["hectare", "ha", null, "area", false, true, 10000],
199 ["henry", "H", null, "inductance", true, false, 1],
200 ["hertz", "Hz", null, "frequency", true, false, 1],
201 ["horsepower", "HP", ["h"], "power", false, true, 745.69987158227],
202 ["horsepower-hour", "HPh", ["hh", "hph"], "energy", false, true, 2684519.538],
203 ["hour", "h", ["hr"], "time", false, true, 3600],
204 ["imperial gallon (U.K.)", "uk_gal", null, "volume", false, true, 0.00454609],
205 ["imperial hundredweight", "lcwt", ["uk_cwt", "hweight"], "mass", false, true, 50.802345],
206 ["imperial quart (U.K)", "uk_qt", null, "volume", false, true, 0.0011365225],
207 ["imperial ton", "brton", ["uk_ton", "LTON"], "mass", false, true, 1016.046909],
208 ["inch", "in", null, "length", false, true, 0.0254],
209 ["international acre", "uk_acre", null, "area", false, true, 4046.8564224],
210 ["IT calorie", "cal", null, "energy", false, true, 4.1868],
211 ["joule", "J", null, "energy", true, true, 1],
212 ["katal", "kat", null, "catalytic_activity", true, false, 1],
213 ["kelvin", "K", ["kel"], "temperature", true, true, 1],
214 ["kilogram", "kg", null, "mass", true, true, 1],
215 ["knot", "kn", null, "speed", false, true, 0.514444444444444],
216 ["light-year", "ly", null, "length", false, true, 9460730472580800],
217 ["litre", "L", ["l", "lt"], "volume", false, true, 0.001],
218 ["lumen", "lm", null, "luminous_flux", true, false, 1],
219 ["lux", "lx", null, "illuminance", true, false, 1],
220 ["maxwell", "Mx", null, "magnetic_flux", false, false, 1e-18],
221 ["measurement ton", "MTON", null, "volume", false, true, 1.13267386368],
222 ["meter per hour", "m/h", ["m/hr"], "speed", false, true, 0.00027777777777778],
223 ["meter per second", "m/s", ["m/sec"], "speed", true, true, 1],
224 ["meter per second squared", "m?s??", null, "acceleration", true, false, 1],
225 ["parsec", "pc", ["parsec"], "length", false, true, 30856775814671900],
226 ["meter squared per second", "m?/s", null, "kinematic_viscosity", true, false, 1],
227 ["metre", "m", null, "length", true, true, 1],
228 ["miles per hour", "mph", null, "speed", false, true, 0.44704],
229 ["millimetre of mercury", "mmHg", null, "pressure", false, false, 133.322],
230 ["minute", "?", null, "angle", false, false, 0.000290888208665722],
231 ["minute", "min", ["mn"], "time", false, true, 60],
232 ["modern teaspoon", "tspm", null, "volume", false, true, 0.000005],
233 ["mole", "mol", null, "amount_of_substance", true, false, 1],
234 ["morgen", "Morgen", null, "area", false, true, 2500],
235 ["n.u. of action", "?", null, "action", false, false, 1.05457168181818e-34],
236 ["n.u. of mass", "m?", null, "mass", false, false, 9.10938261616162e-31],
237 ["n.u. of speed", "c?", null, "speed", false, false, 299792458],
238 ["n.u. of time", "?/(me?c??)", null, "time", false, false, 1.28808866778687e-21],
239 ["nautical mile", "M", ["Nmi"], "length", false, true, 1852],
240 ["newton", "N", null, "force", true, true, 1],
241 ["œrsted", "Oe ", null, "magnetic_field_intensity", false, false, 79.5774715459477],
242 ["ohm", "Ω", null, "electric_resistance", true, false, 1],
243 ["ounce mass", "ozm", null, "mass", false, true, 0.028349523125],
244 ["pascal", "Pa", null, "pressure", true, false, 1],
245 ["pascal second", "Pa?s", null, "dynamic_viscosity", true, false, 1],
246 ["pferdestärke", "PS", null, "power", false, true, 735.49875],
247 ["phot", "ph", null, "illuminance", false, false, 0.0001],
248 ["pica (1/6 inch)", "pica", null, "length", false, true, 0.00035277777777778],
249 ["pica (1/72 inch)", "Pica", ["Picapt"], "length", false, true, 0.00423333333333333],
250 ["poise", "P", null, "dynamic_viscosity", false, false, 0.1],
251 ["pond", "pond", null, "force", false, true, 0.00980665],
252 ["pound force", "lbf", null, "force", false, true, 4.4482216152605],
253 ["pound mass", "lbm", null, "mass", false, true, 0.45359237],
254 ["quart", "qt", null, "volume", false, true, 0.000946352946],
255 ["radian", "rad", null, "angle", true, false, 1],
256 ["second", "?", null, "angle", false, false, 0.00000484813681109536],
257 ["second", "s", ["sec"], "time", true, true, 1],
258 ["short hundredweight", "cwt", ["shweight"], "mass", false, true, 45.359237],
259 ["siemens", "S", null, "electrical_conductance", true, false, 1],
260 ["sievert", "Sv", null, "equivalent_dose", true, false, 1],
261 ["slug", "sg", null, "mass", false, true, 14.59390294],
262 ["square ångström", "ang2", ["ang^2"], "area", false, true, 1e-20],
263 ["square foot", "ft2", ["ft^2"], "area", false, true, 0.09290304],
264 ["square inch", "in2", ["in^2"], "area", false, true, 0.00064516],
265 ["square light-year", "ly2", ["ly^2"], "area", false, true, 8.95054210748189e+31],
266 ["square meter", "m?", null, "area", true, true, 1],
267 ["square meter", "m^2", null, "area", true, true, 1],
268 ["square mile", "mi2", ["mi^2"], "area", false, true, 2589988.110336],
269 ["square nautical mile", "Nmi2", ["Nmi^2"], "area", false, true, 3429904],
270 ["square Pica", "Pica2", ["Picapt2", "Pica^2", "Picapt^2"], "area", false, true, 0.00001792111111111],
271 ["square yard", "yd2", ["yd^2"], "area", false, true, 0.83612736],
272 ["statute mile", "mi", null, "length", false, true, 1609.344],
273 ["steradian", "sr", null, "solid_angle", true, false, 1],
274 ["stilb", "sb", null, "luminance", false, false, 0.0001],
275 ["stokes", "St", null, "kinematic_viscosity", false, false, 0.0001],
276 ["stone", "stone", null, "mass", false, true, 6.35029318],
277 ["tablespoon", "tbs", null, "volume", false, true, 0.0000147868],
278 ["teaspoon", "tsp", null, "volume", false, true, 0.00000492892],
279 ["tesla", "T", null, "magnetic_flux_density", true, true, 1],
280 ["thermodynamic calorie", "c", null, "energy", false, true, 4.184],
281 ["ton", "ton", null, "mass", false, true, 907.18474],
282 ["tonne", "t", null, "mass", false, false, 1000],
283 ["U.K. pint", "uk_pt", null, "volume", false, true, 0.00056826125],
284 ["U.S. bushel", "bushel", null, "volume", false, true, 0.03523907],
285 ["U.S. oil barrel", "barrel", null, "volume", false, true, 0.158987295],
286 ["U.S. pint", "pt", ["us_pt"], "volume", false, true, 0.000473176473],
287 ["U.S. survey mile", "survey_mi", null, "length", false, true, 1609.347219],
288 ["U.S. survey/statute acre", "us_acre", null, "area", false, true, 4046.87261],
289 ["volt", "V", null, "voltage", true, false, 1],
290 ["watt", "W", null, "power", true, true, 1],
291 ["watt-hour", "Wh", ["wh"], "energy", false, true, 3600],
292 ["weber", "Wb", null, "magnetic_flux", true, false, 1],
293 ["yard", "yd", null, "length", false, true, 0.9144],
294 ["year", "yr", null, "time", false, true, 31557600]
295 ];
296 // Binary prefixes
297 // [Name, Prefix power of 2 value, Previx value, Abbreviation, Derived from]
298 var binary_prefixes = {
299 Yi: ["yobi", 80, 1208925819614629174706176, "Yi", "yotta"],
300 Zi: ["zebi", 70, 1180591620717411303424, "Zi", "zetta"],
301 Ei: ["exbi", 60, 1152921504606846976, "Ei", "exa"],
302 Pi: ["pebi", 50, 1125899906842624, "Pi", "peta"],
303 Ti: ["tebi", 40, 1099511627776, "Ti", "tera"],
304 Gi: ["gibi", 30, 1073741824, "Gi", "giga"],
305 Mi: ["mebi", 20, 1048576, "Mi", "mega"],
306 ki: ["kibi", 10, 1024, "ki", "kilo"]
307 };
308 // Unit prefixes
309 // [Name, Multiplier, Abbreviation]
310 var unit_prefixes = {
311 Y: ["yotta", 1e+24, "Y"],
312 Z: ["zetta", 1e+21, "Z"],
313 E: ["exa", 1e+18, "E"],
314 P: ["peta", 1e+15, "P"],
315 T: ["tera", 1e+12, "T"],
316 G: ["giga", 1e+09, "G"],
317 M: ["mega", 1e+06, "M"],
318 k: ["kilo", 1e+03, "k"],
319 h: ["hecto", 1e+02, "h"],
320 e: ["dekao", 1e+01, "e"],
321 d: ["deci", 1e-01, "d"],
322 c: ["centi", 1e-02, "c"],
323 m: ["milli", 1e-03, "m"],
324 u: ["micro", 1e-06, "u"],
325 n: ["nano", 1e-09, "n"],
326 p: ["pico", 1e-12, "p"],
327 f: ["femto", 1e-15, "f"],
328 a: ["atto", 1e-18, "a"],
329 z: ["zepto", 1e-21, "z"],
330 y: ["yocto", 1e-24, "y"]
331 };
332 // Initialize units and multipliers
333 var from = null;
334 var to = null;
335 var base_from_unit = fromUnit;
336 var base_to_unit = toUnit;
337 var from_multiplier = 1;
338 var to_multiplier = 1;
339 var alt;
340 // Lookup from and to units
341 for (var i = 0; i < units.length; i++) {
342 alt = (units[i][2] === null) ? [] : units[i][2];
343 if (units[i][1] === base_from_unit || alt.indexOf(base_from_unit) >= 0) {
344 from = units[i];
345 }
346 if (units[i][1] === base_to_unit || alt.indexOf(base_to_unit) >= 0) {
347 to = units[i];
348 }
349 }
350 // Lookup from prefix
351 if (from === null) {
352 var from_binary_prefix = binary_prefixes[fromUnit.substring(0, 2)];
353 var from_unit_prefix = unit_prefixes[fromUnit.substring(0, 1)];
354 // Handle dekao unit prefix (only unit prefix with two characters)
355 if (fromUnit.substring(0, 2) === 'da') {
356 from_unit_prefix = ["dekao", 1e+01, "da"];
357 }
358 // Handle binary prefixes first (so that 'Yi' is processed before 'Y')
359 if (from_binary_prefix) {
360 from_multiplier = from_binary_prefix[2];
361 base_from_unit = fromUnit.substring(2);
362 }
363 else if (from_unit_prefix) {
364 from_multiplier = from_unit_prefix[1];
365 base_from_unit = fromUnit.substring(from_unit_prefix[2].length);
366 }
367 // Lookup from unit
368 for (var j = 0; j < units.length; j++) {
369 alt = (units[j][2] === null) ? [] : units[j][2];
370 if (units[j][1] === base_from_unit || alt.indexOf(base_from_unit) >= 0) {
371 from = units[j];
372 }
373 }
374 }
375 // Lookup to prefix
376 if (to === null) {
377 var to_binary_prefix = binary_prefixes[toUnit.substring(0, 2)];
378 var to_unit_prefix = unit_prefixes[toUnit.substring(0, 1)];
379 // Handle dekao unit prefix (only unit prefix with two characters)
380 if (toUnit.substring(0, 2) === 'da') {
381 to_unit_prefix = ["dekao", 1e+01, "da"];
382 }
383 // Handle binary prefixes first (so that 'Yi' is processed before 'Y')
384 if (to_binary_prefix) {
385 to_multiplier = to_binary_prefix[2];
386 base_to_unit = toUnit.substring(2);
387 }
388 else if (to_unit_prefix) {
389 to_multiplier = to_unit_prefix[1];
390 base_to_unit = toUnit.substring(to_unit_prefix[2].length);
391 }
392 // Lookup to unit
393 for (var k = 0; k < units.length; k++) {
394 alt = (units[k][2] === null) ? [] : units[k][2];
395 if (units[k][1] === base_to_unit || alt.indexOf(base_to_unit) >= 0) {
396 to = units[k];
397 }
398 }
399 }
400 // Return error if a unit does not exist
401 if (from === null || to === null) {
402 throw new Errors_1.NAError("Invalid units for conversion.");
403 }
404 // Return error if units represent different quantities
405 if (from[3] !== to[3]) {
406 throw new Errors_1.NAError("Invalid units for conversion.");
407 }
408 // Return converted number
409 return n * from[6] * from_multiplier / (to[6] * to_multiplier);
410};
411exports.CONVERT = CONVERT;
412/**
413 * Removes leading and trailing spaces in a specified string.
414 * @param value - The string or reference to a cell containing a string to be trimmed.
415 * @returns {string}
416 * @constructor
417 */
418var TRIM = function (value) {
419 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "TRIM");
420 var text = TypeConverter_1.TypeConverter.firstValueAsString(value);
421 return text.trim();
422};
423exports.TRIM = TRIM;
424/**
425 * Converts text to lowercase.
426 * @param value - Text to convert.
427 * @constructor
428 */
429var LOWER = function (value) {
430 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "LOWER");
431 var text = TypeConverter_1.TypeConverter.firstValueAsString(value);
432 return text.toLowerCase();
433};
434exports.LOWER = LOWER;
435/**
436 * Converts text to uppercase.
437 * @param value - Text to convert.
438 * @constructor
439 */
440var UPPER = function (value) {
441 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "UPPER");
442 var text = TypeConverter_1.TypeConverter.firstValueAsString(value);
443 return text.toUpperCase();
444};
445exports.UPPER = UPPER;
446/**
447 * Returns string arguments as text, or the empty string if the value is not text.
448 * @param value - Value to return.
449 * @constructor
450 */
451var T = function (value) {
452 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "T");
453 var v = TypeConverter_1.TypeConverter.firstValue(value);
454 if (typeof v === "string") {
455 return v;
456 }
457 return "";
458};
459exports.T = T;
460/**
461 * Converts a number into a Roman numeral.
462 * @param value - The value to convert. Must be between 0 and 3999.
463 * @constructor
464 * TODO: Second parameter should be 'rule_relaxation'.
465 */
466var ROMAN = function (value) {
467 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "ROMAN");
468 value = TypeConverter_1.TypeConverter.firstValueAsNumber(value);
469 if (value < 1 || value > 3999) {
470 throw new Errors_1.ValueError("Function ROMAN parameter 1 value is " + value
471 + ", while valid values are between 1 and 3999 inclusive.");
472 }
473 // The MIT License
474 // Copyright (c) 2008 Steven Levithan
475 // https://stackoverflow.com/questions/9083037/convert-a-number-into-a-roman-numeral-in-javascript
476 var digits = String(value).split('');
477 var key = ['',
478 'C',
479 'CC',
480 'CCC',
481 'CD',
482 'D',
483 'DC',
484 'DCC',
485 'DCCC',
486 'CM',
487 '',
488 'X',
489 'XX',
490 'XXX',
491 'XL',
492 'L',
493 'LX',
494 'LXX',
495 'LXXX',
496 'XC',
497 '',
498 'I',
499 'II',
500 'III',
501 'IV',
502 'V',
503 'VI',
504 'VII',
505 'VIII',
506 'IX'
507 ];
508 var roman = '';
509 var i = 3;
510 while (i--) {
511 roman = (key[+digits.pop() + (i * 10)] || '') + roman;
512 }
513 return new Array(+digits.join('') + 1).join('M') + roman;
514};
515exports.ROMAN = ROMAN;
516/**
517 * Converts a number into text according to a given format.
518 * @param value - The value to be converted.
519 * @param format - Text which defines the format. "0" forces the display of zeros, while "#" suppresses the display of
520 * zeros. For example TEXT(22.1,"000.00") produces 022.10, while TEXT(22.1,"###.##") produces 22.1, and
521 * TEXT(22.405,"00.00") results in 22.41. To format days: "dddd" indicates full name of the day of the week, "ddd"
522 * short name of the day of the week, "dd" indicates the day of the month as two digits, "d" indicates day of the month
523 * as one or two digits, "mmmmm" indicates the first letter in the month of the year, "mmmm" indicates the full name of
524 * the month of the year, "mmm" indicates short name of the month of the year, "mm" indicates month of the year as two
525 * digits or the number of minutes in a time, depending on whether it follows yy or dd, or if it follows hh, "m" month
526 * of the year as one or two digits or the number of minutes in a time, depending on whether it follows yy or dd, or if
527 * it follows hh, "yyyy" indicates year as four digits, "yy" and "y" indicate year as two digits, "hh" indicates hour
528 * on a 24-hour clock, "h" indicates hour on a 12-hour clock, "ss.000" indicates milliseconds in a time, "ss" indicates
529 * seconds in a time, "AM/PM" or "A/P" indicate displaying hours based on a 12-hour clock and showing AM or PM
530 * depending on the time of day. Eg: `TEXT("01/09/2012 10:04:33AM", "mmmm-dd-yyyy, hh:mm AM/PM")` would result in
531 * "January-09-2012, 10:04 AM".
532 * @constructor
533 */
534var TEXT = function (value, format) {
535 ArgsChecker_1.ArgsChecker.checkLength(arguments, 2, "TEXT");
536 value = TypeConverter_1.TypeConverter.firstValue(value);
537 function splitReplace(values, regex, index) {
538 return values.map(function (value) {
539 if (typeof value === "number") {
540 return [value];
541 }
542 else if (value instanceof Array) {
543 return splitReplace(value, regex, index);
544 }
545 else {
546 var splits_1 = value.split(regex);
547 var building_1 = [];
548 if (splits_1.length === 1) {
549 return [splits_1];
550 }
551 splits_1.map(function (splitValue, splitIndex) {
552 building_1.push(splitValue);
553 if (splitIndex !== splits_1.length - 1) {
554 building_1.push(index);
555 }
556 });
557 return building_1;
558 }
559 });
560 }
561 // Short cut for booleans
562 if (typeof value === "boolean") {
563 return TypeConverter_1.TypeConverter.valueToString(value);
564 }
565 // If the format matches the date format
566 if (format.match(/^.*(d|D|M|m|yy|Y|HH|hh|h|s|S|AM|PM|am|pm|A\/P|\*).*$/g)) {
567 // If the format contains both, throw error
568 if (format.indexOf("#") > -1 || format.indexOf("0") > -1) {
569 throw new Errors_1.ValueError("Invalid format pattern '" + format + "' for TEXT formula.");
570 }
571 var valueAsMoment_1;
572 if (typeof value === "string") {
573 valueAsMoment_1 = TypeConverter_1.TypeConverter.stringToMoment(value);
574 if (valueAsMoment_1 === undefined) {
575 valueAsMoment_1 = TypeConverter_1.TypeConverter.decimalNumberToMoment(TypeConverter_1.TypeConverter.valueToNumber(value));
576 }
577 }
578 else {
579 valueAsMoment_1 = TypeConverter_1.TypeConverter.decimalNumberToMoment(TypeConverter_1.TypeConverter.valueToNumber(value));
580 }
581 var replacementPairs_1 = [
582 // full name of the day of the week
583 [/dddd/gi, valueAsMoment_1.format("dddd")],
584 // short name of the day of the week
585 [/ddd/gi, valueAsMoment_1.format("ddd")],
586 // day of the month as two digits
587 [/dd/gi, valueAsMoment_1.format("DD")],
588 // day of the month as one or two digits
589 [/d/gi, valueAsMoment_1.format("d")],
590 // first letter in the month of the year
591 [/mmmmm/gi, valueAsMoment_1.format("MMMM").charAt(0)],
592 // full name of the month of the year
593 [/mmmm/gi, valueAsMoment_1.format("MMMM")],
594 // short name of the month of the year
595 [/mmm/gi, valueAsMoment_1.format("MMM")],
596 // month of the year as two digits or the number of minutes in a time
597 [/mm/gi, function (monthOrMinute) {
598 return monthOrMinute === "month" ? valueAsMoment_1.format("MM") : valueAsMoment_1.format("mm");
599 }],
600 // month of the year as one or two digits or the number of minutes in a time
601 [/m/g, function (monthOrMinute) {
602 return monthOrMinute === "month" ? valueAsMoment_1.format("M") : valueAsMoment_1.format("m");
603 }],
604 // year as four digits
605 [/yyyy/gi, valueAsMoment_1.format("YYYY")],
606 // year as two digits
607 [/yy/gi, valueAsMoment_1.format("YY")],
608 // year as two digits
609 [/y/gi, valueAsMoment_1.format("YY")],
610 // hour on a 24-hour clock
611 [/HH/g, valueAsMoment_1.format("HH")],
612 // hour on a 12-hour clock
613 [/hh/g, valueAsMoment_1.format("hh")],
614 // hour on a 12-hour clock
615 [/h/gi, valueAsMoment_1.format("hh")],
616 // milliseconds in a time
617 [/ss\.000/gi, valueAsMoment_1.format("ss.SSS")],
618 // seconds in a time
619 [/ss/gi, valueAsMoment_1.format("ss")],
620 // seconds in a time
621 [/s/gi, valueAsMoment_1.format("ss")],
622 [/AM\/PM/gi, valueAsMoment_1.format("A")],
623 // displaying hours based on a 12-hour clock and showing AM or PM depending on the time of day
624 [/A\/P/gi, valueAsMoment_1.format("A").charAt(0)]
625 ];
626 var builtList_1 = [format];
627 replacementPairs_1.map(function (pair, pairIndex) {
628 var regex = pair[0];
629 builtList_1 = splitReplace(builtList_1, regex, pairIndex);
630 });
631 var lastRegEx_1 = "";
632 return Filter_1.Filter.flatten(builtList_1).map(function (val) {
633 if (typeof val === "number") {
634 if (typeof replacementPairs_1[val][1] === "function") {
635 var monthOrMinute = "month";
636 // Hack-ish way of determining if MM, mm, M, or m should be evaluated as minute or month.
637 var lastRegExWasHour = lastRegEx_1.toString() === new RegExp("hh", "g").toString()
638 || lastRegEx_1.toString() === new RegExp("HH", "g").toString()
639 || lastRegEx_1.toString() === new RegExp("h", "g").toString();
640 if (lastRegExWasHour) {
641 monthOrMinute = "minute";
642 }
643 lastRegEx_1 = replacementPairs_1[val][0];
644 return replacementPairs_1[val][1](monthOrMinute);
645 }
646 lastRegEx_1 = replacementPairs_1[val][0];
647 return replacementPairs_1[val][1];
648 }
649 return val;
650 }).join("");
651 }
652 else {
653 var numberValue = TypeConverter_1.TypeConverter.valueToNumber(value);
654 // Format string can't contain both 0 and #.
655 if (format.indexOf("#") > -1 && format.indexOf("0") > -1) {
656 throw new Errors_1.ValueError("Invalid format pattern '" + format + "' for TEXT formula.");
657 }
658 // See https://regex101.com/r/Jji2Ng/8 for more information.
659 var POUND_SIGN_FORMAT_CAPTURE = /^([$%+-]*)([#,]+)?(\.?)([# ]*)([$%+ -]*)$/gi;
660 var matches = POUND_SIGN_FORMAT_CAPTURE.exec(format);
661 if (matches !== null) {
662 var headSignsFormat = matches[1] || "";
663 var wholeNumberFormat = matches[2] || "";
664 var decimalNumberFormat = matches[4] || "";
665 var tailingSignsFormat = matches[5] || "";
666 var commafyNumber = wholeNumberFormat.indexOf(",") > -1;
667 var builder = MoreUtils_1.NumberStringBuilder.start()
668 .number(numberValue)
669 .commafy(commafyNumber)
670 .integerZeros(1)
671 .maximumDecimalPlaces(decimalNumberFormat.replace(/ /g, "").length)
672 .head(headSignsFormat)
673 .tail(tailingSignsFormat);
674 return builder.build();
675 }
676 /*
677 * See https://regex101.com/r/Pbx7js/6 for more information.
678 * 1 = signs, currency, etc.
679 * 2 = whole number including commas
680 * 3 = decimal
681 * 4 = decimal place including spaces
682 * 5 = signs, currency, etc.
683 * */
684 var ZERO_FORMAT_CAPTURE = /^([$%+-]*)([0,]+)?(\.?)([0 ]*)([$%+ -]*)$/gi;
685 matches = ZERO_FORMAT_CAPTURE.exec(format);
686 if (matches !== null) {
687 var headSignsFormat = matches[1] || "";
688 var wholeNumberFormat = matches[2] || "";
689 var decimalNumberFormat = matches[4] || "";
690 var tailingSignsFormat = matches[5] || "";
691 var commafyNumber = wholeNumberFormat.indexOf(",") > -1;
692 var builder = MoreUtils_1.NumberStringBuilder.start()
693 .number(numberValue)
694 .commafy(commafyNumber)
695 .integerZeros(wholeNumberFormat.replace(/,/g, "").length)
696 .decimalZeros(decimalNumberFormat.replace(/ /g, "").length)
697 .head(headSignsFormat)
698 .tail(tailingSignsFormat);
699 return builder.build();
700 }
701 // If the format didn't match the patterns above, it is invalid.
702 throw new Errors_1.ValueError("Invalid format pattern '" + format + "' for TEXT formula.");
703 }
704};
705exports.TEXT = TEXT;
706/**
707 * Looks for a string of text within another string. Where to begin the search can also be defined. The search term can
708 * be a number or any string of characters. The search is case-sensitive.
709 * @param searchFor - The text to be found.
710 * @param searchIn - The text where the search takes place.
711 * @param startAt - [OPTIONAL defaults to 1] - The position in the text from which the search starts.
712 * @returns {number}
713 * @constructor
714 */
715var FIND = function (searchFor, searchIn, startAt) {
716 ArgsChecker_1.ArgsChecker.checkLengthWithin(arguments, 2, 3, "FIND");
717 searchFor = TypeConverter_1.TypeConverter.firstValueAsString(searchFor);
718 searchIn = TypeConverter_1.TypeConverter.firstValueAsString(searchIn);
719 startAt = MoreUtils_1.isUndefined(startAt) ? 1 : TypeConverter_1.TypeConverter.firstValueAsNumber(startAt);
720 if (startAt < 1) {
721 throw new Errors_1.ValueError("FIND parameter 3 value is " + startAt + ", but should be greater than or equal to 1.");
722 }
723 var index = searchIn.indexOf(searchFor, startAt - 1);
724 if (index > -1) {
725 return index + 1;
726 }
727 throw new Errors_1.ValueError("For FIND cannot find '" + searchFor + "' within '" + searchIn + "'.");
728};
729exports.FIND = FIND;
730/**
731 * Concatenates the values of one or more arrays using a specified delimiter.
732 * @param delimiter - The string to place between the values.
733 * @param values - The values to be appended using the delimiter.
734 * @returns {string}
735 * @constructor
736 */
737var JOIN = function (delimiter) {
738 var values = [];
739 for (var _i = 1; _i < arguments.length; _i++) {
740 values[_i - 1] = arguments[_i];
741 }
742 ArgsChecker_1.ArgsChecker.checkAtLeastLength(arguments, 2, "JOIN");
743 delimiter = TypeConverter_1.TypeConverter.firstValueAsString(delimiter);
744 values = Filter_1.Filter.flattenAndThrow(values);
745 return values.join(delimiter);
746};
747exports.JOIN = JOIN;
748/**
749 * Returns the length of a string including spaces.
750 * @param value - The text whose length is to be determined.
751 * @constructor
752 */
753var LEN = function (value) {
754 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "LEN");
755 value = TypeConverter_1.TypeConverter.firstValueAsString(value);
756 return value.length;
757};
758exports.LEN = LEN;
759/**
760 * Returns the first character or characters in a text string.
761 * @param text - The text where the initial partial words are to be determined.
762 * @param numberOfCharacters [OPTIONAL] - The number of characters for the start text. If this parameter is not defined,
763 * one character is returned.
764 * @returns {string}
765 * @constructor
766 */
767var LEFT = function (text, numberOfCharacters) {
768 ArgsChecker_1.ArgsChecker.checkLengthWithin(arguments, 1, 2, "LEFT");
769 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
770 numberOfCharacters = MoreUtils_1.isUndefined(numberOfCharacters) ? 1 : TypeConverter_1.TypeConverter.firstValueAsNumber(numberOfCharacters);
771 if (numberOfCharacters < 0) {
772 throw new Errors_1.ValueError("Formula LEFT parameter 2 value is " + numberOfCharacters
773 + ", but should be greater than or equal to 0.");
774 }
775 return text.substring(0, numberOfCharacters);
776};
777exports.LEFT = LEFT;
778/**
779 * Defines the last character or characters in a text string.
780 * @param text - The text where the initial partial words are to be determined.
781 * @param numberOfCharacters [OPTIONAL] - The number of characters for the start text. If this parameter is not defined,
782 * one character is returned.
783 * @returns {string}
784 * @constructor
785 */
786var RIGHT = function (text, numberOfCharacters) {
787 ArgsChecker_1.ArgsChecker.checkLengthWithin(arguments, 1, 2, "RIGHT");
788 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
789 numberOfCharacters = MoreUtils_1.isUndefined(numberOfCharacters) ? 1 : TypeConverter_1.TypeConverter.firstValueAsNumber(numberOfCharacters);
790 if (numberOfCharacters < 0) {
791 throw new Errors_1.ValueError("Formula RIGHT parameter 2 value is " + numberOfCharacters
792 + ", but should be greater than or equal to 0.");
793 }
794 return text.substring(text.length - numberOfCharacters);
795};
796exports.RIGHT = RIGHT;
797/**
798 * Returns the position of a text segment within a character string. The start of the search can be set as an option.
799 * The search text can be a number or any sequence of characters. The search is not case-sensitive.
800 * @param findText - The text to be searched for.
801 * @param withinText - The text where the search will take place
802 * @param position - [OPTIONAL default 1] The position in the text where the search is to start.
803 * @constructor
804 */
805var SEARCH = function (findText, withinText, position) {
806 ArgsChecker_1.ArgsChecker.checkLengthWithin(arguments, 2, 3, "SEARCH");
807 findText = TypeConverter_1.TypeConverter.firstValueAsString(findText);
808 withinText = TypeConverter_1.TypeConverter.firstValueAsString(withinText);
809 position = MoreUtils_1.isUndefined(position) ? 0 : TypeConverter_1.TypeConverter.firstValueAsNumber(position);
810 if (position < 0) {
811 throw new Errors_1.ValueError("Formula SEARCH parameter 3 value is " + position
812 + ", but should be greater than or equal to 1.");
813 }
814 var index = withinText.toLowerCase().indexOf(findText.toLowerCase(), position - 1);
815 if (index > -1) {
816 return index + 1;
817 }
818 throw new Errors_1.ValueError("For SEARCH evaluation, cannot find '" + findText + "' inside '" + withinText + "'");
819};
820exports.SEARCH = SEARCH;
821/**
822 * Repeats a character string by the given number of copies.
823 * @param text - The text to be repeated.
824 * @param numberOfReps - The number of repetitions
825 * @constructor
826 */
827var REPT = function (text, numberOfReps) {
828 ArgsChecker_1.ArgsChecker.checkLength(arguments, 2, "REPT");
829 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
830 numberOfReps = TypeConverter_1.TypeConverter.firstValueAsNumber(numberOfReps);
831 if (numberOfReps < 0) {
832 throw new Errors_1.ValueError("Formula REPT parameter 2 value is " + numberOfReps
833 + ", but should be greater than or equal to 0.");
834 }
835 return new Array(numberOfReps + 1).join(text);
836};
837exports.REPT = REPT;
838/**
839 * Converts a value into a number if possible.
840 * @param value - The value to convert to a number.
841 * @returns {number}
842 * @constructor
843 */
844var VALUE = function (value) {
845 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "VALUE");
846 value = TypeConverter_1.TypeConverter.firstValue(value);
847 if (typeof value === "boolean") {
848 throw new Errors_1.ValueError("VALUE parameter '" + value + "' cannot be parsed to number.");
849 }
850 return TypeConverter_1.TypeConverter.firstValueAsNumber(value);
851};
852exports.VALUE = VALUE;
853/**
854 * Removes all non-printing characters from the string.
855 * @param text - The text from which to remove all non-printable characters.
856 * @returns {string}
857 * @constructor
858 */
859var CLEAN = function (text) {
860 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "CLEAN");
861 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
862 return text.replace(/[\0-\x1F]/g, "");
863};
864exports.CLEAN = CLEAN;
865/**
866 * Returns a text segment of a character string. The parameters specify the starting position and the number of
867 * characters.
868 * @param text - The text containing the characters to extract.
869 * @param start - The position of the first character in the text to extract.
870 * @param number - The number of characters in the part of the text.
871 * @returns {string}
872 * @constructor
873 */
874var MID = function (text, start, number) {
875 ArgsChecker_1.ArgsChecker.checkLength(arguments, 3, "MID");
876 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
877 start = TypeConverter_1.TypeConverter.firstValueAsNumber(start);
878 number = TypeConverter_1.TypeConverter.firstValueAsNumber(number);
879 if (number === 0) {
880 return "";
881 }
882 if (number < 0) {
883 throw new Errors_1.ValueError("MID parameter 3 value is " + number + ", but should be greater than or equal to 0.");
884 }
885 if (start < 1) {
886 throw new Errors_1.NumError("Function MID parameter 2 value is " + start + ", but should be greater than or equal to 1.");
887 }
888 return text.substring(start - 1, start + number - 1);
889};
890exports.MID = MID;
891var PROPER = function (text) {
892 ArgsChecker_1.ArgsChecker.checkLength(arguments, 1, "PROPER");
893 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
894 return text.replace(/\w\S*/g, function (txt) {
895 return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
896 });
897};
898exports.PROPER = PROPER;
899/**
900 * Replaces part of a text string with a different text string. This function can be used to replace both characters and
901 * numbers (which are automatically converted to text). The result of the function is always displayed as text.
902 * @param text - The text of which a part will be replaced.
903 * @param position - The position within the text where the replacement will begin.
904 * @param length - The number of characters in text to be replaced.
905 * @param newText - The text which replaces text.
906 * @constructor
907 */
908var REPLACE = function (text, position, length, newText) {
909 ArgsChecker_1.ArgsChecker.checkLength(arguments, 4, "REPLACE");
910 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
911 position = TypeConverter_1.TypeConverter.firstValueAsNumber(position);
912 if (position < 1) {
913 throw new Errors_1.ValueError("Function REPLACE parameter 2 value is " + position
914 + ", but should be greater than or equal to 1.");
915 }
916 length = TypeConverter_1.TypeConverter.firstValueAsNumber(length);
917 if (length < 0) {
918 throw new Errors_1.ValueError("Function REPLACE parameter 3 value is " + length
919 + ", but should be greater than or equal to 1.");
920 }
921 newText = TypeConverter_1.TypeConverter.firstValueAsString(newText);
922 return text.substr(0, position - 1) + newText + text.substr(position - 1 + length);
923};
924exports.REPLACE = REPLACE;
925/**
926 * Substitutes new text for old text in a string.
927 * @param text - The text in which text segments are to be exchanged.
928 * @param searchFor - The text segment that is to be replaced (a number of times)
929 * @param replaceWith - The text that is to replace the text segment.
930 * @param occurrence - [OPTIONAL] - Indicates how many occurrences of the search text are to be replaced. If this
931 * parameter is missing, the search text is replaced throughout.
932 * @returns {string}
933 * @constructor
934 */
935var SUBSTITUTE = function (text, searchFor, replaceWith, occurrence) {
936 ArgsChecker_1.ArgsChecker.checkLengthWithin(arguments, 3, 4, "SUBSTITUTE");
937 text = TypeConverter_1.TypeConverter.firstValueAsString(text);
938 searchFor = TypeConverter_1.TypeConverter.firstValueAsString(searchFor);
939 replaceWith = TypeConverter_1.TypeConverter.firstValueAsString(replaceWith);
940 if (MoreUtils_1.isUndefined(occurrence)) {
941 return text.replace(new RegExp(searchFor, 'g'), replaceWith);
942 }
943 occurrence = TypeConverter_1.TypeConverter.firstValueAsNumber(occurrence);
944 if (occurrence < 0) {
945 throw new Errors_1.ValueError("Function SUBSTITUTE parameter 4 value is " + occurrence
946 + ", but should be greater than or equal to 0.");
947 }
948 var index = 0;
949 var i = 0;
950 while (text.indexOf(searchFor, index) > -1) {
951 index = text.indexOf(searchFor, index);
952 i++;
953 if (i === occurrence) {
954 return text.substring(0, index) + replaceWith + text.substring(index + searchFor.length);
955 }
956 }
957 return text;
958};
959exports.SUBSTITUTE = SUBSTITUTE;