monitorjbl / excel-streaming-reader Goto Github PK

final File source = new File("\some\file.xlsx");
final File tmp = Files.createTempFile("tmp-", ".xlsx").toFile();
FileUtils.copyFile(source, tmp); //this is Apache-commons-IO but it doesnt' really matter how we produced the temp file, the problem is the same
System.out.println(tmp.getCanonicalPath());
try(StreamingReader reader = StreamingReader.builder().sheetIndex(0).read(tmp)){
    for (Row row : reader) {
        //do something
    }
}
Files.delete(tmp.toPath()); //throws an exception

Row row=rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
currentCell = cellIterator.next();
parthyperlink=currentCell.getHyperlink().getAddress();
String label=currentCell.getHyperlink().getLabel();

[ERROR]     at org.slf4j.LoggerFactory.getLogger(LoggerFactory.java:305)
[ERROR]     at com.monitorjbl.xlsx.StreamingReader.<clinit>(StreamingReader.java:59)
[ERROR]     at com.monitorjbl.xlsx.StreamingReader$Builder.findSheet(StreamingReader.java:457)
[ERROR]     at com.monitorjbl.xlsx.StreamingReader$Builder.read(StreamingReader.java:418)
[ERROR]     at com.utilitiessavings.usavappv7.server.reader.ParseBgPrices.parseFromFile(ParseBgPrices.java:63)
[ERROR]     at com.utilitiessavings.usavappv7.server.handler.parse.ParseBgPriceBookHandler.execute(ParseBgPriceBookHandler.java:75)
[ERROR]     at com.utilitiessavings.usavappv7.server.handler.parse.ParseBgPriceBookHandler.execute(ParseBgPriceBookHandler.java:33)
[ERROR]     at com.gwtplatform.dispatch.rpc.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:154)
[ERROR]     at com.gwtplatform.dispatch.rpc.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:110)
[ERROR]     at com.gwtplatform.dispatch.rpc.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:87)
[ERROR]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[ERROR]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
[ERROR]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
[ERROR]     at java.lang.reflect.Method.invoke(Method.java:497)
[ERROR]     at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:587)
[ERROR]     ... 54 more
[ERROR] Caused by: java.security.AccessControlException: access denied ("java.lang.RuntimePermission" "createSecurityManager")
[ERROR]     at java.security.AccessControlContext.checkPermission(AccessControlContext.java:457)

 StreamingReader reader = new StreamingReader.Builder()
                .rowCacheSize(100)
                .bufferSize(4096)
                .sheetIndex(0)
                .read(file);

java.lang.AbstractMethodError: org.apache.xerces.dom.DocumentImpl.getXmlStandalone()Z
at com.sun.org.apache.xalan.internal.xsltc.trax.DOM2TO.setDocumentInfo(DOM2TO.java:377)
at com.sun.org.apache.xalan.internal.xsltc.trax.DOM2TO.parse(DOM2TO.java:131)
at com.sun.org.apache.xalan.internal.xsltc.trax.DOM2TO.parse(DOM2TO.java:98)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.transformIdentity(TransformerImpl.java:699)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.transform(TransformerImpl.java:743)
at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerImpl.transform(TransformerImpl.java:357)
at org.apache.poi.openxml4j.opc.StreamHelper.saveXmlInStream(StreamHelper.java:80)
at org.apache.poi.openxml4j.opc.internal.marshallers.ZipPartMarshaller.marshallRelationshipPart(ZipPartMarshaller.java:174)
at org.apache.poi.openxml4j.opc.ZipPackage.saveImpl(ZipPackage.java:464)
at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1441)
at org.apache.poi.openxml4j.opc.OPCPackage.save(OPCPackage.java:1426)
at org.apache.poi.openxml4j.opc.ZipPackage.closeImpl(ZipPackage.java:351)
at org.apache.poi.openxml4j.opc.OPCPackage.close(OPCPackage.java:426)
at com.monitorjbl.xlsx.StreamingReaderTest.testClosingFiles(StreamingReaderTest.java:459)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)
at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)
at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)
at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)
at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)
at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)
at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)

