org.apache.poi.ss.usermodel.CellType Java Examples

/** Add a new row to the sheet */
@Override
public void add(Entity entity) {
  if (entity == null) throw new IllegalArgumentException("Entity cannot be null");
  if (cachedAttributes == null)
    throw new MolgenisDataException(
        "The attribute names are not defined, call writeAttributeNames first");

  int i = 0;
  Row poiRow = sheet.createRow(row++);
  for (Attribute attribute : cachedAttributes) {
    Cell cell = poiRow.createCell(i++, CellType.STRING);
    cell.setCellValue(toValue(entity.get(attribute.getName())));
  }

  entity.getIdValue();
}
 

@SuppressWarnings("deprecation")
	private String validateRow(Row row) {
		for (int i = 0; i < NUM_COLS; i++) {
			Cell cell = row.getCell(i);
			if (cell == null) {
				if (REQUIRED[i]) {
					return "Required cell "+HEADER_TITLES[i]+" missing for row "+String.valueOf(row.getRowNum()+" in Origins Spreadsheet");
				}
			} else {
				if (i == CREATED_COL) {
					if (!(cell.getCellTypeEnum() == CellType.NUMERIC)) {
						return "Created column in origin spreadsheet is not of type Date";
					}
				}
//				if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
//					return "Invalid cell format for "+HEADER_TITLES[i]+" for forw "+String.valueOf(row.getRowNum());
//				}
			}
		}
		return null;
	}
 

@Override
protected void setupCell(final Cell cell, final Optional<CellLink> cellValue) throws TypeBindException {
    
    // 既存のハイパーリンクを削除
    // 削除しないと、Excelの見た目上はリンクは変わっているが、データ上は2重にリンクが設定されている。
    cell.removeHyperlink();
    
    if(cellValue.isPresent()) {
        final CreationHelper helper = cell.getSheet().getWorkbook().getCreationHelper();
        final HyperlinkType type = POIUtils.judgeLinkType(cellValue.get().getLink());
        final Hyperlink link = helper.createHyperlink(type);
        link.setAddress(cellValue.get().getLink());
        cell.setHyperlink(link);
        
        cell.setCellValue(cellValue.get().getLabel());
        
    } else {
        cell.setCellType(CellType.BLANK);
    }
    
}
 

@Override
protected Boolean parseCell(final Cell evaluatedCell, final String formattedValue) throws TypeBindException {
    
    if(evaluatedCell.getCellTypeEnum().equals(CellType.BOOLEAN)) {
        return evaluatedCell.getBooleanCellValue();
        
    } else if(!formattedValue.isEmpty()) {
        try {
            return this.textFormatter.parse(formattedValue);
        } catch(TextParseException e) {
            throw newTypeBindExceptionOnParse(e, evaluatedCell, formattedValue);
        }
        
    }
    
    if(field.getType().isPrimitive()) {
        return false;
        
    } else if(field.isComponentType() && field.getComponentType().isPrimitive()) {
        return false;
    }
    
    return null;
}
 

private static String getCellValueAsString(Cell cell, CellType cellType)
{
    switch (cellType)
    {
        case NUMERIC:
            return String.valueOf(cell.getNumericCellValue());
        case BOOLEAN:
            return String.valueOf(cell.getBooleanCellValue());
        case FORMULA:
            return getCellValueAsString(cell, cell.getCachedFormulaResultType());
        case STRING:
            return cell.getStringCellValue();
        default:
            return StringUtils.EMPTY;
    }
}
 

/**
 * get the value of the cell as a string - for numeric cells we throw an exception.
 * For blank cells we return an empty string.
 * For formulaCells that are not string Formulas, we throw an exception
 */
public HSSFRichTextString getRichStringCellValue() {

    switch(_cellType) {
        case BLANK:
            return new HSSFRichTextString("");
        case STRING:
            return _stringValue;
        default:
            throw typeMismatch(CellType.STRING, _cellType, false);
        case FORMULA:
            break;
    }
    FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record);
    checkFormulaCachedValueType(CellType.STRING, fra.getFormulaRecord());
    String strVal = fra.getStringValue();
    return new HSSFRichTextString(strVal == null ? "" : strVal);
}
 

/**
 * Get the hssfcell representing a given column (logical cell)
 *  0-based.  If you ask for a cell that is not defined, then
 *  your supplied policy says what to do
 *
 * @param cellnum  0 based column number
 * @param policy Policy on blank / missing cells
 * @return representing that column or null if undefined + policy allows.
 */
