java - 如何在 HSSF apache poi (JAVA) 上克隆具有相关引用的函数

Question

我正在尝试导出 Excel，我需要一些公式来计算 Excel 中的数字，但我有数据库中的数据，并且它会不止一个。所以我想将该公式克隆到下一个数据。

一切顺利，但引用单元格无法更改为下一个单元格，就像我们尝试从 Excel 复制单元格

cellOne[data].setCellFormula(celltwo[data].getCellFormula());

i want to copy count from data1 to data2

Answer 1

在我看来，最佳实践实际上是使用 FormulaParser，而不是操作公式字符串来更新公式引用。优点是只要 FormulaParser 知道所有可能的公式，就可以更新它们。操作公式字符串很容易导致问题。例如，像 "SUM(H%d;I%d)" 这样的公式模板将 %d 作为要替换的变量。但百分号在某些公式中也可能是不可变的部分。

apache poi 有一个可以使用的FormulaParser。在 Apache POI update formula references when copying我已经仅针对 XSSF 展示了这一点。

由于这个问题是关于HSSF，我将展示一个适用于SS的String copyFormula(Sheet Sheet, String Formula, int Coldiff, int rowdiff)方法 适用于 HSSF 以及 XSSF。

import java.io.FileOutputStream;

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.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;

public class ExcelCopyFormula {

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

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

  //String type = "XSSF";
  String type = "HSSF";

  try (Workbook workbook = ("XSSF".equals(type))?new XSSFWorkbook():new HSSFWorkbook(); 
       FileOutputStream out = new FileOutputStream(("XSSF".equals(type))?"Excel.xlsx":"Excel.xls") ) {

   Sheet sheet = workbook.createSheet();

   for (int r = 2 ; r < 10; r++) {
    Row row = sheet.createRow(r);
    for (int c = 2 ; c < 5; c++) {
     Cell cell = row.createCell(c);
     if (r == 2) {
      if (c == 2) cell.setCellValue("No");
      if (c == 3) cell.setCellValue("Number One");
      if (c == 4) cell.setCellValue("Number Two");
     } else {
      if (c == 2) cell.setCellValue("data" + (r-2));
      if (c == 3) cell.setCellValue(r*c);
      if (c == 4) cell.setCellValue(r*c);
     }
    }
   }

   for (int r = 2 ; r < 10; r++) {
    Row row = sheet.getRow(r);
    Cell cell = row.createCell(5);
    String formula = "D4+E4";
    if (r == 2) cell.setCellValue("Formula");
    else cell.setCellFormula(copyFormula(sheet, formula, 0, r-3));
   }

   for (int r = 2 ; r < 10; r++) {
    Row row = sheet.getRow(r);
    Cell cell = row.createCell(6);
    String formula = "G4+F5";
    if (r == 2) cell.setCellValue("Cumulative");
    else if (r == 3) cell.setCellFormula("F4");
    else cell.setCellFormula(copyFormula(sheet, formula, 0, r-4));
   }

   workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
   workbook.write(out);

  }
 }
}