Using Coldfusion cfspreadsheet to format spreadsheet page properties

Question

I would like to format the spreadsheet as below but couldn't find the proper functions to do it with cfspreadsheet or independent spreadsheet() functions.

• set page orientation to either portrait or landscape
• set page margin

Please advise!

Answer 1

Last I checked, there were no built-in functions for manipulating margins or orientation. You need to tap into the underlying POI functions.

Create a CF spreadsheet object, then grab a reference to the underlying POI worksheet (whose properties you want to change):

<cfset cfSheetObject = SpreadSheetNew()>
<cfset poiSheet = cfSheetObject.getWorkBook().getSheet("TheSheetName")>

Orientation can be changed through the sheet's print properties:

<cfset ps = poiSheet.getPrintSetup()>
<cfset ps.setLandscape(true)>

Use setMargin(margin, size) to change the desired margin(s): LeftMargin, RightMargin, etcetera... For a complete list of margin names, see the field names in the Sheet interface.

<cfset poiSheet.setMargin( poiSheet.LeftMargin, 0.25)>