-calcuate producing inconsistent results

[feardamhan]

When I crack open an excel file, inject some values and save with -calculate, some of the formulas dont calculate properly

Example of working formula Style : OfficeOpenXml.Style.ExcelStyle StyleName : Normal StyleID : 31 Value : sfo-m01-fd-nsx Text : sfo-m01-fd-nsx Formula : IF(ISBLANK(input_mgmt_sddc_domain),"Value Missing",mgmt_sddc_domain&"-fd-nsx") FormulaR1C1 : IF(ISBLANK(input_mgmt_sddc_domain),"Value Missing",mgmt_sddc_domain&"-fd-nsx") Hyperlink : Merge : False AutoFilter : False IsRichText : False IsArrayFormula : False RichText : {} Comment : Worksheet : Value Reference Tables FullAddress : 'Value Reference Tables'!P65 FullAddressAbsolute : 'Value Reference Tables'!$P$65 ConditionalFormatting : OfficeOpenXml.ConditionalFormatting.RangeConditionalFormatting DataValidation : OfficeOpenXml.DataValidation.RangeDataValidation Current : Address : P65 Start : OfficeOpenXml.ExcelCellAddress End : OfficeOpenXml.ExcelCellAddress Table : IsName : False Rows : 1 Columns : 1

Another cell, leveraging the same source named cell, but appending different text to the end of it results in this

Style : OfficeOpenXml.Style.ExcelStyle StyleName : Normal StyleID : 31 Value : OfficeOpenXml.FormulaParsing.EpplusExcelDataProvider+RangeInfo-fd-edge Text : OfficeOpenXml.FormulaParsing.EpplusExcelDataProvider+RangeInfo-fd-edge Formula : IF(ISBLANK(input_mgmt_sddc_domain),"Value Missing",mgmt_sddc_domain&"-fd-edge") FormulaR1C1 : IF(ISBLANK(input_mgmt_sddc_domain),"Value Missing",mgmt_sddc_domain&"-fd-edge") Hyperlink : Merge : False AutoFilter : False IsRichText : False IsArrayFormula : False RichText : {} Comment : Worksheet : Value Reference Tables FullAddress : 'Value Reference Tables'!P66 FullAddressAbsolute : 'Value Reference Tables'!$P$66 ConditionalFormatting : OfficeOpenXml.ConditionalFormatting.RangeConditionalFormatting DataValidation : OfficeOpenXml.DataValidation.RangeDataValidation Current : Address : P66 Start : OfficeOpenXml.ExcelCellAddress End : OfficeOpenXml.ExcelCellAddress Table : IsName : False Rows : 1 Columns : 1

Note the value/text properties are different for the second instance than for the first, even tho the format of the formula and the named cell it references are the same

Running Build 7.8.5

[feardamhan]

Some updates from further troubleshooting. It seems to be related to the location of the named cells in question within the file. For instance, in the above case there were three similar cells, one under the other (P64,P65,P66). P64 and P65 were calculating successfully, P66 was not, even with a copy of the formula from P65. Placing the same formula further down the worksheet with a new named cell name also failed to calculate BUT would work fine if you put a simple cell reference in it (rather than the string concatenation formula)

I change the order of P64, P65, and P66 by cutting and pasting P66 in above P64, moving P64 and P65 down one row each. At that point, when I calculate, P64 and P65 still work and P66 still doesn't, even though the content of P66 worked previously when in P65!

Is there some form to maximum number of formula calculations that can be done per worksheet or some other limitation I might be hitting?

[dfinke]

Thanks for using ImportExcel.

The -Calculate switch is simply implemented like this. There may be additional params for it. I don't know.

https://github.com/dfinke/ImportExcel/blob/edf25cf94615b0b2795cc084be579b6ac254391b/Public/Close-ExcelPackage.ps1#L19