Solved

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

Posted on 2014-03-21
10
569 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
[X]
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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses

734 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