Java tutorial
/* * Copyright 2015 www.hyberbin.com. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * Email:hyberbin@qq.com */ package com.dituiba.excel; import java.lang.annotation.Annotation; import java.util.Set; import org.apache.poi.hssf.usermodel.DVConstraint; import org.apache.poi.hssf.usermodel.HSSFDataValidation; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddressList; /** * User: Hyberbin * Date: 13-12-3 * Time: ?11:02 */ public class DefaultValidateAdapter extends IOAdapter { private final static String DICCODE_SHEET_NAME = "diccodeSheet"; public DefaultValidateAdapter(DicCodePool dicCodePool) { super(dicCodePool); } /** * ??? * @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); } } /** * ?? * @param validation * @param config */ protected void setValidationTip(DataValidation validation, Annotation config) { String tipLangName = AdapterUtil.getTipLangName(config); if (!ObjectHelper.isNullOrEmptyString(tipLangName)) { validation.createPromptBox(LanguageUtils.translate(Message.TIP), LanguageUtils.translate(tipLangName)); } } /** * ?? * @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("??"); } /** * ? * @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); } /** * ? * @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); } /** * ? * @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); } /** * ? * @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); } } /** * ??? * @param dataBean * @param sheet * @param columnIndex * @param filedName */ public void HiddenValidateAdapter(DataBean dataBean, Sheet sheet, int columnIndex, String filedName) { sheet.setColumnHidden(columnIndex, true); } }