formualizer_workbook/backends/
json.rs

1use crate::IoError;
2use crate::traits::{
3    AccessGranularity, BackendCaps, CellData, MergedRange, NamedRange, SaveDestination, SheetData,
4    SpreadsheetReader, SpreadsheetWriter, TableDefinition,
5};
6use serde::{Deserialize, Serialize};
7use std::collections::BTreeMap;
8use std::fs::File;
9use std::io::{BufReader, Read, Write};
10use std::path::{Path, PathBuf};
11
12#[derive(Serialize, Deserialize, Debug, Default, Clone)]
13struct JsonWorkbook {
14    #[serde(default = "default_version")]
15    version: u32,
16    #[serde(default)]
17    compression: Option<CompressionType>,
18    #[serde(default)]
19    sheets: BTreeMap<String, JsonSheet>,
20}
21
22fn default_version() -> u32 {
23    1
24}
25
26#[derive(Serialize, Deserialize, Debug, Clone, Copy)]
27#[serde(rename_all = "lowercase")]
28#[derive(Default)]
29enum CompressionType {
30    #[default]
31    None,
32    Lz4,
33}
34
35#[derive(Serialize, Deserialize, Debug, Default, Clone)]
36struct JsonSheet {
37    #[serde(default)]
38    cells: Vec<JsonCell>,
39    #[serde(default)]
40    dimensions: Option<(u32, u32)>,
41    #[serde(default)]
42    hidden: bool,
43    #[serde(default)]
44    date_system_1904: bool,
45    #[serde(default)]
46    merged_cells: Vec<MergedRange>,
47    #[serde(default)]
48    tables: Vec<TableDefinition>,
49    #[serde(default)]
50    named_ranges: Vec<NamedRange>,
51}
52
53#[derive(Serialize, Deserialize, Debug, Clone)]
54struct JsonCell {
55    row: u32,
56    col: u32,
57    #[serde(default)]
58    value: Option<JsonValue>,
59    #[serde(default)]
60    formula: Option<String>,
61    #[serde(default)]
62    style: Option<u32>,
63}
64
65#[derive(Serialize, Deserialize, Debug, Clone)]
66#[serde(tag = "type", content = "value")]
67enum JsonValue {
68    Int(i64),
69    Number(f64),
70    Text(String),
71    Boolean(bool),
72    Empty,
73    Date(String),
74    DateTime(String),
75    Time(String),
76    Duration(i64),
77    Array(Vec<Vec<JsonValue>>),
78    Error(String),
79    Pending,
80}
81
82pub struct JsonAdapter {
83    data: JsonWorkbook,
84    path: Option<PathBuf>,
85    caps: BackendCaps,
86}
87
88impl Default for JsonAdapter {
89    fn default() -> Self {
90        Self::new()
91    }
92}
93
94impl JsonAdapter {
95    pub fn new() -> Self {
96        Self {
97            data: JsonWorkbook::default(),
98            path: None,
99            caps: BackendCaps {
100                read: true,
101                write: true,
102                streaming: false,
103                tables: true,
104                named_ranges: true,
105                formulas: true,
106                styles: true,
107                lazy_loading: false,
108                random_access: true,
109                bytes_input: true,
110                date_system_1904: false,
111                merged_cells: true,
112                rich_text: false,
113                hyperlinks: false,
114                data_validations: false,
115                shared_formulas: false,
116            },
117        }
118    }
119
120    fn to_sheet_data(js: &JsonSheet) -> SheetData {
121        let mut cells: BTreeMap<(u32, u32), CellData> = BTreeMap::new();
122        for c in &js.cells {
123            let lit = c.value.as_ref().map(json_to_literal);
124            cells.insert(
125                (c.row, c.col),
126                CellData {
127                    value: lit,
128                    formula: c.formula.clone(),
129                    style: c.style,
130                },
131            );
132        }
133        SheetData {
134            cells,
135            dimensions: js.dimensions,
136            tables: js.tables.clone(),
137            named_ranges: js.named_ranges.clone(),
138            date_system_1904: js.date_system_1904,
139            merged_cells: js.merged_cells.clone(),
140            hidden: js.hidden,
141        }
142    }
143
144    pub fn to_json_string(&self) -> Result<String, IoError> {
145        Ok(serde_json::to_string_pretty(&self.data)?)
146    }
147
148    // Backend-specific helpers (not part of SpreadsheetWriter)
149    fn ensure_sheet_mut(&mut self, name: &str) -> &mut JsonSheet {
150        self.data.sheets.entry(name.to_string()).or_default()
151    }
152
153    pub fn set_dimensions(&mut self, sheet: &str, dims: Option<(u32, u32)>) {
154        self.ensure_sheet_mut(sheet).dimensions = dims;
155    }
156
157    pub fn set_date_system_1904(&mut self, sheet: &str, value: bool) {
158        self.ensure_sheet_mut(sheet).date_system_1904 = value;
159    }
160
161    pub fn set_merged_cells(&mut self, sheet: &str, merged: Vec<MergedRange>) {
162        self.ensure_sheet_mut(sheet).merged_cells = merged;
163    }
164
165    pub fn set_tables(&mut self, sheet: &str, tables: Vec<TableDefinition>) {
166        self.ensure_sheet_mut(sheet).tables = tables;
167    }
168
169    pub fn set_named_ranges(&mut self, sheet: &str, named: Vec<NamedRange>) {
170        self.ensure_sheet_mut(sheet).named_ranges = named;
171    }
172}
173
174impl SpreadsheetReader for JsonAdapter {
175    type Error = IoError;
176
177    fn access_granularity(&self) -> AccessGranularity {
178        AccessGranularity::Workbook
179    }
180
181    fn capabilities(&self) -> BackendCaps {
182        self.caps.clone()
183    }
184
185    fn sheet_names(&self) -> Result<Vec<String>, Self::Error> {
186        Ok(self.data.sheets.keys().cloned().collect())
187    }
188
189    fn open_path<P: AsRef<Path>>(path: P) -> Result<Self, Self::Error>
190    where
191        Self: Sized,
192    {
193        let file = File::open(path.as_ref())?;
194        let reader = BufReader::new(file);
195        let data: JsonWorkbook = serde_json::from_reader(reader)?;
196        Ok(JsonAdapter {
197            data,
198            path: Some(path.as_ref().to_path_buf()),
199            ..JsonAdapter::new()
200        })
201    }
202
203    fn open_reader(reader: Box<dyn Read + Send + Sync>) -> Result<Self, Self::Error>
204    where
205        Self: Sized,
206    {
207        let data: JsonWorkbook = serde_json::from_reader(reader)?;
208        Ok(JsonAdapter {
209            data,
210            ..JsonAdapter::new()
211        })
212    }
213
214    fn open_bytes(bytes: Vec<u8>) -> Result<Self, Self::Error>
215    where
216        Self: Sized,
217    {
218        let data: JsonWorkbook = serde_json::from_slice(&bytes)?;
219        Ok(JsonAdapter {
220            data,
221            ..JsonAdapter::new()
222        })
223    }
224
225    fn read_range(
226        &mut self,
227        sheet: &str,
228        start: (u32, u32),
229        end: (u32, u32),
230    ) -> Result<BTreeMap<(u32, u32), CellData>, Self::Error> {
231        if let Some(js) = self.data.sheets.get(sheet) {
232            let mut out = BTreeMap::new();
233            for c in &js.cells {
234                if c.row >= start.0 && c.row <= end.0 && c.col >= start.1 && c.col <= end.1 {
235                    let lit = c.value.as_ref().map(json_to_literal);
236                    out.insert(
237                        (c.row, c.col),
238                        CellData {
239                            value: lit,
240                            formula: c.formula.clone(),
241                            style: c.style,
242                        },
243                    );
244                }
245            }
246            Ok(out)
247        } else {
248            Ok(BTreeMap::new())
249        }
250    }
251
252    fn read_sheet(&mut self, sheet: &str) -> Result<SheetData, Self::Error> {
253        if let Some(js) = self.data.sheets.get(sheet) {
254            Ok(Self::to_sheet_data(js))
255        } else {
256            Ok(SheetData {
257                cells: BTreeMap::new(),
258                dimensions: None,
259                tables: vec![],
260                named_ranges: vec![],
261                date_system_1904: false,
262                merged_cells: vec![],
263                hidden: false,
264            })
265        }
266    }
267
268    fn sheet_bounds(&self, sheet: &str) -> Option<(u32, u32)> {
269        self.data.sheets.get(sheet).and_then(|s| s.dimensions)
270    }
271
272    fn is_loaded(&self, _sheet: &str, _row: Option<u32>, _col: Option<u32>) -> bool {
273        true
274    }
275}
276
277impl SpreadsheetWriter for JsonAdapter {
278    type Error = IoError;
279
280    fn write_cell(
281        &mut self,
282        sheet: &str,
283        row: u32,
284        col: u32,
285        data: CellData,
286    ) -> Result<(), Self::Error> {
287        let sheet_entry = self.data.sheets.entry(sheet.to_string()).or_default();
288        if let Some(cell) = sheet_entry
289            .cells
290            .iter_mut()
291            .find(|c| c.row == row && c.col == col)
292        {
293            cell.value = data.value.as_ref().map(literal_to_json);
294            cell.formula = data.formula;
295            cell.style = data.style;
296        } else {
297            sheet_entry.cells.push(JsonCell {
298                row,
299                col,
300                value: data.value.as_ref().map(literal_to_json),
301                formula: data.formula,
302                style: data.style,
303            });
304        }
305        Ok(())
306    }
307
308    fn write_range(
309        &mut self,
310        sheet: &str,
311        cells: BTreeMap<(u32, u32), CellData>,
312    ) -> Result<(), Self::Error> {
313        for ((r, c), d) in cells {
314            self.write_cell(sheet, r, c, d)?;
315        }
316        Ok(())
317    }
318
319    fn clear_range(
320        &mut self,
321        sheet: &str,
322        start: (u32, u32),
323        end: (u32, u32),
324    ) -> Result<(), Self::Error> {
325        if let Some(js) = self.data.sheets.get_mut(sheet) {
326            js.cells.retain(|c| {
327                !(c.row >= start.0 && c.row <= end.0 && c.col >= start.1 && c.col <= end.1)
328            });
329        }
330        Ok(())
331    }
332
333    fn create_sheet(&mut self, name: &str) -> Result<(), Self::Error> {
334        self.data.sheets.entry(name.to_string()).or_default();
335        Ok(())
336    }
337
338    fn delete_sheet(&mut self, name: &str) -> Result<(), Self::Error> {
339        self.data.sheets.remove(name);
340        Ok(())
341    }
342
343    fn rename_sheet(&mut self, old: &str, new: &str) -> Result<(), Self::Error> {
344        if let Some(sheet) = self.data.sheets.remove(old) {
345            self.data.sheets.insert(new.to_string(), sheet);
346        }
347        Ok(())
348    }
349
350    fn flush(&mut self) -> Result<(), Self::Error> {
351        Ok(())
352    }
353
354    fn save(&mut self) -> Result<(), Self::Error> {
355        if let Some(path) = &self.path {
356            let mut file = File::create(path)?;
357            let s = serde_json::to_string_pretty(&self.data)?;
358            file.write_all(s.as_bytes())?;
359            Ok(())
360        } else {
361            Ok(())
362        }
363    }
364
365    fn save_to<'a>(&mut self, dest: SaveDestination<'a>) -> Result<Option<Vec<u8>>, Self::Error> {
366        match dest {
367            SaveDestination::InPlace => self.save().map(|_| None),
368            SaveDestination::Path(path) => {
369                let mut file = File::create(path)?;
370                let s = serde_json::to_string_pretty(&self.data)?;
371                file.write_all(s.as_bytes())?;
372                self.path = Some(path.to_path_buf());
373                Ok(None)
374            }
375            SaveDestination::Writer(writer) => {
376                let s = serde_json::to_string_pretty(&self.data)?;
377                writer.write_all(s.as_bytes())?;
378                Ok(None)
379            }
380            SaveDestination::Bytes => {
381                let s = serde_json::to_vec_pretty(&self.data)?;
382                Ok(Some(s))
383            }
384        }
385    }
386}
387
388fn literal_to_json(v: &formualizer_common::LiteralValue) -> JsonValue {
389    use formualizer_common::LiteralValue as L;
390    match v {
391        L::Int(i) => JsonValue::Int(*i),
392        L::Number(n) => JsonValue::Number(*n),
393        L::Text(s) => JsonValue::Text(s.clone()),
394        L::Boolean(b) => JsonValue::Boolean(*b),
395        L::Empty => JsonValue::Empty,
396        L::Array(arr) => JsonValue::Array(
397            arr.iter()
398                .map(|row| row.iter().map(literal_to_json).collect())
399                .collect(),
400        ),
401        L::Date(d) => JsonValue::Date(d.to_string()),
402        L::DateTime(dt) => JsonValue::DateTime(dt.to_string()),
403        L::Time(t) => JsonValue::Time(t.to_string()),
404        L::Duration(dur) => JsonValue::Duration(dur.num_seconds()),
405        L::Error(e) => JsonValue::Error(e.kind.to_string()),
406        L::Pending => JsonValue::Pending,
407    }
408}
409
410fn json_to_literal(v: &JsonValue) -> formualizer_common::LiteralValue {
411    use formualizer_common::LiteralValue as L;
412    match v {
413        JsonValue::Int(i) => L::Int(*i),
414        JsonValue::Number(n) => L::Number(*n),
415        JsonValue::Text(s) => L::Text(s.clone()),
416        JsonValue::Boolean(b) => L::Boolean(*b),
417        JsonValue::Empty => L::Empty,
418        JsonValue::Array(arr) => L::Array(
419            arr.iter()
420                .map(|row| row.iter().map(json_to_literal).collect())
421                .collect(),
422        ),
423        JsonValue::Date(s) => {
424            let d = chrono::NaiveDate::parse_from_str(s, "%Y-%m-%d")
425                .unwrap_or_else(|_| chrono::NaiveDate::from_ymd_opt(1970, 1, 1).unwrap());
426            L::Date(d)
427        }
428        JsonValue::DateTime(s) => {
429            let dt = chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S")
430                .or_else(|_| chrono::NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S"))
431                .unwrap_or_else(|_| {
432                    chrono::NaiveDate::from_ymd_opt(1970, 1, 1)
433                        .unwrap()
434                        .and_hms_opt(0, 0, 0)
435                        .unwrap()
436                });
437            L::DateTime(dt)
438        }
439        JsonValue::Time(s) => {
440            let t = chrono::NaiveTime::parse_from_str(s, "%H:%M:%S")
441                .unwrap_or_else(|_| chrono::NaiveTime::from_hms_opt(0, 0, 0).unwrap());
442            L::Time(t)
443        }
444        JsonValue::Duration(secs) => L::Duration(chrono::Duration::seconds(*secs)),
445        JsonValue::Error(code) => L::Error(
446            formualizer_common::error::ExcelError::from_error_string(code),
447        ),
448        JsonValue::Pending => L::Pending,
449    }
450}
451
452// Stream JSON workbook contents into the evaluation engine (values + formulas),
453// and propagate the workbook date system to the engine config.
454impl<R> formualizer_eval::engine::ingest::EngineLoadStream<R> for JsonAdapter
455where
456    R: formualizer_eval::traits::EvaluationContext,
457{
458    type Error = IoError;
459
460    fn stream_into_engine(
461        &mut self,
462        engine: &mut formualizer_eval::engine::Engine<R>,
463    ) -> Result<(), Self::Error> {
464        // Propagate date system: if any sheet declares 1904, treat workbook as 1904
465        let any_1904 = self.data.sheets.values().any(|s| s.date_system_1904);
466        engine.config.date_system = if any_1904 {
467            formualizer_eval::engine::DateSystem::Excel1904
468        } else {
469            formualizer_eval::engine::DateSystem::Excel1900
470        };
471
472        // Ensure all sheets exist in the graph first
473        for name in self.data.sheets.keys() {
474            engine
475                .graph
476                .add_sheet(name)
477                .map_err(|e| IoError::from_backend("json", e))?;
478        }
479
480        // Ingest values via Arrow IngestBuilder per sheet
481        let chunk_rows: usize = 32 * 1024;
482        for (name, sheet) in self.data.sheets.iter() {
483            let dims = sheet.dimensions.unwrap_or_else(|| {
484                let mut max_r = 0u32;
485                let mut max_c = 0u32;
486                for c in &sheet.cells {
487                    if c.row > max_r {
488                        max_r = c.row;
489                    }
490                    if c.col > max_c {
491                        max_c = c.col;
492                    }
493                }
494                (max_r, max_c)
495            });
496            let rows = dims.0 as usize;
497            let cols = dims.1 as usize;
498
499            let mut aib = formualizer_eval::arrow_store::IngestBuilder::new(
500                name,
501                cols,
502                chunk_rows,
503                engine.config.date_system,
504            );
505            // Build a map for quick lookup
506            let mut cell_map: BTreeMap<(u32, u32), &JsonCell> = BTreeMap::new();
507            for c in &sheet.cells {
508                cell_map.insert((c.row, c.col), c);
509            }
510            for r in 1..=rows {
511                let mut row_vals: Vec<formualizer_common::LiteralValue> =
512                    vec![formualizer_common::LiteralValue::Empty; cols];
513                for c in 1..=cols {
514                    if let Some(cell) = cell_map.get(&(r as u32, c as u32))
515                        && let Some(v) = &cell.value
516                    {
517                        row_vals[c - 1] = json_to_literal(v);
518                    }
519                }
520                aib.append_row(&row_vals)
521                    .map_err(|e| IoError::from_backend("json", e))?;
522            }
523            let asheet = aib.finish();
524            let store = engine.sheet_store_mut();
525            if let Some(pos) = store.sheets.iter().position(|s| s.name.as_ref() == name) {
526                store.sheets[pos] = asheet;
527            } else {
528                store.sheets.push(asheet);
529            }
530
531            // Formulas: either stage into graph now or defer
532            if engine.config.defer_graph_building {
533                for c in &sheet.cells {
534                    if let Some(f) = &c.formula {
535                        if f.is_empty() {
536                            continue;
537                        }
538                        engine.stage_formula_text(name, c.row, c.col, f.clone());
539                    }
540                }
541            } else {
542                let mut builder = engine.begin_bulk_ingest();
543                let sid = builder.add_sheet(name);
544                for c in &sheet.cells {
545                    if let Some(f) = &c.formula {
546                        if f.is_empty() {
547                            continue;
548                        }
549                        let with_eq = if f.starts_with('=') {
550                            f.clone()
551                        } else {
552                            format!("={f}")
553                        };
554                        let parsed = formualizer_parse::parser::parse(&with_eq)
555                            .map_err(|e| IoError::from_backend("json", e))?;
556                        builder.add_formulas(sid, std::iter::once((c.row, c.col, parsed)));
557                    }
558                }
559                let _ = builder.finish();
560            }
561        }
562        // Finalize sheet indexes after load
563        for name in self.data.sheets.keys() {
564            engine.graph.finalize_sheet_index(name);
565        }
566        engine.graph.set_first_load_assume_new(false);
567        engine.graph.reset_ensure_touched();
568        Ok(())
569    }
570}
formualizer_workbook/backends/json.rs

formualizer_workbook/backends/
json.rs
