numfmt.js 18 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371
  1. /*
  2. ArozOS Office Sheets - number format codes
  3. ==========================================
  4. DOM-free formatter for spreadsheet format codes, used by TEXT() and
  5. usable by the grid. Browser global SheetNumFmt, Node module.exports.
  6. SheetNumFmt.format(value, code) -> string
  7. Supported: sections (positive;negative;zero;text) and [>100]-style
  8. conditions, General, 0 # ? placeholders, thousands separators and
  9. trailing-comma scaling, %, E+/E- scientific, "literal" \x _x *x, @,
  10. fractions (# ?/?, ?/8), [Red]-style colours and [$-409] locales
  11. (ignored), [$sym-409] currency symbols, and dates/times: yyyy yy mmmm
  12. mmm mm m dddd ddd dd d hh h mm (minutes after h / before s) ss .00 AM/PM
  13. A/P [h] [m] [s].
  14. */
  15. var SheetNumFmt = (function () {
  16. "use strict";
  17. var EPOCH = Date.UTC(1899, 11, 30), DAY_MS = 86400000;
  18. var MONTHS = ["January", "February", "March", "April", "May", "June", "July", "August",
  19. "September", "October", "November", "December"];
  20. var DAYS = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"];
  21. // split on ; outside quotes, brackets and escapes
  22. function sections(code) {
  23. var out = [], cur = "", q = false, br = false;
  24. for (var i = 0; i < code.length; i++) {
  25. var ch = code.charAt(i);
  26. if (ch === "\\" && !q && i + 1 < code.length) { cur += ch + code.charAt(++i); continue; }
  27. if (ch === '"') q = !q;
  28. else if (!q && ch === "[") br = true;
  29. else if (!q && ch === "]") br = false;
  30. if (ch === ";" && !q && !br) { out.push(cur); cur = ""; continue; }
  31. cur += ch;
  32. }
  33. out.push(cur);
  34. return out;
  35. }
  36. /* tokens: {t:"lit", s} | {t:"ph", c:"0|#|?"} | {t:"dot"} | {t:"comma"} |
  37. {t:"pct"} | {t:"exp", sign} | {t:"slash"} | {t:"at"} | {t:"date", s} |
  38. {t:"ampm", s} | {t:"elapsed", s} */
  39. function tokenize(sec) {
  40. var toks = [], i = 0, cond = null, m;
  41. while (i < sec.length) {
  42. var ch = sec.charAt(i);
  43. if (ch === '"') {
  44. var j = sec.indexOf('"', i + 1);
  45. if (j < 0) j = sec.length;
  46. toks.push({ t: "lit", s: sec.slice(i + 1, j) });
  47. i = j + 1;
  48. continue;
  49. }
  50. if (ch === "\\") { toks.push({ t: "lit", s: sec.charAt(i + 1) }); i += 2; continue; }
  51. if (ch === "_") { toks.push({ t: "lit", s: " " }); i += 2; continue; }
  52. if (ch === "*") { i += 2; continue; }
  53. if (ch === "[") {
  54. var end = sec.indexOf("]", i);
  55. if (end < 0) end = sec.length;
  56. var inner = sec.slice(i + 1, end);
  57. if ((m = /^(<=|>=|<>|<|>|=)(-?\d+(\.\d+)?)$/.exec(inner))) cond = { op: m[1], v: parseFloat(m[2]) };
  58. else if (/^(h+|m+|s+)$/i.test(inner)) toks.push({ t: "elapsed", s: inner.toLowerCase() });
  59. else if ((m = /^\$([^-]*)/.exec(inner)) && m[1]) toks.push({ t: "lit", s: m[1] });
  60. i = end + 1;
  61. continue;
  62. }
  63. var rest = sec.slice(i);
  64. if ((m = /^(AM\/PM|A\/P)/i.exec(rest))) { toks.push({ t: "ampm", s: m[1] }); i += m[1].length; continue; }
  65. if ((m = /^(y+|m+|d+|h+|s+)/i.exec(rest))) { toks.push({ t: "date", s: m[1].toLowerCase() }); i += m[1].length; continue; }
  66. if ((m = /^[eE]([+-])/.exec(rest))) { toks.push({ t: "exp", sign: m[1] }); i += 2; continue; }
  67. if (/^general/i.test(rest)) { toks.push({ t: "general" }); i += 7; continue; }
  68. if (ch === "0" || ch === "#" || ch === "?") { toks.push({ t: "ph", c: ch }); i++; continue; }
  69. if (ch === ".") { toks.push({ t: "dot" }); i++; continue; }
  70. if (ch === ",") { toks.push({ t: "comma" }); i++; continue; }
  71. if (ch === "%") { toks.push({ t: "pct" }); i++; continue; }
  72. if (ch === "/") { toks.push({ t: "slash" }); i++; continue; }
  73. if (ch === "@") { toks.push({ t: "at" }); i++; continue; }
  74. toks.push({ t: "lit", s: ch });
  75. i++;
  76. }
  77. toks.cond = cond;
  78. return toks;
  79. }
  80. function general(v) {
  81. if (!isFinite(v)) return "#NUM!";
  82. var a = Math.abs(v);
  83. if (a !== 0 && (a >= 1e11 || a < 1e-9)) return v.toExponential(5).replace(/\.?0+e/, "E").replace("e", "E").replace(/E(\d)/, "E+$1");
  84. var s = String(+v.toPrecision(11));
  85. return s;
  86. }
  87. function isDateFormat(toks) {
  88. return toks.some(function (t) { return t.t === "date" || t.t === "ampm" || t.t === "elapsed"; });
  89. }
  90. function pad(n, w) { var s = String(n); while (s.length < w) s = "0" + s; return s; }
  91. function formatDate(v, toks) {
  92. var ampm = toks.some(function (t) { return t.t === "ampm"; });
  93. // fractional-second precision = zeros after a "." that follows s
  94. var fracDigits = 0;
  95. for (var i = 0; i < toks.length; i++) {
  96. if (toks[i].t === "date" && toks[i].s.charAt(0) === "s" && toks[i + 1] && toks[i + 1].t === "dot") {
  97. for (var k = i + 2; k < toks.length && toks[k].t === "ph" && toks[k].c === "0"; k++) fracDigits++;
  98. }
  99. }
  100. var totalMs = Math.round(v * DAY_MS / Math.pow(10, 3 - Math.min(3, fracDigits))) * Math.pow(10, 3 - Math.min(3, fracDigits));
  101. var d = new Date(EPOCH + totalMs);
  102. var hours = d.getUTCHours(), mins = d.getUTCMinutes(), secs = d.getUTCSeconds(), ms = d.getUTCMilliseconds();
  103. var out = "";
  104. for (i = 0; i < toks.length; i++) {
  105. var t = toks[i];
  106. if (t.t === "date") {
  107. var s = t.s, c = s.charAt(0);
  108. if (c === "m") {
  109. // minutes when next to hours / seconds
  110. var prev = null, next = null, p;
  111. for (p = i - 1; p >= 0; p--) if (toks[p].t === "date" || toks[p].t === "elapsed") { prev = toks[p]; break; }
  112. for (p = i + 1; p < toks.length; p++) if (toks[p].t === "date" || toks[p].t === "elapsed") { next = toks[p]; break; }
  113. var isMin = s.length <= 2 && ((prev && /^h|^\[?h/.test(prev.s)) || (next && next.s.charAt(0) === "s"));
  114. if (isMin) out += s.length === 2 ? pad(mins, 2) : mins;
  115. else if (s.length === 1) out += d.getUTCMonth() + 1;
  116. else if (s.length === 2) out += pad(d.getUTCMonth() + 1, 2);
  117. else if (s.length === 3) out += MONTHS[d.getUTCMonth()].slice(0, 3);
  118. else if (s.length === 5) out += MONTHS[d.getUTCMonth()].charAt(0);
  119. else out += MONTHS[d.getUTCMonth()];
  120. } else if (c === "y") {
  121. out += s.length <= 2 ? pad(d.getUTCFullYear() % 100, 2) : d.getUTCFullYear();
  122. } else if (c === "d") {
  123. if (s.length === 1) out += d.getUTCDate();
  124. else if (s.length === 2) out += pad(d.getUTCDate(), 2);
  125. else if (s.length === 3) out += DAYS[d.getUTCDay()].slice(0, 3);
  126. else out += DAYS[d.getUTCDay()];
  127. } else if (c === "h") {
  128. var h = ampm ? (hours % 12 || 12) : hours;
  129. out += s.length >= 2 ? pad(h, 2) : h;
  130. } else if (c === "s") {
  131. out += s.length >= 2 ? pad(secs, 2) : secs;
  132. }
  133. } else if (t.t === "elapsed") {
  134. var unit = t.s.charAt(0), total = totalMs / 1000;
  135. var val = unit === "h" ? Math.floor(total / 3600) : unit === "m" ? Math.floor(total / 60) : Math.floor(total);
  136. out += pad(val, t.s.length);
  137. } else if (t.t === "ampm") {
  138. var pm = hours >= 12;
  139. if (t.s.length === 3) out += pm ? (t.s.charAt(2) === "p" ? "p" : "P") : (t.s.charAt(0) === "a" ? "a" : "A");
  140. else out += pm ? "PM" : "AM";
  141. } else if (t.t === "dot" && fracDigits && toks[i - 1] && toks[i - 1].t === "date" && toks[i - 1].s.charAt(0) === "s") {
  142. out += "." + pad(ms, 3).slice(0, fracDigits);
  143. i += fracDigits;
  144. } else if (t.t === "lit") out += t.s;
  145. else if (t.t === "dot") out += ".";
  146. else if (t.t === "comma") out += ",";
  147. else if (t.t === "slash") out += "/";
  148. else if (t.t === "ph") out += t.c === "?" ? " " : t.c === "0" ? "0" : "";
  149. else if (t.t === "pct") out += "%";
  150. }
  151. return out;
  152. }
  153. function group(digits) {
  154. return digits.replace(/\B(?=(\d{3})+(?!\d))/g, ",");
  155. }
  156. function gcd(a, b) { while (b) { var t = b; b = a % b; a = t; } return a; }
  157. // best fraction with a denominator of at most maxDen digits (or exactly den)
  158. function fraction(x, digits, fixedDen) {
  159. if (fixedDen) return [Math.round(x * fixedDen), fixedDen];
  160. var maxDen = Math.pow(10, digits) - 1, best = [0, 1], bestErr = Infinity;
  161. for (var den = 1; den <= maxDen; den++) {
  162. var nu = Math.round(x * den), err = Math.abs(x - nu / den);
  163. if (err < bestErr - 1e-12) { best = [nu, den]; bestErr = err; if (err === 0) break; }
  164. }
  165. var g = gcd(best[0], best[1]) || 1;
  166. return [best[0] / g, best[1] / g];
  167. }
  168. function formatNumber(v, toks) {
  169. if (toks.some(function (t) { return t.t === "general"; }) && !toks.some(function (t) { return t.t === "ph"; })) {
  170. return toks.map(function (t) { return t.t === "general" ? general(v) : t.t === "lit" ? t.s : ""; }).join("");
  171. }
  172. var slashAt = -1, i;
  173. for (i = 0; i < toks.length; i++) if (toks[i].t === "slash") { slashAt = i; break; }
  174. var pct = toks.filter(function (t) { return t.t === "pct"; }).length;
  175. v = v * Math.pow(100, pct);
  176. // fraction: [int part] num/den
  177. if (slashAt > 0) return formatFraction(v, toks, slashAt);
  178. var dotAt = -1, expAt = -1;
  179. for (i = 0; i < toks.length; i++) {
  180. if (toks[i].t === "dot" && dotAt < 0 && expAt < 0) dotAt = i;
  181. if (toks[i].t === "exp" && expAt < 0) expAt = i;
  182. }
  183. var intEnd = dotAt >= 0 ? dotAt : (expAt >= 0 ? expAt : toks.length);
  184. var fracEnd = expAt >= 0 ? expAt : toks.length;
  185. var intPh = [], fracPh = [], lastIntPh = -1;
  186. for (i = 0; i < intEnd; i++) if (toks[i].t === "ph") { intPh.push(i); lastIntPh = i; }
  187. if (dotAt >= 0) for (i = dotAt + 1; i < fracEnd; i++) if (toks[i].t === "ph") fracPh.push(i);
  188. // commas: between integer placeholders = grouping; right after the
  189. // last digit placeholder = divide by 1000 each
  190. var grouping = false, scale = 0;
  191. for (i = 0; i < intEnd; i++) {
  192. if (toks[i].t !== "comma") continue;
  193. if (intPh.length && i > intPh[0] && i < lastIntPh) grouping = true;
  194. else if (i > lastIntPh && lastIntPh >= 0) scale++;
  195. }
  196. if (dotAt < 0 && expAt < 0) {
  197. // trailing commas at the very end also scale
  198. for (i = toks.length - 1; i >= 0 && toks[i].t === "comma"; i--) { /* counted above */ }
  199. }
  200. v = v / Math.pow(1000, scale);
  201. var exponent = 0, expPh = [];
  202. if (expAt >= 0) {
  203. for (i = expAt + 1; i < toks.length; i++) if (toks[i].t === "ph") expPh.push(i);
  204. if (v !== 0) {
  205. var intDigits = Math.max(1, intPh.length);
  206. exponent = Math.floor(Math.log10(Math.abs(v))) - (intDigits - 1);
  207. v = v / Math.pow(10, exponent);
  208. // rounding may push the mantissa to the next power
  209. var rounded = +Math.abs(v).toFixed(fracPh.length);
  210. if (rounded >= Math.pow(10, intDigits)) { exponent++; v = v / 10; }
  211. }
  212. }
  213. var neg = v < 0;
  214. var fixed = Math.abs(v).toFixed(fracPh.length);
  215. var parts = fixed.split(".");
  216. var intStr = parts[0] === "0" ? "" : parts[0];
  217. var fracStr = parts[1] || "";
  218. // integer placeholders filled from the right, one digit each (padding
  219. // per placeholder kind); the leftmost one takes any extra digits
  220. var intOut = {};
  221. var digits = intStr.split(""), p;
  222. for (p = intPh.length - 1; p >= 0; p--) {
  223. var tk = toks[intPh[p]];
  224. var d = digits.length ? digits.pop() : (tk.c === "0" ? "0" : tk.c === "?" ? " " : "");
  225. if (p === 0 && digits.length) d = digits.join("") + d;
  226. intOut[intPh[p]] = d;
  227. }
  228. if (grouping && intPh.length) {
  229. // thousands separators run across the whole integer (padding
  230. // zeros included, as in "000,000"): gather it into the first slot
  231. var whole = intPh.map(function (ix) { return intOut[ix]; }).join("");
  232. var lead = whole.match(/^\s*/)[0];
  233. intOut[intPh[0]] = lead + group(whole.slice(lead.length));
  234. for (p = 1; p < intPh.length; p++) intOut[intPh[p]] = "";
  235. }
  236. var fracOut = {};
  237. var lastSig = fracStr.replace(/0+$/, "").length;
  238. for (p = 0; p < fracPh.length; p++) {
  239. var fc = fracStr.charAt(p), ftk = toks[fracPh[p]];
  240. if (p < lastSig || ftk.c === "0") fracOut[fracPh[p]] = fc || "0";
  241. else fracOut[fracPh[p]] = ftk.c === "?" ? " " : "";
  242. }
  243. var expOut = {};
  244. if (expAt >= 0) {
  245. var es = String(Math.abs(exponent));
  246. var need = expPh.length;
  247. while (es.length < need) es = "0" + es;
  248. for (p = 0; p < expPh.length; p++) expOut[expPh[p]] = p === 0 ? es.slice(0, es.length - expPh.length + 1) : es.charAt(es.length - expPh.length + p);
  249. }
  250. var out = "";
  251. for (i = 0; i < toks.length; i++) {
  252. var t = toks[i];
  253. if (t.t === "ph") {
  254. if (Object.prototype.hasOwnProperty.call(intOut, i)) out += intOut[i];
  255. else if (Object.prototype.hasOwnProperty.call(fracOut, i)) out += fracOut[i];
  256. else if (Object.prototype.hasOwnProperty.call(expOut, i)) out += expOut[i];
  257. } else if (t.t === "dot") {
  258. if (i === dotAt) out += ".";
  259. else out += ".";
  260. } else if (t.t === "comma") {
  261. // grouping / scaling commas are consumed
  262. } else if (t.t === "pct") out += "%";
  263. else if (t.t === "exp") out += "E" + (exponent < 0 ? "-" : (t.sign === "+" ? "+" : ""));
  264. else if (t.t === "lit") out += t.s;
  265. else if (t.t === "slash") out += "/";
  266. }
  267. if (neg && /[1-9]/.test(out)) out = "-" + out;
  268. return out;
  269. }
  270. function formatFraction(v, toks, slashAt) {
  271. var neg = v < 0;
  272. v = Math.abs(v);
  273. // placeholders before the slash: the last run is the numerator; any
  274. // run before it (separated by a literal / space) is the whole part
  275. var numPh = [], wholePh = [], i = slashAt - 1;
  276. while (i >= 0 && toks[i].t !== "ph") i--;
  277. while (i >= 0 && toks[i].t === "ph") { numPh.unshift(i); i--; }
  278. for (var k = i; k >= 0; k--) if (toks[k].t === "ph") wholePh.unshift(k);
  279. var denPh = [], fixedDen = 0, j = slashAt + 1, denDigits = "";
  280. while (j < toks.length && (toks[j].t === "ph" || (toks[j].t === "lit" && /^\d$/.test(toks[j].s)))) {
  281. if (toks[j].t === "ph") denPh.push(j); else denDigits += toks[j].s;
  282. j++;
  283. }
  284. if (denDigits) fixedDen = parseInt(denDigits, 10);
  285. var whole = wholePh.length ? Math.floor(v) : 0;
  286. var frac = fraction(v - whole, Math.max(1, denPh.length), fixedDen);
  287. if (frac[0] === frac[1] && frac[1] !== 0 && wholePh.length) { whole++; frac[0] = 0; }
  288. var out = "";
  289. for (i = 0; i < toks.length; i++) {
  290. var t = toks[i];
  291. if (t.t === "ph") {
  292. if (i === wholePh[wholePh.length - 1]) out += whole || (frac[0] ? "" : "0");
  293. else if (i === numPh[numPh.length - 1]) out += frac[0] || (whole ? "" : "0");
  294. else if (i === denPh[denPh.length - 1]) out += frac[0] || !whole ? frac[1] : "";
  295. } else if (t.t === "slash") out += (frac[0] || !whole) ? "/" : "";
  296. else if (t.t === "lit") {
  297. if (fixedDen && i > slashAt && /^\d$/.test(t.s)) {
  298. if (i === slashAt + 1) out += (frac[0] || !whole) ? String(fixedDen) : "";
  299. } else out += t.s;
  300. }
  301. }
  302. out = out.replace(/\s+$/, "");
  303. return (neg ? "-" : "") + out;
  304. }
  305. function passes(cond, v) {
  306. switch (cond.op) {
  307. case "<": return v < cond.v;
  308. case ">": return v > cond.v;
  309. case "<=": return v <= cond.v;
  310. case ">=": return v >= cond.v;
  311. case "=": return v === cond.v;
  312. case "<>": return v !== cond.v;
  313. }
  314. return true;
  315. }
  316. function format(value, code) {
  317. code = String(code === undefined || code === null ? "General" : code);
  318. var secs = sections(code).map(tokenize);
  319. if (typeof value === "string") {
  320. var ts = secs.length >= 4 ? secs[3] : secs.filter(function (s) { return s.some(function (t) { return t.t === "at"; }); })[0];
  321. if (!ts) return value;
  322. return ts.map(function (t) { return t.t === "at" ? value : t.t === "lit" ? t.s : ""; }).join("");
  323. }
  324. if (typeof value === "boolean") return value ? "TRUE" : "FALSE";
  325. var v = Number(value), toks, abs = false;
  326. if (secs.some(function (s) { return s.cond; })) {
  327. toks = null;
  328. for (var i = 0; i < Math.min(secs.length, 2); i++) {
  329. if (secs[i].cond && passes(secs[i].cond, v)) { toks = secs[i]; break; }
  330. }
  331. if (!toks) toks = secs[secs[1] && secs[1].cond ? 2 : 1] || secs[0];
  332. abs = toks !== secs[0] && v < 0;
  333. } else if (v > 0 || secs.length === 1) toks = secs[0];
  334. else if (v < 0) { toks = secs[1] || secs[0]; abs = !!secs[1]; }
  335. else toks = secs.length >= 3 ? secs[2] : secs[0];
  336. if (!toks.length) return "";
  337. if (abs) v = Math.abs(v);
  338. if (isDateFormat(toks)) {
  339. if (v < 0) return "#".repeat(8);
  340. return formatDate(v, toks);
  341. }
  342. return formatNumber(v, toks);
  343. }
  344. return { format: format };
  345. })();
  346. if (typeof module !== "undefined" && module.exports) {
  347. module.exports = SheetNumFmt;
  348. }