生成excel模板（下拉选、多级联动）

写excel模板

package com;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.security.SecureRandom;
import java.util.*;

public class DownExcelUtil {
    public static void main(String[] args) {
        getFile();
    }

    public static void getFile(){
        FileOutputStream outputStream = null;
        String path = "";
        try{
            //创建模板.xlsx格式
            XSSFWorkbook workbook = new XSSFWorkbook();
            //创建一个sheet表，可设置多个sheet
            Sheet sheet = workbook.createSheet("绩效考核结果");
            sheet.setDefaultColumnWidth(15);
            //获取模板表头
            HashMap<String,String> map = findTemplate();
            //生成模板表头
            createTemplate(workbook, sheet, map);
            List<Integer> countList = new ArrayList<Integer>();
            countList.add(4);
            countList.add(8);
            List<String> assessList = new ArrayList<String>();
            assessList.add("A");
            assessList.add("B");
            List<String> provinceList = new ArrayList<String>();
            provinceList.add("湖北省");
            provinceList.add("广东省");
            List<String> cityList = new ArrayList<String>();
            cityList.add("黄冈市");
            cityList.add("武汉市");
            cityList.add("黄石市");
            cityList.add("深圳市");
            cityList.add("广州市");
            cityList.add("惠州市");
            cityList.add("东莞市");

            //生成文件名
            String excelName = "performance.result";
            String displayName = String.format("%s_Template.xlsx",excelName);
            String fileName = displayName;
            path = "D:\\appfile\\temp\\downloadTempFile" + File.separator + fileName;
            //创建说明Sheet页
            createExplainSheet(workbook);
            //创建省、市、考核绩效sheet页
            createOtherSheet(workbook,"省",provinceList);
            createOtherSheet(workbook,"市",cityList);
            createOtherSheet(workbook,"考核绩效",assessList);

            //设置月份下拉选
            String[] monthArr = {"202007"};
            createSelect(sheet, 0, monthArr);
            //设置省、市、考核绩效下拉选
            createSelectSheetGrade(workbook, sheet, provinceList, countList);
            createSelectSheet(workbook, sheet, 3,"考核绩效",assessList.size()+1);

            //将excel写入
            outputStream = FileUtils.openOutputStream(new File(path));
            workbook.write(outputStream);
            outputStream.flush();
            workbook.close();
        }catch (IOException e){
            System.out.println(e);
        }finally {
            try {
                outputStream.close();
            }catch (IOException e){
                System.out.println(e);
            }
        }
        System.out.println("pathNewUrl:"+ path);
    }

    //生成模板表头
    public static void createTemplate(XSSFWorkbook workbook, Sheet sheet, HashMap<String,String> map){
        String[] tableHeaderTemp = map.get("performanceResultsTemplateTemp").toString().split(",");
        String[] tableHeader = map.get("performanceResultsTemplate").toString().split(",");
        XSSFRow firstRow = (XSSFRow)sheet.createRow((short)0);
        firstRow.setHeight((short)600);
        for(int i=0;i<tableHeader.length;i++){
            XSSFCell cell = firstRow.createCell((short)i);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            if(i<5){
                XSSFRichTextString richString = new XSSFRichTextString();
                richString.append("*",createFont(workbook,HSSFColor.HSSFColorPredefined.RED.getIndex()));
                richString.append(tableHeaderTemp[i],createFont(workbook,HSSFColor.HSSFColorPredefined.BLACK.getIndex()));
                cell.setCellValue(richString);
            }else{
                cell.setCellValue(tableHeaderTemp[i]);
            }
        }
        XSSFRichTextString richStringLast = new XSSFRichTextString();
        richStringLast.append("*",createFont(workbook,HSSFColor.HSSFColorPredefined.RED.getIndex()));
        richStringLast.append("考核总得分",createFont(workbook,HSSFColor.HSSFColorPredefined.BLACK.getIndex()));
        XSSFCell cellFirstLast = firstRow.createCell((short)tableHeader.length);
        cellFirstLast.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
        cellFirstLast.setCellValue(richStringLast);
        for(int i=tableHeader.length;i<tableHeaderTemp.length;i++){
            XSSFCell cell = firstRow.createCell((short)i+1);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            cell.setCellValue(tableHeaderTemp[i]);
        }
        firstRow.setZeroHeight(true);
        XSSFRow secondRow = (XSSFRow)sheet.createRow((short)1);
        secondRow.setHeight((short)600);
        for(int i=0;i<tableHeader.length;i++){
            XSSFCell cell = secondRow.createCell((short)i);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            if(i<5){
                XSSFRichTextString richString = new XSSFRichTextString();
                richString.append("*",createFont(workbook,HSSFColor.HSSFColorPredefined.RED.getIndex()));
                richString.append(tableHeader[i],createFont(workbook,HSSFColor.HSSFColorPredefined.BLACK.getIndex()));
                cell.setCellValue(richString);
            }else{
                cell.setCellValue(tableHeader[i]);
            }
        }

        //生成最后一行*考核总得分
        XSSFCell cellSecondLast = secondRow.createCell((short)tableHeader.length);
        cellSecondLast.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
        cellSecondLast.setCellValue(richStringLast);
    }

