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