Convert Excel Exponential number in java

When we write the number in excel, it automatically converts in the exponential format and then try to get the values in Java using Apache POI API, it comes in the exponential format only. So to get the actual, we have to convert the cell into number format and get the value.

Let's take an example and see how to get the actual value that is in exponential format.

Suppose we have the below excel that need to parse

Actual values are:

• 90153856767
• 90176554678

Below is the program to parse the excel file and get the actual values. The section that is marked in red is used to convert the exponential number into actual values.

package com.test.ankur;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ParseExponential {

 public static void main(String[] args) {
  try {
   FileInputStream file = new FileInputStream(new File("C:/Ankur/ExponentialNumber.xlsx"));
   XSSFWorkbook workbook = new XSSFWorkbook(file);
   XSSFSheet sheet = workbook.getSheetAt(0);
   Iterator rowIterator = sheet.iterator();
   while (rowIterator.hasNext()) {
    Row currentRow = rowIterator.next();
    Iterator cellIterator = currentRow.iterator();
    while (cellIterator.hasNext()) {
     Cell nextCell = cellIterator.next();
     int columnIndex = nextCell.getColumnIndex();
     switch (columnIndex) {
     case 0:
      nextCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
      Double doubleValue = nextCell.getNumericCellValue();
      BigDecimal bd = new BigDecimal(doubleValue.toString());
      long lonVal = bd.longValue();
      String phoneNumber = Long.toString(lonVal).trim();
      System.out.print("PhoneNumber " + phoneNumber);
      break;
     case 1:
      nextCell.setCellType(XSSFCell.CELL_TYPE_STRING);
      String firstName = nextCell.getStringCellValue();
      System.out.print(" FirstName " + firstName);
      break;
     }

    }
    System.out.println();

   }
   file.close();
  } catch (Exception e) {
   e.printStackTrace();
  }
 }
}

Output would be: