Copy link to clipboard
Copied
Hello everybody
I don't know if you can help me but:
I create an excel document using the instructions "spreadsheet" for ColdFusion 10.
There is, among the data to be added, a "hyperlink" formula that will contain the URL of a website
I therefore use the instruction "SpreadsheetSetCellFormula"
See the code:
<cfset SpreadsheetSetCellFormula(s, 'HYPERLINK("#Query.Field1#", "#qQuery.Field1#")', curRow, curCol)>
Explanation of different variables:
s : object representing the excel sheet
Query.Field1 : it is the data containing the URL (in a Query)
curRow : row number where the data will be write
curCol : Column number
The problem:
When I execute the code on my computer and I open the file: the display is correct
When my colleague tries to create the file with the same code, he receives, not the internet link, but the number "0" is displayed in blue colour but and no formulas.
any ideas ?
I don't understand.
Thank you for you help
Best regards
Andre
Hello,
I would like to thank you for your help.
About the name (query and Query), it is false name just for example.
The problem is (If I understood correctly) when it opens the document, it has not clicked on the "enable content" button.
This morning everything seems to work fine.
So thank you
Copy link to clipboard
Copied
You call the query Query, then qQuery. That is confusing. I would suggest the following test for your colleague:
<cfset SpreadsheetSetCellFormula(s, 'HYPERLINK("http://www.google.com", "http://www.google.com")', 76, 1)>
If this works, then the problem lies with your code and not with the ColdFusion engine.
Copy link to clipboard
Copied
Hello,
I would like to thank you for your help.
About the name (query and Query), it is false name just for example.
The problem is (If I understood correctly) when it opens the document, it has not clicked on the "enable content" button.
This morning everything seems to work fine.
So thank you
Copy link to clipboard
Copied
Thanks. Please mark your answer as the correct one.
Copy link to clipboard
Copied
SAME ISSUE: CF server 2021 Release.
VERY IMPORTANT: This code ran flawlessly on CF v10 (same 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#")>
SOMEBODY PLEASE HELP... THIS IS FRUSTATION TO THE 38TH POWER!!!!!
Copy link to clipboard
Copied
How is this?
<cfset SpreadsheetSetActiveSheet(SpreadsheetObj, "interview")>
<cfset obj=SpreadsheetObj.getWorkBook()>
<cfset obj.getCreationHelper().createFormulaEvaluator().evaluateAll()>
<cfset spreadsheetwrite(SpreadsheetObj, "d:\temp\#dummyNumber #.#fileExt#")>
Copy link to clipboard
Copied
Hi @shari.miller ,
I don't think that your issue is the same as the one originally reported here. In the original issue, there was 0 in place of a link. Whereas, in your case, a calculation isn't done.
It might help if you could start a new thread of your own. Just copy the above message into it.
In any case, when I ran your test, the contents of the resulting xls and xlsx files were the same. 🙂
As you can see, the calculations have been done in both cases.
Copy link to clipboard
Copied
Additional information about my test:
Excel 2007 with Calculation Options set to Automatic
Copy link to clipboard
Copied
Ok, So why is yours correct and the poster's isn't?
Copy link to clipboard
Copied
Ok, So why is yours correct and the poster's isn't?
By @jedifan
I just said. 🙂
Not as the cause, more as a possible explanation: I opened my test file "with Calculation Options set to Automatic" in Excel.
Copy link to clipboard
Copied
Yup, Auto calc is set on My excel. Same results... 0 in all cells
Copy link to clipboard
Copied
Ah, OK.
The discussion has moved to the thread https://community.adobe.com/t5/coldfusion-discussions/cf-2021-excel-xlsx-no-longer-autocalculates/td...