tracker issue : CF-4206915

select a category, or use search below
(searches all categories and all time range)
Title:

SpreadsheetGetValue returns a "-"

| View in Tracker

Status/Resolution/Reason: To Track//NeedMoreInfo

Reporter/Name(from Bugbase): Mike F. / ()

Created: 01/29/2020

Components: Document Management, Spreadsheet

Versions: 2018

Failure Type: Incorrectly functioning

Found In Build/Fixed In Build: 2018.0.06.316308 /

Priority/Frequency: Normal / All users will encounter

Locale/System: English / Win 2016

Vote Count: 0

Problem Description: the spreadsheetgetvalue returns a '-' when I call it.  the cell has a formula which displays a number.  When I call spreadsheetgetformula it works correctly.  When I run the same code and same file on CF2016 it works correctly.

Steps to Reproduce: 

Actual Result:  returns  - 

Expected Result: It should return a numerical value as a string

Any Workarounds: None

Attachments:

Comments:

Mike, Can you pls. share the formula or the spreadsheet and the test cfm script that you are using . I don't so the issue on CF 2018 update 7. I'm attaching the test script and the spreadsheet I used.
Comment by Piyush K.
33109 | February 13, 2020 06:45:59 AM GMT
Mike, can you pls provide the input requested in my previous comment
Comment by Piyush K.
33176 | February 25, 2020 11:18:04 AM GMT
We are currently at update 6. Maybe updating to 7 might help. I found a work around so it is functioning correctly. I am unable to send the file because it has financial information. The formula that we us is =SUM(B10:B13). Here is a snippet of code I use <cffile action="UPLOAD" filefield="form.newFileName1" destination="#variables.dirpath#\" nameconflict="overwrite"> <cfset theFile="#variables.dirpath##GetFileFromPath(form.filename1)#"> <cfspreadsheet action="read" src="#theFile#" name="sObj2" sheetname="Project Summary"> I loop through the rows and columns. <cfset fin_array[variables.inc][1] =SpreadsheetGetCellValue(sObj2,variables.rowcount,1)>
Comment by Mike F.
33189 | February 25, 2020 12:24:35 PM GMT
Thanks Mike, the methods return the correct formula as well the value for me on update 6 and 7, with that formula. Can you share the values contained in cells B10 through B13, as well.
Comment by Piyush K.
33196 | February 26, 2020 08:46:38 AM GMT
Mike, can you pls share the info requested in my last note.
Comment by Piyush K.
33257 | March 06, 2020 10:02:28 AM GMT
Here are the values but they are generated by formulas. 10,874,130 - =B15+B20+B25+B30+B35+B40+B45+B55+B50 8,238,630 - =B16+B21+B26+B31+B36+B41+B46+B56+B51 2,961,370 - =B17+B22+B27+B32+B37+B42+B47+B57+B52 45,730 - =B18+B23+B28+B33+B38+B43+B48+B58+B53 Each value for B15+B20+B25+B30+B35+B40+B45+B55+B50 are actually a look up to another tab's cell location Let me know if you need more clarification.
Comment by Mike F.
33258 | March 06, 2020 12:24:18 PM GMT
Mike, I still don't see the issue. I'm attaching the test excel sheets with cells referencing data from another sheet. If possible please narrow it down at your end and share an excel file an cfm test code with which you can observe the issue, <cfscript> writeOutput(server.coldfusion.productversion & "<br>") theSheet=SpreadsheetNew(); sobj = spreadSheetRead("#expandpath('./')#SingleSheet2.xlsx") writeOutput(SpreadsheetGetCellFormula(sobj,5,3) & "<br>") writeOutput(SpreadsheetGetCellValue(sobj,5,3) & "<br>") variables.rowcount = 5 writeOutput(SpreadsheetGetCellFormula(sobj,variables.rowcount,3) & "<br>") writeOutput(SpreadsheetGetCellValue(sobj,variables.rowcount,3) & "<br>") </cfscript> outputs: 2018,0,07,316715 (B15+B16+B17+B18+B19+B20+B21+B22) 10809 (B15+B16+B17+B18+B19+B20+B21+B22) 10809
Comment by Piyush K.
33295 | March 16, 2020 04:55:12 AM GMT