Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-21
10
Medium Priority
?
580 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month8 days, 4 hours left to enroll

824 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