Example usage for org.apache.poi.hssf.usermodel HSSFDataValidation HSSFDataValidation

List of usage examples for org.apache.poi.hssf.usermodel HSSFDataValidation HSSFDataValidation

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFDataValidation
  6. HSSFDataValidation

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFDataValidation HSSFDataValidation.

Prototype

public HSSFDataValidation(CellRangeAddressList regions, DataValidationConstraint constraint)

Source Link

Document

Constructor which initializes the cell range on which this object will be applied

Usage

From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java

License:Apache License

private void setExplicitListConstraint(HSSFSheet sheet, String[] list, int firstRow, int lastRow, int firstCol,
        int lastCol) {
    //????//w ww  .ja va  2s . c o  m
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    validation.setEmptyCellAllowed(true);
    validation.setSuppressDropDownArrow(false);
    sheet.addValidationData(validation);
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ???/*from  w w  w .j a  v a2 s . c  o m*/
 * @param dataBean
 * @param sheet
 * @param columnIndex
 * @param filedName
 */
public void DicCodeValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) {
    DicValidateConfig config = dataBean.getValidateConfig(filedName);
    String dicCode = config.dicCode();
    Set<String> set = dicCodePool.getDicValueSet().get(dicCode);
    if (ObjectHelper.isEmpty(set)) {
        log.error("?{}", dicCode);
        return;
    }
    if (config.columnName() != 0) {//?
        createDicCodeSheet(config, sheet, columnIndex, set);
    } else {
        String[] strings = set.toArray(new String[] {});
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(strings);
        // ??,????
        CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
                columnIndex, columnIndex);
        // ?
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        setValidationTip(data_validation_list, config);
        sheet.addValidationData(data_validation_list);
    }
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ??//  w ww  . ja  va2s.c  o  m
 * @param config
 * @param sheet
 * @param columnIndex
 * @param valueSet
 */
protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex,
        Set<String> valueSet) {
    Workbook workbook = sheet.getWorkbook();
    Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME);
    if (codeSheet == null) {
        log.debug("?Sheet?Sheet");
        codeSheet = workbook.createSheet(DICCODE_SHEET_NAME);
    }
    int codeIndex = config.columnName() - 'A';
    log.debug("codeIndex{}", codeIndex);
    if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) {
        log.debug("????");
        int i = 0;
        for (String dic : valueSet) {
            Row row = codeSheet.getRow(i);
            if (row == null)
                row = codeSheet.createRow(i);
            Cell cell = row.createCell(codeIndex);
            cell.setCellValue(dic);
            i++;
        }
    } else {
        log.debug("????");
    }
    Name name = workbook.getName(config.columnName() + "");
    if (name == null || name.isDeleted()) {
        log.debug("?Name?Name");
        name = workbook.createName();
        name.setNameName(config.columnName() + "");
    }
    name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$"
            + valueSet.size());
    DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName());
    CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN);
    setValidationTip(validation, config);
    sheet.addValidationData(validation);
    log.debug("??");
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ?/* www .  ja  v  a  2s. c o m*/
 * @param dataBean
 * @param sheet
 * @param columnIndex
 * @param filedName
 */
public void DateValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) {
    DateValidateConfig config = dataBean.getValidateConfig(filedName);
    DVConstraint dateConstraint = DVConstraint.createDateConstraint(
            DataValidationConstraint.OperatorType.BETWEEN, config.min(), config.max(), config.format());
    // ??,????
    CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    // ?
    DataValidation data_validation_list = new HSSFDataValidation(regions, dateConstraint);
    setValidationTip(data_validation_list, config);
    sheet.addValidationData(data_validation_list);
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ?/*from  w  w w . j  a v  a  2  s . c om*/
 * @param dataBean
 * @param sheet
 * @param columnIndex
 * @param filedName
 */
public void NumericValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) {
    NumericValidateConfig config = dataBean.getValidateConfig(filedName);
    DVConstraint constraint = DVConstraint.createNumericConstraint(
            DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.BETWEEN,
            config.min(), config.max());
    // ??,????
    CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    // ?
    DataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
    setValidationTip(data_validation_list, config);
    sheet.addValidationData(data_validation_list);
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ?/*from   www  .j a  va  2  s.co  m*/
 * @param dataBean
 * @param sheet
 * @param columnIndex
 * @param filedName
 */
public void IntegerValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) {
    IntValidateConfig config = dataBean.getValidateConfig(filedName);
    DVConstraint constraint = DVConstraint.createNumericConstraint(
            DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN,
            config.min(), config.max());
    // ??,????
    CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
            columnIndex, columnIndex);
    // ?
    DataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
    setValidationTip(data_validation_list, config);
    sheet.addValidationData(data_validation_list);
}

