Issue with reading blank cells in large xlsx files using apache POI/monitorjbl/excel-streaming-reader

2
votes

I am working on requirement where I need to read large xlsx file contains more than one million records. The apache POI is not memory efficient when reading large files .Hence I am using below API which adds

https://github.com/monitorjbl/excel-streaming-reader which is a wrapper around that streaming API while preserving the syntax of the standard POI API. Everything working fine except reading blank cells in the row. The above API throwing null pointer if cell is blank

       for(int i=0; i<=expectedColumns-1; i++) {
              Cell cell = row.getCell(i);
    switch (cell.getCellType()) {
    }
            }
java.lang.NullPointerException
at test.XLSXToCSVConverterStreamer.xlsx(XLSXToCSVConverterStreamer.java:67)
at test.XLSXToCSVConverterStreamer.main(XLSXToCSVConverterStreamer.java:164)

if a cell in row is null it is throwing null pointer at Switch case i.e cell.getCelltype. I have modified code to read null cells as blank cells but its not supporting

    for(int i=0; i<=expectedColumns-1; i++) {
      //Cell cell = row.getCell(i);
     Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
switch (cell.getCellType()) {
}
    }

if I use Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK) to read empty cells as blank I am getting below issue. Kindly help me in resolving this

com.monitorjbl.xlsx.exceptions.NotSupportedException
at com.monitorjbl.xlsx.impl.StreamingRow.getCell(StreamingRow.java:108)
1
javaapache-poi
try this if(Cell.CELL_TYPE_BLANK) Cell.setValue(" "); – manikant gautam
Why not just add a if (cell != null) before your switch statement? – Gagravarr
I am converting xlsx to csv . if any cell is null I have to consider that as blank and write blank string("") as csv data for that field. – Kiran
Agree with Gagravarr. If you want to write "" if the cell is null, you can do it in ELSE block. – The Guest

1 Answers

0
votes

Lot of methods are not supported by the streaming excel But It gives advantage of reading large excel files. You can read the blank cells from a row as follows (use Streaming Excel Reader v1.1.0)

boolean flag = false;
int lastcolno = row.getLastCellNum();

for (colno = 0; colno < lastcolno; colno++) {
    colFlag = isColumnEmpty(row, colno);

    if (flag == true)
        break;
}

if (colFlag == true) {
     System.out.println("In index row, column no: "
            + (colno + 1) + " is empty");
}

public static boolean isColumnEmpty(Row row, int colno) {
     Cell c = row.getCell(colno);
     if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK)
            return true;
return false;
}