    //获取模板表头
    public static HashMap<String,String> findTemplate(){
        HashMap<String,String> map = new HashMap<String,String>();
        StringBuilder performanceResultsTemplate = new StringBuilder();
        StringBuilder performanceResultsTemplateTemp = new StringBuilder();
        performanceResultsTemplate.append("月份,省,市,考核绩效");
        performanceResultsTemplateTemp.append("月份,省,市,考核绩效,动态列1,动态列2,动态列3,动态列4,动态列5,动态列6," +
                "动态列7,动态列8,动态列9,动态列10,动态列11,动态列12,动态列13,动态列14,动态列15,动态列16,动态列17,动态列18," +
                "动态列19,动态列20,动态列21,动态列22,动态列23,动态列24,动态列25,动态列26,动态列27,动态列28,动态列29,动态列30");
        map.put("performanceResultsTemplate",performanceResultsTemplate.toString());
        map.put("performanceResultsTemplateTemp",performanceResultsTemplateTemp.toString());
        return map;
    }

    /**
     * 设置单元格样式
     * @param workbook
     * @param color 单元格背景色
     * @param n 0默认字体，1加粗字体
     * @return
     */
    public static CellStyle createStyle(XSSFWorkbook workbook, short color, int n){
        //表头样式
        CellStyle defaultStyle = workbook.createCellStyle();
        defaultStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        defaultStyle.setBorderRight(BorderStyle.THIN);//右边边框
        defaultStyle.setBorderTop(BorderStyle.THIN);//顶部边框
        defaultStyle.setFillForegroundColor(color);//设置背景色
        defaultStyle.setBorderBottom(BorderStyle.THIN);//底部边框
        defaultStyle.setBorderLeft(BorderStyle.THIN);//左边边框
        defaultStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        defaultStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //生成一个字体
        XSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());//字体颜色
        font.setFontHeightInPoints((short)12);//字体大小
        if(n==1){
            font.setBold(true);
        }
        //把字体应用到当前的样式
        defaultStyle.setFont(font);
        return defaultStyle;
    }

    /**
     * 设置单元格样式
     * @param workbook
     * @param color 字体颜色
     * @return
     */
    public static XSSFFont createFont(XSSFWorkbook workbook, short color){
        XSSFFont defaultFont = workbook.createFont();
        defaultFont.setFontName("宋体");
        defaultFont.setColor(color);
        defaultFont.setFontHeightInPoints((short)12);
        defaultFont.setBold(true);
        return defaultFont;
    }

    /**
     * 创建下拉选Sheet
     * @param workbook
     * @param sheetName
     * @param list 下拉选列表
     */
    public static void createOtherSheet(XSSFWorkbook workbook, String sheetName, List<String> list){
        Sheet sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(32);
        //创建标题
        XSSFRow rowTitle = (XSSFRow)sheet.createRow(0);
        XSSFCell cellTitle = rowTitle.createCell(0);
        cellTitle.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
        cellTitle.setCellValue(sheetName);
        //创建新行
        for(int i=0;i<list.size();i++){
            //i+1:第一行为标题
            XSSFRow row = (XSSFRow)sheet.createRow(i+1);
            XSSFCell cell = row.createCell(0);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 0));
            cell.setCellValue(StringUtils.isBlank(list.get(i))?"":list.get(i).toString());
        }
    }

    /**
     * 设置下拉选值（直接写值进入，少数值才可以用此方式，否则会报错）
     * @param sheet
     * @param col 第几列
     * @param arr 下拉选数组
     */
    public static void createSelect(Sheet sheet, int col, String[] arr){
        CellRangeAddressList regions = new CellRangeAddressList(2,1000000,col,col);
        DataValidationHelper help = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = help.createExplicitListConstraint(arr);
        DataValidation validation = help.createValidation(constraint,regions);
        sheet.addValidationData(validation);
    }

    /**
     * 设置下拉选值（以sheet页做为下拉选值）
     * @param workbook
     * @param sheet
     * @param col 第几列
     * @param sheetName
     * @param count 下拉选列表数量+1
     */
    public static void createSelectSheet(XSSFWorkbook workbook, Sheet sheet, int col, String sheetName, int count){
        XSSFName nameCell = workbook.createName();
        nameCell.setNameName(sheetName);
        nameCell.setRefersToFormula(sheetName+"!$A$2:$A$"+count);
        CellRangeAddressList regions = new CellRangeAddressList(2,1000000,col,col);
        DataValidationHelper help = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = help.createFormulaListConstraint(sheetName);
        DataValidation validation = help.createValidation(constraint,regions);
        sheet.addValidationData(validation);
    }

    //设置多级下拉选值（以sheet页做为下拉选值，名称管理器不能有空格等特殊字符，否则会报错）
    public static void createSelectSheetGrade(XSSFWorkbook workbook, Sheet sheet, List<String> list, List<Integer> countList){
        XSSFName nameCell = workbook.createName();
        nameCell.setNameName("省");
        nameCell.setRefersToFormula("省"+"!$A$2:$A$"+list.size()+1);
        CellRangeAddressList regionsSecond = new CellRangeAddressList(2,1000000,1,1);
        DataValidationHelper helpSecond = sheet.getDataValidationHelper();
        DataValidationConstraint constraintSecond = helpSecond.createFormulaListConstraint("省");
        DataValidation validationSecond = helpSecond.createValidation(constraintSecond,regionsSecond);
        sheet.addValidationData(validationSecond);

        //循环设置市
        for(int i=0;i<list.size();i++){
            nameCell = workbook.createName();
            try{
                nameCell.setNameName(list.get(i).replaceAll(" ",""));
            }catch (Exception e){
                nameCell.setNameName("市"+new SecureRandom().nextDouble());
            }
            if(i==0){
                nameCell.setRefersToFormula("市"+"!$A$2:$A$"+countList.get(i));
            }else{
                if(countList.get(i-1)!=countList.get(i)){
                    nameCell.setRefersToFormula("市"+"!$A$"+(countList.get(i-1)+1)+":$A$"+countList.get(i));
                }else{
                    nameCell.setRefersToFormula("市"+"!$A$"+(countList.get(list.size()-1)+1)+":$A$"+countList.get(list.size()-1)+1);
                }
            }
        }
        CellRangeAddressList regions = new CellRangeAddressList(2,1000000,2,2);
        DataValidationHelper help = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = help.createFormulaListConstraint("INDIRECT(SUBSTITUTE($B3,\"\",\"\"))");//=INDIRECT(SUBSTITUTE($A1,"",""))
        DataValidation validation = help.createValidation(constraint,regions);
        sheet.addValidationData(validation);
    }

    //创建说明sheet页
    public static void createExplainSheet(XSSFWorkbook workbook){
        String[] cells = {"月份","省","市","考核绩效","XX指标","XX得分","考核总得分"};
        String[] cellsFirst = {"下拉选择","下拉选择","下拉选择","下拉选择","文字或百分比","数值","数值"};
        String[] cellsSecond = {"202009","湖北省","黄冈市","A","30%","1","2.02"};
        Sheet sheet = workbook.createSheet("说明");
        sheet.setDefaultColumnWidth(15);
        //创建标题
        XSSFRow rowTitle = (XSSFRow)sheet.createRow(0);
        XSSFCell cellExplain = rowTitle.createCell(1);
        cellExplain.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 1));
        cellExplain.setCellValue("说明");
        XSSFCell cellExample = rowTitle.createCell(2);
        cellExample.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 1));
        cellExample.setCellValue("举例");
        //填写字段说明
        for(int i=0;i<cells.length;i++){
            XSSFRow row = (XSSFRow)sheet.createRow(i+1);
            XSSFCell cell = row.createCell(0);
            cell.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.GOLD.getIndex(), 1));
            cell.setCellValue(cells[i]);

            XSSFCell cellFirst = row.createCell(1);
            cellFirst.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 0));
            cellFirst.setCellValue(cellsFirst[i]);

            XSSFCell cellSecond = row.createCell(2);
            cellSecond.setCellStyle(createStyle(workbook, HSSFColor.HSSFColorPredefined.WHITE.getIndex(), 0));
            cellSecond.setCellValue(cellsSecond[i]);
        }
    }
}

pom文件引用

<dependencies>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!--支持xlsx读取-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.8.0</version>
        </dependency>
    </dependencies>

控制台打印

pathNewUrl:D:\appfile\temp\downloadTempFile\performance.result_Template.xlsx

excel文件