Exception while reading excel files using apache poi

Question

I am reading two excel sheets which have 97000 rows combined. I get this exception when I run the program on eclipse. It gets stuck at 92865 rows. Is there some problem in the code or is it a memory configuration problem. iteratormaster is the iterator for the second workbook. stringSet is a hash set. It's running fine when I increase the heap size. It's doesn't work when I create a jar and distribute it on other machines. Any permanent solution?

       while (iteratormaster.hasNext()){
           //jLabel5.setText("Processing record "+qw+" out of"+ total+ " "+ "(" +   (qw/total)*100+"%");
          Row rowmaster =   iteratormaster.next();
              int qw1= rowmaster.getRowNum();
               if (qw1%1500==0){
               //jProgressBar1.setValue(((qw+n)/total)*100);
               //    Thread.sleep(500);
               jLabel11.setText("Processing record "+(qw1+n)+" out of "+ total);
               jLabel11.paintImmediately(jLabel11.getVisibleRect());
               }
          Cell media1 = rowmaster.getCell(5,Row.CREATE_NULL_AS_BLANK);
          String gandu=media1.toString();
          b.add(gandu);
              //sorted.add(gandu);
          Cell cellmaster0 = rowmaster.getCell(4,Row.CREATE_NULL_AS_BLANK);
          if (stringSet.contains((cellmaster0).toString()))
          {
              rowindexa++;

              cellindexa=-1;
              Row rowa=sheeta.createRow(rowindexa);
              //Iterator<Cell> cellmaster = rowmaster.iterator();
              for (int cn=0;cn<rowmaster.getLastCellNum();cn++){
                  cellindexa++;
                  Cell cellmaster1= rowmaster.getCell(cn, Row.CREATE_NULL_AS_BLANK);
                  String aap = cellmaster1.toString();
                  Cell cella =rowa.createCell(cellindexa);
                  cella.setCellValue(aap);
                              if (aap.indexOf("\t")!=-1){
                              System.out.println(aap);}
              }


          }
          else {
              rowindextemp++;
              cellindextemp=-1;
              Row rowb=sheettemp.createRow(rowindextemp);
              //Iterator<Cell> celltemp1 = rowmaster.cellIterator();
              for (int cn1=0;cn1<rowmaster.getLastCellNum();cn1++){
                  cellindextemp++;
                  Cell celltemp2 = rowmaster.getCell(cn1,Row.CREATE_NULL_AS_BLANK);
                  String congress=celltemp2.toString();
                  Cell cellb = rowb.createCell(cellindextemp);
                  cellb.setCellValue(congress);
              }

              }


          }







Exception in thread "AWT-EventQueue-0" java.lang.OutOfMemoryError: GC overhead limit exceeded
at org.apache.xmlbeans.impl.store.Saver$TextSaver.resize(Saver.java:1592)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.preEmit(Saver.java:1223)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.emit(Saver.java:1144)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.emitElement(Saver.java:926)
at org.apache.xmlbeans.impl.store.Saver.processElement(Saver.java:456)
at org.apache.xmlbeans.impl.store.Saver.process(Saver.java:307)
at org.apache.xmlbeans.impl.store.Saver$TextSaver.saveToString(Saver.java:1727)
at org.apache.xmlbeans.impl.store.Cursor._xmlText(Cursor.java:546)
at org.apache.xmlbeans.impl.store.Cursor.xmlText(Cursor.java:2436)
at org.apache.xmlbeans.impl.values.XmlObjectBase.xmlText(XmlObjectBase.java:1455)
at org.apache.poi.xssf.model.SharedStringsTable.getKey(SharedStringsTable.java:130)
at org.apache.poi.xssf.model.SharedStringsTable.addEntry(SharedStringsTable.java:176)
at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:350)
at org.apache.poi.xssf.usermodel.XSSFCell.setCellValue(XSSFCell.java:320)
at defg.jButton4ActionPerformed(defg.java:1068)
at defg.access$9(defg.java:942)
at defg$9.actionPerformed(defg.java:539)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2018)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2341)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
at java.awt.Component.processMouseEvent(Component.java:6505)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3320)
at java.awt.Component.processEvent(Component.java:6270)
at java.awt.Container.processEvent(Container.java:2229)
at java.awt.Component.dispatchEventImpl(Component.java:4861)
at java.awt.Container.dispatchEventImpl(Container.java:2287)

What's the heap size of your JVM? And did you try increasing it? — Gagravarr
– Gagravarr, Commented Jul 3, 2014 at 8:28
it works when I increase it but if I create a jar out of it and run on other machines. It fails. A permanent solution? — user3766749
– user3766749, Commented Jul 3, 2014 at 9:03
You can set the default JVM heap size on a machine - just up that everywhere and it'll be fine for this and everything else. (The install normally sets a stupidly low value) — Gagravarr
– Gagravarr, Commented Jul 3, 2014 at 9:29
I am increasing heap size on a 32 bit machine using SET _JAVA_OPTIONS = -Xms512m -Xmx1024m still not working. What heap size should I put. Infact it gets stuck very early in the process. — user3766749
– user3766749, Commented Jul 3, 2014 at 9:53
Have you tried reading the Apache POI FAQ Entry on memory use? — Gagravarr
– Gagravarr, Commented Jul 3, 2014 at 10:37

Ninad Pingale · Accepted Answer · 2014-07-03 08:03:11Z

2

Try to increase memory for your project -

In Eclipse - Right click your project > Run As > Run Configurations > Argument tab > under VM arguments section > add -Xms512M -Xmx1024M or > Apply

answered Jul 3, 2014 at 8:03

Ninad Pingale

7,0995 gold badges36 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3766749 Over a year ago

that is all right. but if make a jar out of it i am facing the same problem. so no use

Collectives™ on Stack Overflow

Exception while reading excel files using apache poi

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related