Bug 64864 - Exception when I pass in a parameter with a special symbol using the cell's method setcellformula
Summary: Exception when I pass in a parameter with a special symbol using the cell's m...
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.1.1-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-10-31 03:03 UTC by guhuaiyu@tech-winning.com
Modified: 2020-12-10 21:43 UTC (History)
0 users



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description guhuaiyu@tech-winning.com 2020-10-31 03:03:26 UTC
hello!
    I can't call method setCellFormula() with argument
  cell.setCellFormula("HI2-4!E4");

c.h.f.common.exception.FasExtendExceptionResolver.doResolveException(35)- fas system happen error
org.apache.poi.ss.formula.FormulaParseException: Unused input [!B12] after attempting to parse the formula [LI2-2!B12]
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:2041)
	at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:170)
	at org.apache.poi.xssf.usermodel.XSSFCell.setFormula(XSSFCell.java:550)
	at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormulaImpl(XSSFCell.java:526)
	at org.apache.poi.ss.usermodel.CellBase.setCellFormula(CellBase.java:132)
	at cn.hsa.fas.common.fs.poi.ExcelUtil.getFormulaValue(ExcelUtil.java:929)
	at cn.hsa.fas.report.util.ExcelValidate.getCellValue(ExcelValidate.java:393)

example code:

public static BigDecimal getFormulaValue(Workbook workbook, String formula) {
        if (FundStringUtil.isEmpty(formula)) {
            return BigDecimal.ZERO;
        }
        // 获取公式求值
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

        // 创建一个虚拟的cell单元格,将公式放入单元格
        Cell cell = workbook.createSheet().createRow(0).createCell(0);
        cell.setCellFormula("HI2-4!E4");
        CellValue cellValue = formulaEvaluator.evaluate(cell);
        if (null == cellValue) {
            return BigDecimal.ZERO;
        }

        try {
            String cellStringValue = getCellStringValue(cellValue);
            return BigDecimalUtil.buildBigDecimal(cellStringValue);
        } catch (Exception e) {
            logger.error("cellValue=" + cellValue.formatAsString() + ",e=" + e.getMessage(), e);
            return BigDecimal.ZERO;
        }
    }
Comment 1 Nick Burch 2020-11-05 16:45:30 UTC
Normally an ! is used to separate a sheet name from a cell reference in a formula

What is your formula supposed to mean?

If you enter the formula in Excel and read it back in Apache POI, how did it get stored? Any escaping etc?
Comment 2 Dominik Stadler 2020-12-10 21:43:53 UTC
No response on the question so we cannot do much for now here, please reopen this with more information if this is still a problem for you.