Skip to main content
R
RockfordConst
Participant
August 3, 2011
Answered

spreadsheetsetcellformula

  • August 3, 2011
  • 1 reply
  • 1207 views
  • ColdFusion version:9.0.1 standard
  • server OS: Windows 2008 R2
  • webserver: Apache 2.2.19

I am having an issue with tick marks (') when using the formula function.  I am trying to set a formula in Excel that pulls information from a different tab.  In Excel it's simply:  ='10-00-In Progress'!HB10.  I would assume I can then just use the wrapper:

<cfset spreadsheetSetCellFormula(sObj, "'10-00-In Progress'!HB10", 10, #column#)>

It gives me this error:

An exception occurred while using action=update.

org.apache.poi.ss.formula.FormulaParser$FormulaParseException: Unused input [HB10] after attempting to parse the formula [#REF!HB10]
The error occurred in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\Accounting\wipNonCM.cfm: line 216


I've tried every variation of quotes and tick marks I can think of and still nothing.

Any help would be appreciated.

    This topic has been closed for replies.
    Correct answer -__cfSearching__-
    <cfset sObjt=spreadsheetNew()>

    <!--- Try to pull cell A1 from the first page created --->
    <cfset spreadsheetSetCellFormula(sObjt, "Test!A1", 1, 2)>

    Perhaps POI is complaining because the spreadsheet object does not contain a sheet named "Test" at that point, making the formula invalid.  Try creating both sheets first, then saving.

    <cfset sheet = spreadsheetNew("Test")>
    <cfset SpreadsheetAddColumn(sheet, 888, 1, 1, true)>
    <!--- create a new sheet AND activate it  --->
    <cfset SpreadsheetCreateSheet(sheet, "OtherSheet")>
    <cfset SpreadsheetSetActiveSheet(sheet, "OtherSheet")>
    <!--- Try to pull cell A1 from the first page created --->
    <cfset spreadsheetSetCellFormula(sheet, "Test!A1", 1, 2)>
    <cfheader name="Content-Disposition" value="inline; filename=test.xls"> 
    <cfcontent type="application/vnd.msexcel" variable="#SpreadSheetReadBinary(sheet)#">

    1 reply

    R
    RockfordConstAuthor
    Participant
    August 3, 2011

    If the above post isn't clear, it's easy to reproduce the problem.

    <!--- Create new spreadsheet --->
    <cfset sObj=spreadsheetNew()>

    <!--- Add some info --->
    <cfset SpreadsheetAddColumn(sObj, 888, 1, 1, true)>

    <cfspreadsheet action="update" name="sObj" filename="yourPath/Test.xls" sheetname="Test">

    <!--- Create another page in the spreadsheet --->
    <cfset sObjt=spreadsheetNew()>

    <!--- Try to pull cell A1 from the first page created --->
    <cfset spreadsheetSetCellFormula(sObjt, "Test!A1", 1, 2)>

    <cfspreadsheet action="update" name="sObjt" filename="yourPath/Test.xls" sheetname="Second">

    The same error pops up.

    -
    -__cfSearching__-Correct answer
    Inspiring
    August 3, 2011
    <cfset sObjt=spreadsheetNew()>

    <!--- Try to pull cell A1 from the first page created --->
    <cfset spreadsheetSetCellFormula(sObjt, "Test!A1", 1, 2)>

    Perhaps POI is complaining because the spreadsheet object does not contain a sheet named "Test" at that point, making the formula invalid.  Try creating both sheets first, then saving.

    <cfset sheet = spreadsheetNew("Test")>
    <cfset SpreadsheetAddColumn(sheet, 888, 1, 1, true)>
    <!--- create a new sheet AND activate it  --->
    <cfset SpreadsheetCreateSheet(sheet, "OtherSheet")>
    <cfset SpreadsheetSetActiveSheet(sheet, "OtherSheet")>
    <!--- Try to pull cell A1 from the first page created --->
    <cfset spreadsheetSetCellFormula(sheet, "Test!A1", 1, 2)>
    <cfheader name="Content-Disposition" value="inline; filename=test.xls"> 
    <cfcontent type="application/vnd.msexcel" variable="#SpreadSheetReadBinary(sheet)#">
    R
    RockfordConstAuthor
    Participant
    August 3, 2011

    I wasn't doing it the way you mentioned originally.  I tested your method and it worked great.  Instead of looping through my queries and just updating the workbook at the end, I will create all the pages in just one object.

    Thanks for your help.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices