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

The following examples show how to use org.apache.poi.ss.usermodel.CellType. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: vividus   Source File: CellUtils.java    License: Apache License 2.0 6 votes vote down vote up
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;
    }
}
 
Example 2
Source Project: tools   Source File: OriginsSheetV1d1.java    License: Apache License 2.0 6 votes vote down vote up
@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;
	}
 
Example 3
Source Project: tools   Source File: OriginsSheetV1d2.java    License: Apache License 2.0 6 votes vote down vote up
@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;
	}
 
Example 4
Source Project: lams   Source File: HSSFCell.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * 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);
}
 
Example 5
/** 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();
}
 
Example 6
Source Project: xlsmapper   Source File: CellLinkCellConverterFactory.java    License: Apache License 2.0 6 votes vote down vote up
@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);
    }
    
}
 
Example 7
Source Project: lams   Source File: WorkbookEvaluator.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * 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 + ")");
    }
    
}
 
Example 8
Source Project: xlsmapper   Source File: BooleanCellConverterFactory.java    License: Apache License 2.0 6 votes vote down vote up
@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;
}
 
Example 9
Source Project: lams   Source File: HSSFRow.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * 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);
    }
}
 
Example 10
Source Project: lams   Source File: HSSFCell.java    License: GNU General Public License v2.0 6 votes vote down vote up
/**
 * 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() + ")");
}
 
Example 11
Source Project: lams   Source File: HSSFCell.java    License: GNU General Public License v2.0 5 votes vote down vote up
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);
}
 
Example 12
Source Project: datacollector   Source File: Cells.java    License: Apache License 2.0 5 votes vote down vote up
static Field parseCell(Cell cell, FormulaEvaluator evaluator) throws ExcelUnsupportedCellTypeException {
  CellType cellType = cell.getCellTypeEnum();
  // set the cellType of a formula cell to its cached formula result type in order to process it as its result type
  boolean isFormula = cell.getCellTypeEnum().equals(CellType.FORMULA);
  if (isFormula) {
    cellType = cell.getCachedFormulaResultTypeEnum();
  }

  switch (cellType) {
    case STRING:
      return Field.create(cell.getStringCellValue());
    case NUMERIC:
      Double rawValue = cell.getNumericCellValue();  // resolves formulas automatically and gets value without cell formatting
      String displayValue = isFormula ? evaluator.evaluate(cell).formatAsString() : dataFormatter.formatCellValue(cell);
      boolean numericallyEquivalent = false;
      try {
        numericallyEquivalent = Double.parseDouble(displayValue) == rawValue;
      } catch (NumberFormatException e) { }

      if (DateUtil.isCellDateFormatted(cell)) {
        // It's a date, not a number
        java.util.Date dt = cell.getDateCellValue();
        // if raw number is < 1 then it's a time component only, otherwise date.
        return rawValue < 1 ? Field.createTime(dt) : Field.createDate(dt);
      }

      // some machinations to handle integer values going in without decimal vs. with .0 for rawValue
      return Field.create(numericallyEquivalent ? new BigDecimal(displayValue) : BigDecimal.valueOf(rawValue));

    case BOOLEAN:
      return Field.create(cell.getBooleanCellValue());
    case BLANK:
      return Field.create("");
    default:
      throw new ExcelUnsupportedCellTypeException(cell, cellType);
  }
}
 
Example 13
/** Write sheet column headers */
public void writeAttributeHeaders(
    Iterable<Attribute> attributes, AttributeWriteMode attributeWriteMode) {
  if (attributes == null) throw new IllegalArgumentException("Attributes cannot be null");
  if (attributeWriteMode == null)
    throw new IllegalArgumentException("AttributeWriteMode cannot be null");

  if (cachedAttributes == null) {
    Row poiRow = sheet.createRow(row++);

    // write header
    int i = 0;
    for (Attribute attribute : attributes) {
      Cell cell = poiRow.createCell(i++, CellType.STRING);

      switch (attributeWriteMode) {
        case ATTRIBUTE_LABELS:
          cell.setCellValue(
              AbstractCellProcessor.processCell(attribute.getLabel(), true, cellProcessors));
          break;
        case ATTRIBUTE_NAMES:
          cell.setCellValue(
              AbstractCellProcessor.processCell(attribute.getName(), true, cellProcessors));
          break;
        default:
          throw new UnexpectedEnumException(attributeWriteMode);
      }
    }

    // store header
    this.cachedAttributes = attributes;
  }
}
 
