diff --git a/crates/engine/src/cmd/data.rs b/crates/engine/src/cmd/data.rs --- a/crates/engine/src/cmd/data.rs +++ b/crates/engine/src/cmd/data.rs @@ -679,17 +679,17 @@ pub(crate) fn grow_autofilter(sh: &mut Sheet, at: CellRef) { } } -/// Hides rows of the autofilter range that fail any criterion. -pub(crate) fn apply_filter(wb: &mut Workbook, sheet: usize) { - let Some(sh) = wb.sheet(sheet) else { return }; - let Some(af) = sh.autofilter.clone() else { return }; +/// Statistics use the original range, regardless of the other active criteria (as in Excel). +fn filter_stats(sh: &Sheet, af: &AutoFilter, excluded_offset: Option) -> std::collections::HashMap, f64)> { let r = af.range; - let mut hide = Vec::new(); // Precompute per-criterion top10 threshold and average once, not per row. let mut col_stats: std::collections::HashMap, f64)> = std::collections::HashMap::new(); for (i, (off, c)) in af.criteria.iter().enumerate() { + if Some(*off) == excluded_offset { + continue; + } if matches!(c, FilterCriterion::Top10 { .. } | FilterCriterion::AboveAverage(_)) { - let col = r.start.col + off; + let Some(col) = r.start.col.checked_add(*off).filter(|col| *col <= r.end.col) else { continue }; let mut nums: Vec = (r.start.row + 1..=r.end.row).filter_map(|row| sh.value(CellRef::new(row, col)).as_f64()).collect(); let avg = if nums.is_empty() { 0.0 } else { nums.iter().sum::() / nums.len() as f64 }; let thr = match c { @@ -704,59 +704,84 @@ pub(crate) fn apply_filter(wb: &mut Workbook, sheet: usize) { col_stats.insert(i, (thr, avg)); } } - for row in r.start.row + 1..=r.end.row { - let mut visible = true; - for (i, (off, crit)) in af.criteria.iter().enumerate() { - let c = CellRef::new(row, r.start.col + off); - let v = sh.value(c); - let text = crate::display::cell_text(wb, sh, c); - let ok = match crit { - FilterCriterion::Values { values, blanks } => { - if filter_blank(&v) { - *blanks - } else { - values.iter().any(|x| x.eq_ignore_ascii_case(&text) || x.eq_ignore_ascii_case(&v.display())) - } + col_stats +} + +/// Evaluate criteria directly, without conflating manual visibility with filter results. +fn filter_row_matches( + wb: &Workbook, + sh: &Sheet, + af: &AutoFilter, + row: u32, + col_stats: &std::collections::HashMap, f64)>, + excluded_offset: Option, +) -> bool { + for (i, (off, crit)) in af.criteria.iter().enumerate() { + if Some(*off) == excluded_offset { + continue; + } + let Some(col) = af.range.start.col.checked_add(*off).filter(|col| *col <= af.range.end.col) else { continue }; + let c = CellRef::new(row, col); + let v = sh.value(c); + let text = crate::display::cell_text(wb, sh, c); + let ok = match crit { + FilterCriterion::Values { values, blanks } => { + if filter_blank(&v) { + *blanks + } else { + values.iter().any(|x| x.eq_ignore_ascii_case(&text) || x.eq_ignore_ascii_case(&v.display())) } - FilterCriterion::Custom { a, b, and } => { - let x = custom_ok(&v, &text, &a.0, &a.1); - match b { - Some(b) => { - let y = custom_ok(&v, &text, &b.0, &b.1); - if *and { x && y } else { x || y } - } - None => x, + } + FilterCriterion::Custom { a, b, and } => { + let x = custom_ok(&v, &text, &a.0, &a.1); + match b { + Some(b) => { + let y = custom_ok(&v, &text, &b.0, &b.1); + if *and { x && y } else { x || y } } + None => x, } - FilterCriterion::Top10 { bottom, .. } => match (v.as_f64(), col_stats.get(&i)) { - (Some(n), Some((thr, _))) => { - let thr = thr.unwrap_or(n); - if *bottom { n <= thr } else { n >= thr } - } - _ => false, - }, - FilterCriterion::AboveAverage(above) => match (v.as_f64(), col_stats.get(&i)) { - (Some(n), Some((_, avg))) => { - if *above { - n > *avg - } else { - n < *avg - } - } - _ => false, - }, - FilterCriterion::FillColor(col) => wb.styles.get(sh.style_id(c)).fill.fg == *col, - FilterCriterion::FontColor(col) => wb.styles.get(sh.style_id(c)).font.color == *col, - // Icons are not evaluated here: the row keeps its current visibility. - FilterCriterion::Icon { .. } => !sh.is_row_hidden(row), - }; - if !ok { - visible = false; - break; } + FilterCriterion::Top10 { bottom, .. } => match (v.as_f64(), col_stats.get(&i)) { + (Some(n), Some((thr, _))) => { + let thr = thr.unwrap_or(n); + if *bottom { n <= thr } else { n >= thr } + } + _ => false, + }, + FilterCriterion::AboveAverage(above) => match (v.as_f64(), col_stats.get(&i)) { + (Some(n), Some((_, avg))) => { + if *above { + n > *avg + } else { + n < *avg + } + } + _ => false, + }, + FilterCriterion::FillColor(col) => wb.styles.get(sh.style_id(c)).fill.fg == *col, + FilterCriterion::FontColor(col) => wb.styles.get(sh.style_id(c)).font.color == *col, + // Icon criteria are retained from files but cannot be evaluated yet. + FilterCriterion::Icon { .. } => true, + }; + if !ok { + return false; } - hide.push((row, !visible)); } + true +} + +/// Hides rows of the autofilter range that fail any criterion. +pub(crate) fn apply_filter(wb: &mut Workbook, sheet: usize) { + let Some(sh) = wb.sheet(sheet) else { return }; + let Some(af) = sh.autofilter.clone() else { return }; + let r = af.range; + let col_stats = filter_stats(sh, &af, None); + // Reapplying a retained icon filter preserves visibility until icons can be evaluated. + let has_icon = af.criteria.iter().any(|(_, c)| matches!(c, FilterCriterion::Icon { .. })); + let hide: Vec<_> = (r.start.row + 1..=r.end.row) + .map(|row| (row, !filter_row_matches(wb, sh, &af, row, &col_stats, None) || (has_icon && sh.is_row_hidden(row)))) + .collect(); let Some(shm) = wb.sheet_mut(sheet) else { return }; for (row, h) in hide { if h { @@ -789,14 +814,22 @@ fn reapply(s: &mut Session, _: &Json) -> Result { }) } -/// Distinct display values of a filter column (for the dropdown list). +/// Distinct display values from rows matching the other columns' criteria. +/// The current criterion only determines checkboxes, so its unchecked alternatives stay available. pub fn filter_values(wb: &Workbook, sheet: usize, col: u32) -> Vec<(String, bool)> { let Some(sh) = wb.sheet(sheet) else { return vec![] }; let Some(af) = &sh.autofilter else { return vec![] }; - let off = col.saturating_sub(af.range.start.col); + if col < af.range.start.col || col > af.range.end.col { + return vec![]; + } + let off = col - af.range.start.col; let selected: Option<&FilterCriterion> = af.criteria.iter().find(|(o, _)| *o == off).map(|(_, c)| c); + let col_stats = filter_stats(sh, af, Some(off)); let mut seen = std::collections::BTreeMap::new(); - for row in af.range.start.row + 1..=af.range.end.row.min(af.range.start.row + 1_000_000) { + for row in af.range.start.row.saturating_add(1)..=af.range.end.row.min(af.range.start.row.saturating_add(1_000_000)) { + if !filter_row_matches(wb, sh, af, row, &col_stats, Some(off)) { + continue; + } let c = CellRef::new(row, col); let v = sh.value(c); let blank = filter_blank(&v); diff --git a/crates/engine/tests/it/cascading_filter_values.rs b/crates/engine/tests/it/cascading_filter_values.rs new file mode 100644 --- /dev/null +++ b/crates/engine/tests/it/cascading_filter_values.rs @@ -0,0 +1,214 @@ +use std::sync::Arc; + +use gridcraft_engine::{ + Session, + cmd::data::filter_values, + model::{Color, FilterCriterion}, +}; +use serde_json::json; + +#[test] +fn choices_use_the_same_custom_and_statistical_criteria_as_filtering() { + for (criterion, expected) in [ + (json!({"custom": {"op": ">=", "value": 20, "op2": "<", "value2": 100}}), vec!["mid", "upper"]), + (json!({"custom": {"op": "=", "value": 10, "op2": "=", "value2": 100, "and": false}}), vec!["high", "low"]), + (json!({"custom": {"op": "=", "value": "n?*"}}), vec!["text"]), + (json!({"top": {"count": 2}}), vec!["high", "upper"]), + (json!({"top": {"count": 50, "percent": true, "bottom": true}}), vec!["low", "mid"]), + (json!({"aboveAverage": true}), vec!["high"]), + (json!({"aboveAverage": false}), vec!["low", "mid", "upper"]), + ] { + let mut s = Session::new(); + s.new_workbook(); + s.execute( + "range.setValues", + json!({"range": "A1", "values": [ + ["Name", "Amount"], ["low", 10], ["mid", 20], ["upper", 30], ["high", 100], ["blank", null], ["text", "n/a"] + ]}), + ) + .unwrap(); + s.execute("data.filter", json!({"range": "A1:B7"})).unwrap(); + let mut params = criterion.clone(); + params["column"] = json!("B"); + s.execute("data.filterBy", params).unwrap(); + let actual: Vec<_> = filter_values(&s.doc().unwrap().wb, 0, 0).into_iter().map(|(v, _)| v).collect(); + assert_eq!(actual, expected, "{criterion}"); + // The current column's own criterion must not narrow its alternatives. + s.execute("data.filterBy", json!({"column": "A", "values": ["mid"]})).unwrap(); + let actual: Vec<_> = filter_values(&s.doc().unwrap().wb, 0, 0).into_iter().map(|(v, _)| v).collect(); + assert_eq!(actual, expected, "{criterion}, with the current column filtered"); + assert_eq!( + filter_values(&s.doc().unwrap().wb, 0, 1), + vec![("20".into(), true)], + "the numeric column's own criterion must be excluded: {criterion}" + ); + } +} + +#[test] +fn statistical_thresholds_use_the_original_range_not_other_columns_subset() { + for (criterion, names, expected) in [ + (json!({"top": {"count": 2}}), vec!["low", "mid", "upper"], vec!["upper"]), + (json!({"top": {"count": 50, "percent": true}}), vec!["low", "mid", "upper"], vec!["upper"]), + (json!({"top": {"count": 2, "bottom": true}}), vec!["mid", "upper", "high"], vec!["mid"]), + (json!({"top": {"count": 50, "percent": true, "bottom": true}}), vec!["mid", "upper", "high"], vec!["mid"]), + (json!({"aboveAverage": true}), vec!["low", "mid", "upper"], vec![]), + (json!({"aboveAverage": false}), vec!["low", "mid", "upper"], vec!["low", "mid", "upper"]), + ] { + let mut s = Session::new(); + s.new_workbook(); + s.execute( + "range.setValues", + json!({"range": "A1", "values": [ + ["Name", "Amount", "Job"], ["low", 10, "low"], ["mid", 20, "mid"], ["upper", 30, "upper"], ["high", 100, "high"] + ]}), + ) + .unwrap(); + s.execute("data.filter", json!({"range": "A1:C5"})).unwrap(); + s.execute("data.filterBy", json!({"column": "A", "values": names})).unwrap(); + let mut params = criterion.clone(); + params["column"] = json!("B"); + s.execute("data.filterBy", params).unwrap(); + // A and B both remain active when enumerating C. Subset statistics produce different jobs. + let actual: Vec<_> = filter_values(&s.doc().unwrap().wb, 0, 2).into_iter().map(|(v, _)| v).collect(); + assert_eq!(actual, expected, "{criterion}"); + } +} + +#[test] +fn choices_match_other_columns_but_keep_the_current_columns_unchecked_values() { + let mut s = Session::new(); + s.new_workbook(); + s.execute( + "range.setValues", + json!({"range": "B3", "values": [ + ["Company", "Job", "Status"], + ["East", "Alpha", "Open"], + ["East", "Beta", "Open"], + ["East", "Closed job", "Closed"], + ["West", "Other company", "Open"], + ["East", null, "Open"], + ["East", "=\"\"", "Open"], + ["East", "Outside range", "Open"] + ]}), + ) + .unwrap(); + s.execute("data.filter", json!({"range": "B3:D9"})).unwrap(); + s.execute("data.filterBy", json!({"column": "B", "values": ["East"]})).unwrap(); + s.execute("data.filterBy", json!({"column": "D", "values": ["Open"]})).unwrap(); + s.execute("data.filterBy", json!({"column": "C", "values": ["Alpha"]})).unwrap(); + + let choices = |s: &Session| filter_values(&s.doc().unwrap().wb, 0, 2); + let expected = + |beta: bool, blanks: bool| -> Vec<(String, bool)> { vec![("Alpha".into(), !beta), ("Beta".into(), beta), ("(Blanks)".into(), blanks)] }; + assert_eq!(choices(&s), expected(false, false)); + s.execute("data.filterBy", json!({"column": "C", "values": ["Beta"], "blanks": true})).unwrap(); + assert_eq!(choices(&s), expected(true, true)); + s.execute("data.filterBy", json!({"column": "C", "clear": true})).unwrap(); + assert_eq!(choices(&s), vec![("Alpha".into(), true), ("Beta".into(), true), ("(Blanks)".into(), true)]); + // Manual visibility is not a filter criterion, even when every eligible row is hidden. + s.execute("home.hideRows", json!({"rows": "4:10"})).unwrap(); + assert_eq!(choices(&s).len(), 3); + assert!(filter_values(&s.doc().unwrap().wb, 0, 0).is_empty()); + assert!(filter_values(&s.doc().unwrap().wb, 0, 4).is_empty()); +} + +#[test] +fn choices_respect_blank_and_formatted_value_criteria_on_other_columns() { + let mut s = Session::new(); + s.new_workbook(); + s.execute( + "range.setValues", + json!({"range": "A1", "values": [ + ["Name", "Value"], ["empty", null], ["formula", "=\"\""], ["spaces", " "], ["number", 12], ["other", 30] + ]}), + ) + .unwrap(); + s.execute("home.numberFormat", json!({"range": "B5:B6", "code": "0.00"})).unwrap(); + s.execute("data.filter", json!({"range": "A1:B6"})).unwrap(); + s.execute("data.filterBy", json!({"column": "B", "values": [], "blanks": true})).unwrap(); + assert_eq!(filter_values(&s.doc().unwrap().wb, 0, 0), vec![("empty".into(), true), ("formula".into(), true)]); + for value in ["12", "12.00"] { + s.execute("data.filterBy", json!({"column": "B", "values": [value]})).unwrap(); + assert_eq!(filter_values(&s.doc().unwrap().wb, 0, 0), vec![("number".into(), true)]); + } + s.execute("data.filterBy", json!({"column": "B", "values": ["absent"]})).unwrap(); + assert!(filter_values(&s.doc().unwrap().wb, 0, 0).is_empty()); +} + +#[test] +fn choices_match_color_criteria_and_do_not_infer_icon_matches_from_hidden_rows() { + let mut s = Session::new(); + s.new_workbook(); + s.execute("range.setValues", json!({"range": "A1", "values": [["Name", "Color"], ["red", 1], ["plain", 2]]})).unwrap(); + s.execute("home.fillColor", json!({"range": "B2", "color": "#FF0000"})).unwrap(); + s.execute("home.fontColor", json!({"range": "B2", "color": "#FF0000"})).unwrap(); + s.execute("data.filter", json!({"range": "A1:B3"})).unwrap(); + for criterion in [FilterCriterion::FillColor(Color::Rgb(0xFF0000)), FilterCriterion::FontColor(Color::Rgb(0xFF0000))] { + let sh = Arc::make_mut(&mut s.doc_mut().unwrap().wb).sheet_mut(0).unwrap(); + sh.autofilter.as_mut().unwrap().criteria = vec![(1, criterion)]; + s.execute("data.reapply", json!({})).unwrap(); + assert_eq!(filter_values(&s.doc().unwrap().wb, 0, 0), vec![("red".into(), true)]); + } + let sh = Arc::make_mut(&mut s.doc_mut().unwrap().wb).sheet_mut(0).unwrap(); + sh.autofilter.as_mut().unwrap().criteria = vec![(1, FilterCriterion::Icon { set: "3Arrows".into(), id: Some(0) })]; + s.execute("data.reapply", json!({})).unwrap(); + assert!(s.doc().unwrap().wb.active().unwrap().is_row_hidden(2), "reapply preserves imported icon visibility"); + assert_eq!( + filter_values(&s.doc().unwrap().wb, 0, 0), + vec![("plain".into(), true), ("red".into(), true)], + "unsupported icon criteria cannot distinguish manual and current-column hiding" + ); +} + +#[test] +fn querying_a_table_backed_sheet_filter_is_read_only_and_preserves_xlsx_criteria() { + for table in [false, true] { + let mut s = Session::new(); + s.new_workbook(); + s.execute("range.setValues", json!({"range": "B3", "values": [["Company", "Job"], ["East", "Alpha"], ["East", "Beta"], ["West", "Other"]]})) + .unwrap(); + if table { + s.execute("insert.table", json!({"range": "B3:C6", "header": true})).unwrap(); + } + s.execute("selection.set", json!({"cell": "B3"})).unwrap(); + s.execute("data.filterBy", json!({"column": "B", "values": ["East"]})).unwrap(); + let before = gridcraft_xlsx::write_xlsx(&s.doc().unwrap().wb).unwrap(); + assert_eq!(filter_values(&s.doc().unwrap().wb, 0, 2), vec![("Alpha".into(), true), ("Beta".into(), true)]); + assert_eq!(before, gridcraft_xlsx::write_xlsx(&s.doc().unwrap().wb).unwrap()); + let (reopened, _) = gridcraft_xlsx::read_xlsx(&before).unwrap(); + let sh = reopened.active().unwrap(); + let criteria = if table { &sh.tables[0].filter } else { &sh.autofilter.as_ref().unwrap().criteria }; + assert_eq!(criteria, &vec![(0, FilterCriterion::Values { values: vec!["East".into()], blanks: false })]); + assert!(!sh.is_row_hidden(3) && !sh.is_row_hidden(4) && sh.is_row_hidden(5)); + } +} + +#[test] +fn an_unrelated_tables_criteria_do_not_restrict_the_sheet_filter_choices() { + let mut s = Session::new(); + s.new_workbook(); + s.execute("range.setValues", json!({"range": "B3", "values": [["Company", "Job"], ["East", "Alpha"], ["East", "Beta"], ["West", "Other"]]})) + .unwrap(); + s.execute("range.setValues", json!({"range": "F3", "values": [["Company", "Job"], ["West", "Unrelated"], ["East", "Separate"]]})).unwrap(); + s.execute("insert.table", json!({"range": "F3:G5", "header": true})).unwrap(); + let sh = Arc::make_mut(&mut s.doc_mut().unwrap().wb).sheet_mut(0).unwrap(); + sh.tables[0].filter = vec![(0, FilterCriterion::Values { values: vec!["West".into()], blanks: false })]; + s.execute("selection.set", json!({"cell": "B3"})).unwrap(); + s.execute("data.filter", json!({"range": "B3:C6"})).unwrap(); + s.execute("data.filterBy", json!({"column": "B", "values": ["East"]})).unwrap(); + assert_eq!(filter_values(&s.doc().unwrap().wb, 0, 2), vec![("Alpha".into(), true), ("Beta".into(), true)]); +} + +#[test] +fn choices_are_bounded_at_the_last_sheet_row_and_empty_for_missing_data() { + let mut s = Session::new(); + s.new_workbook(); + assert!(filter_values(&s.doc().unwrap().wb, 0, 0).is_empty()); + assert!(filter_values(&s.doc().unwrap().wb, 99, 0).is_empty()); + s.execute("range.setValues", json!({"range": "B1048574", "values": [["Company", "Job"], ["West", "Before last"], ["East", "Last East"]]})) + .unwrap(); + s.execute("data.filter", json!({"range": "B1048574:C1048576"})).unwrap(); + s.execute("data.filterBy", json!({"column": "B", "values": ["East"]})).unwrap(); + assert_eq!(filter_values(&s.doc().unwrap().wb, 0, 2), vec![("Last East".into(), true)]); +} diff --git a/crates/engine/tests/it/main.rs b/crates/engine/tests/it/main.rs --- a/crates/engine/tests/it/main.rs +++ b/crates/engine/tests/it/main.rs @@ -1,6 +1,7 @@ //! The crate's integration tests, one module per file, built as one test binary. mod autofit_filter_buttons; +mod cascading_filter_values; mod cf_formula_validate; mod chart_area_stacked100; mod chart_workbook_names; diff --git a/crates/ui-egui/src/grid.rs b/crates/ui-egui/src/grid.rs --- a/crates/ui-egui/src/grid.rs +++ b/crates/ui-egui/src/grid.rs @@ -3326,7 +3326,6 @@ fn filter_menu(app: &mut SheetApp, ui: &mut egui::Ui, geo: &Geo, was_open: bool) } } let wb = app.session.active().map(|d| d.wb.clone()).unwrap_or(wb); - let values = gridcraft_engine::cmd::data::filter_values(&wb, si, col); let key = egui::Id::unique(("filter_sel", col)); let search_key = egui::Id::unique(("filter_search", col)); let buttons_key = egui::Id::unique(("filter_buttons", col)); @@ -3352,7 +3351,10 @@ fn filter_menu(app: &mut SheetApp, ui: &mut egui::Ui, geo: &Geo, was_open: bool) } ui.separator(); ui.label(egui::RichText::new("Filter").strong()); - let mut checks: Vec<(String, bool)> = ui.ctx().data_mut(|d| d.get_temp::>(key)).unwrap_or(values.clone()); + let mut checks: Vec<(String, bool)> = ui + .ctx() + .data_mut(|d| d.get_temp::>(key)) + .unwrap_or_else(|| gridcraft_engine::cmd::data::filter_values(&wb, si, col)); // A search narrows the list; Select All and Apply then only cover the matches. let mut search: String = ui.ctx().data_mut(|d| d.get_temp(search_key)).unwrap_or_default(); let field = ui.add(egui::TextEdit::singleline(&mut search).hint_text("Search").desired_width(f32::INFINITY)); diff --git a/crates/ui-egui/tests/it/cascading_filter_values.rs b/crates/ui-egui/tests/it/cascading_filter_values.rs new file mode 100644 --- /dev/null +++ b/crates/ui-egui/tests/it/cascading_filter_values.rs @@ -0,0 +1,68 @@ +use crate::common; + +use egui::{Event, Key, Modifiers}; +use egui_kittest::kittest::Queryable; +use gridcraft_engine::Session; +use serde_json::json; + +fn key(h: &mut common::Harness, key: Key, modifiers: Modifiers) { + for pressed in [true, false] { + h.input_mut().events.push(Event::Key { key, physical_key: Some(key), pressed, repeat: false, modifiers }); + } + h.run_steps(3); +} + +#[test] +fn dropdown_can_change_and_clear_a_column_while_another_filter_stays_active() { + for table in [false, true] { + let mut session = Session::new(); + session.new_workbook(); + session + .execute( + "range.setValues", + json!({"range": "A1", "values": [ + ["Company", "Job"], ["East", "Alpha"], ["East", "Beta"], ["West", "Other"], ["East", null] + ]}), + ) + .unwrap(); + if table { + session.execute("insert.table", json!({"range": "A1:B5", "header": true})).unwrap(); + } + session.execute("data.filterBy", json!({"column": "A", "values": ["East"]})).unwrap(); + session.execute("data.filterBy", json!({"column": "B", "values": ["Alpha"]})).unwrap(); + session.execute("selection.set", json!({"cell": "B1"})).unwrap(); + let screenshot = std::env::var("GRIDCRAFT_CASCADING_SNAPSHOT").ok().filter(|_| !table); + let mut h = if screenshot.is_some() { + common::harness_with( + common::builder().with_os(egui::os::OperatingSystem::Windows).with_size(egui::vec2(1200.0, 800.0)).wgpu(), + common::app(session), + 4, + ) + } else { + common::os_harness(session, egui::os::OperatingSystem::Windows) + }; + key(&mut h, Key::ArrowDown, Modifiers::ALT); + assert!(h.query_by_label("Alpha").is_some()); + assert!(h.query_by_label("Beta").is_some(), "current-column filtering must not remove alternatives"); + assert!(h.query_by_label("(Blanks)").is_some()); + assert!(h.query_by_label("Other").is_none(), "the other company's job must not be listed"); + if let Some(path) = screenshot { + h.render().unwrap().save(path).unwrap(); + } + h.get_by_label("Alpha").click(); + h.run_steps(2); + h.get_by_label("Beta").click(); + h.run_steps(2); + h.get_by_label("Apply").click(); + h.run_steps(3); + let hidden = |h: &common::Harness| { + let sh = h.state().session.active().unwrap().wb.active().unwrap(); + (1..=4).map(|row| sh.is_row_hidden(row)).collect::>() + }; + assert_eq!(hidden(&h), [true, false, true, true]); + key(&mut h, Key::ArrowDown, Modifiers::ALT); + h.get_by_label("Clear Filter").click(); + h.run_steps(3); + assert_eq!(hidden(&h), [false, false, true, false], "clearing Job keeps Company filtered"); + } +} diff --git a/crates/ui-egui/tests/it/main.rs b/crates/ui-egui/tests/it/main.rs --- a/crates/ui-egui/tests/it/main.rs +++ b/crates/ui-egui/tests/it/main.rs @@ -11,6 +11,7 @@ mod advanced_filter; mod argument_names; mod autosave_persist; mod borders; +mod cascading_filter_values; mod cell_editor_caret; mod cell_pictures; mod cf_rule_pick; diff --git a/docs/ui-parity.md b/docs/ui-parity.md --- a/docs/ui-parity.md +++ b/docs/ui-parity.md @@ -1,6 +1,6 @@ # UI parity -> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 +> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-26 · **Change:** minor (cascading AutoFilter choices) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Interaction, controls and feel, against Excel. Part of [target-app-parity.md](target-app-parity.md). **UI/UX fidelity: ~55% ready, 50–80 h** (estimated). Ribbon command coverage is measured @@ -60,6 +60,7 @@ this logic, with one undo step. | Point mode (click/drag/arrow references), reference colouring, F4 | done | | AutoComplete in columns, function autocomplete and argument tips | done; cell helpers clip to the worksheet and hide when the editor scrolls out (#200) | | Header resize and double-click AutoFit | done (#42, #88 for header dividers) | +| AutoFilter value dropdowns | Choices come from rows satisfying the other columns' active criteria; the current column only determines checkbox state, including (Blanks), so its filter can be changed or cleared (#2641). Custom comparisons, Top 10, average and color criteria use the same evaluator as row filtering. Top 10 / average thresholds still use the original range. Manual hidden rows do not restrict choices; imported icon criteria remain unevaluated. | | Data › Advanced Filter | dialog offers in-place or copy-to filtering, list and optional criteria ranges, and unique records; Alt+A,Q opens it (#1622). The engine's copy-to path clears only the result footprint and rejects worksheet overflow (#1031). | | Outline symbols for grouped rows / columns (band beside the headers, brackets, − / +, level buttons 1…n+1) | done (#373): clicks run `data.hideDetail` / `data.showDetail` / `data.showLevels`, frozen panes and right-to-left sheets included; Data › Outline › Settings (`data.outlineSettings`) puts summary rows above and summary columns left of their detail, saved as `outlinePr` (the dialog's Automatic styles check box and Apply Styles button are missing), View › Show Outline Symbols (Ctrl+8) hides the bands and is saved with the sheet, and a block of cells still groups rows without the Rows / Columns dialog | | Reorder selected whole columns by dragging their headers | done (10-11) | @@ -91,6 +92,22 @@ this logic, with one undo step. | Sequential Alt access keys (Alt, H, N, …) on every ribbon tab | yes | 364-sequence keytip table for all tabs: per-control badges, prefix waiting, dropdowns open for editing (type/arrows/Enter), pending bindings for not-yet-built commands; letters also work with left Alt held (Alt+H, O, A), as in Excel; each Borders letter (H, B, …) draws its own preset, H, B, M opens Format Cells on the Border tab, and Erase Border, Line Color and Line Style are reserved; QAT/Backstage excluded | | Requests | — | #110 (more shortcuts and hot keys), #33 (sequential Alt; initial support landed in #43) | +### AutoFilter population and pending integrations + +[Microsoft's filtering guide](https://support.microsoft.com/en-us/excel/get-started/filter-data-in-a-range-or-table-in-excel) +describes additive column filters and restricting other dropdowns to the filtered range. The +current column is excluded from enumeration so its unchecked alternatives remain available. +The existing one-million-data-row enumeration limit and value sorting remain unchanged. + +This changes the population behind the pending row-count work (#1737, and its stacked Only +button #1741): a future count beside a choice should count eligible rows satisfying the other +columns, including rows rejected by the current column. It must not count the whole original +range or just currently visible rows. Search narrows the displayed choices, not that population. +These PRs are not incorporated here. Date grouping (#1509) should group this same eligible +population. Table filter activation / roundtrip ownership changes remain in #2506; enumeration +reads the active sheet AutoFilter used by the current table dropdown without changing criteria, +row visibility or the XLSX representation. + ### Shortcut audit (#110) Checked against Microsoft's published "Keyboard shortcuts in Excel" (Windows and Mac tables, @@ -187,6 +204,7 @@ scrollbar bugs 2–4 · pixel/feel pass against observed Excel 7–12. | Date | Change | Summary | |---|---|---| +| 2026-10-26 | minor | AutoFilter choices cascade across other columns while retaining current-column alternatives and blanks (#2641); document pending counts, date grouping and table roundtrip integration | | 2026-10-25 | minor | AutoSum (Alt+=, Home › AutoSum) over a selected row puts its total to the right of the row, or in its last cell when that is empty, as in Excel; it wrote a one-cell total under each cell. A selection's empty last column takes each row's total and its empty last row each column's, with the block's total in the corner when both are empty | | 2026-10-25 | minor | Paste Name lays out only the names in view, so a workbook with thousands of names opens it at once; the arrow keys still scroll the chosen name into view. Go To matches a `Sheet2!Name` reference and hides a workbook name behind the active sheet's own name in any case and script (`Größe` = `GRÖSSE`), as formulas do. The two Page Layout view rows are one; the 0.7 in side margin used when Align with Page Margins is off is marked unverified | | 2026-10-24 | minor | Go To lists the named ranges on every sheet, as Excel's does ("The Go to popup window shows named ranges on every worksheet in your workbook"): a name scoped to another sheet appears as `Sheet2!Name`, and hidden names, constants and formulas are left out. It keeps the references entered (newest first, ten at most), and a double-click on an entry goes there. The Reference box and `edit.goTo` take several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30,H21:H29`) and select them all; `edit.goTo {list: true}` returns the list |