CF 2021- Excel .xlsx NO LONGER Autocalculates

[jedifan]

VERY IMPORTANT:  This code ran flawlessly on CF v10 (same master excel file "xlsx")

MUST Find solution. 

My Master excel is "xlsx".   Going back to "xls" is NOT option as we're using advanced features not backward compatible.

To simulate workflow I used Excel to create a blank "xls" and "xlsx" both with "sheet1" renamed to "interview".

The following code sets the cells correctly... BUT "xls" will autocalc when opened, "xlsx" WILL NOT.  User must hit [cntrl-alt-F9] which then updates.

Please try yourself, you will see same results.

 (simply switch lines 1/2 to use different "master" in your simulation.

<cfset excelFileToUse = "d:\oldExcel.xls">
<cfset excelFileToUse = "d:\testMaster.xlsx">

<cfset dummyNumber = randrange(12345,99999)>

<cfset fileExt = listLast(excelFileToUse,".")>

<cfset CompanyLink = "HYPERLINK(""http://localhost/customers/customer.cfm?customerID=156"",""My Company"")">
<cfset contactLink = "HYPERLINK(""http://localhost//customers/customer.cfm?customerID=156"",""Johnny Walker"")">
<cfset quoteLink = "HYPERLINK(""http://localhost/quotes/oldquote.cfm?quoteID=15263"",""15626"")">

<!--- Read QUOTE Sheet --->

<cfset SpreadsheetObj = SpreadSheetRead(excelFileToUse)>

<cfset SpreadsheetSetActiveSheet(SpreadsheetObj, "interview")>

<!--- hard coded param rather than CF variable, NO DIFFERENCE in outcome --->

<cfset SpreadsheetSetCellFormula(SpreadsheetObj, 'HYPERLINK("http://localhost/customers/customer.cfm?customerID=156","My Company")', 1,2)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, contactLink, 2,2)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, quoteLink, 3,2)>

<!--- Basic math test --->

<cfset SpreadsheetSetCellFormula(SpreadsheetObj, '134+442', 1,3)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, "154*342", 2,3)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, "14/62", 3,3)>

<cfset SpreadsheetSetColumnWidth(SpreadsheetObj, 2, 24)>
<cfset SpreadsheetSetColumnWidth(SpreadsheetObj, 3, 24)>

<!--- set active sheet again in effort to troubleshoot, no difference if this line is in code or not --->

<cfset SpreadsheetSetActiveSheet(SpreadsheetObj, "interview")>

<cfset spreadsheetwrite(SpreadsheetObj, "d:\temp\#dummyNumber #.#fileExt#")>

This is my xlsx file upon opening....

NOTE:  cells DO have formula and will calculate corrrectly using [cntl-alt-F9]

my "xls" file upon opening

Altready tried adding this to test code.

<cfset obj=SpreadsheetObj.getWorkBook()>
<cfset obj.getCreationHelper().createFormulaEvaluator().evaluateAll()>

This DID solve problem on this simple excel file.  However it does not work on actual file as we have lots of formulas and sheet lookups.

SOMEBODY PLEASE HELP

[JeffCoughlin]

A couple thoughts:

• Could maybe setting the datatype first make a difference (I doubt it will help, but just wondering)?
• Did you set the Excel doc to use automatic recalculation under the formulas ribbon?
  • On the Formulas ribbon, look to the far right and click Calculation Options. On the dropdown list, verify that Automatic is selected.
• If nothing else helps and you still think this is a bug, you might have to submit a ticket to the CF dev team. Note: This person appears to be having the same issue as yourself (unless it is you as well just cross-posting) https://coldfusion.adobe.com/2022/01/spreadsheetsetcellformula-not-honored-excel/

[BKBK]

Hi @jedifan ,

Please note that, strictly speaking, the following lines may NOT actually set a formula:

<cfset SpreadsheetSetCellFormula(SpreadsheetObj, '134+442', 1,3)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, "154*342", 2,3)>
<cfset SpreadsheetSetCellFormula(SpreadsheetObj, "14/62", 3,3)>

I say that because Excel may interpret 134+442, 154*342 and 14/62 as constants, rather than as formulas. In fact, Microsoft's Overview of formulas in Excel says the following:

"A constant is a value that is not calculated; it always stays the same. For example, the date 10/9/2008, the number 210, and the text "Quarterly Earnings" are all constants. An expression or a value resulting from an expression is not a constant. If you use constants in a formula instead of references to cells (for example, =30+70+110), the result changes only if you modify the formula. In general, it's best to place constants in individual cells where they can be easily changed if needed, then reference those cells in formulas."

This can only be a good thing. Otherwise, Excel might evaluate 11/2/2022 as 11 divided by 2 divided by 2022, rather than as the date of today.   

[jedifan]

Thanks, but I'm unconcerned about the results of the "simple" match cells.  I only added them to see if Excel calculated anything.  The main focus is ALL cells net a "0" display in Excel until I manually [cntrl-alt-f9].  Again.  This was all working PERFECTLY for years under CF 10.  I upgraded to 2021 and now it does NOT work.  Also to reitierate, the code offered of some "off-book" java function ONLY worked on this simple sheet.  It does NOT work on the actual production sheet as there are MANY cross-sheet references and lookups.  STILL NEED SOLUTION OR EXPLANATION WHY THIS NO LONGER WORKS IN 2021

[BKBK]

The main focus is ALL cells net a "0" display in Excel until I manually [cntrl-alt-f9].  Again.  This was all working PERFECTLY for years under CF 10.  I upgraded to 2021 and now it does NOT work.  Also to reitierate, the code offered of some "off-book" java function ONLY worked on this simple sheet.  It does NOT work on the actual production sheet as there are MANY cross-sheet references and lookups.  STILL NEED SOLUTION OR EXPLANATION WHY THIS NO LONGER WORKS IN 2021

By @jedifan

Understandable. Afterall ColdFusion promises backward-compatibility.

Reason enough for you to create a bug ticket.

[George=A]

Take a look at the use of setForceFormulaRecalculations(true) in these links.

https://community.adobe.com/t5/coldfusion-discussions/poi-excel-workbook-chart-text-class-textrecord...

https://matthewbusche.com/2017/02/26/coldfusion-and-apache-poi-modifying-an-existing-file/

https://poi.apache.org/components/spreadsheet/eval.html