List of usage examples for org.apache.poi.ss.usermodel DataValidationHelper createFormulaListConstraint
DataValidationConstraint createFormulaListConstraint(String listFormula);
From source file:cn.org.vbn.util.LinkedDropDownLists.java
License:Apache License
LinkedDropDownLists(String workbookName) {
File file = null;/*from www . j a va 2 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:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.CommentsAnalysisReportGenerator.java
License:Apache License
/** * Add category failure drop down list. It is from "J8" to "Jn". * //from w w w . j a v a2 s.co m * @param p_sheet * @param startRow * @param lastRow * @param startColumn * @param lastColumn */ private void addCategoryFailureValidation(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(CATEGORY_FAILURE_DROP_DOWN_LIST); 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.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java
License:Apache License
private void addMarketSuitabilityValidation(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(MARKET_SUITABILITY_LIST); CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, startColumn, lastColumn); DataValidation validation = dvHelper.createValidation(dvConstraint, addressList); validation.setSuppressDropDownArrow(true); validation.setShowErrorBox(true);/* www.jav a2 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 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);/* ww w.ja v a 2 s .c om*/ p_sheet.addValidationData(validation); }
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} * /* www.j av a2 s .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: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 a2s .c om * @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:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java
License:Open Source License
public DataValidation setDataValidator(String dataValidationName, DataValidationHelper validationHelper, CellRangeAddressList addressList, DataValidationConstraint constraint) { DataValidation dataValidation = null; constraint = validationHelper.createFormulaListConstraint(dataValidationName); dataValidation = validationHelper.createValidation(constraint, addressList); dataValidation.setSuppressDropDownArrow(true); if (dataValidation instanceof XSSFDataValidation) { dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); } else {//from w ww .ja v a2 s .c o m dataValidation.setSuppressDropDownArrow(false); } return dataValidation; }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java
License:Open Source License
public void createFile() throws FileNotFoundException { try {/*from ww w .j av a 2 s . 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. j av 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.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;/*from w ww . j a va 2 s . c o m*/ //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }