2

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?

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
user3427540
  • 1,162
  • 1
  • 14
  • 30

2 Answers2

0

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

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

Cory Fail
  • 1,070
  • 8
  • 26
  • Just check my example exactly what i want. – user3427540 Apr 11 '14 at 15:15
  • Your answer is no way related to my question. – user3427540 Apr 11 '14 at 15:52
  • 1
    Neither of those are "great" options as they require a "comma delimited list of cell entries", requiring hacks if the data contains commas. Unfortunately, there is no one-line-solution. All of the options have drawbacks, but personally I think the others are cleaner. – Leigh Apr 11 '14 at 16:21
0

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)>.

Fish Below the Ice
  • 1,273
  • 13
  • 23
  • There is no direct function to do this? – user3427540 Apr 11 '14 at 15:32
  • This isn't a very good way to accomplish the task. There is no need for a second query. – Cory Fail Apr 11 '14 at 15:52
  • *There is no direct function to do this?* No. The options are a) Loop and add the cell values manually b) create an intermediary query containing only the columns needed OR c) add all columns, then [delete the extra(s)](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSe9cbe5cf462523a0-660681012216ab44a6-7fff.html). Each option has pros and cons. – Leigh Apr 11 '14 at 16:03
  • Actually, it's potentially a very efficient way to accomplish the task. Once you do the q of q, it's a simple – Dan Bracuk Apr 11 '14 at 16:27
  • I stand corrected. I was always under the impression that the QoQ would slow the process down, depending on the size of the spreadsheet. – Cory Fail Apr 11 '14 at 16:33
  • 1
    In terms of what happens under the hood, it's probably less efficient. The q of q has to execute and the results take up more memory. However, the increased simplicity of the code might make it worthwhile. – Dan Bracuk Apr 12 '14 at 14:43