@Override
public HSSFCell getCell(int cellnum, MissingCellPolicy policy) {
    HSSFCell cell = retrieveCell(cellnum);
    switch (policy) {
        case RETURN_NULL_AND_BLANK:
            return cell;
        case RETURN_BLANK_AS_NULL:
            boolean isBlank = (cell != null && cell.getCellTypeEnum() == CellType.BLANK);
            return (isBlank) ? null : cell;
        case CREATE_NULL_AS_BLANK:
            return (cell == null) ? createCell(cellnum, CellType.BLANK) : cell;
        default:
            throw new IllegalArgumentException("Illegal policy " + policy);
    }
}
 

/**
 * used internally -- given a cell value record, figure out its type
 */
private static CellType determineType(CellValueRecordInterface cval) {
    if (cval instanceof FormulaRecordAggregate) {
        return CellType.FORMULA;
    }
    // all others are plain BIFF records
    Record record = ( Record ) cval;
    switch (record.getSid()) {

        case NumberRecord.sid :   return CellType.NUMERIC;
        case BlankRecord.sid :    return CellType.BLANK;
        case LabelSSTRecord.sid : return CellType.STRING;
        case BoolErrRecord.sid :
            BoolErrRecord boolErrRecord = ( BoolErrRecord ) record;

            return boolErrRecord.isBoolean()
                     ? CellType.BOOLEAN
                     : CellType.ERROR;
    }
    throw new RuntimeException("Bad cell value rec (" + cval.getClass().getName() + ")");
}
 

@SuppressWarnings("deprecation")
	private String validateRow(Row row) {
		for (int i = 0; i < NUM_COLS; i++) {
			Cell cell = row.getCell(i);
			if (cell == null) {
				if (REQUIRED[i]) {
					return "Required cell "+HEADER_TITLES[i]+" missing for row "+String.valueOf(row.getRowNum()+" in Origins Spreadsheet");
				}
			} else {
				if (i == CREATED_COL) {
					if (!(cell.getCellTypeEnum() == CellType.NUMERIC)) {
						return "Created column in origin spreadsheet is not of type Date";
					}
				}
//				if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
//					return "Invalid cell format for "+HEADER_TITLES[i]+" for forw "+String.valueOf(row.getRowNum());
//				}
			}
		}
		return null;
	}
 

/**
 * Gets the value from a non-formula cell.
 * @param cell may be <code>null</code>
 * @return {@link BlankEval} if cell is <code>null</code> or blank, never <code>null</code>
 */
/* package */ static ValueEval getValueFromNonFormulaCell(EvaluationCell cell) {
    if (cell == null) {
        return BlankEval.instance;
    }
    CellType cellType = cell.getCellTypeEnum();
    switch (cellType) {
        case NUMERIC:
            return new NumberEval(cell.getNumericCellValue());
        case STRING:
            return new StringEval(cell.getStringCellValue());
        case BOOLEAN:
            return BoolEval.valueOf(cell.getBooleanCellValue());
        case BLANK:
            return BlankEval.instance;
        case ERROR:
            return ErrorEval.valueOf(cell.getErrorCellValue());
        default:
            throw new RuntimeException("Unexpected cell type (" + cellType + ")");
    }
    
}
 

/**
 * Chooses a new boolean value for the cell when its type is changing.<p>
 *
 * Usually the caller is calling setCellType() with the intention of calling
 * setCellValue(boolean) straight afterwards.  This method only exists to give
 * the cell a somewhat reasonable value until the setCellValue() call (if at all).
 * TODO - perhaps a method like setCellTypeAndValue(int, Object) should be introduced to avoid this
 */
private boolean convertCellValueToBoolean() {

    switch (_cellType) {
        case BOOLEAN:
            return (( BoolErrRecord ) _record).getBooleanValue();
        case STRING:
            int sstIndex = ((LabelSSTRecord)_record).getSSTIndex();
            String text = _book.getWorkbook().getSSTString(sstIndex).getString();
            return Boolean.valueOf(text).booleanValue();
        case NUMERIC:
            return ((NumberRecord)_record).getValue() != 0;

        case FORMULA:
            // use cached formula result if it's the right type:
            FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
            checkFormulaCachedValueType(CellType.BOOLEAN, fr);
            return fr.getCachedBooleanValue();
        // Other cases convert to false
        // These choices are not well justified.
        case ERROR:
        case BLANK:
            return false;
    }
    throw new RuntimeException("Unexpected cell type (" + _cellType + ")");
}
 

