如何在Java中读取具有空值的Excel单元格...？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3132732/
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
How to read Excel cell having null values too in Java...?
提问by Venkat
I'm using Apache POI 3.6. I've a column which is blank. I would like to be able to read it and then move to the next column. Even if I could resolve the NullPointerExceptionproblem I could not get to the next cell.
我正在使用 Apache POI 3.6。我有一列是空白的。我希望能够阅读它，然后转到下一列。即使我能解决 NullPointerException问题，我也无法进入下一个单元格。
Here's my code snippet :
这是我的代码片段：
HSSFCell cell = row.getCell(c);
String value = null;
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
value = "FORMULA value=" + cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value = "NUMERIC value=" + cell.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING:
value = "STRING value=" + cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
value="";
break;
case HSSFCell.CELL_TYPE_ERROR:
value="error";
break;
default:
break;
}
System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
How can I resolve my problem?
我该如何解决我的问题？
采纳答案by mezmo
Well, you could check for null before your switch statement, or you could change which call to row.getCell you make. Checking the Javadoc for POI there are 2 forms, the first is what you are using, the second has an additional parameter, of the type Row.MissingCellPolicy, where you can pass a value that would automagically transform null cells into blanks.
好吧，您可以在 switch 语句之前检查 null，或者您可以更改对 row.getCell 的调用。检查 POI 的 Javadoc 有两种形式，第一种是您正在使用的形式，第二种有一个附加参数，类型为 Row.MissingCellPolicy，您可以在其中传递一个值，该值会自动将空单元格转换为空白。
回答by Erich Kitzmueller
You need to check if cell!=null, because if a cell doesn't exist in a row, row.getCell(c)returns null
您需要检查 if cell!=null，因为如果一行中不存在单元格，则row.getCell(c)返回null
回答by Venkat
I've finally got what I want. I thank mezmofor it. I want to share the exact code snippet to be changed. Just replace the line having :
我终于得到了我想要的东西。我为此感谢mezmo。我想分享要更改的确切代码片段。只需替换具有以下内容的行：
HSSFCell cell = row.getCell(c);
with
和
HSSFCell cell=row.getCell(c, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );
回答by Praveen
Try this
尝试这个
List cellDataList = new ArrayList();
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
Iterator rows = sheet.rowIterator();
int number=sheet.getLastRowNum();
int lineNumber = 0;
while (rows.hasNext())
{
XSSFRow row = ((XSSFRow) rows.next());
lineNumber++;
if(lineNumber==1){continue;}
Iterator cells = row.cellIterator();
List cellTempList = new ArrayList();
int current = 0, next =1;
while(cells.hasNext())
{
XSSFCell cell = (XSSFCell) cells.next();
current = cell.getColumnIndex();
if(current<next){
}
else{
int loop = current-next;
for(int k=0;k<loop+1;k++){
cellTempList.add(null);
next = next + 1;
}
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
cellTempList.add(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println(cell.getNumericCellValue());
cellTempList.add(String.valueOf(cell.getNumericCellValue()));
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
cellTempList.add(cell.getCellFormula());
break;
default:
System.out.println("Inside default");
}
next = next + 1;
}
cellDataList.add(cellTempList);
}
回答by BeniHassenWolf
Thank you Venkat, it work for me also except
for those who are using Apache 4.0.0 and up they need to do it as :
谢谢 Venkat，它对我也有用，除了
那些使用 Apache 4.0.0 及更高版本的人，他们需要这样做：
HSSFCell cell=row.getCell(c, org.apache.poi.ss.usermodel.Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
