复制时 Apache POI 更新公式引用 [英] Apache POI update formula references when copying

问题描述

在 Apache POI 中复制公式时，是否可以更新公式引用?

假设在 Excel 中，您在第 1 行有公式 =A1/B1.如果你复制粘贴它，比如在第 5 行，公式变成 =A5/B5.

在 Apache POI 中，如果您运行这些行

r5.getCell(2).setCellType(CellType.FORMULA);r5.getCell(2).setCellFormula(r1.getCell(2).getCellFormula());

公式保持=A1/B1.

解决方案

您的代码不是复制/粘贴某些内容，而是从一个单元格获取公式字符串并将此公式字符串准确设置到另一个单元格.这不会改变公式字符串.它应该怎么做?

所以需要从一个单元格中获取公式字符串，然后将此公式字符串调整到目标单元格.

既然 apache poi 能够计算公式，它也必须能够解析公式.解析类位于包

和以下代码:

import java.io.FileInputStream;导入 org.apache.poi.ss.formula.*;导入 org.apache.poi.ss.formula.ptg.*;导入 org.apache.poi.ss.usermodel.*;导入 org.apache.poi.xssf.usermodel.*;导入 org.apache.poi.ss.util.CellAddress;公共类 ExcelCopyFormula {private static String copyFormula(XSSFSheet sheet, String formula, int coldiff, int rowdiff) {XSSFEvaluationWorkbook workbookWrapper =XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));for (int i = 0; i < ptgs.length; i++) {if (ptgs[i] instanceof RefPtgBase) {//单元格引用的基类RefPtgBase ref = (RefPtgBase) ptgs[i];如果 (ref.isColRelative())ref.setColumn(ref.getColumn() + Coldiff);如果 (ref.isRowRelative())ref.setRow(ref.getRow() + rowdiff);}else if (ptgs[i] instanceof AreaPtgBase) {//范围引用的基类AreaPtgBase ref = (AreaPtgBase) ptgs[i];如果 (ref.isFirstColRelative())ref.setFirstColumn(ref.getFirstColumn() + Coldiff);如果 (ref.isLastColRelative())ref.setLastColumn(ref.getLastColumn() + Coldiff);如果 (ref.isFirstRowRelative())ref.setFirstRow(ref.getFirstRow() + rowdiff);如果 (ref.isLastRowRelative())ref.setLastRow(ref.getLastRow() + rowdiff);}}公式 = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);回报公式；}public static void main(String[] args) 抛出异常 {XSSFWorkbook 工作簿 = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("test.xlsx"));XSSFSheet sheet = workbook.getSheetAt(0);for(行行:工作表){对于(单元格单元格:行){//if (cell.getCellTypeEnum() == CellType.FORMULA) {//到 apache poi 版本 3if (cell.getCellType() == CellType.FORMULA) {//自 apache poi 版本 4CellAddress source = cell.getAddress();字符串公式 = cell.getCellFormula();System.out.print(source + "=" + 公式);int rowdiff = 3;int Coldiff = -2;CellAddress target = new CellAddress(source.getRow() + rowdiff, source.getColumn() + Coldiff);String newformula = copyFormula(sheet, formula, Coldiff, rowdiff);System.out.println("->" + target + "=" + newformula);}}}workbook.close();}}

导致以下输出:

E3=C3/D3->C6=A6/B6E4=$C4/D$4->C7=$C7/B$4E5=SUM(C3:D5)->C8=SUM(A6:B8)E6=SUM(C$3:$D6)->C9=SUM(A$3:$D9)E7=C3+SUM(C3:D7)->C10=A6+SUM(A6:B10)E8=C$3+SUM($C3:D$8)->C11=A$3+SUM($C6:B$8)

---

更新了 String copyFormula(Sheet sheet, String formula, int Coldiff, int rowdiff) 方法，该方法适用于 SS，也就是 HSSF以及 XSSF:

 private static String copyFormula(Sheet sheet, String formula, int Coldiff, int rowdiff) {工作簿工作簿 = sheet.getWorkbook();评估工作簿评估工作簿=空；如果(HSSFWorkbook 的工作簿实例){评估工作簿 = HSSFEvaluationWorkbook.create((HSSFWorkbook) 工作簿);} else if (workbook instanceof XSSFWorkbook) {evaluationWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) 工作簿);}Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook)evaluationWorkbook,FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));for (int i = 0; i < ptgs.length; i++) {if (ptgs[i] instanceof RefPtgBase) {//单元格引用的基类RefPtgBase ref = (RefPtgBase) ptgs[i];如果 (ref.isColRelative())ref.setColumn(ref.getColumn() + Coldiff);如果 (ref.isRowRelative())ref.setRow(ref.getRow() + rowdiff);}else if (ptgs[i] instanceof AreaPtgBase) {//范围引用的基类AreaPtgBase ref = (AreaPtgBase) ptgs[i];如果 (ref.isFirstColRelative())ref.setFirstColumn(ref.getFirstColumn() + Coldiff);如果 (ref.isLastColRelative())ref.setLastColumn(ref.getLastColumn() + Coldiff);如果 (ref.isFirstRowRelative())ref.setFirstRow(ref.getFirstRow() + rowdiff);如果 (ref.isLastRowRelative())ref.setLastRow(ref.getLastRow() + rowdiff);}}公式 = FormulaRenderer.toFormulaString((FormulaRenderingWorkbook)evaluationWorkbook, ptgs);回报公式；}

Is there a way to update formula references when copying a formula in Apache POI?

Say in Excel you have in row 1 the formula =A1/B1. If you copy-paste it, say in row 5, the formula becomes =A5/B5.

In Apache POI if you run the lines

r5.getCell(2).setCellType(CellType.FORMULA);
r5.getCell(2).setCellFormula(r1.getCell(2).getCellFormula());

the formula remains =A1/B1.

解决方案

Your code is not copy/pasting something but gets the formula string from one cell and sets exactly this formula string to another cell. This will not changing the formula string. How even should it do?

So the need is to get the the formula string from one cell and then adjust this formula string to the target cell.

Since apache poi is able to evaluate formulas, it must also be able to parse formulas. The parsing classes are in the packages org.apache.poi.ss.formula and org.apache.poi.ss.formula.ptg.

So we can use those classes to adjust the formula string to the target cell.

Example:

Following Excel workbook:

and following code:

import java.io.FileInputStream;

import org.apache.poi.ss.formula.*;
import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellAddress;

public class ExcelCopyFormula {

 private static String copyFormula(XSSFSheet sheet, String formula, int coldiff, int rowdiff) {

  XSSFEvaluationWorkbook workbookWrapper = 
   XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
  Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL
   , sheet.getWorkbook().getSheetIndex(sheet));

  for (int i = 0; i < ptgs.length; i++) {
   if (ptgs[i] instanceof RefPtgBase) { // base class for cell references
    RefPtgBase ref = (RefPtgBase) ptgs[i];
    if (ref.isColRelative())
     ref.setColumn(ref.getColumn() + coldiff);
    if (ref.isRowRelative())
     ref.setRow(ref.getRow() + rowdiff);
   }
   else if (ptgs[i] instanceof AreaPtgBase) { // base class for range references
    AreaPtgBase ref = (AreaPtgBase) ptgs[i];
    if (ref.isFirstColRelative())
     ref.setFirstColumn(ref.getFirstColumn() + coldiff);
    if (ref.isLastColRelative())
     ref.setLastColumn(ref.getLastColumn() + coldiff);
    if (ref.isFirstRowRelative())
     ref.setFirstRow(ref.getFirstRow() + rowdiff);
    if (ref.isLastRowRelative())
     ref.setLastRow(ref.getLastRow() + rowdiff);
   }
  }

  formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
  return formula;
 }

 public static void main(String[] args) throws Exception {

  XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("test.xlsx"));  
  XSSFSheet sheet = workbook.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    //if (cell.getCellTypeEnum() == CellType.FORMULA) { // up to apache poi version 3
    if (cell.getCellType() == CellType.FORMULA) { // since apache poi version 4
     CellAddress source = cell.getAddress();
     String formula = cell.getCellFormula();
     System.out.print(source + "=" + formula);
     int rowdiff = 3;
     int coldiff = -2;
     CellAddress target = new CellAddress(source.getRow() + rowdiff, source.getColumn() + coldiff);
     String newformula = copyFormula(sheet, formula, coldiff, rowdiff);
     System.out.println("->" + target + "=" + newformula);
    }
   }
  }

  workbook.close();
 }
}

