easypoi动态表头导出数据

需求：动态导出某年某月用户和用户评分数据信息，表头(序号、姓名、用户姓名)，数据(所有用户对应的评分以及平均分)；

分析：1、表头除过序号、姓名，用户姓名要动态生成；

2、用户评分信息要和表头中的用户一一对应；

1、maven依赖

<!-- 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>

2、数据表结构

2.1用户表

2.2 评分表

2.3 sql

select 
tu.id as userId,//用户id（被评分人id）
tu.real_name as realName, //用户姓名（被评分人）
GROUP_CONCAT(ts.scorer_id  SEPARATOR ',') as scorerId,//该用户对应的所有评分人id,用逗号隔开
GROUP_CONCAT(ts.score SEPARATOR ',') as scoreStr,//该用户的所有评分分数,用逗号隔开
ROUND(avg(ts.score),3) as avgScore//该用户评分平均数
from t_user tu left join t_score ts 
on tu.id  = ts.user_id 
where ts.`year` = '2024' and ts.`month` = '5'
GROUP by userId ,realName
order by  avgScore;

3、代码

1、查询表头所有用户，即可知道表头总列数

2、对应上面sql,查询用户评分数据

3、被评分人分数按照评分人userDOS顺序设置

4、评分人是固定的，但是所有评分人不一定都给(用户)被评分人评分，如果评分人还未评分，分数则为0

5、scoreList 中的数据即是按照表头评分人评的分数有序排列(如果评分人还未评分，分数则为0)

6、excel title

7、excel data

8、规则设置高度

9、表头设置样式

3.1 controller

/**
     * Excel导出
     */
    @GetMapping("/excel")
    public Result excelDownload( HttpServletResponse response, HttpServletRequest request,
            @RequestParam(name = "year",required = false) String year,
            @RequestParam(name = "month",required = false) String month) throws Exception{
        scoreService.excelDownload(response,request,year,month);
        return ResultGenerator.genOkResult();
    }

3.2实现类

