How to Read number of rows in Excel Files ?

How to Read a number of rows in multiple Excel Files?

• In order to read excel files using java, we have to add the Apache POI Excel library.
• Apache POI library is used to read excel files by using its in-built functions and we can do many operations on excel files(create/edit/read)

Let's say we have excel files in a project like below with data in each excel file:

Apache POI

Read Excel Apache POI

Observe here, ExcelFile1 has 3rows and ExceFile2 has 4rows.

Demo on Reading number of rows in multiple Excel Files:

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

public class excelCountDemo {
    public static void main(String args[]) throws Exception {
        String filePath;
        String directorypath = "D:\\Sessions\\ExcelFiles";
        List<Integer> list = new ArrayList<>();
        File directory = new File(directorypath);
        String[] fileList = directory.list();
        for (String name : fileList) {
            System.out.println("file name=" + name);
            filePath = directorypath+"\\" + name;
            System.out.println("File path=" + filePath);
            File file = new File(filePath);
            FileInputStream inputStream = new FileInputStream(file);
            Workbook wb = new HSSFWorkbook(inputStream);
            Sheet wbSheet = wb.getSheet("Sample sheet");

            //we are adding +1 because getLastRowNum() value starts from index 0;

            int rowTotal = wbSheet.getLastRowNum()+1;
            System.out.println("Total number of records in " + name + " excel report=" + rowTotal);
            list.add(rowTotal);
            System.out.println("added " + rowTotal + " to the list");
            System.out.println("---------------------------------------");
        }
        int sum;
        sum = list.stream().mapToInt(Integer::intValue).sum();
        System.out.println("Total count="+sum);
    }
}

Output:

file name=excelFile1.xls
File path=D:\Sessions\ExcelFiles\excelFile1.xls
Total number of records in excelFile1.xls excel report=3
added 3 to the list
---------------------------------------
file name=excelFile2.xls
File path=D:\Sessions\ExcelFiles\excelFile2.xls
Total number of records in excelFile2.xls excel report=4
added 4 to the list
---------------------------------------
Total count=7

Likewise as above, we can read and count total number of rows in individual excel file and multiple files.

Please comment below to feedback or ask questions.