/**
 * セルの書式をコピーする。
 * <p>コピー先のセルの種類は、空セルとする。</p>
 * <p>結合情報も列方向の結合をコピーする。</p>
 *
 * @since 2.0
 * @param fromCell コピー元
 * @param toCell コピー先
 */
private void copyCellStyle(final Cell fromCell, final Cell toCell) {

    final CellStyle style = fromCell.getCellStyle();
    toCell.setCellStyle(style);
    toCell.setCellType(CellType.BLANK);

    // 横方向に結合されている場合、結合情報のコピーする。（XlsArrayColumns用）
    final Sheet sheet = fromCell.getSheet();
    final CellRangeAddress mergedRegion = POIUtils.getMergedRegion(sheet, fromCell.getRowIndex(), fromCell.getColumnIndex());
    final int mergedSize = POIUtils.getColumnSize(mergedRegion);

    if(POIUtils.getColumnSize(mergedRegion) >= 2) {
        CellRangeAddress newMergedRegion = POIUtils.getMergedRegion(sheet, toCell.getRowIndex(), toCell.getColumnIndex());
        if(newMergedRegion != null) {
            // 既に結合している場合 - 通常はありえない。
            return;
        }

        newMergedRegion = POIUtils.mergeCells(sheet,
                mergedRegion.getFirstColumn(), toCell.getRowIndex(), mergedRegion.getLastColumn(), toCell.getRowIndex());

        // 結合先のセルの書式も設定する
        // 中間のセルの設定
        for(int i=1; i < mergedSize; i++) {
            Cell mergedFromCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, fromCell.getRowIndex());

            Cell mergedToCell = POIUtils.getCell(sheet, toCell.getColumnIndex()+i, toCell.getRowIndex());
            mergedToCell.setCellStyle(mergedFromCell.getCellStyle());
            mergedToCell.setCellType(CellType.BLANK);
        }

    }

}
 

@Test
public void setValueWithStringFormula_SetsAFormulaOnTheCell() {
    SpreadsheetCell cell = getCell();

    cell.setValue("=B1*C1");    // formula is any string starting with an equals (=) sign

    assertThat(cell.getPoiCell().getCellType())
            .isEqualTo(CellType.FORMULA);

    assertThat(cell.getPoiCell().getCellFormula())
            .isEqualTo("B1*C1");
}
 

@Test
public void extractUnhandledCellType() throws Exception {

    RowCellExtractor extractor = new RowCellExtractor(4, CellType.FORMULA);

    extractor.setRow(workbook.getSheet("sheet").getRow(0));
    thrown.expect(ExtractorException.class);
    thrown.expectMessage(new StringStartsWith("Unhandled cell type"));
    extractor.extract();
}
 

public void importValues(				
	CellType cellType,
	HSSFCellStyle cellStyle,
	Object cellValue,
	String formula
	) 
{
	this.importValues(cellType, cellStyle, cellValue, formula, null);
}
 

/**
* Note that this assumes the cell cached value is up to date and in sync with data edits
* @param cell
* @param type
* @return true if the cell or cached cell formula result type match the given type
*/
public static boolean isType(Cell cell, CellType type) {
    final CellType cellType = cell.getCellTypeEnum();
    return cellType == type 
          || (cellType == CellType.FORMULA 
              && cell.getCachedFormulaResultTypeEnum() == type
             );
}
 

@Override
protected void setupCell(final Cell cell, final Optional<Set> cellValue) throws TypeBindException {
    
    if(cellValue.isPresent()) {
        cell.setCellValue(textFormatter.format(cellValue.get()));
    } else {
        cell.setCellType(CellType.BLANK);
    }
    
}
 

public void setCellFormula(String formula) {
    if(isPartOfArrayFormulaGroup()){
        notifyArrayFormulaChanging();
    }

    int row=_record.getRow();
    short col=_record.getColumn();
    short styleIndex=_record.getXFIndex();

    if (formula==null) {
        notifyFormulaChanging();
        setCellType(CellType.BLANK, false, row, col, styleIndex);
        return;
    }
    int sheetIndex = _book.getSheetIndex(_sheet);
    Ptg[] ptgs = HSSFFormulaParser.parse(formula, _book, FormulaType.CELL, sheetIndex);
    setCellType(CellType.FORMULA, false, row, col, styleIndex);
    FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
    FormulaRecord frec = agg.getFormulaRecord();
    frec.setOptions((short) 2);
    frec.setValue(0);

    //only set to default if there is no extended format index already set
    if (agg.getXFIndex() == (short)0) {
        agg.setXFIndex((short) 0x0f);
    }
    agg.setParsedExpression(ptgs);
}
 

