create .xlsx file using cfspreadsheet with more than 200K records in CF 10

[cfrbt]

Hello,

I am trying to create an excel sheet (xlsx) using cfspreadsheet which gets data through a query object (SQL Server) having more than 200K records. When attempting to do that the cfm page just runs for few minutes (have set timeout for the page as 3600s) and stop processing further without any error and at the same time the excel sheet does not get created.

The requirement is to let user download the excel sheet with very large number of records whenever they want, no fix schedule for it.

Note: For another instance, upon dumping the SQL query object alone on the page it displays the records (in this case approx. 80K) successfully in a minute (approx.) however, cfspreadsheet does not work for this even.

Any advise / thoughts would be greatly appreciated.

Thank you.

[WolfShade]

Hello, cfrbt,

Place the cfspreadsheet code within a CFTRY/CFCATCH and dump #cfcatch# in the catch portion to see what might be causing this to fail.  Also, check the logs in CFAdmin for any clues.

HTH,

^ _ ^

[cfrbt]

Thank you. I had done it already but with no success.

[EddieLotter]

As a test, make the database query return only about ten records.

Does the ColdFusion script complete with such a small result set?

[cfrbt]

Thank you.

Actually, earlier it was already working for .xls file with maximum rows (65,536). Later the rows were increasing so the requirement was to generate .xlsx to accommodate very large set of rows.

I will try that to work with only few records.

[BKBK]

Use threads. Here is an example:

<cfscript>

thread action="run" name="createSheet" {

  filepath=expandpath(".") & "\" & "Orders.xlsx";

  // Get data from a query

  sql="SELECT orderid,customerfirstname,customerlastname,address,total,city FROM orders

  ORDER BY orderid";

  sqlParams={};

  artOrders=queryExecute(sql,sqlParams,{datasource="cfartgallery"});

  // Create a spreadsheet object with sheetname Art

  spreadsheetObj=spreadsheetNew("Art",true);

  // Add rows with data from query result. The data start from row 1, col 1.

  // The spreadsheet will have column names.

  spreadsheetAddrows(spreadsheetObj,ArtOrders,1,1,true,[""],true);

  dirName=GetDirectoryFromPath(GetCurrentTemplatePath());

  // Write spreadsheet to a file

  SpreadsheetWrite(spreadsheetObj,filepath,true);

}

thread action="run" name="downloadSheet" {

    filepath=expandpath(".") & "\" & "Orders.xlsx";

    cfheader(name="Content-Disposition", value="attachment; filename=Orders.xlsx");

    cfcontent(type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", file="#filepath#");

}

thread action="join" name="createSheet,downloadSheet";

</cfscript>

[Dave Watts]

I think this example is kind of problematic for several reasons.

First, I'm not sure the original poster's problem is solvable with concurrency at all. If you're willing to let a CF page run long enough, as in the original example, CF will do whatever it needs to do if that thing can be done at all. CFTHREAD lets you solve two basic problems. One of those problems is keeping a user waiting for a response when none is immediately needed, and the other (the one you illustrated) is making a user wait for serial processing for something that can be done concurrently.

Second, the specific CFTHREAD example doesn't really solve any problems even by using concurrency. If you're going to generate a report and then serve that report, those are two things that have to happen serially. When you use CFTHREAD to run multiple tasks, then use the JOIN action of CFTHREAD, you're working under the assumption that all of these tasks are going to happen simultaneously while your main page waits around for a while for them to finish. What happens in this case if your report hasn't been generated? Are you going to serve the previous iteration of the report? If so, why even generate another report now? You could generate a report at a predetermined time, then serve that report when someone requests the previous iteration of the report.

In general, if I'm trying to use CFTHREAD to solve a problem of long-running reports, I'd use it to generate a thread that runs in the background, then simply end the main page and have other code elsewhere to handle getting the report back to the user at a later time - maybe something that checks for the existence of the report file itself.

Of course, maybe I'm missing something here so I'm open to enlightenment. That's how I learn new things!

Dave Watts, Fig Leaf Software

[BKBK]

Hi @Dave Watts,

Thanks for your remarks.  The points you make are valid. Nevertheless, there is a reason I chose to use threads here: to separate the task of creating the Excel file from that of downloading. This follows from:

@cfrbt wrote:

... the cfm page just runs for few minutes (have set timeout for the page as 3600s) and stop processing further without any error and at the same time the excel sheet does not get created.

The threads are therefore used more or less for debugging purposes. With the threads, it's easier to tell which of the tasks is failing.

In fact, @cfrbt can now define a timeout for each thread. This should provide us with more information than the original page, which silently went into the mist after a few minutes.

[Dave Watts]

Those all sound like valid justifications to me! Thanks for elaborating.

Dave Watts, Fig Leaf Software