Example usage for org.apache.poi.xssf.usermodel XSSFDataValidationHelper createNumericConstraint

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFDataValidationHelper createNumericConstraint.

Prototype

public DataValidationConstraint createNumericConstraint(int validationType, int operatorType, String formula1,
            String formula2) 

Source Link

Usage

From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java

License:Open Source License

/**
 * Excel Workbook?.// w ww . ja  v  a 2  s.  co m
 * 
 * @param colDesc 1717?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 */
public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    //???1717
    //TODO: ?
    List<String> colTitleList = getColumnTitleList(colDesc);
    List<String> colFieldList = getColumnFieldList(colDesc);

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    //?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    //CellStyle strCellStyle = getStringCellStyle(wb);

    //??1717
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    //
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < colTitleList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colTitleList.get(i));
    }

    //?1717
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            Object value = BeanConvertUtil.getProperty(obj, fieldName);
            //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            //??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue((Date) value);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    //
    for (int i = 0; i < colTitleList.size(); i++) {
        //??
        //sheet.autoSizeColumn(i);
    }

    return wb;
}

---

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * Excel Workbook?./*  w  ww. j av a  2 s  . c o m*/
 * 
 * @param colDesc
 *            "?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 */
public static XSSFWorkbook createHSSFWorkbook(List<String> titlesList, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {

    // ???
    List<String> colFieldList = getColumnFieldList(titlesList);

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    // ?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    // ??1717
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // 
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < titlesList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(titlesList.get(i));
    }
    ClassInfo classInfo = null;
    // ?
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        if (classInfo == null)
            classInfo = ClassUtil.getClassInfo(obj.getClass());
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(fieldName);
            Object value = reflexField.getValue(obj);

            // ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            // Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            // ??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date || value instanceof Timestamp) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue(sdf.format((Date) value));
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    // ??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    // 
    for (int i = 0; i < titlesList.size(); i++) {
        // ??
        sheet.autoSizeColumn(i);
    }

    return wb;
}

---

From source file:com.frameworkset.platform.util.POIExcelUtil2007.java

License:Open Source License

/**
 * Excel Workbook?./*from  w ww .j  av  a 2s. c  om*/
 * 
 * @param colDesc ?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 */
public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    //???
    //TODO: ?
    List<String> colTitleList = getColumnTitleList(colDesc);
    List<String> colFieldList = getColumnFieldList(colDesc);

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    //?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    //CellStyle strCellStyle = getStringCellStyle(wb);

    //??
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    //
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < colTitleList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colTitleList.get(i));
    }

    //?
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            Object value = BeanConvertUtil.getProperty(obj, fieldName);
            //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            //??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue((Date) value);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    //
    for (int i = 0; i < colTitleList.size(); i++) {
        //??
        //sheet.autoSizeColumn(i);
    }

    return wb;
}

---