java - Deleting multiple rows in Excel using Apache POI -

i have excel sheet table of 75 rows. in 76th row have total function of each column =sum(a1:a75) , =sum(b1:b75) in 77th 92th rows have excel chart takes a1:a75 , b1:b75 values

if use apache poi populate 75 rows, looks great. (i using xssfworkbook)

if use apache poi populate 30 rows, delete rows 31 75. run problems.

were delete these rows in excel total functions update, , excel chart updates use a1:a30 , b1:b30, , excel chart moves row 31.

i mirror behaviour using apache poi

this have tried

xlstable.shiftrows(75,91,-45) 

(rows 75 91 apache row equivalents of excel rows 76 92 , include total function , chart)

i have tried

for (int = 75; > 30; i--) {     row r = cellutil.getrow(i, xlstable);     xlstable.removerow(r); } 

and

for (int = 30; < 75; i) {     row r = cellutil.getrow(i, xlstable);     xlstable.removerow(r); } 

these cause sheet become messy #ref errors. imagine want may not possible poi, , need build entire sheet requisite amount of rows, , add chart. thought ask!

the best solution have found far hide rows. in way, of issues have been solved.

1. the excel chart moves
2. the excel chart de facto uses rows unhidden (it ignores hidden rows, these hidden rows don't create vast "empty spaces" in chart)
3. the total functions work

to more precise, 1 assign named ranges columns, , set chart use these named ranges, , use code update named ranges. hiding rows still necessary.