sheets_cf.js 32 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769
  1. /*
  2. ArozOS Office - Sheets: conditional formatting.
  3. Requires sheets.js (SheetsApp core API) and formula.js.
  4. Rules belong to CELLS, not to the sheet. Each cell lists the rules it
  5. carries and the sheet holds the rule bodies, keyed by id:
  6. cell.cf = ["cf-a1b2", ...] // this cell's rules, in order
  7. sheet.cfDefs["cf-a1b2"] = {
  8. anchor: "B2", // cell the relative refs are read from
  9. type: "gt" | "contains" | "formula" | ... (see CONDS)
  10. v1: "200", // operand(s), kept as typed
  11. v2: "", // second operand for between
  12. style: { bg, fc, b, i, u } // what a matching cell gets
  13. }
  14. Ownership per cell is what makes a rule behave like the rest of a cell's
  15. formatting: it shows up in the panel only for the cells that actually
  16. carry it, and it travels on move, copy and fill because those already
  17. move whole cell objects. Applying to a range simply stamps the id onto
  18. every cell in it, so range-wide rules still take one action.
  19. The id is shared, so editing a rule is copy-on-write: the edit mints a
  20. new def and swaps it onto just the cells being edited, leaving any other
  21. cells that happened to share the old rule alone. Defs nobody references
  22. are swept up afterwards.
  23. Rules are first-match-wins *per property*, the way Google Sheets does it:
  24. the topmost matching rule that sets a background decides the background,
  25. and a later rule can still contribute a text color the first one left
  26. alone. Only bg/fc/b/i/u are conditional - number format, alignment and
  27. borders always come from the cell's own style.
  28. Operands may be cell references or formulas ("=AVERAGE($F$2:$F$99)"),
  29. and a "Custom formula" rule evaluates its formula per cell. Both shift
  30. relative references by the cell's offset from the rule's anchor, so
  31. "=$F2>=SUM($C2:$E2)" stamped down a column tests every row against its
  32. own total.
  33. */
  34. var SheetsCF = (function () {
  35. "use strict";
  36. var Core = SheetsApp;
  37. var F = SheetFormula;
  38. function esc(t) { return OfficeApp.escapeHtml(t); }
  39. /* ================= condition catalogue ================= */
  40. /* args: how many operand boxes the editor shows.
  41. kind: which help text / placeholder the editor uses. */
  42. var CONDS = [
  43. { id: "notempty", label: "Is not empty", args: 0 },
  44. { id: "empty", label: "Is empty", args: 0 },
  45. { id: "contains", label: "Text contains", args: 1, kind: "text" },
  46. { id: "notcontains", label: "Text does not contain", args: 1, kind: "text" },
  47. { id: "startswith", label: "Text starts with", args: 1, kind: "text" },
  48. { id: "endswith", label: "Text ends with", args: 1, kind: "text" },
  49. { id: "exact", label: "Text is exactly", args: 1, kind: "text" },
  50. { id: "eq", label: "Is equal to", args: 1, kind: "num" },
  51. { id: "ne", label: "Is not equal to", args: 1, kind: "num" },
  52. { id: "gt", label: "Is greater than", args: 1, kind: "num" },
  53. { id: "gte", label: "Is greater than or equal to", args: 1, kind: "num" },
  54. { id: "lt", label: "Is less than", args: 1, kind: "num" },
  55. { id: "lte", label: "Is less than or equal to", args: 1, kind: "num" },
  56. { id: "between", label: "Is between", args: 2, kind: "num" },
  57. { id: "notbetween", label: "Is not between", args: 2, kind: "num" },
  58. { id: "dbefore", label: "Date is before", args: 1, kind: "date" },
  59. { id: "dafter", label: "Date is after", args: 1, kind: "date" },
  60. { id: "don", label: "Date is", args: 1, kind: "date" },
  61. { id: "formula", label: "Custom formula is", args: 1, kind: "formula" }
  62. ];
  63. function condById(id) {
  64. for (var i = 0; i < CONDS.length; i++) if (CONDS[i].id === id) return CONDS[i];
  65. return CONDS[0];
  66. }
  67. var DEFAULT_STYLE = { bg: "#b7e1cd", fc: "", b: false, i: false, u: false };
  68. /* ================= value helpers ================= */
  69. function textOf(v) {
  70. if (v === null || v === undefined) return "";
  71. if (F.isErr(v)) return v.code;
  72. if (typeof v === "boolean") return v ? "TRUE" : "FALSE";
  73. if (typeof v === "number") return F.numToText(v);
  74. return String(v);
  75. }
  76. // numeric view of a value, or null when it is not a number
  77. function numOf(v) {
  78. if (typeof v === "number") return v;
  79. if (typeof v === "boolean") return v ? 1 : 0;
  80. if (typeof v === "string") {
  81. var t = v.trim();
  82. if (t !== "" && !isNaN(Number(t))) return Number(t);
  83. }
  84. return null;
  85. }
  86. /* Date operands are typed by hand, so accept both the ISO form the app
  87. displays and the M/D/Y form a user is likely to paste. */
  88. function dateSerialOf(s) {
  89. var t = String(s).trim();
  90. var m = /^(\d{4})-(\d{1,2})-(\d{1,2})$/.exec(t);
  91. if (m) return F.dateToSerial(new Date(+m[1], +m[2] - 1, +m[3]));
  92. m = /^(\d{1,2})[/](\d{1,2})[/](\d{4})$/.exec(t);
  93. if (m) return F.dateToSerial(new Date(+m[3], +m[1] - 1, +m[2]));
  94. return null;
  95. }
  96. /* ================= compiled formulas =================
  97. Parsing per cell would be far too slow on a repainting grid, so each
  98. distinct formula is parsed once and its reference nodes are collected.
  99. Evaluating for a cell just rewrites those nodes in place - relative
  100. refs shifted by the cell's offset from the range anchor, absolute ones
  101. left alone - which is what makes "=$F2>100" mean row-by-row. */
  102. var compiled = {}; // formula source -> {ast, refs} | null when broken
  103. function collectRefs(node, out) {
  104. if (!node || typeof node !== "object") return;
  105. if (node.t === "ref") {
  106. out.push({ n: node, c: node.col, r: node.row, absC: !!node.absC, absR: !!node.absR });
  107. return;
  108. }
  109. if (node.t === "range") {
  110. [node.a, node.b].forEach(function (e) {
  111. out.push({ n: e, c: e.col, r: e.row, absC: !!e.absC, absR: !!e.absR });
  112. });
  113. return;
  114. }
  115. Object.keys(node).forEach(function (k) {
  116. var v = node[k];
  117. if (Array.isArray(v)) v.forEach(function (x) { collectRefs(x, out); });
  118. else if (v && typeof v === "object") collectRefs(v, out);
  119. });
  120. }
  121. function compile(src) {
  122. var body = String(src).replace(/^\s*=/, "").trim();
  123. if (body === "") return null;
  124. if (!Object.prototype.hasOwnProperty.call(compiled, body)) {
  125. var entry = null;
  126. try {
  127. var ast = F.parse(body);
  128. var refs = [];
  129. collectRefs(ast, refs);
  130. entry = { ast: ast, refs: refs };
  131. } catch (e) {
  132. entry = null; // a malformed rule simply never matches
  133. }
  134. compiled[body] = entry;
  135. }
  136. return compiled[body];
  137. }
  138. // shift the compiled formula's relative refs, then evaluate it
  139. function evalAt(entry, dC, dR) {
  140. if (!entry) return null;
  141. var ok = true;
  142. entry.refs.forEach(function (x) {
  143. var c = x.absC ? x.c : x.c + dC;
  144. var r = x.absR ? x.r : x.r + dR;
  145. if (c < 0 || r < 0) ok = false;
  146. x.n.col = c;
  147. x.n.row = r;
  148. });
  149. if (!ok) return null; // shifted off the sheet: treat as no match
  150. try {
  151. return F.evaluate(entry.ast, Core.calcCtx());
  152. } catch (e) {
  153. return null;
  154. }
  155. }
  156. // a bare A1-style reference or range and nothing else: "B3", "$B$3", "B1:B3"
  157. var REF_RE = /^\$?[A-Za-z]{1,3}\$?\d+(?::\$?[A-Za-z]{1,3}\$?\d+)?$/;
  158. /*
  159. An operand is a literal the user typed, a cell reference, or a
  160. formula. References and formulas are shifted per cell exactly like a
  161. custom formula is, so a rule over B2:B10 comparing against "C2" tests
  162. every row against its own C - and a single-cell rule shifts by zero,
  163. which is simply the cell named.
  164. A bare "B3" counts as a reference for the number and date tests: the
  165. literal text could never match one of those anyway. Text tests keep
  166. it literal, because product codes really do look like "B3"; write
  167. "=B3" there when the cell is what is meant.
  168. */
  169. function operandValue(raw, cond, dC, dR) {
  170. var s = String(raw === undefined || raw === null ? "" : raw).trim();
  171. if (s === "") return null;
  172. if (s.charAt(0) === "=") return evalAt(compile(s), dC, dR);
  173. if (cond && cond.kind !== "text" && REF_RE.test(s)) {
  174. return evalAt(compile(s), dC, dR);
  175. }
  176. return F.literalValue(s);
  177. }
  178. /* ================= rule evaluation ================= */
  179. function matches(rule, c, r, anchor) {
  180. var cond = condById(rule.type);
  181. if (cond.id === "formula") {
  182. var v = evalAt(compile(rule.v1), c - anchor.c, r - anchor.r);
  183. if (v === null || F.isErr(v)) return false;
  184. if (typeof v === "boolean") return v;
  185. if (typeof v === "number") return v !== 0;
  186. return String(v).toUpperCase() === "TRUE";
  187. }
  188. var val = Core.valueAt(c, r);
  189. if (F.isErr(val)) return false;
  190. var isBlank = val === null || val === undefined || val === "";
  191. if (cond.id === "empty") return isBlank;
  192. if (cond.id === "notempty") return !isBlank;
  193. if (isBlank) return false; // every other test needs something to test
  194. var dC = c - anchor.c, dR = r - anchor.r;
  195. var a = operandValue(rule.v1, cond, dC, dR);
  196. if (cond.kind === "text") {
  197. var hay = textOf(val).toLowerCase();
  198. var needle = textOf(a).toLowerCase();
  199. if (needle === "") return false;
  200. switch (cond.id) {
  201. case "contains": return hay.indexOf(needle) >= 0;
  202. case "notcontains": return hay.indexOf(needle) < 0;
  203. case "startswith": return hay.lastIndexOf(needle, 0) === 0;
  204. case "endswith": return hay.length >= needle.length &&
  205. hay.indexOf(needle, hay.length - needle.length) >= 0;
  206. case "exact": return hay === needle;
  207. }
  208. return false;
  209. }
  210. if (cond.kind === "date") {
  211. var cellSerial = numOf(val);
  212. var want = numOf(a);
  213. if (want === null) want = dateSerialOf(rule.v1);
  214. if (cellSerial === null || want === null) return false;
  215. // compare whole days, so a timestamp still matches its date
  216. var cd = Math.floor(cellSerial), wd = Math.floor(want);
  217. if (cond.id === "dbefore") return cd < wd;
  218. if (cond.id === "dafter") return cd > wd;
  219. return cd === wd;
  220. }
  221. // numeric comparisons; equality also works for plain text
  222. var n = numOf(val), an = numOf(a);
  223. if (cond.id === "eq" || cond.id === "ne") {
  224. var same = (n !== null && an !== null) ? n === an :
  225. textOf(val).toLowerCase() === textOf(a).toLowerCase();
  226. return cond.id === "eq" ? same : !same;
  227. }
  228. if (n === null || an === null) return false;
  229. switch (cond.id) {
  230. case "gt": return n > an;
  231. case "gte": return n >= an;
  232. case "lt": return n < an;
  233. case "lte": return n <= an;
  234. case "between":
  235. case "notbetween": {
  236. var b = numOf(operandValue(rule.v2, cond, dC, dR));
  237. if (b === null) return false;
  238. var lo = Math.min(an, b), hi = Math.max(an, b);
  239. var within = n >= lo && n <= hi;
  240. return cond.id === "between" ? within : !within;
  241. }
  242. }
  243. return false;
  244. }
  245. /* ================= storage ================= */
  246. var anchorCache = {}; // anchor cell key -> {c,r}
  247. // caches are keyed by text, so they only need clearing when rules change
  248. function invalidate() {
  249. compiled = {};
  250. anchorCache = {};
  251. }
  252. function defs() {
  253. var s = Core.sheet();
  254. if (!s.cfDefs || typeof s.cfDefs !== "object") s.cfDefs = {};
  255. return s.cfDefs;
  256. }
  257. function defOf(id) { return defs()[id] || null; }
  258. function anchorOf(def) {
  259. var k = (def && def.anchor) || "A1";
  260. if (!Object.prototype.hasOwnProperty.call(anchorCache, k)) {
  261. var p = F.parseCellKey(k);
  262. anchorCache[k] = p ? { c: p.col, r: p.row } : { c: 0, r: 0 };
  263. }
  264. return anchorCache[k];
  265. }
  266. // ids on one cell, in the order they were applied
  267. function idsAt(c, r) {
  268. var cell = Core.sheet().cells[F.cellName(c, r)];
  269. return (cell && Array.isArray(cell.cf)) ? cell.cf : null;
  270. }
  271. /* ================= render hook ================= */
  272. /*
  273. The conditional part of a cell's style, or null when no rule applies.
  274. Called for every painted cell, so it does nothing at all until the
  275. cell actually carries a rule.
  276. */
  277. function styleFor(c, r) {
  278. var ids = idsAt(c, r);
  279. if (!ids || !ids.length) return null;
  280. var out = null;
  281. for (var i = 0; i < ids.length; i++) {
  282. var def = defOf(ids[i]);
  283. if (!def || !def.style) continue;
  284. if (!matches(def, c, r, anchorOf(def))) continue;
  285. var st = def.style;
  286. if (!out) out = {};
  287. // first rule to set a property owns it
  288. if (st.bg && !out.bg) out.bg = st.bg;
  289. if (st.fc && !out.fc) out.fc = st.fc;
  290. if (st.b && !out.b) out.b = true;
  291. if (st.i && !out.i) out.i = true;
  292. if (st.u && !out.u) out.u = true;
  293. }
  294. return out;
  295. }
  296. /*
  297. Inserting or deleting rows/columns moves the cells themselves - and
  298. their rule ids with them - so only the anchors that relative
  299. references are measured from have to be adjusted.
  300. */
  301. function shiftAnchors(axis, index, count) {
  302. var d = defs();
  303. Object.keys(d).forEach(function (id) {
  304. var p = F.parseCellKey(d[id].anchor || "A1");
  305. if (!p) return;
  306. var v = axis === "col" ? p.col : p.row;
  307. if (count > 0) { if (v >= index) v += count; }
  308. else {
  309. var del = -count;
  310. if (v >= index + del) v -= del;
  311. else if (v >= index) v = index;
  312. }
  313. d[id].anchor = F.cellName(axis === "col" ? v : p.col,
  314. axis === "row" ? v : p.row);
  315. });
  316. invalidate();
  317. }
  318. /* ================= per-cell bookkeeping ================= */
  319. var MAX_STAMP = 50000; // guard against "apply to the whole sheet"
  320. function eachCellIn(rg, fn) {
  321. for (var r = rg.r1; r <= rg.r2; r++) {
  322. for (var c = rg.c1; c <= rg.c2; c++) fn(c, r);
  323. }
  324. }
  325. function rangeCellCount(rg) {
  326. return (rg.c2 - rg.c1 + 1) * (rg.r2 - rg.r1 + 1);
  327. }
  328. // stamp a rule id onto every cell of a range, replacing `replaces` when given
  329. function stampRange(rg, id, replaces) {
  330. eachCellIn(rg, function (c, r) {
  331. var cell = Core.cellObj(c, r, true);
  332. if (!Array.isArray(cell.cf)) cell.cf = [];
  333. var at = replaces ? cell.cf.indexOf(replaces) : -1;
  334. if (at >= 0) cell.cf[at] = id;
  335. else if (cell.cf.indexOf(id) < 0) cell.cf.push(id);
  336. });
  337. }
  338. function unstampRange(rg, id) {
  339. eachCellIn(rg, function (c, r) {
  340. var cell = Core.sheet().cells[F.cellName(c, r)];
  341. if (!cell || !Array.isArray(cell.cf)) return;
  342. cell.cf = cell.cf.filter(function (x) { return x !== id; });
  343. if (!cell.cf.length) delete cell.cf;
  344. Core.pruneCell(c, r);
  345. });
  346. }
  347. // forget rule bodies no cell refers to any more
  348. function sweepDefs() {
  349. var s = Core.sheet();
  350. if (!s.cfDefs) return;
  351. var live = {};
  352. Object.keys(s.cells).forEach(function (k) {
  353. var cell = s.cells[k];
  354. if (cell && cell.cf) cell.cf.forEach(function (id) { live[id] = true; });
  355. });
  356. Object.keys(s.cfDefs).forEach(function (id) {
  357. if (!live[id]) delete s.cfDefs[id];
  358. });
  359. }
  360. /*
  361. The rules present on the current selection, in the order the anchor
  362. cell lists them, each with the block of selected cells carrying it.
  363. This is what the panel shows - so it only ever describes the cells
  364. the user has actually got selected.
  365. */
  366. function rulesInSelection() {
  367. var sel = Core.selRange();
  368. var seen = {}, order = [];
  369. eachCellIn(sel, function (c, r) {
  370. var ids = idsAt(c, r);
  371. if (!ids) return;
  372. ids.forEach(function (id) {
  373. if (!defOf(id)) return;
  374. var e = seen[id];
  375. if (!e) {
  376. e = seen[id] = { id: id, def: defOf(id), n: 0,
  377. c1: c, c2: c, r1: r, r2: r };
  378. order.push(e);
  379. }
  380. e.n++;
  381. if (c < e.c1) e.c1 = c;
  382. if (c > e.c2) e.c2 = c;
  383. if (r < e.r1) e.r1 = r;
  384. if (r > e.r2) e.r2 = r;
  385. });
  386. });
  387. return order;
  388. }
  389. function selectionHasRules() {
  390. var sel = Core.selRange();
  391. var found = false;
  392. eachCellIn(sel, function (c, r) {
  393. if (found) return;
  394. var ids = idsAt(c, r);
  395. if (ids && ids.length) found = true;
  396. });
  397. return found;
  398. }
  399. /* ================= rule descriptions ================= */
  400. function describe(rule) {
  401. var cond = condById(rule.type);
  402. if (cond.args === 0) return cond.label;
  403. if (cond.id === "between" || cond.id === "notbetween") {
  404. return cond.label + " " + (rule.v1 || "?") + " and " + (rule.v2 || "?");
  405. }
  406. return cond.label + " " + (rule.v1 || "?");
  407. }
  408. function swatchCss(st) {
  409. var css = "background:" + (st.bg || "transparent") + ";";
  410. if (st.fc) css += "color:" + st.fc + ";";
  411. if (st.b) css += "font-weight:700;";
  412. if (st.i) css += "font-style:italic;";
  413. if (st.u) css += "text-decoration:underline;";
  414. return css;
  415. }
  416. /* ================= editor UI ================= */
  417. function genId() {
  418. return "cf-" + Date.now().toString(36) + Math.random().toString(36).substring(2, 6);
  419. }
  420. // an editor draft: the rule body plus the cells it is being applied to
  421. function newDraft() {
  422. return {
  423. id: null, // null = not saved yet
  424. range: tidyRef(Core.rangeStr(Core.selRange())),
  425. anchor: "", type: "notempty", v1: "", v2: "",
  426. style: {
  427. bg: DEFAULT_STYLE.bg, fc: DEFAULT_STYLE.fc,
  428. b: false, i: false, u: false
  429. }
  430. };
  431. }
  432. function draftFrom(entry) {
  433. return {
  434. id: entry.id,
  435. range: tidyRef(Core.rangeStr({
  436. c1: entry.c1, r1: entry.r1, c2: entry.c2, r2: entry.r2
  437. })),
  438. anchor: entry.def.anchor || "",
  439. type: entry.def.type, v1: entry.def.v1 || "", v2: entry.def.v2 || "",
  440. style: $.extend({}, entry.def.style)
  441. };
  442. }
  443. function commitRules() {
  444. sweepDefs();
  445. invalidate();
  446. Core.commit();
  447. Core.renderAll();
  448. }
  449. // the grid picker always hands back "B3:B3" for one cell; say "B3"
  450. function tidyRef(rgStr) {
  451. var p = String(rgStr).split(":");
  452. return (p.length === 2 && p[0] === p[1]) ? p[0] : String(rgStr);
  453. }
  454. /*
  455. A text box with a crosshair tucked into its right edge: click it,
  456. drag on the grid, and the reference lands back in the box. It goes in
  457. at the caret when the box has focus, so a range can be dropped into
  458. the middle of a half-typed formula; otherwise it replaces the whole
  459. value, which is what clicking straight into an untouched box means.
  460. */
  461. function refInput(id, placeholder, value, onInput) {
  462. var $wrap = $('<div class="sh-cf-refwrap"></div>');
  463. var $in = $('<input type="text">')
  464. .attr({ id: id, placeholder: placeholder }).val(value);
  465. var $btn = $('<button type="button" class="sh-cf-refpick" ' +
  466. 'title="Select a cell or range on the grid"><i class="crosshairs icon"></i></button>');
  467. // keep the caret where it was: a plain click would blur the input first
  468. $btn.on("mousedown", function (e) { e.preventDefault(); });
  469. $btn.on("click", function () {
  470. var el = $in[0];
  471. var focused = document.activeElement === el;
  472. var from = focused ? el.selectionStart : null;
  473. var to = focused ? el.selectionEnd : null;
  474. Core.pickRangeFromGrid(function (rgStr) {
  475. if (!rgStr) return;
  476. var ref = tidyRef(rgStr);
  477. var pos;
  478. if (from === null) {
  479. el.value = ref;
  480. pos = ref.length;
  481. } else {
  482. el.value = el.value.slice(0, from) + ref + el.value.slice(to);
  483. pos = from + ref.length;
  484. }
  485. $in.trigger("input").trigger("change");
  486. el.focus();
  487. try { el.setSelectionRange(pos, pos); } catch (e) { }
  488. });
  489. });
  490. if (onInput) $in.on("input", onInput);
  491. return $wrap.append($in).append($btn);
  492. }
  493. // the dialog swaps between the rule list and the single-rule editor
  494. function open() {
  495. var $body = $('<div class="sh-cf"></div>');
  496. OfficeApp.dialog({
  497. title: "Conditional format rules",
  498. body: $body,
  499. buttons: [{ label: "Done", primary: true }]
  500. });
  501. showList($body);
  502. }
  503. function showList($body) {
  504. var sel = tidyRef(Core.rangeStr(Core.selRange()));
  505. var list = rulesInSelection();
  506. $body.empty();
  507. $body.append($('<div class="sh-cf-scope"></div>')
  508. .text("Rules on " + sel));
  509. if (!list.length) {
  510. $body.append('<div class="sh-cf-empty">These cells carry no rules. ' +
  511. 'A rule belongs to the cells you apply it to and travels with them ' +
  512. 'when they are moved, copied or filled.</div>');
  513. }
  514. var $rows = $('<div class="sh-cf-list"></div>');
  515. list.forEach(function (entry) {
  516. var covers = tidyRef(Core.rangeStr({
  517. c1: entry.c1, r1: entry.r1, c2: entry.c2, r2: entry.r2
  518. }));
  519. var $row = $('<div class="sh-cf-row"></div>');
  520. $row.append($('<div class="sh-cf-swatch"></div>')
  521. .attr("style", swatchCss(entry.def.style || {})).text("123"));
  522. $row.append($('<div class="sh-cf-info"><div class="sh-cf-desc"></div>' +
  523. '<div class="sh-cf-range"></div></div>')
  524. .find(".sh-cf-desc").text(describe(entry.def)).end()
  525. .find(".sh-cf-range").text(
  526. covers + (entry.n > 1 ? " - " + entry.n + " cells" : "")).end());
  527. var $del = $('<button type="button" class="of-tbtn sh-cf-del" ' +
  528. 'title="Remove this rule from the selected cells">' +
  529. '<i class="trash alternate outline icon"></i></button>');
  530. $del.on("click", function (e) {
  531. e.stopPropagation();
  532. unstampRange(Core.selRange(), entry.id);
  533. commitRules();
  534. showList($body);
  535. });
  536. $row.append($del);
  537. $row.on("click", function () { showEditor($body, draftFrom(entry), false); });
  538. $rows.append($row);
  539. });
  540. $body.append($rows);
  541. var $add = $('<button type="button" class="of-btn sh-cf-add">' +
  542. '<i class="plus icon"></i> Add another rule</button>');
  543. $add.on("click", function () { showEditor($body, newDraft(), true); });
  544. $body.append($add);
  545. }
  546. function showEditor($body, rule, isNew) {
  547. $body.empty();
  548. var $ed = $(
  549. '<label>Apply to range</label>' +
  550. '<div id="shCfRangeSlot"></div>' +
  551. '<label style="margin-top:10px;">Format cells if...</label>' +
  552. '<select id="shCfType"></select>' +
  553. '<div id="shCfArgs"></div>' +
  554. '<div class="sh-cf-hint" id="shCfHint"></div>' +
  555. '<label style="margin-top:10px;">Formatting style</label>' +
  556. '<div class="sh-cf-style">' +
  557. '<div class="sh-cf-preview" id="shCfPreview">123</div>' +
  558. '<button type="button" class="of-tbtn" id="shCfB" title="Bold"><i class="bold icon"></i></button>' +
  559. '<button type="button" class="of-tbtn" id="shCfI" title="Italic"><i class="italic icon"></i></button>' +
  560. '<button type="button" class="of-tbtn" id="shCfU" title="Underline"><i class="underline icon"></i></button>' +
  561. '<span id="shCfFcSlot"></span><span id="shCfBgSlot"></span>' +
  562. "</div>"
  563. );
  564. $body.append($ed);
  565. var draft = rule; // already a draft object (newDraft / draftFrom)
  566. $body.find("#shCfRangeSlot").append(
  567. refInput("shCfRange", "A1:D20", draft.range, function () {
  568. draft.range = $(this).val().trim();
  569. }));
  570. $body.find("#shCfRange").on("change", function () {
  571. draft.range = $(this).val().trim();
  572. });
  573. var $type = $body.find("#shCfType");
  574. CONDS.forEach(function (cd) {
  575. $type.append($("<option></option>").attr("value", cd.id).text(cd.label));
  576. });
  577. $type.val(draft.type);
  578. var HINTS = {
  579. formula: "Relative references are read from the top-left cell of the range, " +
  580. "so =$F2&gt;=SUM($C2:$E2) tests every row against its own total.",
  581. text: "Matching ignores upper/lower case. Write =B3 to compare against " +
  582. "a cell rather than the text &quot;B3&quot;.",
  583. date: "A date as YYYY-MM-DD or MM/DD/YYYY, a cell such as B3, or a formula.",
  584. num: "A number, a cell such as B3, or a formula such as " +
  585. "=AVERAGE($F$2:$F$99). Relative references shift per row."
  586. };
  587. function renderArgs() {
  588. var cond = condById(draft.type);
  589. var $args = $body.find("#shCfArgs").empty();
  590. var ph = cond.kind === "formula" ? "=$F2>100" :
  591. (cond.kind === "date" ? "2026-01-31, B3 or =formula" :
  592. (cond.kind === "text" ? "text to look for" : "value, B3 or =formula"));
  593. if (cond.args >= 1) {
  594. $args.append(refInput("shCfV1", ph, draft.v1, function () {
  595. draft.v1 = $(this).val();
  596. }).css("margin-top", "6px"));
  597. }
  598. if (cond.args >= 2) {
  599. $args.append(refInput("shCfV2", "and", draft.v2, function () {
  600. draft.v2 = $(this).val();
  601. }).css("margin-top", "6px"));
  602. }
  603. $body.find("#shCfHint").html(HINTS[cond.kind] || "");
  604. }
  605. $type.on("change", function () {
  606. draft.type = $(this).val();
  607. renderArgs();
  608. });
  609. renderArgs();
  610. function paintPreview() {
  611. $body.find("#shCfPreview").attr("style", swatchCss(draft.style));
  612. $body.find("#shCfB").toggleClass("active", !!draft.style.b);
  613. $body.find("#shCfI").toggleClass("active", !!draft.style.i);
  614. $body.find("#shCfU").toggleClass("active", !!draft.style.u);
  615. }
  616. [["#shCfB", "b"], ["#shCfI", "i"], ["#shCfU", "u"]].forEach(function (p) {
  617. $body.find(p[0]).on("click", function () {
  618. draft.style[p[1]] = !draft.style[p[1]];
  619. paintPreview();
  620. });
  621. });
  622. var $fc = OfficeColorPicker.swatchInput({
  623. id: "shCfFc", title: "Text color", value: draft.style.fc || "#202124",
  624. allowNone: true, noneLabel: "Automatic"
  625. });
  626. $fc.on("change", function () {
  627. draft.style.fc = $fc.val() || "";
  628. paintPreview();
  629. });
  630. $body.find("#shCfFcSlot").append($fc);
  631. var $bg = OfficeColorPicker.swatchInput({
  632. id: "shCfBg", title: "Fill color", value: draft.style.bg || DEFAULT_STYLE.bg,
  633. allowNone: true, noneLabel: "No fill"
  634. });
  635. $bg.on("change", function () {
  636. draft.style.bg = $bg.val() || "";
  637. paintPreview();
  638. });
  639. $body.find("#shCfBgSlot").append($bg);
  640. paintPreview();
  641. var $actions = $('<div class="sh-cf-actions"></div>');
  642. var $cancel = $('<button type="button" class="of-btn">Cancel</button>');
  643. $cancel.on("click", function () { showList($body); });
  644. var $save = $('<button type="button" class="of-btn primary">' +
  645. (isNew ? "Add rule" : "Save rule") + "</button>");
  646. $save.on("click", function () {
  647. if (!Core.parseRange(draft.range)) {
  648. OfficeApp.toast("Invalid range: " + draft.range, "error");
  649. return;
  650. }
  651. var cond = condById(draft.type);
  652. if (cond.args >= 1 && String(draft.v1).trim() === "") {
  653. OfficeApp.toast("This condition needs a value", "error");
  654. return;
  655. }
  656. if (cond.kind === "formula" && !compile(draft.v1)) {
  657. OfficeApp.toast("That formula cannot be parsed", "error");
  658. return;
  659. }
  660. if (!draft.style.bg && !draft.style.fc &&
  661. !draft.style.b && !draft.style.i && !draft.style.u) {
  662. OfficeApp.toast("Pick at least one formatting change", "error");
  663. return;
  664. }
  665. var rg = Core.parseRange(draft.range);
  666. if (rangeCellCount(rg) > MAX_STAMP) {
  667. OfficeApp.toast("That is " + rangeCellCount(rg) + " cells - apply the rule " +
  668. "to at most " + MAX_STAMP + " at a time", "error");
  669. return;
  670. }
  671. /*
  672. Copy-on-write: the edit always mints a new rule body and swaps
  673. it in over the cells being edited. Cells elsewhere that happen
  674. to share the old rule keep it, which is what per-cell
  675. ownership has to mean once a rule can be copied around.
  676. */
  677. var id = genId();
  678. defs()[id] = {
  679. // relative references are read from where the rule was applied
  680. anchor: draft.anchor || F.cellName(rg.c1, rg.r1),
  681. type: draft.type, v1: draft.v1, v2: draft.v2,
  682. style: $.extend({}, draft.style)
  683. };
  684. if (draft.id) unstampRange(Core.selRange(), draft.id);
  685. stampRange(rg, id, null);
  686. commitRules();
  687. showList($body);
  688. });
  689. $actions.append($cancel).append($save);
  690. if (!isNew) {
  691. var $del = $('<button type="button" class="of-btn danger">Delete</button>');
  692. $del.on("click", function () {
  693. unstampRange(Core.selRange(), draft.id);
  694. commitRules();
  695. showList($body);
  696. });
  697. $actions.prepend($del);
  698. }
  699. $body.append($actions);
  700. }
  701. /* clear every rule that covers the current selection */
  702. /* strip every rule from the selected cells, leaving other cells alone */
  703. function clearForSelection() {
  704. if (!selectionHasRules()) {
  705. OfficeApp.toast("The selected cells have no conditional formatting", "error");
  706. return;
  707. }
  708. var sel = Core.selRange();
  709. var n = 0;
  710. eachCellIn(sel, function (c, r) {
  711. var cell = Core.sheet().cells[F.cellName(c, r)];
  712. if (!cell || !cell.cf) return;
  713. n++;
  714. delete cell.cf;
  715. Core.pruneCell(c, r);
  716. });
  717. commitRules();
  718. OfficeApp.setStatus("Cleared conditional formatting from " + n + " cell(s)");
  719. }
  720. return {
  721. styleFor: styleFor,
  722. shiftAnchors: shiftAnchors,
  723. invalidate: invalidate,
  724. open: open,
  725. clearForSelection: clearForSelection,
  726. selectionHasRules: selectionHasRules,
  727. rulesInSelection: rulesInSelection,
  728. describe: describe,
  729. conditions: CONDS
  730. };
  731. })();