How to skip or delete rows from the excel file using apache POI

Question

I am working with an excel file which i have to convert to a text file and i have to choose specific rows from that.I have done already but i am stuc with the end of the file bacause i have to delete or skip 3 rows there and their position might change dynamially.So i am sharing the screen of the excel file

Three hightlighted rows i have to skip or delete and their position might change dynamically.I am giving the code which i have used to get specific rows from the file

@Override
public List < String > processSheet(Sheet sheet) {
    ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " started");
    List < String > rows = new ArrayList < String > ();
    Iterator < Row > rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        Row currentRow = rowIterator.next();
        StringBuilder row = new StringBuilder();
        for (int i = 0; i < currentRow.getLastCellNum(); i++) {
            if (currentRow.getRowNum() == 0 || currentRow.getRowNum() == 1 || 
                    currentRow.getRowNum() == 2 || currentRow.getRowNum() == 3 || 
                    currentRow.getRowNum() == 4 || currentRow.getRowNum() == 5 || 
                    currentRow.getRowNum() == 6 || currentRow.getRowNum() == 7 || 
                    currentRow.getRowNum() == 9 || currentRow.getRowNum() == 10 || 
                    currentRow.getRowNum() == 11) {
                continue;
            } else {
                Cell currentCell = currentRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                String cellValue = excelManager.evalCell(currentCell);

                if (cellValue.contains("Total")) { //i have tried here
                    continue;
                }
                if (!cellValue.isEmpty()) {
                    row.append(cellValue);
                }

                //adjusting the cell values with the | or BLANK 
                if (currentCell == null || currentCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                    row.append("");
                } else {
                    row.append(SEPARATOR);
                }
            }
        }
        if (!row.toString().isEmpty()) {
            rows.add(row.toString());
        }
    }
    ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " completed");
    return rows;
}

I can not skip by position because the position might change by time.

You could check value in the cell in the first column in screenshot. If it is empty or contains Total then skip that row. — Ivan
– Ivan, Commented Feb 8, 2018 at 20:17
@Ivan can you modify my code please ? i really not getting any idea — Mandrek
– Mandrek, Commented Feb 8, 2018 at 20:19
@Ivan see i have made some try but still it is not getting in prope way — Mandrek
– Mandrek, Commented Feb 8, 2018 at 20:30

Ivan · Accepted Answer · 2018-02-08 21:08:52Z

I assume that column with values ARC... is the first column. If not then please change value for startCell variable to correct value.

@Override
public List < String > processSheet(Sheet sheet) {
    ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " started");
    List <String> rows = new ArrayList<String>();
    Iterator<Row> rowIterator = sheet.iterator();
    final int startCell = 0;
    while (rowIterator.hasNext()) {
        Row currentRow = rowIterator.next();
        StringBuilder row = new StringBuilder();
        // not sure if you really need this if statement in your Prod code
        if (currentRow.getRowNum() == 0 || currentRow.getRowNum() == 1 || 
                currentRow.getRowNum() == 2 || currentRow.getRowNum() == 3 || 
                currentRow.getRowNum() == 4 || currentRow.getRowNum() == 5 || 
                currentRow.getRowNum() == 6 || currentRow.getRowNum() == 7 || 
                currentRow.getRowNum() == 9 || currentRow.getRowNum() == 10 || 
                currentRow.getRowNum() == 11) {
            continue;
        } 
        if (currentRow.getLastCellNum() < 0 || currentRow.getCell(startCell) == null || "".equals(currentRow.getCell(startCell).getStringValue()) || (currentRow.getCell(startCell).getStringValue() != null && currentRow.getCell(startCell).getStringValue().contains("Total"))) {
            continue;
       }
       for (int i = 0; i < currentRow.getLastCellNum(); i++) {
         else {
            Cell currentCell = currentRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            String cellValue = excelManager.evalCell(currentCell);
            if (!cellValue.isEmpty()) {
                row.append(cellValue);
            }

            //adjusting the cell values with the | or BLANK 
            if (currentCell == null || currentCell.getCellType() == Cell.CELL_TYPE_BLANK) {
                row.append("");
            } else {
                row.append(SEPARATOR);
            }
        }
    }
    if (!row.toString().isEmpty()) {
        rows.add(row.toString());
    }
  }
  ActivityLauncher.logConsoleMsg("Processing sheet " + sheet.getSheetName() + " completed");
  return rows;
}

Collectives™ on Stack Overflow

How to skip or delete rows from the excel file using apache POI

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related