ColdFusion Spreadsheet Bug with Formulas

A few days ago a reader asked me how to use ColdFusion to read the value of a spreadsheet cell that was defined by a value. I quickly created a spreadsheet where I used a sum formula to give the total of a number in the first two columns. ColdFusion allows you then to get both the actual value and the formula which is kinda cool. Consider:

<cfset f = expandPath("./book1.xlsx")> <cfset s = spreadsheetRead(f)> <cfset value = spreadsheetGetCellValue(s, 1,3)> <cfoutput>Value in C1 is ...#value#</cfoutput> <p> <cfset formula = spreadsheetGetCellFormula(s, 1,3)> <cfoutput>Based on formula... #formula#</cfoutput> <p>

What's cool is that the spreadsheet object is "live" - if you change the value of one one of the numbers and run spreadsheetGetCellValue again, you will see the updated result. The reader took my code and tried it on his spreadsheet but something weird happened. The exact same code (except for what row/cell he used) returns the formula for both calls! I thought perhaps it was a file issue. But when I replaced his formula with a simple Sum it worked correctly again. I have no idea why his formula 'broke' ColdFusion's getCellValue call. He has already logged a bug report for it. For those who are curious, here is the formula. It's not simple - but it seems like it should be work (note - I added a few spaces to make it wrap nicely):

=(IF($B$3>=$B16,0, MIN(((1-$B$7)*$B$6*((1+$B$11)^(((YEAR(EOMONTH($B16,0))- YEAR(EOMONTH($B$3,0)))*12)+ MONTH(EOMONTH($B16,0))- MONTH(EOMONTH($B$3,0)))-1)),$B$8))- IF($B$3>=$B16,0,MIN(((1-$B$7)*$B$6* ((1+$B$11)^(((YEAR(EOMONTH($B16,-1))- YEAR(EOMONTH($B$3,0)))*12)+ MONTH(EOMONTH($B16,-1))-MONTH(EOMONTH($B$3,0)))-1)),$B$8)))* (1/DAY(EOMONTH(B16,0)))

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate. He focuses on JavaScript, serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Comments