问题描述
在提及我的previous问题How来计算行数在使用Java Excel文档的一列的我能够计算在给定的片材的总列数。现在，一半的工作尚未完成的我要计算在特定列的行数。可能的解决方法可以是使用二维数组存储和列索引和总行或使用地图，等我怎样才能做到这一点？ Java的code为这里提供。我为我的演示文件中获取权数（列数）。请修改/根据需要提出修改意见。
（编辑）：我用搭扣地图计算存储列索引的关键和行数的值，但是它不是工作，可能是应用逻辑是错误的。好吧，如果我想使用做到这一点的散地图的我怎么可以存储在一个特定的列行数（在迭代）为值
Java的code：
进口的java.io.File;
进口java.io.FileInputStream中;
进口java.io.FileNotFoundException;
进口java.io.IOException异常;
进口的java.util.HashMap;
进口java.util.Iterator的;进口org.apache.poi.ss.formula.functions.Column;
进口org.apache.poi.ss.usermodel.Cell;
进口org.apache.poi.ss.usermodel.Row;
进口org.apache.poi.xssf.usermodel.XSSFRow;
进口org.apache.poi.xssf.usermodel.XSSFSheet;
进口org.apache.poi.xssf.usermodel.XSSFWorkbook;公共类ExcelRead {
静态INT colrange = 1000;
公共静态无效的主要（字串[] args）{
HashMap的HM =新的HashMap（）;
诠释计数= 0;
尝试{
的FileInputStream文件=新的FileInputStream（新文件（C：/Users/vinayakp/Desktop/Demo2.xlsx））;
XSSFWorkbook工作簿=新XSSFWorkbook（文件）;
XSSFSheet片= workbook.getSheetAt（0）;
迭代器＆LT;行＆GT; rowIterator = sheet.iterator（）;
而（rowIterator.hasNext（））{
鳞次栉比= rowIterator.next（）;
迭代＆所述;电池＆gt; cellIterator = row.cellIterator（）;
而（cellIterator.hasNext（））{
电池单元= cellIterator.next（）;
开关（cell.getCellType（））{
案例Cell.CELL_TYPE_BOOLEAN：
System.out.print（cell.getBooleanCellValue（）+\\ t \\ t的）;
打破;
案例Cell.CELL_TYPE_NUMERIC：
System.out.print（cell.getNumericCellValue（）+\\ t \\ t的）;
打破;
案例Cell.CELL_TYPE_STRING：
System.out.print（cell.getStringCellValue（）+\\ t \\ t的）;
打破;
}
}
的System.out.println（）;
} 为（行R：表）
{
短minColIx = r.getFirstCellNum（）;
短maxColIx = r.getLastCellNum（）;
对于（短colIx = minColIx; colIx＆LT; maxColIx; colIx ++）{
小区C = r.getCell（colIx）;
如果（C！= NULL）{
如果（c.getCellType（）== Cell.CELL_TYPE_STRING || c.getCellType（）== Cell.CELL_TYPE_NUMERIC || c.getCellType（）== Cell.CELL_TYPE_BOOLEAN）{
算上++; --- //我可以使用哈希code在这里得到的键值对？键=列索引值=该列中的总行数
}
}
别人休息;
}
} 的System.out.println（列\\ n总计数：\\ t+计数）;
的System.out.println（HM）;
file.close（）;
}赶上（FileNotFoundException异常五）{
e.printStackTrace（）;
}赶上（IOException异常AE）{
ae.printStackTrace（）;
}
}
}
试试这个：
私人无效excelReader（）{
字符串数据;
尝试{
InputStream为=新的FileInputStream（Read.xlsx）;
工作簿WB = WorkbookFactory.create（是）;
苫布苫布= wb.getSheetAt（0）;
迭代器rowIter = sheet.rowIterator（）;
行R =（行）rowIter.next（）;
短lastCellNum = r.getLastCellNum（）;
INT [] = DATACOUNT新INT [lastCellNum]
INT COL = 0;
rowIter = sheet.rowIterator（）;
而（rowIter.hasNext（））{
迭代cellIter =（（行）rowIter.next（））cellIterator（）;
而（cellIter.hasNext（））{
细胞;细胞=（Cell）的cellIter.next（）;
COL = cell.getColumnIndex（）;
DATACOUNT [COL] + = 1;
DataFormatter DF =新DataFormatter（）;
数据= df.formatCellValue（单元）;
的System.out.println（数据：+数据）;
}
}
is.close（）;
为（中间体X = 0; X＆下; dataCount.length; X ++）{
的System.out.println（栏+ X +：+ DATACOUNT [X]）;
}
}
赶上（例外五）{
e.printStackTrace（）;
返回;
}
}
测试code
我创建XLSX文件与下面的单元格数据：
COL0 COL1 col2的COL3 COL4
1一λA Q
2 B Y形ŞW¯¯
的3CžD E
4 D由于F R
5（E T）
ÿ
DATACOUNT数组的内容是这样的：
COL 0：6
栏1：6
西2：4
西3：5
COL 4：7
右边的数字计数细胞与每一列数据的数目，其中包括标题行
如果您要排除的标题行，只是删除行：
rowIter = sheet.rowIterator（）;
之前while循环。
这是你在寻找什么？
In reference to my previous question How to calculate number of rows in a column of Excel document using Java i was able to calculate the total number of columns in the given sheet. Now half of the work is yet to be done as i want to calculate the number of rows in a particular column. Possible solution could be using 2d array and storing column index and the total rows or using map, etc. How i can achieve this? Java code is provided here. I'm getting right count(column count) for my demo file. Please modify/suggest changes as required.
(edit): i've used hasp map to calculate store column index as key and row count as value, but it wasnt working, may be the applied logic was wrong. Well, if i want to accomplish this by using Hash Map, how i can store number of rows in a particular column(while iterating) as a value
Java Code:
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.HashMap;
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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelRead {
static int colrange=1000;
public static void main(String[] args) {
HashMap hm=new HashMap();
int count=0;
try {
FileInputStream file = new FileInputStream(new File("C:/Users/vinayakp/Desktop/Demo2.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("");
}
for(Row r:sheet)
{
short minColIx=r.getFirstCellNum();
short maxColIx=r.getLastCellNum();
for(short colIx=minColIx;colIx<maxColIx;colIx++) {
Cell c= r.getCell(colIx);
if(c!=null) {
if(c.getCellType()== Cell.CELL_TYPE_STRING||c.getCellType() == Cell.CELL_TYPE_NUMERIC||c.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
count++; ---// can i use hashcode in here to get the key and value pair? key=column index value=total number of rows in that column
}
}
else break;
}
}
System.out.println("\nTotal Number of columns are:\t"+count);
System.out.println(hm);
file.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException ae) {
ae.printStackTrace();
}
}
}
Try this:
private void excelReader() {
String data;
try {
InputStream is = new FileInputStream("Read.xlsx");
Workbook wb = WorkbookFactory.create(is);
Sheet sheet = wb.getSheetAt(0);
Iterator rowIter = sheet.rowIterator();
Row r = (Row)rowIter.next();
short lastCellNum = r.getLastCellNum();
int[] dataCount = new int[lastCellNum];
int col = 0;
rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
Iterator cellIter = ((Row)rowIter.next()).cellIterator();
while(cellIter.hasNext()) {
Cell cell = (Cell)cellIter.next();
col = cell.getColumnIndex();
dataCount[col] += 1;
DataFormatter df = new DataFormatter();
data = df.formatCellValue(cell);
System.out.println("Data: " + data);
}
}
is.close();
for(int x = 0; x < dataCount.length; x++) {
System.out.println("col " + x + ": " + dataCount[x]);
}
}
catch(Exception e) {
e.printStackTrace();
return;
}
}
Tested code
I created an xlsx file with the following cell data:
Col0 Col1 Col2 Col3 Col4
1 a x a q
2 b y s w
3 c z d e
4 d f r
5 e t
y
The contents of dataCount array is this:
col 0: 6
col 1: 6
col 2: 4
col 3: 5
col 4: 7
The numbers on the right count the number of cells with data for each column, including the header row.
If you want to exclude the header row, just remove the line:
rowIter = sheet.rowIterator();
just before the while loop.
Is this what you are looking for?
这篇关于算上Excel工作表的列行数（Java的code提供）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！