org.apache.poi.xssf.usermodel.XSSFCell#CELL_TYPE

Source File: ExcelServiceImpl.java From agile-service-old with Apache License 2.0

6 votes

private Integer getRealRowCount(Sheet sheet) {
    Integer count = 0;
    for (int r = 1; r <= sheet.getPhysicalNumberOfRows(); r++) {
        Row row = sheet.getRow(r);
        if (row == null || (((row.getCell(0) == null || row.getCell(0).toString().equals("") || row.getCell(0).getCellType() == XSSFCell.CELL_TYPE_BLANK)) &&
                (row.getCell(1) == null || row.getCell(1).toString().equals("") || row.getCell(1).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(2) == null || row.getCell(2).toString().equals("") || row.getCell(2).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(3) == null || row.getCell(3).toString().equals("") || row.getCell(3).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(4) == null || row.getCell(4).toString().equals("") || row.getCell(4).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(5) == null || row.getCell(5).toString().equals("") || row.getCell(5).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(6) == null || row.getCell(6).toString().equals("") || row.getCell(6).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(7) == null || row.getCell(7).toString().equals("") || row.getCell(7).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(8) == null || row.getCell(8).toString().equals("") || row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_BLANK) &&
                (row.getCell(9) == null || row.getCell(9).toString().equals("") || row.getCell(9).getCellType() == XSSFCell.CELL_TYPE_BLANK))) {
            continue;
        }
        count++;
    }
    return count;
}

Source File: ExcelDataProvider.java From xframium-java with GNU General Public License v3.0

6 votes

/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf( ( int ) cell.getNumericCellValue() );
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();

		}
	}
	return null;
}

Source File: ExcelCloudProvider.java From xframium-java with GNU General Public License v3.0

6 votes

/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf( cell.getNumericCellValue() );
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();
		}
	}
	return null;
}

Source File: ExcelApplicationProvider.java From xframium-java with GNU General Public License v3.0

6 votes

/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf( cell.getNumericCellValue() );
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();
		}
	}
	return null;
}

Source File: ExcelContentProvider.java From xframium-java with GNU General Public License v3.0

6 votes

/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf( cell.getNumericCellValue() );
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();
		}
	}
	return null;
}

Source File: ExcelElementProvider.java From xframium-java with GNU General Public License v3.0

6 votes

/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf( cell.getNumericCellValue() );
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();
		}
	}
	return null;
}

Source File: ExcelPageDataProvider.java From xframium-java with GNU General Public License v3.0

6 votes

/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
			{
			    String useValue = String.valueOf( cell.getNumericCellValue() );
			    if ( useValue.endsWith( ".0" ) )
			        return useValue.split( "\\." )[0];
			    else
			        return useValue;
			}
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();
		}
	}
	return null;
}

Source File: ExcelKeyWordProvider.java From xframium-java with GNU General Public License v3.0

6 votes

private String getCellValue( XSSFCell cell )
{
    if (cell != null )
    {
        switch (cell.getCellType())
        {
            case XSSFCell.CELL_TYPE_BLANK:
                return null;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                return String.valueOf( cell.getBooleanCellValue() );
            case XSSFCell.CELL_TYPE_NUMERIC:
                return String.valueOf( cell.getNumericCellValue() );
            case XSSFCell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().toString();
        }
    }
    return null;
}

Source File: ExcelUtil.java From springboot-learn with MIT License

5 votes

private Object getCellValue(Cell cell) {
    Object value;
    DecimalFormat df = new DecimalFormat("0");// 格式化 number String
    // 字符
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
    DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
    switch (cell.getCellType()) {
        case XSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue();
            break;
        case XSSFCell.CELL_TYPE_NUMERIC:
            if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = nf.format(cell.getNumericCellValue());
            } else {
                value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
            }
            break;
        case XSSFCell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case XSSFCell.CELL_TYPE_BLANK:
            value = "";
            break;
        default:
            value = cell.toString();
    }
    return value;
}

Source File: ExcelServiceImpl.java From agile-service-old with Apache License 2.0

4 votes

