Java Code Examples for org.apache.poi.ss.usermodel.Cell#CELL_TYPE_FORMULA

The following examples show how to use org.apache.poi.ss.usermodel.Cell#CELL_TYPE_FORMULA . 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 File: CellValueFormatter.java    From CloverETL-Engine with GNU Lesser General Public License v2.1 6 votes vote down vote up 
@Override
public String formatCellValue(Cell cell, FormulaEvaluator evaluator) {

    if (cell == null) {
        return "";
    }

    int cellType = cell.getCellType();
    if (cellType == Cell.CELL_TYPE_FORMULA) {
        if (evaluator == null) {
            return cell.getCellFormula();
        }
        cellType = evaluator.evaluateFormulaCell(cell);
    }
    switch (cellType) {
        case Cell.CELL_TYPE_NUMERIC :
            return getFormattedNumberString(cell);
        case Cell.CELL_TYPE_STRING :
            return cell.getRichStringCellValue().getString();
        case Cell.CELL_TYPE_BOOLEAN :
            return String.valueOf(cell.getBooleanCellValue());
        case Cell.CELL_TYPE_BLANK :
            return "";
    }
    throw new RuntimeException("Unexpected celltype (" + cellType + ")");
}
Example 2
Source File: ExcelImportServer.java    From autopoi with Apache License 2.0 6 votes vote down vote up 
/**
 * 获取key的值,针对不同类型获取不同的值
 * 
 * @Author JEECG
 * @date 2013-11-21
 * @param cell
 * @return
 */
private String getKeyValue(Cell cell) {
	if(cell==null){
		return null;
	}
	Object obj = null;
	switch (cell.getCellType()) {
	case Cell.CELL_TYPE_STRING:
		obj = cell.getStringCellValue();
		break;
	case Cell.CELL_TYPE_BOOLEAN:
		obj = cell.getBooleanCellValue();
		break;
	case Cell.CELL_TYPE_NUMERIC:
		obj = cell.getNumericCellValue();
		break;
	case Cell.CELL_TYPE_FORMULA:
		obj = cell.getCellFormula();
		break;
	}
	return obj == null ? null : obj.toString().trim();
}
Example 3
Source File: ExcelImportServer.java    From jeasypoi with Apache License 2.0 6 votes vote down vote up 
/**
 * 获取key的值,针对不同类型获取不同的值
 * 
 * @Author JueYue
 * @date 2013-11-21
 * @param cell
 * @return
 */
private String getKeyValue(Cell cell) {
	Object obj = null;
	switch (cell.getCellType()) {
	case Cell.CELL_TYPE_STRING:
		obj = cell.getStringCellValue();
		break;
	case Cell.CELL_TYPE_BOOLEAN:
		obj = cell.getBooleanCellValue();
		break;
	case Cell.CELL_TYPE_NUMERIC:
		obj = cell.getNumericCellValue();
		break;
	case Cell.CELL_TYPE_FORMULA:
		obj = cell.getCellFormula();
		break;
	}
	return obj == null ? null : obj.toString().trim();
}
Example 4
Source File: SpreadsheetGetCellFormula.java    From openbd-core with GNU General Public License v3.0 6 votes vote down vote up 
private cfData	getAllFormulaForSheet( cfSession _session, cfSpreadSheetData	spreadsheet ) throws cfmRunTimeException {
	cfArrayData	array	= cfArrayListData.createArray(1);	
	
	Iterator<Row>	rowIt	= spreadsheet.getActiveSheet().rowIterator();
	while ( rowIt.hasNext() ){
		Row row = rowIt.next();
		
		Iterator<Cell> cellIt	= row.cellIterator();
		while ( cellIt.hasNext() ){
			Cell cell	= cellIt.next();
			
			if ( cell.getCellType() == Cell.CELL_TYPE_FORMULA ){
				cfStructData	s = new cfStructData();
				s.setData( "formula", new cfStringData( cell.getCellFormula() ) );
				s.setData( "row", 		new cfNumberData( row.getRowNum() + 1 ) );
				s.setData( "column", 	new cfNumberData( cell.getColumnIndex() + 1 ) );
				array.addElement( s );
			}
		}
	}
	
	return array;
}
Example 5
Source File: ExcelUtil.java    From Leo with Apache License 2.0 6 votes vote down vote up 
private String getStrFromCell(Cell cell) {
		String res = "";
		if (null==cell) {
			return "";
		}
//		res=cell.getRichStringCellValue().toString();
		
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC: // 数字/日期
				if (DateUtil.isCellDateFormatted(cell)){
					res=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
				}else{
					BigDecimal value =  new BigDecimal(cell.getNumericCellValue());
					String str = value.toString(); 
					if(str.contains(".0"))str = str.replace(".0", "");
					res=str;
				}
				break;
			case Cell.CELL_TYPE_STRING: // 字符串
				res = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN: // 布尔
				Boolean booleanValue = cell.getBooleanCellValue();
				res = booleanValue.toString();
				break;
			case Cell.CELL_TYPE_BLANK: // 空值
				res = "";
				break;
			case Cell.CELL_TYPE_FORMULA: // 公式
				res = cell.getCellFormula();
				break;
			case Cell.CELL_TYPE_ERROR: // 故障
				res = "";
				break;
			default:
				System.out.println("未知类型");
				break;
		}
		return res;
	}
