Removing Excel rows with POI

https://poi.apache.org

https://poi.apache.org

When generating Excel Sheets it is sometimes necessary to remove rows which match a given criteria afterwards. The first idea for this would be to use removeRow(Row row) but this has the problem that it deletes all the row contents and leaves the empty row in your sheet which is probably not what you want. To remove the rows as a whole the shiftRows(int startRow, int endRow, int n) is needed.
Lets say we have a xls file with column A being the title which is always filled but we want to remove every row in which the data column B is not filled. For this case the following snippet would be appropriate

/**
* Removes rows where the column B is empty.
* @param sheet the sheet where rows should be removed
*/
private int removeEmptyRows(final Sheet sheet) {
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
boolean isRowEmpty;
if (sheet.getRow(i)==null) {
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
i--;
continue;
}
final Row actualRow = sheet.getRow(i);
isRowEmpty =
actualRow.getCell(1).toString().trim().equals("");
if (isRowEmpty) {
if (i == sheet.getLastRowNum()) {
sheet.removeRow(actualRow);
} else {
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
}
i--;
}
}
}

We define in the for loop to get through all available rows and check within the first if clause if we have a row which is completely null and shift it if yes. The shiftRows takes the start- and the end-row as parameters and shift the so selected row block as is defined by the last parameter int n. This means when we use the -1 in the example all the selected rows will be shifted one up. If we would take a positive 1 as parameter for int n they would be shifted one down.
After that we get the actual row and perform our check on it to determine if the column B is empty. Column B in POI ist the cell 1 as counting of the cells starts at 0.
The next if clause checks if there was an empty row found and if yes the row gets shifted up again. The additional inner if clause is needed in the case the last row contains an empty column B because this can’t be shifted as we cannot select the next row after the last row.

empty cells before

empty cells before

empty cells after

empty cells after

This entry was posted in coding, english, howto, Java and tagged , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

One Comment

  1. Akshay Nimbalkar
    Posted 12. April 2018 at 14:55 | Permalink

    Hello,
    Am trying to Read and Applying filter on Excel. And this filter data am saving in other excel file using r1.getCTRow().setHidden(true); /* Hide Row that does not meet Filter Criteria */.

    Now question is Instead of Hiding How can i delete that rows which are not matching filter Criteria ? Is there any api in apache POI to delete Unmatching Rows ?

    Below is my sample Program:

    package com.excel.sap;

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;

    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.AutoFilter;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilter;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilterColumn;
    import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTFilters;

    public class ReadSheet {

    static XSSFRow row;
    public static void main(String[] args) throws Exception {
    FileInputStream fis = new FileInputStream(new File(“Role_Matrix_Final – Copy.xlsx”));

    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet spreadsheet = workbook.getSheetAt(0);
    Iterator rowIterator = spreadsheet.iterator();

    while (rowIterator.hasNext()) {
    row = (XSSFRow) rowIterator.next();
    Iterator cellIterator = row.cellIterator();

    while ( cellIterator.hasNext()) {
    Cell cell = cellIterator.next();

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_NUMERIC:
    System.out.print(cell.getNumericCellValue() + ” \t\t “);
    break;

    case Cell.CELL_TYPE_STRING:
    System.out.print(cell.getStringCellValue() + ” \t\t “);
    break;
    }
    }
    System.out.println();
    }
    spreadsheet.setAutoFilter(CellRangeAddress.valueOf(“A1:M383”));

    /* Step-1: Get the CTAutoFilter Object */
    CTAutoFilter sheetFilter=spreadsheet.getCTWorksheet().getAutoFilter();

    /* Step -2: Add new Filter Column */
    CTFilterColumn myFilterColumn=sheetFilter.insertNewFilterColumn(0);

    /* Step-3: Set Filter Column ID */
    myFilterColumn.setColId(5L);

    /* Step-4: Add new Filter */
    CTFilter myFilter=myFilterColumn.addNewFilters().insertNewFilter(0);

    /* Step -5: Define Auto Filter Condition – We filter Brand with Value of “A” */
    myFilter.setVal(“TCD”);
    XSSFRow r1;

    /* Step-6: Loop through Rows and Apply Filter */
    for(Row r : spreadsheet) {
    for (Cell c : r) {
    if (c.getColumnIndex()==5 && !c.getStringCellValue().equals(“TCD”)) {
    r1=(XSSFRow) c.getRow();
    if (r1.getRowNum()!=0) { /* Ignore top row */
    /* Hide Row that does not meet Filter Criteria */
    **r1.getCTRow().setHidden(true);**
    }
    }
    }
    }

    FileOutputStream out = new FileOutputStream(new File(“F:\\auto_filter_output.xlsx”));
    workbook.write(out);

    fis.close();
    out.close();
    }

    }

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">