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

问题描述

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

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

假设在Excel中，第1行的公式为=A1/B1.如果您将其复制粘贴，请在第5行中说，该公式将变为=A5/B5.

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.

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

In Apache POI if you run the lines

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

公式仍为=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.

由于apache poi能够计算公式，所以它也必须能够解析公式.解析类位于软件包 org.apache中.poi.ss.formula 和

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.

示例:

以下Excel工作簿:

Following Excel workbook:

和以下代码:

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

导致以下输出:

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)

---

更新的String copyFormula(Sheet sheet, String formula, int coldiff, int rowdiff)方法适用于HSSF和XSSF的SS:

---

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屋！