tracker issue : CF-4198618

select a category, or use search below
(searches all categories and all time range)

Monitoring CFSpreadSheet

| View in Tracker

Status/Resolution/Reason: To Fix//NeedMoreInfo

Reporter/Name(from Bugbase): Barry Friedland / Barry Friedland ()

Created: 05/09/2017

Components: Document Management, Office Integration, Performance

Versions: 2016

Failure Type: Others

Found In Build/Fixed In Build: /

Priority/Frequency: Normal /

Locale/System: / Windows 7 64-bit

Vote Count: 0

Currently there is no way to monitor the progress of <CFSpreadSheet>.   For big files this can cause a long delay.  

Either provide a way to monitor a file size or allow the ability to append to the spreadsheet so that it can be assembled in stages.



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.
763 | August 21, 2017 07:46:07 AM GMT
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.
764 | August 22, 2017 12:06:54 AM GMT
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.
765 | August 23, 2017 09:01:28 AM GMT
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.
766 | August 29, 2017 07:10:40 PM GMT
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.
767 | September 05, 2017 12:11:23 PM GMT