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

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

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

Introduction

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

Prototype

CellType getCachedFormulaResultType();

Source Link

Document

Only valid for formula cells Will return CellType in a future version of POI.

Usage

From source file:adams.data.io.input.ExcelSpreadSheetReader.java

License:Open Source License

/**
 * Reads the spreadsheet content from the specified file.
 *
 * @param in      the input stream to read from
 * @return      the spreadsheets or null in case of an error
 *//*w w  w  .  j  av  a2  s.c  om*/
@Override
protected List<SpreadSheet> doReadRange(InputStream in) {
    List<SpreadSheet> result;
    int[] indices;
    Workbook workbook;
    Sheet sheet;
    SpreadSheet spsheet;
    Row exRow;
    Cell exCell;
    adams.data.spreadsheet.Row spRow;
    int i;
    int n;
    int cellType;
    DateFormat dformat;
    boolean numeric;
    int dataRowStart;
    int firstRow;
    int lastRow;
    List<String> header;

    result = new ArrayList<>();

    workbook = null;
    dformat = DateUtils.getTimestampFormatter();
    try {
        workbook = WorkbookFactory.create(in);
        m_SheetRange.setMax(workbook.getNumberOfSheets());
        indices = m_SheetRange.getIntIndices();
        firstRow = m_FirstRow - 1;
        dataRowStart = getNoHeader() ? firstRow : firstRow + 1;
        for (int index : indices) {
            if (m_Stopped)
                break;

            spsheet = m_SpreadSheetType.newInstance();
            spsheet.setDataRowClass(m_DataRowType.getClass());
            result.add(spsheet);

            if (isLoggingEnabled())
                getLogger().info("sheet: " + (index + 1));

            sheet = workbook.getSheetAt(index);
            if (sheet.getLastRowNum() == 0) {
                getLogger().severe("No rows in sheet #" + index);
                return null;
            }
            spsheet.setName(sheet.getSheetName());

            // header
            if (isLoggingEnabled())
                getLogger().info("header row");
            exRow = sheet.getRow(firstRow);
            if (exRow == null) {
                getLogger().warning("No data in sheet #" + (index + 1) + "?");
            } else if (exRow != null) {
                spRow = spsheet.getHeaderRow();
                m_TextColumns.setMax(exRow.getLastCellNum());
                if (getNoHeader()) {
                    header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders);
                    for (i = 0; i < header.size(); i++)
                        spRow.addCell("" + (i + 1)).setContent(header.get(i));
                } else {
                    if (!m_CustomColumnHeaders.trim().isEmpty()) {
                        header = SpreadSheetUtils.createHeader(exRow.getLastCellNum(), m_CustomColumnHeaders);
                        for (i = 0; i < header.size(); i++)
                            spRow.addCell("" + (i + 1)).setContent(header.get(i));
                    } else {
                        for (i = 0; i < exRow.getLastCellNum(); i++) {
                            if (m_Stopped)
                                break;
                            exCell = exRow.getCell(i);
                            if (exCell == null) {
                                spRow.addCell("" + (i + 1)).setMissing();
                                continue;
                            }
                            numeric = !m_TextColumns.isInRange(i);
                            switch (exCell.getCellType()) {
                            case Cell.CELL_TYPE_BLANK:
                            case Cell.CELL_TYPE_ERROR:
                                spRow.addCell("" + (i + 1)).setContent("column-" + (i + 1));
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(exCell))
                                    spRow.addCell("" + (i + 1)).setContent(new DateTime(
                                            HSSFDateUtil.getJavaDate(exCell.getNumericCellValue())));
                                else if (numeric)
                                    spRow.addCell("" + (i + 1)).setContent(exCell.getNumericCellValue());
                                else
                                    spRow.addCell("" + (i + 1)).setContentAsString(numericToString(exCell));
                                break;
                            default:
                                spRow.addCell("" + (i + 1)).setContentAsString(exCell.getStringCellValue());
                            }
                        }
                    }
                }
            }

            // data
            if (spsheet.getColumnCount() > 0) {
                if (m_NumRows < 1)
                    lastRow = sheet.getLastRowNum();
                else
                    lastRow = Math.min(firstRow + m_NumRows - 1, sheet.getLastRowNum());
                for (i = dataRowStart; i <= lastRow; i++) {
                    if (m_Stopped)
                        break;
                    if (isLoggingEnabled())
                        getLogger().info("data row: " + (i + 1));
                    spRow = spsheet.addRow("" + spsheet.getRowCount());
                    exRow = sheet.getRow(i);
                    if (exRow == null)
                        continue;
                    for (n = 0; n < exRow.getLastCellNum(); n++) {
                        // too few columns in header?
                        if ((n >= spsheet.getHeaderRow().getCellCount()) && m_AutoExtendHeader)
                            spsheet.insertColumn(spsheet.getColumnCount(), "");

                        m_TextColumns.setMax(spsheet.getHeaderRow().getCellCount());
                        exCell = exRow.getCell(n);
                        if (exCell == null) {
                            spRow.addCell(n).setMissing();
                            continue;
                        }
                        cellType = exCell.getCellType();
                        if (cellType == Cell.CELL_TYPE_FORMULA)
                            cellType = exCell.getCachedFormulaResultType();
                        numeric = !m_TextColumns.isInRange(n);
                        switch (cellType) {
                        case Cell.CELL_TYPE_BLANK:
                        case Cell.CELL_TYPE_ERROR:
                            if (m_MissingValue.isEmpty())
                                spRow.addCell(n).setMissing();
                            else
                                spRow.addCell(n).setContent("");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (HSSFDateUtil.isCellDateFormatted(exCell))
                                spRow.addCell(n).setContent(
                                        dformat.format(HSSFDateUtil.getJavaDate(exCell.getNumericCellValue())));
                            else if (numeric)
                                spRow.addCell(n).setContent(exCell.getNumericCellValue());
                            else
                                spRow.addCell(n).setContentAsString(numericToString(exCell));
                            break;
                        default:
                            if (m_MissingValue.isMatch(exCell.getStringCellValue()))
                                spRow.addCell(n).setMissing();
                            else
                                spRow.addCell(n).setContentAsString(exCell.getStringCellValue());
                        }
                    }
                }
            }
        }
    } catch (Exception ioe) {
        getLogger().log(Level.SEVERE, "Failed to read range '" + m_SheetRange + "':", ioe);
        result = null;
        m_LastError = "Failed to read range '" + m_SheetRange + "' from stream!\n"
                + Utils.throwableToString(ioe);
    }

    return result;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyString readString(Cell cell, int type) throws Exception {
    ExclyString output = null;/* ww w . j a  va2 s. co  m*/

    if (type == Cell.CELL_TYPE_STRING) {
        output = new ExclyString(cell.getStringCellValue());
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyStringError();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readString(cell, formulaType);
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyString("");
    } else if (type == Cell.CELL_TYPE_BOOLEAN) {
        Boolean data = cell.getBooleanCellValue();
        if (data) {
            output = new ExclyString("WAHR");
        } else {
            output = new ExclyString("FALSCH");
        }
    } else if (DateUtil.isCellDateFormatted(cell)) {
        Date data = cell.getDateCellValue();
        output = new ExclyString(data.toString());
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double cellValue = cell.getNumericCellValue();
        String data = String.valueOf(cellValue);
        if (cellValue % 1 == 0 && data.endsWith(".0")) {
            data = data.substring(0, data.length() - 2);
        }
        output = new ExclyString(data);
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyStringError();
    }

    return output;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyDouble readDouble(Cell cell, int type) throws Exception {
    ExclyDouble output = null;/*from ww  w . j  av  a  2 s  .  c  o m*/

    if (type == Cell.CELL_TYPE_STRING) {
        String data = cell.getStringCellValue();
        if (isNumericGerman(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data);
            output = new ExclyDouble(number.doubleValue());
        } else if (isNumericUK(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data);
            output = new ExclyDouble(number.doubleValue());
        } else if (data.equals("") || data.equals(" ") || data.equals("-")) {
            output = new ExclyDoubleBlank();
        } else {
            output = new ExclyDoubleError();
            log.warn("The reader has expected a numeric value, but found a string value. [Row, Column] ("
                    + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        }
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyDoubleBlank();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readDouble(cell, formulaType);
    } else if (type == Cell.CELL_TYPE_BOOLEAN) {
        Boolean data = cell.getBooleanCellValue();
        if (data) {
            output = new ExclyDouble(1);
        } else {
            output = new ExclyDouble(0);
        }
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double data = cell.getNumericCellValue();
        output = new ExclyDouble(data);
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyDoubleError();
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyDoubleError();
    }

    return output;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyInteger readInteger(Cell cell, int type) throws Exception {
    ExclyInteger output = null;//from w w w.j  a v a  2s  .c o  m

    if (type == Cell.CELL_TYPE_STRING) {
        String data = cell.getStringCellValue();
        if (isNumericGerman(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data);
            output = new ExclyInteger(number.intValue());
        } else if (isNumericUK(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data);
            output = new ExclyInteger(number.intValue());
        } else if (data.equals("") || data.equals(" ") || data.trim().equals("-")) {
            output = new ExclyIntegerBlank();
        } else {
            output = new ExclyIntegerError();
            log.warn("The reader has expected a numeric value, but found a string value. [Row, Column] ("
                    + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        }
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyIntegerBlank();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readInteger(cell, formulaType);
    } else if (type == Cell.CELL_TYPE_BOOLEAN) {
        Boolean data = cell.getBooleanCellValue();
        if (data) {
            output = new ExclyInteger(1);
        } else {
            output = new ExclyInteger(0);
        }
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double data = cell.getNumericCellValue();
        output = new ExclyInteger(data);
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyIntegerError();
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyIntegerError();
    }

    return output;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyLong readLong(Cell cell, int type) throws Exception {
    ExclyLong output = null;/* w w w .  j a va 2  s . c  o m*/

    if (type == Cell.CELL_TYPE_STRING) {
        String data = cell.getStringCellValue();
        if (isNumericGerman(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data);
            output = new ExclyLong(number.intValue());
        } else if (isNumericUK(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data);
            output = new ExclyLong(number.intValue());
        } else if (data.equals("") || data.equals(" ") || data.equals("-")) {
            output = new ExclyLongBlank();
        } else {
            output = new ExclyLongError();
            log.warn("The reader has expected a numeric value, but found a string value. [Row, Column] ("
                    + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        }
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyLongBlank();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readLong(cell, formulaType);
    } else if (type == Cell.CELL_TYPE_BOOLEAN) {
        Boolean data = cell.getBooleanCellValue();
        if (data) {
            output = new ExclyLong(1);
        } else {
            output = new ExclyLong(0);
        }
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double data = cell.getNumericCellValue();
        output = new ExclyLong(data);
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyLongError();
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyLongError();
    }

    return output;
}

From source file:at.mukprojects.exclycore.dao.XLSXReader.java

License:Open Source License

private ExclyDate readDate(Cell cell, int type) throws Exception {
    ExclyDate output = null;//w w w.  j a  v  a2  s.  c o  m

    if (type == Cell.CELL_TYPE_STRING) {
        String data = cell.getStringCellValue();
        if (isNumericGerman(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.GERMAN).parse(data);
            output = new ExclyDate(DateUtil.getJavaDate(number.intValue()));
        } else if (isNumericUK(data)) {
            Number number = NumberFormat.getNumberInstance(Locale.UK).parse(data);
            output = new ExclyDate(DateUtil.getJavaDate(number.intValue()));
        } else if (data.equals("") || data.equals(" ") || data.trim().equals("-")) {
            output = new ExclyDateBlank();
        } else {
            ExclyDate parsedDate = parse(cell.getStringCellValue());
            output = parsedDate;
        }
    } else if (type == Cell.CELL_TYPE_BLANK) {
        output = new ExclyDateBlank();
    } else if (type == Cell.CELL_TYPE_FORMULA) {
        int formulaType = cell.getCachedFormulaResultType();
        output = readDate(cell, formulaType);
    } else if (DateUtil.isCellDateFormatted(cell)) {
        Date data = cell.getDateCellValue();
        output = new ExclyDate(data);
    } else if (type == Cell.CELL_TYPE_NUMERIC) {
        double data = cell.getNumericCellValue();
        output = new ExclyDate(DateUtil.getJavaDate(data));
    } else if (type == Cell.CELL_TYPE_ERROR) {
        output = new ExclyDateError();
    } else {
        log.warn("The reader was unable to find a valid parser for the cell [Row, Column] ("
                + cell.getRowIndex() + ", " + cell.getColumnIndex() + ")");
        output = new ExclyDateError();
    }

    return output;
}

From source file:automatedhgl.AutomatedHGL.java

public static void main(String[] args) {

    try {/*from ww w.  ja va 2s  .  c o  m*/

        FileInputStream excelFile = new FileInputStream(new File(INFILE_NAME));

        //create workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(excelFile);

        //get first desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //create workbook instance to output excel file
        XSSFWorkbook workbookHGL = new XSSFWorkbook();

        //create sheet in output excel file
        XSSFSheet sheetHGL = workbookHGL.createSheet("HGL");

        //iterate through each row one by one
        Iterator<Row> rowiterator = sheet.iterator();

        while (rowiterator.hasNext()) {
            Row row = rowiterator.next();

            //for each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {

                Cell cell = cellIterator.next();

                if (row.getRowNum() > 7 && count < 23) //to filter column headings
                {

                    //check the cell type and format accordingly
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        count++;

                        //assign get value to correct variable
                        if (count == 1) {
                            InletStr = cell.getNumericCellValue();
                        } else if (count == 2) {
                            OutWSE = cell.getNumericCellValue();
                        }

                        System.out.print(cell.getNumericCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_STRING:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getStringCellValue();}*/

                        System.out.print(cell.getStringCellValue() + " (" + count + ") ");
                        break;

                    case Cell.CELL_TYPE_FORMULA:
                        count++;

                        /*//assign get value to correct variable
                        if( count == 1 ){InletStr = cell.getCachedFormulaResultType();}*/

                        System.out.print(cell.getCachedFormulaResultType() + " (" + count + ") ");
                        break;
                    }
                }

                else {
                    count = 0; //reset the count at the end of the row
                }

            }

            System.out.println("return");
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    //Output Excel file

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Datatypes in Java");
    Object[][] datatypes = { { "Datatype", "Type", "Size(in bytes)" }, { "int", "Primitive", 2 },
            { "float", "Primitive", 4 }, { "double", "Primitive", 8 }, { "char", "Primitive", 1 },
            { "String", "Non-Primitive", "No fixed size" } };

    int rowNum = 0;
    System.out.println("Creating excel");

    for (Object[] datatype : datatypes) {
        Row row = sheet.createRow(rowNum++);
        int colNum = 0;
        for (Object field : datatype) {
            Cell cell = row.createCell(colNum++);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }
    }

    try {
        FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
        workbook.write(outputStream);
        workbook.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.print(InletStr + " ");
    System.out.print(OutWSE + " ");
    System.out.println("HGL Done");

}

From source file:cherry.goods.excel.ExcelReader.java

License:Apache License

/**
 * ??????<br />//from   w  w w .  j a  v  a2  s.  c  om
 * 
 * @param cell ??
 * @return ?
 */
private String getCellValueAsString(Cell cell) {
    switch (cell.getCellType()) {
    case CELL_TYPE_NUMERIC:
        return getNumericCellValueAsString(cell);
    case CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case CELL_TYPE_FORMULA:
        switch (cell.getCachedFormulaResultType()) {
        case CELL_TYPE_NUMERIC:
            return getNumericCellValueAsString(cell);
        case CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case CELL_TYPE_BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        default:
            return null;
        }
    case CELL_TYPE_BLANK:
        return "";
    case CELL_TYPE_BOOLEAN:
        return String.valueOf(cell.getBooleanCellValue());
    default:
        return null;
    }
}

From source file:cherry.parser.worksheet.CellUtil.java

License:Apache License

public static String getCellValueAsString(Cell cell) {
    switch (cell.getCellType()) {
    case CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case CELL_TYPE_NUMERIC:
        return String.valueOf((int) cell.getNumericCellValue());
    case CELL_TYPE_FORMULA:
        switch (cell.getCachedFormulaResultType()) {
        case CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case CELL_TYPE_NUMERIC:
            return String.valueOf((int) cell.getNumericCellValue());
        default:/*from w w w  .  java2 s .  c o  m*/
            return null;
        }
    default:
        return null;
    }
}

From source file:cn.edu.zucc.chenxg.preview.ToHtml.java

License:Apache License

private static int ultimateCellType(Cell c) {
    int type = c.getCellType();
    if (type == Cell.CELL_TYPE_FORMULA)
        type = c.getCachedFormulaResultType();
    return type;/*from  w  ww. j  av  a 2  s.  c  om*/
}