List of usage examples for org.apache.poi.ss.usermodel Sheet addValidationData
public void addValidationData(DataValidation 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 ww w . j a va 2s . 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
/** * Creates the cell style.//w ww.j av a 2s . com * * @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:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java
License:Open Source License
public String dowmloadTemplate() { try {/*from w w w . j a va2 s .co 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 {/* ww w . j a v a2 s .com*/ 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;//w ww.ja v a 2 s. c om
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 www . ja v a2 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); } } } }
From source file:output.ExcelM3Upgrad.java
private void writeMigration() { Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Migration"); sheet.setDisplayGridlines(false);//from w w w. j a v a 2 s. com sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); styles = createStyles(workbook); int rownum = beginROW; int cellnum = beginCOL; Row row = sheet.createRow(rownum++); for (int k = 0; k < model.getListColumn().length; k++) { Cell cell = row.createCell(cellnum++); cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng())); cell.setCellStyle(styles.get("header")); sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden()); sheet.autoSizeColumn(k); dialStatus(); } ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); } String[] listLevel = i18n.Language.traduce(Ressource.listLevel) .toArray(new String[Ressource.listLevel.length]); data = model.getData(); for (int i = 0; i < data.length; i++) { busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length); row = sheet.createRow(rownum++); Object[] objArr = data[i]; cellnum = beginCOL; boolean first = true; int j = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { if (first) { first = false; if ((Boolean) obj) { cell.setCellValue("Oui"); } else { cell.setCellValue("Non"); } } else { if ((Boolean) obj) { cell.setCellValue("OK"); } else { cell.setCellValue("KO"); } } } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } if (listHeader.indexOf(218) == j) { try { int n = Integer.parseInt(obj.toString().trim()); if (n == -1) { cell.setCellValue("ERROR"); } else { cell.setCellValue(listLevel[n]); } } catch (NumberFormatException ex) { cell.setCellValue(""); } } if (j < objArr.length - 3) { cell.setCellStyle(styles.get("cell_b_centered_locked")); } else { cell.setCellStyle(styles.get("cell_b_centered")); } j++; dialStatus(); } dialStatus(); } dialStatus(); busyDial.setText("Formatage du document"); CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 1, beginCOL + data[0].length - 1); DataValidationConstraint userConstraint; DataValidation userValidation; if (type == 0) { userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel() .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = new HSSFDataValidation(userList, userConstraint); } else { XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); userConstraint = (XSSFDataValidationConstraint) userHelper .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect() .toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList); } sheet.addValidationData(userValidation); CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 2, beginCOL + data[0].length - 2); DataValidationConstraint migConstraint; DataValidation migValidation; if (type == 0) { migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = new HSSFDataValidation(migList, migConstraint); } else { XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); migConstraint = (XSSFDataValidationConstraint) migHelper .createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList); } sheet.addValidationData(migValidation); CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 3, beginCOL + data[0].length - 3); DataValidationConstraint levelConstraint; DataValidation levelValidation; ArrayList<String> listNameLevel = new ArrayList<>(); listNameLevel.add("ERROR"); listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length]) if (type == 0) { levelConstraint = DVConstraint .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()])); levelValidation = new HSSFDataValidation(levelList, levelConstraint); } else { XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint( i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length])); levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList); } sheet.addValidationData(levelValidation); int irow = beginROW; int icol = beginCOL + model.getListColumn().length + 2; row = sheet.getRow(irow); Cell cell = row.createCell(icol); sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1)); cell.setCellValue("Estimation de la charge"); cell.setCellStyle(styles.get("header")); irow++; row = sheet.getRow(irow); int cpt = 0; ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel); for (String s : listStringLevel) { cell = row.createCell(icol); cell.setCellValue(s); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL); cell.setCellFormula( "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]); cell.setCellStyle(styles.get("cell_b_centered_locked")); irow++; row = sheet.getRow(irow); cpt++; } row = sheet.getRow(irow); cell = row.createCell(icol); cell.setCellValue("Total des charges"); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1)); cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")"); cell.setCellStyle(styles.get("cell_b_centered_locked")); for (int k = 0; k < model.getListColumn().length + 3; k++) { sheet.autoSizeColumn(k); } sheet.protectSheet("3kles2014"); }