CELL_TYPE_ERROR有什么用？

I want to understand the use of cell type CELL_TYPE_ERROR in apache poi. I tried the following code, I see no error.

我想了解在apache poi中使用单元格类型CELL_TYPE_ERROR。我尝试了以下代码,我看到没有错误。

Workbook wb = new XSSFWorkbook();
Row row = sheet1.createRow(0);
Cell cell = row.createCell(0);

cell.setCellType(Cell.CELL_TYPE_ERROR);
cell.setCellValue(234);
System.out.println("error cell value-"+ cell.getNumericCellValue()); //this prints 234.0

Also, I want to understand if the cell can be of type error if we don't manually set its type.

此外,如果我们不手动设置其类型,我想了解单元格是否可以是类型错误。

1 个解决方案

#1

See the comments in the code.

请参阅代码中的注释。

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

class CellTypeErrorTest {

 public static void main(String[] args) {
  Workbook wb = new XSSFWorkbook();
  Sheet sheet = wb.createSheet("Sheet1");
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);

  //The following works, but it makes no sense, because the cell will have no real content.
  //If you wants to see, how this will be shown into the Workbook, then comment out the
  //following code that overwrites the Cell with numeric content.
  cell.setCellType(Cell.CELL_TYPE_ERROR);
  cell.setCellErrorValue(FormulaError.DIV0.getCode());
  System.out.println("error cell value-"+ FormulaError.forInt(cell.getErrorCellValue()).getString());

  //If you put real content in the cell, then the CELL_TYPE_ERROR goes away, if the content 
  //not produces ERROR.
  cell.setCellValue(234);
  System.out.println(cell.getCellType()); //0 == CELL_TYPE_NUMERIC

  //If you put a Formula in the Cell, it will not be evaluated automatically.
  //So there is no error, even the formula will produce error if it will be evaluated.
  cell = row.createCell(1);
  cell.setCellFormula("1/0");
  System.out.println(cell.getCellType()); //2 == CELL_TYPE_FORMULA

  //It you need to check if a formula produces error, then you have to evaluate it.
  FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
  CellValue cellValue = evaluator.evaluate(cell);
  System.out.println(cellValue.getCellType()); //5 == CELL_TYPE_ERROR
  if (cellValue.getCellType() == Cell.CELL_TYPE_ERROR) {
   System.out.println("error cell value-"+ FormulaError.forInt(cellValue.getErrorValue()).getString());
  }

  try {
   FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
   wb.write(fileOut);
   fileOut.close();
  } catch (FileNotFoundException fnfex) {
  } catch (IOException ioex) {
  }

 }
}

Conclusion:

The Cell.CELL_TYPE_ERROR is necessary to detect if a cell content produces an error. It mostly makes no sense, to set it manually.

Cell.CELL_TYPE_ERROR是检测单元格内容是否产生错误所必需的。手动设置它几乎没有意义。

It can be setted manually to cells without real content with cell.setCellErrorValue. But this mostly makes no sense, because if the cell gets real content and this don't produces an error, then the CellType changes automatically to another type.

可以使用cell.setCellErrorValue手动将其设置为没有实际内容的单元格。但这大多没有意义,因为如果单元格获得真实内容并且这不会产生错误,那么CellType会自动更改为其他类型。

POI do not evaluate the cells formulas automatically. CellTypes of cells with formulas are ever Cell.CELL_TYPE_FORMULA. Therefore, to check whether a cell formula produces error, we have to evaluate manually and then to check the CellType of the evaluated CellValue. See: http://poi.apache.org/spreadsheet/eval.html

POI不会自动评估单元格公式。具有公式的单元格的CellTypes永远是Cell.CELL_TYPE_FORMULA。因此,要检查单元格公式是否产生错误,我们必须手动评估,然后检查评估的CellValue的CellType。请参阅:http://poi.apache.org/spreadsheet/eval.html

Greetings

Axel

#1