Why am I getting IllegalArgumentException while using copyRowFrom(...) in XSSFRow?

Question

I'm trying to use copyRowFrom(...); to copy the first row of a spreadsheet to the first row of a new XSSFSheet but something is not working right. You can find the XSSFRow class and the method here: https://github.com/apache/poi/blob/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java#L581

I'm getting IllegalArgumentException("amountToMove must not be zero") from FormulaShifter.java: https://github.com/apache/poi/blob/trunk/src/java/org/apache/poi/ss/formula/FormulaShifter.java#L80

It seems that the problem is at line 623 of XSSFRow where FormulaShifter.createForRowCopy(...) is invoked with parameter rowDifference = 0 because source row is 0 and destination row is 0: https://github.com/apache/poi/blob/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRow.java#L623

I don't know, maybe it's an error, but when reaching line 80 in FormulaShifter.java the parameter rowDifference corresponds to amountToMove which is 0 so it throws IllegalArgumentException.

Am I missing something or is this a bug of copyRowFrom(...); method in XSSFRow?

score 8 · Accepted Answer · edited May 18 '20 at 14:26

You are correct. This is a bug in XSSFRow since it calls FormulaShifter.createForRowCopy even if there is nothing to shift because destination row number is the same as source row number. You could file this as a bug to apache poi.

But then there is a need for creating a test case which can be provided there. I have done that for you. The code also provides a workaround. This is first copying to a wrong destination row where row number differs from source row number. Then it copies the wrong first destination row to the really needed destination row.

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

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

class ExcelCopyRowFrom {

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

  XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("SAMPLE.xlsx"));
  XSSFSheet srcSheet = workbook.getSheetAt(0);
  XSSFRow srcRow = srcSheet.getRow(0);

  XSSFSheet destSheet = workbook.createSheet();
  //XSSFRow destRow = destSheet.createRow(0); //this fails because destination row number is the same as source row number

  XSSFRow destRow = destSheet.createRow(1); //this works 
  destRow.copyRowFrom(srcRow, new CellCopyPolicy());

  //workaround copy wrong first destination row to really needed destination row
  XSSFRow destRowNeeded = destSheet.createRow(0);
  destRowNeeded.copyRowFrom(destRow, new CellCopyPolicy());
  //the remove wrong first destination row
  destSheet.removeRow(destRow);

  FileOutputStream outputStream = new FileOutputStream("SAMPLENEW.xlsx");
  workbook.write(outputStream);
  outputStream.close();
  workbook.close();

 }
}

@Ariel: I have just seen your bug in the POI issue tracker while looking for something to fix in POI. But honestly, I won't put effort into this. People work voluntarily on POI. Fixing bugs takes time and is not paid for. All you did was create a bug report where you copy/pasted this SO question's title and provided a link. If you want people to fix your bugs, please at least provide a bug report with a [SSCCE or MWE](https://stackoverflow.com/help/minimal-reproducible-example). Show some appreciation and invest those 5 minutes. Thank you. — Axel, Oct 04 '19 at 16:33
@Axel Sorry. Will do that next time. I thought that it was sufficient to point out the bug inside the library and not clutter things with my code. — Ariel, Oct 06 '19 at 10:29
@Ariel: If you attach a simple working test case (sample code and corresponding test data) that exhibits the bug, I will look into it. — Axel, Oct 06 '19 at 12:03

score 1 · Answer 2 · answered Mar 27 '21 at 05:42

1

//fix amountToMove must not be zero:srcRows index base destStartRow+1,avoid the same
int indexAdd1 = 1;
            for (Row row : failRowList) {
                row.setRowNum(indexAdd1);
                indexAdd1++;
            }
            failSheet.copyRows(failRowList,0, CellCopyPolicyFactory.newOnlyValue());

answered Mar 27 '21 at 05:42

HappyRush

11
1

how does this relate to the OP qeustion? – Mr R Mar 27 '21 at 12:23

Why am I getting IllegalArgumentException while using copyRowFrom(...) in XSSFRow?

2 Answers2