Apache POI remove multiple columns effectively

Question

I have a program which changes multiple excel files in given directory. I need to remove multiple columns from excel file. For example, I want to remove all columns after 5th.

private void deleteColumns(Sheet sheet, int columnIndex) {
    for (Row row : sheet) {
        int j = 0;

        for (Cell cell : row) {
            if (j >= columnIndex) {
                row.removeCell(cell);
            }
            j++;
        }
    }
}

I get ConcurrentModificationException.

I saw a post on how to delete a cell. Apache POI xls column Remove

This idea is clear for me. I will need to create a row, clone necessary cells there, and remove the row.

What are other solutions?

I also tried to create a substitute row, to clone cells which I need, and then remove the original row. I took this idea from here.

Apache POI xls column Remove

I would like to see some better solutions, with less code or more effective then removing cells one by one.

This question will probably help: https://stackoverflow.com/questions/1180110/apache-poi-xls-column-remove — , Jul 10 '17 at 11:22
I do not need to remove first K columns where K is `columnIndex`. — Yan Khonski, Jul 10 '17 at 11:24
see this...https://stackoverflow.com/questions/6936956/how-to-delete-contents-of-an-excel-sheet-in-java/22198038#22198038 — Thirupathi Sriramoji, Jul 10 '17 at 11:39
@ThirupathiSriramoji Thank you man, post your answer, please! — Yan Khonski, Jul 10 '17 at 11:43
I don't think there will be a **better** solution possible you are looking for because of there is nothing like a "column" directly in `Excel` sheet's building plan. A sheet contains rows having cells stored in each row. A "column" is the amount of all cells of certain position in each row. Column 3 for example is the amount of all third cells in each row. But this is an indirect amount and cannot be gotten directly but only indirectly by getting all third cells from each row. It is the same with `HTML` tables for example. — Axel Richter, Jul 10 '17 at 12:25

score 3 · Accepted Answer · answered Jul 10 '17 at 11:46

3

Instead of updating Row directly use Iterator like below

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

answered Jul 10 '17 at 11:46

Thirupathi Sriramoji

153
1
5

1

I accept this answer because there are no other solutions. But I am still interested in making it more effective then removing cells one by one for each row. – Yan Khonski Jul 10 '17 at 11:49
1

The code in this answer attempts deleting all rows in first sheet of a workbook. How is this related to the question? – Axel Richter Jul 10 '17 at 13:46
Hi Axel..This answer fixes 'ConcurrentModificationException' issue which is mentioned in question.. – Thirupathi Sriramoji Jul 10 '17 at 14:01

Apache POI remove multiple columns effectively

1 Answers1