Safe way to export cell value as String

Question

What is the safe way to get cell value as String using Apache POI?

For some non-empty cells I get the following exception:

com.monitorjbl.xlsx.exceptions.NotSupportedException: null
    at com.monitorjbl.xlsx.impl.StreamingCell.getSheet(StreamingCell.java:330)
    at org.apache.poi.ss.usermodel.DataFormatter.isDate1904(DataFormatter.java:313)
    at org.apache.poi.ss.usermodel.DataFormatter.getFormat(DataFormatter.java:309)
    at org.apache.poi.ss.usermodel.DataFormatter.getFormattedNumberString(DataFormatter.java:868)
    at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:1021)
    at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:971)
    at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:950)
...

I use the code below to extract cell values as text at the moment:

import org.apache.poi.ss.usermodel.*;

private static DataFormatter formatter = new DataFormatter();

private static String formatCellValue(Cell cell) {
    return formatter.formatCellValue(cell);
}

The javadoc of the method says:

Returns the formatted value of a cell as a String regardless of the cell type. If the Excel format pattern cannot be parsed then the cell value will be formatted using a default format. When passed a null or blank cell, this method will return an empty String (""). Formulas in formula type cells will not be evaluated.

Well your cell could be null? Does this help https://stackoverflow.com/questions/5578535/get-cell-value-from-excel-sheet-with-apache-poi — JCompetence, Feb 26 '21 at 12:23
@SusanMustafa No. The cell isn't null. I saw it during debugging. — ka3ak, Feb 26 '21 at 12:25
Seems your `Cell` is a `com.monitorjbl.xlsx.impl.StreamingCell`. That is not the same as a `apache poi` `XSSFCell` or `HSSFCell`. But `DataFormatter` needs a `apache poi` `Cell` to work with. See https://github.com/monitorjbl/excel-streaming-reader#usage for how to use this streaming reader. — Axel Richter, Feb 26 '21 at 12:34
@AxelRichter Thanks. I'll take a look at it. But it's strange that it works with the same kind of cell in other parts of code and Excel document. — ka3ak, Feb 26 '21 at 12:50
@AxelRichter Ok. I see now. It is safe for a StreamingCell to call getStringCellValue() on it which would be not safe for a POI cell. Thanks. You can post your answer and I'll accept it. — ka3ak, Feb 26 '21 at 12:55

ka3ak · Answer 1 · 2021-02-26T19:43:19.820

2

The solution was to add the check to the mentioned method and call getStringCellValue() on StreamingCell which is always safe in contrast to calling it on a POI cell.

private static String formatCellValue(Cell cell) {
    if (cell instanceof com.monitorjbl.xlsx.impl.StreamingCell) {
        return cell.getStringCellValue();
    }

    return formatter.formatCellValue(cell);
}

edited Feb 26 '21 at 19:43

answered Feb 26 '21 at 16:43

ka3ak

2,435
2
30
57

score 1 · Accepted Answer · answered Feb 26 '21 at 13:04

1

Seems your Cell is a com.monitorjbl.xlsx.impl.StreamingCell. That is not the same as a apache poi XSSFCell or HSSFCell. But DataFormatter needs a apache poi Cell to work with. See https://github.com/monitorjbl/excel-streaming-reader#usage for how to use this streaming reader.

The com.monitorjbl.xlsx.impl.StreamingCell implements org.apache.poi.ss.usermodel.Cell. But for many of the methods the implementation is simply throw new NotSupportedException(). So it works only for the methods it implements correctly.

But as of the current version it implements Cell.getSheet. So your error leads to the assumption that you are not using the current version of Excel Streaming Reader.

answered Feb 26 '21 at 13:04

Axel Richter

56,077
6
60
87

I'm using the current verion - 2.1.0 – ka3ak Feb 26 '21 at 19:40
1

https://mvnrepository.com/artifact/com.github.pjfanning/excel-streaming-reader – Axel Richter Feb 27 '21 at 07:00
I'm using https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer or this one https://github.com/monitorjbl/excel-streaming-reader/releases When I use the latest dependency of your link I get lots of compile errors. For example `com.monitorjbl.xlsx.StreamingReader` isn't found any more... Is it the same library? If so, it doesn't seem to be very backwards compatible. Sorry, I've no time to resolve all the errors. I've to deliver a working solution quickly. So minimal changes to existing source code a highly desired. – ka3ak Feb 27 '21 at 20:59
@ka3ak: Yes, you are correct. Seems to be two different libraries. I was confused because https://github.com/monitorjbl/excel-streaming-reader/blob/master/src/main/java/com/monitorjbl/xlsx/impl/StreamingCell.java#L274 shows implementation of `getSheet`. So source code on `github` seems different from binary `jar` on `maven`. – Axel Richter Feb 28 '21 at 06:36

Safe way to export cell value as String

2 Answers2