java POI下载自定义EXCEL模板

通过POI形成固定excel模板，然后下载

springboot添加依赖

此处引入的是easypoi依赖，也可直接引入apache的POI

		<!--easyPOI-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.4.0</version>
        </dependency>

业务处理

package com.demo.modules.poi.controller;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;

/**
 * @author 
 * @Version 1.0
 * @since 2022-06-02
 */

@RestController
@RequestMapping("/equip")
@Api(tags = "测试")
@Slf4j
public class EquipmentController {


    @GetMapping("download")
    @ApiOperation("下载数据")
    public void download(HttpServletRequest request, HttpServletResponse response) throws Exception{
        //excel第一行标题内容
        String title[] = new String[]{"姓名", "姓名全拼", "性别","身份证证件类型","身份证证件号码","出生日期","手机号码","电子邮箱"};
        String title2[] = new String[]{"王铁柱", "WangTieZhu", "男","居民身份证","110121200205101212","2002-05-10","119","12@qq.com"};
        //工作薄名称
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("用户信息表");
        //列宽
        sheet.setDefaultColumnWidth(20);
        //设置单元格下拉框
        String [] sex = {"男","女"};
        String [] identity = {"居民身份证","士官证","学生证","驾驶证","护照","港澳通行证"};
        // 设置多少列为下拉框并给赋值
        createDropDownList(sheet,sex,0,10000,2,2);
        createDropDownList(sheet,identity,0,10000,3,3);
        setHSSFPrompt(sheet,"提示","填写样式：yyyy-MM-dd",0,10000,5,5);
        // 头部樣式
        HSSFCellStyle headStyle = getHeadStyle(workbook);

        // title部分
        int rowNO = 0;
        HSSFRow row = null;
        HSSFCell cell = null;
        row = sheet.createRow(rowNO);
        row.setHeight((short) 400);
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(headStyle);
        }

        // 内容样式  两个for循环 生成单元格内容
        HSSFCellStyle normalStyle = getNormalStyle(workbook);
        // j 有多少行
        for (int j = 1; j < 5; j++) {
            int rowNO2 = j;
            HSSFRow row2 = null;
            HSSFCell cell2 = null;
            row2 = sheet.createRow(rowNO2);
            row2.setHeight((short) 400);
            for (int i = 0; i < title2.length; i++) {
                cell2 = row2.createCell(i);
                cell2.setCellValue(title2[i]);
                cell2.setCellStyle(normalStyle);
            }
        }






        //excel文件名
        String fileName = "用户信息模板.xls";
        //告诉浏览器返回文件下载
        response.setContentType("application/download;charset=UTF-8");
        //激活文件下载保存框
        response.setHeader("Content-disposition", "attachment;filename=\""
                + new String(fileName.getBytes("UTF-8"), "ISO8859_1") + "\"");
        OutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
    }


    private HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
        HSSFCellStyle headStyle = workbook.createCellStyle();
        // 设置背景色
        headStyle.setFillForegroundColor((short) 70);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        HSSFDataFormat dataFormat = workbook.createDataFormat();
        headStyle.setDataFormat(dataFormat.getFormat("@"));
        // 居中
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        HSSFDataFormat format = workbook.createDataFormat();
        //设置单元格格式为常规
        headStyle.setDataFormat(format.getFormat("@"));
        //设置头格式(第一行)
        HSSFCellStyle columnHeadStyle = workbook.createCellStyle();
        // 居中
        columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
        return headStyle;
    }

    private HSSFCellStyle getNormalStyle(HSSFWorkbook workbook) {
        HSSFCellStyle normalStyle = workbook.createCellStyle();
        // 加边框
        normalStyle.setAlignment(HorizontalAlignment.CENTER);
        normalStyle.setBorderBottom(BorderStyle.MEDIUM);
        normalStyle.setBottomBorderColor(IndexedColors.BLACK1.index);
        normalStyle.setBorderLeft(BorderStyle.MEDIUM);
        normalStyle.setLeftBorderColor(IndexedColors.BLACK1.index);
        normalStyle.setBorderRight(BorderStyle.MEDIUM);
        normalStyle.setRightBorderColor(IndexedColors.BLACK1.index);
        normalStyle.setBorderTop(BorderStyle.MEDIUM);
        normalStyle.setTopBorderColor(IndexedColors.BLACK1.index);

        HSSFDataFormat format = workbook.createDataFormat();
        //设置单元格格式为常规
        normalStyle.setDataFormat(format.getFormat("@"));
        return normalStyle;
    }







    /**
     * 设置下拉框的方法
     * @param sheet : 传参 哪一sheet的表
     * @param values : 下拉框的内容
     * @param firstRow : 下拉框从哪一行开始
     * @param lastRow : 下拉框到哪一行结束
     * @param firstCol : 下拉框从哪一列开始
     * @param lastCol : 下拉框到哪一列结束
     */
    public void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }
    /**
     * 设置单元格上提示
     *
     * @param sheet
     *            要设置的sheet.
     * @param promptTitle
     *            标题
     * @param promptContent
     *            内容
     * @param firstRow
     *            开始行
     * @param endRow
     *            结束行
     * @param firstCol
     *            开始列
     * @param endCol
     *            结束列
     * @return 设置好的sheet.
     */
    public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle,
                                          String promptContent, int firstRow, int endRow, int firstCol,
                                          int endCol) {
        // 构造constraint对象
        DVConstraint constraint = DVConstraint
                .createCustomFormulaConstraint("BB1");
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_view = new HSSFDataValidation(
                regions, constraint);
        data_validation_view.createPromptBox(promptTitle, promptContent);
        sheet.addValidationData(data_validation_view);
        return sheet;
    }


}