0

I need a way to insert new cells and/or rows between pre-existing rows without deleting any of the rows.

I tried using: 

public static void addRow(File f, int amount, int currentRow)
        throws Exception {
    FileInputStream file = new FileInputStream(f);
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);
    sheet.shiftRows(currentRow, currentRow + amount, amount, true, true);
    file.close();
    FileOutputStream out = new FileOutputStream(f);
    workbook.write(out);
    out.close();
}

But unfortunately it deletes some rows down the line to accommodate the shift. The deleted rows seem to be random they aren't actually directly below the shifted rows or above. They seem to happen inbetween shifted rows if I shift more than one row.

Thank you in advance for your help.

Ya Wang
  • 1,758
  • 1
  • 19
  • 41
  • http://stackoverflow.com/questions/5785724/how-to-insert-a-row-between-two-rows-in-an-existing-excel-with-hssf-apache-poi – user432 Jun 10 '14 at 06:41

1 Answers1

5

The second argument of the API is "endRow" - by passing "currentRow+amount" you will not shift all rows starting from the insertion point up to the end. Rather, you will just shift "amount" rows - this could well cause an overwrite for all rows beyond "currentRow+amount" (if there are any).

I would do it this way:

sheet.shiftRows(currentRow, sheet.getLastRowNum()+1, amount, true,true);
Sandro
  • 120
  • 1
  • 5