EvotecIT
Suspected Bug with Request-ExcelWorkSheetCalculation
Hello Przemek,
I can successfully read both formulas and results as text from cells by looping through a series of rows like so:
For ($i=1; $i -le 40; $i++){
$RowFormula = $Worksheet.Cells[$i,4].Formula.PadRight(30)
$RowText = $Worksheet.Cells[$i,4].Text.PadLeft(5)
$Report = "Row " + $i + ": Formula: " + $RowFormula + "`t" + "Text: " + $RowText
Write-Output $Report
}
However, if I update some data cells, then call Request-ExcelWorkSheetCalculation, then read the re-calculated results any formula that contains an integer or decimal constant results in an #VALUE! error. Brackets used (outside of functions) are also an issue after re-calculation.
For example: formulas like: =SUM(A1:C1) or =AVERAGE(A6:C6) or =PRODUCT(A16:C16) containing only cell references will be read successfully, but remain unchanged from there original values after re-calculation, whereas;
Formulas like =SUM(A2:C2)+2 or =SUM(A3:C3)-2 or =SUM(A4:C4)/2 or =SUM(A5:C5)*2 or SUM(A2:C2)+2.5 or =SUM(A3:C3)-2.5 or =SUM(A4:C4)/2.5 or =SUM(A5:C5)*2.5 which contain constants (such as 2 or 2.5) when read will result in error.
Once the error is encountered the only way to re-set it for the initial read, is to manually open and recalculate then save the file, however the error returns as soon as Request-ExcelWorkSheetCalculation is called again.
Please find my full test script below (and sample file attached): pswriteexcel_cell.xlsx
Import-Module -Name 'C:\Program Files\WindowsPowerShell\Modules\PSWriteExcel'
$FilePath = "c:\ps_scripts\pswriteexcel_cell.xlsx"
$Excel = Get-ExcelDocument -Path $FilePath
$Worksheet = Get-ExcelWorkSheet -ExcelDocument $Excel -Name 'Sheet1'
# read values from existing formulas
For ($i=1; $i -le 40; $i++){
$RowFormula = $Worksheet.Cells[$i,4].Formula.PadRight(30)
$RowText = $Worksheet.Cells[$i,4].Text.PadLeft(5)
$Report = "Row " + $i + ": Formula: " + $RowFormula + "`t" + "Text: " + $RowText
Write-Output $Report
}
$update = 5;
# update some data
For ($i=1; $i -le 40; $i++){
Add-ExcelWorkSheetCell -ExcelWorksheet $WorkSheet -CellRow $i -CellColumn 3 -CellValue $update
}
# re-calculate sheet
Request-ExcelWorkSheetCalculation -ExcelWorksheet $WorkSheet
# re-read values
For ($i=1; $i -le 40; $i++){
$RowFormula = $Worksheet.Cells[$i,4].Formula.PadRight(30)
$RowText = $Worksheet.Cells[$i,4].Text.PadLeft(5)
$Report = "Row " + $i + ": Formula: " + $RowFormula + "`t" + "Text: " + $RowText
Write-Output $Report
}
Save-ExcelDocument -ExcelDocument $Excel -FilePath $FilePath
Outputs:
Row 1: Formula: SUM(A1:C1) Text: 6
Row 2: Formula: SUM(A2:C2)+2 Text: 8
Row 3: Formula: SUM(A3:C3)-2 Text: 4
Row 4: Formula: SUM(A4:C4)/2 Text: 3
Row 5: Formula: SUM(A5:C5)*2 Text: 12
Row 6: Formula: AVERAGE(A6:C6) Text: 2
Row 7: Formula: AVERAGE(A7:C7)+2 Text: 4
Row 8: Formula: AVERAGE(A8:C8)-2 Text: 0
Row 9: Formula: AVERAGE(A9:C9)/2 Text: 1
Row 10: Formula: AVERAGE(A10:C10)*2 Text: 4
Row 11: Formula: (A11*B11*C11) Text: 6
Row 12: Formula: (A12*B12*C12)+2 Text: 8
Row 13: Formula: (A13*B13*C13)-2 Text: 4
Row 14: Formula: (A14*B14*C14)/2 Text: 3
Row 15: Formula: (A15*B15*C15)*2 Text: 12
Row 16: Formula: PRODUCT(A16:C16) Text: 6
Row 17: Formula: PRODUCT(A17:C17)+2 Text: 8
Row 18: Formula: PRODUCT(A18:C18)-2 Text: 4
Row 19: Formula: PRODUCT(A19:C19)/2 Text: 3
Row 20: Formula: PRODUCT(A20:C20)*2 Text: 12
Row 21: Formula: SUM(A21:C21) Text: 6
Row 22: Formula: SUM(A22:C22)+2.5 Text: 8.5
Row 23: Formula: SUM(A23:C23)-2.5 Text: 3.5
Row 24: Formula: SUM(A24:C24)/2.5 Text: 2.4
Row 25: Formula: SUM(A25:C25)*2.5 Text: 15
Row 26: Formula: AVERAGE(A26:C26) Text: 2
Row 27: Formula: AVERAGE(A27:C27)+2.5 Text: 4.5
Row 28: Formula: AVERAGE(A28:C28)-2.5 Text: -0.5
Row 29: Formula: AVERAGE(A29:C29)/2.5 Text: 0.8
Row 30: Formula: AVERAGE(A30:C30)*2.5 Text: 5
Row 31: Formula: (A31*B31*C31) Text: 6
Row 32: Formula: (A32*B32*C32)+2.5 Text: 8.5
Row 33: Formula: (A33*B33*C33)-2.5 Text: 3.5
Row 34: Formula: (A34*B34*C34)/2.5 Text: 2.4
Row 35: Formula: (A35*B35*C35)*2.5 Text: 15
Row 36: Formula: PRODUCT(A36:C36) Text: 6
Row 37: Formula: PRODUCT(A37:C37)+2.5 Text: 8.5
Row 38: Formula: PRODUCT(A38:C38)-2.5 Text: 3.5
Row 39: Formula: PRODUCT(A39:C39)/2.5 Text: 2.4
Row 40: Formula: PRODUCT(A40:C40)*2.5 Text: 15
Row 1: Formula: SUM(A1:C1) Text: 6
Row 2: Formula: SUM(A2:C2)+2 Text: #VALUE!
Row 3: Formula: SUM(A3:C3)-2 Text: #VALUE!
Row 4: Formula: SUM(A4:C4)/2 Text: #VALUE!
Row 5: Formula: SUM(A5:C5)*2 Text: #VALUE!
Row 6: Formula: AVERAGE(A6:C6) Text: 2
Row 7: Formula: AVERAGE(A7:C7)+2 Text: #VALUE!
Row 8: Formula: AVERAGE(A8:C8)-2 Text: #VALUE!
Row 9: Formula: AVERAGE(A9:C9)/2 Text: #VALUE!
Row 10: Formula: AVERAGE(A10:C10)*2 Text: #VALUE!
Row 11: Formula: (A11*B11*C11) Text: #VALUE!
Row 12: Formula: (A12*B12*C12)+2 Text: #VALUE!
Row 13: Formula: (A13*B13*C13)-2 Text: #VALUE!
Row 14: Formula: (A14*B14*C14)/2 Text: #VALUE!
Row 15: Formula: (A15*B15*C15)*2 Text: #VALUE!
Row 16: Formula: PRODUCT(A16:C16) Text: 6
Row 17: Formula: PRODUCT(A17:C17)+2 Text: #VALUE!
Row 18: Formula: PRODUCT(A18:C18)-2 Text: #VALUE!
Row 19: Formula: PRODUCT(A19:C19)/2 Text: #VALUE!
Row 20: Formula: PRODUCT(A20:C20)*2 Text: #VALUE!
Row 21: Formula: SUM(A21:C21) Text: 6
Row 22: Formula: SUM(A22:C22)+2.5 Text: #VALUE!
Row 23: Formula: SUM(A23:C23)-2.5 Text: #VALUE!
Row 24: Formula: SUM(A24:C24)/2.5 Text: #VALUE!
Row 25: Formula: SUM(A25:C25)*2.5 Text: #VALUE!
Row 26: Formula: AVERAGE(A26:C26) Text: 2
Row 27: Formula: AVERAGE(A27:C27)+2.5 Text: #VALUE!
Row 28: Formula: AVERAGE(A28:C28)-2.5 Text: #VALUE!
Row 29: Formula: AVERAGE(A29:C29)/2.5 Text: #VALUE!
Row 30: Formula: AVERAGE(A30:C30)*2.5 Text: #VALUE!
Row 31: Formula: (A31*B31*C31) Text: #VALUE!
Row 32: Formula: (A32*B32*C32)+2.5 Text: #VALUE!
Row 33: Formula: (A33*B33*C33)-2.5 Text: #VALUE!
Row 34: Formula: (A34*B34*C34)/2.5 Text: #VALUE!
Row 35: Formula: (A35*B35*C35)*2.5 Text: #VALUE!
Row 36: Formula: PRODUCT(A36:C36) Text: 6
Row 37: Formula: PRODUCT(A37:C37)+2.5 Text: #VALUE!
Row 38: Formula: PRODUCT(A38:C38)-2.5 Text: #VALUE!
Row 39: Formula: PRODUCT(A39:C39)/2.5 Text: #VALUE!
Row 40: Formula: PRODUCT(A40:C40)*2.5 Text: #VALUE!
Please advise. Only too happy to test, when addressed. Thank you.
Hello Przemek,
Thank you for your reply.
My default setting are (US) with decimal (period) and thousands (comma) separators.
I have tried using (German) settings with decimal (comma) and thousands (period) separators and the issue persists. Excel > Options >Advanced > Editing Options > Use System Separators.
Rows 1-21, any constants are integers. Rows 22-40, any constants are decimal.
I did this to test both integer and decimal constants in formulas.
Again, thank you for reviewing my post.
Hello Przemek,
Please advise if I can be of any further assistance in troubleshooting this issue.
Kind regards, Andrew
I don't see anything obvious unfortunately. I've checked code and it seems it's related to Calculation mechanism where it simply does it wrong. The output when you open Excel is correct so just inside PowerShell it's wrong. I guess underlying library of Epplus is at fault and since they abandoned the free version it's not getting fixed. There is new version for non-commercial use but that would heavily impact use cases of PSWriteExcel. I guess one could read formula, disect it into pieces and do the calculation manually, but that's another topic.
Thank you for the update Przemek. As you say the current workaround is to parse the formula and perform the recalculation manually within the PowerShell script. I have been doing this as required. However, the workaround becomes difficult with more complex formulas (with many dependencies).
I assume both PSWriteExcel and ImportExcel PS modules are effected by the change in the licencing model of EPPlus. Does that mean you will not continue development of PSWriteExcel? or perhaps, unless it is based on the non-commercial version of EPPlus. I'm unsure of the final outcome of the change of licence. Please advise.
No, there is no plan to stop working on PSWriteExcel, just like there is no plan to stop PSWriteWord or PSWriteHTML. It's just more difficult with PSWriteExcel and PSWriteWord because the underlying library changing their license model. This means we have three choices:
- Continue with features that Epplus provides, considering some non-fixable bugs as known issues but working on other areas (after all Epplus is a beast of features with charts and multiple other things that could be useful and that I was thinking on adding)
- Consider changing to a new model where you can use PSWriteExcel non-commercially with a new library that is actively developed. If you notice what they say on changelog: https://epplussoftware.com/en/Developers/MinorFeaturesAndIssues they made a lot of improvements to calculation formulas so it may be that it was simply never implemented to start with. If you need to use it commercially you would insert Key that you bought from the Epplus Software company. As far as I understand the new model there is no feature distinction so it's a bit goodwill on whether you buy the license or not - although that may be just me misreading it. However, that will be a bit like stealing - so it's possible some people could abuse the free model for commercial use with non-commercial software.
Considering the license is 299USD per year per license or 29USD per month this doesn't seems like deal breaker for most people that consider how much development of similar tools costs.
- Switch to a different library such as https://github.com/ClosedXML/ClosedXML but that means most of the stuff done here has to be adjusted to the new library and we can encounter other issues. I downloaded it and started playing, but haven't had much time.
I have a similar problem with PSWriteWord where the license model is changed and there are features I would like but it's hard to push "payed" licenses to PowerShell community. Everyone expects things for free ;-)
It's really hard choice. I don't have the same hard choices with PSWriteHTML because I have mostly MIT code all over the place that I glue together, so it's a bit up to me what goes in, what doesn't.
I am not paid either - and I do those stuff because I need them as much as you do for my other projects. I am currently undecided which direction to choose so I usually stay as status quo updating the current code until I hit library problems like this one.