Search code examples
coldfusionorientationmarginspreadsheetcfspreadsheet

Using Coldfusion cfspreadsheet to format spreadsheet page properties

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!

Solution

  • 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)>