Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to get the computed value #65

Closed
vchugreev opened this issue Jun 26, 2017 · 10 comments
Closed

How to get the computed value #65

vchugreev opened this issue Jun 26, 2017 · 10 comments

Comments

@vchugreev
Copy link

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
Copy link
Member

xuri commented Jun 26, 2017

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

@vchugreev
Copy link
Author

Got it.

@vchugreev
Copy link
Author

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

@xuri
Copy link
Member

xuri commented Jun 27, 2017

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
Copy link
Author

vchugreev commented Jun 27, 2017

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
Copy link
Member

xuri commented Jun 27, 2017

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
Copy link
Author

vchugreev commented Jun 27, 2017

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
Copy link
Member

xuri commented Jun 27, 2017

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
Copy link
Author

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

@valasek
Copy link

valasek commented Nov 25, 2017

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.

@xuri xuri closed this as completed Jul 5, 2019
xuri added a commit that referenced this issue May 5, 2020
…LOOR.MATH, FLOOR.PRECISE, INT, ISO.CEILING, LN, LOG, LOG10, MDETERM
xuri added a commit that referenced this issue May 6, 2020
xuri added a commit that referenced this issue May 7, 2020
nullfy pushed a commit to nullfy/excelize that referenced this issue Oct 23, 2020
nullfy pushed a commit to nullfy/excelize that referenced this issue Oct 23, 2020
nullfy pushed a commit to nullfy/excelize that referenced this issue Oct 23, 2020
…OOR, FLOOR.MATH, FLOOR.PRECISE, INT, ISO.CEILING, LN, LOG, LOG10, MDETERM
nullfy pushed a commit to nullfy/excelize that referenced this issue Oct 23, 2020
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
…formula functions

- ref qax-os#65, new formula functions: DCOUNT and DCOUNTA
- support percentile symbol in condition criteria expression
- this update dependencies module
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
- Support set and get font color with indexed color
- New export variable `IndexedColorMapping`
- Fix getting incorrect page margin settings when the margin is 0
- Update unit tests and comments typo fixes
- ref qax-os#65, new formula functions: AGGREGATE and SUBTOTAL
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
- Add support for 10 formula functions: ARRAYTOTEXT, FORECAST, FORECAST.LINEAR, FREQUENCY, INTERCEPT, ODDFYIELD, ODDLPRICE, ODDLYIELD, PROB and VALUETOTEXT
- Update unit tests
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
- Correction cell type when formatting date type cell value
- Add check for MID and MIDB formula functions num_chars arguments, prevent panic on specifying a negative number
- Ref qax-os#65, add support for 2 formula functions: SEARCH and SEARCHB
- Fix a v2.8.0 regression bug, error on set print area and print titles with built-in special defined name
- Add new exported function `GetPivotTables` for get pivot tables
- Add a new `Name` field in the `PivotTableOptions` to support specify pivot table name
- Using relative cell reference in the pivot table docs and unit tests
- Support adding slicer content type part internally
- Add new exported source relationship and namespace `NameSpaceSpreadSheetXR10`, `ContentTypeSlicer`, `ContentTypeSlicerCache`, and `SourceRelationshipSlicer`
- Add new exported extended URI `ExtURIPivotCacheDefinition`
- Fix formula argument wildcard match issues
- Update GitHub Actions configuration, test on Go 1.21.x with 1.21.1 and later
- Avoid corrupted workbooks generated by improving compatibility with internally indexed color styles
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
- Improve compatibility for absolute path drawing part
- Fix incorrect table ID generated in the workbook which contains single table cells
- Fix missing relationship parts in the content types in some cases
- Upgrade number format parser to fix missing literal tokens in some cases
- Update built-in zh-cn and zh-tw language number format
- Ref qax-os#65, init new formula function: TEXT
- Remove duplicate style-related variables
- Update the unit tests
jenbonzhang pushed a commit to jenbonzhang/excelize that referenced this issue Oct 22, 2023
3zmx added a commit to 3zmx/excelize that referenced this issue Jan 18, 2024
xuri pushed a commit that referenced this issue Jan 18, 2024
- Initial formula array calculation support
- Update unit test and documentation
rememberher pushed a commit to rememberher/excelize that referenced this issue Jan 25, 2024
xuri pushed a commit that referenced this issue Jan 25, 2024
Co-authored-by: wujierui <wujierui@jimabrand.com>
xuri pushed a commit that referenced this issue Sep 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

3 participants