Java CellType.FORMULA属性代码示例

/**
 * 获取单元格的值
 * 
 * @param cell
 * @return
 */
public static Object getCellValue(Cell cell) {
	Object cellValue = null;
	CellType cellType = cell.getCellTypeEnum();// CellType.forInt(cell.getCellType());
	if (cellType == CellType.STRING) {
		cellValue = cell.getStringCellValue();
	} else if (cellType == CellType.NUMERIC) {
		if (DateUtil.isCellDateFormatted(cell)) {
			cellValue = cell.getDateCellValue();
		} else {
			cellValue = cell.getNumericCellValue();
		}
	} else if (cellType == CellType.BOOLEAN) {
		cellValue = cell.getBooleanCellValue();
	} else if (cellType == CellType.FORMULA) {
		cellValue = cell.getCellFormula();
	} else if (cellType == CellType.BLANK) {
		cellValue = "";
	}
	return cellValue;
}
 

/**
 * Return the cell type.
 *
 * @return the cell type
 * Will be renamed to <code>getCellType()</code> when we make the CellType enum transition in POI 4.0. See bug 59791.
 */
@Override
public CellType getCellTypeEnum() {
  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)) {
    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 + "'");
  }
}
 

@Test
public void testGetCellTypeAsStr() throws Exception {
	//
	ExpowCellLib cell = new ExpowCellLib(0, 0, CellType.STRING, "StringValue"); 
	assertEquals("CELL_TYPE_STRING", cell.getCellTypeAsStr());
	
	cell = new ExpowCellLib(0, 0, CellType.BLANK, ""); 
	assertEquals("CELL_TYPE_BLANK", cell.getCellTypeAsStr());

	cell = new ExpowCellLib(0, 0, CellType.BOOLEAN, "true"); 
	assertEquals("CELL_TYPE_BOOLEAN", cell.getCellTypeAsStr()); 

	cell = new ExpowCellLib(0, 0, CellType.ERROR, ""); 
	assertEquals("CELL_TYPE_ERROR", cell.getCellTypeAsStr());

	cell = new ExpowCellLib(0, 0, CellType.FORMULA, ""); 
	assertEquals("CELL_TYPE_FORMULA", cell.getCellTypeAsStr());
	
	cell = new ExpowCellLib(0, 0, CellType.NUMERIC, ""); 
	assertEquals("CELL_TYPE_NUMERIC", cell.getCellTypeAsStr());
}
 

/**
 * Not supported
 */
@Override
public CellType getCachedFormulaResultTypeEnum() {
  if (type != null && "str".equals(type)) {
    if(contentsSupplier.getContent() == null || cachedFormulaResultType == null) {
      return CellType.BLANK;
    } else if("n".equals(cachedFormulaResultType)) {
      return CellType.NUMERIC;
    } else if("s".equals(cachedFormulaResultType) || "inlineStr".equals(cachedFormulaResultType)) {
      return CellType.STRING;
    } else if("str".equals(cachedFormulaResultType)) {
      return CellType.FORMULA;
    } else if("b".equals(cachedFormulaResultType)) {
      return CellType.BOOLEAN;
    } else if("e".equals(cachedFormulaResultType)) {
      return CellType.ERROR;
    } else {
      throw new UnsupportedOperationException("Unsupported cell type '" + cachedFormulaResultType + "'");
    }
  }
  else  {
    throw new IllegalStateException("Only formula cells have cached results");
  }
}
 

@Override
public String asString() {
	if (excelCell == null) {
		// POI sometimes returns null when a cell is empty...
		return null;
	}
	if (excelCell.getCellTypeEnum() == CellType.NUMERIC
		|| excelCell.getCellTypeEnum() == CellType.FORMULA) {
		excelCell.setCellType(CellType.STRING);
	}
	return emptyToNullTrimmed(excelCell.getRichStringCellValue().getString(), trimValue);
}
 

private<T> T tryGetValue(Function<Double, T> cast) {
	if (excelCell != null) {
		if (excelCell.getCellTypeEnum() == CellType.FORMULA) {
			excelCell.setCellType(CellType.NUMERIC);
		}
		if(excelCell.getCellTypeEnum() == CellType.NUMERIC) {
			return cast.apply(excelCell.getNumericCellValue());
		}
	}
	return null;
}
 

