[Bug 64864] New: Exception when I pass in a parameter with a special symbol using the cell's method setcellformula

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[Bug 64864] New: Exception when I pass in a parameter with a special symbol using the cell's method setcellformula

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=64864

            Bug ID: 64864
           Summary: Exception when I pass in a parameter with a special
                    symbol using the cell's method setcellformula
           Product: POI
           Version: 4.1.1-FINAL
          Hardware: PC
            Status: NEW
          Severity: major
          Priority: P2
         Component: SS Common
          Assignee: [hidden email]
          Reporter: [hidden email]
  Target Milestone: ---

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

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

[Bug 64864] Exception when I pass in a parameter with a special symbol using the cell's method setcellformula

Bugzilla from bugzilla@apache.org
https://bz.apache.org/bugzilla/show_bug.cgi?id=64864

Nick Burch <[hidden email]> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All
             Status|NEW                         |NEEDINFO

--- Comment #1 from Nick Burch <[hidden email]> ---
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?

--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]