Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-03-21
10
564 Views
Last Modified: 2014-03-21
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
Comment
Question by:DJPr0
  • 5
  • 4
10 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 39945709
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
 

Author Comment

by:DJPr0
ID: 39945825
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
 
LVL 16

Expert Comment

by:Gurpreet Singh Randhawa
ID: 39945871
That is still unclear, can you show some actual code where we can try to figure what output you need
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39945879
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
 

Author Comment

by:DJPr0
ID: 39945929
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
 
LVL 52

Expert Comment

by:_agx_
ID: 39945965
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
 

Author Comment

by:DJPr0
ID: 39945974
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 39945993
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
 

Author Closing Comment

by:DJPr0
ID: 39946037
Thanks _agx_!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39946068
Welcome :)
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Fixing some Issues with regex again 9 39
Airline check in kiosk 4 112
cfgrid format issue and columns missing 4 108
Printing to a specific printer tray (HP 806dn printer) 3 111
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question