leads to following output:

E3=C3/D3->C6=A6/B6
E4=$C4/D$4->C7=$C7/B$4
E5=SUM(C3:D5)->C8=SUM(A6:B8)
E6=SUM(C$3:$D6)->C9=SUM(A$3:$D9)
E7=C3+SUM(C3:D7)->C10=A6+SUM(A6:B10)
E8=C$3+SUM($C3:D$8)->C11=A$3+SUM($C6:B$8)

---

Updated String copyFormula(Sheet sheet, String formula, int coldiff, int rowdiff) method which works for SS that is for HSSFas well as for XSSF:

 private static String copyFormula(Sheet sheet, String formula, int coldiff, int rowdiff) {

  Workbook workbook = sheet.getWorkbook();
  EvaluationWorkbook evaluationWorkbook = null;
  if (workbook instanceof HSSFWorkbook) {
   evaluationWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);
  } else if (workbook instanceof XSSFWorkbook) {
   evaluationWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
  }

  Ptg[] ptgs = FormulaParser.parse(formula, (FormulaParsingWorkbook)evaluationWorkbook, 
   FormulaType.CELL, sheet.getWorkbook().getSheetIndex(sheet));

  for (int i = 0; i < ptgs.length; i++) {
   if (ptgs[i] instanceof RefPtgBase) { // base class for cell references
    RefPtgBase ref = (RefPtgBase) ptgs[i];
    if (ref.isColRelative())
     ref.setColumn(ref.getColumn() + coldiff);
    if (ref.isRowRelative())
     ref.setRow(ref.getRow() + rowdiff);
   }
   else if (ptgs[i] instanceof AreaPtgBase) { // base class for range references
    AreaPtgBase ref = (AreaPtgBase) ptgs[i];
    if (ref.isFirstColRelative())
     ref.setFirstColumn(ref.getFirstColumn() + coldiff);
    if (ref.isLastColRelative())
     ref.setLastColumn(ref.getLastColumn() + coldiff);
    if (ref.isFirstRowRelative())
     ref.setFirstRow(ref.getFirstRow() + rowdiff);
    if (ref.isLastRowRelative())
     ref.setLastRow(ref.getLastRow() + rowdiff);
   }
  }

  formula = FormulaRenderer.toFormulaString((FormulaRenderingWorkbook)evaluationWorkbook, ptgs);
  return formula;
 }

这篇关于复制时 Apache POI 更新公式引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！