Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Removing a row from an Excel sheet with Apache POI HSSF

I'm using the Apache POi HSSF library to import info into my application. The problem is that the files have some extra/empty rows that need to be removed first before parsing.

There's not a HSSFSheet.removeRow( int rowNum ) method. Only removeRow( HSSFRow row ). The problem with this it that empty rows can't be removed. For example:

sheet.removeRow( sheet.getRow(rowNum) ); 

gives a NullPointerException on empty rows because getRow() returns null. Also, as I read on forums, removeRow() only erases the cell contents but the row is still there as an empty row.

Is there a way of removing rows (empty or not) without creating a whole new sheet without the rows that I want to remove?

like image 779
fmaste Avatar asked Dec 02 '09 18:12

fmaste


People also ask

How do you delete a row in Excel using Java code?

removeRow(row); you can use the shiftRows(int startRow, int endRow, int n) instruction, that seems you have tried, that shifts rows between startRow and endRow n number of rows. // ... code before ... for (int i=0; i<=lastIndex; i++) { row=sheet. getRow(i); if(row.

How do you delete a column in Excel using Apache POI?

For every row, delete the cell representing the column which shall be deleted and move all cells to the right of this column one to the left.

How do I delete multiple rows in Excel using Java?

To delete multiple rows from a worksheet, call the deleteRows method of the Cells collection. The deleteRows method takes two parameters: Row index: the index of the row from where the rows will be deleted. Number of rows: the total number of rows that need to be deleted.


1 Answers

 /**  * Remove a row by its index  * @param sheet a Excel sheet  * @param rowIndex a 0 based index of removing row  */ public static void removeRow(HSSFSheet sheet, int rowIndex) {     int lastRowNum=sheet.getLastRowNum();     if(rowIndex>=0&&rowIndex<lastRowNum){         sheet.shiftRows(rowIndex+1,lastRowNum, -1);     }     if(rowIndex==lastRowNum){         HSSFRow removingRow=sheet.getRow(rowIndex);         if(removingRow!=null){             sheet.removeRow(removingRow);         }     } } 
like image 180
AndreAY Avatar answered Sep 19 '22 17:09

AndreAY