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

SpreadsheetSetCellFormula with the function Hyperlink has a strange behavior

Explorer ,
Oct 03, 2017 Oct 03, 2017

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

Copie1.jpg

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.

Copie2.jpg

any ideas ?

I don't understand.

Thank you for you help

Best regards

Andre

Views

818

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

correct answers 1 Correct answer

Explorer , Oct 05, 2017 Oct 05, 2017

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

Votes

Translate

Translate
Community Expert ,
Oct 04, 2017 Oct 04, 2017

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.

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 ,
Oct 05, 2017 Oct 05, 2017

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

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 ,
Oct 05, 2017 Oct 05, 2017

Copy link to clipboard

Copied

Thanks. Please mark your answer as the correct one.

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
New Here ,
Feb 08, 2022 Feb 08, 2022

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!!!!!

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 08, 2022 Feb 08, 2022

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#")>

 

 

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 10, 2022 Feb 10, 2022

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.

BKBK_0-1644495819588.png

BKBK_0-1644496081410.png

 

 

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 10, 2022 Feb 10, 2022

Copy link to clipboard

Copied

Additional information about my test:

Excel 2007 with Calculation Options set to Automatic

BKBK_1-1644496686117.png

 

 

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

Ok, So why is yours correct and the poster's isn't?

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

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.

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 13, 2022 Feb 13, 2022

Copy link to clipboard

Copied

Yup, Auto calc is set on My excel.  Same results... 0 in all cells

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

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