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

/**
 * 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
 */
@Override
public CellType getCachedFormulaResultType() {
  if (formulaType) {
    if(contentsSupplier.getContent() == null || type == null) {
      return CellType.BLANK;
    } else if("n".equals(type)) {
      return CellType.NUMERIC;
    } else if("s".equals(type) || "inlineStr".equals(type) || "str".equals(type)) {
      return CellType.STRING;
    } else if("b".equals(type)) {
      return CellType.BOOLEAN;
    } else if("e".equals(type)) {
      return CellType.ERROR;
    } else {
      throw new UnsupportedOperationException("Unsupported cell type '" + type + "'");
    }
  } else  {
    throw new IllegalStateException("Only formula cells have cached results");
  }
}
 

/**
 * 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() + ")");
}
 

@Override
public String extract() throws ExtractorException {
    if (row == null)
        throw new BlankCellException("Empty row");
    if (row.getCell(columnId) == null)
        throw new ExtractorException("Column with index "+columnId+" does not exit");
    if (row.getCell(columnId).getCellTypeEnum() == CellType.BLANK)
        throw new BlankCellException("Empty cell value");
    try{
        switch (cellType) {
            case BOOLEAN:
                return String.valueOf(row.getCell(columnId).getBooleanCellValue());
            case NUMERIC:
                return String.valueOf(row.getCell(columnId).getNumericCellValue());
            case STRING:
                return String.valueOf(row.getCell(columnId).getStringCellValue());
            default:
                throw new ExtractorException("Unhandled cell type: "+cellType);
        }
    }catch (IllegalStateException e){
        // Most likely trying to read a non-numeric value like '--'
        // Hence we treat this as a blank cell
        throw new BlankCellException("Could not extract value", e);
    }
}
 

public static boolean isEmptyRow(Row row) {
  if (row == null) {
      return true;
  }
  
  if (row.getLastCellNum() < 1) {
      return true;
  }
  
  for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
      Cell cell = row.getCell(cellNum);
      if (cell != null && cell.getCellTypeEnum() != CellType.BLANK && StringUtils.isNotBlank(cell.toString())) {
          return false;
      }
  }
  return true;
}
 

/**
 * Return the cell type.
 *
 * @return the cell type
 */
@Override
public CellType getCellType() {
  if(formulaType) {
    return CellType.FORMULA;
  } else if(contentsSupplier.getContent() == null || type == null) {
    return CellType.BLANK;
  } else if("n".equals(type)) {
    return CellType.NUMERIC;
  } else if("s".equals(type) || "inlineStr".equals(type) || "str".equals(type)) {
    return CellType.STRING;
  } else if("str".equals(type)) {
    return CellType.FORMULA;
  } else if("b".equals(type)) {
    return CellType.BOOLEAN;
  } else if("e".equals(type)) {
    return CellType.ERROR;
  } else {
    throw new UnsupportedOperationException("Unsupported cell type '" + type + "'");
  }
}
 

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;
}
 

public void setValue(ValueEval value) {
	Class<? extends ValueEval> cls = value.getClass();

	if (cls == NumberEval.class) {
		_cellType = CellType.NUMERIC;
		_numberValue = ((NumberEval)value).getNumberValue();
		return;
	}
	if (cls == StringEval.class) {
		_cellType = CellType.STRING;
		_stringValue = ((StringEval)value).getStringValue();
		return;
	}
	if (cls == BoolEval.class) {
		_cellType = CellType.BOOLEAN;
		_booleanValue = ((BoolEval)value).getBooleanValue();
		return;
	}
	if (cls == ErrorEval.class) {
		_cellType = CellType.ERROR;
		_errorValue = ((ErrorEval)value).getErrorCode();
		return;
	}
	if (cls == BlankEval.class) {
		_cellType = CellType.BLANK;
		return;
	}
	throw new IllegalArgumentException("Unexpected value class (" + cls.getName() + ")");
}
 

