使用Apache Poi从Excel表获取单元格值。

How to get cell value with poi in java ?

如何在java中使用poi获取单元格值?

My code is look like this

我的代码是这样的。

String cellformula_total__percentage= "(1-E" + (rowIndex + 2) + "/" + "D" + (rowIndex + 2) + ")*100";
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellStyle(this.valueRightAlignStyleLightBlueBackground);
cell.setCellFormula("abs(" + cellformula_total__percentage + ")");

But if there is in this case how can i check that my cell value contain error value like #DIV/0! and how can i replace it with N/A

但是如果在这种情况下，我如何检查我的单元格值是否包含像#DIV/0这样的错误值!如何用N/A替换它。

2 个解决方案

#1

You have to use the FormulaEvaluator, as shown here. This will return a value that is either the value present in the cell or the result of the formula if the cell contains such a formula :

你必须使用公式评估器，如图所示。这将返回一个值，该值要么是单元中的值，要么是公式的结果，如果单元格包含这样一个公式:

FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
Sheet sheet = wb.getSheetAt(0);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

// suppose your formula is in B3
CellReference cellReference = new CellReference("B3"); 
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 

if (cell!=null) {
    switch (evaluator.evaluateFormulaCell(cell)) {
        case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            System.out.println(cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_ERROR:
            System.out.println(cell.getErrorCellValue());
            break;

        // CELL_TYPE_FORMULA will never occur
        case Cell.CELL_TYPE_FORMULA: 
            break;
    }
}

if you need the exact contant (ie the formla if the cell contains a formula), then this is shown here.

如果您需要精确的contant(如果单元格包含一个公式)，那么这里显示的是这个。

Edit : Added a few example to help you.

编辑:添加几个例子来帮助你。

first you get the cell (just an example)

首先是单元格(只是一个例子)

Row row = sheet.getRow(rowIndex+2);    
Cell cell = row.getCell(1);

If you just want to set the value into the cell using the formula (without knowing the result) :

如果您只想使用公式(不知道结果)将值设置到单元格中:

 String formula ="ABS((1-E"+(rowIndex + 2)+"/D"+(rowIndex + 2)+")*100)";    
 cell.setCellFormula(formula);    
 cell.setCellStyle(this.valueRightAlignStyleLightBlueBackground);

if you want to change the message if there is an error in the cell, you have to change the formula to do so, something like

如果您想要更改消息，如果在单元格中有错误，则必须更改该公式，以便这样做。

IF(ISERR(ABS((1-E3/D3)*100));"N/A"; ABS((1-E3/D3)*100))

(this formula check if the evaluation return an error and then display the string "N/A", or the evaluation if this is not an error).

(此公式检查评估是否返回一个错误，然后显示字符串“N/A”，如果这不是一个错误，则进行评估)。

if you want to get the value corresponding to the formula, then you have to use the evaluator.

如果想要得到与公式相对应的值，那么就必须使用求值器。

Hope this help,
Guillaume

希望这次的帮助,纪尧姆

#2

May be by:-

可能是由:-

    for(Row row : sheet) {          
        for(Cell cell : row) {              
            System.out.print(cell.getStringCellValue());

        }
    }

For specific type of cell you can try:

对于特定类型的细胞，你可以尝试:

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
    cellValue = cell.getStringCellValue();
    break;

case Cell.CELL_TYPE_FORMULA:
    cellValue = cell.getCellFormula();
    break;

case Cell.CELL_TYPE_NUMERIC:
    if (DateUtil.isCellDateFormatted(cell)) {
        cellValue = cell.getDateCellValue().toString();
    } else {
        cellValue = Double.toString(cell.getNumericCellValue());
    }
    break;

case Cell.CELL_TYPE_BLANK:
    cellValue = "";
    break;

case Cell.CELL_TYPE_BOOLEAN:
    cellValue = Boolean.toString(cell.getBooleanCellValue());
    break;

}

#1