@Override
public SpreadsheetExporter addRow(String... values) {
    Row dataRow = dataSheet.createRow(rowCount++);
    for (int i = 0; i < values.length; i++) {
        Cell cell = dataRow.createCell(i);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(values[i]);
    }
    return this;
}
 

@Override
public List<Attribute> getTimedValueAttributes(String datasourceID) throws Exception {
    RowCellExtractor attributeNameExtractor = new RowCellExtractor(0, CellType.STRING);

    if (null == getWorkbook()) {
        setWorkbook(excelUtils.getWorkbook(
                downloadUtils.fetchInputStream(new URL(DATAFILE), getProvider().getLabel(), DATAFILE_SUFFIX)));
    }

    Map<String, Attribute> attributes = new HashMap<>();
    Sheet sheet = workbook.getSheetAt(3);
    Iterator<Row> rowIterator = sheet.iterator();
    Row header = rowIterator.next();
    while (rowIterator.hasNext()){
        Row row = rowIterator.next();

        attributeNameExtractor.setRow(row);
        String attributeLabel = attributeNameExtractor.extract();

        if (!attributes.containsKey(attributeLabel))
            attributes.put(
                    attributeLabel,
                    new Attribute(getProvider(), attributeLabel, attributeLabel)
            );
    }
    workbook.close();
    return new ArrayList<>(attributes.values());
}
 

/**
 * {@inheritDoc}
 */
@Override
public Cell getCell(int cellnum, MissingCellPolicy policy) {
  StreamingCell cell = (StreamingCell) cellMap.get(cellnum);
  if(policy == MissingCellPolicy.CREATE_NULL_AS_BLANK) {
    if(cell == null) { return new StreamingCell(sheet, cellnum, rowIndex, false); }
  } else if(policy == MissingCellPolicy.RETURN_BLANK_AS_NULL) {
    if(cell == null || cell.getCellType() == CellType.BLANK) { return null; }
  }
  return cell;
}
 

@Test
public void extractSillyValue() throws Exception {

    RowCellExtractor extractor = new RowCellExtractor(3, CellType.NUMERIC);

    extractor.setRow(workbook.getSheet("sheet").getRow(0));
    thrown.expect(BlankCellException.class);
    thrown.expectMessage(new StringStartsWith("Could not extract value"));
    extractor.extract();

    extractor.setRow(workbook.getSheet("sheet").getRow(1));
    assertEquals("7.0", extractor.extract());

}
 

private String createExcelContent(){
    Workbook workBook = new HSSFWorkbook();
    workBook.createSheet();
    Sheet sheet = workBook.getSheetAt(0);
    Row column = sheet.createRow(0);
    Row data1 = sheet.createRow(1);
    sheet.createRow(2);
    Row data2 = sheet.createRow(4);


    Field[] fields = TestClass.class.getDeclaredFields();
    for (int i = 0; i < fields.length; i++) {
        column.createCell(i, CellType.STRING).setCellValue(fields[i].getName());
    }

    column.getCell(5).setCellValue("test");

    data1.createCell(0, CellType.BOOLEAN).setCellValue(true);
    data2.createCell(0, CellType.STRING).setCellValue("True");
    data1.createCell(1, CellType.NUMERIC).setCellValue(2.3);
    data1.createCell(2, CellType.NUMERIC).setCellValue(3.3);
    data1.createCell(3, CellType.NUMERIC).setCellValue(4.3);
    data1.createCell(4, CellType.NUMERIC).setCellValue(5.3);
    data2.createCell(5, CellType.NUMERIC).setCellValue("a,b,c");

    ByteArrayOutputStream os = new ByteArrayOutputStream();
    try {
        workBook.write(os);
        byte[] bytes = os.toByteArray();
        os.close();
        return Base64.getEncoder().encodeToString(bytes);
    } catch (IOException e) {
        e.printStackTrace();
        return null;
    }
}
 

private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    cell.setCellType(CellType.STRING);
    if (cell.getStringCellValue() != null) {
	return cell.getStringCellValue().trim();
    }
}
return null;
   }
 

