Deleting multiple rows in Excel using Apache POI

Question

I have an Excel sheet with a table of 75 rows. In the 76th row I have a Total function of each column =SUM(A1:A75) and =SUM(B1:B75) In the 77th to 92th rows I have an Excel chart which takes A1:A75 and B1:B75 for values Picture of Data and Total and Chart

If I use Apache POI to populate all 75 rows, then everything looks great. (I am using XSSFWorkbook)

If I use Apache POI to populate just 30 rows, then I would like to delete rows 31 to 75. This is where I run into problems.

Were I to delete these Rows in Excel then the Total functions update, and the Excel chart updates to use A1:A30 and B1:B30, and the Excel chart moves up to row 31.

I would like to mirror this behaviour using Apache POI

This is what I have tried

xlsTable.shiftRows(75,91,-45)

(Rows 75 to 91 are the Apache Row equivalents of Excel Rows 76 to 92 and include the Total function and the chart)

I have also tried

for (int i = 75; i > 30; i--) {
    Row r = CellUtil.getRow(i, xlsTable);
    xlsTable.removeRow(r);
}

and

for (int i = 30; i < 75; i) {
    Row r = CellUtil.getRow(i, xlsTable);
    xlsTable.removeRow(r);
}

These just cause my sheet to become messy with #REF errors. I imagine that what I want may not be possible with POI, and that I would need to actually build my entire sheet for the requisite amount of rows, and then add the chart. However I thought I would ask!

you need to combine two of them. Play with the indexes as needed in this code : HSSFRow rowToRemove = sheet.getRow(currentRowIndex); sheet.shiftRows(currentRowIndex+1, lastRowIndex, -1); sheet.removeRow(rowToRemove); — Jimmy
Thanks @Jimmy. I could only get this to not crash with nullPointerException if I swapped the second and third line of your code. Also, the formulae for the TOTAL did update, but the chart did not move and also it still pointed to the original 75 lines... — gordon613
For playing with graph, I personally suggest use Name Ranges to create graph. and then using code update the name ranges... — Sankumarsingh
Hi @Sankumarsingh. Thanks I think your answer (combined with hiding the rows - as opposed to deleting them) is the best solution. If you post it as an answer I aim to accept it. — gordon613

gordon613 gordon613 · Accepted Answer · 2015-04-12T10:19:00

The best solution that I have found so far is to simply hide the rows. In this way, most of the issues have been solved.

The Excel chart moves up
The Excel chart de facto uses only those rows which are unhidden (it ignores hidden rows, so these hidden rows don't create vast "empty spaces" in the chart)
The Total functions work

To be more precise, one could also assign named ranges to the columns, and then set the chart to use these named ranges, and then use code to update the named ranges. However hiding the rows would still be necessary.

Deleting multiple rows in Excel using Apache POI

1 Answers