• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

CF 2021- Excel .xlsx NO LONGER Autocalculates

Explorer ,
Feb 10, 2022 Feb 10, 2022

Copy link to clipboard

Copied

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]

jedifan_0-1644502561886.png

 

my "xls" file upon opening

jedifan_1-1644502676242.png

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

Views

130

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 10, 2022 Feb 10, 2022

Copy link to clipboard

Copied

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/



Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 11, 2022 Feb 11, 2022

Copy link to clipboard

Copied

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.   

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 12, 2022 Feb 12, 2022

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 13, 2022 Feb 13, 2022

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 14, 2022 Feb 14, 2022

Copy link to clipboard

Copied

LATEST

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation