blocks|key|1883096|text|++++++++for(org.apache.poi.ss.usermodel.Row+tmp+:+hssfSheet){
++++++++++++for(int+i+=+0;+i<8;i%2B%2B){
++++++++++++++++System.out.println(tmp.getCell(i));
++++++++++++}+++++++++++++++
++++++++}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1883097|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code> for(org.apache.poi.ss.usermodel.Row tmp : hssfSheet){
 for(int i = 0; i&lt;8;i++){
 System.out.println(tmp.getCell(i));
 } 
 }
</code></pre>

blocks|key|3993203|text|同样的问题也让我很沮丧。这是我在poi-3.7-20101029和poi-3.8中找到的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3993204|RowIterator和CellIterator不支持迭代空单元格或行--仅支持物理定义的单元格(可以为空)。|3993205|返回我期望的结果的解决方案需要使用从0开始的Row.getCell([int],+Row.CREATE_NULL_AS_BLANK)，就像Chavira的答案所暗示的那样(假设有8个单元格行)。或者，您可以在迭代时使用Cell.columnIndex值来检查跳跃数字...|offset|length|style|CODE|3993206|令人恼火的是，在使用方法#1创建空单元格之后，迭代器将返回现在创建的空单元格。我认为CellIterator忽略MissingCellPolicy是一个错误。|3993207|entityMap^0|0|0|M|18|31|G|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@$F|Q|G|R|H|I]|$F|S|G|T|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|M|$]]

I have been frustrated by this same problem. Here is what I found with poi-3.7-20101029 and poi-3.8.

RowIterator and CellIterator do not support iterating over NULL cells or rows -- only physically defined cells (which can be BLANK).

The solution that returns what I expect requires using the 0-based <code>Row.getCell([int], Row.CREATE_NULL_AS_BLANK)</code>, much like Chavira's answer alludes to (assuming 8 cell rows). Or you can use the <code>Cell.columnIndex</code> value while iterating to check for jumping numbers...

Annoyingly, after creating blank cells using method #1, the iterators will return the now created BLANK cells. I consider it a bug that MissingCellPolicy is ignored by CellIterator.

blocks|key|1883153|text|如果你想得到所有的单元格，不管它们是否存在，那么迭代器不适合你。取而代之的是，您需要手动获取适当的单元，很可能是缺少单元策略|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1883154|for(Row+row+:+sheet)+{
+++for(int+cn=0;+cn<row.getLastCellNum();+cn%2B%2B)+{
+++++++//+If+the+cell+is+missing+from+the+file,+generate+a+blank+one
+++++++//+(Works+by+specifying+a+MissingCellPolicy)
+++++++Cell+cell+=+row.getCell(cn,+Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
+++++++//+Print+the+cell+for+debugging
+++++++System.out.println("CELL:+"+%2B+cn+%2B+"+-->+"+%2B+cell.toString());
+++}
}|code-block|syntax|javascript|1883155|在the+Apache+POI+documentation+on+iterating+over+cells中有更多关于这一切的详细信息|offset|length|1883156|entityMap|0|LINK|mutability|MUTABLE|url|http://poi.apache.org/spreadsheet/quick-guide.html#Iterator^0|0|0|1|1G|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

If you want to get all cells, no matter if they exist or not, then the iterator isn't for you. Instead, you need to manually fetch the appropriate cells, likely with a missing cell policy

<pre><code>for(Row row : sheet) {
 for(int cn=0; cn&lt;row.getLastCellNum(); cn++) {
 // If the cell is missing from the file, generate a blank one
 // (Works by specifying a MissingCellPolicy)
 Cell cell = row.getCell(cn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
 // Print the cell for debugging
 System.out.println("CELL: " + cn + " --&gt; " + cell.toString());
 }
}
</code></pre>

There's more details on all of this in <a href="http://poi.apache.org/spreadsheet/quick-guide.html#Iterator" rel="noreferrer">the Apache POI documentation on iterating over cells</a>

blocks|key|1559302|text|原因很简单:+Excel文件可以包含尽可能多的行和列，因此返回所有可用的空白行和列将使单元格变得巨大且占用大量内存。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1559303|假设您有一个10x10的工作表，在Excel中，它不是“确切的”10x10，因为您可以很容易地用空白单元格添加11x10，那么POI是否应该返回第11列？|1559304|完成请求的一种方法是使用HSSFCell.getColumnIndex()。|offset|length|style|CODE|1559305|示例：|1559306|//Assuming+your+have+a+2+dimensional+array.
String[][]+values+=+......;//+It+is+assigned

POIFSFileSystem+fileSystem+=+new+POIFSFileSystem(new+FileInputStream(fileName));
HSSFWorkbook+workbook+=+new+HSSFWorkbook(fileSystem);

//Going+through+every+worksheet.
for+(int+sheetPos+=+0;+sheetPos+<+workbook.getNumberOfSheets();+sheetPos%2B%2B)+{
++++HSSFSheet+sheet+=+workbook.getSheetAt(sheetPos);

++++int+rowPos+=+0;
++++Iterator<Row>+rows+=+sheet.rowIterator();
++++while+(rows.hasNext())+{
++++++++HSSFRow+row+=+(HSSFRow)+rows.next();

++++++++Iterator<Cell>+cells+=+row.cellIterator();
++++++++while+(cells.hasNext())+{
++++++++++++HSSFCell+cell+=+(HSSFCell)+cells.next();
++++++++++++String+value+=+"";

++++++++++++switch+(cell.getCellType())+{
++++++++++++++++case+HSSFCell.CELL_TYPE_NUMERIC:
++++++++++++++++++++value+=+BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
++++++++++++++++++++break;

++++++++++++++++case+HSSFCell.CELL_TYPE_STRING:
++++++++++++++++++++value+=+cell.getStringCellValue();
++++++++++++++++++++break;

++++++++++++++++case+HSSFCell.CELL_TYPE_BLANK:
++++++++++++++++++++value+=+"";
++++++++++++++++++++break;

++++++++++++++++case+HSSFCell.CELL_TYPE_FORMULA:
++++++++++++++++++++value+=+cell.getCellFormula();
++++++++++++++++++++break;

++++++++++++++++default:
++++++++++++++++++++break;
++++++++++++}

++++++++++++values[rowPos][cell.getColumnIndex()]+=+value;
++++++++}

++++++++rowPos%2B%2B;
++++}
}|code-block|syntax|javascript|1559307|entityMap^0|0|0|C|P|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@$F|V|G|W|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|X|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|Y|8|@]|9|@]|A|$O|P]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

