List of usage examples for org.apache.poi.ss.util CellRangeAddressList CellRangeAddressList
public CellRangeAddressList(int firstRow, int lastRow, int firstCol, int lastCol)
From source file:cn.org.vbn.util.LinkedDropDownLists.java
License:Apache License
LinkedDropDownLists(String workbookName) {
File file = null;//from w ww. j ava 2 s .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.asakusafw.testtools.templategen.ExcelBookBuilder.java
License:Apache License
private void setExplicitListConstraint(HSSFSheet sheet, String[] list, int firstRow, int lastRow, int firstCol, int lastCol) { //????/* w w w.j a v a 2s.c o m*/ CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); DVConstraint constraint = DVConstraint.createExplicitListConstraint(list); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); validation.setEmptyCellAllowed(true); validation.setSuppressDropDownArrow(false); sheet.addValidationData(validation); }
From source file:com.devnexus.ting.web.controller.admin.RegistrationController.java
License:Apache License
private void createTicketTypeDropDown(Sheet formSheet, Sheet ticketTypeSheet, String[] ticketTypes) { XSSFDataValidationHelper validationHelper = new XSSFDataValidationHelper((XSSFSheet) formSheet); CellRangeAddressList ticketCellAddress = new CellRangeAddressList(7, 100, 6, 7); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(ticketTypeSheet.getSheetName() + "!$A$1:$A$" + ticketTypes.length); // constraint = validationHelper.createExplicitListConstraint(ticketTypes); DataValidation dataValidation = validationHelper.createValidation(constraint, ticketCellAddress); dataValidation.setSuppressDropDownArrow(true); formSheet.addValidationData(dataValidation); }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ???//from ww w . j ava 2 s .c om * @param dataBean * @param sheet * @param columnIndex * @param filedName */ public void DicCodeValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) { DicValidateConfig config = dataBean.getValidateConfig(filedName); String dicCode = config.dicCode(); Set<String> set = dicCodePool.getDicValueSet().get(dicCode); if (ObjectHelper.isEmpty(set)) { log.error("?{}", dicCode); return; } if (config.columnName() != 0) {//? createDicCodeSheet(config, sheet, columnIndex, set); } else { String[] strings = set.toArray(new String[] {}); DVConstraint constraint = DVConstraint.createExplicitListConstraint(strings); // ??,???? CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); // ? HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint); setValidationTip(data_validation_list, config); sheet.addValidationData(data_validation_list); } }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ??//from ww w . jav a 2 s . c o m * @param config * @param sheet * @param columnIndex * @param valueSet */ protected void createDicCodeSheet(DicValidateConfig config, Sheet sheet, int columnIndex, Set<String> valueSet) { Workbook workbook = sheet.getWorkbook(); Sheet codeSheet = workbook.getSheet(DICCODE_SHEET_NAME); if (codeSheet == null) { log.debug("?Sheet?Sheet"); codeSheet = workbook.createSheet(DICCODE_SHEET_NAME); } int codeIndex = config.columnName() - 'A'; log.debug("codeIndex{}", codeIndex); if (codeSheet.getRow(0) == null || codeSheet.getRow(0).getCell(codeIndex) == null) { log.debug("????"); int i = 0; for (String dic : valueSet) { Row row = codeSheet.getRow(i); if (row == null) row = codeSheet.createRow(i); Cell cell = row.createCell(codeIndex); cell.setCellValue(dic); i++; } } else { log.debug("????"); } Name name = workbook.getName(config.columnName() + ""); if (name == null || name.isDeleted()) { log.debug("?Name?Name"); name = workbook.createName(); name.setNameName(config.columnName() + ""); } name.setRefersToFormula(DICCODE_SHEET_NAME + "!$" + config.columnName() + "$1:$" + config.columnName() + "$" + valueSet.size()); DVConstraint constraint = DVConstraint.createFormulaListConstraint(name.getNameName()); CellRangeAddressList addressList = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint); workbook.setSheetHidden(workbook.getSheetIndex(DICCODE_SHEET_NAME), Workbook.SHEET_STATE_VERY_HIDDEN); setValidationTip(validation, config); sheet.addValidationData(validation); log.debug("??"); }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ?//from ww w . j a v a 2 s . c om * @param dataBean * @param sheet * @param columnIndex * @param filedName */ public void DateValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) { DateValidateConfig config = dataBean.getValidateConfig(filedName); DVConstraint dateConstraint = DVConstraint.createDateConstraint( DataValidationConstraint.OperatorType.BETWEEN, config.min(), config.max(), config.format()); // ??,???? CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); // ? DataValidation data_validation_list = new HSSFDataValidation(regions, dateConstraint); setValidationTip(data_validation_list, config); sheet.addValidationData(data_validation_list); }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ?/*ww w . j a v a2 s.c o m*/ * @param dataBean * @param sheet * @param columnIndex * @param filedName */ public void NumericValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) { NumericValidateConfig config = dataBean.getValidateConfig(filedName); DVConstraint constraint = DVConstraint.createNumericConstraint( DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.BETWEEN, config.min(), config.max()); // ??,???? CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); // ? DataValidation data_validation_list = new HSSFDataValidation(regions, constraint); setValidationTip(data_validation_list, config); sheet.addValidationData(data_validation_list); }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ?//from w ww. j a v a 2s . c o m * @param dataBean * @param sheet * @param columnIndex * @param filedName */ public void IntegerValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) { IntValidateConfig config = dataBean.getValidateConfig(filedName); DVConstraint constraint = DVConstraint.createNumericConstraint( DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, config.min(), config.max()); // ??,???? CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); // ? DataValidation data_validation_list = new HSSFDataValidation(regions, constraint); setValidationTip(data_validation_list, config); sheet.addValidationData(data_validation_list); }
From source file:com.dituiba.excel.DefaultValidateAdapter.java
License:Apache License
/** * ?/*from w ww .j ava 2 s . c o m*/ * @param dataBean * @param sheet * @param columnIndex * @param filedName */ public void TextValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) { TextValidateConfig config = dataBean.getValidateConfig(filedName); if (config.length() != 0) { DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.TEXT_LENGTH, DVConstraint.OperatorType.LESS_OR_EQUAL, config.length() + "", null); // ??,???? CellRangeAddressList regions = new CellRangeAddressList(BaseExcelService.START_ROW, Short.MAX_VALUE, columnIndex, columnIndex); // ? DataValidation data_validation_list = new HSSFDataValidation(regions, constraint); setValidationTip(data_validation_list, config); sheet.addValidationData(data_validation_list); } }