5

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

Shumon Saha
  • 1,415
  • 6
  • 16
  • 33

10 Answers10

6

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();
}
Thirupathi Sriramoji
  • 153
  • 1
  • 5
6

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));
    }
VoiceOfUnreason
  • 52,766
  • 5
  • 49
  • 91
2

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();
    }

}
Joel Newman
  • 58
  • 6
  • Doesn't this solution delete all of the sheets in the workbook, not just the active one? The original question asked about just the active sheet. – Chris Jun 06 '13 at 22:46
  • Then all you have to do is create a for loop that copies all but page index 0. Thats very easy to do. Just use int sheetNum = oldFile.getNumberOfSheets(); then use all of the indexes except 0. – Joel Newman Jun 07 '13 at 17:40
2

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);
}
powerMicha
  • 2,753
  • 1
  • 24
  • 31
  • Why don't you first delete the complete sheet using jkraybill's answer and then add an empty sheet. It think it would be cost effective. – Umar Iqbal Mar 05 '14 at 12:58
1

I guess it is an old thread but I also get ConcurrentModificationException. Based on VoiceOfUnreason I found this to work:

        while (xlsSheet.getPhysicalNumberOfRows() > 0) {
          xlsSheet.removeRow(xlsSheet.getRow(xlsSheet.getLastRowNum()));
        }
        if (xlsSheet.getDrawingPatriarch() != null) {
          xlsSheet.getDrawingPatriarch().clear();
        }
user2381739
  • 11
  • 1
1

I also got concurrent modification exception, also using the more "modern" way of doing it :

sheet.forEach(r->sheet.remove(r));

The iterator based solution from @Thirupathi S apparently worked, but for reasons I don't exactly know it was creating xslx files that were not readable by Apple's Numbers and OSX preview (and probably other softwares too).

I suspect this has something to do with the iterator not removing something: the code of the removeRow method is way more complex than the simple iterator remove operation.

Using old plain for-loop with reversed index worked like a charm :

for (int i = sheet.getLastRowNum(); i >= 0; i--) {
  sheet.removeRow(sheet.getRow(i));
}
Simone Gianni
  • 11,426
  • 40
  • 49
1

This solution works fine with me. And also consider special cases, e.g. the sheet is blank, or the spaces between the firstRow and lastRow are present.

public void cleanSheet(Sheet sheet) {
    int numberOfRows = sheet.getPhysicalNumberOfRows();

    if(numberOfRows > 0) {
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            if(sheet.getRow(i) != null) {
                sheet.removeRow( sheet.getRow(i));
            } else {
                System.out.println("Info: clean sheet='" + sheet.getSheetName() + "' ... skip line: " + i);
            }
        }               
    } else {
        System.out.println("Info: clean sheet='" + sheet.getSheetName() + "' ... is empty");
    }       
}
Sten
  • 91
  • 1
  • 10
1

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);
  }
}
Reto Höhener
  • 5,419
  • 4
  • 39
  • 79
1

You probably want to use HSSFWorkbook.removeSheetAt(index).

jkraybill
  • 3,339
  • 27
  • 32
  • Nope, I want to keep the sheet but without any data – Shumon Saha Aug 04 '11 at 06:36
  • Please clarify your original question then. Are you trying to erase the data in every cell but preserve the formatting etc? – jkraybill Aug 04 '11 at 07:06
  • I'm trying to erase data+formatting in every cell, as if the sheet has just been created – Shumon Saha Aug 04 '11 at 09:46
  • 1
    Hey jkraybill, if you add `HSSFWorkbook.createSheet()`, than your answer will fit to Sumon's needs =D – powerMicha Aug 04 '11 at 13:57
  • 1
    For me removing a sheet at a given index occasionally throws a weird out of bounds exception, even though the sheet exists at that number. (POI 3.10) So clearing a sheet instead of re-creating it is imo the better solution at the moment. – 1813222 Apr 09 '14 at 20:38
0

The other iterator methods appeared to work but Excel then refused to open the file. This one worked for me:

int rownum;
while ((rownum=sheet.getLastRowNum()) > 0) sheet.removeRow(sheet.getRow(rownum));
Chris Harlow
  • 61
  • 5