Example usage for org.apache.poi.ss.usermodel DataValidationHelper createValidation

List of usage examples for org.apache.poi.ss.usermodel DataValidationHelper createValidation

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. DataValidationHelper
  6. createValidation

Introduction

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

Prototype

DataValidation createValidation(DataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList);

Source Link

Usage

From source file:bo.com.offercruzmail.utils.HojaExcelHelper.java

public void agregarValidacionLista(int primerFila, int ultimaFila, int primerColumna, int ultimaColumna,
        String[] valores, boolean mostrarCombo, boolean mostrarError) {
    if (valores != null && valores.length > 0) {
        CellRangeAddressList celdas = new CellRangeAddressList(primerFila, ultimaFila, primerColumna,
                ultimaColumna);//ww  w .j a v  a2s  .com
        DataValidationHelper dvHelper = hoja.getDataValidationHelper();
        DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(valores);
        DataValidation validation = dvHelper.createValidation(dvConstraint, celdas);
        validation.setSuppressDropDownArrow(mostrarCombo);
        validation.setShowErrorBox(mostrarError);
        hoja.addValidationData(validation);
    }
}

From source file:cn.org.vbn.util.LinkedDropDownLists.java

License:Apache License

LinkedDropDownLists(String workbookName) {
    File file = null;//from   ww  w .jav a2s . co  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.asakusafw.testdata.generator.excel.SheetEditor.java

License:Apache License

private void setExplicitListConstraint(Sheet sheet, String[] list, int firstRow, int firstCol, int lastRow,
        int lastCol) {
    assert sheet != null;
    assert list != null;
    DataValidationHelper helper = sheet.getDataValidationHelper();
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
    DataValidation validation = helper.createValidation(constraint, addressList);
    validation.setEmptyCellAllowed(true);
    sheet.addValidationData(validation);
}

From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java

License:Open Source License

public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl,
        String sonarLogin, String sonarPassword) {

    short formatIndex;
    HSSFDataFormat dataFormat = null;//ww  w . j a  va 2s .c o  m
    FileOutputStream out = null;
    HSSFWorkbook workbook = null;

    String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_"
            + sonarProject.getEffectiveKey().replace(':', '-') + "."
            + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION;

    File resultFile = new File(filePath);

    try {
        out = new FileOutputStream(resultFile);

        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Tasks list");

        // Date format.
        dataFormat = workbook.createDataFormat();
        formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss");
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(formatIndex);

        Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin,
                sonarPassword);

        if (rootIssue == null) {
            return null;
        }

        DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(
                new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" });
        CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1,
                STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX);
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);

        int rownum = 0;

        Row row = sheet.createRow(rownum++);
        row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status");
        row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity");
        row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component");
        row.createCell(LINE_COLUMN_INDEX).setCellValue("Line");
        row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message");
        row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author");
        row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned");
        row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate");
        row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate");
        row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path");

        for (Issue issue : rootIssue.getIssues()) {
            if (issue != null) {
                row = sheet.createRow(rownum++);
                int componentIndex = 0;
                if (issue.getComponent() != null) {
                    componentIndex = issue.getComponent().lastIndexOf('/');
                }
                String component;
                String path;
                if (componentIndex > 0) {
                    component = issue.getComponent().substring(componentIndex + 1);
                    path = issue.getComponent().substring(0, componentIndex);
                } else {
                    component = issue.getComponent();
                    path = "";
                }

                // Set values.
                row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus());
                row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity());
                row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component);
                row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine());
                row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage());
                row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor());
                row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee());
                row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate());
                row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate());
                row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path);

                // Set date style to date column.
                row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
                row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
            }
        }

        // Auto-size sheet columns.
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX);
        sheet.autoSizeColumn(LINE_COLUMN_INDEX);
        sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX);
        sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX);
        sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX);
        sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX);

        workbook.write(out);

    } catch (FileNotFoundException e) {

        // TODO manage error.
        e.printStackTrace();
    } catch (IOException e) {

        // TODO manage error.
        e.printStackTrace();
    } finally {
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(out);
    }

    return resultFile;
}

From source file:com.globalsight.everest.qachecks.DITAQAChecker.java

License:Apache License

private void addFalsePositiveValidation(Sheet p_sheet, int startRow, int lastRow, int startColumn,
        int lastColumn) {
    // Add category failure drop down list here.
    DataValidationHelper dvHelper = p_sheet.getDataValidationHelper();

    String[] options = { "Yes", "No" };
    DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(options);

    CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, startColumn, lastColumn);

    DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);

    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);/* w  w w  . ja va  2  s  .  c om*/
    p_sheet.addValidationData(validation);
}

From source file:com.globalsight.everest.qachecks.QAChecker.java

License:Apache License

private void addFalsePositiveValidation(Sheet p_sheet, int p_lastRow) {
    DataValidationHelper dvHelper = p_sheet.getDataValidationHelper();

    DataValidationConstraint dvConstraint = dvHelper
            .createExplicitListConstraint(new String[] { FALSE_POSITIVE_YES, FALSE_POSITIVE_NO });
    CellRangeAddressList addressList = new CellRangeAddressList(ROW_SEGMENT_START, p_lastRow,
            COLUMN_FALSE_POSITIVE, COLUMN_FALSE_POSITIVE);

    DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
    validation.setSuppressDropDownArrow(true);
    validation.setShowErrorBox(true);/*w w  w .jav a  2 s  . c  o m*/

    p_sheet.addValidationData(validation);
}

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".
 * /*  ww w  .  j a  va 2s.  c  om*/
 * @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);/* ww  w .  j a v  a 2  s.  com*/
    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);//  w  w  w.  j  ava 2 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 addCommentStatusValidation(Sheet p_sheet, DataValidationHelper dvHelper,
        DataValidationConstraint dvConstraint, CellRangeAddressList addressList) {
    if (addressList == null || addressList.countRanges() == 0)
        return;/*  www  .j  a  va 2  s  . c om*/

    DataValidation validationOne = dvHelper.createValidation(dvConstraint, addressList);
    validationOne.setSuppressDropDownArrow(true);
    validationOne.setShowErrorBox(true);
    p_sheet.addValidationData(validationOne);
}