How to add a sheet?

[AlHolden]

Our current ACF code allows for us to

1. Load a blank or "source" spreadsheet file
2. Remove one of the sheets
3. Add a new sheet back, populated with a query
4. Hide the new sheet
5. Save the file, download to user

(FYI the source file is provided by Excel gurus, and will take values from the straight, boring, hidden sheet I added - and populate the pretty stuff in the visible sheets)

In attempting to reproduce these steps under this library (to compare speed), I've hit a roadblock or dumb moment trying to find the function to add another sheet to an existing spreadsheet object. In ACF we use the cfspreadsheet tag with the "update" action attribute.

Am I missing the correct function to do this? Does one exist?
Thanks!

[AlHolden]

Slapping head... I think it's just a syntax quirk.

Among a list with all the "add" functions, the one I'm probably looking for is called createSheet.

I'm sure someone else obsessed with taxonomy would complain about this...

[cfsimplicity]

Hi Al.

I'm sure someone else obsessed with taxonomy would complain about this...

Maybe, but I don't think they would be justified in doing so. The method is called createSheet() because:

1. It creates a new sheet object from scratch (to me adding suggests inserting an existing object)
2. That's what ACF calls its equivalent function: https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-s/spreadsheetcreatesheet.html
3. That's what the underlying Apache POI java method is called: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Workbook.html#createSheet--

[cfsimplicity]

What I do find dubious in terms of naming is why anyone would intuitively think <cfspreadsheet action="update" filename="#theFile#" name="theSecondSheet" sheetname="centers"> creates a new sheet. There's no create or add in there at all!

[AlHolden]

What I do find dubious in terms of naming

Totally agree.. It probably took a while to figure that one out. Our legacy code is a mess, I can see Adobe bug links in the code's comments; processes where they needed to basically save the object, turn around and re-load it again, in order to get a fresh sheet count.
I've been tasked with finding better ways to optimize this - and I think I found your library via CFBreak or similar...

[cfsimplicity]

@AlHolden Incidentally, how are you currently hiding your newly created (added!) sheet in ACF? I'm not aware of an ACF function that will do that.

It is actually possible with Spreadsheet CFML using an undocumented helper function but this makes me think we should add it as a documented feature.

See new issue: #394

[AlHolden]

I think I discovered the process via this SO thread here.

<cfset workingFile2 = SpreadsheetRead(destinationPath) />
<cfset fileInfo2 = SpreadSheetInfo(workingFile2) />
<cfset poiObj= workingFile2.getWorkBook() />

<cfset sheetIndex = poiObj.getSheetIndex("Export") />
<cfif sheetIndex>
	<cfset poiObj.setSheetHidden(javacast("int", sheetIndex), javacast("boolean", true)) />
</cfif>

[cfsimplicity]

OK, I assumed you must be interacting directly with POI methods to do that.

I'm going to add a new hideSheet() method to the library so you won't need to anymore. Should be ready in the next couple of days.

[cfsimplicity]

Your workflow could then look something like this:

<cfscript>
spreadsheet = new spreadsheet.Spreadsheet() // instantiate the library
workbook = spreadsheet.read( filepath )
spreadsheet.removeSheet( workbook, "Unneeded sheet" )
  .createSheet( workbook, "Export" )
  .setActiveSheet( workbook, "Export" )
  .addRows( workbook, data )
  .hideSheet( workbook, "Export" )
  .download( workbook, "downloaded" )
</cfscript>

or if you prefer tags

<cfset spreadsheet = new spreadsheet.Spreadsheet()>
<cfset workbook = spreadsheet.read( filepath )>
<cfset spreadsheet.removeSheet( workbook, "Unneeded sheet" )
  .createSheet( workbook, "Export" )
  .setActiveSheet( workbook, "Export" )
  .addRows( workbook, data )
  .hideSheet( workbook, "Export" )
  .download( workbook, "downloaded" )>

but simplest of all with a chainable object:

spreadsheet.newChainable()
  .read( filepath )
  .removeSheet(  "Unneeded sheet" )
  .createSheet( "Export" )
  .setActiveSheet( "Export" )
  .addRows( data )
  .hideSheet( "Export" )
  .download( "downloaded" )

[cfsimplicity]

@AlHolden The latest release now has hideSheet().

If you decide to use the library, please do let me know how you get on.