springboot中开发导出excel文件功能，实现写入标题、写入表头、写入数据、写入下拉框功能

效果

引入pom依赖

 <!--导入导出excel-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>

Controller层接口代码

	@ApiOperation("下载模板")
    @GetMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) {
        try {
            //1 在这里定义下拉框中的数据(不需要下拉框的话这块代码可以去掉，下面调用setExcelData工具类时不传这个shiftMarks 参数就可以了)  
            //手动定义下拉框中数据
            List<String> selects= new ArrayList<>();
            //selects.add("选项1");
            //selects.add("选项2");
            //selects.add("选项3");
            
            //2 在这里定义写入表中的数据(不需要写入数据的话这块代码可以去掉，下面调用setExcelData工具类时不传这个datas参数就可以了)  
            List<List<String>> datas = new ArrayList<>();
           	//List<String> list = new ArrayList<>();
            //list.add("数据1");
            //list.add("数据2");
            //list.add("数据3");
            //datas.add(list);
            
            //调用下面工具类中的方法
            HSSFWorkbook wb = checkDownloadExcel.setExcelData("2022-10",selects,datas);
            
            OutputStream output = null;
            String fileName = "文件名.xls";
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName);
            // 服务端要在header设置Access-Control-Expose-Headers, 前端才能正常获取到
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            output = response.getOutputStream();
            wb.write(output);
            output.flush();
            output.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

checkDownloadExcel工具类

	/**
     * @Author: ljh
     * @Description:
     * @DateTime: 14:17 2022/10/29
     * @Params: dateTime  时间yyyy-MM
     * @Params: selectList 下拉框中的数据
     * @Params: dataList 写入表的数据
     * @Return
     */
    public HSSFWorkbook setExcelData(String dateTime, List<String> selectList, List<List<String>> dataList) {
        //1 在这里定义表头数据headerList，我这里是根据时间dateTime动态生成的表头信息
        List<String> headerList = new ArrayList();
        headerList.add("序号");
        headerList.add("姓名");
        headerList.add("手机号");
        headerList.add("部门");
        //1.1 获取当月日期
        List<String> months = DateTimeUtils.getDayByMonth(Integer.valueOf(dateTime.substring(0, dateTime.indexOf("-"))), Integer.valueOf(dateTime.substring(dateTime.indexOf("-") + 1, dateTime.length())));
        //1.2 获取当月星期
        for (String day : months) {
            headerList.add(day.substring(day.lastIndexOf("-") + 1, day.length()) + "日/" + DateTimeUtils.dateToWeek(day));
        }
        String[] headers = headerList.toArray(new String[headerList.size()]);


        //2 下拉框数据
        String[] selects = selectList.toArray(new String[selectList.size()]);

        return setExcelStyle(headers, selects, dataList, dateTime);

    }


    /**
     * @Author: ljh
     * @Description: 设置样式和写入数据
     * @DateTime: 14:17 2022/10/29
     * @Params: headers表头数据  selects下拉框数据  datas写入的真实数据  date时间
     * @Return
     */
    public HSSFWorkbook setExcelStyle(String[] headers, String[] selects, List<List<String>> datas, String date) {
        //1 创建工作薄
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("排版设置");

        //2 设置标题样式
        HSSFCellStyle titleStyle = wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);//左右居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) 15);//字体大小
        titleFont.setFontName("黑体");//字体样式
        titleStyle.setFont(titleFont);

        //3 设置表头样式
        HSSFCellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setFontName("黑体");
        headerStyle.setFont(headerFont);

        //6 指定合并开始行、合并结束行 合并开始列、合并结束列
        CellRangeAddress rangeAddress = new CellRangeAddress(0, 0, 0, headers.length - 1);
        //添加要合并地址到表格
        sheet.addMergedRegion(rangeAddress);

        //7 添加标题和标题样式
        HSSFRow row0 = sheet.createRow((int) 0);
        row0.setHeightInPoints(40);
        HSSFCell cell0 = row0.createCell(0);
        String year = date.substring(0, date.indexOf("-"));
        String month = date.substring(date.indexOf("-") + 1);
        cell0.setCellValue("出勤情况统计表（" + year + "年" + month + "月）");
        cell0.setCellStyle(titleStyle);

        //在excel表中添加表头
        HSSFRow row = sheet.createRow((int) 1);
        for (int j = 0; j < headers.length; j++) {
            HSSFCell cell = row.createCell(j);
            cell.setCellValue(headers[j]);
            cell.setCellStyle(headerStyle);
        }

        //9 数据样式  如不需要写入数据这块代码可以去掉
        HSSFCellStyle dataStyle = wb.createCellStyle();
        //设置居中样式，水平居中
        dataStyle.setAlignment(HorizontalAlignment.CENTER);

        //写入数据  如不需要写入数据这块代码可以去掉
        int index = 2;
        for (int i = 0; i < datas.size(); i++) {
            // 默认的行数从0开始，为了统一格式设置从2开始，就是从excel的第三行开始
            row = sheet.createRow(index);
            index++;
            List<String> data = datas.get(i);
            for (int j = 0; j < data.size(); j++) {
                Cell cell = row.createCell(j);
                // 为当前列赋值
                cell.setCellValue(data.get(j));
                //设置数据的样式
                cell.setCellStyle(dataStyle);
            }
        }

        //设置下拉框 如不需要设置下拉框这块代码可以去掉
        if(datas.size() == 0){
            setHSSFValidation(sheet, selects, 2, 100, 4, headers.length - 1);
        }else{
            setHSSFValidation(sheet, selects, 2, datas.size()+1, 4, headers.length - 1);
        }


        return wb;
    }


    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框
     *
     * @param sheet    HSSFSheet sheetlist = wb.createSheet("XXXX");工作表对象
     * @param textlist 下拉框内容
     * @param firstRow 添加下拉框对应开始行
     * @param endRow   添加下拉框对应结束行
     * @param firstCol 添加下拉框对应开始列
     * @param endCol   添加下拉框对应结束列
     * @return
     */
    private HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        //这里将下拉框进行拆分存储在每一个单元格  并进行隐藏  防止下拉框数据过多存储在一个单元格的字符数大于255而报错
        HSSFWorkbook workbook = sheet.getWorkbook();
        Sheet hidden = workbook.createSheet("hidden");
        Cell cell = null;
        for (int i = 0, length = textlist.length; i < length; i++) {
            String name = textlist[i];
            Row roww = hidden.createRow(i);
            cell = roww.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName("hidden");
        namedCell.setRefersToFormula("hidden!$A$1:$A$" + textlist.length);
        //将第二个sheet页设置为隐藏
        workbook.setSheetHidden(1, true);
        //将名称为hidden的数据进行加载
        DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
        //设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
        return sheet;
    }