Java CellType.NUMERIC属性代码示例

/**
 * 获取单元格的值
 * 
 * @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;
}
 

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

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

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

public final static PartitionMap<String ,RCell> readDatas(Sheet i_Sheet ,Integer i_BeginRow ,Integer i_EndRow)
{
    PartitionMap<String ,RCell> v_Ret      = new TablePartition<String ,RCell>();
    Sheet                       v_Sheet    = i_Sheet;
    int                         v_BeginRow = 0;
    int                         v_EndRow   = 0;
    
    if ( i_BeginRow != null )
    {
        v_BeginRow = i_BeginRow.intValue();
        
        if ( v_BeginRow < 0 )
        {
            v_BeginRow = 0;
        }
    }
    
    if ( i_EndRow != null )
    {
        v_EndRow = i_EndRow.intValue();
    }
    else
    {
        v_EndRow = v_Sheet.getPhysicalNumberOfRows();
    }
    
    for (int v_RowNo=v_BeginRow; v_RowNo<=v_EndRow; v_RowNo++)
    {
        Row v_Row = v_Sheet.getRow(v_RowNo);
        if ( v_Row == null )
        {
            continue;
        }
        
        short v_CellCount = v_Row.getLastCellNum();
        
        for (int v_ColumnNo=0; v_ColumnNo<v_CellCount; v_ColumnNo++)
        {
            Cell v_Cell = v_Row.getCell(v_ColumnNo);
            if ( v_Cell == null )
            {
                continue;
            }
            
            if ( v_Cell.getCellTypeEnum() == CellType.STRING )
            {
                String v_Value = v_Cell.getStringCellValue();
                
                if ( !Help.isNull(v_Value) )
                {
                    RCell        v_RCell    = new RCell(v_RowNo ,v_ColumnNo);
                    List<String> v_Decimals = StringHelp.getString(v_Cell.getCellStyle().getDataFormatString() ,$Decimal);
                    if ( !Help.isNull(v_Decimals) )
                    {
                        v_RCell.setDecimal(v_Decimals.get(0).split("\\.")[1].length());
                    }
                    
                    v_Ret.putRow(v_Value.trim() ,v_RCell);
                }
            }
            else if ( v_Cell.getCellTypeEnum() == CellType.NUMERIC )
            {
                if ( HSSFDateUtil.isCellDateFormatted(v_Cell) ) 
                {
                    if ( v_Cell.getDateCellValue() != null )
                    {
                        v_Ret.putRow((new Date(v_Cell.getDateCellValue())).getFull() ,new RCell(v_RowNo ,v_ColumnNo));
                    }
                } 
                else 
                {
                    v_Ret.putRow(String.valueOf(v_Cell.getNumericCellValue()) ,new RCell(v_RowNo ,v_ColumnNo));
                }
            }
        }
    }
    
    return v_Ret;
}
 

@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 = null;
    SubjectType subjectType = null;
    String year = "2014";
    for (String geographyScopeString : geographyScope) {
        GeographyLabel geographyLabel = GeographyLabel.valueOf(geographyScopeString);
        switch (geographyLabel) {
            case la:
                sheet = workbook.getSheet("LAData_2011-12_2013-14");
                subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER,
                        OaImporter.OaType.localAuthority.name(), OaImporter.OaType.localAuthority.datasourceSpec.getDescription());
                break;
            case msoa:
                sheet = workbook.getSheet("MSOAData_2011-12_2013-14");
                subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER,
                        OaImporter.OaType.msoa.name(), OaImporter.OaType.msoa.datasourceSpec.getDescription());
                break;
            case ward:
                throw new Error("Wards are not yet supported");
                // FIXME: In case we want to suport wards at some point, here is the sheet to use
                //sheet = workbook.getSheet("WardData_2011-12_2013-14");
                //break;
        }
        if (sheet == null)
            throw new Error("Sheet not found for datasource: " + datasource.getDatasourceSpec().getId());

        // Define a list of timed value extractor, one for each attribute
        List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();

        RowCellExtractor subjectExtractor = new RowCellExtractor(0, CellType.STRING);
        ConstantExtractor timestampExtractor = new ConstantExtractor(year);

        for (AttributeLabel attributeLabel : AttributeLabel.values()) {
            ConstantExtractor attributeExtractor = new ConstantExtractor(attributeLabel.name());
            RowCellExtractor valueExtractor = new RowCellExtractor(getAttributeColumnId(geographyLabel, attributeLabel), CellType.NUMERIC);
            timedValueExtractors.add(new TimedValueExtractor(getProvider(), subjectType, subjectExtractor, attributeExtractor, timestampExtractor, valueExtractor));
        }

        // Extract timed values
        excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
    }
}
 

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

/**
 * 复制单位格（空白行的复制，即只复制格式和固定文字，不填充数据）
 * 
 * @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);
    }
}
 

private Map<String, String> getRow(Sheet sheet, int index, Map<Integer, String> columnNames, DataFormatter formatter) {
    Map<String, String> values = new HashMap<>();
    for (Cell cell : sheet.getRow(index)) {
        String key = columnNames.get(cell.getColumnIndex());
        if (key != null) {
            if (cell.getCellTypeEnum() == CellType.NUMERIC && !DateUtil.isCellDateFormatted(cell)) values.put(key, String.valueOf(cell.getNumericCellValue()));
            else values.put(key, formatter.formatCellValue(cell));
        }
    }
    return values;
}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER, OaImporter.OaType.lsoa.name(), OaImporter.OaType.lsoa.datasourceSpec.getDescription());
    // Loop over years
    for (int sheetId = 0; sheetId < getWorkbook().getNumberOfSheets(); sheetId++){
        Sheet sheet = getWorkbook().getSheetAt(sheetId);

        int year;
        try {
            year = Integer.parseInt(sheet.getSheetName().substring(sheet.getSheetName().length()-4, sheet.getSheetName().length()));
        }catch (NumberFormatException e){
            // Sheetname does not end in a year
            continue;
        }

        // Create extractors for each timed value
        List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();

        RowCellExtractor subjectExtractor = new RowCellExtractor(0, CellType.STRING);
        ConstantExtractor timestampExtractor = new ConstantExtractor(String.valueOf(year));

        // Get the attribute label row and create TimedValueExtractors
        Row attributeLabelRow = sheet.getRow(5);
        for (int columnId = 0; columnId < attributeLabelRow.getLastCellNum(); columnId++){
            RowCellExtractor tmpAttributeLabelExtractor = new RowCellExtractor(columnId, CellType.STRING);
            tmpAttributeLabelExtractor.setRow(attributeLabelRow);
            Attribute attribute = AttributeUtils.getByProviderAndLabel(getProvider(), tmpAttributeLabelExtractor.extract());
            if (attribute != null){
                ConstantExtractor attributeExtractor = new ConstantExtractor(attribute.getLabel());
                RowCellExtractor valueExtractor = new RowCellExtractor(columnId, CellType.NUMERIC);
                timedValueExtractors.add(new TimedValueExtractor(getProvider(), subjectType, subjectExtractor, attributeExtractor, timestampExtractor, valueExtractor));
            }
        }

        // Extract timed values
        excelUtils.extractAndSaveTimedValues(sheet, this, timedValueExtractors);
    }

    getWorkbook().close();
}
 

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

@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.getSheetAt(1);
    String year = "2014";

    List<TimedValueExtractor> timedValueExtractors = new ArrayList<>();
    RowCellExtractor subjectExtractor = new RowCellExtractor(1, 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);
}
 

@Override
protected void importDatasource(Datasource datasource, List<String> geographyScope, List<String> temporalScope, List<String> datasourceLocation) throws Exception {
    SubjectType subjectType = SubjectTypeUtils.getOrCreate(AbstractONSImporter.PROVIDER,
            OaImporter.OaType.localAuthority.name(), OaImporter.OaType.localAuthority.datasourceSpec.getDescription());

    ExcelUtils excelUtils = new ExcelUtils();

    File localFile = downloadUtils.fetchFile(new URL(DATAFILE), getProvider().getLabel(), ".zip");
    ZipFile zipFile = new ZipFile(localFile);
    for (AttributePrefix attributePrefix : AttributePrefix.values()){
        ZipArchiveEntry zipEntry = zipFile.getEntry(bookNames[attributePrefix.ordinal()]);
        Workbook workbook = excelUtils.getWorkbook(zipFile.getInputStream(zipEntry));
        for (String sheetName : sheetNames) {
            RowCellExtractor subjectLabelExtractor = new RowCellExtractor(1, CellType.STRING);
            ConstantExtractor timestampExtractor = new ConstantExtractor("2016");   // FIXME: Need to generalise when we update Datasource to be time aware

            List<TimedValueExtractor> extractors = new ArrayList<>();

            for (String metricName : metricNames) {
                ConstantExtractor attributeLabelExtractor = new ConstantExtractor(getAttributeLabel(attributePrefix, sheetName, metricName));
                RowCellExtractor valueExtractor;
                switch (metricName){
                    case "Mean":
                        valueExtractor = new RowCellExtractor(5, CellType.NUMERIC);
                        break;
                    case "Median":
                        valueExtractor = new RowCellExtractor(3, CellType.NUMERIC);
                        break;
                    default:
                        throw new Error("Unknown metric name: " + metricName);
                }
                extractors.add(new TimedValueExtractor(getProvider(), subjectType, subjectLabelExtractor, attributeLabelExtractor, timestampExtractor, valueExtractor));
            }

            Sheet sheet = workbook.getSheet(sheetName);
            excelUtils.extractAndSaveTimedValues(sheet,this,extractors);
        }
    }
}
 

@Test
public void extract() throws Exception {
    RowCellExtractor extractor = new RowCellExtractor(1, CellType.NUMERIC);

    extractor.setRow(workbook.getSheet("sheet").getRow(0));
    assertEquals("5.0", extractor.extract());

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