How to calculate number of rows in a column of Excel document using Java

Question

I have a java code which fetches data from excel document. I want to calculate the number of columns and total number of rows(in a particular column). How can I achieve this? Java code and desired o/p is provided below

(edit): what modification I should make to get the desired o/p for e.g. I should write a loop to get the count of columns and rows or there is a method to do the same

Desired O/P

ColumnA ColumnB ColumnC
Vinayak James   Dan
India   US      Denmark

 Total number of Columns: 3
number of data in ColumnA:2
number of data in ColumnB:2
number of data in ColumnC:2  

(EDIT):- Answered here-- Count number of rows in a column of Excel sheet(Java code provided)

My Java Code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.formula.functions.Column;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelRead {
    public static void main(String[] args) {
        int count=0;
    try {
        FileInputStream file = new FileInputStream(new File("C:/Users/vinayakp/Desktop/Book.xlsx"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while(rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while(cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch(cell.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t\t");
                        break;
                }
            }
            System.out.println("");
        }

        file.close();    
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException ae) {
        ae.printStackTrace();
    }
}
}

Output I'm getting is:

ColumnA ColumnB ColumnC
Vinayak James   Dan
India   US      Denmark

I need to get the desired o/p as shown above. Code is working fine however I need to get the count values of column and rows. Kindly provide me the solution for the same. I had problems with the code earlier which was resolved in this question: Issue while reading Excel document (Java code)

Answer 1

I think you can use the code suggested here to get the columns and then for each row in a column (though it's more for each column in the row concerning POI's approach), just count the values you need.

So your code would probably follows something as:

for(Row row : sheet) {
   short minColIx = row.getFirstCellNum();
   short maxColIx = row.getLastCellNum();
   for(short colIx = minColIx; colIx<maxColIx; colIx++) {
     Cell c = row.getCell(colIx);
     if(c != null) {
        if(c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
           // add c.getNumericCellValue()
        }
     }
   }
}

Also nice ideas from poi api docs for working with column numbers.

Answer 2

The code uses Iterator to parse each row and column value. It doesn't know how many items it's got left to iterate over, so you can't query it for that result.

However you can get the last row index using API getLastRowNum (reference).

Gets the number last row on the sheet. Owing to idiosyncrasies in the excel file format, if the result of calling this method is zero, you can't tell if that means there are zero rows on the sheet, or one at position zero. For that case, additionally call getPhysicalNumberOfRows() to tell if there is a row at position zero or not.

Similarly for each cell you have API getLastCellNum in HSSFRow class (reference).

You need to alter your code to take advantage of these APIs.

Tutorial: Read/Write Excel in Java

Answer 3

I'm not familiar with working with Excel workbooks in java code, but you have an iterator right there. Couldn't you simply add an int to it and increment it every iteration?

int counter = 0;
while(rowIterator.hasNext()) {
...
counter++;
}

With your cascaded iterators, you can keep track of the different numbers you want using several counters;

int[] counters = new int[4];
counters[0] = 0; // number of columns
counters[1] = 0; // number of rows in colA
counters[2] = 0; // number of rows in colB
counters[3] = 0; // number of rows in colC

while(rowIterator.hasNext()) {
    /* 
     * keep track of columns. Useful if colD and more may exist too, but
     * you don't want them counted
     */
    int col = 0;
    while(cellIterator.hasNext()) {
        switch(col) {
        case 0 : counters[1]++; break; // add row to colA counter
        case 1 : counters[2]++; break; // add row to colB counter
        case 2 : counters[3]++; break; // add row to colC counter
        }
        col++;
    }
    counters[0] = col; // save number of columns
}

This may not be the best way mind you, but I think it should work well enough. Bare in mind that I have no experience in this either so if anyone answers with a solution working with the xssf classes, it's likely better. Also, I have not tested this, but it should at least offer you one approach.

I don't know how xssf handle's this, but you may want to check if the cells are empty before counting them. Judging by your posted code however, I guess that's not necessary.

If you have a variable number of columns (likely, otherwise, why count?) you should make a list of variable length with counters in them, and add a new counter int for every column. Hope this helps you!