How to delete contents of an Excel sheet in Java?

Question

How to delete contents of an Excel sheet in an Excel workbook, using Java SE and Apache POI?

Solution 1

As mentioned in previous comments

Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
   sheet.removeRow(row);
}

this code throwing ConcurrentModificationException to me. So, I have modified the code and it's working fine. Here is the code:

Sheet sheet = wb.getSheetAt(0);
Iterator<Row> rowIte =  sheet.iterator();
while(rowIte.hasNext()){
    rowIte.next();              
    rowIte.remove();
}

Solution 2

I've found that removeSheetAt/createSheet isn't really an acceptable answer, because you can't put the new sheet into the correct position in the workbook without running into a bug in WorkSheet.setSheetOrder

This code snippet

Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
   sheet.removeRow(row);
}

in my world throws a ConcurrentModificationException

I had to resort to

    for (int index = crnt.getLastRowNum(); index >= crnt.getFirstRowNum(); index--) {
        crnt.removeRow( crnt.getRow(index));
    }

Solution 3

Depending on what contents you want to delete you may remove a single cell or row.

Too erase the complete sheet iterate over all rows and delete it.

Sheet sheet = wb.getSheetAt(0);
for (Row row : sheet) {
   sheet.removeRow(row);
}

Solution 4

I know this is an old thread but I think I found the best solution

What I did was just create a new workbook of the same type and save it over the file that I wanted to delete.

Heres the code

private void clearOldFile(){
    FileOutputStream out = null;
    try{
        oldFile = new XSSFWorkbook();    
        Sheet sheet = oldFile.createSheet("temp data");

        out = new FileOutputStream(AbsolutePathForTempExcelFile);
        oldFile.write(out);
        out.close();

    } catch(Exception e){
        e.printStackTrace();
    }

}

Solution 5

My reason for not deleting and recreating sheet: Keep references to sheet-scoped names working.

for(int i = sheet.getLastRowNum(); i >= 0; i--)
{
  Row row = sheet.getRow(i);
  if(row != null)
  {
    sheet.removeRow(row);
  }
}

View more solutions