How to add particular columns of a query to spreadsheet object in Coldfusion

Question

I have a query object which contains 5 columns, but I only need to add 4 columns to the spreadsheet. When I am using <cfset spreadSheetAddRows(spreadSheetObj,qryObj)> it is adding all the columns to the spreadsheet. I cannot remove the 5th column from the query as it is required for some other purpose.

So How can I add only 4 columns from the query to spreadsheet?

Ex: My query contains 4 columns like "Id,Name,Roll,CGPA"

But my Excel sheet should contain only "Id,Name,Roll".

How to do this?

Answer 1

Just add each row individually with SpreadsheetAddRow(spreadsheetObj, data [,row, column, insert]);

And for columns use SpreadsheetAddColumn(SpreadsheetObj, data[, startRow, startColumn, insert]);

• SpreadsheetAddRow Documentation
• SpreadsheetAddColumn Documentation

Answer 2

You could do a Query-of-Query to create a temporary query that you can then use to pump into your spreadSheetAddRows().

<cfquery name="local.spreadsheetQuery" dbtype="query">
SELECT Id,Name,Roll
FROM variables.originalQuery
</cfquery>

Then use <cfset spreadSheetAddRows(spreadSheetObj,local.spreadsheetQuery)> instead of <cfset spreadSheetAddRows(spreadSheetObj,variables.originalQuery)>.