Create workbook with multiple sheets from Queries

Question

I am using ColdFusion 2016 and I've discovered that the server that I'm using only has CF9, I'm pretty new to this. I've been working on updating existing code to fit what the users now want. So far I've managed, but this is beyond me. I've got a website that generates an excel workbook with one sheet. It uses HTML and a query to create it. Starts with the query name in A1 the report date in A3, the table headers in A5:H5 and then the data in A6:H53 (The exact length could vary, but always from column A - H). I'll post what is being used to create the workbook. What i want to do is use 3 more queries to add 3 more sheets to the workbook. I've tried adding a function that I found here and that didn't do any good. I tried modifying the existing code some to try and use all 4 queries, no joy.

Any help would be appreciated. Let me know if I need to add more detail. Here's the code:(I added comments for what I added trying to get this to work)

<cfsilent>
<!--- *******************************************************************
Filename:   execSummary_Excel.cfm, v1.0 03/07/2012
Created By: Original Writer
Description:    Excel report export for Executive Summary Report.       
Change History:
Date........Name...........Description of Change........................
08/01/2012  Original Writer Added committed column.
02/28/2013  Original Writer Added stateGM and GM.
*************************************************************************--->
<cfinvoke component="financial.financial" method="getExecSummary" returnvariable="qExecSummary">
    <cfinvokeargument name="level" value="#URL.level#" />
    <cfinvokeargument name="stateGM" value="#URL.stateGM#" />
    <cfinvokeargument name="GM" value="#URL.GM#" />
    <cfinvokeargument name="engVP" value="#URL.engVP#" />
    <cfinvokeargument name="engDir" value="#URL.engDir#" />
</cfinvoke>
<!---Added this to test if I can get more than one sheet to the Workbook--->
<cfinvoke component="financial.financial" method="getExecSummary_OLD" returnvariable="qExecSummary_OLD">
    <cfinvokeargument name="level" value="#URL.level#" />
    <cfinvokeargument name="stateGM" value="#URL.stateGM#" />
    <cfinvokeargument name="GM" value="#URL.GM#" />
    <cfinvokeargument name="engVP" value="#URL.engVP#" />
    <cfinvokeargument name="engDir" value="#URL.engDir#" />
</cfinvoke>
<!--- Get Report Date since qExecSummary is more complex than the other report queries --->
<cfquery name="qRpt_Date" datasource="#application.dsn#">
    SELECT DISTINCT rpt_date
    FROM fin_data
</cfquery>
<cfsetting requesttimeout="5000" />
</cfsilent>
<!---Added this as a function that should allow me to use multiple queries and create a workbook with more than one sheet--->
<cfscript>
    //Create new workbook with one sheet
    //by default that sheet is the active sheet
    Workbook = SpreadsheetNew("ExecSummary");
    //Add Data to the sheet
    format1.bold="true";
    formatNum.dataformat="0.00%";
    Spreadsheetformatcell(Workbook,format1,1,1);
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    Spreadsheetformatcell(Workbook,format1,3,1);
    SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
        arr="Here";
        writedump(arr);
    //if (qExecSummary.recordCount) {
    //  rowNum = 6;
    //  arr="Here";
    //  writedump(rowNum);
        //alert(qExecSummary.recordCount);
        //for(dataRow in qExecSummary){
            //SpreadSheetSetCellValue(Workbook,dateFormat(qRpt_Date.rpt_Date, 'mm/dd/yyyy'),rowNum,1);
            //SpreadSheetSetCellValue(Workbook,dataRow.responsible,rowNum,2);
            //SpreadSheetSetCellValue(Workbook,dataRow.name,rowNum,3);
            //SpreadSheetSetCellValue(Workbook,dataRow.Description,rowNum,4);
            //SpreadSheetSetCellValue(Workbook,dataRow.bud_sum,rowNum,5);
            //SpreadSheetSetCellValue(Workbook,dataRow.committed,rowNum,6);
            //SpreadSheetSetCellValue(Workbook,dataRow.Spent_YTD,rowNum,7);
            /*if (qExecSummary.bud_sum NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);
                //percentSpent="#(qExecSummary.Spent_YTD/qExecSummary.bud_sum)*100#";
                SpreadSheetSetCellValue(Workbook,(dataRow.Spent_YTD/dataRow.bud_sum)*100,rowNum,8);
            }
            else {*/
                //SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            //}
            //rowNum++;
        //}
        //End of WriteOutput
    //} else {
    //  SpreadSheetAddRows(Workbook,"No results for your criteria.");
    //} 
