Example usage for org.apache.poi.ss.usermodel Cell getDateCellValue

List of usage examples for org.apache.poi.ss.usermodel Cell getDateCellValue

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getDateCellValue

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Cell getDateCellValue.

Prototype

Date getDateCellValue();

Source Link

Document

Get the value of the cell as a date.

Usage

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ??? ??????<br>//  w  w  w  .  j a  va2  s  .  com
 * <br>
 * ?[CELL_TYPE_ERROR]??<br>
 * xls? ?HSSFErrorConstants?<br>
 * xlsx? Excel??ex.#DIV/0!?#N/A?#REF!
 * 
 * @param cell 
 * @return 
 */
public static Object getCellValue(Cell cell) {
    Object value = null;

    if (cell != null) {
        switch (cell.getCellTypeEnum()) {
        case BLANK:
            break;
        case BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case ERROR:
            value = cell.getErrorCellValue();
            break;
        case NUMERIC:
            // ??
            if (isCellDateFormatted(cell)) {
                value = cell.getDateCellValue();
            } else {
                value = cell.getNumericCellValue();
            }
            break;
        case STRING:
            value = cell.getStringCellValue();
            break;
        case FORMULA:
            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper()
                    .createFormulaEvaluator();
            // ?
            CellValue cellValue = evaluator.evaluate(cell);
            CellType cellType = cellValue.getCellTypeEnum();
            // ????
            switch (cellType) {
            case BLANK:
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case ERROR:
                if (cell instanceof XSSFCell) {
                    // XSSF??????
                    XSSFCell xssfCell = (XSSFCell) cell;
                    CTCell ctCell = xssfCell.getCTCell();
                    value = ctCell.getV();
                } else if (cell instanceof HSSFCell) {
                    // HSSF??????
                    value = cell.getErrorCellValue();
                }
                break;
            case NUMERIC:
                // ??
                if (isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                } else {
                    value = cell.getNumericCellValue();
                }
                break;
            case STRING:
                value = cell.getStringCellValue();
                break;
            default:
                break;
            }
        default:
            break;
        }
    }
    return value;
}

From source file:org.bbreak.excella.core.util.PoiUtil.java

License:Open Source License

/**
 * ?????????????/* w  w  w.  java2s . c o m*/
 * 
 * @param cell ?
 * @param propertyClass ??Java?
 * @return ???
 */
public static Object getCellValue(Cell cell, Class<?> propertyClass) {
    if (cell.getCellTypeEnum() == CellType.BLANK) {
        // ?
        return null;
    } else if (cell.getCellTypeEnum() == CellType.STRING && StringUtil.isEmpty(cell.getStringCellValue())) {
        // ??????null?
        return null;
    }

    if (Object.class.isAssignableFrom(propertyClass)) {
        if (Number.class.isAssignableFrom(propertyClass)) {
            Number number = (Number) cell.getNumericCellValue();
            // 

            if (propertyClass.equals(Short.class)) {
                return number.shortValue();
            } else if (propertyClass.equals(Integer.class)) {
                return number.intValue();
            } else if (propertyClass.equals(Long.class)) {
                return number.longValue();
            } else if (propertyClass.equals(Float.class)) {
                return number.floatValue();
            } else if (propertyClass.equals(Double.class)) {
                return number.doubleValue();
            } else if (propertyClass.equals(BigDecimal.class)) {
                return new BigDecimal(number.doubleValue());
            } else if (propertyClass.equals(Byte.class)) {
                return new Byte(number.byteValue());
            } else {
                return number;
            }
        } else if (Date.class.isAssignableFrom(propertyClass)) {
            // 
            return cell.getDateCellValue();
        } else if (String.class.isAssignableFrom(propertyClass)) {
            // 
            Object value = getCellValue(cell);
            if (value == null) {
                return null;
            }
            String strValue = null;
            if (value instanceof String) {
                strValue = (String) value;
            }
            if (value instanceof Double) {
                // Double -> String???????
                strValue = String.valueOf(((Double) value).intValue());
            } else {
                strValue = value.toString();
            }
            return strValue;
        } else if (Boolean.class.isAssignableFrom(propertyClass)
                || boolean.class.isAssignableFrom(propertyClass)) {
            // Boolean
            Object value = getCellValue(cell);
            if (value == null) {
                return null;
            }
            if (value instanceof String) {
                return Boolean.valueOf((String) value);
            }
            return value;
        }
    } else {
        // 
        Object value = getCellValue(cell);
        if (value == null) {
            return null;
        }
        if (value instanceof Double) {
            if (byte.class.isAssignableFrom(propertyClass)) {
                int intValue = Double.valueOf((Double) value).intValue();
                value = Byte.valueOf(String.valueOf(intValue));
            } else if (short.class.isAssignableFrom(propertyClass)) {
                value = Double.valueOf((Double) value).shortValue();
            } else if (int.class.isAssignableFrom(propertyClass)) {
                value = Double.valueOf((Double) value).intValue();
            } else if (long.class.isAssignableFrom(propertyClass)) {
                value = Double.valueOf((Double) value).longValue();
            } else if (float.class.isAssignableFrom(propertyClass)) {
                value = Double.valueOf((Double) value).floatValue();
            } else if (double.class.isAssignableFrom(propertyClass)) {
                value = Double.valueOf((Double) value).doubleValue();
            }
        }
        return value;
    }
    return null;
}

