poi 公式复制函数

 

 当poi 输出excel时, 需要处理公式。 并且要求公式 能像excel中复制一数,非绝对引用会自动变。比如 第一行第一列公式为:A1+B1 ,把它COPY到第二行时,应为A2+B2

private static void copyFormula(Sheet sheet, Cell org, Cell dest) {
        if (org == null || dest == null || sheet == null
                || org.getCellType() != CellType.FORMULA)
            return;
        if (org.isPartOfArrayFormulaGroup())
            return;
        String formula = org.getCellFormula();
        int shiftRows = dest.getRowIndex() - org.getRowIndex();
        int shiftCols = dest.getColumnIndex() - org.getColumnIndex();
        HSSFEvaluationWorkbook workbookWrapper =
                HSSFEvaluationWorkbook.create((HSSFWorkbook) sheet.getWorkbook());
        Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL
                , sheet.getWorkbook().getSheetIndex(sheet));
        for (Ptg ptg : ptgs) {
            if (ptg instanceof RefPtgBase) // base class for cell references
            {
                RefPtgBase ref = (RefPtgBase) ptg;
                if (ref.isColRelative())
                    ref.setColumn(ref.getColumn() + shiftCols);
                if (ref.isRowRelative())
                    ref.setRow(ref.getRow() + shiftRows);
            } else if (ptg instanceof AreaPtg) // base class for range references
            {
                AreaPtg ref = (AreaPtg) ptg;
                if (ref.isFirstColRelative())
                    ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
                if (ref.isLastColRelative())
                    ref.setLastColumn(ref.getLastColumn() + shiftCols);
                if (ref.isFirstRowRelative())
                    ref.setFirstRow(ref.getFirstRow() + shiftRows);
                if (ref.isLastRowRelative())
                    ref.setLastRow(ref.getLastRow() + shiftRows);
            }
        }
        formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
        dest.setCellFormula(formula);
    }

 

posted @ 2020-09-19 16:10  isV1  阅读(1309)  评论(0编辑  收藏  举报