Geek asks and answers

Skipping blank Excel cells in Apache POI

I am new to Apache POI, but what I want to do is read through an Excel file (.xls) and put it in an ArrayList for storage so that I can later manipulate. I can get the whole sheet, but my problem is that: I get the whole dang sheet (~ 54183 lines).

I want to skip cells that are empty, which are of type 3. For some reason, when I have a system.out.print ArrayList, it has all the empty cells.

Is there a way to skip them 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;
}

Ignore the names of ArrayList ... I used vectors until I found that they were discounted with 1.2 or something like that.

+4
source share
4 answers

, . - :

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

, .

+4

, , POI .

HSSFSheet.iterator(), . Row.cellIterator(), .

.

, :

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

try it

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);
                            }
0
source 
Try the following code with excel as 
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class excelDataReader {
public static void main(String[] args) {
    List<Info> infoList = extractInfo();
    for (Info info : infoList) {
        System.out.println(info);
    }
}
public static List<Info> extractInfo() {
    List<Info> infoList = new ArrayList<Info>();
    Workbook wb = null;
    try {
        wb = new XSSFWorkbook(new FileInputStream(new         
 File("C:\\Users\\snaik\\Downloads\\contacts.xlsx")));
        Sheet sheet = wb.getSheetAt(0);
        boolean skipHeader = true;
        for (Row row : sheet) {
            if (skipHeader) {
                skipHeader = false;
                continue;
            }
            List<Cell> cells = new ArrayList<Cell>();
            int lastColumn = Math.max(row.getLastCellNum(), 5);// because my
                                                                // excel
                                                                // sheet has
                                                                // max 5
                                                                // columns,
                                                                // in case
                                                                // last
                                                                // column is
                                                                // empty
                                                                // then
                                                                // row.getLastCellNum()
                                                                // will
                                                                // return 4
            for (int cn = 0; cn < lastColumn; cn++) {
                Cell c = row.getCell(cn, Row.RETURN_BLANK_AS_NULL);
                cells.add(c);
            }
            Info info = extractInfoFromCell(cells);
            infoList.add(info);
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (wb != null) {
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    return infoList;
}
private static Info extractInfoFromCell(List<Cell> cells) {
    Info info = new Info();
    Cell nameCell = cells.get(0);
    if (nameCell != null) {
        nameCell.setCellType(Cell.CELL_TYPE_STRING);
        info.setName(nameCell.getStringCellValue());
    }
    Cell mobileCell = cells.get(1);
    if (mobileCell != null) {
        mobileCell.setCellType(Cell.CELL_TYPE_STRING);
        info.setMobile(mobileCell.getStringCellValue());
    }
    Cell phoneCell = cells.get(2);
    if (phoneCell != null) {
        phoneCell.setCellType(Cell.CELL_TYPE_STRING);
        info.setPhone(phoneCell.getStringCellValue());
    }
    Cell permAddressCell = cells.get(3);
    if (permAddressCell != null) {
        permAddressCell.setCellType(Cell.CELL_TYPE_STRING);
        info.setPermAddress(permAddressCell.getStringCellValue());
    }
    Cell commAddressCell = cells.get(4);
    if (commAddressCell != null) {
        commAddressCell.setCellType(Cell.CELL_TYPE_STRING);
        info.setCommAddress(commAddressCell.getStringCellValue());
    }
    return info;
}

}

0
source

More articles:


All Articles