Java ConcurrentModificationException 在写入Excel时发生的错误。

huangapple
go评论84阅读模式
英文:

Java ConcurrentModificationException Error while writing in Excel

问题

以下是您要翻译的代码部分：

我有一个Java类，我使用它来根据输入在特定单元格中写入特定字符串。
我使用Apache POI。

我的类有两个写入方法。一个用于单行写入，另一个用于多次写入。
多次写入的方法似乎是我的问题，我认为 :(

这是我的写入类：

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TrueExcelWriter {
    
    public TrueExcelWriter(){}

    private static int findRow(XSSFSheet sheet, String cellContent) {
        for (Row row : sheet) {
            for (Cell cell : row) {
                if (cell.getCellType() == CellType.STRING) {
                    if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
                        return row.getRowNum();  
                    }
                }
                    
                if (cell.getCellType().equals(CellType.NUMERIC)){
                    long lookup = (long) cell.getNumericCellValue();
                    String catcher = Long.toString(lookup);
                        
                    if(catcher.equals(cellContent)){
                        return row.getRowNum();
                    }
                }
                    
            }
        }               
        return 0;
    }
    
    public void WriteToAll(String suchobjekt, String ID, String SPID) throws FileNotFoundException, IOException{
    
        final String fileName = "C:/Temp/TTT.xlsx";
        InputStream input = new FileInputStream(fileName);
        
        XSSFWorkbook wb = new XSSFWorkbook(input);
        XSSFSheet sheet = wb.getSheetAt(0);
        
        int colnr = 0; 
        
        switch (suchobjekt) {
            case "option1":
                colnr = 15; 
                break;
             case "option2":
                colnr = 16; 
                break;
            case "option3":
                colnr = 999; 
                break;
        }
        
        Iterator<Row> iterator = sheet.iterator();
        
        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            
            if(nextRow.getRowNum()==0){ 
            continue; //just skip the rows if row number is X or Y
            }
            
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            while (cellIterator.hasNext()) {
                Cell nextCell = cellIterator.next();
                
                String y = nextCell + "";
                
                if(y.equals(ID)){
                    
                    Row r = sheet.getRow(nextRow.getRowNum());
                    if (r == null) {
                        r = sheet.createRow(nextRow.getRowNum());
                    }
                    
                    Cell c = r.getCell(colnr);
                    if (c == null) {
                        c = r.createCell(colnr, CellType.STRING);
                    }
                    c.setCellValue(SPID);
                    
                }
                
            }
            
        }
        
        input.close();
        OutputStream output = new FileOutputStream(fileName);
        wb.write(output);
        output.close();
        wb.close();
        
    }
 
    public void WriteTo(String suchobjekt, String ID, String SPID) throws FileNotFoundException, IOException{
    
        final String fileName = "C:/Temp/TTT.xlsx";
        InputStream input = new FileInputStream(fileName);
        
        XSSFWorkbook wb = new XSSFWorkbook(input);
        XSSFSheet sheet = wb.getSheetAt(0);
        
        int colnr = 0;
        int rownr;
        
        switch (suchobjekt) {
            case "option1":
                colnr = 15; 
                break;
             case "option2":
                colnr = 16; 
                break;
            case "option3":
                colnr = 999; 
                break;
        }
        
        rownr = findRow(sheet, ID);
        
        Row r = sheet.getRow(rownr);
        if (r == null) {
            r = sheet.createRow(rownr);
        }
        
        Cell c = r.getCell(colnr); 
        if (c == null) {
            c = r.createCell(colnr, CellType.STRING);
        }
        c.setCellValue(SPID);
        
        input.close();
        OutputStream output = new FileOutputStream(fileName);
        wb.write(output);
        output.close();
        wb.close();
    
    }
}

我正在使用以下代码在我的Excel文件中写入内容：

try {
    ter.WriteToAll("option2", "abc", "done");
} catch (IOException ex) {
    System.out.println("Error! "+ex);
}

我的Excel文件当前如下所示：

...14   15    16   ...
  ... | ID  | ...
  ... | abc | done
  ... | abc | done
  ... | abc | done
  ... | def | 
  ... | ghi | 
  ... | jkl | 
  ... | jkl | 
  ... | jkl | 
  ... | mno | 
  ... | mno | 
  ... | mno |

