如何使用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屋!