Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

ColdFusion How can I control which fields are outputting to my excel export function?

Some queries result in displaying or not displaying fields. This is easy to achieve in CF web output with the <cfif statement.

How can I restrict field output to my Excel spreadsheet download function utilizing CF spreadsheet commands?
0
DJPr0
Asked:
DJPr0
  • 5
  • 4
2 Solutions
 
_agx_Commented:
All depends on the values and desired output.  Can you provide an example? Say you're dumping the results of a query:

QueryData
Row # |  TextColumn  | NumericColumn
1        |  AAAA           |  NULL
2        |  BBBB            |  15
3        |  NULL            |  22.50
4        |  NULL            |  NULL

What's the desired output in the spreadsheet and why?
0
 
DJPr0Author Commented:
Here is piece of code for my web output results:

 <cfif discipline1 eq "ALL">
           <td class="rdata">#SHIP#</td>
 </cfif>

If a user selects a Ship the column will not be displayed.

If a ship is not selected in the drop down, then "ALL" will trigger to output the ship column.

The desired output is for the Excel output to reflect the web output.
0
 
Gurpreet Singh RandhawaWeb DeveloperCommented:
That is still unclear, can you show some actual code where we can try to figure what output you need
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
_agx_Commented:
Assuming you're dumping the results to a spreadsheet from a query using:

         SpreadSheetAddRows(theSheet, someQuery, ...)>
 
... then you need to modify your query to exclude that column in SQL. There's no way to skip a column using SpreadSheetAddRows(). It must be excluded from the query results:

SELECT   OtherColumns
             <!--- include the "SHIP" column in the results --->  
             <cfif discipline1 eq "ALL">
                , TheShipColumnName
             </cfif>
FROM    SomeTable
WHERE  ....

Another option is to leave the SQL as is, and delete the entire column from the spreadsheet after the fact using SpreadSheetDeleteColumn. Note, that will delete all data in the spreadsheet column (headers, etc...) - not just the values added by the query.
0
 
DJPr0Author Commented:
I.E. After installing the above code I shouldn't need the code for my output results:
 <cfif discipline1 eq "ALL">
           <td class="rdata">#SHIP#</td>
 </cfif>

Due to it's being taken care of in the select statement?


If I go via the  SpreadSheetDeleteColumn route - how would I trigger within the Excel export code?
0
 
_agx_Commented:
After you've added all of the data, headers, etc... to the spreadsheet, just delete the "SHIP" column number. The save/download as usual.  If ship is column 3:

<cfset SpreadsheetDeleteColumn(theSheet, 3)>
0
 
DJPr0Author Commented:
How do I trigger it?

If a user selects a ship - delete!
<cfset SpreadsheetDeleteColumn(theSheet, 3)>

If a user doesn't select a ship - don't delete!
<cfset SpreadsheetDeleteColumn(theSheet, 3)>   How can I stop this from running?
0
 
_agx_Commented:
Same way you triggered the original, just use NEQ. If the filter is *not* "ALL", assume a ship was selected and delete the column:

<cfif discipline1 NEQ "ALL">
      <cfset SpreadsheetDeleteColumn(theSheet, 3)>
 </cfif>
0
 
DJPr0Author Commented:
Thanks _agx_!
0
 
_agx_Commented:
Welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now