java 处理 apache poi 中的空列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13171870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
handling empty columns in apache poi
提问by Vicky
I have a program reading excel sheet from a java program.
我有一个从 java 程序读取 excel 表的程序。
I am iterating over cells as below:
我正在迭代单元格，如下所示：
Iterator cells = row.cellIterator();
String temp;
StringBuilder sb;
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
while (cells.hasNext()) {
Cell cell = (Cell) cells.next();
temp = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
temp = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
temp = sdf.format(cell.getDateCellValue());
} else {
temp = df.format(new BigDecimal(cell.getNumericCellValue()));
}
break;
default:
}
if (temp == null || temp.equalsIgnoreCase("null")) {
sb.append("").append(";");
} else {
sb.append(temp).append(";");
}
}
As seen, I am trying to create a string builder containing values from excel row in semicolon separated way.
正如所见，我正在尝试创建一个字符串构建器，其中包含以分号分隔的方式来自 excel 行的值。
Issue is, if a column value is empty, I want it as an empty value in the string builder with two consecutive semicolons.
问题是，如果列值为空，我希望它作为字符串构建器中的空值，并带有两个连续的分号。
However, the call
然而，调用
Cell cell = (Cell) cells.next();
Cell cell = (Cell) cells.next();
simply ignores the empty cells and jumps over to next non empty cell.
简单地忽略空单元格并跳转到下一个非空单元格。
So the line
所以线
if (temp == null || temp.equalsIgnoreCase("null"))
if (temp == null || temp.equalsIgnoreCase("null"))
is never met.
从来没有遇到过。
How to get a handle on empty column values as well in the iterator ?
如何在迭代器中处理空列值？
回答by Gagravarr
This is virtually a duplicate of this question, and so my answer to that questionbasically applies exactly to you too.
这实际上是这个问题的重复，所以我对那个问题的回答基本上也完全适用于你。
The Cell Iterator only iterates over cells that are defined in the file. If the cell has never been used in Excel, it probably won't appear in the file (Excel isn't always consistent...), so POI won't see it
Cell Iterator 仅迭代文件中定义的单元格。如果该单元格从未在 Excel 中使用过，它可能不会出现在文件中（Excel 并不总是一致的...），因此 POI 将看不到它
If you want to make sure you hit every cell, you should lookup by index instead, and either check for null cells (indicating the cell has never existed in the file), or set a MissingCellPolicyto control how you want null and blank cells to be treated
如果要确保命中每个单元格，则应改为按索引查找，并检查空单元格（表示该单元格从未存在于文件中），或设置 aMissingCellPolicy来控制您希望空单元格和空白单元格的显示方式治疗
So, if you really do want to get every cell, do something like:
所以，如果你真的想得到每个单元格，请执行以下操作：
Row r = sheet.getRow(myRowNum);
int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
for (int cn=0; cn<lastColumn; cn++) {
Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
if (c == null) {
// The spreadsheet is empty in this cell
} else {
// Do something useful with the cell's contents
}
}
回答by venkata
Answer posted by @Gagravarr works perfectly for me but MissingCellPolicy is an enum now, so while getting the cell value instead of using
@Gagravarr 发布的答案非常适合我，但 MissingCellPolicy 现在是一个枚举，因此在获取单元格值而不是使用时
Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
I have used
我用过了
Cell c =r.getCell(cn,Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
回答by Bhavik Shah
You can do this
你可以这样做
int previous=0;
while(cell.hasNext())
{
Cell cell = (Cell) cells.next();
int current=cell.getColumnIndex();
int numberofsemicolons=previous-current;
for(numberofsemicolons)
{
sb.append("").append(";");
}
previous=current;
}
or you can do
或者你可以做
int numberofcells=row.getFirstCellNum()-row.getLastCellNum();
for(i=0;i<=numberofcells;i++)
{
Cell cell = (Cell) cells.next();
int current=cell.getColumnIndex();
while(i<current)
{
sb.append("").append(";");
i++
}
}
