How to get row count in an Excel file using POI library

Question

Guys I m currently using the POI 3 9 library to work with excel files  I know of the getLastRowNum   function  which returns a number of rows in an Excel file   The only problem is getLastRowNum   returns a number with the count starting from 0   So if an Excel file uses the first 3 rows  getLastRowNum   returns 2  If an Excel file has just 1 row  getLastRowNum   returns 0   The problem occurs when the Excel file is completely empty  getLastRowNum   still returns 0  so I cannot determine if the Excel file has 1 row of data  or if its empty   So how can I detect if an Excel file is empty or not

User · Answer

Sheet getPhysicalNumberOfRows   does not involve some empty rows  If you want to loop for all rows  do not use this to know the loop size

User · Answer

There are two Things you can do  use  int noOfColumns   sh getRow 0  getPhysicalNumberOfCells      or  int noOfColumns   sh getRow 0  getLastCellNum      There is a fine difference between them   Option 1 gives the no of columns which are actually filled with contents If the 2nd column of 10 columns is not filled you will get 9  Option 2 just gives you the index of last column  Hence done  getLastCellNum

User · Answer

Try Sheet getPhysicalNumberOfRows

User · Answer

getLastRowNum   return index of last row   So if you wants to know total number of row   getLastRowNum    1   I hope this will work   int rowTotal   sheet getLastRowNum    1

User · Answer

If you do a check   if  getLastRowNum   lt 1    res  Sheet Cannot be empty   return     This will make sure you have at least one row with data except header  Below is my program which works fine  Excel file has three columns ie  ID  NAME   LASTNAME  XSSFWorkbook workbook   new XSSFWorkbook inputstream           XSSFSheet sheet   workbook getSheetAt 0           Row header   sheet getRow 0           int n   header getLastCellNum            String header1   header getCell 0  getStringCellValue            String header2   header getCell 1  getStringCellValue            String header3   header getCell 2  getStringCellValue            if  header1 equals  ID    amp  amp  header2 equals  NAME                    amp  amp  header3 equals  LASTNAME                  if sheet getLastRowNum   lt 1                   System out println  Sheet empty                             return                                           iterate over sheet to get cell values          else                            SOP  invalid format                              return

User · Answer

To find last data row  in case you created table template in excel where it is filled partially or in between rows are empty  Logic   int count   0  int emptyrow 0  int irow 0  while  rowIterator hasNext          row    Row  rowIterator next        if  count    0  amp  amp   checkIfRowIsEmpty row           else          if count  0  amp  amp  emptyrow  irow               emptyrow             else              emptyrow 0              irow 0                      if emptyrow gt 0           irow              if emptyrow gt 3           break            count

User · Answer

Since Sheet getPhysicalNumberOfRows   does not count empty rows and Sheet getLastRowNum   returns 0 both if there is one row or no rows  I use a combination of the two methods to accurately calculate the total number of rows   int rowTotal   sheet getLastRowNum     if   rowTotal  gt  0      sheet getPhysicalNumberOfRows    gt  0         rowTotal        Note  This will treat a spreadsheet with one empty row as having none but for most purposes this is probably okay

[java] How to get row count in an Excel file using POI library?

Examples related to java

Examples related to excel

Examples related to apache-poi

Examples related to poi-hssf