Deleting (not removing content of) rows of excel using Apache POI

Question

- I found that there was an existing bug with shiftRows function which caused excel to break. https://bz.apache.org/bugzilla/show_bug.cgi?id=57423 I am not exactly sure if this was fixed. I have added a comment on bugzilla to know further on this.

I am trying to delete rows (not removing content of) from an excel file if a particular cell value condition is met.

I achieved this successfully, but the problem is that it breaks my excel and when i reopen the file (manually), excel shows me an error that it broke and when i open the repaired value, the file has lost all its formatting. Below is the code that i have used. I think the culprit here is shiftRows. This is because if i use other option of hiding the cells, the excel does not break and it also works fine. But the thing is I want to delete the rows and not hide it.

My first question is: What could be my options here to change the way excel is behaving here? Second question is , I want to understand the functioning of shiftRows method. The syntax for it is

void shiftRows(int startRow, int endRow, int n);

POI documentation says,

Shifts rows between startRow and endRow n number of rows. * If you use a negative number, it will shift rows up. * Code ensures that rows don't wrap around.

So, lets say i am at Row1 and will remove its contents, then what parameters should I pass to shift whole of the sheet below the Row1, 1 row above. I tried with below and it failed in the sense that t=it gave me null pointer exception on the second for loop iteration.

shiftRows(Row1+1, last row number value, difference between the last row and current row);

Code for filtering and shifting rows:

for(int r=sh.getFirstRowNum(); r<=sh.getLastRowNum(); r++){ //iterating through all the rows of excel
            Row currentRow = sh.getRow(r);                              //get r'th row
            if(currentRow.getCell(1).getStringCellValue().equalsIgnoreCase("Assigned to Duration")){
                int tempRowNum = (currentRow.getRowNum()+1);
                System.out.println("Working on Row: " + (currentRow.getRowNum()+1) + " value " + currentRow.getCell(0).getStringCellValue());
                int tempValue = sh.getLastRowNum() - currentRow.getRowNum();
                System.out.println("Difference: " +tempValue);
                sh.removeRow(currentRow);
//              currentRow.setZeroHeight(true);
                sh.shiftRows(tempRowNum, sh.getLastRowNum(), tempValue);
                r--;
//              System.out.print(" row number below the current row is " + (tempRowNum+1) + " and value is " + sh.getRow(tempRowNum).getCell(0).getStringCellValue());
            }
            countfilter++;
        }
        System.out.println("Total Rows after removing: " + sh.getLastRowNum());
        System.out.println("Loop iterated for " + countfilter + " times.");

        fos = new FileOutputStream(file);
        wb.write(fos);
        wb.close();
        fis.close();
        System.out.println("Row filtering completed");

Excel opening error encountered after running the above code:

Answer 1

I believe the way you want to use shiftRows is as follows:

If you found a row to remove based on cell value 1, then we know this is row r based on your for loop, so:

if(currentRow.getCell(1).getStringCellValue().equalsIgnoreCase("Assigned to Duration")){
    sh.removeRow(currentRow);
    // shift rows 1 row up starting from the row after the row you just removed
    shiftRows(r+1, sh.getLastRowNum(), -1); 
    r--;
}

Answer 2

The questioning code required all the cells' value must be string and cannot be null.
The following code works on POI 3.11, not sure any bugs for latest POI version.

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestData {

    /**   
     * Remove a row by its index   
     * @param sheet a Excel sheet   
     * @param rowIndex a 0 based index of removing row   
     */   
    public static void removeRow(Sheet sheet, int rowIndex) {   
        int lastRowNum = sheet.getLastRowNum();   
        if(rowIndex >=0 && rowIndex < lastRowNum){   
            sheet.shiftRows(rowIndex + 1,lastRowNum, -1);   
        }   
        if(rowIndex == lastRowNum){   
            Row removingRow = sheet.getRow(rowIndex);   
            if(removingRow != null){   
                sheet.removeRow(removingRow);   
            }   
        }   
    }  

    public static void main(String[] args) {
        try {
            Workbook wb = new XSSFWorkbook();
            Sheet sh = wb.createSheet("new sheet");
            for(int i=0; i<6; ++i) {
                Row currentRow = sh.createRow(i);
                currentRow.createCell(0);
                currentRow.getCell(0).setCellValue(String.valueOf(i+1));
                currentRow.createCell(1);
                currentRow.getCell(1).setCellValue(i > 3 ? "Assigned to Duration" : "OMG");
            }

            int countfilter = 0;
            for(int r=sh.getFirstRowNum(); r<=sh.getLastRowNum(); r++){ //iterating through all the rows of excel
                Row currentRow = sh.getRow(r);                              //get r'th row
                System.out.println(currentRow.getCell(1).getStringCellValue());
                if(currentRow.getCell(1).getStringCellValue().equalsIgnoreCase("Assigned to Duration")){
                    removeRow(sh, r--);
                }
                countfilter++;
            }
            System.out.println("Total Rows after removing: " + sh.getPhysicalNumberOfRows());
            System.out.println("Loop iterated for " + countfilter + " times.");

            FileOutputStream fos = new FileOutputStream(new File("/1.xlsx"));
            wb.write(fos);
            wb.close();
            System.out.println("Row filtering completed");

        }
        catch(Exception ex) {
            ex.printStackTrace();
        }

    }
}