ravenrs_sheet/engine/cmd/
python.rs

1//! Python in cells: making and editing Python cells, and their return type. This build stores
2//! the code and the last result a file cached; it doesn't run Python, so a cell with new or
3//! changed code shows `#BLOCKED!`.
4
5use std::sync::Arc;
6
7use crate::base::{CellRef, Value};
8use crate::model::{Cell, PyCode, python::MAX_FORMULA_CHARS};
9use serde_json::{Value as Json, json};
10
11use super::{CommandSpec, bad, cell_param_on, cmd, edit, has_doc, str_param, target_sheet};
12use crate::engine::{EngineError, Result, Session};
13
14pub fn specs() -> Vec<CommandSpec> {
15    vec![
16        cmd!(
17            "python.set",
18            "Set Python Code",
19            [],
20            None,
21            "{cell?, code, output?: object|value} makes the cell (default: the active cell) a Python cell with this code; the code must fit in Excel's 8,192-character formula once written as PY(…). `output` is the return type: `value` (an Excel value, the default) or `object` (a Python object)",
22            has_doc,
23            set
24        ),
25        cmd!(
26            "python.output",
27            "Python Output",
28            [],
29            Some("Ctrl+Alt+Shift+M"),
30            "{cell?, mode?: object|value} sets the return type of a Python cell (default: the active cell): `object` (a Python object) or `value` (an Excel value). Without `mode` it switches to the other one. The cell keeps its code; a cached result of the old return type goes, as with `python.set`, so the cell shows #BLOCKED! until it runs",
31            has_doc,
32            output
33        ),
34    ]
35}
36
37fn target_cell(s: &Session, p: &Json) -> Result<(usize, CellRef)> {
38    let sheet = target_sheet(s, p)?;
39    let at = match cell_param_on(s, p, "cell", sheet)? {
40        Some(c) => c,
41        None => s.doc()?.selection.active,
42    };
43    Ok((sheet, at))
44}
45
46fn existing(s: &Session, sheet: usize, at: CellRef) -> Result<Option<PyCode>> {
47    Ok(s.doc()?.wb.sheet(sheet).and_then(|sh| sh.cell(at)).and_then(|c| c.py.as_deref()).cloned())
48}
49
50fn output_mode(cmd: &str, p: &Json, key: &str) -> Result<Option<bool>> {
51    match p.get(key) {
52        None | Some(Json::Null) => return Ok(None),
53        Some(Json::String(_)) => {}
54        Some(_) => return Err(bad(cmd, format!("`{key}` is `object` or `value`"))),
55    }
56    match str_param(p, key) {
57        None => Ok(None),
58        Some(m) if m.eq_ignore_ascii_case("object") => Ok(Some(true)),
59        Some(m) if m.eq_ignore_ascii_case("value") => Ok(Some(false)),
60        Some(m) => Err(bad(cmd, format!("`{key}` is `object` or `value`, not `{m}`"))),
61    }
62}
63
64fn set(s: &mut Session, p: &Json) -> Result<Json> {
65    let (sheet, at) = target_cell(s, p)?;
66    let code = match p.get("code") {
67        Some(Json::String(c)) => c.as_str(),
68        Some(_) => return Err(bad("python.set", "`code` must be text")),
69        None => return Err(bad("python.set", "missing `code`")),
70    };
71    let mode = output_mode("python.set", p, "output")?;
72    let old = existing(s, sheet, at)?;
73    let object = mode.or(old.as_ref().map(|o| o.object)).unwrap_or(false);
74    store(s, "python.set", sheet, at, PyCode { object, ..PyCode::new(code) }, old)?;
75    let d = s.doc()?;
76    let shown = d.wb.sheet(sheet).map(|sh| sh.value(at)).unwrap_or_default();
77    Ok(json!({"cell": at.a1(), "value": crate::engine::cmd::inspect::value_json(&shown)}))
78}
79
80/// Writes `py` into the cell, which held `old`: what `python.set` and `python.output` share. The
81/// code must fit in one cell and a table header takes none; a locked cell on a protected sheet is
82/// refused by the edit itself.
83fn store(s: &mut Session, cmd: &str, sheet: usize, at: CellRef, mut py: PyCode, old: Option<PyCode>) -> Result<()> {
84    if !py.fits() {
85        return Err(bad(
86            cmd,
87            format!("the code doesn't fit in one cell: written as PY(…) it would be longer than Excel's {MAX_FORMULA_CHARS}-character formula limit"),
88        ));
89    }
90    // A table header is the column's name, never a formula or code (as in Excel).
91    let header =
92        s.doc()?.wb.sheet(sheet).is_some_and(|sh| sh.tables.iter().any(|t| t.header_row && t.range.start.row == at.row && t.range.contains(at)));
93    if header {
94        return Err(EngineError::Other(format!("{} is a table header; Python cells can't go in a table's header row", at.a1())));
95    }
96    // Unchanged code with the same return type keeps its result (and its value); new code, or a
97    // new return type, has none until it runs: a result of the other type would show a value and
98    // preview that don't belong to the cell.
99    let keep = old.filter(|o| o.code == py.code && o.object == py.object);
100    let value = match &keep {
101        Some(o) => {
102            py.output = o.output.clone();
103            py.stale = o.stale;
104            None
105        }
106        None => Some(crate::model::python::BLOCKED),
107    };
108    edit(s, |cx| {
109        let sh = cx.sheet_mut(sheet)?;
110        let style = sh.style_id(at);
111        let value = value.unwrap_or_else(|| sh.cell(at).map(|c| c.value.clone()).unwrap_or(Value::Empty));
112        // A spill the cell had (a formula's, or the old result's) is left to the recalculation,
113        // which removes it and recalculates what read it.
114        sh.set_cell(at, Cell { value, formula: None, style, py: Some(Arc::new(py)) });
115        cx.touch(sheet, at);
116        Ok(())
117    })
118}
119
120/// `python.output`: the cell's return type. The code stays; a cached result belongs to the old
121/// return type, so it goes, as with `python.set` and a new return type: the cell shows
122/// `#BLOCKED!` until it runs, and its old spill goes with its readers recalculated.
123fn output(s: &mut Session, p: &Json) -> Result<Json> {
124    let (sheet, at) = target_cell(s, p)?;
125    let Some(old) = existing(s, sheet, at)? else {
126        return Err(EngineError::Other(format!("{} isn't a Python cell, so it has no Python output to change", at.a1())));
127    };
128    let object = output_mode("python.output", p, "mode")?.unwrap_or(!old.object);
129    let mode = |object: bool| if object { "object" } else { "value" };
130    if object != old.object {
131        let py = PyCode { code: old.code.clone(), object, output: None, stale: false };
132        store(s, "python.output", sheet, at, py, Some(old))?;
133    }
134    let d = s.doc()?;
135    let shown = d.wb.sheet(sheet).map(|sh| sh.value(at)).unwrap_or_default();
136    Ok(json!({"cell": at.a1(), "output": mode(object), "value": crate::engine::cmd::inspect::value_json(&shown)}))
137}
ravenrs_sheet/engine/cmd/python.rs

ravenrs_sheet/engine/cmd/
python.rs
