Java Code Examples for org.apache.poi.hssf.usermodel.HSSFCell#CELL_TYPE_NUMERIC

The following examples show how to use org.apache.poi.hssf.usermodel.HSSFCell#CELL_TYPE_NUMERIC . 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: GenerateDoc.java    From danyuan-application with Apache License 2.0 6 votes vote down vote up 
@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
}
Example 2
Source File: ExcelOperator.java    From minsx-framework with Apache License 2.0 6 votes vote down vote up 
@SuppressWarnings("deprecation")
private static String getCellData(Cell cell) {
    String value = null;
    if (cell == null) {
        return null;
    }
    switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            value = cell.getCellFormula();
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
            value = dataFormatter.formatCellValue(cell);
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            value = null;
            break;
        case HSSFCell.CELL_TYPE_ERROR:
            value = "#ERROR#";
            break;
    }
    return value;
}
Example 3
Source File: XlsSessionReader.java    From conference-app with MIT License 6 votes vote down vote up 
private String getCellValue(HSSFCell cell) {
    String result = null;
    if (cell != null) {
        switch (cell.getCellType()) {

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

        case HSSFCell.CELL_TYPE_NUMERIC:
            result = "" + cell.getNumericCellValue();
            break;

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

        default:
        }
    }
    return result;
}
Example 4
Source File: ExcelQuery.java    From micro-integrator with Apache License 2.0 5 votes vote down vote up 
private String[] extractRowData(Row row) {
    if (row == null || row.getLastCellNum() == -1) {
        return null;
    }
    String[] data = new String[row.getLastCellNum()];
    Cell cell;
    for (int i = 0; i < data.length; i++) {
        cell = row.getCell(i);
        if (cell == null) {
            data[i] = "";
            continue;
        }
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                data[i] = cell.getRichStringCellValue().getString();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                data[i] = "";
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                data[i] = String.valueOf(cell.getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                data[i] = "{formula}";
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                data[i] = processNumericValue(cell.getNumericCellValue());
                break;
        }
    }
    return data;
}
Example 5
Source File: PoiUtil.java    From dk-fitting with Apache License 2.0 5 votes vote down vote up 
/**
 * 获取单元格中的内容 ,该犯法用于解析各种形式的数据
 */
private Object getCellString(HSSFCell cell) {
    Object result = null;
    if (cell != null) {
        int cellType = cell.getCellType();
        switch (cellType) {
            case HSSFCell.CELL_TYPE_STRING:
                result = cell.getRichStringCellValue().getString();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                result = cell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                result = cell.getNumericCellValue();
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                result = null;
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                result = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                result = null;
                break;
            default:
                break;
        }
    }
    return result;
}
Example 6
Source File: XLSFileNormalizer.java    From Knowage-Server with GNU Affero General Public License v3.0 5 votes vote down vote up 
private String parseCell(HSSFCell cell) {
	String valueField = null;
	
	if(cell == null) return "";
	
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_FORMULA:
		valueField = cell.getCellFormula().toString();
		break;
	
	case HSSFCell.CELL_TYPE_NUMERIC:
		Double numericValue = cell.getNumericCellValue();
		//testing if the double is an integer value
		if ((numericValue == Math.floor(numericValue)) && !Double.isInfinite(numericValue)) {
		    //the number is an integer, this will remove the .0 trailing zeros
			int numericInt = numericValue.intValue();
			valueField = String.valueOf(numericInt);
		} else {
			valueField = String.valueOf(cell.getNumericCellValue());

		}
		break;
	
	case HSSFCell.CELL_TYPE_STRING:
		valueField = cell.getStringCellValue();
		break;
	
	default:
}
	
	return valueField;
}
Example 7
Source File: ReadExcelUtil.java    From DWSurvey with GNU Affero General Public License v3.0 5 votes vote down vote up 
public static String getCellStringValue(HSSFCell cell) {
	String cellValue = "";
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_STRING:
		cellValue = cell.getStringCellValue();
		if (cellValue.trim().equals("") || cellValue.trim().length() <= 0) {
			cellValue = " ";
		}
		break;
	case HSSFCell.CELL_TYPE_NUMERIC:
		// cellValue = String.valueOf(cell.getNumericCellValue());
		DecimalFormat formatter = new DecimalFormat("######");
		cellValue = formatter.format(cell.getNumericCellValue());
		break;
	case HSSFCell.CELL_TYPE_FORMULA:
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cellValue = String.valueOf(cell.getNumericCellValue());
		break;
	case HSSFCell.CELL_TYPE_BLANK:
		cellValue = " ";
		break;
	case HSSFCell.CELL_TYPE_BOOLEAN:
		break;
	case HSSFCell.CELL_TYPE_ERROR:
		break;
	default:
		break;
	}
	return cellValue;
}
Example 8
Source File: POIUtils.java    From ermaster-b with Apache License 2.0 5 votes vote down vote up 
public static int getIntCellValue(HSSFSheet sheet, int r, int c) {
	HSSFRow row = sheet.getRow(r);
	if (row == null) {
		return 0;
	}
	HSSFCell cell = row.getCell(c);

	if (cell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
		return 0;
	}

	return (int) cell.getNumericCellValue();
}
Example 9
Source File: GenerateDoc.java    From danyuan-application with Apache License 2.0 4 votes vote down vote up 
/**
 * @方法名 copySheet
 * @功能 复制sheet
 * @参数 @param sheetFrom
 * @参数 @param sheetTo
 * @参数 @return
 * @返回 HSSFSheet
 * @author Administrator
 * @throws
 */
