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:nu.mine.kino.jenkins.plugins.projectmanagement.utils.PMUtils.java

License:Open Source License

public static Date getBaseDateFromExcelWithPoi(File file) {
    InputStream in = null;//from w w w  .  j a v a  2s .c  o m
    try {
        in = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(in);
        Sheet sheet = workbook.getSheetAt(0);
        Name name = workbook.getName("??");
        CellReference cellRef = new CellReference(name.getRefersToFormula());
        Row row = sheet.getRow(cellRef.getRow());
        Cell baseDateCell = row.getCell(cellRef.getCol());
        // System.out.println("cellt:"
        // + PoiUtil.isCellDateFormatted(baseDateCell));
        Date baseDate = baseDateCell.getDateCellValue();
        return baseDate;

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (in != null)
            try {
                in.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

    }
    return null;
}

From source file:nu.mine.kino.projects.utils.POI2Test.java

License:Open Source License

public void test3() throws InvalidFormatException, IOException {

    // String range = "A1:C5";
    // CellRangeAddress address = CellRangeAddress.valueOf(range);
    // int firstRow = address.getFirstRow();
    // int lastRow = address.getLastRow();
    // int firstColumn = address.getFirstColumn();
    // int lastColumn = address.getLastColumn();
    // System.out.println(firstRow);
    // System.out.println(lastRow);
    // System.out.println(lastColumn);
    // System.out.println(firstColumn);

    // OwZ/*from w  w  w . j  a v  a  2s . co m*/
    Sheet sheet = workbook.getSheetAt(0);
    Name name = workbook.getName("??");
    CellReference cellRef = new CellReference(name.getRefersToFormula());

    Row row = sheet.getRow(cellRef.getRow());
    Cell baseDateCell = row.getCell(cellRef.getCol());
    System.out.println("cellt:" + PoiUtil.isCellDateFormatted(baseDateCell));
    Date baseDate = baseDateCell.getDateCellValue();
    System.out.println(baseDate);
    System.out.println(baseDateCell.getNumericCellValue());

    // OwZ?B
    final Name DATA_AREA = workbook.getName("DATA_AREA");
    final AreaReference areaReference = new AreaReference(DATA_AREA.getRefersToFormula());
    final CellReference firstCell = areaReference.getFirstCell();
    final CellReference lastCell = areaReference.getLastCell();

    Row fRow = sheet.getRow(firstCell.getRow());
    Row lRow = sheet.getRow(lastCell.getRow());

    System.out.printf("??sIndex %s\n", firstCell.getRow());
    System.out.printf("??I?sIndex %s\n", lastCell.getRow());

    Cell fCell = fRow.getCell(firstCell.getCol());
    Cell lCell = lRow.getCell(lastCell.getCol());
    System.out.println(fCell);
    System.out.println(lCell);

    for (int index = firstCell.getRow(); index <= lastCell.getRow(); index++) {
        Row tmpRow = sheet.getRow(index);
        Cell tmpCell = tmpRow.getCell(firstCell.getCol()); // RRwColumnNumber?B
        System.out.println(tmpCell);
    }

}

From source file:nu.mine.kino.projects.utils.POI2Test.java

License:Open Source License

public void test4() throws InvalidFormatException, IOException {

    // OwZ//from   ww  w.  ja v  a  2 s  .  c  o m
    Sheet sheet = workbook.getSheetAt(0);
    Name name = workbook.getName("??");
    CellReference cellRef = new CellReference(name.getRefersToFormula());

    Row row = sheet.getRow(cellRef.getRow());
    Cell baseDateCell = row.getCell(cellRef.getCol());
    System.out.println("cellt:" + PoiUtil.isCellDateFormatted(baseDateCell));
    Date baseDate = baseDateCell.getDateCellValue();
    System.out.println(baseDate);
    System.out.println(baseDateCell.getNumericCellValue());

    // OwZ?B
    final Name DATA_AREA = workbook.getName("DATA_AREA");
    final AreaReference areaReference = new AreaReference(DATA_AREA.getRefersToFormula());
    final CellReference firstCell = areaReference.getFirstCell();
    final CellReference lastCell = areaReference.getLastCell();

    Row fRow = sheet.getRow(firstCell.getRow());
    Row lRow = sheet.getRow(lastCell.getRow());

    System.out.printf("??sIndex %s\n", firstCell.getRow());
    System.out.printf("??I?sIndex %s\n", lastCell.getRow());

    Cell fCell = fRow.getCell(firstCell.getCol());
    Cell lCell = lRow.getCell(lastCell.getCol());
    System.out.println(fCell);
    System.out.println(lCell);

    for (int index = firstCell.getRow(); index <= lastCell.getRow(); index++) {
        Row tmpRow = sheet.getRow(index);
        Cell tmpCell = tmpRow.getCell(firstCell.getCol()); // RRwColumnNumber?B
        tmpCell.setCellValue(index * 100);
        System.out.println(tmpCell);
    }

    try {
        out = new FileOutputStream(new java.io.File("testdata4.xls"));
        workbook.write(out);
    } catch (FileNotFoundException e) {
        Assert.fail(e.getMessage());
    } catch (IOException e) {
        Assert.fail(e.getMessage());
    }
}

From source file:nu.mine.kino.projects.utils.POITest.java

License:Open Source License

@Test
public void test3() throws InvalidFormatException, IOException {
    Sheet sheet = workbook.getSheetAt(0);
    Name name = workbook.getName("??");
    CellReference cellRef = new CellReference(name.getRefersToFormula());
    Row row = sheet.getRow(cellRef.getRow());
    Cell baseDateCell = row.getCell(cellRef.getCol());
    System.out.println("cellt:" + PoiUtil.isCellDateFormatted(baseDateCell));
    Date baseDate = baseDateCell.getDateCellValue();
    System.out.println(baseDate);
}

From source file:nu.mine.kino.projects.utils.PoiUtils.java

License:Open Source License

public static Date getDate(Cell dateCell) {
    Date baseDate = null;/*from www. j a  v a  2 s  .c  om*/
    if (dateCell != null) {// taskId?l?A
        if (dateCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (PoiUtil.isCellDateFormatted(dateCell)) {
                baseDate = dateCell.getDateCellValue();
            }
        }
    }
    return baseDate;
}

From source file:nu.mine.kino.projects.utils.ProjectUtils.java

License:Open Source License

public static Holiday[] createHolidays(Workbook workbook) {
    Sheet sheet = workbook.getSheet("xe?[u");

    List<Holiday> arrayList = new ArrayList<Holiday>();
    Iterator<Row> e = sheet.rowIterator();
    while (e.hasNext()) {
        Row row = e.next();//  w  w w  . j  av a2  s.c om
        Holiday holiday = new Holiday();

        Cell dateCell = row.getCell(0);
        if (dateCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (PoiUtil.isCellDateFormatted(dateCell)) {
                Date dateCellValue = dateCell.getDateCellValue();
                holiday.setDate(dateCellValue);
            }
            arrayList.add(holiday);
        }

        // Cell cell1 = row.getCell(1);
        // if (cell1 != null && cell1.getCellType() ==
        // Cell.CELL_TYPE_FORMULA) {
        // holiday.setDayOfWeek((String) PoiUtils.getCellValue(cell1,
        // String.class));
        // }

        Cell cell2 = row.getCell(2);
        if (cell2 != null && cell2.getCellType() == Cell.CELL_TYPE_STRING) {
            holiday.setName(cell2.getStringCellValue());
        }
        Cell cell3 = row.getCell(3);
        if (cell3 != null && cell3.getCellType() == Cell.CELL_TYPE_STRING) {
            holiday.setRule(cell3.getStringCellValue());
        }
        Cell cell4 = row.getCell(4);
        if (cell4 != null && cell4.getCellType() == Cell.CELL_TYPE_STRING) {
            holiday.setHurikae(cell4.getStringCellValue());
        }

    }
    return arrayList.toArray(new Holiday[arrayList.size()]);
}

From source file:org.apache.metamodel.excel.ExcelUtils.java

License:Apache License

public static String getCellValue(Workbook wb, Cell cell) {
    if (cell == null) {
        return null;
    }/* ww w .j a  va  2s .c om*/

    final String cellCoordinate = "(" + cell.getRowIndex() + "," + cell.getColumnIndex() + ")";

    final String result;

    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        result = null;
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        result = Boolean.toString(cell.getBooleanCellValue());
        break;
    case Cell.CELL_TYPE_ERROR:
        String errorResult;
        try {
            byte errorCode = cell.getErrorCellValue();
            FormulaError formulaError = FormulaError.forInt(errorCode);
            errorResult = formulaError.getString();
        } catch (RuntimeException e) {
            logger.debug("Getting error code for {} failed!: {}", cellCoordinate, e.getMessage());
            if (cell instanceof XSSFCell) {
                // hack to get error string, which is available
                String value = ((XSSFCell) cell).getErrorCellString();
                errorResult = value;
            } else {
                logger.error("Couldn't handle unexpected error scenario in cell: " + cellCoordinate, e);
                throw e;
            }
        }
        result = errorResult;
        break;
    case Cell.CELL_TYPE_FORMULA:
        // result = cell.getCellFormula();
        result = getFormulaCellValue(wb, cell);
        break;
    case Cell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            if (date == null) {
                result = null;
            } else {
                result = DateUtils.createDateFormat().format(date);
            }
        } else {
            // TODO: Consider not formatting it, but simple using
            // Double.toString(...)
            result = _numberFormat.format(cell.getNumericCellValue());
        }
        break;
    case Cell.CELL_TYPE_STRING:
        result = cell.getRichStringCellValue().getString();
        break;
    default:
        throw new IllegalStateException("Unknown cell type: " + cell.getCellType());
    }

    logger.debug("cell {} resolved to value: {}", cellCoordinate, result);

    return result;
}

