poi 导出

@ApiOperation(value = "导入模板下载" ,notes = "导入模板下载")
    @GetResource(name = "导入模板下载", path = "/excelTemplate")
    public void excelTemplate(HttpServletRequest request,HttpServletResponse response){
        try {
            // 设置 ContentType 和返回头
            response.setContentType("application/binary;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("采样点导入模板"+".xls", "UTF-8"));

            hssfWorkbookUtil.getSamplingPointHSSF(request,response);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;
import java.util.stream.Collectors;


/**
 * @Description:
 * @Author: fan
 * @Version: 1.0
 * @CreateTime: 2022/11/10 8:46
 */
@Component
public class HSSFWorkbookUtil {

    @Resource
    DictService dictService;

    @Resource
    DictTypeService dictTypeService;


    /**
     * 采样点模板导出
     *
     * @param request  请求
     * @param response 响应
     */
    public void getSamplingPointHSSF(HttpServletRequest request, HttpServletResponse response) throws IOException {

        HSSFWorkbook workbook = new HSSFWorkbook();

        // 数据页
        setDate(workbook);

        // 字典页
        setDict(workbook);

        OutputStream out = response.getOutputStream();
        // 将数据输出，刷出，关闭
        workbook.write(out);
        out.flush();
        out.close();

    }

    private void setDate(HSSFWorkbook workbook) {

        // 一级标题
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);



        // 创建一个sheet页
        HSSFSheet dateSheet = workbook.createSheet(SAMPLING_POINT_SHEET_NAME);

        String[] title = {"编号", "区域","环境类型", "频率", "采样点名称", "规则"};

        // 创建两行，并合，得到标题
        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, title.length - 1);
        dateSheet.addMergedRegion(cellRangeAddress);
        HSSFCell cell = dateSheet.createRow(0).createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue("采样点导入");

        HSSFCellStyle titleStyle = creatStyle(workbook);

        // 写入二级标题
        HSSFRow row = dateSheet.createRow(2);
        for (int i = 0; i < title.length; i++) {
            dateSheet.setColumnWidth(i, title[i].getBytes(StandardCharsets.UTF_8).length * 256);
            HSSFCell titleCell = row.createCell(i);
            titleCell.setCellStyle(titleStyle);
            titleCell.setCellValue(title[i]);
        }

    }

    private void setDict(HSSFWorkbook workbook) {

        String[] titleCode = {"environment" ,"area" ,"frequency"};

        Map<String, SysDictType> dictTypeMap =
                dictTypeService.list(new QueryWrapper<SysDictType>().lambda().in(SysDictType::getDictTypeCode, Arrays.asList(titleCode)))
                .stream()
                .collect(Collectors.toMap(SysDictType::getDictTypeCode, Function.identity()));


        HSSFCellStyle titleStyle = creatStyle(workbook);
        // 创建字典页
        HSSFSheet dictSheet = workbook.createSheet("数据字典");
        // 创建标题行
        HSSFRow row = dictSheet.createRow(0);

        AtomicInteger index = new AtomicInteger(0);
        AtomicInteger maxLine = new AtomicInteger(1);

        dictTypeMap.forEach((k,v)->{
            // 创建单元格
            HSSFCell cell = row.createCell(index.getAndIncrement());
            cell.setCellStyle(titleStyle);
            cell.setCellValue(v.getDictTypeName());

            // 查询字典数据
            DictRequest dictRequest = new DictRequest();
            dictRequest.setDictTypeCode(k);
            List<SysDict> dictList = dictService.findList(dictRequest);

            for (int i = 0; i < dictList.size(); i++) {
                // 填写字典数据
                // 从第二行开始写
                HSSFRow row1 = null;
                if(i+1 >= maxLine.get()){
                    row1 = dictSheet.createRow(maxLine.getAndIncrement());
                }else{
                    row1 =dictSheet.getRow(i+1);
                }
                row1.createCell(index.get() - 1).setCellValue(dictList.get(i).getDictName());
            }

        });

    }

    /**
     * 创建样式
     * @param workbook
     * @return
     */
    private HSSFCellStyle creatStyle(HSSFWorkbook workbook){
        // 二级标题
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        // 字体
        HSSFFont font = workbook.createFont();
        //字体样式
        font.setFontName("微软雅黑");
        // 加粗
        font.setBold(true);
        // 大小
        font.setFontHeightInPoints((short) 11);

        titleStyle.setFont(font);

        return titleStyle;
    }
}