@SuppressWarnings("deprecation")
private static HSSFSheet copySheet(HSSFSheet sheetFrom, HSSFSheet sheetTo) {
	
	// 初期化
	CellRangeAddress region = null;
	Row rowFrom = null;
	Row rowTo = null;
	Cell cellFrom = null;
	Cell cellTo = null;
	// セル結合のコピー
	for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
		region = sheetFrom.getMergedRegion(i);
		
		if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
			sheetTo.addMergedRegion(region);
		}
	}
	
	// セルのコピー
	for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
		rowFrom = sheetFrom.getRow(intRow);
		rowTo = sheetTo.createRow(intRow);
		if (null == rowFrom) {
			continue;
		}
		rowTo.setHeight(rowFrom.getHeight());
		for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
			// セル幅のコピー
			sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
			sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
			cellFrom = rowFrom.getCell(intCol);
			cellTo = rowTo.createCell(intCol);
			if (null == cellFrom) {
				continue;
			}
			// セルスタイルとタイプのコピー
			cellTo.setCellStyle(cellFrom.getCellStyle());
			cellTo.setCellType(cellFrom.getCellType());
			// タイトル内容のコピー
			// 不同数据类型处理
			int cellFromType = cellFrom.getCellType();
			cellTo.setCellType(cellFromType);
			if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
				if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
					cellTo.setCellValue(cellFrom.getDateCellValue());
				} else {
					cellTo.setCellValue(cellFrom.getNumericCellValue());
				}
			} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
				cellTo.setCellValue(cellFrom.getRichStringCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
				// nothing21
			} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
				cellTo.setCellValue(cellFrom.getBooleanCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
				cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
			} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
				cellTo.setCellFormula(cellFrom.getCellFormula());
			} else { // nothing29
			}
		}
	}
	
	// 枠線の設定
	sheetTo.setDisplayGridlines(false);
	// sheetTo.setDisplayGuts(true);
	// sheetTo.setDisplayRowColHeadings(true);
	// 剪切
	// sheetTo.shiftRows(13, 15, 31, false, false, false);
	// Excelのズーム設定
	sheetTo.setZoom(85, 100);
	
	// シートを戻る。
	return sheetTo;
	
}
Example 10
Source File: QbeXLSExporter.java    From Knowage-Server with GNU Affero General Public License v3.0 4 votes vote down vote up 
protected int getCellTypeNumeric() {
	return HSSFCell.CELL_TYPE_NUMERIC;
}
Example 11
Source File: CrosstabXLSExporter.java    From Knowage-Server with GNU Affero General Public License v3.0 4 votes vote down vote up 
protected int getCellTypeNumeric() {
	return HSSFCell.CELL_TYPE_NUMERIC;
}
Example 12
Source File: ExcelUtil.java    From jeewx with Apache License 2.0 4 votes vote down vote up 
/**
	 * 读取 Excel文件内容
	 * 
	 * @param excel_name
	 * @return
	 * @throws Exception
	 */
	public static List<String[]> readExcel(String excel_name) throws Exception {
		// 结果集
		List<String[]> list = new ArrayList<String[]>();

		HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(
				excel_name));

		// 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
		HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);

		// 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
		for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
			HSSFRow hssfrow = hssfsheet.getRow(j);
			if(hssfrow!=null){
			int col = hssfrow.getPhysicalNumberOfCells();
			// 单行数据
			String[] arrayString = new String[col];
			for (int i = 0; i < col; i++) {
				HSSFCell cell = hssfrow.getCell(i);
				if (cell == null) {
					arrayString[i] = "";
				} else if (cell.getCellType() == 0) {
					// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
					if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { 
						  if (HSSFDateUtil.isCellDateFormatted(cell)) {    
						    Date d = cell.getDateCellValue();    
//						    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");    
						     DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						    arrayString[i] = formater.format(d);   
						   } else {    
						       arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue()+"";    
						}
					}
				} else {// 如果EXCEL表格中的数据类型为字符串型
					arrayString[i] = cell.getStringCellValue().trim();
				}
			}
			list.add(arrayString);
		}
		}
		return list;
	}
