Bug 63660 - XSSFSheet.shiftRows() the cell value didn't ref to new row in version 4.1.0
Summary: XSSFSheet.shiftRows() the cell value didn't ref to new row in version 4.1.0
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2019-08-13 03:27 UTC by chaichunyang
Modified: 2020-09-25 06:26 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description chaichunyang 2019-08-13 03:27:22 UTC
i'd to use poi 4.1.0 generator a excel file with a List Data

and i get a wrong excel file when i use XSSFSheet.shiftRows(),my excel has some contents as footer, so i need to shift the row down before insert a new row. the question is when i shift the row down,the footer cell include the mergedcell has shift down already,but the cell value didn't move,still in old cell's position.

i found the nightly build【poi-bin-4.1.1-SNAPSHOT-20190807.tar.gz】may have solved this problem. 

So. When the version 4.1.1 will be release.  This problem has a great impact on me. 
i use maven to build my project, can i fix this in other way?

thank you very much!

follow the code:

File file = new File("/Users/chaichunyang/Documents/test.xlsx");
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);//already confirm this is a XSSFSheet

//shift the row down at 1
sheet.shiftRows(1, sheet.getLastRowNum(), 1);

//create new row at 1
Row newRow = sheet.createRow(1);
//create new cell
Cell newCell = newRow.createCell(0);
//...handle the List

//out a new excel file
workbook.write(new FileOutputStream(new File("/Users/chaichunyang/Documents/test_new.xlsx")));
Comment 1 Dominik Stadler 2019-11-17 12:17:14 UTC
Apache POI 4.1.1 is available now, can you state if this fixes your problem?
Comment 2 chaichunyang 2020-09-25 06:26:52 UTC
Thanks, this has solved my problem. However, I found a new problem. When there is content up and down the shifted row, I will get a corrupt xlsx file after using shiftRows, which cannot be opened using Microsoft Office, but the file can be displayed normally using LibreOffice.