I've been struggling with
cfspreadsheet recently and here are a few things I learned.
At first I was adding rows to my sheets using the
SpreadsheetAddRow method, which worked fine until I met data with special characters and punctuation.
One workaround when building the list of values was surrounding them with quotes, like this :
<cfset value = listAppend(values," ' The value, look a comma, eat it ' ")>
.
Until it crashed ... I found out that a value starting with a comma was breaking the code, no idea why but I didn't want to start escaping characters from the original data, time to find another way.
That's where
SpreadsheetSetCellValue comes to the rescue :
<cfset SpreadsheetSetCellValue(sheet, anyValue, rowIndex, columnIndex)>
When you have cells with a lot of data and others with simple values, it messes the default vertical alignment, time to apply some formatting using
SpreadsheetFormatColumns :
<cfset SpreadsheetFormatColumns(sheet, {textwrap=true,verticalalignment='vertical_top'}, columns)>
It looked better, but the columns have the same width by default (very small), resulting in very tall columns, so I searched how to autoSize the columns, like you would do by double clicking between two columns, here's the solution, it requires to use an undocumented method of POI.
- Make the first sheet active, like this :
<cfset mysheet = sheet.getWorkBook().getSheetAt( javacast("int", 0) )>
- Then for each column you want to autoSize :
<cfloop from="0" to="16" index='col'>
<cfset mysheet.autoSizeColumn( javacast("int", col) )>
</cfloop>>
To prompt the user to download the sheet, I used
spreadsheetReadBinary :
<cfheader name="Content-Disposition" value="attachment;filename=Applications.xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(sheet)#" reset="true">
No more mystical error messages and now the spreadsheet looks nice when it is opened.
