使用poi或jxl api转换Excel单元格值类型

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 dont know wich method to use. Could you help me please.

我有一个包含数值的excel文件,但它们存储为标签。所以我想使用jxl或poi api来设置它们的值类型。但是我不知道使用哪种方法。请问你能帮帮我吗。

Thank you

1 个解决方案

#1

I use poi library and this is my ExcelParser Class:

我使用poi库,这是我的ExcelParser类:

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

警告,此类仅适用于xls文件,如果使用必须使用的xlsx文件

XSSF

class rather than

而不是

HSSF

#1