List of usage examples for org.apache.poi.ss.usermodel DataValidationHelper createValidation
DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList);
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 w ww . jav a 2 s .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". * /* ww w.ja v a2s. 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
/** * ?//from w w w . jav a 2 s . c om * * @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} * /*from w ww. j av a2s . c o 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();/*from w w w .ja v a 2 s .c o m*/ 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:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelGeneratorUtils.java
License:Open Source License
/** * Creates a dropdown box for each cell in the specified {@code addressList}. * //from w w w .j a v a2 s. co m * @param sheet the sheet to add the dropdown box to * @param addressList the cell range to add the dropdown box to * @param namesListFormula the formula name of the values to be shown in dropdown box */ public static void createDropdown(Sheet sheet, CellRangeAddressList addressList, String namesListFormula) { DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); DataValidationConstraint dvConstraint = dataValidationHelper.createFormulaListConstraint(namesListFormula); DataValidation dataValidation = dataValidationHelper.createValidation(dvConstraint, addressList); // There is an error in the interpretation of the argument of setSuppressDropDownArrow in POI 3.9 // for XSSF Workbooks. The arrow is suppressed if the argument is set to 'false' instead of 'true'. // In HSSF Workbooks the method works as designed. // Luckily in both cases the default behavior is, that the arrow is displayed. So we can skip the explicit setting of this behavior. // SKIP THIS: dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); }
From source file:de.iteratec.iteraplan.businesslogic.exchange.elasticExcel.util.ExcelGeneratorUtils.java
License:Open Source License
/** * Creates a dropdown box for each cell in the specified {@code addressList}. * /*from w ww . jav a 2s .c om*/ * @param sheet the sheet to add the dropdown box to * @param addressList the cell range to add the dropdown box to * @param values the values to be shown in dropdown box */ public static void createDropdownWithValues(Sheet sheet, CellRangeAddressList addressList, List<String> values) { DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper(); String[] valuesList = values.toArray(new String[values.size()]); DataValidationConstraint dvConstraint = dataValidationHelper.createExplicitListConstraint(valuesList); DataValidation dataValidation = dataValidationHelper.createValidation(dvConstraint, addressList); // There is an error in the interpretation of the argument of setSuppressDropDownArrow in POI 3.9 // for XSSF Workbooks. The arrow is suppressed if the argument is set to 'false' instead of 'true'. // In HSSF Workbooks the method works as designed. // Luckily in both cases the default behavior is, that the arrow is displayed. So we can skip the explicit setting of this behavior. // SKIP THIS: dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); }
From source file:de.jlo.talendcomp.excel.SpreadsheetOutput.java
License:Apache License
private void createNewAppendingDataValidationAsCopy(Sheet sheet, DataValidation originalDv, int lastRowIndex) { CellRangeAddressList originalAl = originalDv.getRegions(); CellRangeAddressList appendingAddressList = createNewAppendingCellRangeAddressList(originalAl, lastRowIndex);/*from w w w . j a va 2 s. c o m*/ DataValidationHelper dvHelper = sheet.getDataValidationHelper(); DataValidation newValidation = dvHelper.createValidation(originalDv.getValidationConstraint(), appendingAddressList); newValidation.setSuppressDropDownArrow(originalDv.getSuppressDropDownArrow()); newValidation.setShowErrorBox(originalDv.getShowErrorBox()); newValidation.setShowPromptBox(originalDv.getShowPromptBox()); newValidation.setEmptyCellAllowed(originalDv.getEmptyCellAllowed()); newValidation.setErrorStyle(originalDv.getErrorStyle()); String promptBoxText = originalDv.getPromptBoxText(); String promptBoxTitle = originalDv.getPromptBoxTitle(); String errorBoxText = originalDv.getErrorBoxText(); String errorBoxTitle = originalDv.getErrorBoxTitle(); if (promptBoxTitle != null && promptBoxText != null) { newValidation.createPromptBox(promptBoxTitle, promptBoxText); } if (errorBoxTitle != null && errorBoxText != null) { newValidation.createErrorBox(errorBoxTitle, errorBoxText); } sheet.addValidationData(newValidation); }
From source file:edu.casetools.rcase.extensions.excel.control.Exporter.java
License:Open Source License
private DataValidation createExcel2007CellStyle(Sheet sheet, String[] possibleValues, CellRangeAddressList addressList) { DataValidation validation;//from w w w. java2 s . c om DataValidationConstraint constraint; DataValidationHelper validationHelper; validationHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); constraint = validationHelper.createExplicitListConstraint(possibleValues); validation = validationHelper.createValidation(constraint, addressList); validation.setEmptyCellAllowed(false); validation.setShowErrorBox(true); validation.setErrorStyle(0); return validation; }