How do I emulate Excel's "Fill down" in Apache POI?

Question

I've got a template workbook, with a sheet ("All data") which I populate using Apache POI. I don't know how many rows I'm going to need in "All data" when I start.

In another sheet (call it "Calc"), I have 4 columns containing formulae that do stuff based on "All data". I need to have as many rows in Calc as in "All data", and I thought the easiest way to do it would be to have, in the template, one row with the formulae in it, which I can then fill down the sheet as many times as necessary.

Thus, in the template I have:

 Col1Header     | Col2Header                            | Col3Header                            | Col4Header
=+'All data'!F2 | =IF(LEFT(A55,1)="4",'All data'!R2,"") | =IF(LEFT(A55,1)="4",'All data'!O2,"") | =+'All data'!W2

Then I would expect to be able to "fill down" from that first formula line, so that I have n rows (where n is the number of rows I'm using in the "All data" sheet).

However, I cannot see how to do "fill down" in Apache POI. Is it something that's not possible? Or am I looking for the wrong name?

Yes, an alternative method would be simply to change the template by manually copying down more rows than I would ever expect to be using, but that is (a) inelegant and (b) is asking for trouble in the future:-)

I feel sure there must be a better way?

score 1 · Accepted Answer · answered Mar 23 '21 at 06:20

If this is for an Office Open XML workbook (*.xlsx, XSSF) and current apache poi 5.0.0 is used, then XSSFSheet.copyRows can be used. The default CellCopyPolicy copies formulas and adjusts the cell references in them.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.FileInputStream;
import java.io.FileOutputStream;

class ExcelReadCopyRowsAndWrite {

 public static void main(String[] args) throws Exception {
  String fileIn= "./TestIn.xlsx";
  String fileOut= "./TestOut.xlsx";

  int n = 10; // 10 rows needed

  int fillRowsFromIdx = 1; // start at row 2 (index 1) which is row having the formulas
  int fillRowsToIdx = fillRowsFromIdx + n - 1;

  try (Workbook workbook = WorkbookFactory.create(new FileInputStream(fileIn));
       FileOutputStream out = new FileOutputStream(fileOut)) {

   Sheet sheet = workbook.getSheet("Calc"); // do it in sheet named "Calc"
   if (sheet instanceof XSSFSheet) {
    XSSFSheet xssfSheet = (XSSFSheet) sheet;
    for (int i = fillRowsFromIdx; i < fillRowsToIdx; i++) {
     xssfSheet.copyRows(i, i, i+1, new CellCopyPolicy());
    }
   }

   workbook.write(out);
  } 
 }
}

ThecopyRows method is only in XSSF up to now. For an example how to copy formulas also working for BIFF workbook (*.xls, HSSF) see Apache POI update formula references when copying.

How do I emulate Excel's "Fill down" in Apache POI?

1 Answers1