From source file:org.bbreak.excella.core.util.PoiUtilTest.java

License:Open Source License

@Test
public void testPoiUtil3() throws IOException, ParseException {

    Workbook workbook = getWorkbook();//from  w w w. ja  v  a2s.c o  m
    Sheet sheet_1 = workbook.getSheetAt(0);
    Sheet sheet_2 = workbook.getSheetAt(1);
    Sheet sheet_3 = workbook.getSheetAt(2);
    Sheet sheet_4 = workbook.getSheetAt(3);
    Sheet sheet_5 = workbook.getSheetAt(4);
    Sheet sheet_6 = workbook.getSheetAt(5);
    Sheet sheet_7 = workbook.getSheetAt(6);

    // ===============================================
    // copyCell( Cell fromCell, Cell toCell)
    // ===============================================
    // No.1 ?
    Cell fromCellNumeric = sheet_1.getRow(0).getCell(0);
    Cell fromCellFormula = sheet_1.getRow(1).getCell(0);
    Cell fromCellString = sheet_1.getRow(2).getCell(0);
    Cell fromCellBoolean = sheet_1.getRow(3).getCell(0);
    Cell fromCellError = sheet_1.getRow(4).getCell(0);
    Cell fromCellDate = sheet_1.getRow(5).getCell(0);
    Cell fromCellBlank = sheet_1.getRow(6).getCell(0);

    Cell toCellNumeric = sheet_1.getRow(0).createCell(9);
    Cell toCellFormula = sheet_1.getRow(1).createCell(9);
    Cell toCellString = sheet_1.getRow(2).createCell(9);
    Cell toCellBoolean = sheet_1.getRow(3).createCell(9);
    Cell toCellError = sheet_1.getRow(4).createCell(9);
    Cell toCellDate = sheet_1.getRow(5).createCell(9);
    Cell toCellBlank = sheet_1.getRow(6).createCell(9);

    Cell fromCellNumericFrml = sheet_1.getRow(7).getCell(0);
    Cell fromCellStringFrml = sheet_1.getRow(8).getCell(0);
    Cell fromCellBooleanFrml = sheet_1.getRow(9).getCell(0);
    Cell fromCellErrorFrml = sheet_1.getRow(10).getCell(0);
    Cell fromCellDateFrml = sheet_1.getRow(11).getCell(0);
    Cell fromCellBlankFrml = sheet_1.getRow(12).getCell(0);

    Cell toCellNumericFrml = sheet_1.getRow(7).createCell(9);
    Cell toCellStringFrml = sheet_1.getRow(8).createCell(9);
    Cell toCellBooleanFrml = sheet_1.getRow(9).createCell(9);
    Cell toCellErrorFrml = sheet_1.getRow(10).createCell(9);
    Cell toCellDateFrml = sheet_1.getRow(11).createCell(9);
    Cell toCellBlankFrml = sheet_1.getRow(12).createCell(9);

    PoiUtil.copyCell(fromCellNumeric, toCellNumeric);
    PoiUtil.copyCell(fromCellFormula, toCellFormula);
    PoiUtil.copyCell(fromCellString, toCellString);
    PoiUtil.copyCell(fromCellBoolean, toCellBoolean);
    PoiUtil.copyCell(fromCellError, toCellError);
    PoiUtil.copyCell(fromCellDate, toCellDate);
    PoiUtil.copyCell(fromCellBlank, toCellBlank);

    PoiUtil.copyCell(fromCellNumericFrml, toCellNumericFrml);
    PoiUtil.copyCell(fromCellStringFrml, toCellStringFrml);
    PoiUtil.copyCell(fromCellBooleanFrml, toCellBooleanFrml);
    PoiUtil.copyCell(fromCellErrorFrml, toCellErrorFrml);
    PoiUtil.copyCell(fromCellDateFrml, toCellDateFrml);
    PoiUtil.copyCell(fromCellBlankFrml, toCellBlankFrml);

    try {
        // ?
        TestUtil.checkCell(fromCellNumeric, toCellNumeric);
        TestUtil.checkCell(fromCellFormula, toCellFormula);
        TestUtil.checkCell(fromCellString, toCellString);
        TestUtil.checkCell(fromCellBoolean, toCellBoolean);
        TestUtil.checkCell(fromCellError, toCellError);
        TestUtil.checkCell(fromCellDate, toCellDate);
        TestUtil.checkCell(fromCellBlank, toCellBlank);

        TestUtil.checkCell(fromCellNumericFrml, toCellNumericFrml);
        TestUtil.checkCell(fromCellStringFrml, toCellStringFrml);
        TestUtil.checkCell(fromCellBooleanFrml, toCellBooleanFrml);
        TestUtil.checkCell(fromCellErrorFrml, toCellErrorFrml);
        TestUtil.checkCell(fromCellDateFrml, toCellDateFrml);
        TestUtil.checkCell(fromCellBlankFrml, toCellBlankFrml);

    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.2 fromCell?null
    Cell toCell = sheet_1.getRow(0).createCell(10);
    PoiUtil.copyCell(null, toCell);

    // No.3 toCell?null
    try {
        PoiUtil.copyCell(fromCellNumeric, null);
        fail();
    } catch (NullPointerException ex) {
        // toCell?null????
    }

    // No.4 ?
    Cell toCellNumeric2 = sheet_2.getRow(0).createCell(0);
    PoiUtil.copyCell(fromCellNumeric, toCellNumeric2);

    // ===============================================
    // copyRange( Sheet fromSheet, CellRangeAddress rangeAddress, Sheet toSheet, int toRowNum, int toColumnNum, boolean clearFromRange)
    // ===============================================
    // No.5 ?
    PoiUtil.copyRange(sheet_1, new CellRangeAddress(0, 0, 0, 0), sheet_2, 0, 3, false);
    try {
        TestUtil.checkCell(sheet_1.getRow(0).getCell(0), sheet_2.getRow(0).getCell(3));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.6 
    PoiUtil.copyRange(sheet_1, new CellRangeAddress(1, 12, 0, 1), sheet_2, 9, 0, false);
    try {
        TestUtil.checkCell(sheet_1.getRow(1).getCell(0), sheet_2.getRow(9).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(2).getCell(0), sheet_2.getRow(10).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(3).getCell(0), sheet_2.getRow(11).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(4).getCell(0), sheet_2.getRow(12).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(5).getCell(0), sheet_2.getRow(13).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(6).getCell(0), sheet_2.getRow(14).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(7).getCell(0), sheet_2.getRow(15).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(8).getCell(0), sheet_2.getRow(16).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(9).getCell(0), sheet_2.getRow(17).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(10).getCell(0), sheet_2.getRow(18).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(11).getCell(0), sheet_2.getRow(19).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(12).getCell(0), sheet_2.getRow(20).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(1).getCell(1), sheet_2.getRow(9).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(2).getCell(1), sheet_2.getRow(10).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(3).getCell(1), sheet_2.getRow(11).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(4).getCell(1), sheet_2.getRow(12).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(5).getCell(1), sheet_2.getRow(13).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(6).getCell(1), sheet_2.getRow(14).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(7).getCell(1), sheet_2.getRow(15).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(8).getCell(1), sheet_2.getRow(16).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(9).getCell(1), sheet_2.getRow(17).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(10).getCell(1), sheet_2.getRow(18).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(11).getCell(1), sheet_2.getRow(19).getCell(1));
        TestUtil.checkCell(sheet_1.getRow(12).getCell(1), sheet_2.getRow(20).getCell(1));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.7 ?null
    PoiUtil.copyRange(null, new CellRangeAddress(0, 0, 0, 0), sheet_2, 0, 0, false);
    PoiUtil.copyRange(sheet_1, null, sheet_2, 0, 0, false);
    PoiUtil.copyRange(sheet_1, new CellRangeAddress(0, 0, 0, 0), null, 0, 0, false);

    // No.8 ??
    try {
        PoiUtil.copyRange(sheet_1, new CellRangeAddress(-1, 0, 0, 0), sheet_2, 0, 0, false);
    } catch (IllegalArgumentException ex) {
        // ???????
    }

    // No.9 ??
    PoiUtil.copyRange(sheet_1, new CellRangeAddress(23, 23, 0, 1), sheet_2, 22, 0, false);
    try {
        TestUtil.checkCell(sheet_1.getRow(23).getCell(0), sheet_2.getRow(22).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(23).getCell(1), sheet_2.getRow(22).getCell(1));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.10 ??
    PoiUtil.copyRange(sheet_1, new CellRangeAddress(25, 26, 0, 0), sheet_2, 24, 0, false);
    try {
        TestUtil.checkCell(sheet_1.getRow(25).getCell(0), sheet_2.getRow(24).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(26).getCell(0), sheet_2.getRow(25).getCell(0));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.11 null
    PoiUtil.copyRange(sheet_1, new CellRangeAddress(30, 30, 0, 1), sheet_2, 29, 0, false);
    try {
        TestUtil.checkCell(sheet_1.getRow(30).getCell(0), sheet_2.getRow(29).getCell(0));
        TestUtil.checkCell(sheet_1.getRow(30).getCell(1), sheet_2.getRow(29).getCell(1));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.12 null
    PoiUtil.copyRange(sheet_1, new CellRangeAddress(34, 34, 0, 3), sheet_2, 33, 0, false);
    assertNull(sheet_2.getRow(33));

    // No.13 ??
    Cell copyFrom1 = sheet_2.getRow(40).getCell(0);
    Cell copyFrom2 = sheet_2.getRow(40).getCell(1);
    Cell copyFrom3 = sheet_2.getRow(40).getCell(2);
    Cell copyFrom4 = sheet_2.getRow(41).getCell(0);
    Cell copyFrom5 = sheet_2.getRow(41).getCell(1);
    Cell copyFrom6 = sheet_2.getRow(41).getCell(2);

    PoiUtil.copyRange(sheet_2, new CellRangeAddress(40, 41, 0, 2), sheet_2, 41, 1, false);
    try {
        TestUtil.checkCell(copyFrom1, sheet_2.getRow(41).getCell(1));
        TestUtil.checkCell(copyFrom2, sheet_2.getRow(41).getCell(2));
        TestUtil.checkCell(copyFrom3, sheet_2.getRow(41).getCell(3));
        TestUtil.checkCell(copyFrom4, sheet_2.getRow(42).getCell(1));
        TestUtil.checkCell(copyFrom5, sheet_2.getRow(42).getCell(2));
        TestUtil.checkCell(copyFrom6, sheet_2.getRow(42).getCell(3));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.14 ???
    copyFrom1 = sheet_2.getRow(49).getCell(0);
    PoiUtil.copyRange(sheet_2, new CellRangeAddress(49, 49, 0, 0), sheet_2, 49, 2, true);
    assertNull(sheet_2.getRow(49).getCell(0));
    try {
        TestUtil.checkCell(copyFrom1, sheet_2.getRow(49).getCell(2));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // No.15 ??
    copyFrom1 = new CellClone(sheet_2.getRow(55).getCell(0));
    copyFrom2 = new CellClone(sheet_2.getRow(55).getCell(1));
    copyFrom3 = new CellClone(sheet_2.getRow(55).getCell(2));
    copyFrom4 = new CellClone(sheet_2.getRow(56).getCell(0));
    copyFrom5 = new CellClone(sheet_2.getRow(56).getCell(1));
    copyFrom6 = new CellClone(sheet_2.getRow(56).getCell(2));

    PoiUtil.copyRange(sheet_2, new CellRangeAddress(55, 56, 0, 2), sheet_2, 56, 1, true);
    assertNull(sheet_2.getRow(55).getCell(0));
    assertNull(sheet_2.getRow(55).getCell(1));
    assertNull(sheet_2.getRow(55).getCell(2));
    assertNull(sheet_2.getRow(56).getCell(0));
    try {
        TestUtil.checkCell(copyFrom1, sheet_2.getRow(56).getCell(1));
        TestUtil.checkCell(copyFrom2, sheet_2.getRow(56).getCell(2));
        TestUtil.checkCell(copyFrom3, sheet_2.getRow(56).getCell(3));
        TestUtil.checkCell(copyFrom4, sheet_2.getRow(57).getCell(1));
        TestUtil.checkCell(copyFrom5, sheet_2.getRow(57).getCell(2));
        TestUtil.checkCell(copyFrom6, sheet_2.getRow(57).getCell(3));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // ===============================================
    // insertRangeDown( Sheet sheet, CellRangeAddress rangeAddress)
    // ===============================================
    // No.16 insertRangeDown
    copyFrom1 = sheet_3.getRow(1).getCell(1);
    copyFrom2 = sheet_3.getRow(1).getCell(2);
    copyFrom3 = sheet_3.getRow(2).getCell(1);
    copyFrom4 = sheet_3.getRow(2).getCell(2);
    PoiUtil.insertRangeDown(sheet_3, new CellRangeAddress(1, 2, 1, 2));
    assertNull(sheet_3.getRow(1).getCell(1));
    assertNull(sheet_3.getRow(1).getCell(2));
    assertNull(sheet_3.getRow(2).getCell(1));
    assertNull(sheet_3.getRow(2).getCell(2));
    try {
        TestUtil.checkCell(copyFrom1, sheet_3.getRow(3).getCell(1));
        TestUtil.checkCell(copyFrom2, sheet_3.getRow(3).getCell(2));
        TestUtil.checkCell(copyFrom3, sheet_3.getRow(4).getCell(1));
        TestUtil.checkCell(copyFrom4, sheet_3.getRow(4).getCell(2));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // ===============================================
    // insertRangeRight( Sheet sheet, CellRangeAddress rangeAddress)
    // ===============================================
    // No.17 insertRangeRight
    copyFrom1 = sheet_3.getRow(6).getCell(5);
    copyFrom2 = sheet_3.getRow(6).getCell(6);
    copyFrom3 = sheet_3.getRow(7).getCell(5);
    copyFrom4 = sheet_3.getRow(7).getCell(6);
    PoiUtil.insertRangeRight(sheet_3, new CellRangeAddress(6, 7, 5, 6));
    assertNull(sheet_3.getRow(6).getCell(5));
    assertNull(sheet_3.getRow(6).getCell(6));
    assertNull(sheet_3.getRow(7).getCell(5));
    assertNull(sheet_3.getRow(7).getCell(6));
    try {
        TestUtil.checkCell(copyFrom1, sheet_3.getRow(6).getCell(7));
        TestUtil.checkCell(copyFrom2, sheet_3.getRow(6).getCell(8));
        TestUtil.checkCell(copyFrom3, sheet_3.getRow(7).getCell(7));
        TestUtil.checkCell(copyFrom4, sheet_3.getRow(7).getCell(8));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // ===============================================
    // deleteRangeUp( Sheet sheet, CellRangeAddress rangeAddress)
    // ===============================================
    // No.18 deleteRangeUp
    copyFrom1 = sheet_3.getRow(13).getCell(9);
    copyFrom2 = sheet_3.getRow(13).getCell(10);
    copyFrom3 = sheet_3.getRow(14).getCell(9);
    copyFrom4 = sheet_3.getRow(14).getCell(10);
    PoiUtil.deleteRangeUp(sheet_3, new CellRangeAddress(11, 12, 9, 10));
    assertNull(sheet_3.getRow(13).getCell(9));
    assertNull(sheet_3.getRow(13).getCell(10));
    assertNull(sheet_3.getRow(14).getCell(9));
    assertNull(sheet_3.getRow(14).getCell(10));
    try {
        TestUtil.checkCell(copyFrom1, sheet_3.getRow(11).getCell(9));
        TestUtil.checkCell(copyFrom2, sheet_3.getRow(11).getCell(10));
        TestUtil.checkCell(copyFrom3, sheet_3.getRow(12).getCell(9));
        TestUtil.checkCell(copyFrom4, sheet_3.getRow(12).getCell(10));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // ===============================================
    // deleteRangeLeft( Sheet sheet, CellRangeAddress rangeAddress)
    // ===============================================
    // No.19 deleteRangeLeft
    copyFrom1 = sheet_3.getRow(16).getCell(15);
    copyFrom2 = sheet_3.getRow(16).getCell(14);
    copyFrom3 = sheet_3.getRow(17).getCell(15);
    copyFrom4 = sheet_3.getRow(17).getCell(14);
    PoiUtil.deleteRangeLeft(sheet_3, new CellRangeAddress(16, 17, 13, 14));
    assertNull(sheet_3.getRow(16).getCell(15));
    assertNull(sheet_3.getRow(16).getCell(16));
    assertNull(sheet_3.getRow(17).getCell(15));
    assertNull(sheet_3.getRow(17).getCell(16));
    try {
        TestUtil.checkCell(copyFrom1, sheet_3.getRow(16).getCell(13));
        TestUtil.checkCell(copyFrom2, sheet_3.getRow(16).getCell(14));
        TestUtil.checkCell(copyFrom3, sheet_3.getRow(17).getCell(13));
        TestUtil.checkCell(copyFrom4, sheet_3.getRow(17).getCell(14));
    } catch (CheckException ex) {
        System.out.println(ex.getCheckMessagesToString());
        fail();
    }

    // ===============================================
    // clearRange( Sheet sheet, CellRangeAddress rangeAddress)
    // ===============================================
    // No.20 ????
    PoiUtil.clearRange(sheet_4, new CellRangeAddress(0, 2, 0, 0));
    assertNull(sheet_4.getRow(0).getCell(0));
    assertNull(sheet_4.getRow(1).getCell(0));
    assertNull(sheet_4.getRow(2).getCell(0));
    assertEquals("4", sheet_4.getRow(3).getCell(0).getStringCellValue());

    // No.21 ????
    PoiUtil.clearRange(sheet_4, new CellRangeAddress(4, 5, 0, 1));
    assertNull(sheet_4.getRow(4).getCell(0));
    assertNull(sheet_4.getRow(5).getCell(0));
    assertNull(sheet_4.getRow(4).getCell(1));
    assertNull(sheet_4.getRow(5).getCell(1));
    assertEquals("5C", sheet_4.getRow(4).getCell(2).getStringCellValue());
    assertEquals("6C", sheet_4.getRow(5).getCell(2).getStringCellValue());

    // No.22 ???
    PoiUtil.clearRange(sheet_4, new CellRangeAddress(8, 8, 0, 1));
    assertNull(null, sheet_4.getRow(8).getCell(0));

    // No.23 ???
    try {
        PoiUtil.clearRange(sheet_4, new CellRangeAddress(10, 10, 0, 0));
        fail();
    } catch (IllegalArgumentException ex) {
        // ????????????
    }
    // ????????
    assertEquals("11", sheet_4.getRow(10).getCell(0).getStringCellValue());
    assertNotNull(sheet_4.getRow(10).getCell(1).getStringCellValue());

    // No.24 ???
    try {
        PoiUtil.clearRange(sheet_4, new CellRangeAddress(12, 12, 0, 0));
        fail();
    } catch (IllegalArgumentException ex) {
        // ????????????
    }
    // ????????
    assertEquals("13", sheet_4.getRow(12).getCell(0).getStringCellValue());
    assertNotNull(sheet_4.getRow(13).getCell(0).getStringCellValue());

    // ===============================================
    // clearCell( Sheet sheet, CellRangeAddress rangeAddress)
    // ===============================================
    // No.25 clearCell
    PoiUtil.clearCell(sheet_4, new CellRangeAddress(15, 16, 0, 0));
    assertNull(sheet_4.getRow(15).getCell(0));
    assertNull(sheet_4.getRow(15).getCell(0));

    // ===============================================
    // setHyperlink( Cell cell, int type, String address)
    // ===============================================
    // No.26 setHyperlink
    Cell cellHyperlink = sheet_5.getRow(0).getCell(0);
    String address = "http://sourceforge.jp/projects/excella-core/";
    PoiUtil.setHyperlink(cellHyperlink, HyperlinkType.URL, address);
    Hyperlink hyperLink = cellHyperlink.getHyperlink();
    if (hyperLink instanceof HSSFHyperlink) {
        assertEquals(address, ((HSSFHyperlink) hyperLink).getTextMark());
    } else if (hyperLink instanceof XSSFHyperlink) {
        assertEquals(address, ((XSSFHyperlink) hyperLink).getAddress());
    }

    // ===============================================
    // setCellValue( Cell cell, Object value)
    // ===============================================
    // No.27 setCellValue
    Cell cellString = sheet_5.getRow(1).getCell(0);
    Cell cellNumber = sheet_5.getRow(1).getCell(1);
    Cell cellFloat = sheet_5.getRow(1).getCell(2);
    Cell cellDate = sheet_5.getRow(1).getCell(3);
    Cell cellBoolean = sheet_5.getRow(1).getCell(4);
    Cell cellNull = sheet_5.getRow(1).getCell(5);

    String stringValue = "aaa";
    Number numberValue = new Double(10);
    Float floatValue = new Float(10f);
    Date dateValue = new Date();
    Boolean booleanValue = Boolean.TRUE;

    PoiUtil.setCellValue(cellString, stringValue);
    PoiUtil.setCellValue(cellNumber, numberValue);
    PoiUtil.setCellValue(cellFloat, floatValue);
    PoiUtil.setCellValue(cellDate, dateValue);
    PoiUtil.setCellValue(cellBoolean, booleanValue);
    PoiUtil.setCellValue(cellNull, null);

    assertEquals(stringValue, cellString.getStringCellValue());
    assertEquals(numberValue, cellNumber.getNumericCellValue());
    assertEquals(new Double(String.valueOf(floatValue)), (Double) cellFloat.getNumericCellValue());
    assertEquals(dateValue, cellDate.getDateCellValue());
    assertEquals(booleanValue, cellBoolean.getBooleanCellValue());
    assertNull(PoiUtil.getCellValue(cellNull));

    // No.28 ?null
    try {
        PoiUtil.setCellValue(null, stringValue);
        fail();
    } catch (NullPointerException ex) {
        // ?null????
    }

    // ===============================================
    // getLastColNum( Sheet sheet)
    // ===============================================
    // No.29 
    int lastColNum1 = PoiUtil.getLastColNum(sheet_6);
    assertEquals(-1, lastColNum1);

    // No.30 ?
    int lastColNum2 = PoiUtil.getLastColNum(sheet_7);
    assertEquals(10, lastColNum2);
}

From source file:org.bdxjug.api.infrastructure.sheet.xlsx.XlsxSheet.java

License:Apache License

private static String getDateValue(Cell cell) {
    Date dateCellValue = cell.getDateCellValue();
    ZonedDateTime localDate = dateCellValue.toInstant().atZone(ZoneId.systemDefault());
    return DATE_TIME_FORMATTER.format(localDate);
}

From source file:org.celllife.idart.gui.patientAdmin.PatientAdmin.java

License:Open Source License

private void importPatientViralLoad(String fileName) {
    ArrayList<PatientViralLoadDataImport> patients = new ArrayList<PatientViralLoadDataImport>();
    int rowErrors = 0;
    Session sess = HibernateUtil.getNewSession();
    try {/*from  w  w w.ja v a2 s  .  com*/
        FileInputStream file = new FileInputStream(new File(fileName));

        //Create Workbook instance holding reference to .xlsx file
        Workbook workbook = WorkbookFactory.create(file);

        //Get first/desired sheet from the workbook
        Sheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        Integer patientId;
        Boolean highViralLoad;
        Date resultDate;
        Row row = rowIterator.next();

        while (rowIterator.hasNext()) {
            try {
                row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();

                Cell cell = cellIterator.next();

                patientId = new Integer((int) cell.getNumericCellValue());

                cell = cellIterator.next();
                cell = cellIterator.next();
                cell = cellIterator.next();
                cell = cellIterator.next();
                cell = cellIterator.next();

                highViralLoad = (new BooleanConverter()).convert(cell.getStringCellValue());
                //cell.getStringCellValue().equalsIgnoreCase("Sim") ? true : false; 

                cell = cellIterator.next();

                resultDate = cell.getDateCellValue();
                //(new DateConverter()).convert(cell.getNumericCellValue());
                //DateUtil.getJavaDate(cell.getNumericCellValue());
                patients.add(new PatientViralLoadDataImport(patientId, highViralLoad, resultDate));
            } catch (Exception e) {
                e.printStackTrace();
                rowErrors++;
            }
            System.out.println("");
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

    PatientViralLoad latestViralLoad, newViralLoad;
    for (PatientViralLoadDataImport patientDataImport : patients) {
        try {
            latestViralLoad = PatientManager.getLastPatientViralLoad(sess, patientDataImport.getId());
            newViralLoad = new PatientViralLoad();
            if (latestViralLoad == null) {
                newViralLoad = new PatientViralLoad();
                newViralLoad.setHighViralLoad(patientDataImport.getHighViralLoad());
                newViralLoad.setBelongsGaac(false);
                newViralLoad.setRecommendedToCounselor(false);
                newViralLoad.setResultDate(new java.sql.Date(patientDataImport.getResultDate().getTime()));
                newViralLoad.setCounselingDate(null);
                newViralLoad.setGaacNumber(Integer.parseInt("0"));
                newViralLoad.setPatient(PatientManager.getPatient(sess, patientDataImport.getId()));
            } else {
                newViralLoad = new PatientViralLoad();
                newViralLoad.setHighViralLoad(patientDataImport.getHighViralLoad());
                newViralLoad.setBelongsGaac(latestViralLoad.getBelongsGaac());
                newViralLoad.setRecommendedToCounselor(latestViralLoad.getRecommendedToCounselor());
                newViralLoad.setResultDate(new java.sql.Date(patientDataImport.getResultDate().getTime()));
                newViralLoad.setCounselingDate(latestViralLoad.getCounselingDate());
                newViralLoad.setGaacNumber(latestViralLoad.getGaacNumber());
                newViralLoad.setPatient(PatientManager.getPatient(sess, patientDataImport.getId()));
            }

            Transaction tx = null;

            try {

                tx = sess.beginTransaction();

                PatientManager.addPatientViralLoad(sess, newViralLoad);

                sess.flush();
                tx.commit();

            } catch (HibernateException he) {
                if (tx != null) {
                    tx.rollback();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    String message = Messages.getString("PatientAdmin.button.importPatient.error1") + " " + rowErrors + " "
            + Messages.getString("PatientAdmin.button.importPatient.error2") + patients.size()
            + Messages.getString("PatientAdmin.button.importPatient.success");
    JOptionPane.showMessageDialog(null, message);

}

From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD
    // by//from www .java 2 s . com
    // "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

From source file:org.codelabor.example.crud.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD
    // by// ww  w .  java  2 s  . c om
    // "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

From source file:org.codelabor.example.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(MultipartFile file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);// w  w  w  .  j av a  2 s .c o  m
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

From source file:org.codelabor.example.emp.web.controller.EmpController.java

License:Apache License

private List<EmpDto> fileToDtoList(Part file, List<String> failureMessages)
        throws IllegalArgumentException, InvalidFormatException, IOException { // NOPMD by "SHIN Sang-jae"

    Workbook wb = WorkbookFactory.create(file.getInputStream());
    int numberOfSheets = wb.getNumberOfSheets();
    logger.debug("numberOfSheets: {}", numberOfSheets);

    // prepare model
    List<EmpDto> empDtoList = new ArrayList<EmpDto>();

    // set effective position
    int effectiveFirstSheetIndex = 0;
    int effectiveLastSheetIndex = numberOfSheets - 1;

    // traverse sheet
    StringBuilder sb = new StringBuilder();
    for (int i = effectiveFirstSheetIndex; i <= effectiveLastSheetIndex; i++) {
        Sheet sheet = wb.getSheetAt(i);/*from w w w .j a  v a2s  .  co  m*/
        String sheetName = sheet.getSheetName();
        logger.debug("sheetName: {}", sheetName);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        logger.debug("firstRowNum: {},  lastRowNum: {}", firstRowNum, lastRowNum);

        // set effective position
        int effectiveFirstRowIndex = 1; // header row: 0
        int effectiveLastRowIndex = lastRowNum;

        // traverse row
        for (int j = effectiveFirstRowIndex; j <= effectiveLastRowIndex; j++) {
            // prepare model
            EmpDto empDto = new EmpDto(); // NOPMD by "SHIN Sang-jae"

            Row row = sheet.getRow(j);
            int rowNum = row.getRowNum();
            int firstCellNum = row.getFirstCellNum();
            int lastCellNum = row.getLastCellNum();
            logger.debug("rowNum: {}, firstCellNum: {},  lastCellNum: {}", rowNum, firstCellNum, lastCellNum);

            // set effective position
            int effectiveFirstCellIndex = firstCellNum;
            int effectiveLastCellIndex = lastCellNum - 1;

            // traverse cell
            for (int k = effectiveFirstCellIndex; k <= effectiveLastCellIndex; k++) {
                Cell cell = row.getCell(k);
                if (cell != null) {
                    int rowIndex = cell.getRowIndex();
                    int columnIndex = cell.getColumnIndex();
                    CellReference cellRef = new CellReference(rowIndex, columnIndex); // NOPMD by "SHIN Sang-jae"

                    logger.debug("cellRef: {}, rowIndex: {}, columnIndex: {}", cellRef, rowIndex, columnIndex);
                    // populate dto
                    switch (k) {
                    case 0: // EMPNO
                        empDto.setEmpNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 1: // ENAME
                        empDto.setEname(cell.getRichStringCellValue().toString());
                        break;
                    case 2: // JOB
                        empDto.setJob(cell.getRichStringCellValue().toString());
                        break;
                    case 3: // MGR
                        empDto.setMgr(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    case 4: // HIREDATE
                        empDto.setHireDate(cell.getDateCellValue());
                        break;
                    case 5: // SAL
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setSal(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 6: // COMM
                        // http://stackoverflow.com/questions/12395281/convert-double-to-bigdecimal-and-set-bigdecimal-precision
                        empDto.setComm(BigDecimal.valueOf(cell.getNumericCellValue()));
                        break;
                    case 7: // DEPTNO
                        empDto.setDeptNo(((Double) cell.getNumericCellValue()).intValue());
                        break;
                    default:
                        break;
                    }
                }
            }
            logger.debug("empDto: {}", empDto);

            // validate
            Validator validator = Validation.buildDefaultValidatorFactory().getValidator();
            Set<ConstraintViolation<EmpDto>> violations = validator.validate(empDto);

            if (violations.isEmpty()) {
                // do all or nothing
                empDtoList.add(empDto);
            } else {
                // add failure message
                sb.setLength(0); // init StringBuilder for reuse
                for (ConstraintViolation<EmpDto> violation : violations) {
                    String propertyPath = violation.getPropertyPath().toString();
                    String message = violation.getMessage();
                    sb.append(message);
                    sb.append(" (row: ").append(j).append(", property: ").append(propertyPath).append(')');
                    failureMessages.add(sb.toString());
                    logger.error(sb.toString());
                    sb.setLength(0);
                }
            }
        }
    }
    return empDtoList;
}

From source file:org.databene.formats.xls.XLSUtil.java

License:Open Source License

public static Object resolveCellValue(Cell cell, String emptyMarker, String nullMarker,
        Converter<String, ?> stringPreprocessor) {
    if (cell == null)
        return null;
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        return convertString(cell, emptyMarker, nullMarker, stringPreprocessor);
    case Cell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell))
            return cell.getDateCellValue();
        else//w ww  . ja  v a  2  s  . com
            return mapNumberType(cell.getNumericCellValue());
    case Cell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue();
    case Cell.CELL_TYPE_BLANK:
    case Cell.CELL_TYPE_ERROR:
        return cell.getRichStringCellValue().getString();
    case Cell.CELL_TYPE_FORMULA:
        FormulaEvaluator evaluator = createFormulaEvaluator(cell);
        CellValue cellValue = evaluator.evaluate(cell);
        switch (cellValue.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            return convertString(cellValue, emptyMarker, stringPreprocessor);
        case HSSFCell.CELL_TYPE_NUMERIC:
            if (HSSFDateUtil.isCellDateFormatted(cell))
                return HSSFDateUtil.getJavaDate(cellValue.getNumberValue());
            else
                return mapNumberType(cellValue.getNumberValue());
        case Cell.CELL_TYPE_BOOLEAN:
            return cellValue.getBooleanValue();
        case HSSFCell.CELL_TYPE_BLANK:
        case HSSFCell.CELL_TYPE_ERROR:
            return null;
        default:
            throw new IllegalStateException("Unexpected cell type: " + cellValue.getCellType());
            // CELL_TYPE_FORMULA is not supposed to be encountered here
        }
    default:
        throw new ConfigurationError("Not a supported cell type: " + cell.getCellType());
    }
}