private static String getCellString(Cell cell) {
	if (cell.getCellTypeEnum() == CellType.FORMULA) {
		try {
			return String.valueOf(cell.getNumericCellValue());
		} catch (Exception e) {
			return cell.getRichStringCellValue().toString(); 
		}
	} else {
		return cell.toString();
	} 
}
 

/**
 * Final formatting of the sheet upon completion of writing the data. For
 * example, we can only size the column widths once the data is in the
 * report and the sheet knows how wide the data is.
 */
protected void finalSheetFormat() {
    final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    if (isHierarchical()) {
        /*
         * evaluateInCell() is equivalent to paste special -> value. The formula refers to cells
         * in the other sheet we are going to delete. We sum in the other sheet because if we
         * summed in the main sheet, we would double count. Subtotal with hidden rows is not yet
         * implemented in POI.
         */
        for (final Row r : sheet) {
            for (final Cell c : r) {
                if (c.getCellTypeEnum() == CellType.FORMULA) {
                    evaluator.evaluateInCell(c);
                }
            }
        }
        workbook.setActiveSheet(workbook.getSheetIndex(sheet));
        if (hierarchicalTotalsSheet != null) {
            workbook.removeSheetAt(workbook.getSheetIndex(hierarchicalTotalsSheet));
        }
    } else {
        evaluator.evaluateAll();
    }
    for (int col = 0; col < getPropIds().size(); col++) {
        sheet.autoSizeColumn(col);
    }
}
 

/**
 * 复制单位格（空白行的复制，即只复制格式和固定文字，不填充数据）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-07-03
 * @version     v1.0
 *
 * @param i_RTemplate      模板对象
 * @param i_TemplateCell   模板中的单元格对象
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataCell       数据中的单元格对象
 * @param io_RSystemValue 系统变量信息
 * @param i_Datas          本行对应的数据
 * @param io_RValue        小计循环的迭代器
 * @return                 
 */
public final static void copyCellByBlankSpace(RTemplate i_RTemplate ,Cell i_TemplateCell ,RWorkbook i_DataWorkbook ,Cell i_DataCell ,RSystemValue io_RSystemValue)
{
    // 复制样式
    i_DataCell.setCellStyle(i_DataWorkbook.getCellStyle(i_RTemplate ,i_TemplateCell.getCellStyle().getIndex()));
    
    // 复制评论
    copyComment(i_RTemplate ,i_TemplateCell ,i_DataWorkbook ,i_DataCell);
    
    // 复制数据类型
    CellType v_CellType = i_TemplateCell.getCellTypeEnum();
    // i_DataCell.setCellType(v_CellType);  不能在此统一设置，原因是：下面代码对类型是有浮动的
    
    if ( v_CellType == CellType.NUMERIC ) 
    {
        i_DataCell.setCellType(v_CellType);
        
        if ( HSSFDateUtil.isCellDateFormatted(i_TemplateCell) ) 
        {
            i_DataCell.setCellValue(i_TemplateCell.getDateCellValue());
        } 
        else 
        {
            i_DataCell.setCellValue(i_TemplateCell.getNumericCellValue());
        }
    }
    else if ( v_CellType == CellType.STRING ) 
    {
        RichTextString v_TemplateRichText = i_TemplateCell.getRichStringCellValue();
        String         v_ValueName        = v_TemplateRichText.toString();
        
        if ( i_RTemplate.isExists(v_ValueName) )
        {
            i_DataCell.setCellType(v_CellType);
            i_DataCell.setCellValue("");
        }
        else 
        {
            i_DataCell.setCellType(v_CellType);
            copyRichTextStyle(i_RTemplate ,v_TemplateRichText ,i_DataWorkbook ,i_DataCell);
        }
    } 
    else if ( v_CellType == CellType.BOOLEAN ) 
    {
        i_DataCell.setCellType(v_CellType);
        i_DataCell.setCellValue(i_TemplateCell.getBooleanCellValue());
    } 
    else if ( v_CellType == CellType.FORMULA) 
    {
        i_DataCell.setCellType(v_CellType);
        i_DataCell.setCellFormula(i_TemplateCell.getCellFormula());
    } 
    else 
    {
        // Nothing.
        i_DataCell.setCellType(v_CellType);
    }
}
 