@Override
    public void excelDownload(HttpServletResponse response, HttpServletRequest request,String year, String month) throws IOException {
        LocalDate currentDate = LocalDate.now();
        String currentYear = String.valueOf(currentDate.getYear());
        String currentMonth = String.valueOf(currentDate.getMonthValue());
        year = StrUtil.isNotEmpty(year) ? year : currentYear;
        month = StrUtil.isNotEmpty(month) ? month : currentMonth;
        //1、查询表头所有用户，即可知道表头总列数
        QueryWrapper<UserDO> queryWrapper =  new QueryWrapper<UserDO>();
        queryWrapper.isNotNull("real_name");
        final List<UserDO> userDOS = userMapper.selectList(queryWrapper);

        //2、对应上面sql,查询用户评分数据
        final List<UserIdAndScoreDTO> userIdAndScoreDTOS = userMapper.selectUserScores(year, month);

        
        //3、被评分人分数按照评分人userDOS顺序设置
        for(UserIdAndScoreDTO userIdAndScoreDTO : userIdAndScoreDTOS){
            List<Double> scoreList = new ArrayList<>();
            final String[] split = userIdAndScoreDTO.getScorerId().split(",");
            final String[] split1 = userIdAndScoreDTO.getScoreStr().split(",");
            for(UserDO userDO : userDOS){
                //判断某个字符串在数组中如果存在，找到对应下标，如果不存在则为-1
                int index = findStringIndex(split, userDO.getId());
                //4、评分人是固定的，但是所有评分人不一定都给(用户)被评分人评分，如果评分人还未评分，分数则为0
                String a = (index!= -1) ? split1[index] : "0";
                scoreList.add(Double.valueOf(a));
            }
            //5、scoreList 中的数据即是按照表头评分人评的分数有序排列(如果评分人还未评分，分数则为0)
            userIdAndScoreDTO.setScoreList(scoreList);
        }

        //6、excel  title
        final List<DynamicTablePo> tablePoList = getTalePoList(userDOS);
        //7、excel data
        final List<Map<String, Object>> excelScoreVOS = dataList(userIdAndScoreDTOS, userDOS);


        List<ExcelExportEntity> beanList = new ArrayList<ExcelExportEntity>(tablePoList.size());
        for (DynamicTablePo tablePo : tablePoList){
            ExcelExportEntity entity = new ExcelExportEntity();
            entity.setName(tablePo.getFiledShowName());
            entity.setKey(tablePo.getFiledCode());
            entity.setOrderNum(tablePo.getOrderNum());
            beanList.add(entity);
        }
            String excelName = "营销部"+ month +"月份各专责互评的得分表";
            String secondTitle = "规则:从日常工作协同等工作进行考评，从1-5分为9个分值进行打分，" +
                "如:1-1.5-2-2.5-3-3.5-4-4.5-5，每个人总分除以9作为最终得分。" +
                "注意:分值出现一样作为废票，该人员打分全部人员均按照委分统计。";
            ExportParams exportParams = new ExportParams(excelName,secondTitle, "sheet1");
            //8、规则设置高度
            exportParams.setSecondTitleHeight((short) 10);
            //9、表头设置样式
            exportParams.setStyle(ExcelExportTitleStyle.class);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, beanList , excelScoreVOS);
            response.setHeader("content-Type","application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(excelName + "导出表", "UTF-8") + ".xls");
            response.setCharacterEncoding("UTF-8");
            workbook.write(response.getOutputStream());
            workbook.close();
    }

    /**
     * 组装数据
     * @return
     */
    private List<Map<String,Object>> dataList(List<UserIdAndScoreDTO> userIdAndScoreDTOS,List<UserDO> userDOS){
        List<Map<String,Object>> exportList = new ArrayList<>();
        int j = 1;
        for(UserIdAndScoreDTO userIdAndScoreDTO : userIdAndScoreDTOS){
            Map<String,Object> map = new HashMap<>();
            map.put("num",j);
            map.put("realName",userIdAndScoreDTO.getRealName());
            map.put("avgScore",userIdAndScoreDTO.getAvgScore());
            final List<Double> scoreList = userIdAndScoreDTO.getScoreList();
           int i =0;
            for (UserDO userDO : userDOS) {
                map.put(userDO.getId(),scoreList.get(i));
                i++;
            }
            exportList.add(map);
            j++;
        }
        return exportList;
    }

    /**
     * 设置动态表头
     * @return
     */
    private List<DynamicTablePo> getTalePoList(List<UserDO> userDOS) {
        int i = 3;
        List<DynamicTablePo> resultList = new ArrayList<>();
        DynamicTablePo tablePo = new DynamicTablePo();
        tablePo.setFiledShowName("序号");
        tablePo.setFiledCode("num");
        tablePo.setOrderNum(1);
        tablePo.setDataType(0);
        DynamicTablePo tablePo1 = new DynamicTablePo();
        tablePo1.setFiledShowName("姓名");
        tablePo1.setFiledCode("realName");
        tablePo1.setOrderNum(2);
        tablePo1.setDataType(0);
        resultList.add(tablePo);
        resultList.add(tablePo1);
        for (UserDO userDO : userDOS) {
            DynamicTablePo tablePo2 = new DynamicTablePo();
            tablePo2.setFiledShowName(userDO.getRealName());
            tablePo2.setFiledCode(userDO.getId());
            tablePo2.setOrderNum(i);
            tablePo2.setDataType(0);
            resultList.add(tablePo2);
            i++;
        }
        DynamicTablePo tablePo3 = new DynamicTablePo();
        tablePo3.setFiledShowName("平均分");
        tablePo3.setFiledCode("avgScore");
        tablePo3.setOrderNum(i);
        tablePo3.setDataType(0);
        resultList.add(tablePo3);
        return resultList;
    }


    /**
     * 判断某个字符串在数组中如果存在，找到对应下标，如果不存在则为-1
     * @param array
     * @param target
     * @return
     */
    public static int findStringIndex(String[] array, String target) {
        for (int i = 0; i < array.length; i++) {
            if (array[i].equals(target)) {
                return i;
            }
        }
        return -1;
    }

3.3 实体 filedCode(表头key值和查询出来的数据的实体/map相对应，否则无法填充数据)

package com.langzhifangling.wx.scoringapplet.model.VO;

import lombok.Data;

import java.io.Serializable;

@Data
public class DynamicTablePo implements Serializable {
 
    private static final long serialVersionUID = 8991244829305414889L;
 
    //表头名字
    private String filedShowName;
 
    //表头key值和查询出来的数据的实体相对应
    private String filedCode;
 
    //表头顺序
    private Integer orderNum;
 
    //字段数据类型
    private Integer dataType;
}

3.4 表头样式

package com.langzhifangling.wx.scoringapplet.config;

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

/**
 * 导出自定义title的工具类
 *
 * @author changjiang.liu
 * @date 2022/5/23 16:44
 */
public class ExcelExportTitleStyle extends AbstractExcelExportStyler
        implements IExcelExportStyler {
    public ExcelExportTitleStyle(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        // 自定义字体
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE1.getIndex());
        font.setBold(true);
        font.setFontName("宋体");
        titleStyle.setFont(font);

		// 自定义背景色
        titleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

}

4、结果