/**
 * Get the value of the cell as a date.
 * For strings we throw an exception.
 * For blank cells we return a null.
 * See {@link HSSFDataFormatter} for formatting
 *  this date into a string similar to how excel does.
 */
public Date getDateCellValue() {

    if (_cellType == CellType.BLANK) {
        return null;
    }
    double value = getNumericCellValue();
    if (_book.getWorkbook().isUsing1904DateWindowing()) {
        return HSSFDateUtil.getJavaDate(value, true);
    }
    return HSSFDateUtil.getJavaDate(value, false);
}
 

/**
 * {@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;
}
 

private boolean isEmpty(Cell cell) {
    return (cell.getCellTypeEnum() == CellType.BLANK);
}
 

private void processSpreadsheetUpload(Workbook wb, List<Map<QName,String>> users)
    throws IOException
{
    if (wb.getNumberOfSheets() > 1)
    {
        logger.info("Uploaded Excel file has " + wb.getNumberOfSheets() + 
                " sheets, ignoring  all except the first one"); 
    }
    
    int firstRow = 0;
    Sheet s = wb.getSheetAt(0);
    DataFormatter df = new DataFormatter();
    
    String[][] data = new String[s.getLastRowNum()+1][];
                                 
    // If there is a heading freezepane row, skip it
    PaneInformation pane = s.getPaneInformation();
    if (pane != null && pane.isFreezePane() && pane.getHorizontalSplitTopRow() > 0)
    {
        firstRow = pane.getHorizontalSplitTopRow();
        logger.debug("Skipping excel freeze header of " + firstRow + " rows");
    }
    
    // Process each row in turn, getting columns up to our limit
    for (int row=firstRow; row <= s.getLastRowNum(); row++)
    {
        Row r = s.getRow(row);
        if (r != null)
        {
            String[] d = new String[COLUMNS.length];
            for (int cn=0; cn<COLUMNS.length; cn++)
            {
                Cell cell = r.getCell(cn);
                if (cell != null && cell.getCellType() != CellType.BLANK)
                {
                    d[cn] = df.formatCellValue(cell);
                }
            }
            data[row] = d;
        }
    }
    
    // Handle the contents
    processSpreadsheetUpload(data, users);
}
 

public CellSettings(HSSFCellStyle cellStyle) 
{
	this(CellType.BLANK, cellStyle, null);
}
 

private static void processFormulaColumn(final Workbook excelDoc,
                                         final Sheet worksheet,
                                         final int firstRow,
                                         final int columnIndex) {

    final FormulaEvaluator evaluator = excelDoc.getCreationHelper().createFormulaEvaluator();
    for (int i = firstRow; i <= worksheet.getLastRowNum(); i++) {
        final Row row = worksheet.getRow(i);
        if (row != null) {
            final Cell cell = row.getCell(columnIndex);
            if (cell != null && (cell.getCellType() != CellType.BLANK)) {
                //formula type
                if (cell.getCellType() == CellType.FORMULA) {
                    CellValue cellValue = evaluator.evaluate(cell);

                    switch (cellValue.getCellType()) {
                        case BOOLEAN:
                            cell.setCellType(CellType.STRING);
                            break;
                        case NUMERIC:
                            cell.setCellType(CellType.NUMERIC);
                            break;
                        case STRING:
                            if (StringUtils.isBlank(cell.getStringCellValue())) {
                                cell.setCellType(CellType.BLANK);
                            } else {
                                cell.setCellType(CellType.STRING);
                            }
                            break;
                        case BLANK:

                            break;
                        case ERROR:
                            break;

                        // CELL_TYPE_FORMULA will never happen
                        case FORMULA:
                            break;
                    }
                }
            }
        }
    }
}
 

public CellType getCellType(int col, int row) {
    Cell cell = getCell(col, row);
    return cell != null ? cell.getCellType() : CellType.BLANK;
}