How to Clone Function with relative Reference on HSSF apache poi (JAVA)

Question

I'm try to make a export Excel, and i need some formula for count number in my excel , but i have a data from database and it will be more than one. so i wanna clone that formula to next data.

it goes well , but the reference cell can't change to next cell, like if we tried copy cell from excel

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

i want to copy count from data1 to data2

Could you show more of your code? Your single line of code is just not enough for anyone to help you. — deHaar, Jul 17 '19 at 09:36
i think , that is the point for clone my cell to another cell, i using that method, to get my formula from celltwo and drop it at cellOne — Prihatma Faza, Jul 17 '19 at 09:38
The formula to be set is a `String` containing cell and row references. That's why it does not work if you just copy the formula from one cell and paste it into another, which would work via Excel-GUI by mouse/keyboard. You could create a class representing the formula and give it some attributes that have to be adjusted to the new cell or row numbers along with a proper `toString()` or `toExcelFormula()` method. — deHaar, Jul 17 '19 at 09:42
Could you add the formula to your question by [editing it](https://stackoverflow.com/posts/57072729/edit)? — deHaar, Jul 17 '19 at 09:56
See https://stackoverflow.com/questions/47594254/apache-poi-update-formula-references-when-copying/47606426#47606426 — Axel Richter, Jul 18 '19 at 03:37
Are you sure you don't want the real formula `SUM(H20;I20)` instead of directly summing `H20+I20`? — deHaar, Jul 18 '19 at 06:04
it's ok , for using SUM(H20;I20) but i still need a method to copy that cell @deHaar — Prihatma Faza, Jul 18 '19 at 12:01
You could provide the formula as a `String` template that takes arguments, like `String sumFormulaTemplate = "SUM(H%d;I%d)";` (columns are hard coded here). Then you could loop and set the row number, like `String sumFormula = String.format(sumFormulaTemplate, rowNumber, rowNumber);` and set that in column J via `cell.setFormula(sumFormula);`. If you need to read a formula first (because you don't know what formula it is or similar), you will have to find the row numbers in it and replace them with the currently needed one. — deHaar, Jul 18 '19 at 12:34

score 1 · Answer 1 · answered Jul 19 '19 at 14:19

Instead of manipulating the formula strings to update formula references, best practice is really using a FormulaParser in my opinion. Advantage is that really all possible formulas can be updated as long as the FormulaParser knows them. Manipulating the formula strings easily leads to problems. For example a formula template like "SUM(H%d;I%d)" has %d as a variable to be replaced. But the percent sign could also be a not variable part in some formulas.

The apache poi has a FormulaParser which can be used. In Apache POI update formula references when copying I have shown this already for XSSF only.

Since this question is about HSSF I will show an String copyFormula(Sheet sheet, String formula, int coldiff, int rowdiff) method which works for SS that is for HSSFas well as for 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);

  }
 }
}

How to Clone Function with relative Reference on HSSF apache poi (JAVA)

1 Answers1