ironcalc/
compare.rs

1#![allow(clippy::unwrap_used, clippy::panic)]
2
3use std::path::Path;
4
5use ironcalc_base::cell::CellValue;
6use ironcalc_base::types::*;
7use ironcalc_base::{expressions::utils::number_to_column, Model};
8
9use crate::export::save_to_xlsx;
10use crate::import::load_from_xlsx;
11
12use super::util::get_workbook_metadata;
13
14#[cfg(feature = "mock_time")]
15use crate::mock_time::set_mock_time_from_metadata;
16
17pub struct CompareError {
18    message: String,
19}
20
21type CompareResult<T> = std::result::Result<T, CompareError>;
22
23pub struct Diff {
24    pub sheet_name: String,
25    pub row: i32,
26    pub column: i32,
27    pub value1: Cell,
28    pub value2: Cell,
29    pub reason: String,
30}
31
32// TODO use f64::EPSILON
33const EPS: f64 = 5e-8;
34// const EPS: f64 = f64::EPSILON;
35
36fn numbers_are_close(x: f64, y: f64, eps: f64) -> bool {
37    let norm = (x * x + y * y).sqrt();
38    if norm == 0.0 {
39        return true;
40    }
41    let d = f64::abs(x - y);
42    if d < eps {
43        return true;
44    }
45    d / norm < eps
46}
47/// Compares two Models in the internal representation and returns a list of differences
48pub fn compare(model1: &Model, model2: &Model) -> CompareResult<Vec<Diff>> {
49    let ws1 = model1.workbook.get_worksheet_names();
50    let ws2 = model2.workbook.get_worksheet_names();
51    if ws1.len() != ws2.len() {
52        return Err(CompareError {
53            message: "Different number of sheets".to_string(),
54        });
55    }
56    let eps = if let Ok(CellValue::Number(v)) = model1.get_cell_value_by_ref("METADATA!A1") {
57        v
58    } else {
59        EPS
60    };
61    let mut diffs = Vec::new();
62    let cells = model1.get_all_cells();
63    for cell in cells {
64        let sheet = cell.index;
65        let row = cell.row;
66        let column = cell.column;
67        let cell1 = &model1
68            .workbook
69            .worksheet(sheet)
70            .unwrap()
71            .cell(row, column)
72            .cloned()
73            .unwrap_or_default();
74        let cell2 = &model2
75            .workbook
76            .worksheet(sheet)
77            .unwrap()
78            .cell(row, column)
79            .cloned()
80            .unwrap_or_default();
81        match (cell1, cell2) {
82            (Cell::EmptyCell { .. }, Cell::EmptyCell { .. }) => {}
83
84            (Cell::NumberCell { v: value1, .. }, Cell::NumberCell { v: value2, .. }) => {
85                if !numbers_are_close(*value1, *value2, eps) {
86                    diffs.push(Diff {
87                        sheet_name: ws1[cell.index as usize].clone(),
88                        row,
89                        column,
90                        value1: cell1.clone(),
91                        value2: cell2.clone(),
92                        reason: "Numbers are different".to_string(),
93                    });
94                }
95            }
96
97            (Cell::BooleanCell { v: value1, .. }, Cell::BooleanCell { v: value2, .. }) => {
98                if value1 != value2 {
99                    diffs.push(Diff {
100                        sheet_name: ws1[cell.index as usize].clone(),
101                        row,
102                        column,
103                        value1: cell1.clone(),
104                        value2: cell2.clone(),
105                        reason: "Booleans are different".to_string(),
106                    });
107                }
108            }
109
110            (Cell::ErrorCell { ei: value1, .. }, Cell::ErrorCell { ei: value2, .. }) => {
111                if value1 != value2 {
112                    diffs.push(Diff {
113                        sheet_name: ws1[cell.index as usize].clone(),
114                        row,
115                        column,
116                        value1: cell1.clone(),
117                        value2: cell2.clone(),
118                        reason: "Errors are different".to_string(),
119                    });
120                }
121            }
122
123            (Cell::SharedString { si: value1, .. }, Cell::SharedString { si: value2, .. }) => {
124                // FIXME: compare resolved shared-string contents, not indices,
125                // if the two workbooks can have different shared-string tables.
126                if value1 != value2 {
127                    diffs.push(Diff {
128                        sheet_name: ws1[cell.index as usize].clone(),
129                        row,
130                        column,
131                        value1: cell1.clone(),
132                        value2: cell2.clone(),
133                        reason: "Strings are different".to_string(),
134                    });
135                }
136            }
137
138            (
139                Cell::CellFormula { v: v1, .. } | Cell::ArrayFormula { v: v1, .. },
140                Cell::CellFormula { v: v2, .. } | Cell::ArrayFormula { v: v2, .. },
141            ) => {
142                let mismatch = match (v1, v2) {
143                    (FormulaValue::Unevaluated, FormulaValue::Unevaluated) => false,
144                    (FormulaValue::Boolean(a), FormulaValue::Boolean(b)) => a != b,
145                    (FormulaValue::Number(a), FormulaValue::Number(b)) => {
146                        !numbers_are_close(*a, *b, eps)
147                    }
148                    (FormulaValue::Text(a), FormulaValue::Text(b)) => a != b,
149                    (FormulaValue::Error { ei: e1, .. }, FormulaValue::Error { ei: e2, .. }) => {
150                        e1 != e2
151                    }
152                    // Some xlsx files store formula errors as t="str" with the error code as
153                    // text instead of the correct t="e". Treat them as equivalent.
154                    (FormulaValue::Text(s), FormulaValue::Error { ei, .. })
155                    | (FormulaValue::Error { ei, .. }, FormulaValue::Text(s)) => {
156                        s != &format!("{ei}")
157                    }
158                    _ => true,
159                };
160                if mismatch {
161                    diffs.push(Diff {
162                        sheet_name: ws1[cell.index as usize].clone(),
163                        row,
164                        column,
165                        value1: cell1.clone(),
166                        value2: cell2.clone(),
167                        reason: "Formula values are different".to_string(),
168                    });
169                }
170            }
171
172            (Cell::SpillCell { v: v1, .. }, Cell::SpillCell { v: v2, .. }) => {
173                let mismatch = match (v1, v2) {
174                    (SpillValue::Boolean(a), SpillValue::Boolean(b)) => a != b,
175                    (SpillValue::Number(a), SpillValue::Number(b)) => {
176                        !numbers_are_close(*a, *b, eps)
177                    }
178                    (SpillValue::Text(a), SpillValue::Text(b)) => a != b,
179                    (SpillValue::Error(a), SpillValue::Error(b)) => a != b,
180                    _ => true,
181                };
182                if mismatch {
183                    diffs.push(Diff {
184                        sheet_name: ws1[cell.index as usize].clone(),
185                        row,
186                        column,
187                        value1: cell1.clone(),
188                        value2: cell2.clone(),
189                        reason: "Spill values are different".to_string(),
190                    });
191                }
192            }
193
194            (_, _) => {
195                diffs.push(Diff {
196                    sheet_name: ws1[cell.index as usize].clone(),
197                    row,
198                    column,
199                    value1: cell1.clone(),
200                    value2: cell2.clone(),
201                    reason: "Types are different".to_string(),
202                });
203            }
204        }
205    }
206    Ok(diffs)
207}
208
209fn cell_display(cell: &Cell) -> String {
210    match cell {
211        Cell::EmptyCell { .. } => "(empty)".to_string(),
212        Cell::NumberCell { v, .. } => format!("{v}"),
213        Cell::BooleanCell { v, .. } => format!("{v}"),
214        Cell::ErrorCell { ei, .. } => format!("{ei} (error)"),
215        Cell::SharedString { si, .. } => format!("shared_string[{si}]"),
216        Cell::CellFormula {
217            v: FormulaValue::Unevaluated,
218            ..
219        } => "(unevaluated formula)".to_string(),
220        Cell::CellFormula {
221            v: FormulaValue::Boolean(v),
222            ..
223        }
224        | Cell::ArrayFormula {
225            v: FormulaValue::Boolean(v),
226            ..
227        } => format!("{v} (bool)"),
228        Cell::CellFormula {
229            v: FormulaValue::Number(v),
230            ..
231        }
232        | Cell::ArrayFormula {
233            v: FormulaValue::Number(v),
234            ..
235        } => format!("{v} (number)"),
236        Cell::CellFormula {
237            v: FormulaValue::Text(v),
238            ..
239        }
240        | Cell::ArrayFormula {
241            v: FormulaValue::Text(v),
242            ..
243        } => format!("\"{v}\" (string)"),
244        Cell::CellFormula {
245            v: FormulaValue::Error { ei, .. },
246            ..
247        }
248        | Cell::ArrayFormula {
249            v: FormulaValue::Error { ei, .. },
250            ..
251        } => format!("{ei} (error)"),
252        Cell::ArrayFormula {
253            v: FormulaValue::Unevaluated,
254            s,
255            r,
256            kind,
257            ..
258        } => {
259            format!("(unevaluated {kind:?} formula, size={s}, range={r:?})")
260        }
261        Cell::SpillCell {
262            v: SpillValue::Number(v),
263            s,
264            a,
265        } => {
266            format!("{v} (spill, size={s}, area={a:?})")
267        }
268        Cell::SpillCell {
269            v: SpillValue::Boolean(v),
270            s,
271            a,
272        } => {
273            format!("{v} (spill, size={s}, area={a:?})")
274        }
275        Cell::SpillCell {
276            v: SpillValue::Error(ei),
277            s,
278            a,
279        } => {
280            format!("{ei} (spill, size={s}, area={a:?})")
281        }
282        Cell::SpillCell {
283            v: SpillValue::Text(v),
284            s,
285            a,
286        } => {
287            format!("\"{v}\" (spill, size={s}, area={a:?})")
288        }
289    }
290}
291
292pub(crate) fn compare_models(m1: &Model, m2: &Model) -> Result<(), String> {
293    match compare(m1, m2) {
294        Ok(diffs) => {
295            if diffs.is_empty() {
296                Ok(())
297            } else {
298                let count = diffs.len();
299                let mut lines = format!(
300                    "Models are different ({count} diff{}):\n",
301                    if count == 1 { "" } else { "s" }
302                );
303                for diff in diffs {
304                    let col = number_to_column(diff.column).unwrap();
305                    let cell_ref = format!("{}!{}{}", diff.sheet_name, col, diff.row);
306                    let excel = cell_display(&diff.value1);
307                    let ironcalc = cell_display(&diff.value2);
308                    lines.push_str(&format!(
309                        "\n  {cell_ref:<16}  Excel: {excel:<30}  IronCalc: {ironcalc:<30}  [{}]",
310                        diff.reason
311                    ));
312                }
313                Err(lines)
314            }
315        }
316        Err(r) => Err(format!("Models are different: {}", r.message)),
317    }
318}
319
320/// Tests that file in file_path produces the same results in Excel and in IronCalc.
321pub fn test_file(file_path: &str) -> Result<(), String> {
322    // FIXME: we need to load the model twice :S
323    let model1 = load_from_xlsx(file_path, "en", "UTC", "en").unwrap();
324    let locale = get_workbook_metadata(&model1);
325    // Loading a model already evaluates some cells (conditional formatting),
326    // so the clock must be mocked before loading the models we compare.
327    #[cfg(feature = "mock_time")]
328    set_mock_time_from_metadata(&model1);
329    let model1 = load_from_xlsx(file_path, &locale, "UTC", "en").unwrap();
330    let mut model2 = load_from_xlsx(file_path, &locale, "UTC", "en").unwrap();
331    model2.evaluate();
332    compare_models(&model1, &model2)
333}
334
335/// Tests that file in file_path can be converted to xlsx and read again
336pub fn test_load_and_saving(file_path: &str, temp_dir_name: &Path) -> Result<(), String> {
337    // FIXME: we need to evaluate the model twice :S
338    let model1 = load_from_xlsx(file_path, "en", "UTC", "en").unwrap();
339    let locale = get_workbook_metadata(&model1);
340    // Loading a model already evaluates some cells (conditional formatting),
341    // so the clock must be mocked before loading the models we compare.
342    #[cfg(feature = "mock_time")]
343    set_mock_time_from_metadata(&model1);
344
345    let model1 = load_from_xlsx(file_path, &locale, "UTC", "en").unwrap();
346
347    let base_name = Path::new(file_path).file_name().unwrap().to_str().unwrap();
348
349    let temp_path_buff = temp_dir_name.join(base_name);
350    let temp_file_path = &format!("{}.xlsx", temp_path_buff.to_str().unwrap());
351    // test can save
352    save_to_xlsx(&model1, temp_file_path).unwrap();
353    // test can open
354    let mut model2 = load_from_xlsx(temp_file_path, &locale, "UTC", "en").unwrap();
355    model2.evaluate();
356    compare_models(&model1, &model2)
357}
358
359#[cfg(test)]
360mod tests {
361    use crate::compare::compare;
362    use ironcalc_base::Model;
363
364    #[test]
365    fn compare_different_sheets() {
366        let mut model1 = Model::new_empty("model", "en", "UTC", "en").unwrap();
367        model1.new_sheet();
368        let model2 = Model::new_empty("model", "en", "UTC", "en").unwrap();
369
370        assert!(compare(&model1, &model2).is_err());
371    }
372}
ironcalc/compare.rs

ironcalc/
compare.rs
