List of usage examples for org.apache.poi.hssf.usermodel HSSFDataValidationHelper HSSFDataValidationHelper
public HSSFDataValidationHelper(HSSFSheet sheet)
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
/** * Sheet???./* www. j a va 2 s.c o m*/ * * @param sheet * @param dvcMap ??fieldName"type_name" * @param colDesc 17"?:user_id,??:user_name,:type_name" * @author gw_liaozh */ public static void addDataValidationConstraints(HSSFSheet sheet, Map<String, DataValidationConstraint> dvcMap, String colDesc) {// List<String> colFieldList = getColumnFieldList(colDesc); HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); DataValidationConstraint constraint = dvcMap.get(fieldName); if (constraint == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } }
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
/** * 17??17.//from ww w . j a v a 2 s .c o m * * @param sheet * @param dataList * @param fieldName * @return * @throws IllegalArgumentException * @throws IllegalAccessException * @throws InvocationTargetException * @author gw_liaozh */ public static DataValidationConstraint createExplicitListConstraint(HSSFSheet sheet, List<?> dataList, String fieldName) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {// HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); List<String> strList = new ArrayList<String>(); for (Object obj : dataList) { Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value != null) { strList.add(value.toString()); } } return dvHelper.createExplicitListConstraint(strList.toArray(new String[strList.size()])); }
From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java
License:Open Source License
/** * Excel Workbook?./*from ww w . java2s .co m*/ * * @param colDesc 17"?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static HSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //???17 //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFFont font = getBaseFont(wb); HSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //??17 HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // HSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { HSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //?17 for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); HSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); HSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
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 .jav a 2 s . 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; }