Export hundreds of thousands of records with ColdFusion

Question

Using ColdFusion 9.0.1, I need to export hundreds of thousands of database records to Excel XLSX or CSV (preferably XLSX). This must be done on demand. So far I have tried to use cfspreadsheet, but it suffocates when exporting more than several thousand lines in XLSX format. However, exporting to XLS works just fine (of course, there is a limit of 65,000 lines).

What are my options for exporting so many records? In theory, users might need to export up to a million records. I also use SQL Server 2008 R2 - is there a way to somehow export records to a file and then send the file via CF to the user? What are my options? Thanks.

Answer 1

Since you are using SQL Server 2008, you can use SQL Server Reporting Services (SSRS) and create a report that can be called through the ColdFusion web service (or HTTP GET / POST). SSRS has the ability to export reports as Excel. You will need to read about SSRS to make this work, but it is pretty easy to do.

Answer 2

As you have discovered, this happens with the ColdFusion <cfspreadsheet/> because it creates the entire document in memory, which leads to JVM OutOfMemory errors. What you need is what dumps buffers to disk so that you don't run out of memory. This offers a CSV that is much easier to buffer. I suppose there are ways to do this with Excel too, but I don't know them.

So, for you there are two options:

• use java library
• use the ColdFusion methods fileOpen() , fileWrite() , fileClose()

I will cover each one in turn.

Java libary

opencsv is my preference. This assumes, of course, that you know how to configure .jar in the ColdFusion path. If you do this, it is a question using its API to open the file and provide data for each line. It is very simple. Check his documents for examples.

ColdFusion Methods

Be warned that there are dragons here .

If you export numbers or strings that do not contain double quotes or commas, you can probably do this. If not, find out what to avoid and why you use the library in the first place. The code is something like this:

 <!--- query to get whatever data you're working with ---> <cfset csvFile = fileOpen(filePath, 'read')> <cfloop query="yourQuery"> <cfset csvRow = ""><!--- construct a csv row here from the query row ---> <cfset fileWrite(csvFile, csvRow)> </cfloop> <cfset fileClose(csvFile)> 

If the query data you are working with is also large, you can deal with a nested loop to cut it out.

Answer 3

Dustin, I had to research this myself, and at the time of this writing (Summer 2011), POI does a great job of creating large files, but you should use xlsx. 3.8 beta source comes with the “BigGridDemo” example, which quickly creates a 100 KB workbook and 4 columns. I modified it to create a 300K, 125-column sheet, and processed it after about 2 minutes. He created a book of 1.6 GB, 3.6 million lines in half an hour.

Of course, the code is not the most beautiful, but it works. I suspect this will fix a little if put in ColdFusion.