如何在Java中读取具有null值的Excel计算单元格?

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 NullPointerException problem 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?

我如何解决我的问题?

4 个解决方案

#1

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有两种形式，第一种是您正在使用的形式，第二种是类型行的附加参数。MissingCellPolicy，其中您可以传递一个值，该值将自动将空单元格转换为空格。

#2

I've finally got what I want. I thank mezmo for it. I want to share the exact code snippet to be changed. Just replace the line having :

我终于得到了我想要的。我为此感谢梅兹莫。我希望共享要更改的确切代码片段。只需更换一行:

HSSFCell cell = row.getCell(c);

with

与

HSSFCell cell=row.getCell(c, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );

#3

You need to check if cell!=null, because if a cell doesn't exist in a row, row.getCell(c) returns null

你需要检查细胞是否!=null，因为如果行中不存在单元格，那么row. getcell (c)返回null

#4

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); 
                         }

#1

#2