CODEWITHSUNDEEP
coldfusionspreadsheet
airpolgas
airpolgas

Reputation: 77

How can I change the value of a cfquery result before exporting to Excel?

I already have a working code to export a CFQuery into an Excel file. However, I can't seem to figure out how to change the result before being written to the spreadsheet. The RSVP column has Y or N as its value. When I export the data, I wish for the cell to say Accept or Decline instead.

Is there a way to do that? Thank you.

Here is my current code:

<cfscript>
    // Get data from a query
    Query=QueryExecute("SELECT NAME, RSVP FROM TABLE",[],{datasource="DBASE"});

    // Create a spreadsheet object with sheetname
    spObj=spreadsheetNew("Query",true);

    // Add rows with data from query result. The data start from row 1, col 1. The spreadsheet will have column names.
    SpreadSheetAddRow(spObj, 'NAME,RSVP');

    //Make the header bold
    SpreadsheetformatRow(spObj,{bold=true},1);

    //Create the Spreadsheet
    SpreadSheetAddRows(spObj,Query,2,1,true,["STRING"],false);
</cfscript>

Upvotes: 0

Views: 98

Answers (1)

TRose
TRose

Reputation: 1738

Adding this as an answer. In SQL you'll want to return "Accept" or "Decline" (or whatever text you fancy) using a CASE statement.

In your case it'll probably look a lot like this:

SELECT
  [Name],
  CASE WHEN [RSVP] LIKE 'Y' THEN 'ACCEPT' ELSE 'DECLINE' END AS Response
FROM
  [TableName]

Upvotes: 3

Related Questions