0

I have used apache.poi library to parse the .xlsx file but not getting right cell value.
I have Used below Libraries:

  • commons-collections4-4.1.jar
  • poi-3.17.jar
  • poi-ooxml-3.17.jar
  • poi-ooxml-schemas-3.17.jar
  • xmlbeans-2.6.0.jar

Here is my code:

import java.io.File;
import java.io.FileInputStream;
import java.util.HashMap;
import java.util.Iterator;

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


public class XLSXParser {
  private static File clientFile = new File("C:\\demo\\data.xlsx"); 
  
  private static HashMap<String, String> data = new HashMap<>();
  
  public static void main(String[] args) {    
    try {
      FileInputStream fis = new FileInputStream(clientFile);
  
      XSSFWorkbook wb = new XSSFWorkbook(fis);
      XSSFSheet sheet = wb.getSheetAt(0); 
      Iterator<Row> itr = sheet.iterator(); 
      int count = 0;
      while (itr.hasNext() && count <= 3) {
        String mpn = "";
        String desc = "";
        
        Row row = itr.next();
                
        // Get MPN
        mpn = getCellValue(row, 0);
        System.out.println("MPN: "+mpn);
        
        // Get Description
        desc = getCellValue(row, 1);
        
        if (!mpn.equals("MPN")) {
          data.put(mpn, desc);  
        }
        
        count++;
      }
      System.out.println(data);
//      System.out.println(count);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  public static String getCellValue(Row row, int cellIndex) {
    Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    
    String cellValue = "";
    if (cell == null || cell.getCellType() == null) {
      System.out.println("Hold..!!");
    }
    switch (cell.getCellType()) {
    case STRING:
      cellValue = cell.getStringCellValue();          
      break;
    case NUMERIC:
      cellValue = String.valueOf(cell.getNumericCellValue());
      break;
    case BLANK:
      cellValue = "NA";
      break;
    default:
    }
    
    return cellValue;
  }
  
}

Program Output:

MPN: MPN
MPN: 00112124
MPN: 8.2703124E7
MPN: 8.2703135E7
{8.2703124E7=BS 703 F-7024, 8.2703135E7=BS 703 F-7035, 00112124=BA 100806-7024}

Desire Output:

MPN: MPN
MPN: 00112124
MPN: 82703124
MPN: 82703135
{82703124=BS 703 F-7024, 82703135=BS 703 F-7035, 00112124=BA 100806-7024}

you can download my .xlsx file from here.
Here when I am trying to retrieve the value of a cell for MPN using Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); I am not getting the right value of a cell for the third(82703124) and fourth(82703135) row. Don't know why? or I am creating a mistake while retrieving it. can anyone help me to resolve this issue will be good help for me. Thank You.

Java-Dev
  • 438
  • 4
  • 20
  • 1
    maybe the spreadsheet is using some numerical format? take a look at this https://stackoverflow.com/questions/3819633/how-to-get-the-formatted-value-of-a-number-for-a-cell-in-apache-poi – shikida Jun 28 '21 at 12:21
  • `8.2703124E7` means 8.2703124 * 10^7, which is 82703124. So the value is correct but not the format. See https://stackoverflow.com/questions/3819633/how-to-get-the-formatted-value-of-a-number-for-a-cell-in-apache-poi/66241266#66241266. – Axel Richter Jun 28 '21 at 13:24

0 Answers0