Example 6
Source File: ExcelImportServer.java    From easypoi with Apache License 2.0 6 votes vote down vote up 
/**
 * 获取key的值,针对不同类型获取不同的值
 * 
 * @Author JueYue
 * @date 2013-11-21
 * @param cell
 * @return
 */
private String getKeyValue(Cell cell) {
    Object obj = null;
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            obj = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            obj = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            obj = cell.getNumericCellValue();
            break;
        case Cell.CELL_TYPE_FORMULA:
            obj = cell.getCellFormula();
            break;
    }
    return obj == null ? null : obj.toString().trim();
}
Example 7
Source File: ImportExcel.java    From Shop-for-JavaWeb with MIT License 6 votes vote down vote up 
/**
 * 获取单元格值
 * @param row 获取的行
 * @param column 获取单元格列号
 * @return 单元格值
 */
public Object getCellValue(Row row, int column){
	Object val = "";
	try{
		Cell cell = row.getCell(column);
		if (cell != null){
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
				val = cell.getNumericCellValue();
			}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
				val = cell.getStringCellValue();
			}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
				val = cell.getCellFormula();
			}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
				val = cell.getBooleanCellValue();
			}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
				val = cell.getErrorCellValue();
			}
		}
	}catch (Exception e) {
		return val;
	}
	return val;
}
Example 8
Source File: ExcelDataReader.java    From micro-integrator with Apache License 2.0 5 votes vote down vote up 
/**
 * Extracts the value of a particular cell depending on its type
 *
 * @param cell A populated Cell instance
 * @return Value of the cell
 */
private Object extractCellValue(Cell cell) {
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            return cell.getNumericCellValue();
        case Cell.CELL_TYPE_BLANK:
        case Cell.CELL_TYPE_FORMULA:
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue();
        default:
            return cell.getStringCellValue();
    }
}
Example 9
Source File: readExcelXLSX.java    From Selenium with The Unlicense 5 votes vote down vote up 
private static String cellToString(XSSFCell cell) {

		Object result;

		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC:
			result = cell.getNumericCellValue();
			break;

		case Cell.CELL_TYPE_STRING:
			result = cell.getStringCellValue();
			break;

		case Cell.CELL_TYPE_BOOLEAN:
			result = cell.getBooleanCellValue();
			break;

		case Cell.CELL_TYPE_FORMULA:
			result = cell.getCellFormula();
			break;

		default:
			throw new RuntimeException("Unknown Cell Type");
		}

		return result.toString();
	}
