How to format spreadsheet columns using ColdFusion?

Question

I am using SpreadsheetFormatColumns() to format the columns in a spreadsheet to "text", but I don't know how to do this, all the formats in the livedocs are for numbers, currency or dates... is there something like

SpreadsheetFormatColumns(mySpreadsheet, {dataFormat="text"}, "1-15")

out there? this is really bugging me...

Thanks

Answer 1

In ColdFusion 9.0.1 (i.e. updater 1), if you use SpreadsheetSetCellValue() it will respect the format you have previously set. So to force a column to be text when populating a sheet you can use a 3-step process:

1. Populate the spreadsheet, ignoring the incorrectly interpreted number values.
2. Format the column you want as text.
3. Replace the incorrect value in each row of the column with the correct value, which will now be treated as text.

Here's an example which you can copy into a .cfm and run as-is (requires CF9.0.1)

<cfscript>
    // Create a 2 column, 2 row query. The first column contains numbers or possible numbers we want formatted as text in our spreadsheet
    q   =   QueryNew( "" );
    QueryAddColumn( q,"NumbersAsText","VarChar",[ 01050094071094340000,"743059E6" ] );
    QueryAddColumn( q,"Text","VarChar",[ "abc","def" ] );
    // Get the column names as an array so we can get at them more easily later
    columns =   q.getMetaData().getColumnLabels();
    // Create a new spreadsheet object
    sheet   =   SpreadSheetNew( "test" );
    // specify the column we want formatted as text
    forceTextColumnNumber   =   1;
    // Use the query column names as column headers in our sheet
    SpreadSheetAddRow( sheet,q.columnList );
    // Add the data: the numbers will be inserted as numeric for now
    SpreadSheetAddRows( sheet,q );
    // Now we format the column as text
    SpreadSheetFormatColumn( sheet,{ dataformat="text" },forceTextColumnNumber );
    // Having formatted the column, add the column from our query again so the values correct
    while( q.next() )
    {
        // Skip the header row by adding one
        rownumber   =   ( q.currentrow + 1 );
        // Get the value of column at the current row in the loop
        value   =   q[ columns[ forceTextColumnNumber ] ][ q.currentrow ];
        // replace the previously added numeric value which will now be treated as text
        SpreadsheetSetCellValue( sheet,value,rownumber,forceTextColumnNumber );
    }
    // Download the object as a file
    sheetAsBinary   =   SpreadSheetReadBinary( sheet );
    filename    =   "test.xls";
</cfscript>
<cfheader name="Content-Disposition" value="attachment; filename=#Chr(34)##filename##Chr(34)#">
<cfcontent type="application/msexcel" variable="#sheetAsBinary#" reset="true">

By default, both of the values in the first column of my query would be treated as numbers (the second as a HEX). Using this method both preserve their original value as text.