Permanently delete Apache POI blank lines using JAVA in Excel Sheet

Question

I want to permanently delete those empty lines that have no data of any type! I do it like this:

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 will write my new file. But my code is now valid. I just need to remove / delete the blank lines that go into my data (ever). so can this be done? if yes, am i right? if no one can help me with this? Thank you in advance!

Answer 1

If the memory resembles shiftRows(int startRow, int endRow, int n) , then you need to. I don’t quite remember how to check if the row is empty, but if you know that the row is empty (usually using removeRow(Row row) ) and you know rowIndex, then this is not 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 change each row in [rowIndex + 1, lastIndex] inclusively to 1 (which should effectively remove an empty row). If you have multiple lines and you have a way to determine if the line was empty, 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 negative, the lines are shifted up. If n positive, the rows are shifted down. Therefore, I am not entirely sure that you want to move this piece of lines to 500 or not in the code you provided. Hope this helps.

Answer 2

Instead of the shiftRows method. Try the removeRow method.

See here for more information.