title: Read and edit big Excel files with Apache POI
body: <p>I have read many articles and threads about the problems with large files with Apache POI. But I have still some problems with any solution.
  So my task is to remove empty rows from many Excel files. They are arranged in a tree structure of directories and I want to go through all at once program execution.
  For this, I have next method:</p>
  
  <pre><code>public static void listFilesForFolder(final File folder) {
          for (final File fileEntry : folder.listFiles()) {
              if (fileEntry.isDirectory()) {
                  System.out.println("\n" + "Folder: " + fileEntry.getName());
                  listFilesForFolder(fileEntry);
              } else {
                  System.out.println("File:" + fileEntry.getName() + " = " + humanReadableByteCount(fileEntry.length()) + " -&gt; "); 
                  System.out.println("New size: " + humanReadableByteCount(workWithWorkbook2(fileEntry)));
              }
          }
      }
  </code></pre>
  
  <p>The method humanReadableByteCount is not important here. All work with Excel doing in the method workWithWorkbook2. Many times I have rewritten this method, I tried to work with XSSF (usermodel) SXSSF and XSSFReader (eventusermodel) and here are the results:</p>
  
  <ol>
  <li><p>XSSF - with this implementation of Apache POI my program ends with OutOfMemoryException. I tried to set up more usable memory (-Xmx2g and -Xmx3g), but it was not enough. Program used at the beginning of around 1.5 GB RAM, after first Excel it was about 1.7 GB of RAM, but then opened a larger Excel (about 40 megabytes) and the memory s over 2.8 gigabytes of RAM used.</p></li>
  <li><p>SXSSF - I found only examples of creating new files. But that's not what I need. I need to read the file, analyze it and remove blank lines.</p></li>
  <li><p>XSSFReader - Here I had some problems with XMLParser (currently still have). Nevertheless, this is only a reader, I need to be able to edit a file.</p></li>
  </ol>
  
  <p>I would like to show you my method workWithWorkbook2, but it contains only the implementation of XSSFReader because of my experiments with different implementations of Apache POI.
  So, there is a method workWithWorkbook2 with many commented lines to let you see my attempts:</p>
  
  <pre><code>public static long workWithWorkbook2(File fileEntry) {
  
          if (fileEntry.getName().endsWith(".xltx")) {
              System.out.println("== Jump over .xltx file ==");
          }
          else {
  
              //FileInputStream file = new FileInputStream(new File(fileEntry.getPath()));
  
              //Workbook wb = WorkbookFactory.create(new File(fileEntry.getPath()));
  
              //SXSSFWorkbook workbook = new SXSSFWorkbook(wb, 1000);
  
              //workbook.setCompressTempFiles(true);
  
              OPCPackage pkg = OPCPackage.open(fileEntry.getPath());
  
              XSSFReader r = new XSSFReader(pkg);
  
              SharedStringsTable sst = r.getSharedStringsTable();
  
              XMLReader parser = fetchSheetParser(sst);
  
              Iterator&lt;InputStream&gt; sheets = r.getSheetsData();
  
              while(sheets.hasNext()) {
                  InputStream sheet = sheets.next();
                  InputSource sheetSource = new InputSource(sheet);
                  parser.parse(sheetSource);
                  sheet.close();
              }
  
  //            FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
  //
  //            for (int i = 0; i&lt;wb.getNumberOfSheets(); i++) {
  //
  //                Sheet sheet = wb.getSheetAt(i);
  //
  //                Iterator&lt;Row&gt; rowIterator = sheet.iterator();
  //                while (rowIterator.hasNext()) {
  //
  //                    Row row = rowIterator.next();
  //                    Iterator&lt;Cell&gt; cellIterator = row.cellIterator();
  //
  //                    while (cellIterator.hasNext()) {
  //
  //                        Cell cell = cellIterator.next();
  //
  //                        if (cell == null) {
  //                            System.out.println(row.getRowNum() + " NULL, " + cell.getCellType() + ", " + cell.getColumnIndex());
  //                        }
  //                        else {
  //
  //                            switch (evaluator.evaluateInCell(cell).getCellType()) 
  //                            {
  //                                case Cell.CELL_TYPE_STRING:
  //                                    //System.out.println(row.getRowNum() + " String, " + cell.getCellType() + ", " + cell.getColumnIndex() + ", " + cell.getStringCellValue());
  //                                    break;
  //                                case Cell.CELL_TYPE_NUMERIC:
  //                                    //System.out.println(row.getRowNum() + " Numeric, " + cell.getCellType() + ", " + cell.getColumnIndex() + ", " + cell.getNumericCellValue());
  //                                    break;
  //                                case Cell.CELL_TYPE_FORMULA:
  //                                    //System.out.println(row.getRowNum() + " Formula, " + cell.getCellType() + ", " + cell.getColumnIndex() + ", " + cell.getCellFormula());
  //                                    break;
  //                                case Cell.CELL_TYPE_BOOLEAN:
  //                                    //System.out.println(row.getRowNum() + " Boolean, " + cell.getCellType() + ", " + cell.getColumnIndex() + ", " + cell.getBooleanCellValue());
  //                                    break;
  //                                case Cell.CELL_TYPE_BLANK:
  //                                    //System.out.println(row.getRowNum() + " Empty, " + cell.getCellType() + ", " + cell.getColumnIndex());
  //                                    break;
  //                                case Cell.CELL_TYPE_ERROR:
  //                                    //System.out.println(row.getRowNum() + " Error, " + cell.getCellType() + ", " + cell.getColumnIndex());
  //                                    break;
  //                                default:
  //                                    //System.out.println(row.getRowNum() + " WTF type?, " + cell.getCellType() + ", " + cell.getColumnIndex());
  //                                    break;
  //                            }
  //
  //                        }
  //                    }
  //
  //                } 
  //
  //            }
  //            
  //            evaluator.clearAllCachedResultValues();
  
              //file.close();
  
          }
          return 0;
      }
  </code></pre>
  
  <p>Here you can see: </p>
  
  <ol>
  <li>In the first part of the code is commented out implementation SXSSF similar way I had implemented XSSF before.</li>
  <li>Followed by implementation of XSSFReader, my last attempt. But I think that this is not the way to go.</li>
  <li>The following big block of code commented out - my original implementation of XSSF analyzing Excel file.</li>
  </ol>
  
  <p>Please can you help with this task? Reports in subdirectories is about 150, and definitely I do not want go through manually. There are Excel in the hundreds of kilobytes, but also tens of megabytes.</p>
  
  <p>Thx!</p>