The martian - book by Andy Weir

I just finished reading this book, and I have to say it's fantastic, if you like creative science and thriller, go for it !!!!



http://www.andyweirauthor.com/books/the-martian-hc

CFSpreadsheet in coldfusion 10 - what I learned today

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.