Example usage for org.apache.poi.ss.util CellRangeAddressList CellRangeAddressList

List of usage examples for org.apache.poi.ss.util CellRangeAddressList CellRangeAddressList

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.util.*
  5. CellRangeAddressList
  6. CellRangeAddressList

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddressList CellRangeAddressList.

Prototype

public CellRangeAddressList(int firstRow, int lastRow, int firstCol, int lastCol)

Source Link

Document

Convenience constructor for creating a CellRangeAddressList with a single CellRangeAddress.

Usage

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void addQualityAssessmentValidation(Sheet p_sheet, int startRow, int lastRow, int startColumn,
        int lastColumn) {
    // Add category failure drop down list here.
    DataValidationHelper dvHelper = p_sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(QUALITY_ASSESSMENT_LIST);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, startColumn, lastColumn);
    DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);/*from w  w w.java  2  s  . co m*/
    p_sheet.addValidationData(validation);
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java

License:Apache License

private void addPriority(Sheet p_sheet, int startRow, int lastRow, int startColumn, int lastColumn) {
    // Add category failure drop down list here.
    HashMap priorities = new HashMap();
    DataValidationHelper dvHelper = p_sheet.getDataValidationHelper();
    priorities.putAll(IssueOptions.getAllPriorities());
    String[] allpriorities = new String[priorities.size()];
    priorities.values().toArray(allpriorities);
    DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(allpriorities);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, startColumn, lastColumn);
    DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);//from   ww w .  j av a2s  . co m
    p_sheet.addValidationData(validation);
}

From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.ReviewersCommentsReportGenerator.java

License:Apache License

/**
 * Add comment status drop down list. It is from "K8" to "Kn".
 * /*  w  w w.  ja  v a  2 s  .  c  o m*/
 * @param p_sheet
 * @param startRow
 * @param lastRow
 * @param startColumn
 * @param lastColumn
 */
