This content has been marked as final. Show 6 replies
> I need help with creating an Excel file for client that has a table of 40,000
> records. I have code that generates the Excel and has worked well in the past
> but with this much data it is timing out.
> I've already informed the client that Excel has limit of 66000 records. So it
> might be better to export to CSV as the data in this table is going to keep
> If you have time to work with me on this , contact me a alexagui [at] gmail
> [dot] com
> and I can send you more details so you can put together an accurate quote.
For so many records my guess is that the concatenation times the most
time so you'll need to use StringBuffer to build the string. Google for
coldfusion and stringBuffer:
If it's just one client, you should find out what he's doing with the file. Maybe it's something that you could do for him with Cold Fusion
Also, if you send a text file with more rows than Excel can handle, and he opens it with Excel, you've not done him any favours.
Mack, thanks for the link to the article. I'm going to give that a shot.
Dan, you're absolutely correct. But I have already warned them about the 66K Excel limit.
You can attach the RequestTimeout variable to the URL, to extend the timeout.
But to avoid a long wait for the page, you could create the Excel file once a day in a batch run, and just provide a link to the file in the page.
To create the Excel file, I would always recommend to use the Apache POI API, because using the POI the date-fields are date-format, and number fields are number-format, which is very important when any Excel formulas or filters are supposed to be used later.
> I need help with creating an Excel file for client that has a table of 40,000 records
What is the client ultimately doing with the information and how often do they need to retrieve it? Depending on what it is needed for and your database type, you might also investigate database options. MS SQL has features like openrowset, etcetera which are capable of interacting with Excel (ie import/export)