The reason is quite simple: Excel files can contain as many rows and as many columns as possibles, so returning all available blank rows and columns will render the cells huge and memory intensive.

Assuming you have a 10x10 sheet, in Excel, it's not "exactly" 10x10 since you can add 11x10 very easily with blank cell, so should POI return the 11th column?

One way to do what you're requesting is to use <code>HSSFCell.getColumnIndex()</code>.

Example:

<pre><code>//Assuming your have a 2 dimensional array.
String[][] values = ......;// It is assigned

POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(fileName));
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);

//Going through every worksheet.
for (int sheetPos = 0; sheetPos &lt; workbook.getNumberOfSheets(); sheetPos++) {
 HSSFSheet sheet = workbook.getSheetAt(sheetPos);

 int rowPos = 0;
 Iterator&lt;Row&gt; rows = sheet.rowIterator();
 while (rows.hasNext()) {
 HSSFRow row = (HSSFRow) rows.next();

 Iterator&lt;Cell&gt; cells = row.cellIterator();
 while (cells.hasNext()) {
 HSSFCell cell = (HSSFCell) cells.next();
 String value = "";

 switch (cell.getCellType()) {
 case HSSFCell.CELL_TYPE_NUMERIC:
 value = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
 break;

 case HSSFCell.CELL_TYPE_STRING:
 value = cell.getStringCellValue();
 break;

 case HSSFCell.CELL_TYPE_BLANK:
 value = "";
 break;

 case HSSFCell.CELL_TYPE_FORMULA:
 value = cell.getCellFormula();
 break;

 default:
 break;
 }

 values[rowPos][cell.getColumnIndex()] = value;
 }

 rowPos++;
 }
}
</code></pre>

blocks|key|1559309|text|这对我很有效..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1559310|int+rowNumber;
int+previousCell;
int+currentCell;
int+currentRowNumber;
HSSFCell+cell;

while+(rows.hasNext())+{
++++previousCell+=+-1;
++++currentCell+=+0;
++++while+(cellIterator.hasNext())+{
++++++++cell+=+(HSSFCell)+cellIterator.next();
++++++++currentCell+=+cell.getColumnIndex();
++++++++if+(previousCell+==+currentCell-1)++{
++++++++++++//...
++++++++}
++++++++else+{
++++++++++++System.out.println("Blank+cell+found");
++++++++}
++++++++previousCell+=+currentCell;
++++}
}|code-block|syntax|javascript|1559311|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This worked for me....

<pre><code>int rowNumber;
int previousCell;
int currentCell;
int currentRowNumber;
HSSFCell cell;

while (rows.hasNext()) {
 previousCell = -1;
 currentCell = 0;
 while (cellIterator.hasNext()) {
 cell = (HSSFCell) cellIterator.next();
 currentCell = cell.getColumnIndex();
 if (previousCell == currentCell-1) {
 //...
 }
 else {
 System.out.println("Blank cell found");
 }
 previousCell = currentCell;
 }
}
</code></pre>

