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

cfspreadsheet / checking for tabs in a workbook

  • August 8, 2011
  • 3 replies
  • 3627 views
  • ColdFusion version:9.0.1 standard
  • server OS: Windows 2008 R2
  • webserver: Apache 2.2.19

These forums worked great once before, hopefully we can go 2 / 2.

I am opening a workbook, adding a header row to all the tabs, and then saving the workbook back to the directory as a different file.  My problem is that the workbook can have any various combination of tabs in it, and I don't know what ones unless I open the workbook itself.  Is there a way to read either the name of every tab in the workbook and then loop through those names (or the amount of sheets would work also).

Code is here:

<!--- All possible prefixes that could be in the sheet name --->
<cfset prefixList = "10-00, 10-10, 10-20, 10-30, 10-40, 10-50">
<!--- All possible status that could be in the sheet name --->
<cfset statusList = "In progress,Closed">
<cfloop list="#statusList#" index="si">
    <cfloop list="#prefixList#" index="pi">   
        <!--- Read the sheet --->
        <cfspreadsheet action="read" query="qWip" sheetName="#pi#-#si#" src="accounting/Files/wip.xls">   
     <cfset column = 1>   
     <!--- create new sheet with old information --->
  <cfset sObj = spreadsheetNew("#pi#-#si#")>   
  <cfset SpreadsheetAddRow(sObj, "Header")>
        <!--- add column header to all rows --->
        <cfloop from="#column#" to="#listLen(qWip.columnList)#" index="i">
   <cfset spreadsheetSetCellFormula(sObj, "CONCATENATE(""job""&#i#)", 1 , #i#)>
        </cfloop>
     <cfset SpreadsheetAddRows(sObj, qWip)>   
     <!--- dump excel file back into directory with new row header --->
        <cfspreadsheet action="update" name="sObj" filename="accounting/Files/wipHeader.xls">   
    </cfloop>
</cfloop>

The error comes up when a sheet does not exist in the cfspreadsheet read action.  Example:  10-10-In progress does not exist.

Any help is appreciated.

    This topic has been closed for replies.
    Correct answer -__cfSearching__-

    Unless I missed something, I think you could just read in the sheet. Then set each one as active. Make your modifications, and save it to disk.

    <cfset sheet = SpreadsheetRead("c:/path/to/wip.xls")>  
    <cfset info = SpreadSheetInfo(sheet)>


    <!--- process each sheet --->
    <cfloop from="1" to="#info.SHEETS#" index="num">
         <!--- make the sheet active --->
         <cfset SpreadsheetSetActiveSheetNumber(sheet, num)>
         ... do something with the sheet ...
    </cfloop>

    <!--- write it back to disk ...--->
    <cfset SpreadSheetWrite(sheet, "c:/path/to/NewFileName.xls", true)>

    3 replies

    C
    Carlos Elgueta
    Participant
    April 19, 2018

    Thank you very much!

    -
    -__cfSearching__-Correct answer
    Inspiring
    August 8, 2011

    Unless I missed something, I think you could just read in the sheet. Then set each one as active. Make your modifications, and save it to disk.

    <cfset sheet = SpreadsheetRead("c:/path/to/wip.xls")>  
    <cfset info = SpreadSheetInfo(sheet)>


    <!--- process each sheet --->
    <cfloop from="1" to="#info.SHEETS#" index="num">
         <!--- make the sheet active --->
         <cfset SpreadsheetSetActiveSheetNumber(sheet, num)>
         ... do something with the sheet ...
    </cfloop>

    <!--- write it back to disk ...--->
    <cfset SpreadSheetWrite(sheet, "c:/path/to/NewFileName.xls", true)>

    R
    RockfordConstAuthor
    Participant
    August 8, 2011

    Twice now you've helped cfsearching.  I guess I need to find a listing of all the new spreadsheet methods that exist so I quit wasting your time.

    Thanks again, you've made my day.

    -
    -__cfSearching__-
    Inspiring
    August 8, 2011
    a listing of all the new spreadsheet methods that exist

    Try the online documentation ;-)

    http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec1fb12-7fd6.html

    existdissolve
    existdissolve
    Inspiring
    August 8, 2011

    It seems like you could do this pretty easily by tapping into the methods for the POI HSSFWorkbook:

    <cfset workbook  = myspreadsheetobject.getworkbook()>

    You can get the number of sheets in your workbook object like so:

    <cfset numsheets = workbook.getNumberOfSheets()>

    And check for the existence of a sheet by checking:

    <cfset thesheet = workbook.getSheet(name or index)>

    You'll obviously have to work these into your logic, but I think they're a good place to start. And of course, be sure to dump out the workbook object above--you'll see all the methods available (there may be some in there that are more useful than the ones I highlighted...i only briefly looked at it)

    Hope that helps.

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