Skipping Blank Excel Cells in Apache POI

问题:

I'm new to the Apache POI, but what I want to do is read though an Excel file (.xls) and put that into a ArrayList to store so that I can later manipulate. I can get the whole sheet, but my problem is just that: I get the whole dang sheet (~54183 rows).

I want to skip over the cells that are blank, which is of type 3. For some reason, when I system.out.print the ArrayList, it has all the blank cells in there.

Is there a way to skip over those and not add them to the ArrayList I'm trying to create?

I have the following bit of code:

public ArrayList readExcelFile(String filePath) throws IOException {
    ArrayList cellVectorHolder = new ArrayList();
    try {
        FileInputStream inputFile = new FileInputStream(filePath);
        POIFSFileSystem myFileSystem = new POIFSFileSystem(inputFile);
        HSSFWorkbook wkbk = new HSSFWorkbook(myFileSystem);
        wb = wkbk;
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            HSSFSheet wkSheet = wkbk.getSheetAt(i);
            for (Row row : wkSheet) {
                ArrayList cellVector = new ArrayList();
                for (Cell cell : row) {
                    if(cell.getCellType() != 3){
                        cellVector.add(cell);
                    }
                }
                cellVectorHolder.add(cellVector);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return cellVectorHolder;
}

Don't mind the ArrayList names...I was using Vectors until I finally discovered they were depreciated since 1.2 or something like that.

回答1:

Before adding the cell to the List, you can check the cell value. Something like:

if (cell.getStringCellValue() != null &&  cell.getStringCellValue().length() != 0) {   
    cellVector.add(cell); 
}

You only add the cell if there is some content.

回答2:

I think you should iterate over the rows and cells, that way POI won't even give you the empty cells.

Checkout the HSSFSheet.iterator() which iterates over all the Rows in a Sheet. And then Row.cellIterator() which iterates over the Cells in that row.

And you are bascially done.

If you are intent on keeping your old result variables your two inner loops becomes:

for (Iterator<Row> rit = wkSheet.iterator();rit.hasNext();) {
    ArrayList cellVector = new ArrayList();
    for(Iterator<Cell> cit = rit.next().cellIterator(); cit.hasNext(); ) { 
            cellVector.add(cit.next());
    }
    cellVectorHolder.add(cellVector);
}

回答3:

Try this

List cellDataList = new ArrayList
HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
                            HSSFSheet hssfSheet = workBook.getSheetAt(0);
                            Iterator rowIterator = hssfSheet.rowIterator();

                            int lineNumber = 0;
                            while (rowIterator.hasNext())
                            {
                                HSSFRow hssfRow = (HSSFRow) rowIterator.next();

                                lineNumber++;
                                if(lineNumber==1){continue;}



                                Iterator iterator = hssfRow.cellIterator();
                                List cellTempList = new ArrayList();
                                int current = 0, next = 1;
                                while (iterator.hasNext())
                                {
                                    HSSFCell hssfCell = (HSSFCell) iterator.next();

                                    current = hssfCell.getColumnIndex();


                                    if(current<next){                                   


                                    }
                                    else{

                                        int loop = current-next;

                                        for(int k=0;k<loop+1;k++){


                                            cellTempList.add(null);
                                            next = next + 1;
                                        }
                                    }

                                    cellTempList.add(hssfCell);



                                    next = next + 1;



                                }
                                cellDataList.add(cellTempList);
                            }