From source file:com.dituiba.excel.DefaultValidateAdapter.java

License:Apache License

/**
 * ?//from   w  ww.  ja va 2s . co  m
 * @param dataBean
 * @param sheet
 * @param columnIndex
 * @param filedName
 */
public void TextValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) {
    TextValidateConfig config = dataBean.getValidateConfig(filedName);
    if (config.length() != 0) {
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.TEXT_LENGTH,
                DVConstraint.OperatorType.LESS_OR_EQUAL, config.length() + "", null);
        // ??,????
        CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE,
                columnIndex, columnIndex);
        // ?
        DataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        setValidationTip(data_validation_list, config);
        sheet.addValidationData(data_validation_list);
    }
}

From source file:com.sccl.attech.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?/*from   w  w  w . j a  v a2s . c  om*/
 * @param row 
 * @param column ?
 * @param val 
 * @param align ??1?23??
 * @return ?
 */
public Cell addCellStyle(Row row, int column, Object val, int align, Class<?> fieldType) {
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    style.setWrapText(true);
    try {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class) {
                cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
            } else {
                cell.setCellValue((String) Class
                        .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                "fieldtype." + val.getClass().getSimpleName() + "Type"))
                        .getMethod("setValue", Object.class).invoke(null, val));
            }
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    if (column == 8) {
        CellRangeAddressList regions = new CellRangeAddressList(25, 25, 8, 8);
        // ?  
        DVConstraint constraint = DVConstraint
                .createExplicitListConstraint(new String[] { "2", "3", "" });
        //   
        HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);
        // sheet  
        data_validation.createErrorBox("Error", "Error");
        data_validation.createPromptBox("", null);
        sheet.addValidationData(data_validation);
    }
    cell.setCellStyle(style);
    return cell;
}

From source file:edu.casetools.rcase.extensions.excel.control.Exporter.java

License:Open Source License

/**
 * Creates the cell style.// w  ww. jav a  2 s  .  c  o m
 *
 * @param sheet
 *            the sheet where to create the style.
 * @param rowNumber
 *            the row number where to create the style.
 * @param columnNumber
 *            the column number where to create the style.
 * @param cell
 *            the cell where to create the style.
 * @param cellTable
 *            the cell table where to create the style.
 * @return the column number
 */
protected int createCellStyle(Sheet sheet, int rowNumber, int columnNumber, Cell cell, TableCell cellTable) {
    int auxiliarColumnNumber = columnNumber;
    cell.setCellStyle(this.contentCellStyle);
    cell.setCellValue(cellTable.getValue());

    String[] possibleValues = cellTable.getPossibleValues();

    if (0 != possibleValues.length) {
        DataValidation validation;
        DataValidationConstraint constraint;
        CellRangeAddressList addressList = new CellRangeAddressList(rowNumber, rowNumber, auxiliarColumnNumber,
                auxiliarColumnNumber);
        if (this.version == SpreadsheetVersion.EXCEL2007) {
            validation = createExcel2007CellStyle(sheet, possibleValues, addressList);
        } else {
            constraint = DVConstraint.createExplicitListConstraint(possibleValues);
            validation = new HSSFDataValidation(addressList, constraint);
        }

        if (validation != null) {
            sheet.addValidationData(validation);
        }
    }
    auxiliarColumnNumber++;
    return auxiliarColumnNumber;
}

From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

/**
  *//*  w ww  .j a v a 2s  .co  m*/
public static void setOneLineHeader(HSSFSheet sheet, int iRowNum, ArrayList<String> al) {
    LogMaker.makelog("Creating Header in Excel");
    try {
        HSSFRow row = sheet.createRow(iRowNum);
        row.setHeight((short) 600);
        int i = 0;
        for (String str : al) {
            row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(str);
            if (i == 1) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            } else if (i == 2) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            } else if (i == 3) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            }

            else {
                try {
                    row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                } catch (NullPointerException e) {
                    e.printStackTrace();

                }

            }

            sheet.autoSizeColumn((short) i, true);
            i++;

        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}