Solved

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

Posted on 2014-03-21
10
559 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 15

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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now