Example usage for org.apache.poi.ss.usermodel FormulaError NA

Introduction

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

Prototype

FormulaError NA

To view the source code for org.apache.poi.ss.usermodel FormulaError NA.

Click Source Link

Document

Intended to indicate when a designated value is not available.

Usage

From source file:com.miraisolutions.xlconnect.Workbook.java

License:Open Source License

private void writeData(DataFrame data, Sheet sheet, int startRow, int startCol, boolean header) {
    // Get styles
    Map<String, CellStyle> styles = getStyles(data, sheet, startRow, startCol);

    // Define row & column index variables
    int rowIndex = startRow;
    int colIndex = startCol;

    // In case of column headers ...
    if (header && data.hasColumnHeader()) {
        // For each column write corresponding column name
        for (int i = 0; i < data.columns(); i++) {
            Cell cell = getCell(sheet, rowIndex, colIndex + i);
            cell.setCellValue(data.getColumnName(i));
            cell.setCellType(Cell.CELL_TYPE_STRING);
            setCellStyle(cell, styles.get(HEADER + i));
        }/*w  w  w.  j  a va  2  s  .c  o m*/

        ++rowIndex;
    }

    // For each column of data
    for (int i = 0; i < data.columns(); i++) {
        // Get column style
        CellStyle cs = styles.get(COLUMN + i);
        Column col = data.getColumn(i);
        // Depending on column type ...
        switch (data.getColumnType(i)) {
        case Numeric:
            double[] doubleValues = col.getNumericData();
            for (int j = 0; j < data.rows(); j++) {
                Cell cell = getCell(sheet, rowIndex + j, colIndex);
                if (col.isMissing(j))
                    setMissing(cell);
                else {
                    if (Double.isInfinite(doubleValues[j])) {
                        cell.setCellType(Cell.CELL_TYPE_ERROR);
                        cell.setCellErrorValue(FormulaError.NA.getCode());
                    } else {
                        cell.setCellValue(doubleValues[j]);
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    }
                    setCellStyle(cell, cs);
                }
            }
            break;
        case String:
            String[] stringValues = col.getStringData();
            for (int j = 0; j < data.rows(); j++) {
                Cell cell = getCell(sheet, rowIndex + j, colIndex);
                if (col.isMissing(j))
                    setMissing(cell);
                else {
                    cell.setCellValue(stringValues[j]);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    setCellStyle(cell, cs);
                }
            }
            break;
        case Boolean:
            boolean[] booleanValues = col.getBooleanData();
            for (int j = 0; j < data.rows(); j++) {
                Cell cell = getCell(sheet, rowIndex + j, colIndex);
                if (col.isMissing(j))
                    setMissing(cell);
                else {
                    cell.setCellValue(booleanValues[j]);
                    cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
                    setCellStyle(cell, cs);
                }
            }
            break;
        case DateTime:
            Date[] dateValues = col.getDateTimeData();
            for (int j = 0; j < data.rows(); j++) {
                Cell cell = getCell(sheet, rowIndex + j, colIndex);
                if (col.isMissing(j))
                    setMissing(cell);
                else {
                    cell.setCellValue(dateValues[j]);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    setCellStyle(cell, cs);
                }
            }
            break;
        default:
            throw new IllegalArgumentException("Unknown column type detected!");
        }

        ++colIndex;
    }

    // Force formula recalculation for HSSFSheet
    if (isHSSF()) {
        ((HSSFSheet) sheet).setForceFormulaRecalculation(true);
    }
}

---

From source file:hu.webhejj.commons.io.table.excel.ExcelRowValueConverter.java

License:Apache License

public <T> T getValue(Row row, int column, Class<T> valueType) {

    if (row == null) {
        return null;
    }/*w w  w  .j a  v a 2  s.  c o m*/

    Cell cell = row.getCell(column);
    if (cell == null) {
        return null;
    }

    CellValue cellValue = getCellValue(row, cell, column);
    if (cellValue == null) {
        return null;
    }

    switch (cellValue.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
        return null;

    case Cell.CELL_TYPE_BOOLEAN:
        if (String.class.isAssignableFrom(valueType)) {
            return (T) Boolean.toString(cellValue.getBooleanValue());

        } else if (Integer.class.isAssignableFrom(valueType)) {
            return (T) Integer.valueOf(cellValue.getBooleanValue() ? 1 : 0);

        } else if (Long.class.isAssignableFrom(valueType)) {
            return (T) Long.valueOf(cellValue.getBooleanValue() ? 1L : 0L);

        } else {
            throw new ClassCastException(
                    "Can't convert " + cellValue.getBooleanValue() + " to " + valueType.getName());
        }

    case Cell.CELL_TYPE_STRING:
        String stringValue = cellValue.getStringValue();
        if (CompareUtils.isEmpty(stringValue)) {
            return null;
        }
        if ("null".equals(stringValue)) {
            return null;
        }
        if (String.class.isAssignableFrom(valueType)) {
            return (T) stringValue;

        } else if (Integer.class.isAssignableFrom(valueType)) {
            return (T) Integer.valueOf(stringValue);

        } else if (Long.class.isAssignableFrom(valueType)) {
            return (T) Long.valueOf(stringValue);

        } else if (valueType.isEnum()) {
            return (T) Enum.valueOf((Class<? extends Enum>) valueType, stringValue);

        } else if (BigDecimal.class.isAssignableFrom(valueType)) {
            return (T) (CompareUtils.isEmpty(stringValue) ? null : new BigDecimal(stringValue));

        } else if (Boolean.class.isAssignableFrom(valueType)) {
            return (T) Boolean.valueOf("true".equalsIgnoreCase(stringValue)
                    || (!CompareUtils.isEmpty(stringValue) && !"0".equals(stringValue)));

        } else {
            throw new ClassCastException("Can't convert " + stringValue + " to " + valueType.getName());
        }

    case Cell.CELL_TYPE_NUMERIC:
        if (String.class.isAssignableFrom(valueType)) {
            Format format = formatter.createFormat(cell);
            if (format == null) {
                // TODO: do this without creating a BigDecimal each time
                return (T) new BigDecimal(cellValue.getNumberValue()).toString();
            } else {
                return (T) format.format(cellValue.getNumberValue());
            }

        } else if (Integer.class.isAssignableFrom(valueType)) {
            return (T) Integer.valueOf((int) cellValue.getNumberValue());

        } else if (Long.class.isAssignableFrom(valueType)) {
            return (T) Long.valueOf((int) cellValue.getNumberValue());

        } else {
            throw new ClassCastException(
                    "Can't convert " + cellValue.getNumberValue() + " to " + valueType.getName());
        }
    case Cell.CELL_TYPE_ERROR:
        FormulaError error = FormulaError.forInt(cell.getErrorCellValue());
        if (FormulaError.NA.equals(error)) {
            return null;
        } else {
            // System.err.format("  Cell[%d,%d] error code %s\n", r.getRowNum(), column, error);
            return null;
            // throw new RuntimeException(String.format("Cell[%d,%d] error code %s", r.getRowNum(), column, error));
        }
    }
    throw new IllegalArgumentException("Don't know how to convert cell of type " + cellValue.getCellType());
}

---