Java 类org.apache.poi.ss.usermodel.CellValue 实例源码
项目:tecsinapse-data-io
文件:ImporterUtils.java
public static Object getValueOrEmptyAsObject(FormulaEvaluator evaluator, Cell cell) {
final CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
return "";
}
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return Boolean.valueOf(cellValue.getBooleanValue());
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}
BigDecimal bd = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(DECIMAL_PRECISION, BigDecimal.ROUND_HALF_UP);
return bd.stripTrailingZeros();
case Cell.CELL_TYPE_STRING:
return cellValue.getStringValue();
case Cell.CELL_TYPE_ERROR:
return "ERRO";
default:
return "";
}
}
项目:step
文件:WorkbookSetTest.java
@Test
public void testExternRef() {
File file = getResourceFile("Excel1.xlsx");
WorkbookSet set = new WorkbookSet(file, null, new LinkedWorkbookFileResolver() {
@Override
public File resolve(String linkedFilename) {
return getResourceFile(linkedFilename.substring(linkedFilename.lastIndexOf("/")+1));
}
}, false, false);
Sheet s = set.getMainWorkbook().getSheetAt(0);
Cell c = s.getRow(1).getCell(0);
CellValue v = set.getMainFormulaEvaluator().evaluate(c);
Assert.assertEquals("Value", v.getStringValue());
}
项目:paxml
文件:ExcelFile.java
public Object getCellValue(Cell cell) {
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return cellValue.getBooleanValue();
case Cell.CELL_TYPE_NUMERIC:
return cellValue.getNumberValue();
case Cell.CELL_TYPE_STRING:
return cellValue.getStringValue();
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_ERROR:
return cellValue.getError(cell.getErrorCellValue()).getStringValue();
// CELL_TYPE_FORMULA will never happen
case Cell.CELL_TYPE_FORMULA:
throw new PaxmlRuntimeException("Internal error: invalid case");
default:
return null;
}
}
项目:onetwo
文件:WorkbookReaderFactory.java
@Override
public Long doConvert(Cell cell) {
int type = cell.getCellType();
Long value = null;
if(Cell.CELL_TYPE_STRING==type){
value = ((Number)Double.parseDouble(getStringValue(cell))).longValue();
}else if(Cell.CELL_TYPE_NUMERIC==type){
Double dvalue = cell.getNumericCellValue();
value = dvalue.longValue();
}else if(Cell.CELL_TYPE_FORMULA==type){
CellValue cv = ExcelUtils.getFormulaCellValue(cell);
value = cv==null?defaultValue:(long)cv.getNumberValue();
}else{
String strValue = getAsString(cell);
if(StringUtils.isBlank(strValue))
return defaultValue;
value = Long.parseLong(strValue);
}
return value;
}
项目:onetwo
文件:WorkbookReaderFactory.java
@Override
public Double doConvert(Cell cell) {
int type = cell.getCellType();
Double value = null;
if(Cell.CELL_TYPE_STRING==type){
value = Double.parseDouble(getStringValue(cell));
}else if(Cell.CELL_TYPE_NUMERIC==type){
value = cell.getNumericCellValue();
}else if(Cell.CELL_TYPE_FORMULA==type){
CellValue cv = ExcelUtils.getFormulaCellValue(cell);
value = cv==null?defaultValue:cv.getNumberValue();
}else{
String strValue = getAsString(cell);
if(StringUtils.isBlank(strValue))
return defaultValue;
value = Double.parseDouble(strValue);
}
return value;
}
项目:onetwo
文件:WorkbookReaderFactory.java
@Override
public String doConvert(Cell cell) {
int type = cell.getCellType();
String value = null;
if(Cell.CELL_TYPE_STRING==type){
value = getStringValue(cell);
}else if(Cell.CELL_TYPE_NUMERIC==type){
Double dvalue = cell.getNumericCellValue();
if(dvalue!=null){
value = String.valueOf(dvalue.longValue());
}
}else if(Cell.CELL_TYPE_FORMULA==type){
CellValue cv = ExcelUtils.getFormulaCellValue(cell);
value = cv==null?defaultValue:cv.getStringValue();
}else if(Cell.CELL_TYPE_BOOLEAN==type){
boolean bvalue = cell.getBooleanCellValue();
value = String.valueOf(bvalue);
}else if(Cell.CELL_TYPE_BLANK==type){
value = "";
}
return value;
}
项目:onetwo
文件:WorkbookReaderFactory.java
@Override
public Date doConvert(Cell cell) {
int type = cell.getCellType();
Date value = null;
if(Cell.CELL_TYPE_STRING==type){
value = TheFunction.getInstance().parseDateTime(getStringValue(cell));
}else if(Cell.CELL_TYPE_NUMERIC==type){
value = cell.getDateCellValue();
}else if(Cell.CELL_TYPE_FORMULA==type){
CellValue cv = ExcelUtils.getFormulaCellValue(cell);
value = cv==null?defaultValue:TheFunction.getInstance().parseDateTime(cv.getStringValue());//Types.convertValue(cv.getStringValue(), Date.class);
}else {
String strValue = getAsString(cell);
if(StringUtils.isBlank(strValue))
return defaultValue;
value = TheFunction.getInstance().parseDateTime(strValue);
}
return value;
}
项目:data-prep
文件:XlsUtils.java
/**
*
* @param cell
* @param cellValue
* @return internal method which switch on the formula result value type then return a String value
*/
private static String getCellValueAsString(Cell cell, CellValue cellValue) {
if (cellValue == null) {
return StringUtils.EMPTY;
}
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BLANK:
return "";
case Cell.CELL_TYPE_BOOLEAN:
return cellValue.getBooleanValue() ? Boolean.TRUE.toString() : Boolean.FALSE.toString();
case Cell.CELL_TYPE_ERROR:
return "Cell Error type";
case Cell.CELL_TYPE_NUMERIC:
return getNumericValue(cell, cellValue, cellValue != null);
case Cell.CELL_TYPE_STRING:
return StringUtils.trim(cell.getStringCellValue());
default:
return "Unknown Cell Type: " + cell.getCellType();
}
}
项目:data-prep
文件:XlsUtils.java
/**
* Return the numeric value.
*
* @param cell the cell to extract the value from.
* @return the numeric value from the cell.
*/
private static String getNumericValue(Cell cell, CellValue cellValue, boolean fromFormula) {
// Date is typed as numeric
if (HSSFDateUtil.isCellDateFormatted(cell)) { // TODO configurable??
DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
return sdf.format(cell.getDateCellValue());
}
// Numeric type (use data formatter to get number format right)
DataFormatter formatter = new HSSFDataFormatter(Locale.ENGLISH);
if (cellValue == null) {
return formatter.formatCellValue(cell);
}
return fromFormula ? cellValue.formatAsString() : formatter.formatCellValue(cell);
}
项目:BfROpenLab
文件:XlsReader.java
private String getData(Cell cell) {
if (cell == null) {
return null;
}
if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
CellValue value = wb.getCreationHelper().createFormulaEvaluator().evaluate(cell);
switch (value.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(value.getBooleanValue());
case Cell.CELL_TYPE_NUMERIC:
return String.valueOf(value.getNumberValue());
case Cell.CELL_TYPE_STRING:
return Strings.emptyToNull(Strings.nullToEmpty(value.getStringValue()).trim());
default:
return null;
}
} else {
return Strings.emptyToNull(cell.toString().trim());
}
}
项目:aorra
文件:SpreadsheetDataSource.java
private boolean updatePrecalculatedCellValue(Cell destination, CellValue val) {
if(val != null) {
switch(val.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return updatePrecalculatedBoolean(destination, val.getBooleanValue());
case Cell.CELL_TYPE_NUMERIC:
return updatePrecalculatedNumeric(destination, val.getNumberValue());
case Cell.CELL_TYPE_STRING:
return updatePrecalculatedString(destination, val.getStringValue());
case Cell.CELL_TYPE_BLANK:
return updatePrecalculatedBlank(destination);
case Cell.CELL_TYPE_ERROR:
return updatePrecalculatedError(destination,
FormulaError.forInt(val.getErrorValue()));
default: return false;
}
} else {
return updatePrecalculatedError(destination, FormulaError.REF);
}
}
项目:jexunit
文件:ExcelLoader.java
/**
* Evaluate the formula of the given cell.
*
* @param workbook
* workbook (excel) for evaluating the cell formula
* @param cell
* cell (excel)
*
* @return the value of the excel-call as string (the formula will be executed)
*/
static String evaluateCellFormula(XSSFWorkbook workbook, XSSFCell cell) {
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(cellValue.getBooleanValue());
case Cell.CELL_TYPE_NUMERIC:
return String.valueOf(cellValue.getNumberValue());
case Cell.CELL_TYPE_STRING:
return cellValue.getStringValue();
default:
return null;
}
}
项目:modern.core.java.repo
文件:ExcelXlsReaderParameter.java
private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
FormulaEvaluator evaluator = workbook.getCreationHelper()
.createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);
Object result = null;
if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
result = cellValue.getBooleanValue();
} else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
result = cellValue.getNumberValue();
} else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
result = cellValue.getStringValue();
}
return result;
}
项目:judge
文件:ExcelUtil.java
private static JsonElement parseAsJsonElement(Cell cell, FormulaEvaluator evaluator) {
switch (cell.getCellTypeEnum()) {
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return new JsonPrimitive(DateFormatterHolder.formatter.format(cell.getDateCellValue().toInstant()));
} else {
return new JsonPrimitive(cell.getNumericCellValue());
}
case STRING:
return new JsonPrimitive(cell.getStringCellValue());
case FORMULA:
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellTypeEnum()) {
case NUMERIC:
return new JsonPrimitive(cellValue.getNumberValue());
case STRING:
return new JsonPrimitive(cellValue.getStringValue());
case BLANK:
return new JsonPrimitive("");
case BOOLEAN:
return new JsonPrimitive(cellValue.getBooleanValue());
case ERROR:
default:
return null;
}
case BLANK:
return new JsonPrimitive("");
case BOOLEAN:
return new JsonPrimitive(cell.getBooleanCellValue());
case ERROR:
default:
return null;
}
}
项目:owsi-core-parent
文件:WorkbookUtils.java
private static Object getCellValueFromFormula(FormulaEvaluator formulaEvaluator, Cell cell) {
try {
CellValue cellValue = formulaEvaluator.evaluate(cell);
if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(cell)) {
Calendar calendar = GregorianCalendar.getInstance();
calendar.setTime(DateUtil.getJavaDate(cellValue.getNumberValue()));
return calendar.getTime();
} else {
return DECIMAL_FORMAT.format(cellValue.getNumberValue());
}
} else if (cellValue.getCellType() == Cell.CELL_TYPE_STRING) {
if (StringUtils.hasText(cellValue.getStringValue())) {
return cellValue.getStringValue();
}
}
} catch (NotImplementedException e) {
// If formula use Excel features not implemented in POI (like proper),
// we can retrieve the cached value (which may no longer be correct, depending of what you do on your file).
FormulaFeature feature = EnumUtils.getEnum(FormulaFeature.class, e.getCause().getMessage());
if (ALLOWED_NOT_IMPLEMENTED_FORMULA_FEATURES.contains(feature)) {
return getCellPrimitiveValue(cell, cell.getCachedFormulaResultType());
} else {
throw e;
}
}
return null;
}
项目:onetwo
文件:ExcelUtils.java
public static CellValue getFormulaCellValue(Cell cell){
try {
return cell==null?null:cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator().evaluate(cell);
} catch (Exception e) {
// throw new BaseException("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage(), e);
if(ExcelGenerators.isDevModel()){
logger.warn("get formual cell value["+cell.getRowIndex()+", "+ cell.getColumnIndex()+"] error : " + e.getMessage());
}
return null;
}
}
项目:excel-cellformatter
文件:POIEvaluatedCell.java
/**
* セルと評価した結果を
* @param cell タイプが数式のセル。
* @param value 数式を評価した結果。
*/
public POIEvaluatedCell(final Cell cell, final CellValue value) {
super(cell);
this.value = value;
}
项目:arch-tools
文件:DoublePointWithLabel.java
public DoublePointWithLabel(CellValue label, double[] point, CellStyle cellStyle) {
super(point);
this.label = label;
this.cellStyle = cellStyle;
}
项目:arch-tools
文件:DoublePointWithLabel.java
public DoublePointWithLabel(CellValue label, int[] point, CellStyle cellStyle) {
super(point);
this.label = label;
this.cellStyle = cellStyle;
}
项目:openbd-core
文件:SpreadsheetGetCellValue.java
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
cfSpreadSheetData spreadsheet = null;
int rowNo, columnNo;
/*
* Collect up the parameters
*/
spreadsheet = (cfSpreadSheetData)parameters.get(2);
rowNo = parameters.get(1).getInt() - 1;
columnNo = parameters.get(0).getInt() - 1;
if ( rowNo < 0 )
throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
throwException(_session, "column must be 1 or greater (" + columnNo + ")");
/*
* Find the cell in question
*/
Sheet sheet = spreadsheet.getActiveSheet();
Row row = sheet.getRow( rowNo );
if ( row == null )
row = sheet.createRow( rowNo );
Cell cell = row.getCell( columnNo );
if ( cell == null )
cell = row.createCell( columnNo );
FormulaEvaluator evaluator = spreadsheet.getWorkBook().getCreationHelper().createFormulaEvaluator();
if ( cell.getCellType() == Cell.CELL_TYPE_BOOLEAN )
return cfBooleanData.getcfBooleanData( cell.getBooleanCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_NUMERIC )
return new cfNumberData( cell.getNumericCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_BLANK )
return cfStringData.EMPTY_STRING;
else if ( cell.getCellType() == Cell.CELL_TYPE_STRING )
return new cfStringData( cell.getStringCellValue() );
else if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
CellValue cellValue = evaluator.evaluate(cell);
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return cfBooleanData.getcfBooleanData(cellValue.getBooleanValue());
case Cell.CELL_TYPE_NUMERIC:
return new cfNumberData(cellValue.getNumberValue());
case Cell.CELL_TYPE_STRING:
return new cfStringData(cellValue.getStringValue());
default:
return cfStringData.EMPTY_STRING;
}
}else
return cfStringData.EMPTY_STRING;
}
项目:Apache-POI_DataBroker_PlugIn
文件:XSSFSheetToCSVDataProcessor.java
private String generateCSVFromCell(Cell cell, FormulaEvaluator evaluator)
{
try
{
if ((cell != null) && (cell.getCellType() != Cell.CELL_TYPE_BLANK))
{
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue.getCellType() == Cell.CELL_TYPE_STRING)
return cellValue.getStringValue();
else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
if (DateUtil.isCellDateFormatted(cell))
{
CellStyle cellStyle = cell.getCellStyle();
String excelDateFormat = cellStyle.getDataFormatString();
String javaDateFormat = excelToJavaDataFormat(excelDateFormat);
DateFormat dateFormat = new SimpleDateFormat(javaDateFormat);
return dateFormat.format(cell.getDateCellValue());
}
else
return Double.toString(cellValue.getNumberValue());
}
else if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN)
return Boolean.toString(cellValue.getBooleanValue());
else if (cellValue.getCellType() == Cell.CELL_TYPE_BLANK)
return "";
else
{
logger.log(Level.WARNING, "Problem process cell: Unknown CellValue Type = " + cellValue.getCellType());
return "";
}
}
else
return "";
}
catch (Throwable throwable)
{
logger.log(Level.WARNING, "Problem process cell: Unknown Cell Type", throwable);
return "";
}
}
项目:excella-core
文件:PoiUtil.java
/**
* セルの値の取得。 セルのタイプに応じた値を返却する。<br>
* <br>
* 注:セルタイプが[CELL_TYPE_ERROR]の場合<br>
* ・xls形式 :エラーコードを返却(HSSFErrorConstantsに定義)<br>
* ・xlsx形式 :Excelのエラー値を返却(ex.#DIV/0!、#N/A、#REF!・・・)
*
* @param cell 対象セル
* @return 値
*/
public static Object getCellValue( Cell cell) {
Object value = null;
if ( cell != null) {
switch ( cell.getCellTypeEnum()) {
case BLANK:
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case ERROR:
value = cell.getErrorCellValue();
break;
case NUMERIC:
// 日付の場合
if ( isCellDateFormatted( cell)) {
value = cell.getDateCellValue();
} else {
value = cell.getNumericCellValue();
}
break;
case STRING:
value = cell.getStringCellValue();
break;
case FORMULA:
FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
// 式を評価
CellValue cellValue = evaluator.evaluate( cell);
CellType cellType = cellValue.getCellTypeEnum();
// 評価結果の型で分岐
switch ( cellType) {
case BLANK:
break;
case BOOLEAN:
value = cell.getBooleanCellValue();
break;
case ERROR:
if ( cell instanceof XSSFCell) {
// XSSF形式の場合は、文字列を返却
XSSFCell xssfCell = ( XSSFCell) cell;
CTCell ctCell = xssfCell.getCTCell();
value = ctCell.getV();
} else if ( cell instanceof HSSFCell) {
// HSSF形式の場合は、エラーコードを返却
value = cell.getErrorCellValue();
}
break;
case NUMERIC:
// 日付の場合
if ( isCellDateFormatted( cell)) {
value = cell.getDateCellValue();
} else {
value = cell.getNumericCellValue();
}
break;
case STRING:
value = cell.getStringCellValue();
break;
default:
break;
}
default:
break;
}
}
return value;
}
项目:nci-term-browser
文件:ResolvedValueSetIteratorHolder.java
private String getCellData(final HSSFCell cell) {
if (cell == null) return null;
int colspan = 1;
if (colIndex == mergeStart) {
// First cell in the merging region - set colspan.
colspan = mergeEnd - mergeStart + 1;
} else if (colIndex == mergeEnd) {
// Last cell in the merging region - no more skipped cells.
mergeStart = -1;
mergeEnd = -1;
return null;
} else if (mergeStart != -1 && mergeEnd != -1 && colIndex > mergeStart
&& colIndex < mergeEnd) {
// Within the merging region - skip the cell.
return null;
}
StringBuffer buf = new StringBuffer();
String val = "";
try {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
val = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
// POI does not distinguish between integer and double, thus:
final double original = cell.getNumericCellValue(),
rounded = Math.round(original);
if (Math.abs(rounded - original) < 0.00000000000000001) {
val = String.valueOf((int) rounded);
} else {
val = String.valueOf(original);
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
final CellValue cv = evaluator.evaluate(cell);
switch (cv.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
out.append(cv.getBooleanValue());
break;
case Cell.CELL_TYPE_NUMERIC:
out.append(cv.getNumberValue());
break;
case Cell.CELL_TYPE_STRING:
out.append(cv.getStringValue());
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_ERROR:
break;
default:
break;
}
break;
default:
// Neither string or number? Could be a date.
try {
val = sdf.format(cell.getDateCellValue());
} catch (final Exception e1) {
}
}
} catch (final Exception e) {
val = e.getMessage();
}
if ("null".equals(val)) {
val = "";
}
return val;
}
项目:excel-cellformatter
文件:POIEvaluatedCell.java
/**
* 式を評価したセルの値を取得する。
* @return
*/
public CellValue getCellValue() {
return value;
}
项目:ph-poi
文件:ExcelFormulaEvaluator.java
/**
* If cell contains a formula, the formula is evaluated and returned, else the
* CellValue simply copies the appropriate cell value from the cell and also
* its cell type. This method should be preferred over evaluateInCell() when
* the call should not modify the contents of the original cell.
*
* @param aCell
* The cell to evaluate
* @return The evaluation result
*/
public CellValue evaluate (@Nonnull final Cell aCell)
{
return m_aEvaluator.evaluate (aCell);
}