0

I am using apache poi for excel read and write. I am able to use it for small size files properly. But now my input file size is >150mb with 200k+ records. Now when the use the same code for reading the file my thread just keeps on stepping and never returns the workbook to perform operations

final thread run debug log:

2022-11-29 17:04:33.541 DEBUG 33576 --- [ecutor-thread-8] o.a.p.o.o.PackageRelationshipCollection  : Parsing relationship: /xl/pivotCache/_rels/pivotCacheDefinition1.xml.rels
2022-11-29 17:04:33.604 DEBUG 33576 --- [ecutor-thread-8] o.a.p.o.o.PackageRelationshipCollection  : Parsing relationship: /xl/pivotTables/_rels/pivotTable1.xml.rels
2022-11-29 17:04:33.631 DEBUG 33576 --- [ecutor-thread-8] o.a.p.o.o.PackageRelationshipCollection  : Parsing relationship: /xl/_rels/workbook.xml.rels
2022-11-29 17:04:33.646 DEBUG 33576 --- [ecutor-thread-8] o.a.p.o.o.PackageRelationshipCollection  : Parsing relationship: /xl/worksheets/_rels/sheet1.xml.rels
2022-11-29 17:04:33.657 DEBUG 33576 --- [ecutor-thread-8] o.a.p.o.o.PackageRelationshipCollection  : Parsing relationship: /xl/worksheets/_rels/sheet2.xml.rels
2022-11-29 17:04:33.672 DEBUG 33576 --- [ecutor-thread-8] o.a.p.o.o.PackageRelationshipCollection  : Parsing relationship: /_rels/.rels
2022-11-29 17:04:47.661 DEBUG 33576 --- [ecutor-thread-8] org.apache.poi.ooxml.POIXMLFactory       : using default POIXMLDocumentPart for http://schemas.openxmlformats.org/officeDocument/2006/relationships/printerSettings
2022-11-29 17:04:47.666 DEBUG 33576 --- [ecutor-thread-8] org.apache.poi.ooxml.POIXMLFactory       : using default POIXMLDocumentPart for http://schemas.openxmlformats.org/officeDocument/2006/relationships/printerSettings

dependencies used:

<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>5.2.3</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>5.2.3</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml-schemas</artifactId>
    <version>5.2.3</version>
</dependency>

solutions tried:

Workbook workbook = new XSSFWorkbook(file);

SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook(file));

Workbook workbook = WorkbookFactory.create(f);

cant use this solution: because of it doesnt consider the cells which are blank hence having issue in reading columns and also because of some itss company policies

InputStream is = new FileInputStream(new File(uploadfilePath.toString()));
                Workbook workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(is)

can anyone please help me with some solutions for creating the workbook successfully

tara kantheti
  • 1
  • 1
  • What does this mean? `cant use this solution: because of some itss` - if you don't like excel-streaming-reader, you could try https://github.com/dhatim/fastexcel – PJ Fanning Nov 30 '22 at 00:21
  • Hi @PJ Fanning I can't use other solutions except for making apache poi to work according to my company norms, so can you help with any other possible solutions – tara kantheti Nov 30 '22 at 04:50
  • If you absolutely have to use POI and cannot use easier solutions - try https://github.com/dearshor/poi-examples/blob/master/src/main/java/poi/xssf/eventusermodel/examples/FromHowTo.java – PJ Fanning Nov 30 '22 at 10:32

0 Answers0