1use crate::base::{CellRef, RangeRef, Value};
4use serde_json::{Value as Json, json};
5
6use super::{CommandSpec, EngineError, Result, Session, always, bad, bool_param, cell_param_on, cmd, has_doc, str_param, target_range, target_sheet};
7
8pub fn specs() -> Vec<CommandSpec> {
9 vec![
10 cmd!(query "document.inspect", "Inspect Workbook", [], None, "{} → sheets, selection, names, tables, charts, dirty, trust (local|fromFile), history", has_doc, inspect),
11 cmd!(query "sheet.read", "Read Range", [], None, "{range?: \"A1:D10\" (default: used range), sheet?, formulas?: bool, formatted?: bool} → rows of values", has_doc, read),
12 cmd!(query "cell.get", "Get Cell", [], None, "{cell?, sheet?} → value, formula, display text, style, comment, link, python {code, output, typeName, preview, stale}", has_doc, cell_get),
13 cmd!(query "selection.stats", "Selection Statistics", [], None, "{} → count, sum, average, min, max", has_doc, sel_stats),
14 cmd!(query "history.list", "Undo History", [], None, "{} → undo/redo labels, grouping (an undo group is open)", has_doc, history),
15 cmd!(query "app.commands", "List Commands", [], None, "{search?} → ids, labels, params", always, list_commands),
16 ]
17}
18
19pub fn value_json(v: &Value) -> Json {
20 match v {
21 Value::Empty => Json::Null,
22 Value::Number(n) => json!(n),
23 Value::Text(t) => json!(t.as_ref()),
24 Value::Bool(b) => json!(b),
25 Value::Error(e) => json!({"error": e.as_str()}),
26 Value::Array(a) => {
27 let rows: Vec<Json> =
28 (0..a.rows).map(|r| Json::Array((0..a.cols).map(|c| value_json(&a.get(r, c).cloned().unwrap_or_default())).collect())).collect();
29 Json::Array(rows)
30 }
31 }
32}
33
34fn inspect(s: &mut Session, _: &Json) -> Result<Json> {
35 let d = s.doc()?;
36 let wb = &d.wb;
37 let sheets: Vec<Json> = wb
38 .sheets
39 .iter()
40 .map(|sh| {
41 json!({
42 "name": sh.name,
43 "visibility": format!("{:?}", sh.visibility),
44 "usedRange": sh.used_range().map(|r| r.a1()),
45 "cells": sh.cells.len(),
46 "freeze": sh.freeze,
47 "merges": sh.merges.iter().map(|m| m.a1()).collect::<Vec<_>>(),
48 "tables": sh.tables.iter().map(|t| json!({"name": t.name, "range": t.range.a1(), "style": t.style, "totals": t.totals_row, "columns": t.columns.iter().map(|c| c.name.clone()).collect::<Vec<_>>()})).collect::<Vec<_>>(),
49 "charts": sh.charts.iter().map(|c| json!({"id": c.id, "kind": format!("{:?}", c.kind), "title": c.title, "at": c.anchor.cell.a1(), "series": c.series.len(), "dataTable": c.data_table.as_ref().map(|t| json!({"showKeys": t.show_keys, "horizontalBorder": t.horizontal_border, "verticalBorder": t.vertical_border, "outline": t.outline})), "trendlines": c.series.iter().flat_map(|s| s.trendlines.iter().map(|t| format!("{:?}", t.kind))).collect::<Vec<_>>()})).collect::<Vec<_>>(),
50 "images": sh.images.len(),
51 "shapes": sh.shapes.len(),
52 "conditionalFormats": sh.cond_formats.len(),
53 "validations": sh.validations.len(),
54 "comments": sh.comments.len(),
55 "autofilter": sh.autofilter.as_ref().map(|a| a.range.a1()),
56 "protected": sh.protection.is_some(),
57 "tabColor": sh.tab_color.and_then(|c| c.hex(&wb.theme)),
58 "zoom": sh.zoom,
59 })
60 })
61 .collect();
62 Ok(json!({
63 "title": d.display_title(),
64 "path": d.path,
65 "dirty": d.is_dirty(),
66 "trust": d.trust,
67 "activeSheet": wb.active_sheet(),
68 "sheets": sheets,
69 "selection": d.selection.a1(),
70 "activeCell": d.selection.active.a1(),
71 "names": wb.names.iter().map(|n| json!({"name": n.name, "refersTo": format!("={}", n.formula), "scope": n.scope})).collect::<Vec<_>>(),
72 "calcMode": format!("{:?}", wb.calc.mode),
73 "undo": d.undo.iter().rev().take(20).map(|e| e.label.clone()).collect::<Vec<_>>(),
74 "redo": d.redo.iter().rev().take(20).map(|e| e.label.clone()).collect::<Vec<_>>(),
75 "theme": wb.theme.name,
76 "mode": format!("{:?}", s.mode),
77 }))
78}
79
80fn read(s: &mut Session, p: &Json) -> Result<Json> {
81 let sheet = target_sheet(s, p)?;
82 let d = s.doc()?;
83 let sh = d.wb.sheet(sheet).ok_or(EngineError::NoDocument)?;
84 let r = match str_param(p, "range") {
85 Some(_) => target_range(s, p)?,
86 None => match sh.used_range() {
87 Some(r) => r,
88 None => return Ok(json!({"range": null, "values": []})),
89 },
90 };
91 let r = match sh.used_range() {
92 Some(u) if r.is_full_cols() || r.is_full_rows() => r.intersection(&u).unwrap_or(RangeRef::cell(r.start)),
93 _ => r,
94 };
95 if r.count() > 1_000_000 {
96 return Err(bad("sheet.read", "range larger than 1,000,000 cells; read it in parts"));
97 }
98 let formulas = bool_param(p, "formulas").unwrap_or(false);
99 let formatted = bool_param(p, "formatted").unwrap_or(false);
100 let mut rows = Vec::with_capacity(r.height() as usize);
101 for row in r.start.row..=r.end.row {
102 let mut cols = Vec::with_capacity(r.width() as usize);
103 for col in r.start.col..=r.end.col {
104 let c = CellRef::new(row, col);
105 let v = if formulas && let Some(f) = sh.cell(c).and_then(|x| x.formula.as_ref()) {
106 json!(format!("={}", f.text))
107 } else if formatted {
108 json!(crate::engine::display::cell_text(&d.wb, sh, c))
109 } else {
110 value_json(&sh.value(c))
111 };
112 cols.push(v);
113 }
114 rows.push(Json::Array(cols));
115 }
116 Ok(json!({"range": r.a1(), "sheet": sh.name, "values": rows}))
117}
118
119fn cell_get(s: &mut Session, p: &Json) -> Result<Json> {
120 let sheet = target_sheet(s, p)?;
121 let d = s.doc()?;
122 let sh = d.wb.sheet(sheet).ok_or(EngineError::NoDocument)?;
123 let c = cell_param_on(s, p, "cell", sheet)?.unwrap_or(d.selection.active);
124 let cell = sh.cell(c);
125 let style = d.wb.styles.get(sh.style_id(c));
126 Ok(json!({
127 "cell": c.a1(),
128 "value": value_json(&sh.value(c)),
129 "type": if sh.cell_pictures.contains_key(&c) { "picture" } else { match sh.value(c) { Value::Empty => "empty", Value::Number(_) => "number", Value::Text(_) => "text", Value::Bool(_) => "boolean", Value::Error(_) => "error", Value::Array(_) => "array" } },
130 "formula": cell.and_then(|x| x.formula.as_ref()).map(|f| format!("={}", f.text)),
131 "text": crate::engine::display::cell_text(&d.wb, sh, c),
132 "input": sh.input_text(c),
133 "picture": sh.cell_pictures.get(&c).map(|picture| json!({"mime": picture.mime, "alt": picture.alt, "bytes": picture.data.len()})),
134 "spilledFrom": sh.spill_anchor(c).map(|a| a.a1()),
135 "spill": sh.spill_ranges.get(&c).map(|r| r.a1()),
136 "style": style,
137 "merge": sh.merge_at(c).map(|m| m.a1()),
138 "comment": sh.comments.get(&c),
139 "hyperlink": sh.hyperlinks.get(&c),
140 "python": cell.and_then(|x| x.py.as_deref()).map(|py| json!({
141 "code": py.code,
142 "output": if py.object { "object" } else { "value" },
143 "typeName": py.output.as_ref().map(|o| o.type_name.as_str()),
144 "preview": py.output.as_ref().map(|o| o.preview.as_str()),
145 "stale": py.stale,
146 })),
147 }))
148}
149
150fn sel_stats(s: &mut Session, _: &Json) -> Result<Json> {
151 let d = s.doc()?;
152 let sh = d.wb.active().ok_or(EngineError::NoDocument)?;
153 let st = crate::engine::display::stats(sh, &d.selection.ranges);
154 serde_json::to_value(st).map_err(|e| EngineError::Other(e.to_string()))
155}
156
157fn history(s: &mut Session, _: &Json) -> Result<Json> {
158 let d = s.doc()?;
159 Ok(json!({
160 "undo": d.undo.iter().rev().map(|e| e.label.clone()).collect::<Vec<_>>(),
161 "redo": d.redo.iter().rev().map(|e| e.label.clone()).collect::<Vec<_>>(),
162 "grouping": s.in_group(d.uid),
163 }))
164}
165
166fn list_commands(s: &mut Session, p: &Json) -> Result<Json> {
167 let q = str_param(p, "search").map(str::to_ascii_lowercase);
168 let v: Vec<Json> = s
169 .commands()
170 .into_iter()
171 .filter(|c| q.as_ref().is_none_or(|q| c.id.to_ascii_lowercase().contains(q.as_str()) || c.label.to_ascii_lowercase().contains(q.as_str())))
172 .map(|c| serde_json::to_value(c).unwrap_or_default())
173 .collect();
174 Ok(Json::Array(v))
175}