List of usage examples for org.apache.poi.ss.usermodel Sheet getDataValidationHelper
public DataValidationHelper getDataValidationHelper();
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(); 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);//from w ww . j a v a 2 s .c om 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}. * // w w w .ja v a 2 s . c o 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}. * /* ww w . j a v a2 s . c o m*/ * @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);// w w w . ja v a2 s .c om 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:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java
License:Open Source License
public String dowmloadTemplate() { try {/*from ww w .j ava2 s .c o m*/ ClassLoader classLoader = this.getClass().getClassLoader(); File file = new File(this.getClass().getResource("/template/participants-template.xlsm").getFile()); String path = new File(".").getCanonicalPath(); String real_path = path + "/src/main/resources/template/participants-template.xlsm"; String path_ = config.getUploadsBaseFolder() + "/participants-template.xlsm"; FileInputStream fileInput = new FileInputStream(path_); XSSFWorkbook wb = new XSSFWorkbook(fileInput); DataValidationConstraint constraintCountries = null; DataValidationConstraint constraintHighestDegree = null; DataValidationHelper validationHelper = null; Sheet sheet1 = wb.getSheetAt(0); XSSFSheet sheet2 = wb.getSheet("countries"); String dataValidationCountryName = "countriesLis"; // se traen los datos desde la DB con los que se desean crear las listas para los data validator y se rellenan los // arreglos que permitaran escribir los datos en el template List<LocElement> countryList = new ArrayList<>(locElementService.findAll().stream() .filter(le -> le.isActive() && (le.getLocElementType() != null) && (le.getLocElementType().getId() == 2)) .collect(Collectors.toList())); Collections.sort(countryList, (c1, c2) -> c1.getName().compareTo(c2.getName())); // arreglo usado para escribir la data de countries al template String[] countries = new String[countryList.size()]; for (int i = 0; i < countryList.size(); i++) { countries[i] = countryList.get(i).getName() + " - " + countryList.get(i).getIsoAlpha2(); } List<CapdevHighestDegree> highestDegreeList = new ArrayList<>(capdevHighestDegreeService.findAll() .stream().filter(h -> h.getName() != null).collect(Collectors.toList())); Collections.sort(highestDegreeList, (c1, c2) -> c1.getName().compareTo(c2.getName())); // arreglo usado para escribir la data de highest degree al template String[] highestDegree = new String[highestDegreeList.size()]; for (int i = 0; i < highestDegreeList.size(); i++) { highestDegree[i] = highestDegreeList.get(i).getId() + "- " + highestDegreeList.get(i).getName() + " (" + highestDegreeList.get(i).getAcronym() + ")"; } validationHelper = sheet1.getDataValidationHelper(); // se configuran las coordenas donde se desea pegar el data validator en la sheet1 del template CellRangeAddressList addressListCountry = new CellRangeAddressList(10, 1000, 3, 3); CellRangeAddressList addressListHighestDegree = new CellRangeAddressList(10, 1000, 4, 4); // se crean cada uno de los data validator this.createDataValidator(wb, sheet2, countries, dataValidationCountryName); // se configuran y pegan cada uno de los data validator DataValidation dataValidationCountry = this.setDataValidator(dataValidationCountryName, validationHelper, addressListCountry, constraintCountries); // set de cada data davilidator al sheet1 del template sheet1.addValidationData(dataValidationCountry); ByteArrayOutputStream fileOut = new ByteArrayOutputStream(); wb.write(fileOut); wb.close(); inputStream = new ByteArrayInputStream(fileOut.toByteArray()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java
License:Open Source License
public void createFile() throws FileNotFoundException { try {/*w w w .j a va 2s . co m*/ DataValidation dataValidationCountries = null; DataValidation dataValidationInstitutions = null; DataValidation dataValidationCountryOfInstitutions = null; DataValidationConstraint constraintCountries = null; DataValidationConstraint constraintInstitutions = null; DataValidationConstraint constraintCountryOfInstitutions = null; DataValidationHelper validationHelper = null; final String path = new File(".").getCanonicalPath(); final String filePath = "C:\\Users\\logonzalez\\Downloads\\participants-template.xlsm"; final File file = new File(filePath); final FileInputStream fileInput = new FileInputStream(file); final XSSFWorkbook wb = new XSSFWorkbook(fileInput); final Sheet sheet1 = wb.getSheetAt(0); final XSSFSheet sheet2 = wb.getSheet("countries"); final XSSFSheet sheet3 = wb.getSheet("institutions"); final String reference = null; final String dataValidationCountryName = "countriesLis"; final String dataValidationInstitutionName = "institutionsList"; final String[] countries = { "1- Colombia", "2- Brazil", "3- Espenia", "4- Argentina", "5- Aruba", "6- Egipto", "7- Panama", "8- Ecuador" }; final String[] institutions = { "CH- U.chile", "BZ- U.coritiba", "PN- U.panama", "AR- U.de.Palermo", "AF- U.delNilo", "EC- U.de.Quito", }; for (int i = 0; i < countries.length; i++) { final Row fila = sheet2.createRow(i); final Cell celda = fila.createCell(0); final Cell celdaformula = fila.createCell(1); // final String formula = "SUM(C1,D1)"; celda.setCellValue(countries[i]); // celdaformula.setCellFormula(formula); } // sheet2.protectSheet("marlo-ciat"); // // 3. create named range for an area using AreaReference // final Name namedCountry = wb.createName(); // namedCountry.setNameName(dataValidationCountryName); // reference = "countries!$A$1:$A$" + countries.length; // area reference // namedCountry.setRefersToFormula(reference); for (int i = 0; i < institutions.length; i++) { final Row fila = sheet3.createRow(i); final Cell celda = fila.createCell(0); celda.setCellValue(institutions[i]); } // final Name namedInstitution = wb.createName(); // namedInstitution.setNameName(dataValidationInstitutionName); // reference = "institutions!$A$1:$A$" + institutions.length; // area reference // namedInstitution.setRefersToFormula(reference); // // sheet3.protectSheet("marlo-ciat"); validationHelper = sheet1.getDataValidationHelper(); final CellRangeAddressList addressListCountry = new CellRangeAddressList(11, 1000, 4, 4); constraintCountries = validationHelper.createFormulaListConstraint(dataValidationCountryName); dataValidationCountries = validationHelper.createValidation(constraintCountries, addressListCountry); dataValidationCountries.setSuppressDropDownArrow(true); if (dataValidationCountries instanceof XSSFDataValidation) { dataValidationCountries.setSuppressDropDownArrow(true); dataValidationCountries.setShowErrorBox(true); } else { dataValidationCountries.setSuppressDropDownArrow(false); } final CellRangeAddressList addressListInstitution = new CellRangeAddressList(11, 1000, 6, 6); constraintInstitutions = validationHelper.createFormulaListConstraint(dataValidationInstitutionName); dataValidationInstitutions = validationHelper.createValidation(constraintInstitutions, addressListInstitution); dataValidationInstitutions.setSuppressDropDownArrow(true); if (dataValidationInstitutions instanceof XSSFDataValidation) { dataValidationInstitutions.setSuppressDropDownArrow(true); dataValidationInstitutions.setShowErrorBox(true); } else { dataValidationInstitutions.setSuppressDropDownArrow(false); } final CellRangeAddressList addressListCountryOfInstitution = new CellRangeAddressList(11, 1000, 7, 7); constraintCountryOfInstitutions = validationHelper .createFormulaListConstraint(dataValidationCountryName); dataValidationCountryOfInstitutions = validationHelper.createValidation(constraintCountryOfInstitutions, addressListCountryOfInstitution); dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true); if (dataValidationCountryOfInstitutions instanceof XSSFDataValidation) { dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true); dataValidationCountryOfInstitutions.setShowErrorBox(true); } else { dataValidationCountryOfInstitutions.setSuppressDropDownArrow(false); } sheet1.addValidationData(dataValidationCountries); sheet1.addValidationData(dataValidationInstitutions); sheet1.addValidationData(dataValidationCountryOfInstitutions); FileOutputStream fileOut; fileOut = new FileOutputStream("C:\\Users\\logonzalez\\Downloads\\vineet.xlsm"); wb.write(fileOut); fileOut.close(); wb.close(); } catch (EncryptedDocumentException | IOException e1) { e1.printStackTrace(); } }
From source file:org.excel.LinkedDropDownLists.java
License:Apache License
LinkedDropDownLists(String workbookName) {
File file = null;//from w w w .jav a2 s .c o m
FileOutputStream fos = null;
Workbook workbook = null;
Sheet sheet = null;
DataValidationHelper dvHelper = null;
DataValidationConstraint dvConstraint = null;
DataValidation validation = null;
CellRangeAddressList addressList = null;
try {
// Using the ss.usermodel allows this class to support both binary
// and xml based workbooks. The choice of which one to create is
// made by checking the file extension.
if (workbookName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
// Build the sheet that will hold the data for the validations. This
// must be done first as it will create names that are referenced
// later.
sheet = workbook.createSheet("Linked Validations");
LinkedDropDownLists.buildDataSheet(sheet);
// Build the first data validation to occupy cell A1. Note
// that it retrieves it's data from the named area or region called
// CHOICES. Further information about this can be found in the
// static buildDataSheet() method below.
addressList = new CellRangeAddressList(0, 0, 0, 0);
dvHelper = sheet.getDataValidationHelper();
dvConstraint = dvHelper.createFormulaListConstraint("CHOICES");
validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
// Now, build the linked or dependent drop down list that will
// occupy cell B1. The key to the whole process is the use of the
// INDIRECT() function. In the buildDataSheet(0 method, a series of
// named regions are created and the names of three of them mirror
// the options available to the user in the first drop down list
// (in cell A1). Using the INDIRECT() function makes it possible
// to convert the selection the user makes in that first drop down
// into the addresses of a named region of cells and then to use
// those cells to populate the second drop down list.
addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT(UPPER($A$1))");
validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
file = new File(workbookName);
fos = new FileOutputStream(file);
workbook.write(fos);
} catch (IOException ioEx) {
System.out.println("Caught a: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follws:.....");
ioEx.printStackTrace(System.out);
} finally {
try {
if (fos != null) {
fos.close();
fos = null;
}
} catch (IOException ioEx) {
System.out.println("Caught a: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follws:.....");
ioEx.printStackTrace(System.out);
}
}
}
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("rawtypes") private static void writeDataValidations(Sheet sheet, ExcelWriteSheetProcessor sheetProcessor) { int templateRowStartIndex = sheetProcessor.getTemplateStartRowIndex(); int templateRowEndIndex = sheetProcessor.getTemplateEndRowIndex(); int step = templateRowEndIndex - templateRowStartIndex + 1; int rowStartIndex = sheetProcessor.getStartRowIndex(); Set<Integer> configColIndexSet = new HashSet<Integer>(); for (Entry<String, Map<Integer, ExcelWriteFieldMappingAttribute>> fieldIndexMapping : sheetProcessor .getFieldMapping().export().entrySet()) { if (fieldIndexMapping == null || fieldIndexMapping.getValue() == null) { continue; }//from w w w . j a v a 2 s.c om for (Entry<Integer, ExcelWriteFieldMappingAttribute> indexProcessorMapping : fieldIndexMapping .getValue().entrySet()) { if (indexProcessorMapping == null || indexProcessorMapping.getKey() == null) { continue; } configColIndexSet.add(indexProcessorMapping.getKey()); } } List<? extends DataValidation> dataValidations = sheet.getDataValidations(); if (dataValidations != null) { for (DataValidation dataValidation : dataValidations) { if (dataValidation == null) { continue; } CellRangeAddressList cellRangeAddressList = dataValidation.getRegions(); if (cellRangeAddressList == null) { continue; } CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.getCellRangeAddresses(); if (cellRangeAddresses == null || cellRangeAddresses.length == 0) { continue; } CellRangeAddressList newCellRangeAddressList = new CellRangeAddressList(); boolean validationContains = false; for (CellRangeAddress cellRangeAddress : cellRangeAddresses) { if (cellRangeAddress == null) { continue; } if (templateRowEndIndex < cellRangeAddress.getFirstRow() || templateRowStartIndex > cellRangeAddress.getLastRow()) {// specify row continue; } for (Integer configColIndex : configColIndexSet) { if (configColIndex < cellRangeAddress.getFirstColumn() || configColIndex > cellRangeAddress.getLastColumn()) {// specify column continue; } if (templateRowStartIndex == templateRowEndIndex) { newCellRangeAddressList.addCellRangeAddress(rowStartIndex, configColIndex, sheet.getLastRowNum(), configColIndex); validationContains = true; } else { int start = cellRangeAddress.getFirstRow() > templateRowStartIndex ? cellRangeAddress.getFirstRow() : templateRowStartIndex; int end = cellRangeAddress.getLastRow() < templateRowEndIndex ? cellRangeAddress.getLastRow() : templateRowEndIndex; long lastRow = sheet.getLastRowNum(); if (lastRow > end) { long count = (lastRow - templateRowEndIndex) / step; int i = templateRowEndIndex; for (; i < count; i++) { newCellRangeAddressList.addCellRangeAddress(start + i * step, configColIndex, end + i * step, configColIndex); validationContains = true; } long _start = start + i * step; if (_start <= lastRow) { long _end = end + i * step; _end = _end < lastRow ? _end : lastRow; newCellRangeAddressList.addCellRangeAddress((int) _start, configColIndex, (int) _end, configColIndex); validationContains = true; } } } } } if (validationContains) { DataValidation newDataValidation = sheet.getDataValidationHelper() .createValidation(dataValidation.getValidationConstraint(), newCellRangeAddressList); sheet.addValidationData(newDataValidation); } } } }