0

I am querying a table that generates about 60,000 records, which I am getting in a result set. I wish to get the results from the result set in an excel.

  1. I tried using HSSFWorkbook, and I am able to get an excel of about 30,000 records but fails for 80,000 records in result set.

    Error: No error, it simply stopped and no excel was generated at the specified location.

  2. Then I tried using, XSSFWorkbook.

    Error: java.lang.OutOfMemoryError: Java heap space

  3. I also tried increasing the heap space in eclipse by adding the following in VM arguments: -Xms512M

    Error: No error, but no excel generated.

  4. Next, I did use SXSSFWorkbook (reference: link): code follows:

    SXSSFWorkbook wb = new SXSSFWorkbook(wb_template); 
wb.setCompressTempFiles(true);

SXSSFSheet sh = (SXSSFSheet) wb.getSheetAt(0);
sh.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk

    Error: java.lang.OutOfMemoryError: Java heap space

How can I make this work? I have been stuck on this for days now. Any help is appreciated. Thanks in advance.

Edit : Is there any other way to get fire select query on database and get result in an excel.

Allan Fernandes
  • 17
  • 1
  • 9
  • @user7 No, i am firing the database with a select query and getting the result in a result set, which i am then taking in an excel. – Allan Fernandes Apr 09 '18 at 18:34
  • In that case, SXSSFWorkbook should be able to help. Can you post your full code? Also, try reducing 100 to something lower (I guess 100 is not that big unless you have a lot of columns) – Thiyagu Apr 09 '18 at 18:36
  • What is the value of `-Xmx`? – Thiyagu Apr 09 '18 at 18:36
  • @user7 yes i will post the full code asap. But will reducing 100 to some smaller number help? I have around 15 columns. – Allan Fernandes Apr 09 '18 at 18:37
  • As I said, you can try that but I cannot be sure. It won't help unless you are already consuming around the Xmx mark. – Thiyagu Apr 09 '18 at 18:38
  • @user7 i tried setting it to -Xmx1024 but the server wasnt starting then, throwing some error. So i reduced it to -Xmx512 – Allan Fernandes Apr 09 '18 at 18:39
  • Then try for something intermediate (768M). *Maybe*, you are not able to process it with the amount of memory you have. See if you can do some profiling from fake data and see how much memory the SXSSFWorkbook code takes. – Thiyagu Apr 09 '18 at 18:41
  • I guess you have a lot of columns and/or the values of those columns are huge (strings?) – Thiyagu Apr 09 '18 at 18:42
  • @user7 i will surely try that and get back to you. Thanks. – Allan Fernandes Apr 09 '18 at 18:42
  • @user7 some are strings, some are integers. But yes, the value of the columns is huge. – Allan Fernandes Apr 09 '18 at 18:43
  • As a quick check reduce 100 to 1 and try. Then try to find the breaking point. – Thiyagu Apr 09 '18 at 18:44

0 Answers0