CODEWITHSUNDEEP
javaarraysexcelapache-poioutput
Toto D
Toto D

Reputation: 109

Wrong value / Number stored as Text - Apache POI

I'm working on an application which get data from excel file and output one excel after few operations. I got 2 issues, the first one is that one :

On my main excel file, I have this value 170299024773 and in Excel it's appear like that 1.703E+11 and then on the output file I get 1.70299E+11 and the exact value is 170299000000. So as you see, it's not the value of the main file. Morever, for this cell, I have the error "Number stored as text", and then I fix this error from Excel I got 1.70E+11.

I saw few tips about the Nember stored a text and in particular DataFormatter but it's doesn't change anything in my case

The code I use to get data of excel sheet to array :

private static String[][] sheetToArray(org.apache.poi.ss.usermodel.Sheet sheet, int nbCol){
    String[][] tmp = new String[sheet.getLastRowNum()+1][nbCol];

        for (int i = 0; i < sheet.getLastRowNum()+1; i++) { //Config1 to array
            Row row = sheet.getRow(i);
            DataFormatter df = new DataFormatter();
                for (int j = 0; j < nbCol; j++) {
                    Cell cellC1 = row.getCell(j);
                     tmp[i][j] = df.formatCellValue(cellC1);
                }
            }
        return tmp;
    }

Many thanks in advance guys...

Edit : I fix the wrong value adding 

switch (cellD2.getCellTypeEnum()){
       case STRING:
             tmp[i][j] = df.formatCellValue(cellC1);
             break;
        case NUMERIC:
             tmp[i][j] = String.valueOf(cellC1.getNumericCellValue());
        }
 }

But now, I have some decimal value added to my numeric state... To solve it I should use DataFormatter but in that case I will lost the right data

Upvotes: 0

Views: 2931

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

When DataFormatter is used there is no need determining the cell types. DataFormatter will always get the values as String and as they are shown in the Excel cells (not in all cases as shown in edit bar). So if the cell shows 1,70299E+11 then DataFormatter also will get "1,70299E+11". If this is not the wanted value, then DataFormatter must be told what else it shall do than interpreting cell formats as Excel will do.

For example the value 170299024773 will be shown as 1,70299E+11 in excelcells if cell format General is used. If this is unwanted, then either the cell format must be changed in Excel. Using number format 0 for example, what means: show integer values as long this is possible. Or DataFormatter must be told that it shall interpreting cell format General different than Excel.

The following shows how telling DataFormatter that it shall interpreting cell format General as #.###############.

Excel:

enter image description here

Code:

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

import java.io.FileInputStream;

class ExcelDataformatterLikeCalcExample {

 public static void main(String[] args) throws Exception {

  Workbook wb  = WorkbookFactory.create(new FileInputStream("ExcelExample.xlsx"));

  DataFormatter df = new DataFormatter();

  df.addFormat("General", new java.text.DecimalFormat("#.###############"));

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {

     String value = df.formatCellValue(cell);
     System.out.println(value);

   }
  }
  wb.close();

 }

}

Result:

axel@arichter:~/Dokumente/JAVA/poi/poi-4.0.0$ java -cp .:./*:./lib/*:./ooxml-lib/* ExcelDataformatterLikeCalcExample 
170299024773

Upvotes: 2

Related Questions