userimage

Leave some cells with blank or space using Apache POI

Here is my sample requirement what I want to do,

Column1 Column2 Column3 Column4 Column5
abc                            def                             ghi
                    mno                           pqr
......

userimage

Assuming you have a 10x10 sheet, in Excel, it's not "exactly" 10x10 since you can add 11x10 very easily with blank cell, so should POI return the 11th column?

One way to do what you're requesting is to use HSSFCell.getColumnIndex().


/Assuming your have a 2 dimensional array.
String[][] values = ......;// It is assigned

POIFSFileSystem fileSystem = new POIFSFileSystem(new FileInputStream(fileName));
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);

//Going through every worksheet.
for (int sheetPos = 0; sheetPos < workbook.getNumberOfSheets(); sheetPos++) {
    HSSFSheet sheet = workbook.getSheetAt(sheetPos);

    int rowPos = 0;
    Iterator<Row> rows = sheet.rowIterator();
    while (rows.hasNext()) {
        HSSFRow row = (HSSFRow) rows.next();

        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            HSSFCell cell = (HSSFCell) cells.next();
            String value = "";

            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
                    break;

                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;

                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;

                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getCellFormula();
                    break;


                default:
                    break;
            }
            values[rowPos][cell.getColumnIndex()] = value;
        }
        rowPos++;
    }
}

Answer is