user2496503 user2496503 - 7 months ago 15
Java Question

Permanently Delete Empty Rows Apache POI using JAVA in Excel Sheet

I'd like to permanently delete those rows that are empty have no data of any type! I am doing this like:

private void shift(File f){
File F=f;
HSSFWorkbook wb = null;
HSSFSheet sheet=null;
try{
FileInputStream is=new FileInputStream(F);

wb= new HSSFWorkbook(is);
sheet = wb.getSheetAt(0);
int rowIndex = 0;
int lastRowNum = sheet.getLastRowNum();

if (rowIndex >= 0 && rowIndex < lastRowNum) {
sheet.shiftRows(rowIndex, lastRowNum, 500);
}

FileOutputStream fileOut = new FileOutputStream("C:/juni.xls");
wb.write(fileOut);
fileOut.close();
}
catch(Exception e){
System.out.print("SERRO "+e);
}

}


after shiftRows() i write my new file. But my code has now effect. I just need to remove/delete the empty rows that come inside my data (any ever). so is it possible doing so? if yes, am i doing right? if no can anybody please help me doing so? Thanks in advance!

Answer

If memory recalls shiftRows(int startRow, int endRow, int n) is what you need. I don't quite remember how to check if a row is empty but if you DO know that a row is empty (normally through use of removeRow(Row row)) and you know the rowIndex, then it isn't so bad. By calling:

int rowIndex; //Assume already known and this is the row you want to get rid of
int lastIndex = sheet.getLastRowNum();
sheet.shiftRows(rowIndex + 1, lastIndex, -1);

you shift every row in [rowIndex + 1, lastIndex] inclusive up by 1 (which should delete an empty row effectively). If you have multiple rows and had a way to determine if the row was empty then I suggest something like:

for(int i = 0; i < sheet.getLastRowNum(); i++){
    if(isEmpty(sheet.getRow(i)){
        sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
        i--;//Adjusts the sweep in accordance to a row removal
    }
}

boolean isEmpty(Row row){

//Code to determine if a row is empty

}

As a small note if n is negative, the rows shift up. If n is positive the rows shift down. So I'm not quite sure if you meant to shift that chunk of rows down by 500 or not in your provided code. I hope this helps.

Comments