user2496503 user2496503 - 6 months ago 6
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;
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");
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!


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++){
        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.