如果我使用上面的方法并将其用于所有ID，一切都可以正常工作，除了ID "def"！

我不知道为什么这对这一个不起作用。 Java ConcurrentModificationException 在写入Excel时发生的错误。

如果我尝试使用单行写入函数来处理此ID，它可以正常工作。

但我不知道为什么多行写入函数对这一个不起作用。

我收到以下错误消息：

Exception in thread "AWT-EventQueue-0" java.util.ConcurrentModificationException
	at java.base/java.util.TreeMap$PrivateEntryIterator.nextEntry(TreeMap.java:1208)
	at java.base/java.util.TreeMap$ValueIterator.next(TreeMap.java:1253)
	at easypackaging.TrueExcelWriter.WriteToAll(TrueExcelWriter.java:88)
	at easypackaging.TEST_TrueVerladen.actionPerformed(TEST_TrueVerladen.java:172)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
	at java.desktop/java.awt.Component.processMouseEvent(Component.java:6636)
	at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
	at java.desktop/java.awt.Component.processEvent(Component.java:6401)
	at java.desktop/java.awt.Container.processEvent(Container.java:2263)
	at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5012)
	at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
	at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
	at java.desktop/java.awt.LightweightDispatcher.retarget

<details>
<summary>英文:</summary>

I have a java class which I use to write a specific String in a specific cell, depending on the input.
I use Apache POI.

My Class has two writing methods. One for single line writing and one for writing multiple times.
The method for multiple writing seems to be my trouble, I think :(

here is my writing class:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TrueExcelWriter {

public TrueExcelWriter(){}
private static int findRow(XSSFSheet sheet, String cellContent) {
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == CellType.STRING) {
if (cell.getRichStringCellValue().getString().trim().equals(cellContent)) {
return row.getRowNum();  
}
}
if (cell.getCellType().equals(CellType.NUMERIC)){

// if (DateUtil.isCellDateFormatted(cell)) {
// System.out.println(cell.getDateCellValue());
//
// } else {
long lookup = (long) cell.getNumericCellValue();
String catcher = Long.toString(lookup);

                    if(catcher.equals(cellContent)){
return row.getRowNum();
}

// }

            }
}
}               
return 0;
}
public void WriteToAll(String suchobjekt, String ID, String SPID) throws FileNotFoundException, IOException{
final String fileName = &quot;C:/Temp/TTT.xlsx&quot;;
InputStream input = new FileInputStream(fileName);
XSSFWorkbook wb = new XSSFWorkbook(input);
XSSFSheet sheet = wb.getSheetAt(0);
int colnr = 0; 
switch (suchobjekt) {
case &quot;option1&quot;:
colnr = 15; 
break;
case &quot;option2&quot;:
colnr = 16; 
break;
case &quot;option3&quot;:
colnr = 999; 
break;
}
Iterator&lt;Row&gt; iterator = sheet.iterator();
while (iterator.hasNext()) {
Row nextRow = iterator.next();
if(nextRow.getRowNum()==0){ 
continue; //just skip the rows if row number is X or Y
}
Iterator&lt;Cell&gt; cellIterator = nextRow.cellIterator();
while (cellIterator.hasNext()) {
Cell nextCell = cellIterator.next();
String y = nextCell + &quot;&quot;;
if(y.equals(ID)){
Row r = sheet.getRow(nextRow.getRowNum());
if (r == null) {
// First cell in the row, create
r = sheet.createRow(nextRow.getRowNum());
}
Cell c = r.getCell(colnr);
if (c == null) {
// New cell
c = r.createCell(colnr, CellType.STRING);
}
c.setCellValue(SPID);
}
}
}
input.close();
OutputStream output = new FileOutputStream(fileName);
wb.write(output);
output.close();
wb.close();
}
public void WriteTo(String suchobjekt, String ID, String SPID) throws FileNotFoundException, IOException{
final String fileName = &quot;C:/Temp/TTT.xlsx&quot;;
InputStream input = new FileInputStream(fileName);
XSSFWorkbook wb = new XSSFWorkbook(input);
XSSFSheet sheet = wb.getSheetAt(0);
int colnr = 0;
int rownr;
switch (suchobjekt) {
case &quot;option1&quot;:
colnr = 15; 
break;
case &quot;option2&quot;:
colnr = 16; 
break;
case &quot;option3&quot;:
colnr = 999; 
break;
}
rownr = findRow(sheet, ID);
Row r = sheet.getRow(rownr);
if (r == null) {
// First cell in the row, create
r = sheet.createRow(rownr);
}
Cell c = r.getCell(colnr); 
if (c == null) {
// New cell
c = r.createCell(colnr, CellType.STRING);
}
c.setCellValue(SPID);
input.close();
OutputStream output = new FileOutputStream(fileName);
wb.write(output);
output.close();
wb.close();
}

}

