TryCF Gist

trycf-gist.cfm

<cfscript>
    qReportData = queryNew("Name, Age",
        "varchar, integer",
        [
            {name: "Tom", age: 25},
            {name: "Dick", age: 40},
            {name: "Harry", age: 55}
        ]
    );

    qSheetOutput = queryNew("Name, Age");
    dataRows = [];
    messageRows = [];
    for(row in qReportData){
        queryAddRow(qSheetOutput, {
            name: row.name,
            age: row.age
        });
        arrayAppend(dataRows, qSheetOutput.recordCount + 1);
        if(row.age > 40){
            queryAddRow(qSheetOutput, {
                name: row.name & " is over 40"
            });
            arrayAppend(messageRows, qSheetOutput.recordCount + 1);
        }
    }
    sheet = SpreadsheetNew(false);

    //Add and format headers
    spreadsheetAddRow(sheet, "Name, Age");
    bold = {bold: true};
    spreadsheetFormatRow(sheet, bold, 1);

    //Write sheetdata
    spreadsheetAddRows(sheet, qSheetOutput);

    //Format by looking at the data
    for(row in qSheetOutput){
        //Format message row
        if(!len(row.age)){
            Spreadsheetformatcell(sheet, {dataformat="@", alignment="center"}, qSheetOutput.currentRow + 1, 1);
            Spreadsheetmergecells(sheet, qSheetOutput.currentRow + 1, qSheetOutput.currentRow + 1, 1, 2);
        }
        else{
            //Bold the person name
            spreadsheetFormatCell(sheet, bold, qSheetOutput.currentRow + 1, 1 );
        }
    }
    //Another way to format the rows
    for(rowNumber in dataRows){
        spreadsheetFormatCell( sheet, bold, rowNumber, 1 );
    }
    for(rowNumber in messageRows){
        Spreadsheetformatcell(sheet, {dataformat="@", alignment="center"}, rowNumber, 1);
        Spreadsheetmergecells(sheet, rowNumber, rowNumber, 1, 2);
    }
</cfscript>
<cfheader
    name="Content-Disposition"
    value='inline; fileName="test.xls"'>
<cfcontent
    type="application/vnd.ms-excel"
    variable="#spreadSheetReadBinary(sheet)#">
    type="application/vnd.ms-excel"
    variable="#spreadSheetReadBinary(sheet)#" >