How to delete contents of an Excel sheet in an Excel workb开发者_StackOverflowook, using Java SE and Apache POI?
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();
}
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));
}
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);
}
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();
}
}
You probably want to use HSSFWorkbook.removeSheetAt(index)
.
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();
}
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));
}
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");
}
}
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);
}
}
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));
精彩评论