Example usage for org.apache.poi.ss.usermodel DataValidation setSuppressDropDownArrow

List of usage examples for org.apache.poi.ss.usermodel DataValidation setSuppressDropDownArrow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataValidation
  6. setSuppressDropDownArrow

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel DataValidation setSuppressDropDownArrow.

Prototype

public abstract void setSuppressDropDownArrow(boolean suppress);

Source Link

Document

Useful for list validation objects .

Usage

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".
 * //from   w w  w  . j a  va  2  s  .com
 * @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.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   ww w .  j a va 2  s .  c  om*/
 * @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: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 ww.j a  v a 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:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

/**
  *//* w  w  w .jav a 2  s.  com*/
public static void setOneLineHeader(HSSFSheet sheet, int iRowNum, ArrayList<String> al) {
    LogMaker.makelog("Creating Header in Excel");
    try {
        HSSFRow row = sheet.createRow(iRowNum);
        row.setHeight((short) 600);
        int i = 0;
        for (String str : al) {
            row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(str);
            if (i == 1) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            } else if (i == 2) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            } else if (i == 3) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            }

            else {
                try {
                    row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                } catch (NullPointerException e) {
                    e.printStackTrace();

                }

            }

            sheet.autoSizeColumn((short) i, true);
            i++;

        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

@SuppressWarnings("unchecked")

