With Excel files of this size you will almost certainly get a performance hit.
Furthermore, the number of records exceeds 65,536 which means you cannot use the XLS format (Excel 97 / 2000 / 2003). It has a maximum of 65,536 rows per sheet. You will need to use the XLSX format (Excel 2007 / 2010) which has a limit of 1,048,576. I know only one library for that, Apache POI, with its XSSFxxx classes.
vardhan reddy wrote:Apache POI is one option, I think we wont get performance because of data size.
You haven't even got a solution yet, and you're already worried about what the performance might be? I refer you to my quotation below.
Get a working solution first, and THEN worry about performance; and unless you plan on writing your own Excel file writer, you'll be stuck with whatever performance you get from a 3rd party product, so I wouldn't sweat it too much. And like the others, I'd say Apache POI is probably the place to start.
Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Joined: Oct 28, 2011
Also, what did you mean by "data size is 500k"? I thought 500KB of data; Rob thought 500.000 rows of data. Is it either of these, or something else entirely, like 500.000 cells filled with data?
500.000 rows of data. Rob told good point which i don't know.
I have started working with POI. I will get back to you guys if I need any help.
1. Query your data from the database
2. Put the data in ResultSet
3. Import the data from ResultSet to the worksheet
Please see if this might help in your scenario. Please also note that this component provides comprehensive documentation and support as well; it also provides a lot of other features to work with Excel files.
Developer Evangelist @ Aspose. I love to explore and learn new technologies and help other developers along the way.