Example 10
Source File: TestExcelFormulaDemo.java    From poi with Apache License 2.0 5 votes vote down vote up 
public static void readSheetWithFormula() {
	try {
		FileInputStream file = new FileInputStream(new File(
				TestUtil.DOC_PATH + File.separator + EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		// Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		FormulaEvaluator evaluator = workbook.getCreationHelper()
				.createFormulaEvaluator();
		// Get first/desired sheet from the workbook
		XSSFSheet sheet = workbook.getSheetAt(0);
		// Iterate through each rows one by one
		Iterator<Row> rowIterator = sheet.iterator();
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();
			// For each row, iterate through all the columns
			Iterator<Cell> cellIterator = row.cellIterator();
			while (cellIterator.hasNext()) {
				Cell cell = cellIterator.next();
				// If it is formula cell, it will be evaluated otherwise no
				// change will happen
				switch (evaluator.evaluateInCell(cell).getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t\t");
					break;
				case Cell.CELL_TYPE_FORMULA:
					// Not again
					break;
				}
			}
			System.out.println("");
		}
		file.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
Example 11
Source File: AbstractExcelExtractor.java    From wandora with GNU General Public License v3.0 5 votes vote down vote up 
public Topic getCellTypeTopic(Cell cell, TopicMap tm) throws TopicMapException {
    int type = cell.getCellType();
    String typeStr = "string";
    switch(type) {
        case Cell.CELL_TYPE_BLANK: {
            typeStr = "blank";
            break;
        }
        case Cell.CELL_TYPE_BOOLEAN: {
            typeStr = "boolean";
            break;
        }
        case Cell.CELL_TYPE_ERROR: {
            typeStr = "error";
            break;
        }
        case Cell.CELL_TYPE_FORMULA: {
            typeStr = "formula";
            break;
        }
        case Cell.CELL_TYPE_NUMERIC: {
            typeStr = "numeric";
            break;
        }
        case Cell.CELL_TYPE_STRING: {
            typeStr = "string";
            break;
        }   
    }
    Topic t = getOrCreateTopic(tm, EXCEL_CELL_TYPE_SI_PREFIX+"/"+typeStr, "Excel cell type "+typeStr);
    t.addType(getCellTypeTypeTopic(tm));
    return t;
}
Example 12
Source File: ReadExcelFormula.java    From journaldev with MIT License 5 votes vote down vote up 
public static void readExcelFormula(String fileName) throws IOException{
	
	FileInputStream fis = new FileInputStream(fileName);
	
	//assuming xlsx file
	Workbook workbook = new XSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Iterator<Row> rowIterator = sheet.iterator();
	while (rowIterator.hasNext()) 
       {
		Row row = rowIterator.next();
		Iterator<Cell> cellIterator = row.cellIterator();
           
           while (cellIterator.hasNext()) 
           {
           	Cell cell = cellIterator.next();
           	switch(cell.getCellType()){
           	case Cell.CELL_TYPE_NUMERIC:
           		System.out.println(cell.getNumericCellValue());
           		break;
           	case Cell.CELL_TYPE_FORMULA:
           		System.out.println("Cell Formula="+cell.getCellFormula());
           		System.out.println("Cell Formula Result Type="+cell.getCachedFormulaResultType());
           		if(cell.getCachedFormulaResultType() == Cell.CELL_TYPE_NUMERIC){
           			System.out.println("Formula Value="+cell.getNumericCellValue());
           		}
           	}
           }
       }
}
Example 13
Source File: SsioIntegrationTest.java    From sep4j with Apache License 2.0 5 votes vote down vote up 
private Object getStringOrDateValue(Cell cell) {
	if (cell == null) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
		return String.valueOf(cell.getBooleanCellValue());
	}

	if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
		return null;
	}

	if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
		if (DateUtil.isCellDateFormatted(cell)) {
			return cell.getDateCellValue();
		} else {
			double v = cell.getNumericCellValue();
			return String.valueOf(v);
		}
	}

	if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
		String s = cell.getStringCellValue();
		return StringUtils.trimToNull(s);
	}
	return null;
}
Example 14
Source File: Excel2XMLTransformer.java    From equalize-xpi-modules with MIT License 5 votes vote down vote up 
private String retrieveCellContent(Cell cell, Workbook wb, boolean evaluateFormulas, String formatting) {
	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
	DataFormatter formatter = new DataFormatter(true);
	String cellContent = null;
	int cellType = cell.getCellType();
	switch(cellType) {
	case Cell.CELL_TYPE_BLANK:
		break;
	case Cell.CELL_TYPE_FORMULA:
		if (evaluateFormulas) {
			cellContent = formatter.formatCellValue(cell, evaluator);
		} else {
			// Display the formula instead
			cellContent = cell.getCellFormula();
		}
		break;
	default:
		if(formatting.equalsIgnoreCase("excel")) {
			cellContent = formatter.formatCellValue(cell);
		} else if(formatting.equalsIgnoreCase("raw")) {
			// Display the raw cell contents
			switch (cellType) {
			case Cell.CELL_TYPE_NUMERIC:
				cellContent = Double.toString(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING:
				cellContent = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				cellContent = Boolean.toString(cell.getBooleanCellValue());
				break;	
			}
		}
		break;
	}
	return cellContent;
}
Example 15
Source File: XssfWCellImpl.java    From xlsbeans with Apache License 2.0 5 votes vote down vote up 
public String getContents() {
  String contents = null;
  // IllegalStateException occurs , if illegal type defined...
  switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
      contents = "";
      break;
    case Cell.CELL_TYPE_BOOLEAN:
      contents = String.valueOf(cell.getBooleanCellValue());
      break;
    case Cell.CELL_TYPE_ERROR:
      contents = String.valueOf(cell.getCellFormula());
      break;
    case Cell.CELL_TYPE_FORMULA:
      contents = getCellFormulaContents(cell);
      break;
    case Cell.CELL_TYPE_NUMERIC:
      contents = getNumericContents(cell);
      break;
    case Cell.CELL_TYPE_STRING:
      contents = String.valueOf(cell.getStringCellValue());
      break;
    default:
      contents = "";
      break;
  }
  return contents;
}
Example 16
Source File: ExcelTempletService.java    From jeewx with Apache License 2.0 5 votes vote down vote up 
/**
  * 得到某个格子的值 已经对过时方法进行更新
  * 
  * @param cell
  *            格子对象
  * @return 格子的值
  */
