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

/**
 * 先判断删除,省得影响效率
 * 
 * @param sheet
 * @param map
 * @throws Exception
 */
private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception {
	Row row = null;
	Cell cell = null;
	int index = 0;
	while (index <= sheet.getLastRowNum()) {
		row = sheet.getRow(index++);
		if (row == null) {
			continue;
		}
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			cell = row.getCell(i);
			if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) {
				cell.setCellType(Cell.CELL_TYPE_STRING);
				String text = cell.getStringCellValue();
				if (text.contains(IF_DELETE)) {
					if (Boolean.valueOf(eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map).toString())) {
						PoiSheetUtility.deleteColumn(sheet, i);
					}
					cell.setCellValue("");
				}
			}
		}
	}
}
 

public static void main(String[] args) {
	try {
		FileInputStream file = new FileInputStream(new File(
				TestUtil.DOC_PATH + File.separator
						+ TestWriteExcelDemo.EXCEL_NAME
						+ Globals.SUFFIX_XLSX));
		// Create Workbook instance holding reference to .xlsx file
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		// 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();
				// Check the cell type and format accordingly
				switch (cell.getCellType()) {
				case Cell.CELL_TYPE_NUMERIC:
					System.out.print(cell.getNumericCellValue() + "\t");
					break;
				case Cell.CELL_TYPE_STRING:
					System.out.print(cell.getStringCellValue() + "\t");
					break;
				}
			}
			System.out.println("");
		}
		file.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}
 

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

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

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

public static int getIntCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);
    if (row == null) {
        return 0;
    }
    final HSSFCell cell = row.getCell(c);

    try {
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            return 0;
        }
    } catch (final RuntimeException e) {
        System.err.println("Exception at sheet name:" + sheet.getSheetName() + ", row:" + (r + 1) + ", col:" + (c + 1));
        throw e;
    }

    return (int) cell.getNumericCellValue();
}
 

private void commitCell(DataCell cell) {
    int cellType = cell.getCellType();
    switch(cellType) {
        case Cell.CELL_TYPE_NUMERIC:

        case Cell.CELL_TYPE_BLANK:
        case Cell.CELL_TYPE_FORMULA:
        case Cell.CELL_TYPE_STRING:

        case Cell.CELL_TYPE_BOOLEAN:

        default:

    }
}
 

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

private static StringWriter createCsv(Workbook workBook) throws IOException {

    Sheet sheet = workBook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    StringWriter stringWriter = new StringWriter();
    CSVWriter csvOutput = new CSVWriter(stringWriter);

    while (rowIterator.hasNext()) {
      Row row = rowIterator.next();
      int i = 0;
      String[] cellValues = new String[SHEET_CELL_SIZE];
      Iterator<Cell> cellIterator = row.cellIterator();
      while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            cellValues[i] = cell.getStringCellValue();
            break;
          case Cell.CELL_TYPE_NUMERIC:
            cellValues[i] = String.valueOf(cell.getNumericCellValue());
            break;
          case Cell.CELL_TYPE_BLANK:
            cellValues[i] = "";
            break;
        }
        i = i + 1;
      }
      csvOutput.writeNext(cellValues);
    }
    csvOutput.close();
    return stringWriter;
  }
 

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

protected String getCellValueAsString(Cell cell, int type) {
    if(cell != null) {
        switch(type) {
            case Cell.CELL_TYPE_ERROR: {
                return "ERROR"+cell.getErrorCellValue();
            }
            case Cell.CELL_TYPE_BOOLEAN: {
                return ""+cell.getBooleanCellValue();
            }
            case Cell.CELL_TYPE_NUMERIC: {
                if(DateUtil.isCellDateFormatted(cell)) {
                    return dateFormat.format(cell.getDateCellValue());
                }
                else {
                    double value = cell.getNumericCellValue();
                    String formatString = cell.getCellStyle().getDataFormatString();
                    int formatIndex = cell.getCellStyle().getDataFormat();
                    return formatter.formatRawCellContents(value, formatIndex, formatString);
                }
            }
            case Cell.CELL_TYPE_STRING: {
                return cell.getRichStringCellValue().getString();
            }
        }
    }
    return null;
}
 

/**
 * 给每个Cell通过解析方式set值
 * 
 * @param cell
 * @param map
 */
private void setValueForCellByMap(Cell cell, Map<String, Object> map) throws Exception {
	int cellType = cell.getCellType();
	if (cellType != Cell.CELL_TYPE_STRING && cellType != Cell.CELL_TYPE_NUMERIC) {
		return;
	}
	String oldString;
	cell.setCellType(Cell.CELL_TYPE_STRING);
	oldString = cell.getStringCellValue();
	if (oldString != null && oldString.indexOf(START_STR) != -1 && !oldString.contains(FOREACH)) {
		// step 2. 判断是否含有解析函数
		String params = null;
		boolean isNumber = false;
		if (isNumber(oldString)) {
			isNumber = true;
			oldString = oldString.replace(NUMBER_SYMBOL, "");
		}
		while (oldString.indexOf(START_STR) != -1) {
			params = oldString.substring(oldString.indexOf(START_STR) + 2, oldString.indexOf(END_STR));

			oldString = oldString.replace(START_STR + params + END_STR, eval(params, map).toString());
		}
		// 如何是数值 类型,就按照数值类型进行设置
		if (isNumber && StringUtils.isNotBlank(oldString)) {
			cell.setCellValue(Double.parseDouble(oldString));
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
		} else {
			cell.setCellValue(oldString);
		}
	}
	// 判断foreach 这种方法
	if (oldString != null && oldString.contains(FOREACH)) {
		addListDataToExcel(cell, map, oldString.trim());
	}

}
 

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

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