protected Cell getOrCreateDataCell(int colNum) {
	Cell cell = getDataRow().getCell(colNum);
	if (cell == null) {
		cell = getDataRow().createCell(colNum);
		cell.setCellType(CellType.NUMERIC);
	}
	return cell;
}
 

@Test
public void setValueWithShort_SetsANumericValueOnTheCell() {
    SpreadsheetCell cell = getCell();
    cell.setValue((short) 12);
    assertThat(cell.getPoiCell().getCellType())
            .isEqualTo(CellType.NUMERIC);
    
    assertThat(cell.getPoiCell().getNumericCellValue())
            .isEqualTo(12.0);
}
 

public Object getCellValueByEvaluatingFormula(String fileLocation, String cellLocation) throws IOException {
    Object cellValue = new Object();

    FileInputStream inputStream = new FileInputStream(new File(fileLocation));
    Workbook workbook = new XSSFWorkbook(inputStream);

    Sheet sheet = workbook.getSheetAt(0);
    FormulaEvaluator evaluator = workbook.getCreationHelper()
        .createFormulaEvaluator();

    CellAddress cellAddress = new CellAddress(cellLocation);
    Row row = sheet.getRow(cellAddress.getRow());
    Cell cell = row.getCell(cellAddress.getColumn());

    if (cell.getCellType() == CellType.FORMULA) {
        switch (evaluator.evaluateFormulaCell(cell)) {
            case BOOLEAN:
                cellValue = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                cellValue = cell.getNumericCellValue();
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            default:
                cellValue = null;
        }
    }

    workbook.close();
    return cellValue;
}
 

private boolean isRowEmpty(Row row) {
    for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
        Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        if (cell != null && cell.getCellType() != CellType.BLANK) {
            return false;
        }
    }
    return true;
}
 

/**
     * <p>
     * Returns the formatted value of a cell as a <tt>String</tt> regardless of the cell type. If the Excel number
     * format pattern cannot be parsed then the cell value will be formatted using a default format.
     * </p>
     * <p>
     * When passed a null or blank cell, this method will return an empty String (""). Formula cells will be evaluated
     * using the given {@link FormulaEvaluator} if the evaluator is non-null. If the evaluator is null, then the formula
     * String will be returned. The caller is responsible for setting the currentRow on the evaluator
     * </p>
     * <p>
     * When a ConditionalFormattingEvaluator is present, it is checked first to see if there is a number format to
     * apply. If multiple rules apply, the last one is used. If no ConditionalFormattingEvaluator is present, no rules
     * apply, or the applied rules do not define a format, the cell's style format is used.
     * </p>
     * <p>
     * The two evaluators should be from the same context, to avoid inconsistencies in cached values.
     * </p>
     *
     * @param cell
     *            The cell (can be null)
     * @param evaluator
     *            The FormulaEvaluator (can be null)
     * @param cfEvaluator
     *            ConditionalFormattingEvaluator (can be null)
     * @return a string value of the cell
     */
    public String formatCellValue(Cell cell, FormulaEvaluator evaluator, ConditionalFormattingEvaluator cfEvaluator) {
        localeChangedObservable.checkForLocaleChange();

        if (cell == null) {
            return "";
        }

        CellType cellType = cell.getCellTypeEnum();
        if (cellType == CellType.FORMULA) {
            if (evaluator == null) {
                return cell.getCellFormula();
            }
            cellType = evaluator.evaluateFormulaCellEnum(cell);
        }
        switch (cellType) {
            case NUMERIC:

//                if (DateUtil.isCellDateFormatted(cell, cfEvaluator)) {
                    return getFormattedDateString(cell, cfEvaluator);
//                }
//                return getFormattedNumberString(cell, cfEvaluator);

            case STRING:
                return cell.getRichStringCellValue().getString();

            case BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case BLANK:
                return "";
            case ERROR:
                return FormulaError.forInt(cell.getErrorCellValue()).getString();
            default:
                throw new RuntimeException("Unexpected celltype (" + cellType + ")");
        }
    }
 

@Override
protected void setupCell(final Cell cell, final Optional<String> cellValue) throws TypeBindException {
    
    if(cellValue.isPresent() && !cellValue.get().isEmpty()) {
        cell.setCellValue(cellValue.get());
        
    } else {
        cell.setCellType(CellType.BLANK);
    }
}