We help IT Professionals succeed at work.

Remove rows from excel using Apache POI.

sukhoi35
sukhoi35 asked
on
Hello Experts,
Am trying to modify an excel file having multiple rows using Apache POI 3.17 (i.e. remove the rows). When I tried to remove the rows in the first while loop, I used to receive the error concurrentmodificationexception. I tried moving all the rows into a list and then delete it in a separate loop as suggested in one of the forums. I now receive XmlValueDisconnectedException. Can anyone please suggest what might be going wrong here?

public static void removeFalseRows(String readFilePath, String writeFilePath, String sheetName) throws IOException, InvalidFormatException {
		List<Row> toRemove = new ArrayList<Row>();
		FileInputStream fsIP = new FileInputStream(new File(readFilePath));
		Workbook wb = WorkbookFactory.create(fsIP);
		Sheet sheet = wb.getSheet(sheetName);
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();

			if (row.getCell(5).toString().equals("false")) {
				toRemove.add(row);
			}
		}

		for(Row R : toRemove) {
			sheet.removeRow(R);
		}

		for(Row R : toRemove) {
			int rowIndex = R.getRowNum();
			int lastRowNum = sheet.getLastRowNum();
			if (rowIndex >= 0 && rowIndex < lastRowNum)
			{
				sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
			}
		}

Open in new window

Comment
Watch Question

View Solution Only

CEHJ
Top Expert 2016

Commented:
http://technojeeves.com/index.php/aliasjava1/17-errors

You need to connect the code you post to the exception (with full stack trace) line numbers

Also, not that i've done this myself, but if i needed to, i'd try solution 3 here:
https://inneka.com/programming/java/removing-a-row-from-an-excel-sheet-with-apache-poi-hssf/
awking00Information Technology Specialist

Commented:
On its face, your code looks like it should work, so there must be something we're not seeing in your question. As CEHJ has suggested, showing the full stack for the exception would help a lot. I'm guessing but I think the issue could be that you show both a read path and a write path when I assume you mean for them to be the same. You don't show where the workbook is written and, given that a XmlValueDisconnectedException is generated indicates that you are writing to the same workbook twice.
sukhoi35

Author

Commented:
CEHJ and awking00
Many thanks for your response. I have attached the code and the error I get for each of the implementation. Would be of great help if you can help me identify the cause :)
ConcurrentModificationException.zip
XmlValueDisconnectedException.zip
RowDeletion.xlsx