如何使用Java计算Excel文档列中的行数 [英] How to calculate number of rows in a column of Excel document using Java

问题描述

我有一个从 excel 文档中获取数据的 java 代码.我想计算列数和总行数(在特定列中).我怎样才能做到这一点?下面提供了 Java 代码和所需的 o/p

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

(编辑):我应该进行哪些修改以获得所需的o/p，例如我应该写一个循环来获取列和行的数量，或者有一种方法可以做到这一点

(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

期望的 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  

(编辑):- 在这里回答-- 计算Excel表格一列的行数(提供Java代码)

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

我的 Java 代码:

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();
    }
}
}

我得到的输出是:

ColumnA ColumnB ColumnC
Vinayak James   Dan
India   US      Denmark

如上所示，我需要获得所需的 o/p.代码工作正常但是我需要获取列和行的计数值.请为我提供相同的解决方案.我之前的代码有问题，在这个问题中得到了解决:阅读 Excel 时出现问题文档(Java 代码)

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)

推荐答案

我认为您可以使用建议的代码 here 获取列，然后获取列中的每一行(尽管与 POI 方法有关的行中的每一列更多)，只需计算您需要的值.

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()
        }
     }
   }
}

还有来自 poi api 文档 用于处理列号.

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

这篇关于如何使用Java计算Excel文档列中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！