iOfficeAI / iOfficeAI/OfficeCLI

[Bug] xlsx: shared-formula children are not expanded; #OCLI_NOTEVAL! on empty cached values, and dependents lose their cached values on save

Open
#391 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
30.7k
Forks
2.1k
Avg merge
9d 8h
Merged PRs (30d)
5

Description

## Summary

Shared-formula children (`` cells that inherit the master's formula) are not expanded:

1. `get` and `view … annotated` show an empty formula for them.
2. When such a cell's cached value is an empty string, it is reported as `#OCLI_NOTEVAL!` (`evaluated=false`).
3. On save, a cell that depends on it is recomputed from that empty cached value and its own cached value is **dropped**, even though the edit touched an unrelated cell.

Point 3 is a data change on a file the user only meant to edit elsewhere: a reader of cached values (Excel before recalculation, openpyxl `data_only=True`, any downstream tool) now sees an empty cell where the file held a number.

## Environment

officecli 1.0.149 (Homebrew, dotnet 10.0.400), macOS 26.4.1 arm64.

## Minimal reproduction

A stale-cache workbook, the shape Excel add-ins and templates commonly save with `fullCalcOnLoad` set: `B1:B3` is a shared formula whose children carry empty cached strings, and `D1` depends on `B2` and holds a cached number.

```python
import re, shutil, zipfile
from openpyxl import Workbook

wb = Workbook(); ws = wb.active; ws.title = "Sheet1"
for i in range(1, 4): ws.cell(i, 1, i); ws.cell(i, 2, "x")
ws["D1"] = 4; ws["E1"] = 1
wb.save("min_shared.xlsx")

def fn(x):
x = re.sub(r']*>.*?', 'IF(A1>1,A1*2,"")', x, count=1, flags=re.S)
x = re.sub(r']*>.*?', '', x, count=1, flags=re.S)
x = re.sub(r']*>.*?', '', x, count=1, flags=re.S)
x = re.sub(r']*>.*?', 'IF(ISNUMBER(B2),B2,"")4', x, count=1, flags=re.S)
return x

src = zipfile.ZipFile("min_shared.xlsx"); out = zipfile.ZipFile("tmp.xlsx", "w", zipfile.ZIP_DEFLATED)
for info in src.infolist():
data = src.read(info.filename)
if info.filename == "xl/worksheets/sheet1.xml": data = fn(data.decode()).encode()
out.writestr(info, data)
out.close(); src.close(); shutil.move("tmp.xlsx", "min_shared.xlsx")
```

```bash
export OFFICECLI_NO_AUTO_RESIDENT=1
officecli get min_shared.xlsx /Sheet1/B2
# /Sheet1/B2 (cell) "#OCLI_NOTEVAL!" type=String formula= evaluated=false
officecli get min_shared.xlsx /Sheet1/D1
# /Sheet1/D1 (cell) "4" type=Number formula=IF(ISNUMBER(B2),B2,"") cachedValue=4 computedValue= evaluated=true
officecli view min_shared.xlsx annotated
# D1: [4] ← =IF(ISNUMBER(B2),B2,"")
# B2: [#OCLI_NOTEVAL!] ← =

cp min_shared.xlsx edited.xlsx
officecli set edited.xlsx /Sheet1/E1 --prop value=9 # an unrelated cell
```

`xl/worksheets/sheet1.xml`, cell `D1`:

| file | D1 |
|---|---|
| input | `IF(ISNUMBER(B2),B2,"")4` |
| after the edit | `IF(ISNUMBER(B2),B2,"")` (cached value gone) |

With a numeric cached value on `B2` the child is shown with `cachedValue=4` and `D1` keeps its value, so the engine is using the child's cached value in place of the formula it cannot see.

## Where it was found

A vendor `.xlsm` with 899 shared-formula children across three sheets: 336 of them (the ones with empty cached strings) came back as `#OCLI_NOTEVAL!`, and after a one-cell edit several dependent cells had their cached numbers dropped or replaced (`0` where the file held an empty string, and vice versa). Excel recomputes on open because the file sets `fullCalcOnLoad`, so the user never sees it, but anything reading the cached values does.

## Expected

Shared formulas are expanded from the master's `ref` and formula (adjusting relative references per child), so the children evaluate like any other formula cell; and a cell whose inputs cannot be evaluated keeps its cached value rather than having it removed.

Contributor guide

Open the contributing guide

Research direction

Reproduce the issue with the provided Python workbook generator and the officecli get, view, and set commands, then inspect xl/worksheets/sheet1.xml before and after the edit. Trace shared-formula loading, evaluation, and save behavior; done means children expose expanded formulas and unevaluable inputs retain their cached values.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
backend, cli
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.