Apache Poi Xls Column Remove

Apache POI remove multiple columns effectively

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

Unable to delete row using Apache POI because the cell contains email address

The problem is not the email address as such but the fact that those cells have links in them. Links have references and the updating those references fails while shiftRows. One could call this a bug. But the better approach for deleting row 2 (row index 1) is first to delete the row's content and then second shift the other rows up. The removeRow properly removes the link references too.

...
  int lastRowNum = sheet.getLastRowNum();
  Row row = sheet.getRow(1);
  if (row != null) sheet.removeRow(row);
  sheet.shiftRows(2, lastRowNum, -1);
...

This works for me using apache poi 3.17. Note apache poi 4.0.1 has other issues using shiftRows. See Impossible to delete first row of Excel sheet.

skip or delete column from excel using poi

give the property as:

<property key="skip.sheetname" value="column name(s) to delete" />
<property key="skip.sheetname" value="column name(s) to delete" />

and then Try this:

if (props.containsKey("skip." + getSheetName())) {
     colName = props.getProperty("skip." + getSheetName());
     newList = Arrays.asList(colName.split(","));

     for (String skipHeader : newList) {
            Boolean flag = true;
            for (i = 0; i < headerData.size() && flag; i++) {
                if (headerData.get(i).equalsIgnoreCase(skipHeader)) {
                    headerData.set(i, "qwe");
                    flag = false;
                    skipColIndex.add(i);
                }

            }
    }
}

Apache POI - Delete and move up Cell

Here's an example of how I would do it:

public static void main(String[] args) throws InvalidFormatException, IOException {
    FileInputStream inp = new FileInputStream(FILENAME);

    HSSFWorkbook wb = (HSSFWorkbook) WorkbookFactory.create(inp);
    HSSFSheet sheet = wb.getSheetAt(0);

    String selectedid = "3";

    int rowIndex = getRowIndexOfId(sheet, selectedid);

    removeRow(sheet, rowIndex);

    OutputStream out = new FileOutputStream(FILENAME);

    wb.write(out);
    out.close();
}
private static int getRowIndexOfId(HSSFSheet sheet, String selectedid) {
    DataFormatter formatter = new DataFormatter();
    for (Row row : sheet) {
        for (Cell cell : row) {
            if (formatter.formatCellValue(cell).trim().equals(selectedid)) {
                return row.getRowNum();
            }
        }
    }
    return -1;
}
private static void removeRow(HSSFSheet sheet, int rowIndex) {
    if (rowIndex >= 0) {
        sheet.removeRow(sheet.getRow(rowIndex));
        if(rowIndex < sheet.getLastRowNum()) {
            sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1);
        }
    }
}

A few comments:

1. You can use the class DataFormatter to format the cell values (so you can compare any cell value)

2. I - like you - compare any cell in a cell; the typical use case for me would be to search the first col of each row to find the id. SO if you want that, adjust the code accordingly.

3. I did the shift row in the end, which should work better.

---

Related Topics