Example 14
Source Project: vividus   Source File: CellUtilsTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
void testNumericCell()
{
    Mockito.when(cell.getCellType()).thenReturn(CellType.NUMERIC);
    Mockito.when(cell.getNumericCellValue()).thenReturn((double) 3);
    assertEquals("3.0", CellUtils.getCellValueAsString(cell));
}
 
Example 15
Source Project: vividus   Source File: CellUtilsTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
void testFormulaCell()
{
    Mockito.when(cell.getCellType()).thenReturn(CellType.FORMULA);
    Mockito.when(cell.getCachedFormulaResultType()).thenReturn(CellType.STRING);
    Mockito.when(cell.getStringCellValue()).thenReturn(TEST);

    assertEquals(TEST, CellUtils.getCellValueAsString(cell));
}
 
Example 16
Source Project: vividus   Source File: CellUtilsTests.java    License: Apache License 2.0 5 votes vote down vote up
@Test
void testStringCell()
{
    Mockito.when(cell.getCellType()).thenReturn(CellType.STRING);
    Mockito.when(cell.getStringCellValue()).thenReturn(TEST);
    assertEquals(TEST, CellUtils.getCellValueAsString(cell));
}
 
Example 17
Source Project: lams   Source File: ImportService.java    License: GNU General Public License v2.0 5 votes vote down vote up
private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    try {
	cell.setCellType(CellType.STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return null;
	}
	// log.debug("string cell value: '"+cell.getStringCellValue().trim()+"'");
	return cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(CellType.NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	// log.debug("numeric cell value: '"+d+"'");
	return (new Long(new Double(d).longValue()).toString());
    }
}
return null;
   }
 
Example 18
Source Project: taro   Source File: SpreadsheetCellTest.java    License: MIT License 5 votes vote down vote up
@Test
public void setValueWithString_SetsAStringValueOnTheCell() {
    SpreadsheetCell cell = getCell();
    cell.setValue("A String");

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

    assertThat(cell.getPoiCell().getStringCellValue())
            .isEqualTo("A String");
}
 
Example 19
Source Project: java-master   Source File: CellUtils.java    License: Apache License 2.0 5 votes vote down vote up
public static void fillFuncCell(Cell cell, String funcStr, CellStyle style) {
    cell.setCellType(CellType.NUMERIC);
    cell.setCellFormula(funcStr);
    if (funcStr == null) {
        return;
    }
    cell.setCellStyle(style);
}
 
Example 20
Source Project: java-master   Source File: CellUtils.java    License: Apache License 2.0 5 votes vote down vote up
private static void fillNumberCell(Cell cell, Number number, CellStyle style) {
    cell.setCellType(CellType.NUMERIC);
    cell.setCellStyle(style);
    if (number == null) {
        return;
    }
    cell.setCellValue(number.doubleValue());
}
 
Example 21
Source Project: xlsmapper   Source File: CharacterCellConverterFactory.java    License: Apache License 2.0 5 votes vote down vote up
@Override
protected void setupCell(final Cell cell, final Optional<Character> cellValue) throws TypeBindException {
    
    // \u0000 は、初期値として空と判定する。
    if(cellValue.isPresent() && cellValue.get() != DEFAULT_VALUE) {
        cell.setCellValue(cellValue.get().toString());
        
    } else {
        cell.setCellType(CellType.BLANK);
    }
}
 