I am using the following code to write something in my excel file:

try {
ter.WriteToAll("option2", "abc", "done");
} catch (IOException ex) {
System.out.println("Error! "+ex);
}


My excel file currently looks like this:

...14 15 16 ...
... | ID | ...
... | abc | done
... | abc | done
... | abc | done
... | def |
... | ghi |
... | jkl |
... | jkl |
... | jkl |
... | mno |
... | mno |
... | mno |


If I use the method from above and used it for all IDs, everything works **except** for ID &quot;***def***&quot;!
I don&#39;t know why this does not work for this single one. :(
If I try the single line writing function for this ID, it works.
But I font know why the multiple writing function does not work for this one.
I am getting the following error message:

Exception in thread "AWT-EventQueue-0" java.util.ConcurrentModificationException
at java.base/java.util.TreeMap$PrivateEntryIterator.nextEntry(TreeMap.java:1208)
at java.base/java.util.TreeMap$ValueIterator.next(TreeMap.java:1253)
at easypackaging.TrueExcelWriter.WriteToAll(TrueExcelWriter.java:88)
at easypackaging.TEST_TrueVerladen.actionPerformed(TEST_TrueVerladen.java:172)
at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
at java.desktop/java.awt.Component.processMouseEvent(Component.java:6636)
at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
at java.desktop/java.awt.Component.processEvent(Component.java:6401)
at java.desktop/java.awt.Container.processEvent(Container.java:2263)
at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5012)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4918)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4547)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4488)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307)
at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2762)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)


`TrueExcelWriter` is the class where I run the `ter.WriteToAll`.
I googled the error but I was not able to solve my problem with what I found. :(
If needed, I can upload my complete (censored) excel sheet for you.
Can you guys help me out? 
Sincerely yours,
Shathos
**UPDATE:**
I wrote a new, simpler algorithm for my multiple line writing which worked for me. :) Thank&#39;s for the help!
New function:

public void WriteToAll_TV(String ID, String SPID) throws FileNotFoundException, IOException{

    final String fileName = &quot;C:/Temp/TTT.xlsx&quot;;
InputStream input = new FileInputStream(fileName);
XSSFWorkbook wb = new XSSFWorkbook(input);
CreationHelper createHelper = wb.getCreationHelper();
XSSFSheet sheet = wb.getSheetAt(0);
XSSFCell cell;
XSSFRow row;
int colnr = 15;
int rownr = 0;
rownr = findRow(sheet, ID);
for(int i = rownr; i &lt; sheet.getLastRowNum(); i++){
row = sheet.getRow(i);
String y = row.getCell(colnr)+&quot;&quot;;
if(y.equals(ID)){
row.createCell(16).setCellValue(createHelper.createRichTextString(SPID));
}
}
input.close();
OutputStream output = new FileOutputStream(fileName);
wb.write(output);
output.close();
wb.close();
}


</details>
# 答案1
**得分**: 1
你在遍历行时添加行来修改表格，这就是导致ConcurrentModificationException异常的原因。
<details>
<summary>英文:</summary>
You are modifying the sheet by adding rows while iterating over the rows. That is where the ConcurrentModificationException is coming from.
</details>

huangapple
  • 本文由 发表于 2020年8月9日 09:02:54
  • 转载请务必保留本文链接：https://go.coder-hub.com/63321549.html
  • apache-poi
  • arraylist
  • excel
  • java
  • runtime-error
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定