From source file:org.argrr.extractor.excel.SpreadSheetTab.java

License:Open Source License

public SpreadSheetTab(XSSFSheet sheet) {
    this.sheet = sheet;
    columnNames = new ArrayList<String>();
    lines = new ArrayList<HashMap<String, String>>();

    //iterate throw the first line in order to have columns names
    Iterator<Row> rowIterator = sheet.iterator();
    Row curRow = rowIterator.next();/*from   w w  w. jav  a  2 s  .c om*/
    for (int cn = 0; cn < curRow.getLastCellNum(); cn++) {

        Cell cell = curRow.getCell(cn, Row.CREATE_NULL_AS_BLANK);
        columnNames.add(cell.getStringCellValue());
    }

    //Iterate through each other rows in order to have datas
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        HashMap<String, String> curLine = new HashMap<String, String>();

        //For each row, iterate through all the columns
        for (int id = 0; id < columnNames.size(); id++) {

            //add empty cells names if there are more cols in values than header def
            if (id >= this.columnNames.size())
                this.columnNames.add("");

            Cell cell = row.getCell(id, Row.CREATE_NULL_AS_BLANK);
            if (id == 0 && cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            }

            String cellVal = "";
            //Check the cell type and format accordingly
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
                    cellVal = sdf.format(cell.getDateCellValue());
                } else {
                    cellVal = Integer.valueOf(Double.valueOf(cell.getNumericCellValue()).intValue()).toString();
                }
                break;
            case Cell.CELL_TYPE_STRING:
                cellVal = cell.getStringCellValue();
                break;
            }
            curLine.put(this.getColumnName(id), cellVal);
        }
        if (curLine.size() > 0)
            lines.add(curLine);
    }
}

