collective
Browse / github.com/storytold/gridcraft / patch
Conditional formatting: a colour scale interpolates between its stops as Excel does
Conditional formatting: a colour scale interpolates between its stops as Excel does ## Problem A colour scale's in-between values are painted one byte off Excel. The review of the colour-scale formula stops (#1885) reported it: at the exact halfway point of a red-to-green scale Excel gives `807F00` (red 128, green **127**) where GridCraft gives 128,128,0. It is not only the halfway point. Measured over the shape of the colour scale used by that PR's tests (values 0..20, stops 0 red, 20 green), GridCraft differs from Excel on **11 of the 20 values**: | Value | Excel | GridCraft before | |---|---|---| | 1 | 243,12 | 242,13 | | 7 | 166,89 | 165,89 | | 10 | 128,127 | 128,128 |
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- zyzabruh (imported from pull request #2709, open)
- Base
d8c5f5885fc5320f8f1023d5fdc9f484a185ba86- Size
- +41 −7 in 3 files
- Review
- pending
- Shared
- 2026-10-28
- Id
87f4eef76929149cb86269f3
How it went
No other operator has reported using it yet.
Diff
raw · ROADMAP.md crates/engine/src/cf.rs docs/target-app-parity.md
@@ -2,7 +2,7 @@ **Stage: alpha** · next: beta, ~25 points of "ready for real work" and ~160–240 h away -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right, and fills a selection's empty last row and column) · **Target:** Microsoft Excel (Microsoft 365)+> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (colour scales paint the values between their stops as Excel does) · **Target:** Microsoft Excel (Microsoft 365) GridCraft aims at full Microsoft Excel parity, and at being better in places: faster, open (XLSX is the native format), fully scriptable by agents (MCP, control channel, CLI), and on the web.@@ -135,6 +135,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---|+| 2026-10-26 | Conditional formatting: a value between two colour-scale stops takes the colour Excel paints. Each channel moves from the earlier stop's byte toward the later one and the fraction is dropped, so a channel that rises is floored and one that falls is ceiled: red → green over 0..20 reads 243/12 at value 1 and 128/127 at the halfway point, where rounding gave 242/13 and 128/128 (measured in Excel over a whole ramp) | | 2026-10-25 | AutoSum over a selection does what Excel does: a selected row gets one total to its right (in its last cell if that is empty), not a one-cell total under each cell; a block whose last column is empty gets each row's total there, one whose last row is empty each column's, and with both the corner gets the total of the block; otherwise totals go under each column as before | | 2026-10-24 | Go To lists the named ranges of every sheet (`Sheet2!Name` for a name scoped to another sheet; hidden names, constants and formulas left out) and the references entered before, a double-click goes to an entry, and the Reference box takes several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30`) | | 2026-10-24 | Status bar: a right-click opens a menu that sets which selection statistics show (Average, Count and Sum by default; Numerical Count, Minimum and Maximum are new), kept with the preferences (`view.statusBar`) |@@ -242,6 +243,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | Change | Summary | |---|---|---|+| 2026-10-26 | minor | Progress log: colour scales paint the values between their stops as Excel does | | 2026-10-25 | minor | Progress log: AutoSum over a selected row, and a selection's empty last row and column | | 2026-10-23 | minor | Progress log: accounting negatives in number text | | 2026-10-23 | minor | Progress log: INDEX with one index on a 2-D reference is #REF! |@@ -125,13 +125,16 @@ fn threshold(wb: &Workbook, sheet: usize, at: CellRef, st: &RuleStats, k: &CfVal } } +/// The colour `t` of the way from `a` to `b`, as Excel paints it.+///+/// Excel moves each channel from `a`'s byte toward `b`'s and drops the fraction instead of+/// rounding it, so a channel that rises is floored while one that falls is ceiled: the halfway+/// point of red → green is `[128, 127, 0]`, not `[128, 128, 0]`. Measured in Excel over a whole+/// ramp (#1848). fn lerp(a: [u8; 3], b: [u8; 3], t: f64) -> [u8; 3] { let t = t.clamp(0.0, 1.0);- [- (a[0] as f64 + (b[0] as f64 - a[0] as f64) * t).round() as u8,- (a[1] as f64 + (b[1] as f64 - a[1] as f64) * t).round() as u8,- (a[2] as f64 + (b[2] as f64 - a[2] as f64) * t).round() as u8,- ]+ let mix = |i: usize| (a[i] as f64 + ((b[i] as f64 - a[i] as f64) * t).trunc()) as u8;+ [mix(0), mix(1), mix(2)] } pub use gridcraft_model::icon_count;@@ -503,6 +506,34 @@ mod tests { assert_eq!(look("A4").and_then(|l| l.scale_fill), Some([0x63, 0xBE, 0x7B])); } + /// In-between values take Excel's colour: each channel moves from the earlier stop's byte+ /// toward the later one and the fraction is dropped, so red → green over 0..20 reads 243/12+ /// at 1 and 128/127 at the halfway point (measured in Excel, #1848).+ #[test]+ fn color_scale_interpolates_each_channel_toward_its_start_colour() {+ let mut s = crate::Session::new();+ s.new_workbook();+ let values: Vec<serde_json::Value> = (0..=20).map(|n| json!([n])).collect();+ s.execute("range.setValues", json!({"range": "A1", "values": values})).unwrap();+ let sh = std::sync::Arc::make_mut(&mut s.doc_mut().unwrap().wb).sheet_mut(0).unwrap();+ sh.cond_formats = vec![CondFormat {+ ranges: vec![RangeRef::parse("A1:A21").unwrap()],+ rule: CfRule::ColorScale { stops: vec![(CfValueKind::Number(0.0), Color::rgb(0xFF, 0, 0)), (CfValueKind::Max, Color::rgb(0, 0xFF, 0))] },+ priority: 1,+ stop_if_true: false,+ }];+ let fill = |a: &str| {+ let d = s.doc().unwrap();+ CfCache::new().look(&d.wb, 0, CellRef::parse(a).unwrap()).and_then(|l| l.scale_fill)+ };+ // The rising green channel is floored, the falling red one ceiled.+ assert_eq!(fill("A1"), Some([0xFF, 0, 0]));+ assert_eq!(fill("A2"), Some([243, 12, 0]));+ assert_eq!(fill("A11"), Some([0x80, 0x7F, 0]));+ assert_eq!(fill("A14"), Some([90, 165, 0]));+ assert_eq!(fill("A21"), Some([0, 0xFF, 0]));+ }+ /// Looking cells up does work in proportion to the cells asked for, not the rule's range: the /// statistics are only gathered for rules that read them, and once per rule. #[test]@@ -1,6 +1,6 @@ # Target-app parity: GridCraft vs Microsoft Excel -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-22 · **Change:** minor (several windows: a native window per workbook window, New Window on the same workbook, Arrange All; no score change) · **Target:** Microsoft Excel (Microsoft 365)+> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (colour scales paint the values between their stops as Excel does; no score change) · **Target:** Microsoft Excel (Microsoft 365) This is the authoritative parity assessment. [`ROADMAP.md`](../ROADMAP.md) summarizes it, [`gaps.md`](gaps.md) lists every shortfall, and the area checklists hold the detail:@@ -334,6 +334,7 @@ The inventory carried over from the 2026-10-07 ROADMAP.md, updated for what land | Date | Change | Summary | |---|---|---|+| 2026-10-26 | minor | Conditional-formatting colour scales: a value between two stops takes the colour Excel paints, each channel moving from the earlier stop's byte and dropping the fraction (red → green halfway is 128,127,0 where rounding gave 128,128,0); no score change | | 2026-10-22 | minor | Several windows on the desktop: a native window per workbook window, New Window on the same workbook, Switch Windows and Arrange All; "one window" removed from UI/UX fidelity, Hardware and the interaction-fidelity discount; no score change | | 2026-10-21 | minor | Page Setup paper sizes: `B4`/`B5`/`B6` are the JIS sizes Japanese Excel uses (ISO B4/B5/B6 by name), and Japanese Postcard, Japanese Double Postcard, the chou/kaku envelopes and A6 are added; PDF MediaBox and `file.print` follow, and the Size menu lists them | | 2026-10-18 | trivial | Stale chart notes: all chart kinds survive XLSX round-trip (PRs #158, #161) and trendlines landed |