private void testExcel(String fileName) {
        ClassLoader classLoader = getClass().getClassLoader();
        File file = new File(classLoader.getResource(fileName).getFile());
        Workbook workbook = StreamingReader.builder()
                .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
                .bufferSize(4096)     // buffer size to use when reading InputStream to file (defaults to 1024)
                .open(file);
    }

java.lang.NoClassDefFoundError: org/apache/poi/POIXMLTypeLoader
	at org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument$Factory.parse(Unknown Source)
	at org.apache.poi.xssf.model.SharedStringsTable.readFrom(SharedStringsTable.java:119)
	at org.apache.poi.xssf.model.SharedStringsTable.<init>(SharedStringsTable.java:106)
	at org.apache.poi.xssf.eventusermodel.XSSFReader.getSharedStringsTable(XSSFReader.java:82)
	at com.monitorjbl.xlsx.impl.StreamingWorkbookReader.init(StreamingWorkbookReader.java:117)
	at com.monitorjbl.xlsx.StreamingReader$Builder.open(StreamingReader.java:278)

    boolean foundSheet = false;
    int hdrRowPos = -1;
    int sheetPos = -1;
    int rowLimit = 10;
    int count = 0;
    int totalRowCounter = 0;
    StreamingReader reader = null;
    Iterator<Row> rowIterator = null;
    Row headerRow = null;
    try {
        status.init();
        InputStream is = new FileInputStream(excelFile);

        // ########################################### Find sheet and header row ###############################
        for (int i = 0; i < rowLimit && !foundSheet; i++) {
            Workbook workbook = StreamingReader.builder()
                    .rowCacheSize(100)    // number of rows to keep in memory (defaults to 10)
                    .bufferSize(16384)     // buffer size to use when reading InputStream to file (defaults to 1024)
                    .sheetIndex(0)        // index of sheet to use (defaults to 0)
                    .open(is);   
            Sheet  sheet= workbook.getSheetAt(0);
            rowIterator = sheet.iterator();

            while (rowIterator.hasNext() && count < rowLimit && !foundSheet) {
                Row r = rowIterator.next();
                count = r.getRowNum();
                if (checkIfHeaderKeyExists(r)) {
                    foundSheet = true;
                    sheetPos = i;
                    headerRow = r;
                    hdrRowPos = count;
                    log.warn("For file: " + excelFile.getName() + " header key is found on Header position: " + hdrRowPos);
                }

            }
        }
        // ############################################################################

        if (hdrRowPos == -1 || sheetPos == -1) {
            throw new IllegalArgumentException("Given File: " + excelFile + " is not a Valid file");
        }

        getHeaders(excelFile, header, headerRow);

        status.setLoadedHeader();
        Thread.sleep(5000);

        int emptyRowCount = 0;

        status.setReading();
        while (rowIterator.hasNext()) {
            Row r = rowIterator.next();

            if (r == null || isEmptyRowStrm(r, header)) {
                log.warn("For file: " + excelFile + ", row: " + r.getRowNum() + " is empty");
                emptyRowCount++;
                if (emptyRowCount > MAX_EMPTY_ROW) {
                    log.warn("finsihed to end of excel file");
                    break;
                }
                continue;
            }
            if (r.getRowNum() % 1000 == 0) {
                // log.info("Added Row: " + r.getRowNum());
                //Thread.sleep(100);
            }
            if (r != null) {
                totalRowCounter++;
                rows.add(r);
                emptyRowCount = 0;
            }

        }
        Thread.sleep(1000);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        log.info("For file: " + excelFile + " Total Number of Rows read: " + totalRowCounter);
        status.setDone();
    }

}