Example 13
Source File: XlsTable.java    From Leo with Apache License 2.0 4 votes vote down vote up 
public Object getValue(int row, String column) throws DataSetException {
    if (logger.isDebugEnabled())
        logger.debug("getValue(row={}, columnName={}) - start", Integer.toString(row), column);

    assertValidRowIndex(row);

    int columnIndex = getColumnIndex(column);
    HSSFCell cell = _sheet.getRow(row + 1).getCell(columnIndex);
    if (cell == null) {
        return null;
    }

    int type = cell.getCellType();
    switch (type) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        HSSFCellStyle style = cell.getCellStyle();
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
            return getDateValue(cell);
        } else if (XlsDataSetWriter.DATE_FORMAT_AS_NUMBER_DBUNIT.equals(style.getDataFormatString())) {
            // The special dbunit date format
            return getDateValueFromJavaNumber(cell);
        } else {
            return getNumericValue(cell);
            }

    case HSSFCell.CELL_TYPE_STRING:
        return cell.getRichStringCellValue().getString();

        case HSSFCell.CELL_TYPE_FORMULA:
        throw new DataTypeException("Formula not supported at row=" +
                row + ", column=" + column);

        case HSSFCell.CELL_TYPE_BLANK:
        return null;

        case HSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? Boolean.TRUE : Boolean.FALSE;

        case HSSFCell.CELL_TYPE_ERROR:
        throw new DataTypeException("Error at row=" + row +
                ", column=" + column);

        default:
        throw new DataTypeException("Unsupported type at row=" + row +
                ", column=" + column);
    }
}
Example 14
Source File: POIUtils.java    From ermaster-b with Apache License 2.0 4 votes vote down vote up 
public static void copyRow(HSSFSheet oldSheet, HSSFSheet newSheet,
		int oldRowNum, int newRowNum) {
	HSSFRow oldRow = oldSheet.getRow(oldRowNum);

	HSSFRow newRow = newSheet.createRow(newRowNum);

	if (oldRow == null) {
		return;
	}

	newRow.setHeight(oldRow.getHeight());

	if (oldRow.getFirstCellNum() == -1) {
		return;
	}

	for (int colNum = oldRow.getFirstCellNum(); colNum <= oldRow
			.getLastCellNum(); colNum++) {
		HSSFCell oldCell = oldRow.getCell(colNum);
		HSSFCell newCell = newRow.createCell(colNum);

		if (oldCell != null) {
			HSSFCellStyle style = oldCell.getCellStyle();
			newCell.setCellStyle(style);

			int cellType = oldCell.getCellType();
			newCell.setCellType(cellType);

			if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
				newCell.setCellValue(oldCell.getBooleanCellValue());

			} else if (cellType == HSSFCell.CELL_TYPE_FORMULA) {
				newCell.setCellFormula(oldCell.getCellFormula());

			} else if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
				newCell.setCellValue(oldCell.getNumericCellValue());

			} else if (cellType == HSSFCell.CELL_TYPE_STRING) {
				newCell.setCellValue(oldCell.getRichStringCellValue());
			}
		}
	}

	POIUtils.copyMergedRegion(newSheet, getMergedRegionList(oldSheet,
			oldRowNum), newRowNum);
}