private Map<Integer, String> checkRule(Long projectId, Row row, List<String> issueTypeList, List<String> priorityList, List<String> versionList, Map<String, IssueTypeVO> issueTypeMap, List<String> componentList, List<String> sprintList) {
    Map<Integer, String> errorMessage = new HashMap<>();
    // check summary
    if (row.getCell(0) == null || row.getCell(0).toString().equals("") || row.getCell(0).getCellType() == XSSFCell.CELL_TYPE_BLANK) {
        errorMessage.put(0, "概要不能为空");
    } else if (row.getCell(0).toString().length() > 44) {
        errorMessage.put(0, "概要过长");
    }
    // check priority
    if (row.getCell(2) == null || row.getCell(2).toString().equals("") || row.getCell(2).getCellType() == XSSFCell.CELL_TYPE_BLANK) {
        errorMessage.put(2, "优先级不能为空");
    } else if (!priorityList.contains(row.getCell(2).toString())) {
        errorMessage.put(2, "优先级输入错误");
    }
    // check issue type
    if (row.getCell(3) == null || row.getCell(3).toString().equals("") || row.getCell(3).getCellType() == XSSFCell.CELL_TYPE_BLANK) {
        errorMessage.put(3, "问题类型不能为空");
    } else if (!issueTypeList.contains(row.getCell(3).toString())) {
        errorMessage.put(3, "问题类型输入错误");
    }
    // check story point
    if (!(row.getCell(4) == null || row.getCell(4).toString().equals("") || row.getCell(4).getCellType() == XSSFCell.CELL_TYPE_BLANK)) {
        String storyPointStr = row.getCell(4).toString().trim();
        if (storyPointStr.length() > 3) {
            errorMessage.put(4, "请输入正确的位数");
        } else if (!NumberUtil.isNumeric(storyPointStr)) {
            errorMessage.put(4, "请输入数字");
        } else {
            if (NumberUtil.isInteger(storyPointStr) || NumberUtil.canParseInteger(storyPointStr)) {
                if (storyPointStr.trim().length() > 3) {
                    errorMessage.put(4, "最大支持3位整数");
                } else if (storyPointStr.trim().length() > 1 && "0".equals(storyPointStr.trim().substring(0, 0))) {
                    errorMessage.put(4, "请输入正确的整数");
                }
            } else if (!"0.5".equals(storyPointStr)) {
                errorMessage.put(4, "小数只支持0.5");
            }
        }
    }
    // check remain time
    if (!(row.getCell(5) == null || row.getCell(5).toString().equals("") || row.getCell(5).getCellType() == XSSFCell.CELL_TYPE_BLANK)) {
        String remainTime = row.getCell(5).toString().trim();
        if (remainTime.length() > 3) {
            errorMessage.put(5, "请输入正确的位数");
        } else if (!NumberUtil.isNumeric(remainTime)) {
            errorMessage.put(5, "请输入数字");
        } else {
            if (NumberUtil.isInteger(remainTime) || NumberUtil.canParseInteger(remainTime)) {
                if (remainTime.trim().length() > 3) {
                    errorMessage.put(5, "最大支持3位整数");
                } else if (remainTime.trim().length() > 1 && "0".equals(remainTime.trim().substring(0, 0))) {
                    errorMessage.put(5, "请输入正确的整数");
                }
            } else if (!"0.5".equals(remainTime)) {
                errorMessage.put(5, "小数只支持0.5");
            }
        }
    }
    // check version
    if (!(row.getCell(6) == null || row.getCell(6).toString().equals("") || row.getCell(6).getCellType() == XSSFCell.CELL_TYPE_BLANK)) {
        if (!versionList.contains(row.getCell(6).toString())) {
            errorMessage.put(6, "请输入正确的版本");
        }
    }
    // check epic name
    if (!(row.getCell(3) == null || row.getCell(3).toString().equals("") || row.getCell(3).getCellType() == XSSFCell.CELL_TYPE_BLANK)
            && issueTypeList.contains(row.getCell(3).toString())
            && ISSUE_EPIC.equals(issueTypeMap.get(row.getCell(3).toString()).getTypeCode())) {
        if (row.getCell(7) == null || row.getCell(7).toString().equals("") || row.getCell(7).getCellType() == XSSFCell.CELL_TYPE_BLANK) {
            errorMessage.put(7, "史诗名称不能为空");
        } else {
            String epicName = row.getCell(7).toString().trim();
            if (epicName.length() > 10) {
                errorMessage.put(7, "史诗名称过长");
            } else if (!checkEpicNameExist(projectId, epicName)) {
                errorMessage.put(7, "史诗名称重复");
            }
        }
    }
    // check component
    if (!(row.getCell(8) == null || row.getCell(8).toString().equals("") || row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_BLANK)) {
        if (!componentList.contains(row.getCell(8).toString())) {
            errorMessage.put(8, "请输入正确的模块");
        }
    }
    // check sprint
    if (!(row.getCell(9) == null || row.getCell(9).toString().equals("") || row.getCell(9).getCellType() == XSSFCell.CELL_TYPE_BLANK)) {
        if (!sprintList.contains(row.getCell(9).toString())) {
            errorMessage.put(9, "请输入正确的冲刺");
        }
    }
    return errorMessage;
}

