coldfusioncfspreadsheet

ColdFusion: new sheet if query column values don't match

I have a query that shows results as below and it needs to export those results into excel. But instead of 1 sheet, I need multi tab excel sheets based on uniqueness of ID column

So the first sheet should be all results, 2nd spread sheet should be 1st result (as its unique), 3rd sheet should be 2 results (10011) and again 4th sheet should be 10012 and so on.

Query Results:

Customer           ID        Address        City     State     Zip

Tina Wesson        10010     XYZ Street     Omaha     TX       77004
Zeke Bradshaw      10011     ABC Street     Victoria  TX       77459
Mary Poppins       10011     CCC Street     Richmond  TX       77407
Cameron Shaw       10012     BCC Street     Houtson   TX       77001

Code:

<cfset newID = 0>
<cfloop query="#qCustomers#">
    <cfset currentID = "#qSortCombinedReadings.ID#">
    <cfif currentID NEQ newID>
       <cfspreadsheet code>
    </cfif>
    <cfset newID ="#qCustomers["ID"][qCustomers.CurrentRow]#">
</cfloop>

I tried this code and it does create 3 sheets, but it shows all records in all 3 sheets, it should show only 1 record in 1st, 2 records in 2nd and 1 record in 3rd sheet.

Solution

  • I suggest changing your logic to take in the fact that you are always going to process the first row of your query. It would look something like this:

    <cfif qCustomers.recordcount gt 0>
create a workbook
populate with the first query result
set the new ID to the existing ID

<cfif qCustomers.recordcount gt 1>
<cfloop query = "qCustomers" startrow = "2">
<cfif currentID NEQ newID>
add a new sheet to your workbook
</cfif>
populate the first empty row in the current sheet
set the new ID to the existing ID
</cfloop>
</cfif>  
</cfif>