3

I need to upload xslx file having more than 65536 lines with 25 columns in a single sheet and write in to DB table. I use XSSF Reader event handler API and OPCpacakage classes for both upload and read. When a file contains 65536 rows or less it works fine, but when it exceeds 65536 rows, it's getting corrupted without any exception and table gets loaded with many rows missing.

Using: 

POI 3.9 jars  
poi 3.9  
poi xml 3.9  
poi ooxml schemas 3.9  
ooxml_schemas 1.1  
xmlbeans 2.3

Is there a limitation on the number of rows that these libraries can process?

Chubas
  • 17,823
  • 4
  • 48
  • 48
ms_guruvai
  • 31
  • 1
  • 2
  • 1
    Can you just split your XSLX into multiple, smaller files and process them independently (eg. 1000 to 65000 rows per file)? – ashes999 Oct 17 '14 at 19:35
  • XSSF (for .xlsx) can and does handle files of up to 1,048,576 rows, while HSSF (for .xls) is limited to just 65k. Are you sure you're not doing something silly like using a `short` instead of an `int` to track row numbers? – Gagravarr Oct 17 '14 at 23:34
  • No we use int type only to track the row numbers. In addition, w – ms_guruvai Oct 18 '14 at 07:13
  • in addition I use XSSF parser to handle the sheet and start, end element event methods to proecess each cell as below XMLReader parser = fetchSheetParser(sst); Iterator sheets = r.getSheetsData(); while(sheets.hasNext()) { Index=-1; InputStream sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); sheet.close(); } – ms_guruvai Oct 18 '14 at 07:36
  • Can anybody tell that keeping both libraries: 1)poi ooxml schemas 3.9 2)ooxml_schemas 1.1 in classpath make any issue what I am facing – ms_guruvai Oct 20 '14 at 12:05

1 Answers1

0

What type of workbook are you using to parse your file ?

I see you use poi ooxml schemas lib , which offers a stream oriented version of poi

Since 3.8-beta3, POI provides a low-memory footprint SXSSF API built on top of XSSF. SXSSF is an API-compatible streaming extension of XSSF to be used when very large spreadsheets have to be produced, and heap space is limited.

In that case i suggest you to try with SXSSFWorkbook and see if some lines still missing

Alexis Delahaye
  • 644
  • 5
  • 18