Skip to content

Add (*File).Recalc to evaluate formulas and persist cached values #2303

@krystophny

Description

@krystophny

Problem

excelize has no "open → recalc → save" workflow. A consumer that wants it hits three walls:

  1. No cached-value writer. SetCellFloat / SetCellStr / etc. call removeFormula, which deletes <f> and wipes shared-formula children.
  2. Round-tripping. CalcCellValue returns a string; writing it back via SetCellValue records t="s". Aggregates over that shared-string blob return 0.
  3. 3D references:
    • SUM(Sheet1:Sheet3!A1) unquoted → #NAME? invalid reference.
    • SUM('Sheet1':'Sheet3'!A1) quoted → parses, but rangeResolver overwrites sheet per iteration and evaluates only the last sheet.

Proposal

Add two exported methods:

func (f *File) RecalcCell(sheet, cell string) error
func (f *File) Recalc(opts ...RecalcOptions) error

RecalcCell evaluates one formula cell and writes the typed result into <v>/<t>, preserving <f> and shared-formula grouping. Recalc walks formula cells and calls RecalcCell on each. Dependency resolution reuses the existing recursive calcCellValue evaluator; circular references are bounded by MaxCalcIterations.

Scope

Four narrow PRs split under this umbrella, each with its own tracking issue where appropriate:

  1. 3D references, unquoted form. parseReference recognises <sheetA>:<sheetB>!<ref> and expands in workbook order; aggregates consume the expanded matrix. Covers SUM(Jänner:Dezember!M40) etc. Closes this issue (partial).
  2. RecalcCell + private setCellCachedValue helper + ErrCellNoFormula sentinel. Tracked in Add (*File).RecalcCell to persist a single formula cell's cached value #2307.
  3. File.Recalc whole-workbook sweep built on RecalcCell, aggregating failures via errors.Join. Closes this issue.
  4. RecalcOptions{Sheet, Ref} scoping for File.Recalc. Tracked in Add RecalcOptions{Sheet, Ref} to scope File.Recalc #2308.

Out of scope (tracked elsewhere)

Not needed (verified)

  • _xlfn. prefix stripping already works (calc.go:210).
  • ISOWEEKNUM and SHEET are registered and correct.

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions