javaexcelapache-poijxl

Convert Excel cell value type using poi or jxl api


I have an Excel file that contains numerical values, but they are stored as labels. So I want to use jxl or poi api to set their values type. However I don't know which method to use.

How can I do this?


Solution

  • I use poi library and this is my ExcelParser Class:

    public class ExcelParser {
    
        private HSSFWorkbook wb;
    
        public ExcelParser(File xlsFile) throws Exception{
    
            wb = new HSSFWorkbook(new FileInputStream(xlsFile));
        }
    
        public String getValue(String sheetName, int rowNum, int celNum) throws Exception{
            try{
                HSSFSheet sheet = null;
                for(int i=0; i<wb.getNumberOfSheets();i++){
                    if(wb.getSheetName(i).trim().toLowerCase().equals(sheetName.trim().toLowerCase())){
                        sheet = wb.getSheetAt(i);
                        break;
                    }
                }
                if(sheet == null){
                    throw new Exception("Sheet name '"+sheetName+"' not found.");
                }
    
                HSSFRow row     = sheet.getRow(rowNum);        
                if(row == null){return "";}
                HSSFCell cell   = row.getCell(celNum);
                if(cell== null){return "";}
    
                if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
                    return String.valueOf(cell.getNumericCellValue()).trim();
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
                    return cell.getStringCellValue().trim();        
                }  else if (cell.getCellType() == HSSFCell.CELL_TYPE_ERROR){
                    return "";//cell.getErrorCellValue();        
                }else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){
                    try{
    
                        return cell.getStringCellValue().trim();
                    } catch (Exception e) {
                        return "";
                    }
                } else{
                    return cell.getStringCellValue().trim();
                }
            }
            catch (Exception e) {
                throw new Exception(e.getMessage()+" in row:"+rowNum+" col:"+celNum+" sheet:"+sheetName);
            }
        }
    }
    

    WARNING, this class is for xls file only, if you use an xlsx file you have to use

    XSSF 
    

    class rather than

    HSSF