POI实现导出Excel级联操作
来源：互联网 发布：手机游戏源码论坛 编辑：程序博客网 时间：2024/06/07 07:55
百度经验关于Eecel怎么设置级联下拉的设置
http://jingyan.baidu.com/article/afd8f4de98dad134e286e9a9.html
package com.test;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 POICascadeTest { 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); //创建隐藏的Sheet页 creatExcelHidePage(wb); //数据验证 setDataValidation(wb); FileOutputStream fileOut; try { fileOut = new FileOutputStream("f://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-1)+")",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操作导出excel、
- poi导出excel操作
- POI 实现导出Excel
- POI导出EXCEL实现
- POI 实现导出Excel
- EXCEL导出POI实现
- java操作poi导出Excel
- poi操作excel导入\导出
- POI操作 导出Excel表格
- poi实现excel下拉框级联
- POI导出EXCEL经典实现
- POI导出EXCEL经典实现
- POI导出EXCEL经典实现
- POI导出EXCEL经典实现
- POI导出Excel简单实现
- POI导出EXCEL经典实现
- POI导出EXCEL经典实现
- OpenCV 3学习笔记（二） 图像的显示、腐蚀与模糊
- Java 集合
- C-C++练习10
- 获取浏览器相关参数的类
- 基于百度地图的自定义导航app-西华易导航（终）
- POI实现导出Excel级联操作
- 看到她们的妈妈, 也难怪“小芈月”“阿拉蕾”“小苹果”长那么好看
- 20161217
- 基于Maven在Spring中集成CXF，发布Web Service
- Notification---Android
- JS中的函数提升及变量提升
- 树莓派使用迅雷远程下载
- 倒计时(使用GCD)
- ActiveMQ实战
