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
31
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
9
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
31
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
9
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;
}