public static void srcPr(HSSFSheet srcSheet) {
    LogMaker.makelog("prepare to Print ID File info to Excel sheet");

    //category // ww  w  . j a v a 2  s .  co m
    String[] strCategory = new String[FileVals.category.size()];
    FileVals.category.toArray(strCategory);

    String[] strFPath = new String[FileVals.fPath.size()];
    FileVals.fPath.toArray(strFPath);

    String[] strDType = new String[FileVals.dtype.size()];
    FileVals.dtype.toArray(strDType);

    String[] strFileFolder = new String[FileVals.file.size()];
    FileVals.file.toArray(strFileFolder);

    String[] strComponent = new String[FileVals.component.size()];
    FileVals.component.toArray(strComponent);

    String[] strLicense = new String[FileVals.license.size()];
    FileVals.license.toArray(strLicense);

    String[] strVersion = new String[FileVals.version.size()];
    FileVals.version.toArray(strVersion);

    //matched files
    String[] strOSSFname = new String[FileVals.OSSFname.size()];
    FileVals.OSSFname.toArray(strOSSFname);

    String[] strPercent = new String[FileVals.matchedRatio.size()];
    FileVals.matchedRatio.toArray(strPercent);

    //OSS matched file line
    String[] strOSSFLine = new String[FileVals.OSSFLine.size()];
    FileVals.OSSFLine.toArray(strOSSFLine);

    //Dev Matched First Line
    String[] strFirstLine = new String[FileVals.firstLine.size()];
    FileVals.firstLine.toArray(strFirstLine);

    //matched total line
    String[] strTotalLine = new String[FileVals.tline.size()];
    FileVals.tline.toArray(strTotalLine);

    //identified date
    String[] strIdentifiedDate = new String[FileVals.identifiedDate.size()];
    FileVals.identifiedDate.toArray(strIdentifiedDate);

    //identifier
    String[] strIdentifier = new String[FileVals.identifier.size()];
    FileVals.identifier.toArray(strIdentifier);

    String[] strComment = new String[FileVals.comment.size()];
    FileVals.comment.toArray(strComment);

    int partialCount = Count.partialCount;
    //int partialCount=0;
    LogMaker.makelog("Printing File Info to excel");
    for (int i = 0; i < strDType.length; i++) {
        //HSSFRow tempRow = srcSheet.createRow(i+2+iCount-(lineCount-1));//???
        HSSFRow tempRow = srcSheet.createRow(i + 2 + Count.partialCount);//???
        partialCount++;

        tempRow.setHeight((short) 500);

        String a = (strFileFolder[i].substring(1));

        // category print
        int c = 0;

        try {
            tempRow.createCell(c).setCellValue(a.substring(0, a.indexOf("/")));
            //if (strCate.equals(a.substring(0,a.indexOf("/")))){}

        } catch (Exception e) {
            tempRow.createCell(c).setCellValue(a);
        }

        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        tempRow.createCell(c).setCellValue(a);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Identification type print
        tempRow.createCell(c).setCellValue(strDType[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        tempRow.createCell(c).setCellValue(strComponent[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //License Print
        tempRow.createCell(c).setCellValue(strLicense[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //version Print
        tempRow.createCell(c).setCellValue(strVersion[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //OSS matched Files;OSS matched Filename; print
        try {
            tempRow.createCell(c).setCellValue(strOSSFname[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //matched percent print;
        if (strPercent[i] == "" || strPercent[i] == null) {
            tempRow.createCell(c).setCellValue("100%");
        } else {
            tempRow.createCell(c).setCellValue(strPercent[i] + "%");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //OSS file line print
        try {
            tempRow.createCell(c).setCellValue(strOSSFLine[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Dev Matched First Line print
        try {
            tempRow.createCell(c).setCellValue(strFirstLine[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Dev Matched Total Line print
        try {
            tempRow.createCell(c).setCellValue(strTotalLine[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Identified Date; identifieddate;
        try {
            tempRow.createCell(c).setCellValue(strIdentifiedDate[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        //tempRow.createCell(c).setCellValue(strIdentifiedDate[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Identifier print
        try {
            tempRow.createCell(c).setCellValue(strIdentifier[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }

        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Comment print
        tempRow.createCell(c).setCellValue(strComment[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //other empty cell
        for (int temp = c; temp < 21; temp++) {
            tempRow.createCell(temp).setCellValue("");
            tempRow.getCell(temp).setCellStyle(Style.componentCellStyle);
            //srcSheet.autoSizeColumn((short) c, true);
            if (temp == 16) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i + 2, i + 2, temp, temp);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                srcSheet.addValidationData(dataValidation);
            }
            if (temp == 15) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i + 2, i + 2, temp, temp);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                srcSheet.addValidationData(dataValidation);
            }

            //c++;   
        }

        //tempRow.createCell(3).setCellValue(strComponent[i]+" (+"+(compCount-1)+")");
        tempRow.createCell(3).setCellValue(strComponent[i] + " (+0)");
        tempRow.getCell(3).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) 3, true);

        FileVals.clearAllArrayList();
    }

    Count.partialCount = partialCount;

}

From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

/**
 * "Build Image Analysis"  //from  w  w w. j  a va 2s .c om
 *      * 
 */

public static void BIA(HSSFWorkbook wb) {
    LogMaker.makelog("Making BIA sheet");
    HSSFSheet sheetBIA = wb.createSheet("Build Image Analysis");// ??????? 

    ArrayList<String> Header = new ArrayList<String>();

    Header.add("Analysis Description");
    Header.add("1st Reviewer (Development Team)");
    Header.add("Final Revewer");

    lineHeader(sheetBIA, 0, Header);// 0?? ???? ??????? 
    sheetBIA.addMergedRegion(new Region(0, (short) 0, 0, (short) 5));
    sheetBIA.addMergedRegion(new Region(0, (short) 6, 0, (short) 8));
    sheetBIA.addMergedRegion(new Region(0, (short) 9, 0, (short) 10));
    Header.clear();

    HSSFRow row = sheetBIA.createRow(1); //row ????
    row.setHeight((short) 500);
    Header.add("Binary File Path");
    Header.add("                ");
    Header.add("Component");
    Header.add("License");//drop down
    Header.add("Analyzer");
    Header.add("Analyzer's Comments");

    src2ndrow(sheetBIA, Header, 0, row, 6, 9);
    Header.clear();
    sheetBIA.addMergedRegion(new Region(1, (short) 0, 1, (short) 1));

    Header.add("Person In Charge");
    Header.add("A or B");//drop down
    Header.add("Reviewer's Ccomments");

    src2ndrow(sheetBIA, Header, 6, row, 6, 9);// 0?? ???? ??????? 
    sheetBIA.autoSizeColumn((short) 6, true);
    sheetBIA.autoSizeColumn((short) 8, true);
    Header.clear();

    Header.add("Review Date");
    Header.add("Reviewer's Comments");

    src2ndrow(sheetBIA, Header, 9, row, 6, 9);// 0?? ???? ??????? 
    Header.clear();

    for (int i = 2; i < 200; i++) {
        HSSFRow row2 = sheetBIA.createRow(i);
        row2.setHeight((short) 500);
        for (int j = 0; j < 11; j++) {
            row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue("           ");
            row2.getCell(j).setCellStyle(Style.componentCellStyle);

            if (j == 3) {
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheetBIA.addValidationData(dataValidation);
            }

            if (j == 7) {
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheetBIA.addValidationData(dataValidation);
            }
        }
    }

}

From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

/**
 * "Final List"  /*from ww w  .  j  a va2  s.co m*/
 * 
 */
public static void FL(HSSFWorkbook wb) {
    LogMaker.makelog("Making Final sheet");
    HSSFSheet sheet = wb.createSheet("Final List");// ??????? 

    ArrayList<String> Header = new ArrayList<String>();
    Header.add("                     Component Name                        ");
    Header.add("                     A or B                      "); //Drop down 
    Header.add("                 License                 "); //Drop Down               
    Header.add("               Linkage Type              "); //Drop down
    Header.add("                   License Copy                     ");
    Header.add("                 Remark                  ");
    // Macro ? ???
    setOneLineHeader(sheet, 0, Header);// 0?? ???? ??????? 
    Header.clear();

    //?? ????
    for (int i = 1; i < 200; i++) {
        HSSFRow row2 = sheet.createRow(i);
        row2.setHeight((short) 500);
        for (int j = 0; j < 6; j++) {
            row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue("");
            row2.getCell(j).setCellStyle(Style.componentCellStyle);
            if (j == 1) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);
            }

            if (j == 2) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);
            }
            if (j == 3) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                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 {/*  w w  w.  j  a  v a 2  s  .  com*/
        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 {// w w  w. j a  v  a2  s  . c  o 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();
    }
}