How to read numeric values from excel in java?

Question

I have a code to read a xls file in java.
But the problem is that when I am read the numeric value that time it gives me wrong format value. My code is

String fname = "D:/Vijay/BRS_docs/10168/19.11.2014/19.11.2014/Bank/Debit Open Item File/INF 21 Case.xls";
FileInputStream fis = new FileInputStream(fname);
        HSSFWorkbook wb = new HSSFWorkbook(fis);
        HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
        FormulaEvaluator formulaEval = wb.getCreationHelper().createFormulaEvaluator();
        fis = new FileInputStream(fname);
        Iterator rowIter = sheet.rowIterator();
        while (rowIter.hasNext()) {
            HSSFRow myRow = (HSSFRow) rowIter.next();
            Iterator cellIter = myRow.cellIterator();
                while (cellIter.hasNext()) {
                    String cellvalue = "";
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                        cellvalue = ""+ formulaEval.evaluate(myCell).formatAsString();
                        System.out.println("cellvalue--" + cellvalue);
                }
        }

I have value 119710179 this is numeric but when I am syso that time it display cellvalue--1.19710179E8

I used this code also but same numeric fomat

Iterator cellIter = myRow.cellIterator();

                while (cellIter.hasNext()) {
                    String cellvalue = "";
                    HSSFCell myCell = (HSSFCell) cellIter.next();
                        if (myCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            System.out.println("11");
                            cellvalue = myCell.getStringCellValue();
                        } else if (myCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            System.out.println("22");
                             if (DateUtil.isCellDateFormatted(myCell)) {
                                 cellvalue = myCell.getDateCellValue().toString();
                                } else {
                                    cellvalue = Double.toString(myCell.getNumericCellValue());
                                }
                        } else if (myCell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                            System.out.println("33");
                            cellvalue = "" + myCell.getBooleanCellValue();
                        } else if (myCell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                            System.out.println("44");
                            cellvalue = ""+ formulaEval.evaluate(myCell).formatAsString();
                        } else if (myCell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                            System.out.println("55");
                            cellvalue = "";
                        }
                        System.out.println("cellvalue--" + cellvalue);
                    }
            }

All values are display properly but this only one value get wrong. Thanks in advance

or how to change cell type of excel in text format?

vijayk
– vijayk

2014-12-29 11:54:19 +00:00
Commented Dec 29, 2014 at 11:54 — vijayk
– vijayk, Commented Dec 29, 2014 at 11:54

Oleksii Kyslytsyn · Accepted Answer · 2014-12-29 11:56:10Z

here is some formatting example:

        Workbook wb = new HSSFWorkbook();
        // Workbook wb = new XSSFWorkbook();
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");

        // Create a row and put some cells in it. Rows are 0 based.
        Row row = sheet.createRow((short) 0);
        // Create a cell and put a value in it.
        Cell cell = row.createCell(0);
        cell.setCellValue(119710179);

        // Or do it on one line.
        row.createCell(1).setCellValue(119710179);
        row.createCell(2).setCellValue(createHelper.createRichTextString("119710179 "));
        row.createCell(3).setCellValue(true);

        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        wb.write(fileOut);
        fileOut.close();

        String cellvalue = "" + cell.getNumericCellValue();

        DataFormatter formatter = new DataFormatter(Locale.US);

        System.out.println("cellvalue--" + formatter.formatCellValue(cell));

Piotr Chowaniec · Accepted Answer · 2014-12-29 12:10:36Z

0

To change cell type:

if(myCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
        myCell.setCellType(Cell.CELL_TYPE_STRING);
}

And now you can read from this cell a string:

myCell.getStringCellValue();

answered Dec 29, 2014 at 12:10

Piotr Chowaniec

1,23014 silver badges26 bronze badges

1 Comment

Gagravarr Over a year ago

If you look at the JavaDocs for cell.setCellType(int), it's very clear that this is not the right way to do that! You should use DataFormatter as the javadocs explain

Collectives™ on Stack Overflow

How to read numeric values from excel in java?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related