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