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

Java* apachepoi

Avatar of undefined
Last Comment
sukhoi35

8/22/2022 - Mon
CEHJ

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/
awking00

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

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
sukhoi35

ASKER
ASKER CERTIFIED SOLUTION
sukhoi35

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question