public static String getCellValueString(Cell cell) {
  if (cell == null) {
   return null;
  }
  // 时间对象 特殊处理
  int dataFormat = cell.getCellStyle().getDataFormat();
  
  if (dataFormat == 14 || dataFormat == 178 || dataFormat == 180 || dataFormat == 181
    || dataFormat == 182) {
	  	return getDateValue(cell);
  } 
  String value = null;
  switch (cell.getCellType()) {
	   case Cell.CELL_TYPE_NUMERIC :
	    value = new DecimalFormat("0.##########").format(cell.getNumericCellValue());
	    break;
	   case Cell.CELL_TYPE_STRING :
	    // value = cell.getStringCellValue();
	    value = cell.getRichStringCellValue().toString();
	    break;
	   case Cell.CELL_TYPE_FORMULA :
	    value = String.valueOf(cell.getCellFormula());
	    break;
	   case Cell.CELL_TYPE_BLANK :
	    // value = String.valueOf(cell.getStringCellValue());
	    value = String.valueOf(cell.getRichStringCellValue().toString());
	    break;
	   case Cell.CELL_TYPE_BOOLEAN :
	    value = String.valueOf(cell.getBooleanCellValue());
	    break;
	   case Cell.CELL_TYPE_ERROR :
	    value = String.valueOf(cell.getErrorCellValue());
	    break;
  }
  return value;
 }
Example 17
Source File: XssfWCellImpl.java    From xlsbeans with Apache License 2.0 5 votes vote down vote up 
public String getContents() {
  String contents = null;
  // IllegalStateException occurs , if illegal type defined...
  switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BLANK:
      contents = "";
      break;
    case Cell.CELL_TYPE_BOOLEAN:
      contents = String.valueOf(cell.getBooleanCellValue());
      break;
    case Cell.CELL_TYPE_ERROR:
      contents = String.valueOf(cell.getCellFormula());
      break;
    case Cell.CELL_TYPE_FORMULA:
      contents = getCellFormulaContents(cell);
      break;
    case Cell.CELL_TYPE_NUMERIC:
      contents = getNumericContents(cell);
      break;
    case Cell.CELL_TYPE_STRING:
      contents = String.valueOf(cell.getStringCellValue());
      break;
    default:
      contents = "";
      break;
  }
  return contents;
}
Example 18
Source File: SpreadsheetGetCellValue.java    From openbd-core with GNU General Public License v3.0 4 votes vote down vote up 
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;
}
Example 19
Source File: ExcelUtil.java    From autopoi with Apache License 2.0 4 votes vote down vote up 
/**
 * 获取单元格的值
 * @param cell
 * @return
 */
public static String getCellValue(Cell cell){

    if(cell == null) return "";

    if(cell.getCellType() == Cell.CELL_TYPE_STRING){

        return cell.getStringCellValue();

    }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){

        return String.valueOf(cell.getBooleanCellValue());

    }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){

        return cell.getCellFormula() ;

    }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

        return String.valueOf(cell.getNumericCellValue());

    }
    return "";
}
Example 20
Source File: ExcelUtil.java    From SI with BSD 2-Clause "Simplified" License 4 votes vote down vote up 
/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
public static List getData(Workbook wb) {
    List excelList = new ArrayList();

    int sheetNum = wb.getNumberOfSheets();

    for (int k=0; k<sheetNum; k++) {
        Sheet sheet = wb.getSheetAt(k);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int r=0; r<rows; r++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                String[] excelRow = new String[cells];
                for(int c=0; c<cells; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        String value = null;

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                            break;

                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
                            break;

                            case Cell.CELL_TYPE_STRING:
                                value = "" + cell.getStringCellValue();
                            break;

                            case Cell.CELL_TYPE_BLANK:
                                value = "" + cell.getBooleanCellValue();
                            break;

                            case Cell.CELL_TYPE_ERROR:
                                value = "" + cell.getErrorCellValue();
                            break;
                            default:
                        }
                        excelRow[c] = value;
                    }
                }
                excelList.add(excelRow);
            }
        }
    }
    return excelList;
}