Performance enhancement - cache calculated values

[grahamorrell]

Description

My use case is as follows. I open an Excel file. I inject around 10 values into various cells (using SetCellStr) and then extract around 100 values from formulas on the sheet (using CalcCellValue) and then exit.

It seems that Excelize doesn't cache any values it has calculated via CalcCellValue, and recalculates them over and over if they are used in other cells. If these formulae are time consuming this is very wasteful.

Steps to reproduce the issue:

Example :

A1: 40
A2: =VERYEXPENSIVEFUNCTION( )
A3: =A1+A2

Describe the results you received:

CalcCellValue("Sheet1", "A1") // takes 0ms
CalcCellValue("Sheet1", "A2") // takes 100ms (for example)
CalcCellValue("Sheet1", "A3") // takes 100ms (for example)

Describe the results you expected:

CalcCellValue("Sheet1", "A3") could be practically instant, because Excelize could/should already know the value of A1 and A2 from its previous calculations. It also knows that no SetCellX( ) functions have been called and therefore the values A1 and A2 can't have changed. So it shouldn't have to recalculate A2 when calculating A3.

As a first pass Excelize could cache the results of CalcCellValue and invalidate whole cache when any SetCellX( ) is called.

A better version could only invalidate the parts of the cache that are affected by a SetCellX( ) by building a formula dependency tree (as previously suggested here)

Output of go version:

go version go1.23.1 windows/amd64

Excelize version or commit ID:

2.9.0

Environment details (OS, Microsoft Excel™ version, physical, etc.):
Windows