Removing names from an XSSFWorkbook?

user_5661161 0
Viewed 36

I want to remove all names from an XSSFWorkbook. When I iterate over all the names like below I get a ConcurrentModificationException:

for (XSSFName name : workbook.getAllNames()) {
    workbook.removeName(name);
}

When I use an iterator like below I get an UnsupportedOperationException:

Iterator<XSSFName> nameIterator = workbook.getAllNames().iterator();
while (nameIterator.hasNext()) {
    nameIterator.remove();
}

How would I accomplish this? Thankful for any tips.

apache-poi java
1 Answers

In Apache POI Workbook.getAllNames returns an unmodifiable collection. That's why Iterator.remove will not work.

To remove a Name Workbook.removeName needs to be called. But of course that will not work if called from top-down iteration. And for (XSSFName name : workbook.getAllNames()) is nothimg else than for (Iterator<? extends Name> i = workbook.getAllNames().iterator(); i.hasNext();). A vicious circle ;-).

What one can do is gettig the List of all names and then iterating bottom to top and remove.

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;

class ExcelRemoveAllNames {

 public static void main(String[] args) throws Exception {
  String fileIn = "./TestIn.xlsx";
  String fileOut = "./TestOut.xlsx";

  try (Workbook workbook = WorkbookFactory.create(new FileInputStream(fileIn));
       FileOutputStream out = new FileOutputStream(fileOut)) {

   java.util.List<? extends Name> names = workbook.getAllNames();
   for (int i = names.size()-1; i >= 0; i--) {
    Name name = names.get(i);
    System.out.println(name);
    workbook.removeName(name);
   }
       
   workbook.write(out);
  } 
 }
}
user_39154310
Related