Java下使用Apache POI生成具有三级联动下拉列表的Excel文档
使用Apache POI生成具有三级联动下拉列表的Excel文档；
具体效果图与代码如下文。先上效果图：
开始贴代码，代码中部分测试数据不影响功能。
第一部分（核心业务处理）：
此部分包含几个方面：
- 获取三级下拉框各列的数据；
- 创建每个下拉功能的名称管理器
- 在隐藏的sheet中生成下拉菜单所需要的row
代码如下：
/**
* 第一部分
* 将三个列表所有字段从数据库查询出，并生成名称管理器，存放至隐藏的sheet中
*/
private static HSSFWorkbook writePorpData() {
int index = 1;
HSSFWorkbook wb = new HSSFWorkbook(); //Excel工作簿创建
wb.createSheet(DICT_SHEET_TEST); //创建主工作表sheet
Sheet dictDataSheet = wb.createSheet(DICT_SHEET_DATA); //创建数据源字段sheet
List<Province> provinceList = GetData.getProvinces(); //获取所有省份 --测试数据，不影响功能
List<String> provinceNames = new ArrayList<String>(); //1.存放所有省份的名称
provinceNames.add(" "); //使下拉框有置空的选择
//遍历每个省份
for (Province province : provinceList) {
String proName = province.getProvinceName(); //获取每个省份名称
provinceNames.add(proName);
String provinceId = province.getProvinceId(); //获取每个省份Id
List<Area> areaList = GetData.getAreas(provinceId); //获取每个地区 --测试数据，不影响功能
List<String> areaNames = new ArrayList<String>(); //2.存放所有地区名称
areaNames.add(" "); //使下拉框有置空的选择
//遍历每个地区
for (Area area : areaList) {
String areaName = area.getAreaName();
areaNames.add(areaName);
String areaId = area.getAreaId();
List<City> cityList = GetData.getCities(areaId); //获取每个城市 --测试数据，不影响功能
List<String> cityNames = new ArrayList<String>(); //3.存放所有城市名称
cityNames.add(" "); //使下拉框有置空的选择
//遍历每个城市
for (City city : cityList) {
String cityName = city.getCityName();
cityNames.add(cityName);
}
cityNames.add(0, areaName);
createRowData(dictDataSheet.createRow(index++),cityNames);// 3.创建城市row
int i2 = 0;
createExcelName(wb,cityNames.get(i2++),index,cityNames.size()-1,true); //3.城市row，指定名称管理
}
areaNames.add(0, proName);
createRowData(dictDataSheet.createRow(index++),areaNames);// 2.创建地区row
int i1 = 0;
createExcelName(wb,areaNames.get(i1++),index,areaNames.size()-1,true); //2.地区row，指定名称管理
}
createRowData(dictDataSheet.createRow(0),provinceNames); // 1.创建省份row，写入数据
createExcelName(wb,DICT_MNGNAME,1,provinceNames.size()-1, false); //1.省份row，指定名称管理
wb.setSheetHidden(wb.getSheetIndex(DICT_SHEET_DATA), true); //设置隐藏的sheet
return wb;
}
第二部分：
此部分方法都是第一部分核心处理所要使用的几个函数：
- 创建隐藏sheet数据行的函数
- 创建名称管理器的函数
- 创建名称管理器所需要的：计算列的表达式的函数
- 设置数据有效性的函数
- 数据验证的函数
代码如下：
/**
* 第二部分：2.1 创建隐藏sheet数据行的函数
*/
private static void createRowData(Row curRow,List<String> dataList){
if(dataList != null && dataList.size()>0){
int m = 0;
for (String dataValue : dataList) {
Cell dataCell = curRow.createCell(m++);
dataCell.setCellValue(dataValue);
}
}
}
/**
* 第二部分：2.2 创建名称管理器的函数 每一行数据创建一个
*/
private static void createExcelName(HSSFWorkbook workbook,String nameCode,int order,int size,boolean cascadeFlag){
Name name;
name = workbook.createName();
name.setNameName(nameCode);
String cellString = DICT_SHEET_DATA + "!" + createExcelNameList(order,size,cascadeFlag);
name.setRefersToFormula(cellString);
}
/**
* 第二部分：2.3 名称数据行列计算表达式
*/
private static String createExcelNameList(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;
}
}
}
/**
* 第二部分：2.4 设置数据的有效性，即下拉列表的生成
*/
public static HSSFWorkbook getWorkbook(HSSFWorkbook wb, int size){
Sheet sheet = wb.getSheet(DICT_SHEET_TEST);
DataValidation dataValidation = null;
for (int x = 1; x <= size+1; x++) {
dataValidation = getDataValidation("IF($B$"+x+"=\" \",\" \",INDIRECT($B$"+x+"))", x, 3);
sheet.addValidationData(dataValidation);
dataValidation = getDataValidation("IF($C$"+x+"=\" \",\" \",INDIRECT($C$"+x+"))", x, 4);
sheet.addValidationData(dataValidation);
}
return wb;
}
/**
* 第二部分：2.5 数据验证
*/
@SuppressWarnings("deprecation")
private static DataValidation getDataValidation(String formulaString,int naturalRowIndex,int naturalColIndex){
//设置数据有效性加载在哪个单元格上 四个参数：起始行、终止行、起始列、终止列
int firstRow = naturalRowIndex-1;
int lastRow = naturalRowIndex-1;
int firstCol = naturalColIndex-1;
int lastCol = naturalColIndex-1;
CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
//加载下拉列表
DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
//数据有效性对象
DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
//设置输入信息提示信息
dataValidation.createPromptBox("下拉提示", "请选择合适的值");
//设置输入错误提示信息
dataValidation.createErrorBox("非法输入", "不允许输入，请选取下拉值！");
return dataValidation;
}
第三部分：
此部分即获得上两部处理完成后的工作簿，然后填充数据即可。
代码如下：
/**
* 创建并生成excel文档
*/
public static void createExcelFile(){
List<Student> studens = GetData.getStudents(); //测试数据，不影响功能
try {
FileOutputStream fileOutputStream = new FileOutputStream(new File(filePathName));
HSSFWorkbook wb = writePorpData(); // 创建工作簿
HSSFSheet sheet = wb.getSheet(DICT_SHEET_TEST); // 获取主工作表
wb = getWorkbook(wb, studens.size());
HSSFRow row = null;
HSSFCell cell = null;
sheet.setDefaultColumnWidth(28);
row = sheet.createRow(0); // 新增标题行
cell = row.createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("学生");
cell = row.createCell(1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("省份");
cell = row.createCell(2);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("地区");
cell = row.createCell(3);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("城市");
int i = 1;
for (Student stu : studens) {
row = sheet.createRow(i); // 新增一行
cell = row.createCell(0);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(stu.getStudentName()); // 学生姓名
cell = row.createCell(1);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(stu.getProvince()); //省份
cell = row.createCell(2);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(stu.getArea()); // 地区
cell = row.createCell(3);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(stu.getCity()); //城市
i++;
}
sheet.setColumnWidth(0,5000);//设置列宽
sheet.setColumnWidth(1,5000);//设置列宽
sheet.setColumnWidth(2,5000);//设置列宽
sheet.setColumnWidth(3,5000);//设置列宽
wb.write(fileOutputStream); //生成文档
fileOutputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
- 附上源码下载：源码
