How to get the computed value

[vchugreev]

How to get the computed value of a cell, after applying the formula, which is written into the cell? For example, A1 = 1, A2 = 2, A3 = SUM (A1: A2)
xlsx.GetCellValue("Sheet1", "A3") -> ""
Can I extract 3?

[xuri]

No you can't, this library doesn't implement the formula calculation engine.

[vchugreev]

Got it.

[vchugreev]

Is it a laborious functional? Is it difficult to implement it? Maybe it can be ported from tealeg/xlsx?

[xuri]

Implementing a formula engine, we need to create parser and calculation engine to come with over hundreds of formula, named references, volatile functions, dynamic references and many more. It would require a very large number of test cases would be several months of work involved. That's an interesting job and I will consider adding support for this feature later. I'll certainly accept that patch if somebody did that. The library tealeg/xlsx doesn't implement it currently.

[vchugreev]

Yes, indeed, this is not the easiest task.

In tealeg/xlsx it is possible to read the already calculated value.

Example:
1.xlsx => A1 = 1, A2 = 2, A3 = SUM(A1: A2)

excelFileName := "1.xlsx"
xlFile, _ := xlsx.OpenFile(excelFileName)

a1 := xlFile.Sheets[0].Rows[0].Cells[0]
a1.SetInt(100)

a3:= xlFile.Sheets[0].Rows[2].Cells[0]
println(a3.Value)

Output:
3

There is no recalculation here, but the calculated value can be read. Maybe it's easier to implement? Such a functional would also be useful.

[xuri]

Excel will storage last computed value with tag <v> in xl/worksheets/sheet%d.xml if the originally consumed cells contain formulas, reference part 1 of the 4th edition of the ECMA-376 Standard for Office Open XML. So you can get same computed value by excelize function GetCellValue() (without calling function UpdateLinkedValue()) or use the library tealeg/xlsx to open a xlsx file that created by Excel application.

[vchugreev]

Ok. So, I missed something. But... Here's the script I'm interested in:

1.xlsx => A1 = 1, A2 = 2, A3 = SUM(A1: A2)

xlsx, _ := excelize.OpenFile("1.xlsx")

a1 := xlsx.GetCellValue("Sheet1", "A1")
a2 := xlsx.GetCellValue("Sheet1", "A2")
a3 := xlsx.GetCellValue("Sheet1", "A3")
println(a1, a2, a3)

xlsx.SetCellValue("Sheet1", "A1", 100)
xlsx.WriteTo("2.xlsx")

xlsx, _ = excelize.OpenFile("2.xlsx")
a1 = xlsx.GetCellValue("Sheet1", "A1")
a2 = xlsx.GetCellValue("Sheet1", "A2")
a3 = xlsx.GetCellValue("Sheet1", "A3")
println(a1, a2, a3)

Output:
1 2 3
100 2 3

Is it possible to somehow get 102?

That is, I just want Excel to recalculate the values I've entered and saved.

Maybe I want too much, because it's just an xml file...

[xuri]

Call function UpdateLinkedValue() before save file and open 2.xlsx with Excel application, A3 will be recalculate and you will see 102, then close and save it, use excelize reopen the file, call function GetCellValue, you'll get recalculated value 102. In other words, let Excel application do the recalculation jobs.

[vchugreev]

Well, all right. Life goes on. Thank you very much for the clarification!

[valasek]

Here is a tested workaround how to invoke MS Excel recalculation engine from MS Power Shell.

Create poweshell script with a name excel.ps1:

param([string]$ExcelFile)
Write-Host "Updating calculated values for", $ExcelFile
$excl=New-Object -ComObject Excel.Application
$excl.Visible = $true
$wrkb=$excl.Workbooks.Open($ExcelFile)
$excl.DisplayAlerts = $TRUE
$Excel.Calculation = -4135
$excl.CalculateBeforeSave = $true
$wrkb.Save()
$wrkb.Close()
$excl.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excl)

In your go program create a new function

func invokeExcelCalculationEngine(file os.FileInfo, fullFileName string) {
  log.Debugln("Entering invokeExcelCalculationEngine")
  script := "./excel.ps1 " + fullFileName
  cmdOut, err := exec.Command("powershell.exe", script).Output()
  if err != nil {
    log.Fatal(err)
  }
  log.Debugf("Command output: %v", string(cmdOut))
}

And in your go program, where you are saving the excel file, use the folowing code to save it and to force external recalculation:

fullFileName := "output_excel.xslx"
xlsx.UpdateLinkedValue()
err = xlsx.SaveAs(fullFileName)
if err != nil {
  log.Debugln(err)
}
// update calculated fields with workaround
fullFileName = "\"" + fullFileName + "\""
invokeExcelCalculationEngine(file, fullFileName)

Enjoy. Simplification of this code is welcomed.