Example 22
Source Project: TomboloDigitalConnector   Source File: AdultObesityImporter.java    License: MIT License 5 votes vote down vote up
@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    // Choose the apppropriate workbook sheet
    Workbook workbook = excelUtils.getWorkbook(
            downloadUtils.fetchInputStream(new URL(DATASOURCE), getProvider().getLabel(), DATASOURCE_SUFFIX));
    Sheet sheet = workbook.getSheet("Active People Survey");
    String year = "2013";

    List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();
    RowCellExtractor subjectExtractor = new RowCellExtractor(0, CellType.STRING);
    ConstantExtractor timestampExtractor = new ConstantExtractor(year);

    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER,
            OaImporter.OaType.localAuthority.name(), OaImporter.OaType.localAuthority.datasourceSpec.getDescription());
    for (AttributeLabel attributeLabel : AttributeLabel.values()){
        ConstantExtractor attributeExtractor = new ConstantExtractor(attributeLabel.name());
        RowCellExtractor valueExtractor
                = new RowCellExtractor(getAttributeColumnId(attributeLabel), CellType.NUMERIC);
        timedValueExtractors.add(new TimedValueExtractor(
                getProvider(),
                subjectType,
                subjectExtractor,
                attributeExtractor,
                timestampExtractor,
                valueExtractor));
    }
    excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
}
 
Example 23
Source Project: CheckPoint   Source File: PoiWorkSheet.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Create title cell cell.
 *
 * @param str   the str
 * @param width the width
 * @return the cell
 */
public Cell createTitleCell(String str, double width) {

    int cellCnt = this.getCellCnt();

    Cell cell = this.getLastRow().createCell(cellCnt);
    cell.setCellValue(str);
    cell.setCellType(CellType.STRING);
    cell.setCellStyle(this.style.getStringCs());

    sheet.setColumnWidth(cellCnt, (int) (sheet.getColumnWidth(cellCnt) * width));

    return cell;
}
 
Example 24
Source Project: lams   Source File: HSSFCell.java    License: GNU General Public License v2.0 5 votes vote down vote up
/**
 * Only valid for formula cells
 * @return one of ({@link CellType#NUMERIC}, {@link CellType#STRING},
 *     {@link CellType#BOOLEAN}, {@link CellType#ERROR}) depending
 * on the cached value of the formula
 * @since POI 3.15 beta 3
 * Will be deleted when we make the CellType enum transition. See bug 59791.
 */
@Override
public CellType getCachedFormulaResultTypeEnum() {
    if (_cellType != CellType.FORMULA) {
        throw new IllegalStateException("Only formula cells have cached results");
    }
    int code = ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
    return CellType.forInt(code);
}
 
Example 25
Source Project: CheckPoint   Source File: PoiWorkSheet.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Create cell cell.
 *
 * @param value the value
 * @return the cell
 */
public Cell createCell(double value) {

    Cell cell = this.getNextCell(CellType.NUMERIC);
    cell.setCellValue(value);
    cell.setCellStyle(this.style.getNumberCs());
    return cell;
}
 
Example 26
Source Project: excel-streaming-reader   Source File: StreamingCell.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Used to help format error messages
 */
private static String getCellTypeName(CellType cellType) {
  switch (cellType) {
    case BLANK:   return "blank";
    case STRING:  return "text";
    case BOOLEAN: return "boolean";
    case ERROR:   return "error";
    case NUMERIC: return "numeric";
    case FORMULA: return "formula";
  }
  return "#unknown cell type (" + cellType + ")#";
}
 
Example 27
Source Project: CheckPoint   Source File: PoiWorkSheet.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Create cell cell.
 *
 * @param value the value
 * @return the cell
 */
public Cell createCell(long value) {
    Cell cell = this.getNextCell(CellType.NUMERIC);
    cell.setCellValue(value);
    cell.setCellStyle(this.style.getNumberCs());
    return cell;
}
 
Example 28
Source Project: CheckPoint   Source File: PoiWorkSheet.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Create cell cell.
 *
 * @param obj the obj
 * @return the cell
 */
public Cell createCell(Object obj) {

    Cell cell = this.getNextCell(CellType.STRING);
    cell.setCellValue(String.valueOf(obj));
    cell.setCellStyle(this.style.getStringCs());
    return cell;
}
 
Example 29
Source Project: CheckPoint   Source File: PoiWorkSheet.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Create cell cell.
 *
 * @param date the date
 * @return the cell
 */
public Cell createCell(Date date) {

    Cell cell = this.getNextCell(CellType.STRING);
    if (date != null) {
        cell.setCellValue(date);
    }
    cell.setCellStyle(this.style.getDateCs());
    return cell;
}
 
Example 30
Source Project: easyexcel   Source File: DataFormatter1.java    License: Apache License 2.0 5 votes vote down vote up
/**
     * <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 + ")");
        }
    }