List of usage examples for org.apache.poi.ss.usermodel DataValidationHelper createExplicitListConstraint
DataValidationConstraint createExplicitListConstraint(String[] listOfValues);
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);/*from www.j ava 2s . c o m*/ 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: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;//w w w .j a v a2 s . c om 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);/*ww w. j av a 2s . co m*/ 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 w w w. java 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 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);/*from w ww . java 2s .c o 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 w ww. j a va2 s. com 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". * /*from ww w . ja v a 2 s .c om*/ * @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 w ww . j a va2 s . co 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: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); }