</cfscript>
<!---
<cffunction name="QueriesToXLS" access="public">
    <cfargument name="queryArr" required="true">
    <cfargument name="sheetNameArr" required="false">
    <cfset tempPath="C:\Temp\ExecutiveSummary" & ".xls">
    <cfset counter= 1>
    <cfloop array="#ARGUMENTS.queryArr#" index="i" >
        <cfset sheetName="Sheet#counter#">
        <cfif isDefined("ARGUMENTS.sheetNameArr")>
            <cfset sheetName=ARGUMENTS.sheetNameArr[counter]>
        </cfif>
        <cfspreadsheet action="update" filename="#tempPath#" query="i" sheetName="#sheetName#"/>
        <cfset counter += 1>
    </cfloop>
    <cfreturn SpreadsheetRead(tempPath)>
</cffunction>

<cfset xlsData = QueriesToXLS([qExecSummary,qExecSummary],["ExecutiveSummary","ExecutiveSummaryOLD"])>
--->

<cfheader name="Content-Disposition" value='attachment; filename="execSummaryNew.xls"'>
<!---cfcontent type="application/msexcel" variable="#SpreadsheetReadBinary(xlsData)#" reset="true"--->

<cfcontent type="application/vnd.ms-excel"> <!---This is where the application type is being set to Excel--->
<!---html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>

</head>
<h2>Executive Summary Report</h2>
<cfoutput>
<p>#dateFormat(now(), 'mm/dd/yyyy')#</p>
</cfoutput>
<table id="tableOne" class="yui" cellspacing="0" cellpadding="5">
<thead>
<tr>
    <th>Data Date</th>
    <th>Level</th>
    <th>Name</th>
    <th>Description</th>                    
    <th>Budget</th>
    <th>Committed</th>
    <th>Spent YTD</th>
    <th>% Spent</th>
</tr>
</thead>
<cfif qExecSummary.recordCount>
<tbody>
<cfoutput query="qExecSummary">
<tr>
    <td>#dateFormat(qRpt_Date.rpt_Date, 'mm/dd/yyyy')#</td>
    <td>#qExecSummary.responsible#</td>
    <td>#qExecSummary.name#</td>
    <td>#qExecSummary.Description#</td>
    <td>#qExecSummary.bud_sum#</td>
    <td>#qExecSummary.committed#</td>
    <td>#qExecSummary.Spent_YTD#</td>
    <td><cfif qExecSummary.bud_sum NEQ 0> 
            #numberFormat((qExecSummary.Spent_YTD/qExecSummary.bud_sum)*100,"9.9")#%
        <cfelse>
            0
        </cfif> 
    </td>
</tr>
</cfoutput>
</tbody>
<cfelse>
<tr>
    <td colspan="9">No results for your criteria.</td>
</tr>
</cfif>
</table>
</html--->

EDIT

I have tried following the answer provided on the question referenced in the duplicate. I am not able to get it to work with the query that I have. I have added the following code in place of the script that I had:

EDIT 2