Source File: ExcelServiceImpl.java From poi with Apache License 2.0

4 votes

/**
 * 读取 office 2003 excel
 */
private List<List<Object>> readExcel2003(File file) throws IOException {
	List<List<Object>> list = new LinkedList<List<Object>>();
	HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
	HSSFSheet sheet = hwb.getSheetAt(0);
	Object value = null;
	HSSFRow row = null;
	HSSFCell cell = null;
	int counter = 0;
	for (int i = sheet.getFirstRowNum(); counter < sheet
			.getPhysicalNumberOfRows(); i++) {
		row = sheet.getRow(i);
		if (row == null) {
			continue;
		} else {
			counter++;
		}
		List<Object> linked = new LinkedList<Object>();
		for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
			cell = row.getCell(j);
			if (cell == null) {
				continue;
			}
			DecimalFormat df = new DecimalFormat("0");// 格式化 number String
			// 字符
			SimpleDateFormat sdf = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
			DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
			switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
				System.out.println(i + "行" + j + " 列 is String type"
						+ "\tValue:" + value);
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				if ("@".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("General".equals(cell.getCellStyle()
						.getDataFormatString())) {
					value = nf.format(cell.getNumericCellValue());
				} else {
					value = sdf.format(HSSFDateUtil.getJavaDate(cell
							.getNumericCellValue()));
				}
				System.out.println(i + "行" + j
						+ " 列 is Number type ; DateFormt:"
						+ cell.getCellStyle().getDataFormatString()
						+ "\tValue:" + value);
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				value = cell.getBooleanCellValue();
				System.out.println(i + "行" + j + " 列 is Boolean type"
						+ "\tValue:" + value);
				break;
			case XSSFCell.CELL_TYPE_BLANK:
				value = "";
				System.out.println(i + "行" + j + " 列 is Blank type"
						+ "\tValue:" + value);
				break;
			default:
				value = cell.toString();
				System.out.println(i + "行" + j + " 列 is default type"
						+ "\tValue:" + value);
			}
			if (value == null || "".equals(value)) {
				continue;
			}
			linked.add(value);
		}
		list.add(linked);
	}
	return list;
}

Source File: ExcelServiceImpl.java From poi with Apache License 2.0

4 votes

/**
 * 读取Office 2007 excel
 */
private List<List<Object>> readExcel2007(File file) throws IOException {
	List<List<Object>> list = new LinkedList<List<Object>>();
	// 构造 XSSFWorkbook 对象，strPath 传入文件路径
	XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
	// 读取第一章表格内容
	XSSFSheet sheet = xwb.getSheetAt(0);
	Object value = null;
	XSSFRow row = null;
	XSSFCell cell = null;
	int counter = 0;
	for (int i = sheet.getFirstRowNum(); counter < sheet
			.getPhysicalNumberOfRows(); i++) {
		row = sheet.getRow(i);
		if (row == null) {
			continue;
		} else {
			counter++;
		}
		List<Object> linked = new LinkedList<Object>();
		for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
			cell = row.getCell(j);
			if (cell == null) {
				continue;
			}
			DecimalFormat df = new DecimalFormat("0");// 格式化 number String
			// 字符
			SimpleDateFormat sdf = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
			DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
			switch (cell.getCellType()) {
			case XSSFCell.CELL_TYPE_STRING:
				System.out.println(i + "行" + j + " 列 is String type");
				value = cell.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				System.out.println(i + "行" + j
						+ " 列 is Number type ; DateFormt:"
						+ cell.getCellStyle().getDataFormatString());
				if ("@".equals(cell.getCellStyle().getDataFormatString())) {
					value = df.format(cell.getNumericCellValue());
				} else if ("General".equals(cell.getCellStyle()
						.getDataFormatString())) {
					value = nf.format(cell.getNumericCellValue());
				} else {
					value = sdf.format(HSSFDateUtil.getJavaDate(cell
							.getNumericCellValue()));
				}
				break;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				System.out.println(i + "行" + j + " 列 is Boolean type");
				value = cell.getBooleanCellValue();
				break;
			case XSSFCell.CELL_TYPE_BLANK:
				System.out.println(i + "行" + j + " 列 is Blank type");
				value = "";
				break;
			default:
				System.out.println(i + "行" + j + " 列 is default type");
				value = cell.toString();
			}
			if (value == null || "".equals(value)) {
				continue;
			}
			linked.add(value);
		}
		list.add(linked);
	}
	return list;
}

Java Code Examples for org.apache.poi.xssf.usermodel.XSSFCell#CELL_TYPE_BLANK