Create Spreadsheet from Multiple Queries

Question

In try to create a spreadsheet that is generated from multiple queries, the following code only generates one row of data and not the entire spreadsheet:

<cfset filenametouse = 'Usage_Report' />
<cfset theDir = GetDirectoryFromPath(GetCurrentTemplatePath()) /> 
<cfset theFile = theDir & filenametouse & ".xls" /> 

<cflock name="fileActionSentItems" type="exclusive" timeout="30" throwontimeout="true">
     <cfset SpreadsheetObj = spreadsheetNew()>
     <cfset fcol = {}>
     <cfset fcol.dataformat = "@">

     <cfset SpreadsheetAddRow(SpreadsheetObj, "Part Number, Description, Allocated, On Hand, Pending Receipt, Job Count, Qty Needed, Qty Issued, Order Count, Qty Ordered, Qty Shipped")>

     <cfoutput>
          <cfset SpreadsheetAddRows(SpreadsheetObj,"#getParts.partnum#, #getParts.partdescription#, #getParts.allocated#, #getParts.onhand#, #receiptdata.recqty#, #jobdata.JobCount#, #jobdata.QtyNeeded#, #jobdata.qtySent#, #orderdata.ordercount#, #orderdata.ordered#, #orderdata.shipqty#")>
     </cfoutput>

     <cfset SpreadsheetFormatColumn(SpreadsheetObj,fcol,11)>
     <cfspreadsheet action="write" filename="#theFile#" name="SpreadsheetObj" sheetname="Sheet1" overwrite="true" />
</cflock>

The spreadsheetAddRows isn't creating the data to populate the rows. What am I not doing correctly?

Leigh · Accepted Answer · 2012-02-07 19:10:58Z

1

You need to pass in the query object instead of a single row of data.

<cfset SpreadsheetAddRows(SpreadsheetObj, getParts) >

As an aside, you probably do not need to lock that whole section. If the lock is intended to prevent concurrent file access, you only need to lock the code writing the sheet to disk. (Depending on your needs, you might also use a more granular name. But that is just a guess.)

answered Feb 7, 2012 at 19:10

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

aparker81 Over a year ago

This does help, but the last 7 columns get data from queries other than get parts. How do I incorporate them?

Leigh Over a year ago

Why not just do a JOIN and pull all the values needed in one query? You could output them separately by calling SpreadsheetAddRows twice, once with each query -OR- stitch the two queries together but only if the two queries have the same number of records - in the exact same order. Otherwise, the results may be misaligned.

ale Over a year ago

...or do a query-of-queries to get all of the data into a single query object.

Leigh Over a year ago

Yeah, if you really cannot use a JOIN a QoQ on a key column would be better as it assures the right results are matched up.

RKolosky · Accepted Answer · 2012-02-07 19:15:20Z

You're not looping through the query to assign more than one row. Because you don't specify what position the values should come from, they're being assigned from the first record in each query.

Create another query containing everything you want in the spreadsheet with queryNew()

<cfset newQuery = queryNew("partNo,desc,allocated,onHand,rendingReceipt,jobCount,qtyNeeded,qtyIssued,orderCount,qtyOrdered,qtyShipped","varchar,varchar,varchar,varchar,varchar,varchar,integer,integer,integer,integer,integer,integer,integer,integer") />

... etc, assigning cells as you go. When you have a complete query object, then you can add that to spreadsheetAddRows(spreadsheetObj,newQuery) />

If you find the spreadsheet creation to be too slow, you may want to check out POI Utility at http://www.bennadel.com/projects/poi-utility.htm Not as configurable as the spreadsheet options, but faster for some work loads.

Collectives™ on Stack Overflow

Create Spreadsheet from Multiple Queries

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related