apache POI reads text column as numeric
<p>I'm working on a function where I have to store numbers received via uploading an excel file. It's written in Java, using apache poi library, under Spring framework (which is irrelevant).</p>

<p>The file which I'm trying to upload (Note that the column has already been set to Text) :</p>

<p><a href="https://i.stack.imgur.com/0AfB0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0AfB0.png" alt="enter image description here"></a></p>

<p>Code is as follow :</p>

<pre><code>// function accepts "MultipartFile inputFile"
InputStream is = inputFile.getInputStream();
StreamingReader reader = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).sheetIndex(0)
                        .read(is);
for (Row row : reader) {
    System.out.println("Reading next row.");
    System.out.println("row[0] is of type " + row.getCell(0).getCellType());
    Cell cell = row.getCell(0);
    String value = "";
    if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
        value = cell.getStringCellValue().replaceAll("[\\D]", "");
    } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        value = NumberToTextConverter.toText(cell.getNumericCellValue());
    }
    System.out.println("Value is " + value);
}
</code></pre>

<p>And I'm getting below output :</p>

<pre><code>Reading next row.
row[0] is of type 0 // Which is equals to Cell.CELL_TYPE_NUMERIC
Value is 166609999
</code></pre>

<p><strong>Issue is that I'd require to read it as '0166609999' instead of '166609999'</strong>, strangely this only happens to xlsx file, if I save it as xls format and re-upload the file, I have no issue with detecting its cell type. Any ideas?</p>

<p><strong>Edited for being marked as duplication:</strong></p>

<ul>
<li>Given answer <a href="https://stackoverflow.com/a/19401902/1131470">https://stackoverflow.com/a/19401902/1131470</a> uses DataFormatter class which is not supported when we are streaming the sheet, as the Cell object retrieved is going to be a StreamingCell object, which throws an exception if we make a call to getCellStyle() function.</li>
</ul>

<p><strong>As of 29th Mar 2016</strong></p>

<p>Seems that the class <a href="https://github.com/monitorjbl/excel-streaming-reader/blob/master/src/main/java/com/monitorjbl/xlsx/impl/StreamingCell.java" rel="nofollow noreferrer">StreamingCell</a> does not support DataFormatter, where DataFormatter is the only class available at the moment to grab what Excel displays. So current solution would be sticking back to reading the whole Excel file into memory. If anyone found an answer in future you may please post an answer here, I'd greatly appreciate that, as current solution is an absolutely terrible one.</p>

<p><strong>As of 31st Mar 2016</strong></p>

<p>Special thanks to Axel who pointed out its library version issue, updating streamer jar file to 0.2.12 solves the problem. Thanks!</p>