poi实现excel下拉联动
来源：互联网 发布：手机节拍器软件 编辑：程序博客网 时间：2024/06/09 11:36
/** * * @File name: CreateExcelMoBusiness.java * @Create on: 2011-02-12 11:48:453 * @Author : zhys513 * @ChangeList * --------------------------------------------------- * Date Editor ChangeReasons * */ import java.io.FileOutputStream;import java.util.HashMap;import org.apache.poi.hssf.usermodel.DVConstraint;import org.apache.poi.hssf.usermodel.HSSFDataValidation;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DataValidation;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;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; public class CreateExcelMoBusiness {private static String EXCEL_HIDE_SHEET_NAME = "excelhidesheetname"; private static String HIDE_SHEET_NAME_SEX = "sexList"; private static String HIDE_SHEET_NAME_PROVINCE = "provinceList"; private HashMap map = new HashMap(); //设置下拉列表的内容 private static String[] sexList = {"男","女"}; private static String[] provinceList = {"浙江","山东","江西","江苏","四川"}; private static String[] zjProvinceList = {"浙江","杭州","宁波","温州"}; private static String[] sdProvinceList = {"山东","济南","青岛","烟台"}; private static String[] jxProvinceList = {"江西","南昌","新余","鹰潭","抚州"}; private static String[] jsProvinceList = {"江苏","南京","苏州","无锡"}; private static String[] scProvinceList = {"四川","成都","绵阳","自贡"}; public static void main(String[] args) {//使用事例 Workbook wb = new HSSFWorkbook(); createExcelMo(wb);creatExcelHidePage(wb); setDataValidation(wb); FileOutputStream fileOut;try {fileOut = new FileOutputStream("d://excel_template.xls"); wb.write(fileOut); fileOut.close();} catch (Exception e) { e.printStackTrace();} }public static void createExcelMo(Workbook wb){ Sheet sheet = wb.createSheet("用户分类添加批导"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("手机号码"); cell.setCellStyle(getTitleStyle(wb)); cell = row.createCell(1); cell.setCellValue("所属父类"); cell.setCellStyle(getTitleStyle(wb)); cell = row.createCell(2); cell.setCellValue("所属子类"); cell.setCellStyle(getTitleStyle(wb)); cell = row.createCell(3); } /** * 设置模板文件的横向表头单元格的样式 * @param wb * @return */ private static CellStyle getTitleStyle(Workbook wb){ CellStyle style = wb.createCellStyle(); //对齐方式设置 style.setAlignment(CellStyle.ALIGN_CENTER); //边框颜色和宽度设置 style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); //设置背景颜色 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); //粗体字设置 Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); return style; } /** * 设置模板文件的横向表头单元格的样式 * @param wb * @return */ public static void creatExcelHidePage(Workbook workbook){ Sheet hideInfoSheet = workbook.createSheet(EXCEL_HIDE_SHEET_NAME);//隐藏一些信息 //在隐藏页设置选择信息 //第一行设置性别信息 Row sexRow = hideInfoSheet.createRow(0); creatRow(sexRow, sexList); //第二行设置省份名称列表 Row provinceNameRow = hideInfoSheet.createRow(1); creatRow(provinceNameRow, provinceList); //以下行设置城市名称列表 Row cityNameRow = hideInfoSheet.createRow(2); creatRow(cityNameRow, zjProvinceList); cityNameRow = hideInfoSheet.createRow(3); creatRow(cityNameRow, sdProvinceList); cityNameRow = hideInfoSheet.createRow(4); creatRow(cityNameRow, jxProvinceList); cityNameRow = hideInfoSheet.createRow(5); creatRow(cityNameRow, jsProvinceList); cityNameRow = hideInfoSheet.createRow(6); creatRow(cityNameRow, scProvinceList); //名称管理 //第一行设置性别信息 creatExcelNameList(workbook, HIDE_SHEET_NAME_SEX, 1, sexList.length, false); //第二行设置省份名称列表 creatExcelNameList(workbook, HIDE_SHEET_NAME_PROVINCE, 2, provinceList.length, false); //以后动态大小设置省份对应的城市列表 creatExcelNameList(workbook, provinceList[0], 3, zjProvinceList.length, true); creatExcelNameList(workbook, provinceList[1], 4, sdProvinceList.length, true); creatExcelNameList(workbook, provinceList[2], 5, jxProvinceList.length, true); creatExcelNameList(workbook, provinceList[3], 6, jsProvinceList.length, true); creatExcelNameList(workbook, provinceList[4], 7, scProvinceList.length, true); //设置隐藏页标志 workbook.setSheetHidden(workbook.getSheetIndex(EXCEL_HIDE_SHEET_NAME), true); } /** * 创建一个名称 * @param workbook */ private static void creatExcelNameList(Workbook workbook,String nameCode,int order,int size,boolean cascadeFlag){ Name name; name = workbook.createName(); name.setNameName(nameCode); name.setRefersToFormula(EXCEL_HIDE_SHEET_NAME+"!"+creatExcelNameList(order,size,cascadeFlag)); } /** * 名称数据行列计算表达式 * @param workbook */ private static String creatExcelNameList(int order,int size,boolean cascadeFlag){ char start = 'A'; if(cascadeFlag){ start = 'B'; if(size<=25){ char end = (char)(start+size-1); return "$"+start+"$"+order+":$"+end+"$"+order; }else{ char endPrefix = 'A'; char endSuffix = 'A'; if((size-25)/26==0||size==51){//26-51之间，包括边界（仅两次字母表计算） if((size-25)%26==0){//边界值 endSuffix = (char)('A'+25); }else{ endSuffix = (char)('A'+(size-25)%26-1); } }else{//51以上 if((size-25)%26==0){ endSuffix = (char)('A'+25); endPrefix = (char)(endPrefix + (size-25)/26 - 1); }else{ endSuffix = (char)('A'+(size-25)%26-1); endPrefix = (char)(endPrefix + (size-25)/26); } } return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order; } }else{ if(size<=26){ char end = (char)(start+size-1); return "$"+start+"$"+order+":$"+end+"$"+order; }else{ char endPrefix = 'A'; char endSuffix = 'A'; if(size%26==0){ endSuffix = (char)('A'+25); if(size>52&&size/26>0){ endPrefix = (char)(endPrefix + size/26-2); } }else{ endSuffix = (char)('A'+size%26-1); if(size>52&&size/26>0){ endPrefix = (char)(endPrefix + size/26-1); } } return "$"+start+"$"+order+":$"+endPrefix+endSuffix+"$"+order; } } } /** * 创建一列数据 * @param currentRow * @param textList */ private static void creatRow(Row currentRow,String[] textList){ if(textList!=null&&textList.length>0){ int i = 0; for(String cellValue : textList){ Cell userNameLableCell = currentRow.createCell(i++); userNameLableCell.setCellValue(cellValue); } } }/** * 添加数据验证选项 * @param sheet */ public static void setDataValidation(Workbook wb){ int sheetIndex = wb.getNumberOfSheets(); if(sheetIndex>0){ for(int i=0;i<sheetIndex;i++){ Sheet sheet = wb.getSheetAt(i); if(!EXCEL_HIDE_SHEET_NAME.equals(sheet.getSheetName())){ DataValidation data_validation_list = null; //省份选项添加验证数据 for(int a=2;a<3002;a++){ data_validation_list = getDataValidationByFormula(HIDE_SHEET_NAME_PROVINCE,a,2); sheet.addValidationData(data_validation_list); //城市选项添加验证数据 data_validation_list = getDataValidationByFormula("INDIRECT(B"+a+")",a,3); sheet.addValidationData(data_validation_list); //性别添加验证数据 data_validation_list = getDataValidationByFormula(HIDE_SHEET_NAME_SEX,a,1); sheet.addValidationData(data_validation_list); } } } } } /** * 使用已定义的数据源方式设置一个数据验证 * @param formulaString * @param naturalRowIndex * @param naturalColumnIndex * @return */ private static DataValidation getDataValidationByFormula(String formulaString,int naturalRowIndex,int naturalColumnIndex){ //加载下拉列表内容 DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString); //设置数据有效性加载在哪个单元格上。 //四个参数分别是：起始行、终止行、起始列、终止列 int firstRow = naturalRowIndex-1; int lastRow = naturalRowIndex-1; int firstCol = naturalColumnIndex-1; int lastCol = naturalColumnIndex-1; CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol); //数据有效性对象 DataValidation data_validation_list = new HSSFDataValidation(regions,constraint); //设置输入信息提示信息 data_validation_list.createPromptBox("下拉选择提示","请使用下拉方式选择合适的值！"); //设置输入错误提示信息 data_validation_list.createErrorBox("选择错误提示","你输入的值未在备选列表中，请下拉选择合适的值！"); return data_validation_list; } private static DataValidation getDataValidationByDate(int naturalRowIndex,int naturalColumnIndex){ //加载下拉列表内容 DVConstraint constraint = DVConstraint.createDateConstraint(DVConstraint.OperatorType.BETWEEN,"1900-01-01", "5000-01-01", "yyyy-mm-dd"); //设置数据有效性加载在哪个单元格上。 //四个参数分别是：起始行、终止行、起始列、终止列 int firstRow = naturalRowIndex-1; int lastRow = naturalRowIndex-1; int firstCol = naturalColumnIndex-1; int lastCol = naturalColumnIndex-1; CellRangeAddressList regions=new CellRangeAddressList(firstRow,lastRow,firstCol,lastCol); //数据有效性对象 DataValidation data_validation_list = new HSSFDataValidation(regions,constraint); //设置输入信息提示信息 data_validation_list.createPromptBox("日期格式提示","请按照'yyyy-mm-dd'格式输入日期值！"); //设置输入错误提示信息 data_validation_list.createErrorBox("日期格式错误提示","你输入的日期格式不符合'yyyy-mm-dd'格式规范，请重新输入！"); return data_validation_list; } } 0 0
- poi实现excel下拉联动
- poi实现生成下拉选联动
- poi实现生成下拉选联动
- POI实现EXCEL下拉列表
- EXCEL如何实现多级联动下拉菜单
- java poi3.7实现excel下拉联动
- poi excel 多级联动
- poi实现excel下拉框级联
- poi XSSFWorkbook实现excel中的下拉框
- Excel 下拉框 三级联动
- POI 创建联动下拉列表实例
- 联动下拉实现
- poi生成excel下拉菜单
- poi实现execl2007两级联动
- poi实现execl2003 两级联动
- POI 实现导出Excel
- POI导出EXCEL实现
- POI实现EXCEL
- 1.5运算符初探
- Mybatis3.x与Spring4.x整合
- 1.6空白与基本格式
- 几个最佳的WordPress SEO插件
- Android动画分类及视图动画简析
- poi实现excel下拉联动
- java中==和equals的区别
- 怎样在Spring官网下载spring低版本的jar包
- Java核心内库-线程-并行和并发
- MVP框架设计详解
- mbed serial_api模块移植到stm32f107vc（基于st32f1 cube1.4.0)
- svm 支持向量机
- u-boot-2016.05移植：(3)、识别NOR FLASH
- Java：String和Date、Timestamp之间的转换