blocks|key|3993320|text|下面是对我有效的方法。"row.CREATE_NULL_AS_BLANK“似乎无效，但这可能是缺乏NPOI知识。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3993321|HSSFCell+dataCell=+(HSSFCell)row.GetCell(column,+NPOI.SS.UserModel.MissingCellPolicy.CREATE_NULL_AS_BLANK);|code-block|syntax|javascript|3993322|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Below is what worked for me. The "row.CREATE_NULL_AS_BLANK" did not appear to be valid but that could be lack of NPOI knowledge.

<pre><code>HSSFCell dataCell= (HSSFCell)row.GetCell(column, NPOI.SS.UserModel.MissingCellPolicy.CREATE_NULL_AS_BLANK);
</code></pre>

blocks|key|5411038|text|List+cellDataList+=+new+ArrayList();+

int+lineNumber+=+0;+++

while+(rowIterator.hasNext())+{
++++HSSFRow+hssfRow+=+(HSSFRow)+rowIterator.next();
++++//System.out.println("Befor+If");
++++lineNumber%2B%2B;
++++if(lineNumber==1){continue;}
++++//System.out.println("Out+side+if+");

++++Iterator<Cell>+iterator+=+hssfRow.cellIterator();
++++List<Cell>+cellTempList+=+new+ArrayList();
++++int+current+=+0,+next+=+1;
++++while+(iterator.hasNext())+{
++++++Cell+hssfCell+=+iterator.next();
++++++current+=+hssfCell.getColumnIndex();

++++++if(current<next){
++++++++++System.out.println("Condition+Satisfied");
++++++}
++++++else{
++++++++++int+loop+=+current-next;
++++++++++System.out.println("inside+else+Loop+value+:+"%2B(loop));
++++++++++for(int+k=0;k<loop%2B1;k%2B%2B){
+++++++++++++System.out.println("Adding+nulls");
+++++++++++++cellTempList.add(null);
+++++++++++++next+=+next+%2B+1;
++++++++++}
++++++}

++++++cellTempList.add(hssfCell);

++++++next+=+next+%2B+1;
++++++System.out.println("At+End++next+value+is+:+"%2Bnext);
++}
++cellDataList.add(cellTempList);
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|5411039|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>List cellDataList = new ArrayList(); 

int lineNumber = 0; 

while (rowIterator.hasNext()) {
 HSSFRow hssfRow = (HSSFRow) rowIterator.next();
 //System.out.println("Befor If");
 lineNumber++;
 if(lineNumber==1){continue;}
 //System.out.println("Out side if ");

 Iterator&lt;Cell&gt; iterator = hssfRow.cellIterator();
 List&lt;Cell&gt; cellTempList = new ArrayList();
 int current = 0, next = 1;
 while (iterator.hasNext()) {
 Cell hssfCell = iterator.next();
 current = hssfCell.getColumnIndex();

 if(current&lt;next){
 System.out.println("Condition Satisfied");
 }
 else{
 int loop = current-next;
 System.out.println("inside else Loop value : "+(loop));
 for(int k=0;k&lt;loop+1;k++){
 System.out.println("Adding nulls");
 cellTempList.add(null);
 next = next + 1;
 }
 }

 cellTempList.add(hssfCell);

 next = next + 1;
 System.out.println("At End next value is : "+next);
 }
 cellDataList.add(cellTempList);
}
</code></pre>

blocks|key|1883241|text|public+String[]+rowToString(Row+row)
{
++++Iterator<Cell>+cells+=+row.cellIterator()+;
++++String[]+data+=+new+String[row.getLastCellNum()]+;

++++int+previousCell+=+0+;

++++Cell+cell+=+cells.next()+;
++++int+currentCell+=+cell.getColumnIndex();

++++while+(true)
++++{
++++++++if+(previousCell+==+currentCell)+{
++++++++++++switch+(cell.getCellType())+{
++++++++++++++++case+Cell.CELL_TYPE_NUMERIC:
++++++++++++++++++++data[previousCell]+=+cell.getNumericCellValue()%2B""+;
++++++++++++++++++++break;
++++++++++++++++case+Cell.CELL_TYPE_STRING:
++++++++++++++++++++data[previousCell]+=+cell.getStringCellValue()+;
++++++++++++++++++++break;
++++++++++++++++++++/*+//+there+could+be+other+cases+here.
++++++++++++++++++++case+Cell.CELL_TYPE_FORMULA:
++++++++++++++++++++++++data[previousCell]+=eval.evaluateFormulaCell(cell);
++++++++++++++++++++++++break;
++++++++++++++++++++case+Cell.CELL_TYPE_BOOLEAN:
++++++++++++++++++++++++data[previousCell]+=+cell.getBooleanCellValue();
++++++++++++++++++++++++break;
++++++++++++++++++++case+Cell.CELL_TYPE_BLANK:
++++++++++++++++++++++++data[previousCell]+=+"";
++++++++++++++++++++++++break;
++++++++++++++++++++case+Cell.CELL_TYPE_ERROR:
++++++++++++++++++++++++data[previousCell]+=+"ERROR";
++++++++++++++++++++++++break;
++++++++++++++++++++*/
++++++++++++}
++++++++++++if(cells.hasNext()){
++++++++++++++++cell+=+cells.next()+;
++++++++++++++++currentCell+=+cell.getColumnIndex();
++++++++++++}+else+{
++++++++++++++++break+;
++++++++++++}

++++++++}+else+{
++++++++++++data[previousCell]+=+"";
++++++++}
++++++++previousCell%2B%2B+;

++++}

++++return+data+;

}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1883242|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>public String[] rowToString(Row row)
{
 Iterator&lt;Cell&gt; cells = row.cellIterator() ;
 String[] data = new String[row.getLastCellNum()] ;

 int previousCell = 0 ;

 Cell cell = cells.next() ;
 int currentCell = cell.getColumnIndex();

 while (true)
 {
 if (previousCell == currentCell) {
 switch (cell.getCellType()) {
 case Cell.CELL_TYPE_NUMERIC:
 data[previousCell] = cell.getNumericCellValue()+"" ;
 break;
 case Cell.CELL_TYPE_STRING:
 data[previousCell] = cell.getStringCellValue() ;
 break;
 /* // there could be other cases here.
 case Cell.CELL_TYPE_FORMULA:
 data[previousCell] =eval.evaluateFormulaCell(cell);
 break;
 case Cell.CELL_TYPE_BOOLEAN:
 data[previousCell] = cell.getBooleanCellValue();
 break;
 case Cell.CELL_TYPE_BLANK:
 data[previousCell] = "";
 break;
 case Cell.CELL_TYPE_ERROR:
 data[previousCell] = "ERROR";
 break;
 */
 }
 if(cells.hasNext()){
 cell = cells.next() ;
 currentCell = cell.getColumnIndex();
 } else {
 break ;
 }

 } else {
 data[previousCell] = "";
 }
 previousCell++ ;

 }

 return data ;

}
</code></pre>

blocks|key|3993338|text|for+(Row+row:+sheet){
//+This+will+return+null+if+cell+is+empty+/+blank
Cell+cell+=+row.getCell(columnNumber);
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3993339|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>for (Row row: sheet){
// This will return null if cell is empty / blank
Cell cell = row.getCell(columnNumber);
}
</code></pre>

I have a huge excel file with tons of columns which looks like this :-

<pre><code>Column1 Column2 Column3 Column4 Column5
abc def ghi
 mno pqr
......
</code></pre>

This is the code that I wrote to print these values:

<pre><code>try {
 FileInputStream inputStr = new FileInputStream(fileName);
 XSSFWorkbook xssfWork = new XSSFWorkbook(inputStr) ;
 XSSFSheet sheet1 = xssfWork.getSheetAt(0);
 Iterator rowItr = sheet1.rowIterator();

 while ( rowItr.hasNext() ) {
 XSSFRow row = (XSSFRow) rowItr.next();
 System.out.println("ROW:--&gt;");
 Iterator cellItr = row.cellIterator();

 while ( cellItr.hasNext() ) {
 XSSFCell cell = (XSSFCell) cellItr.next();
 System.out.println("CELL:--&gt;"+cell.toString());
 }
 }
} catch (Exception e) {
 e.printStackTrace();
}
</code></pre>

The output generated by this code is :-

<pre><code>ROW:--&gt;
CELL:--&gt;Column1
CELL:--&gt;Column2
CELL:--&gt;Column3
CELL:--&gt;Column4
CELL:--&gt;Column5
ROW:--&gt;
CELL:--&gt;abc
CELL:--&gt;def
CELL:--&gt;ghi
ROW:--&gt;
CELL:--&gt;mno
CELL:--&gt;pqr
</code></pre>

So, If we look at the output above we can note that the cells where I left blank values was not picked up by the POI library , is there a way in which I can get these values as null. or a way to recognize that the values presented skipped blank cells?

Thanks.

How to get an Excel Blank Cell Value in Apache POI?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个巨大的excel文件，里面有很多列，看起来像这样：Column1 Column2 Column3 Column4 Column5abc             def             ghi        mno             pqr......这是我用来打印这些值的代码：try {    F...

问如何在Apache POI中获取Excel空白单元格值？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Apache POI中获取Excel空白单元格值？EN