private void addCommentStatusValidation(Sheet p_sheet, int startRow, int lastRow, int startColumn,
        int lastColumn) {
    List<String> status = new ArrayList<String>(IssueOptions.getAllStatus());
    String[] statusArray = new String[status.size()];
    status.toArray(statusArray);

    DataValidationHelper dvHelper = p_sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(statusArray);
    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, startColumn, lastColumn);
    DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);
    p_sheet.addValidationData(validation);
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?//w ww .  j a  v a2  s  . c  o m
 * 
 * @param row
 *            
 * @param column
 *            ?
 * @param val
 *            
 * @param align
 *            ??1?23??
 * @return ?
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType, String[] constraintValue) {
    Cell cell = row.createCell(column);
    CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : ""));
    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));
            }
        }

        if (constraintValue.length > 0) {
            // ?
            // ???
            CellRangeAddressList regions = new CellRangeAddressList(row.getRowNum(), row.getRowNum(), column,
                    column);
            // ?
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(constraintValue);
            DataValidationHelper help = sheet.getDataValidationHelper();

            // 
            DataValidation validation = help.createValidation(constraint, regions);

            validation.createErrorBox("", "");
            validation.setShowErrorBox(true);

            // sheet
            sheet.addValidationData(validation);
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

/**
 * It helps to update cell and format the excell based on the formatting defined in ExcelCell.{@link ExcelFormat}
 * /* w w w .j a v a2 s  .co m*/
 * @param cell
 * @param excell
 * @param style
 * @param font
 */
private static void updateCell(Cell cell, ExcelCell excell, Map<IndexedColors, CellStyle> s_cellStyle,
        Workbook workbook, Font font, Font invisibleFont) {
    if (excell != null) {

        // [1] format cell
        formatCell(workbook, cell, excell, s_cellStyle, font, invisibleFont);

        // [2] set enum
        if (!excell.isConsiderEnum()) {
            if (StringUtils.isNotBlank(excell.getDisplayText())) {
                cell.setCellValue(excell.getDisplayText());
            }
            if (!excell.isMultiSelect() && excell.isNumberValidation()) {
                addNumberValidation(cell);
            }
        } else {
            String[] list = (String[]) excell.getRestriction().getEnumValues()
                    .toArray(new String[excell.getRestriction().getEnumValues().size()]);

            SXSSFSheet sheet = (SXSSFSheet) cell.getSheet();

            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(list);
            CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                    cell.getColumnIndex(), cell.getColumnIndex());
            DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                    regions);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("Not Applicable", "Can't change the value");
            dataValidation.setShowErrorBox(true);

            try {
                if (isValidEnumList(list)) {
                    sheet.addValidationData(dataValidation);
                } else {
                    Sheet hidden = null;
                    String hiddenName = "hidden" + getHiddenIndex(excell.getReferenceText());
                    Workbook wBook = cell.getSheet().getWorkbook();
                    if (cell.getSheet().getWorkbook().getSheet(hiddenName) != null) {
                        hidden = wBook.getSheet(hiddenName);
                    } else {
                        hidden = wBook.createSheet(hiddenName);

                        for (int i = 0, length = list.length; i < length; i++) {
                            String name = list[i];
                            Row row = hidden.createRow(i);
                            Cell cell1 = row.createCell(0);
                            cell1.setCellValue(name);
                        }
                        Name namedCell = hidden.getWorkbook().getName(hiddenName);
                        namedCell = namedCell != null ? namedCell : hidden.getWorkbook().createName();
                        namedCell.setNameName(hiddenName);
                        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + list.length);
                    }

                    dvConstraint = (XSSFDataValidationConstraint) dvHelper
                            .createFormulaListConstraint(hiddenName);
                    dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
                    dataValidation.setShowErrorBox(true);

                    cell.getSheet().addValidationData(dataValidation);
                    wBook.setSheetHidden(wBook.getSheetIndex(hidden), true);

                }

            } catch (Exception e) {
                String msg = "Excel creation failed while building cell: " + excell.getDisplayText();
                throw new IllegalStateException(msg, e);
            }

            // cell.setCellValue(excelConfig.getDropDownMsg());
        }

    }

}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void addDataValidation(int rowNum, SXSSFSheet sheet) {

    String[] displayNameList = new String[] { "" };
    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    DataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createExplicitListConstraint(displayNameList);
    CellRangeAddressList regions = new CellRangeAddressList(rowNum, rowNum, 0, 1000);
    DataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
    dataValidation.setSuppressDropDownArrow(false);
    dataValidation.createErrorBox("Not Applicable", "Can't change the value");
    dataValidation.setShowErrorBox(true);
    sheet.addValidationData(dataValidation);
}

From source file:com.plugin.excel.util.ExcelFileHelper.java

License:Apache License

private static void addNumberValidation(Cell cell) {

    if (cell != null) {

        Sheet sheet = cell.getSheet();/*  w w w .  ja  va  2  s . c  om*/
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createNumericConstraint(ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, "1.00",
                        "1000000000000.00");
        CellRangeAddressList addressList = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                cell.getColumnIndex(), cell.getColumnIndex());
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint,
                addressList);
        validation.setErrorStyle(ErrorStyle.STOP);
        validation.createErrorBox("Error", "Only numeric values are allowed");
        validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }

}

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

License:Open Source License

/**
 * ?//from w w w. ja va2  s  . c  o m
 * @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:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private void insertDropBox(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col,
        String ref) {/*  ww  w .j  a  v  a 2s . c o m*/
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createFormulaListConstraint(ref);
    CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
    XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
    validation.setShowErrorBox(true);
    validation.setSuppressDropDownArrow(true);
    validation.setShowPromptBox(true);
    sheetTracing.addValidationData(validation);
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java

License:Open Source License

private void insertCondition(XSSFDataValidationHelper dvHelper, XSSFSheet sheetTracing, int row, int col,
        String min, String max) {
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createIntegerConstraint(OperatorType.BETWEEN, min, max);
    // dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(new String[]{"0000011", "0000021", "0000031"});
    CellRangeAddressList addressList = new CellRangeAddressList(row, row, col, col);
    XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
    validation.setShowErrorBox(true);//from  w  w  w .j  a v  a  2  s. co  m
    validation.setSuppressDropDownArrow(true);
    validation.setShowPromptBox(true);
    sheetTracing.addValidationData(validation);
}