Updated this script, still not working. errors on the writeOutput().I'm not sure how to implement using a query to create the data for the rows?

    <cfscript>
    //Create new workbook with one sheet
    //by default that sheet is the active sheet
    Workbook = SpreadsheetNew("ExecSummary");
    //Add Data to the sheet
    format1.bold="true";
    formatNum.dataformat="0.00%";
    Spreadsheetformatcell(Workbook,format1,1,1);
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    Spreadsheetformatcell(Workbook,format1,3,1);
    SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
    if (qExecSummary.recordCount) {
        rowNum = 6;
        //writeOutput(query="qExecSummary");
        for(dataRow in qExecSummary){
            SpreadSheetSetCellValue(Workbook,dateFormat(qRpt_Date.rpt_Date, 'mm/dd/yyyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,dataRow.responsible,rowNum,2);
            SpreadSheetSetCellValue(Workbook,dataRow.name,rowNum,3);
            SpreadSheetSetCellValue(Workbook,dataRow.Description,rowNum,4);
            SpreadSheetSetCellValue(Workbook,dataRow.bud_sum,rowNum,5);
            SpreadSheetSetCellValue(Workbook,dataRow.committed,rowNum,6);
            SpreadSheetSetCellValue(Workbook,dataRow.Spent_YTD,rowNum,7);
            if (qExecSummary.bud_sum NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);
                percentSpent="#(qExecSummary.Spent_YTD/qExecSummary.bud_sum)*100#";
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD/qExecSummary.bud_sum)*100,rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        }
        //End of WriteOutput
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }   
</cfscript>

However it is showing errors on the line with the Output. I don't know what to change it to. I've searched for what is usable in the cfscript tags and found this, but there's nothing there that looks like it would help?

Made update to the <cfscript> now I'm getting this error:

FINAL EDIT

Here's the working script:

<cfsilent>
<!--- *******************************************************************
Filename:   execSummary_Excel.cfm, v1.0 03/07/2012
Created By: Original Writer
Description:    Excel report export for Executive Summary Report.       
Change History:
Date........Name...........Description of Change........................
08/01/2012  Original Writer Added committed column.
02/28/2013  Original Writer Added stateGM and GM.
*************************************************************************--->
<cfinvoke component="financial.financial" method="getExecSummary" returnvariable="qExecSummary">
    <cfinvokeargument name="level" value="#URL.level#" />
    <cfinvokeargument name="stateGM" value="#URL.stateGM#" />
    <cfinvokeargument name="GM" value="#URL.GM#" />
    <cfinvokeargument name="engVP" value="#URL.engVP#" />
    <cfinvokeargument name="engDir" value="#URL.engDir#" />
</cfinvoke>
<!---Added this to test if I can get more than one sheet to the Workbook--->
<cfinvoke component="financial.financial" method="getExecSummary331" returnvariable="qExecSummary331">
    <cfinvokeargument name="level" value="#URL.level#" />
    <cfinvokeargument name="stateGM" value="#URL.stateGM#" />
    <cfinvokeargument name="GM" value="#URL.GM#" />
    <cfinvokeargument name="engVP" value="#URL.engVP#" />
    <cfinvokeargument name="engDir" value="#URL.engDir#" />
</cfinvoke>
<!--- Get Report Date since qExecSummary is more complex than the other report queries --->
<cfquery name="qRpt_Date" datasource="#application.dsn#">
    SELECT DISTINCT rpt_date
    FROM fin_data
</cfquery>
<cfsetting requesttimeout="5000" />
</cfsilent>

<!---Added this as a function that should allow me to use multiple queries and create a workbook with more than one sheet--->
<cfscript>
    //Create new workbook with one sheet
    //by default that sheet is the active sheet
    Workbook = SpreadsheetNew("ExecSummary");
    //Add Data to the sheet
    //Formatting
    format1.bold="true";
    format1.fontsize=12;
    format1.font="Calibri";
    format2.bold="true";
    format2.fontsize=18;
    format2.font="Calibri";
    formatNum.dataformat="0.0%";
    //adding the Headers
    Spreadsheetformatcell(Workbook,format2,1,1);
    Spreadsheetformatcell(Workbook,format1,3,1);
    Spreadsheetformatcell(Workbook,format1,5,1);
    Spreadsheetformatcell(Workbook,format1,5,2);
    Spreadsheetformatcell(Workbook,format1,5,3);
    Spreadsheetformatcell(Workbook,format1,5,4);
    Spreadsheetformatcell(Workbook,format1,5,5);
    Spreadsheetformatcell(Workbook,format1,5,6);
    Spreadsheetformatcell(Workbook,format1,5,7);
    Spreadsheetformatcell(Workbook,format1,5,8);
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
        arr=server.ColdFusion.ProductVersion;
    if (qExecSummary.recordCount) {
        rowNum = 6;
        do {
            SpreadSheetSetCellValue(Workbook,dateFormat(qRpt_Date.rpt_date,'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary.responsible[rowNum-5],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary.name[rowNum-5],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Description[rowNum-5],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary.bud_sum[rowNum-5],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary.committed[rowNum-5],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary.Spent_YTD[rowNum-5],rowNum,7);
            if (qExecSummary.bud_sum[rowNum-5] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD[rowNum-5]/qExecSummary.bud_sum[rowNum-5]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 6 LT qExecSummary.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }   
    SpreadsheetCreateSheet(Workbook,"ExecSummaryTest");
    SpreadsheetSetActiveSheet(Workbook,"ExecSummaryTest");
    //Formatting
    format1.bold="true";
    format1.fontsize=12;
    format1.font="Calibri";
    format2.bold="true";
    format2.fontsize=18;
    format2.font="Calibri";
    formatNum.dataformat="0.0%";

    Spreadsheetformatcell(Workbook,format2,1,1);
    Spreadsheetformatcell(Workbook,format1,3,1);
    Spreadsheetformatcell(Workbook,format1,5,1);
    Spreadsheetformatcell(Workbook,format1,5,2);
    Spreadsheetformatcell(Workbook,format1,5,3);
    Spreadsheetformatcell(Workbook,format1,5,4);
    Spreadsheetformatcell(Workbook,format1,5,5);
    Spreadsheetformatcell(Workbook,format1,5,6);
    Spreadsheetformatcell(Workbook,format1,5,7);
    Spreadsheetformatcell(Workbook,format1,5,8);
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    SpreadSheetSetCellValue(Workbook,"#dateFormat(now(),'mm/dd/yyyy')#",3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);
        arr=server.ColdFusion.ProductVersion;
    if (qExecSummary331.recordCount) {
        rowNum = 6; 
        do {
            SpreadSheetSetCellValue(Workbook,dateFormat(qRpt_Date.rpt_date,'mm/dd/yyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,qExecSummary331.responsible[rowNum-5],rowNum,2);
            SpreadSheetSetCellValue(Workbook,qExecSummary331.name[rowNum-5],rowNum,3);
            SpreadSheetSetCellValue(Workbook,qExecSummary331.Description[rowNum-5],rowNum,4);
            SpreadSheetSetCellValue(Workbook,qExecSummary331.bud_sum[rowNum-5],rowNum,5);
            SpreadSheetSetCellValue(Workbook,qExecSummary331.committed[rowNum-5],rowNum,6);
            SpreadSheetSetCellValue(Workbook,qExecSummary331.Spent_YTD[rowNum-5],rowNum,7);
            if (qExecSummary331.bud_sum[rowNum-5] NEQ 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);
                SpreadSheetSetCellValue(Workbook,(qExecSummary331.Spent_YTD[rowNum-5]/qExecSummary331.bud_sum[rowNum-5]),rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        } while (rowNum - 6 LT qExecSummary331.recordCount);
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }
    SpreadsheetSetActiveSheet(Workbook,"ExecSummary");
</cfscript>

<cfheader name="Content-Disposition" value='attachment; filename="execSummaryNew.xls"'>
<cfcontent type="application/msexcel" variable="#SpreadsheetReadBinary(Workbook)#" reset="true">

Not sure why the dupe close removed the previous comment: *"... In short, do not use `` which is buggy. Instead use spreadsheet functions. Add new sheets with `SpreadSheetCreateSheet`. Use `SpreadSheetSetActiveSheet` to set the active sheet and add data."* — Leigh, Jun 27 '16 at 20:48
@Leigh I understand that this has been marked as a duplicate, but I don't understand how I can use a query with the answer from the other question. I think I get the `SpreadSheetCreateSheet` and `SpreadSheetSetActiveSheet`, but It doesn't say how to create the workbook to begin with since I shouldn't use the `` Can you explain this to me? — Mike, Jun 28 '16 at 12:42
It is created on the first code line, ie `Workbook = SpreadsheetNew("Sheet1");`. You cannot mix cfml and cfscript. Use one or the other, but not both. Personally, I prefer cfscript, which uses for/in loop instead of ``. However, you could just convert the code to cfml if you prefer. Essentially get rid of the cfscript tags and change everything from `FunctionName(....);` to ``. — Leigh, Jun 28 '16 at 13:26
@Leigh I tried modifying it some. But still can't figure out how to get the data from the query to be the data for the rows? — Mike, Jun 28 '16 at 13:50
writeOutput is for displaying data on screen. To add data to a spreadsheet, you need to use spreadsheet functions. See my example below. — Leigh, Jun 28 '16 at 14:18
*the server that I'm using only has CF9* If at all possible, you should run the same version in both. Using different versions in Dev and Prod is just asking for problems... Unfortunately, Adobe tends to remove links for older versions as soon as a new version comes out, but there are a few listings out there, such as : http://bloginblack.de/2012/08/direct-download-links-for-older-versions-of-adobe-coldfusion-and-others/ or http://www.gpickin.com/index.cfm/blog/coldfusion-install-download-repo-new-home-for-all-your-files — Leigh, Jun 29 '16 at 14:43
@Leigh Thanks you for the help. I have successfully gotten it to work and am creating the workbook with multiple sheets. Now I just have to update the queries to pull what my end user wants. — Mike, Jun 29 '16 at 19:09

score 1 · Accepted Answer · edited May 23 '17 at 12:32

1

WriteOutput() is for displaying data on screen. To add data to a spreadsheet, you need to use spreadsheet functions. In your scenario, you can use a for/in loop to iterate through the query rows. Then use SpreadSheetSetCellValue(sheet, value, row, col) to populate the individual cells.

Update 1:

If you run the code below (from "Edit 2"), in a brand new .cfm script, you will see it works perfectly. So any errors must be coming from a different part of the code. If you are having trouble tracking down errors, I would recommend starting over with a clean slate. Create a brand new script with very simple code: just the minimum necessary to generate the spreadsheet. Omit anything extra like functions, downloads, etcetera and get the basic code working first.

<cfscript>
    // FOR DEMO ONLY: Create manual queries
    qRpt_Date = queryNew("");
    queryAddColumn(qRpt_Date, "Rpt_Date", [now()]);
    qExecSummary = queryNew("");
    queryAddColumn(qExecSummary, "responsible", [1,12,13]);
    queryAddColumn(qExecSummary, "bud_sum", [0,50,100]);
    queryAddColumn(qExecSummary, "Spent_YTD", [0,50,100]);
    queryAddColumn(qExecSummary, "Name", ["Name A","Name B","Name C"]);
    queryAddColumn(qExecSummary, "Description", ["Description A","DescriptionB","Description C"]);
    queryAddColumn(qExecSummary, "Committed", [0,50,100]);


    //Create new workbook with one sheet
    //by default that sheet is the active sheet
    Workbook = SpreadsheetNew("ExecSummary");

    //Add Headers
    headerTextFormat.bold=true;
    Spreadsheetformatcell(Workbook,headerTextFormat,1,1);
    SpreadSheetSetCellValue(Workbook,"Executive Summary Report",1,1);
    Spreadsheetformatcell(Workbook,headerTextFormat,3,1);
    SpreadSheetSetCellValue(Workbook, dateFormat(now(),'mm/dd/yyyy'),3,1);
    SpreadSheetSetCellValue(Workbook,"Data Date",5,1);
    SpreadSheetSetCellValue(Workbook,"Level",5,2);
    SpreadSheetSetCellValue(Workbook,"Name",5,3);
    SpreadSheetSetCellValue(Workbook,"Description",5,4);
    SpreadSheetSetCellValue(Workbook,"Budget",5,5);
    SpreadSheetSetCellValue(Workbook,"Commited",5,6);
    SpreadSheetSetCellValue(Workbook,"Spent YTD",5,7);
    SpreadSheetSetCellValue(Workbook,"% Spent",5,8);

    //Add data detail
    if (qExecSummary.recordCount) {
        // Start populating the spreadsheet on this row number
        // Change as needed.
        rowNum = 6;

        // Loop through query rows
        for(dataRow in qExecSummary) {
            // add 
            SpreadSheetSetCellValue(Workbook,dateFormat(qRpt_Date.rpt_Date, 'mm/dd/yyyy'),rowNum,1);
            SpreadSheetSetCellValue(Workbook,dataRow.responsible,rowNum,2);
            SpreadSheetSetCellValue(Workbook,dataRow.name,rowNum,3);
            SpreadSheetSetCellValue(Workbook,dataRow.Description,rowNum,4);
            SpreadSheetSetCellValue(Workbook,dataRow.bud_sum,rowNum,5);
            SpreadSheetSetCellValue(Workbook,dataRow.committed,rowNum,6);
            SpreadSheetSetCellValue(Workbook,dataRow.Spent_YTD,rowNum,7);
            if (qExecSummary.bud_sum != 0){
                Spreadsheetformatcell(Workbook,formatNum,rowNum,8);
                SpreadSheetSetCellValue(Workbook,(qExecSummary.Spent_YTD/qExecSummary.bud_sum)*100,rowNum,8);
            }
            else {
                SpreadSheetSetCellValue(Workbook,0,rowNum,8);
            }
            rowNum++;
        }
        //End of WriteOutput
    } else {
        SpreadSheetAddRows(Workbook,"No results for your criteria.");
    }   

    //For test purposes, save the results to a file
    SpreadSheetWrite(Workbook, "c:/path/to/yourFile.xls", true);

    WriteOutput("Done!");
</cfscript>

Update 2:

As noted in the comments, for..in query loops are not supported in CF9. If you are using an older version of CF, then you will need to use a from/to loop instead.

// Loop through query rows
for(x = 1; x lte yourQueryName.recordCount; x++) {
    SpreadSheetSetCellValue(Workbook, yourQueryName.YourColumnName[x], theSheetRowNum, 1);
    // ...

    theSheetRowNum++;
}

Side note, personally I prefer cfscript. However, if you feel more comfortable with CFML you can always rewrite the example. Just get rid of the cfscript tags and replace:

<cfscript>
    SomeFunctionName(....); 
</cfscript>

with:

<cfset SomeFunctionName(....)>

edited May 23 '17 at 12:32

Community

1
1

answered Jun 28 '16 at 14:11

Leigh

28,765
10
55
103

I think that I've almost got it. Now just have an error with this line: `SpreadSheetSetCellValue(Workbook,#numberFormat((qExecSummary.Spent_YTD/qExecSummary.bud_sum)*100,"9.9")#%,rowNum,8);` I'm guessing that it hass to do with the formatting of the number? I did try changing the double quotes to single, but got the same error. – Mike Jun 28 '16 at 15:08
Never mind I think I fixed that line, but I've got another error that makes no sense to me. I've added it above. – Mike Jun 28 '16 at 15:32
Totally unrelated, but you do not need those pound signs. Regarding the the error A) Since you are using a for/in loop, be sure to change all of the references from `qExecSummary.SomeColumnName` to `dataRow.SomeColumn`. B) If you are still get that error after updating the references, what code is on line 56 (or near it, error messages are not always exact)? – Leigh Jun 28 '16 at 15:46
Line 56 is :`for(dataRow in qExecSummary){`. I did update to make sure all refernces are to `dataRow.SomeColumn` and not `qExecSummary.SomeColumn` – Mike Jun 28 '16 at 15:58
Sounds like the line number is misleading, because for/in works fine with 2016: http://trycf.com/gist/1e25f678a53846ee9a3607e0bf0aec47/acf2016?theme=monokai Nothing is jumping out as blatantly wrong. Try commenting out some of the code to try and pinpoint what is causing the problem. In cfscript, comments are `/* */` – Leigh Jun 28 '16 at 16:45
Is there a way to create an error message popup within the `` so that I can set that and then move it around to see where it's failing? – Mike Jun 28 '16 at 17:12
Do I still need the lines: `` and `` After the ``? – Mike Jun 28 '16 at 17:34
If you mean display a debugging message on screen, use `WriteDump()` or `WriteOutput`. Yes, cfheader/cfcontent are needed (or the cfscript equivalents) if you want the final file displayed in browser. However, leave those commented out for now, until you have resolved the error. – Leigh Jun 28 '16 at 17:46
The reason I asked is that I commented out the whole `for` loop and then it said that the error was on line 101 which in ``. I don't want it displayed in the screen, I want it to download the workbook. Which is what happens now and prints the error in the workbook. – Mike Jun 28 '16 at 17:59
There is nothing wrong with the exact code you posted in Edit 2. It runs fine under CF11. So the problem has to be somewhere else in the code. The only thing I can suggest is debugging 101. *Start with a totally clean slate and the basic code only* - no functions, no downloads, etcetera. Only the minimum code necessary to generate the XLS file. Create a brand new script and add a small section of code to it. Run it. If it works, go ahead and add another section. Repeat until you hit the error. – Leigh Jun 28 '16 at 18:14
Also, so as not to overlook the obvious, did you verify the code is actually working with valid query object(s) before trying to loop through it? – Leigh Jun 28 '16 at 18:24
I assumed that it was since it gets to the loop. The `if(qExecSummary.recordCount)` should stop it first if there's nothing there. It never seems to get the line that says there were no results. – Mike Jun 28 '16 at 18:28
Since I'm using `Workbook` to create the workbook should I change the reference to it in the ``? I just realized that with the `for` loop commented out I still don't get the headers printed, I get a blank workbook with only one sheet. – Mike Jun 28 '16 at 18:31
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115871/discussion-between-mike-and-leigh). – Mike Jun 28 '16 at 18:37
My problem now is that the workbook that I'm creating with `Workbook = SpreadsheetNew("ExecSummaryTest");` is not the workbook that is being opened. How can I either open the one being created here or add my sheets to the one being opened by `` – Mike Jun 29 '16 at 11:42
Nevermind I got that, but I can't figure out what's up with the `for` loop that it doesn't work. Maybe the version of ColdFusion that the server is using is older? I don't have direct access to the server, is there a way to find out what version it has? – Mike Jun 29 '16 at 12:22
Found the problem. My server is using CF9 while the `FOR IN loop (query)` is only available in CF10+ – Mike Jun 29 '16 at 12:34
As far as my original question this is answered. Thank you for your help. If I can't figure out a work around I'll ask another question. – Mike Jun 29 '16 at 12:53
@Mike - Yes, using an earlier version would do it. For CF9 you have to use a from/to loop: http://trycf.com/gist/f47dfa644b3736bd776ba6d94593f428/acf?theme=monokai – Leigh Jun 29 '16 at 14:20

Create workbook with multiple sheets from Queries

1 Answers1