From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java

License:Apache License

private String toStringFromCell(final Cell aCell) { // ???
    String string = "";

    if (null != aCell) {
        switch (aCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            string = Boolean.toString(aCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            string = aCell.getCellFormula();
            // string = cell.getStringCellValue();(
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(aCell)) {
                java.util.Date dt = aCell.getDateCellValue();
                string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt);
            } else {
                string = Double.toString(aCell.getNumericCellValue());
            }/* w  w w .  ja va2  s .com*/
            break;
        case Cell.CELL_TYPE_STRING: {
            string = aCell.getStringCellValue();
            break;
        }
        case Cell.CELL_TYPE_ERROR: {
            break;
        }
        }
    }
    return string;
}

From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java

License:Apache License

private static String toStringFromCell(final Cell aCell) { // ???
    String string = "";

    if (null != aCell) {
        switch (aCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            string = Boolean.toString(aCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            string = aCell.getCellFormula();
            // string = cell.getStringCellValue();(
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(aCell)) {
                java.util.Date dt = aCell.getDateCellValue();
                string = (new SimpleDateFormat("yyyy/MM/dd HH:mm:ss")).format(dt);
            } else {
                string = Double.toString(aCell.getNumericCellValue());
            }/*from  w  ww .j  a  v  a  2  s  .c  o m*/
            break;
        case Cell.CELL_TYPE_STRING: {
            string = aCell.getStringCellValue();
            break;
        }
        case Cell.CELL_TYPE_ERROR: {
            break;
        }
        }
    }
    return string;
}