collective
Browse / github.com/storytold/gridcraft / patch
CSV: fields starting with = open as formulas, as in Excel
CSV: fields starting with = open as formulas, as in Excel A CSV written by a script with totals as formulas (`Total,=SUM(B2:B9)`) showed the formula as text until the cell was re-entered with F2 and Enter; Excel computes it on open. read_csv now parses a field that starts with `=` as a formula (computed by the recalculation that runs when the workbook opens); a field that doesn't parse (`=SOMMA(B2;B9)`, a lone `=`) stays text, and opening as text (`csv_as_text`) still keeps every field as written. Test: tests::csv_formulas_open_computed. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> <!-- coderabbit-ai-generated:v1 -->
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- rickyb82 (imported from pull request #2728, open)
- Base
45f5ab18e4e57bf50ee88d2526deb4b87c5b24cb- Size
- +57 −10 in 4 files
- Review
- pending
- Shared
- 2026-10-28
- Id
d44243781f2705b39eeb9113
How it went
No other operator has reported using it yet.
Diff
raw · crates/engine/src/cmd/extra/data.rs crates/engine/src/tests.rs crates/xlsx/src/csv.rs docs/file-format-parity.md
@@ -82,11 +82,19 @@ pub(super) fn from_csv(s: &mut Session, p: &Json) -> Result<Json> { (u.start.row..=u.end.row) .map(|r| { (u.start.col..=u.end.col)- .map(|c| match sh.value(CellRef::new(r, c)) {- Value::Number(n) => json!(n),- Value::Bool(b) => json!(b),- Value::Empty => Json::Null,- v => json!(v.display()),+ .map(|c| {+ let at = CellRef::new(r, c);+ // A formula field goes in as typed input, so the new sheet holds the same formula+ // (the parsed workbook is never recalculated: its value is still empty).+ if let Some(f) = sh.cell(at).and_then(|cell| cell.formula.as_ref()) {+ return json!(format!("={}", f.text));+ }+ match sh.value(at) {+ Value::Number(n) => json!(n),+ Value::Bool(b) => json!(b),+ Value::Empty => Json::Null,+ v => json!(v.display()),+ } }) .collect() })@@ -2434,6 +2434,31 @@ fn ods_import_never_reuses_source_as_save_target() { } /// Open a `;` CSV by path (a double-click), edit, Save: the file keeps its `;` separator.+/// A CSV written by a script with totals as formulas opens computed, as in Excel, instead of+/// showing the formula text until the cell is re-entered; what doesn't parse stays text.+#[test]+fn csv_formulas_open_computed() {+ let text = "Item,Amount\r\nRent,800\r\nFood,300\r\nTotal,=SUM(B2:B3)\r\nMean,\"=AVERAGE(B2:B3)\"\r\nLocal,=SOMMA(B2;B3)\r\nSign,=\r\n";+ let mut s = s();+ s.execute("file.open", json!({"name": "budget.csv", "base64": crate::io::base64_encode(text.as_bytes())})).unwrap();+ let v = s.execute("sheet.read", json!({"range": "B4:B7"})).unwrap();+ assert_eq!(v["values"], json!([[1100.0], [550.0], ["=SOMMA(B2;B3)"], ["="]]));+ let f = s.execute("sheet.read", json!({"range": "B4:B5", "formulas": true})).unwrap();+ assert_eq!(f["values"], json!([["=SUM(B2:B3)"], ["=AVERAGE(B2:B3)"]]));+}++/// Data › From Text/CSV reads the fields into a new sheet: a formula field arrives as the same+/// formula, computed there, not as an empty cell.+#[test]+fn csv_import_into_a_sheet_keeps_formulas() {+ let mut s = s();+ s.execute("data.fromTextCsv", json!({"text": "Item,Amount\nRent,800\nFood,300\nTotal,=SUM(B2:B3)\n", "sheet": "Budget"})).unwrap();+ let v = s.execute("sheet.read", json!({"sheet": "Budget", "range": "A4:B4"})).unwrap();+ assert_eq!(v["values"], json!([["Total", 1100.0]]));+ let f = s.execute("sheet.read", json!({"sheet": "Budget", "range": "B4", "formulas": true})).unwrap();+ assert_eq!(f["values"], json!([["=SUM(B2:B3)"]]));+}+ #[test] #[cfg(not(target_arch = "wasm32"))] fn saving_an_opened_semicolon_csv_keeps_semicolons() {@@ -4,7 +4,7 @@ use std::sync::Arc; use gridcraft_core::parse::parse_input; use gridcraft_core::{CellRef, MAX_COLS, MAX_ROWS, Value};-use gridcraft_model::{Cell, NumFmt, Sheet, StyleId, Workbook};+use gridcraft_model::{Cell, Formula, NumFmt, Sheet, StyleId, Workbook}; use crate::{CsvOptions, IoError}; @@ -275,7 +275,9 @@ pub fn read_csv(bytes: &[u8], opts: &CsvOptions) -> Result<Workbook, IoError> { continue; } let pos = CellRef::new(row, col as u32);- let cell = if opts.parse_values {+ let cell = if let Some(cell) = opts.parse_values.then(|| formula_cell(&f)).flatten() {+ cell+ } else if opts.parse_values { let p = parse_input(&f, opts.date_system); let style = match p.format { Some(code) => match fmt_cache.iter().find(|(c, _)| *c == code) {@@ -302,6 +304,17 @@ pub fn read_csv(bytes: &[u8], opts: &CsvOptions) -> Result<Workbook, IoError> { Ok(wb) } +/// Like Excel, a field that starts with `=` is a formula (`=SUM(B2:B9)`), computed when the+/// workbook opens; one that doesn't parse (`=SOMMA(B2;B9)`, a lone `=`) stays text.+fn formula_cell(field: &str) -> Option<Cell> {+ let body = field.strip_prefix('=')?;+ if body.trim().is_empty() {+ return None;+ }+ let expr = gridcraft_formula::parse(body).ok()?;+ Some(Cell::formula(Formula::from_expr(expr)))+}+ /// Writes the sheet as CSV from A1 to the end of its used range, so leading empty columns and rows /// stay as empty fields and every cell keeps its position (CRLF line ends, UTF-8 without BOM). `cell_text` gives /// each cell's field text: the caller decides how values look (Save As CSV writes them as@@ -1,6 +1,6 @@ # File-format parity -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-21 · **Change:** minor (.xls dialog sheets are skipped instead of read as worksheets; `.xls` import reads each sheet's display options from WINDOW2: right-to-left, gridlines, headings, zeros, formulas; XLSX text boxes and shapes keep their paragraphs' right-to-left direction on save; CSV/TSV/TXT can open with every field kept as text via `file.open {csvAsText}`; formulas over Excel's 8,192 characters, and `%` or call chains past the nesting limit, are parse errors instead of stack overflows when a file is opened; XLSX PivotTables read and write `rowGrandTotals`/`colGrandTotals` the way Excel does, so a table saved with only one grand total opens with the right one; HTML export keeps right-to-left; CSV and text import detects likely Windows-1256 Arabic when invalid UTF-8 has Arabic byte runs; other invalid UTF-8 remains Windows-1252; HTML export places pictures and charts, as inline SVG, at their anchors) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4+> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-27 · **Change:** minor (CSV fields starting with `=` open as formulas, as in Excel) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Every format Excel opens or saves, with GridCraft's read and write support, fidelity and tests. Part of [target-app-parity.md](target-app-parity.md). **File formats: ~50% ready, 80–120 h**@@ -66,9 +66,9 @@ values) is still dropped. | Format | Excel | GridCraft read | GridCraft write | Notes | |---|---|---|---|---|-| `.csv` UTF-8 / Windows / Mac / MS-DOS | R/W | yes (UTF-8 and UTF-16 BOMs, UTF-8, likely Windows-1256 Arabic detection, otherwise Windows-1252 fallback; delimiter detection) | yes (UTF-8 without BOM, CRLF; a file opened as CSV is saved with the separator it was read with, so a `;` file stays `;`; otherwise `,`) | Excel writes the **displayed** values; we write raw values. No encoding choice on save. Re-import of a single-column export splits text (#127) A used range that would write more than 512 MiB (two far-apart cells span the whole rectangle) is refused with an error instead of being expanded (#528). |+| `.csv` UTF-8 / Windows / Mac / MS-DOS | R/W | yes (UTF-8 and UTF-16 BOMs, UTF-8, likely Windows-1256 Arabic detection, otherwise Windows-1252 fallback; delimiter detection; a field starting with `=` is a formula, computed on open, or text when it doesn't parse) | yes (UTF-8 without BOM, CRLF; a file opened as CSV is saved with the separator it was read with, so a `;` file stays `;`; otherwise `,`) | Excel writes the **displayed** values; we write raw values. No encoding choice on save. Re-import of a single-column export splits text (#127) A used range that would write more than 512 MiB (two far-apart cells span the whole rectangle) is refused with an error instead of being expanded (#528). | | `.txt` tab-delimited, Unicode text | R/W | yes (`.txt`, `.tsv`, `.tab`; likely Windows-1256 Arabic detection for invalid UTF-8) | yes | Text Import Wizard options partly via Text to Columns |-| `.csv` UTF-8 / Windows / Mac / MS-DOS | R/W | yes (UTF-8 and UTF-16 BOMs, UTF-8, likely Windows-1256 Arabic detection, otherwise Windows-1252 fallback; delimiter detection) | yes (UTF-8 without BOM, CRLF) | Writes the **displayed** values like Excel (number formats applied, formula results, never column-width `#####`). No encoding choice on save. Re-import of a single-column export splits text (#127) A used range that would write more than 512 MiB (two far-apart cells span the whole rectangle) is refused with an error instead of being expanded (#528). |+| `.csv` UTF-8 / Windows / Mac / MS-DOS | R/W | yes (UTF-8 and UTF-16 BOMs, UTF-8, likely Windows-1256 Arabic detection, otherwise Windows-1252 fallback; delimiter detection; a field starting with `=` is a formula, computed on open, or text when it doesn't parse) | yes (UTF-8 without BOM, CRLF) | Writes the **displayed** values like Excel (number formats applied, formula results, never column-width `#####`). No encoding choice on save. Re-import of a single-column export splits text (#127) A used range that would write more than 512 MiB (two far-apart cells span the whole rectangle) is refused with an error instead of being expanded (#528). | | `.txt` tab-delimited, Unicode text | R/W | yes (`.txt`, `.tsv`, `.tab`; likely Windows-1256 Arabic detection for invalid UTF-8) | yes (displayed values) | Text Import Wizard options partly via Text to Columns | | `.prn` formatted text (space-delimited) | R/W | no | no | | | `.dif`, `.slk` (SYLK) | R/W | no | no | Small, legacy |@@ -110,6 +110,7 @@ refresh. Counted under features (Get & Transform, ~10% ready), not here. | Date | Change | Summary | |---|---|---|+| 2026-10-27 | minor | CSV import: a field starting with `=` is a formula computed on open, as in Excel (it was kept as text until the cell was re-entered); one that doesn't parse stays text | | 2026-10-21 | minor | HTML (Save as Web Page) writes pictures (`data:` URI `<img>`) and charts (inline SVG from the chart crate's drawing primitives, text included) at their anchors; the table uses the sheet's column widths and row heights when the sheet has either, and output for sheets without pictures or charts is unchanged | | 2026-10-20 | trivial | XLSX shared strings: Japanese phonetic runs (ふりがな, `<rPh sb="…" eb="…">` with `<phoneticPr>`) are read into the workbook and written back next to the string with the same text, so an open + save no longer deletes them; bad offsets and readings are dropped with a warning, a `<phoneticPr>` without runs is kept on its own, and when duplicate `<si>` items share a text but not a reading only the first reading is written (reported on open) (`phonetic_tests.rs`) | | 2026-10-20 | minor | A workbook theme's major/minor per-script typefaces (`<a:font script="Arab" typeface="…"/>`, also Hebr, Jpan and the rest) are read and written back in file order after `<a:cs>`; they were dropped before. A hostile file is held to 64 entries of 256 characters |