Reading an xlsx file using a POI (SXSSFWorkbook)

Question

I am trying to run my first tests of reading a large xlsx file using a POI, but for a simple test with a small file, I cannot show the cell value.

Someone can tell me what my mistake is. All suggestions are welcome. Thanks.

Test.java:

import java.io.File; import java.io.FileInputStream; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Test { public static void main(String[] args) throws Throwable { File file = new File("/tmp/test.xlsx"); OPCPackage pkg = OPCPackage.open(new FileInputStream(file.getAbsolutePath())); XSSFWorkbook xssfwb = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(xssfwb, 100); Sheet sh = wb.getSheet("Hola"); System.out.println("Name: "+sh.getSheetName()); // Line 19 System.out.println("Val: "+sh.getRow(1).getCell(1).getStringCellValue()); // Line 20 } } 

Result:

 Name: Hola Exception in thread "main" java.lang.NullPointerException at Test.main(Test.java:20) 

test.xlsx:

Answer 1

Consult a similar question. SXSSFWorkBook writes only, it does not support reading.

To read low-memory .xlsx files, you should look at the XSSF and SAX EventModel : Gagravarr documentation

If memory is not a problem, you can use XSSFSheet, for example,

  File file = new File("D:/temp/test.xlsx"); FileInputStream fis = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sh = wb.getSheet("Hola"); System.out.println(sh.getLastRowNum()); System.out.println("Name: "+sh.getSheetName()); Row row = sh.getRow(1); System.out.println(row.getRowNum()); System.out.println("Val: "+sh.getRow(1).getCell(1).getStringCellValue()); 

Answer 2

I also ran into the same OOM problem while parsing the xlsx file ... after two days of struggle, I finally found code that was really perfect,

This code is based on sjxlsx. It reads xlsx and is stored in an HSSF sheet.

  // read the xlsx file SimpleXLSXWorkbook = new SimpleXLSXWorkbook(new File("C:/test.xlsx")); HSSFWorkbook hsfWorkbook = new HSSFWorkbook(); org.apache.poi.ss.usermodel.Sheet hsfSheet = hsfWorkbook.createSheet(); Sheet sheetToRead = workbook.getSheet(0, false); SheetRowReader reader = sheetToRead.newReader(); Cell[] row; int rowPos = 0; while ((row = reader.readRow()) != null) { org.apache.poi.ss.usermodel.Row hfsRow = hsfSheet.createRow(rowPos); int cellPos = 0; for (Cell cell : row) { if(cell != null){ org.apache.poi.ss.usermodel.Cell hfsCell = hfsRow.createCell(cellPos); hfsCell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING); hfsCell.setCellValue(cell.getValue()); } cellPos++; } rowPos++; } return hsfSheet;