List of usage examples for org.apache.poi.ss.usermodel Sheet getDataValidationHelper
public DataValidationHelper getDataValidationHelper();
From source file:cn.org.vbn.util.LinkedDropDownLists.java
License:Apache License
LinkedDropDownLists(String workbookName) {
File file = null;/*ww w. j ava2 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: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.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);/*from w w w . ja v a 2s . 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);//from ww w . j a v a 2 s . co 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". * /*from w ww .j a va2 s . com*/ * @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);/*from w w w . j a v a 2 s . c o 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);//from w w w .j av a 2 s . c om p_sheet.addValidationData(validation); }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java
License:Apache License
private void addCommentStatus(Sheet p_sheet, Set<Integer> rowsWithCommentSet, int last_row) { DataValidationHelper dvHelper = p_sheet.getDataValidationHelper(); DataValidationConstraint dvConstraintAll = null; DataValidationConstraint dvConstraintOne = null; CellRangeAddressList addressListOne = new CellRangeAddressList(); CellRangeAddressList addressListAll = new CellRangeAddressList(); CellRangeAddress cellAddress = null; List<String> status = new ArrayList<String>(); status.addAll(IssueOptions.getAllStatus()); String[] allStatus = new String[status.size()]; status.toArray(allStatus);/*w w w .java 2 s.co m*/ dvConstraintAll = dvHelper.createExplicitListConstraint(allStatus); String[] oneStatus = { Issue.STATUS_QUERY }; dvConstraintOne = dvHelper.createExplicitListConstraint(oneStatus); if (rowsWithCommentSet.size() == 0) { cellAddress = new CellRangeAddress(SEGMENT_START_ROW, last_row - 1, COMMENT_STATUS_COLUMN, COMMENT_STATUS_COLUMN); addressListOne.addCellRangeAddress(cellAddress); addCommentStatusValidation(p_sheet, dvHelper, dvConstraintOne, addressListOne); } else { boolean hasComment = false; int startRow = SEGMENT_START_ROW; int endRow = -1; for (int row = SEGMENT_START_ROW; row < last_row; row++) { if (rowsWithCommentSet.contains(row)) { if (!hasComment && row != SEGMENT_START_ROW) { endRow = row - 1; cellAddress = new CellRangeAddress(startRow, endRow, COMMENT_STATUS_COLUMN, COMMENT_STATUS_COLUMN); addressListOne.addCellRangeAddress(cellAddress); startRow = row; } hasComment = true; } else { if (hasComment) { endRow = row - 1; cellAddress = new CellRangeAddress(startRow, endRow, COMMENT_STATUS_COLUMN, COMMENT_STATUS_COLUMN); addressListAll.addCellRangeAddress(cellAddress); startRow = row; } hasComment = false; } if (row == last_row - 1) { cellAddress = new CellRangeAddress(startRow, last_row - 1, COMMENT_STATUS_COLUMN, COMMENT_STATUS_COLUMN); if (hasComment) { addressListAll.addCellRangeAddress(cellAddress); } else { addressListOne.addCellRangeAddress(cellAddress); } } } addCommentStatusValidation(p_sheet, dvHelper, dvConstraintAll, addressListAll); addCommentStatusValidation(p_sheet, dvHelper, dvConstraintOne, addressListOne); } }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.PostReviewQAReportGenerator.java
License:Apache License
private void addPriority(Sheet p_sheet, int startRow, int lastRow, int startColumn, int lastColumn) { // Add category failure drop down list here. HashMap priorities = new HashMap(); DataValidationHelper dvHelper = p_sheet.getDataValidationHelper(); priorities.putAll(IssueOptions.getAllPriorities()); String[] allpriorities = new String[priorities.size()]; priorities.values().toArray(allpriorities); DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(allpriorities); CellRangeAddressList addressList = new CellRangeAddressList(startRow, lastRow, startColumn, lastColumn); DataValidation validation = dvHelper.createValidation(dvConstraint, addressList); validation.setSuppressDropDownArrow(true); validation.setShowErrorBox(true);/*from www . j a v a 2 s.c o m*/ p_sheet.addValidationData(validation); }
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". * //w w w . j a v a2 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); }