@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();
}
 

@Test
public void extractNonExistingColumn() throws Exception {

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

    extractor.setRow(workbook.getSheet("sheet").getRow(1));
    thrown.expect(ExtractorException.class);
    thrown.expectMessage(new StringStartsWith("Column with index 4 does not exit"));
    extractor.extract();
}
 

public static boolean canBeReadAsNumericCell (@Nullable final Cell aCell)
{
  if (aCell == null)
    return false;
  final CellType eType = aCell.getCellTypeEnum ();
  return eType == CellType.BLANK || eType == CellType.NUMERIC || eType == CellType.FORMULA;
}
 

private void loadWorkbook(Workbook workbook) {
	String cellValue = null;
	for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
		Sheet sheet = workbook.getSheetAt(i);
		System.out.println("sheet: " + workbook.getSheetName(i));
		// 有效值
		List<? extends DataValidation> dataValidations = sheet.getDataValidations();
		if (dataValidations != null && dataValidations.size() > 0) {
			System.out.println("data validations: ");
			for (DataValidation validation : dataValidations) {
				System.out.println(validation.toString());
			}
		}
		// 表格行
		for (Row row : sheet) {
			// 行单元格
			for (Cell cell : row) {
				cellValue = "";
				CellType cellType = cell.getCellTypeEnum();// CellType.forInt(cell.getCellType());
				if (cellType == CellType.STRING) {
					cellValue = cell.getRichStringCellValue().getString();
				} else if (cellType == CellType.NUMERIC) {
					if (DateUtil.isCellDateFormatted(cell)) {
						cellValue = String.valueOf(cell.getDateCellValue());
					} else {
						cellValue = String.valueOf(cell.getNumericCellValue());
					}
				} else if (cellType == CellType.BOOLEAN) {
					cellValue = String.valueOf(cell.getBooleanCellValue());
				} else if (cellType == CellType.FORMULA) {
					cellValue = String.valueOf(cell.getCellFormula());
				} else if (cellType == CellType.BLANK) {
					cellValue = String.valueOf(" ");
				}
				System.out.print(cellValue);
				System.out.print("  ");
			}
			System.out.println("");
		}
	}
}
 

@Override
public Object[] getNext() {
	SpreadSheetCellDAO[] result=null;
	// all sheets?
	if (this.sheets==null) { //  go on with all sheets
		if (!nextAllSheets()) {
			return result;
		}
	} else { // go on with specified sheets
		if (!nextSpecificSheets()) {
			return result;
		}
	}
	// read row from the sheet currently to be processed
	Sheet rSheet = this.currentWorkbook.getSheetAt(this.currentSheet);
	Row rRow = rSheet.getRow(this.currentRow);
	if (rRow==null) {
		this.currentRow++;
		return new SpreadSheetCellDAO[0]; // emtpy row
	}
	result = new SpreadSheetCellDAO[rRow.getLastCellNum()];
	for (int i=0;i<rRow.getLastCellNum();i++) {
		Cell currentCell=rRow.getCell(i);
		if (currentCell==null) {
			result[i]=null;
		} else {	
			String formattedValue=useDataFormatter.formatCellValue(currentCell,this.formulaEvaluator);
			String formula = "";
			if (currentCell.getCellTypeEnum()==CellType.FORMULA)  {
				formula = currentCell.getCellFormula();
			}
			Comment currentCellComment = currentCell.getCellComment();
			String comment = "";
			if (currentCellComment!=null) {
				comment = currentCellComment.getString().getString();
			}
			String address = currentCell.getAddress().toString();
			String sheetName = currentCell.getSheet().getSheetName();
			SpreadSheetCellDAO mySpreadSheetCellDAO = new SpreadSheetCellDAO(formattedValue,comment,formula,address,sheetName);
			
			result[i]=mySpreadSheetCellDAO;
		}
	}
	
	// increase rows
	this.currentRow++;
	return result;
}