@Override
public E get(Row target) throws Exception {
    final Cell cell = target.getCell(index);
    if (cell != null) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK : return null;
            case Cell.CELL_TYPE_STRING : return Enum.valueOf(enumClass, cell.getStringCellValue());
            case Cell.CELL_TYPE_NUMERIC : return values[(int)cell.getNumericCellValue()];
            default:
                throw new UnsupportedOperationException("Cannot convert cell to enum");
        }
    } else {
        return null;
    }
}
 

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

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

/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
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;
}
 

/**
 * 读取某个工作簿上的所有单元格的值。
 * 作者：朱志杰
 * July 30, 2013 3:27:08 PM
 * @param sheetOrder 工作簿序号，从0开始。
 * @return List<Object[]> 所有单元格的值。
 * @throws IOException 加载excel文件IO异常。
 * @throws FileNotFoundException excel文件没有找到异常。
 * @throws InvalidFormatException 
 */
public List<Object[]> read(int sheetOrder) throws FileNotFoundException, IOException, InvalidFormatException{
	FileInputStream fis = new FileInputStream(path);
	Workbook workbook = WorkbookFactory.create(fis);
	if(fis != null) {
           fis.close();
       }
	Sheet sheet = workbook.getSheetAt(sheetOrder);
	//用来记录excel值
	List<Object[]> valueList=new LinkedList<Object[]>();
	 //循环遍历每一行、每一列。
       for(Row row : sheet){  
       	//每一行
       	Object[] rowObject=null;
           for(Cell cell : row){  
               //cell.getCellType是获得cell里面保存的值的type  
               switch(cell.getCellType()){  
                   case Cell.CELL_TYPE_BOOLEAN:  
                       //得到Boolean对象的方法  
                       rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getBooleanCellValue());
                       break;  
                   case Cell.CELL_TYPE_NUMERIC:  
                       //先看是否是日期格式  
                       if(DateUtil.isCellDateFormatted(cell)){  
                           //读取日期格式  
                       	rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getDateCellValue());
                       }else{  
                       	DecimalFormat df = new DecimalFormat();
                       	//单元格的值,替换掉,
                       	String value=df.format(cell.getNumericCellValue()).replace(",","");
                           //读取数字  
                       	rowObject=CollectionUtil.addObjectToArray(rowObject, value);
                       }  
                       break;  
                   case Cell.CELL_TYPE_FORMULA:  
                       //读取公式  
                   	rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getCellFormula());
                       break;  
                   case Cell.CELL_TYPE_STRING:  
                       //读取String  
                   	rowObject=CollectionUtil.addObjectToArray(rowObject, cell.getRichStringCellValue().toString());
                       break;                    
               }
           }
           //将这行添加到list。
           valueList.add(rowObject);
       }
       return valueList;
}
 

/**
 * Extracts out the columns in the given excel sheet
 *
 * @param sheet Sheet instance corresponding to the desired Excel sheet
 * @return Array containing the column header data
 * @throws SQLException SQLException
 */
private ColumnInfo[] extractColumnHeaders(Sheet sheet) throws SQLException {
    List<ColumnInfo> headers = new ArrayList<ColumnInfo>();

    /* If hasHeader property is set to false, populate header map with column names following
     * the format 'COLUMN' + 'i' where i corresponds to the column id */
    if (!((TConnection)getConnection()).hasHeader()) {
        int maxColumns = ((TConnection)getConnection()).getMaxColumns();
        for (int i = 0; i < maxColumns; i++) {
            headers.add(new ColumnInfo(i + 1, Constants.COLUMN + (i + 1), sheet.getSheetName(),
                    -1, i + 1));
        }
        return headers.toArray(new ColumnInfo[headers.size()]);
    }
    // Retrieving the first row of the sheet as the header row.
    Row row = sheet.getRow(0);
    if (row != null) {
        Iterator<Cell> itr = row.cellIterator();
        while (itr.hasNext()) {
            Cell cell = itr.next();
            if (cell != null) {
                int cellType = cell.getCellType();
                switch (cellType) {
                    case Cell.CELL_TYPE_STRING:
                        headers.add(new ColumnInfo(cell.getColumnIndex() + 1,
                                cell.getStringCellValue(), sheet.getSheetName(), Types.VARCHAR,
                                cell.getColumnIndex() + 1));
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        headers.add(new ColumnInfo(cell.getColumnIndex() + 1,
                                String.valueOf(cell.getNumericCellValue()),
                                sheet.getSheetName(), Types.INTEGER,
                                cell.getColumnIndex() + 1));
                        break;
                    default:
                        throw new SQLException("Invalid column type");
                }
            }
        }
    }
    return headers.toArray(new ColumnInfo[headers.size()]);
}