tracker issue : CF-4198618

Barry, Pls. share some more details on how you are using the feature and how, what you are proposing, will help. Are we talking about creating or updating spreadsheets, or both. How long is it taking for you? How are you getting the data that you are populating in your spreadsheet. What is the size of the spreadsheet being created. When you say monitoring the progress, do you mean only the size, or some other parameters as well. And, of course, there are ways to update a spreadsheet once it has been created. One way to incrementally add information to a spreadsheet, would be by adding new sheets.

Comment by Piyush K.

The problem I have is the time it takes to create a spreadsheet that contains even a few hundred records. I am requesting some method to monitor the progress. Creating multiple sheets is not an option. One suggestion I have is to append to the output so that after X rows have been written, a counter would be updated. Another way would be to monitor the size of the file as it is generated and knowing the final size would allow one to calculate a percent complete. There may be other schemes. All my data is coming from a <cfquery> and here is the basic structure. <cfset colArr = ArrayNew(1)> <cfset colArr[1] = 'Col1'> <cfset colArr[2] = 'Col2'> <cfset colArr[3] = 'Col3'> <cfset colArr[4] = 'Col4'> <cfset formatHeader = structNew()> <cfset formatHeader.bold="true"> <cfset formatHeader.fgColor="light_orange"> <!--- Query the database for the data ---> <cfset results = dbAccess.getMyData()> <!--- create a spreadsheet ---> <cfset filot = "MyReport.xlsx"> <cfset filotAbs = GetTempDirectory() & filot> <cfspreadsheet action="write" filename="#filotAbs#" query="results" sheetname="My Report" overwrite="true"> <cfset excelObj = SpreadsheetRead(filotAbs)> <!--- format the header row ---> <cfloop from="1" to="#ArrayLen(colArr)#" index="icol"> <cfset spreadsheetFormatCell(excelObj, formatHeader, 1, icol)> </cfloop> <!--- show the spreadsheet ---> <cfspreadsheet action="write" filename="#filotAbs#" name="excelObj" sheetname="My Report" overwrite="true"> <cfheader name="content-disposition" value="attachment;filename=#filot#"> <cfcontent type="application/msexcel" file="#filotAbs#" deletefile="true" reset="true">

Comment by Barry F.

Barry, A few hundred records should not take too long. Can we identify if there's a specific step in your workfllow that is taking too long. For, example how long is the DB call taking You can record the time consumed in each step with something like this: _start = getTickCount(); // get data from DB _end = getTickCount() - _start; writeOutput("time elapsed: " & _end); _start = getTickCount(); // create empty sheet _end = getTickCount() - _start; writeOutput("time elapsed: " & _end); //populate the sheet with data ... and so on.

Comment by Piyush K.

It is about 40,000 records of 15 columns each. The overall time to generate is 5 or more minutes and the output file is < 3 MB. The example code I provided earlier has the structure of the problem but the actual test performed has many more columns and much more formatting. Hence I need a method to monitor the progress. Here are the specific timings: Database Query: 4470 cfspreadsheet write query: 143526 SpreadsheetRead: 2625 Format columns: 2235 cfspreadsheet after formatting: 106710 The final call to <cfheader> and <cfcontent> prevented any output so that last step was omitted.

Comment by Barry F.

staggered creation of a spreadsheet does not [edit: missed "NOT" originally] seem like a practical idea, IMO. opinions are welcome. We can check to see if there's any scope of performance improvement with spreadsheet ops. With the test case below addrows and write operations seem to be taking most of the time. With ~1000 rows on 2016,0,05,303586 the output is: time consumed - query compose: 251 time consumed - spreadsheetAddRows: 15865 time consumed - spreadsheetFormatCell: 891 time consumed - cfspreadsheet/write: 13668 time consumed - cfspreadsheet/read: 2428 <cfsetting requesttimeout="300"> <cfscript> out_fl = expandPath("./") & "data.xlsx"; qRecoCount = 9999; qdata = queryNew("ID, product, customer, qtr","", [ {ID:1, product:"aniseed syrup", customer="annie", qtr="1"}, {ID:2, product:"camembert pierrot", customer="pierre", qtr="2"}, {ID:3, product:"scones", customer="connie", qtr="4"} ]); _start = getTickCount(); for(r=4; r LTE qRecoCount; r++) queryAddRow(qdata, {ID: "#r#",product:"prod_#r#", customer="cust_#r#", qtr="1"}); writeOutput("time consumed - query compose: " & (getTickCount() - _start) & "<br>"); colCount = arrayLen(qdata.getColumnNames()); _start = getTickCount(); xlObj = spreadsheetNew("testsheet_xlsx", true); spreadsheetAddRows(xlobj, qdata); writeOutput("time consumed - spreadsheetAddRows: " & (getTickCount() - _start) & "<br>"); sformat = structNew(); sformat.bold = true; sformat.italics = true; sformat.fgColor = "light_orange"; _start = getTickCount(); for(c=1; c LTE colCount; c++) for(r=1; r LTE qRecoCount; r++) spreadsheetFormatCell(xlobj, sformat, r, c); writeOutput("time consumed - spreadsheetFormatCell: " & (getTickCount() - _start) & "<br>"); _start = getTickCount(); cfspreadsheet(action="write", filename="#out_fl#", name="xlobj", overwrite=true); writeOutput("time consumed - cfspreadsheet/write: " & (getTickCount() - _start) & "<br>"); _start = getTickCount(); cfspreadsheet(action="read", src="#out_fl#", query="rdata"); writeOutput("time consumed - cfspreadsheet/read: " & (getTickCount() - _start) & "<br>"); //writeDump(rdata); </cfscript>

Comment by Piyush K.