//This method is to update the informations of object nota that was already persisted at the database
public void parserPlanilhasContrib(File f, Session s) throws IOException, InvalidFormatException
    {
        int n = 0;
        DAO d = new DAO();

        while(n<2)
        {
            InputStream inputStream = new FileInputStream(f); //Instancia o imputStream que vai receber o arquivo de entrada
            //Prepare the reader of the sheet
            StreamingReader reader = StreamingReader.builder().rowCacheSize(50).bufferSize(2048).sheetIndex(n).read(inputStream);

            if(n==0) //if it is the first sheet...
            {
                for(Row r : reader)
                {
                    int numLinha = r.getRowNum();

                    if(numLinha >= 1) //Ignores the first line, that contains the header
                    {
                        Nota nota = new Nota();

                        for(Cell c : r)
                        {
                            if(c.getColumnIndex()==0)
                            {
                                String id = c.getStringCellValue().replace(".","") //here i get the value normally;

                                int idNota = Integer.parseInt(id);

                                nota = d.pegaNota(idNota); //takes the nota that have the same id value of the cell value
                            }
                            else if(c.getColumnIndex()==5)
                            {
                                nota.setcUF(c.getStringCellValue()); // but from here, and ahead, in the next cells, the value that I get is blank

                                System.out.println("cuf: " + c.getStringCellValue()); 
                            }
                            else if(c.getColumnIndex()==6)
                            {
                                nota.setNatOp(c.getStringCellValue());

                                System.out.println("natop: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==7)
                            {
                                nota.setIndPag(c.getStringCellValue());

                                System.out.println("indpag: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==8)
                            {
                                nota.setModulo(c.getStringCellValue());

                                System.out.println("modulo: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==9)
                            {
                                nota.setdEmi(c.getStringCellValue());

                                System.out.println("demi: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==10)
                            {
                                nota.setdSaiEnt(c.getStringCellValue());

                                System.out.println("dsaient: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==11)
                            {
                                nota.setTpNF(c.getStringCellValue());

                                System.out.println("tpnf: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==12)
                            {
                                nota.setTpEmis(c.getStringCellValue());

                                System.out.println("tpemis: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==13)
                            {
                                nota.setTpAmb(c.getStringCellValue());

                                System.out.println("tpamb: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==14)
                            {
                                nota.setFinNFe(c.getStringCellValue());

                                System.out.println("finnfe: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==17)
                            {
                                nota.setJustificativa(c.getStringCellValue());

                                System.out.println("justificativa: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==18)
                            {
                                nota.setvBC(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==19)
                            {
                                nota.setvICMS(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==20)
                            {
                                nota.setvBCST(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==21)
                            {
                                nota.setvST(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==22)
                            {
                                nota.setvProd(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==23)
                            {
                                nota.setvFrete(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==24)
                            {
                                nota.setvSeg(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==25)
                            {
                                nota.setvDesc(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==26)
                            {
                                nota.setvII(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==27)
                            {
                                nota.setvIPI(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==28)
                            {
                                nota.setvPIS(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==29)
                            {
                                nota.setvCOFINS(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==30)
                            {
                                nota.setvOutro(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==31)
                            {
                                nota.setvNF(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==32)
                            {
                                nota.setvTotTrib(c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==33)
                            {
                                nota.setIndFrete(c.getStringCellValue());

                                System.out.println("indfrete: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==34)
                            {
                                nota.setxMotivo(c.getStringCellValue());

                                System.out.println("xmotivo: " + c.getStringCellValue());
                            }
                            else if(c.getColumnIndex()==35)
                            {
                                nota.setInfoAd(c.getStringCellValue());

                                System.out.println("infoad: " + c.getStringCellValue());
                            }
                        }

                        s.update(nota);
                        s.flush();
                        s.clear();
                    }
                }
            }
            if(n==1) //Se for a sheet 2, correspondente ao item
            {
               //almost the same of the last sheet but for other type of object...
            }

            n++;
        }
    }

  /**
   * Gets the index of the last cell contained in this row <b>PLUS ONE</b>.
   * 
   * @return short representing the last logical cell in the row <b>PLUS ONE</b>,
   *   or -1 if the row does not contain any cells.
   */
  @Override
  public short getLastCellNum() {
    return (short) (cellMap.size() == 0 ? -1 : cellMap.size() + 1);
  }

    private int numCellsInRow(Row row) {
        if (row instanceof StreamingRow) {
            List<Integer> colIndexes = new ArrayList<>(((StreamingRow) row).getCellMap().keySet());
            if (colIndexes.size() < 1) {
                return 0;
            }
            colIndexes.sort(null);
            return colIndexes.get(colIndexes.size()-1) + 1;
        }
        else {
            // HSSFRow.getLastCellNum() returns the 1-based index of the last cell, or -1
            return (row.getLastCellNum() < 1) ? 0 : row.getLastCellNum();
        }
    }

for (Row r : reader) {
    for (Cell c : r) {
        //... show blank cell here.
    }
}