Link to home
Start Free TrialLog in
Avatar of sukhoi35
sukhoi35

asked on

Remove rows from excel using Apache POI.

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

Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

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/
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.
Avatar of sukhoi35
sukhoi35

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of sukhoi35
sukhoi35

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial