easyexcel 复制表头，合并表头，自定义表头名称、表头数量

效果

接口返给前端下载路径

 

@SneakyThrows
@GetMapping("/exportDetailed/{voteId}")
public AjaxResult exportVote(@PathVariable Integer voteId)  {
    // 准备导出数据

    String s = generateExcel(voteId);


    return AjaxResult.success(s);
}

---

方法

public String   generateExcel(Integer voteId){

    // 判断要下载的数据是否存在
    AppVote appVote = appVoteService.selectAppVoteById(voteId);
    if (appVote==null){
        throw new ServiceException("投票不存在");
    }
    // 自定义的标题 ，如同意，中立，拒绝
    List<AppVoteOption> appVoteOptions = appVoteOptionMapper.selectByVoteId(voteId);
    // 内容数据
    List<AppVoteDetailedVO> appVoteDetailedVO =  appVoteRecordMapper.selectVoteid(voteId);
    //标头
    String title = appVote.getTitle();
    // 选项标题
    String content = appVote.getContent();
    //表格头
    ArrayList<List<String>> head = new ArrayList<>();
    List<String> head1 = new ArrayList<>();
    head1.add(title);
    head1.add("选项标题:");
    head1.add("序号");
    head.add(head1);

    List<String> head11= new ArrayList<>();
    head11.add(title);
    head11.add(content);
    head11.add("姓名");
    head.add(head11);
    List<String> head2 = new ArrayList<>();
    head2.add(title);
    head2.add(content);
    head2.add("房屋信息");
    head.add(head2);
    String bodySrt = " ";
    // 如同意，中立，拒绝
    for (AppVoteOption appVoteOption : appVoteOptions) {
        List<String> headfor = new ArrayList<>();
        headfor.add(title);
        bodySrt = bodySrt+" ";
        headfor.add(bodySrt);
        headfor.add(appVoteOption.getOptionContent());
        head.add(headfor);
    }

    List<String> head5 = new ArrayList<>();
    head5.add(title);
    head5.add(" ");
    head5.add("投票时间");
    head.add(head5);
    //内容
    List<List<String>> bodyList = new ArrayList<>();
    // 序号
    int num = 0;
    for (AppVoteDetailedVO detailedVO : appVoteDetailedVO) {
        List<List<String>>   bodyList2 = new ArrayList<>();
        List<String> body = new ArrayList<>();
        num = num+1;
        body.add(num+"");
        body.add(detailedVO.getUserName());
        body.add(detailedVO.getRoomIdentNo());
        // 表头同意，中立，拒绝 的内容
        for (AppVoteOption appVoteOption : appVoteOptions) {
            // 一样就同意，否者就空
            if (detailedVO.getOptionContent().equals(appVoteOption.getOptionContent())){
                body.add("1");
            }else {
                body.add(" ");
            }

        }
        body.add(detailedVO.getCreateTime());
        bodyList2.add(body);
        bodyList.addAll(bodyList2);
    }

    // 合计统计
    if (appVoteDetailedVO.size()>0){
        List<List<String>>   bodyList2 = new ArrayList<>();
        List<String> body = new ArrayList<>();
        body.add("合计");
        body.add(" ");
        body.add(" ");
        Integer statisticsNum = 0;
        // 数量累加
            for (AppVoteOption appVoteOption : appVoteOptions) {
                for (AppVoteDetailedVO detailedVO : appVoteDetailedVO) {
                if (detailedVO.getOptionContent().equals(appVoteOption.getOptionContent())){
                    statisticsNum =  statisticsNum +1;
                }
            }
            body.add(statisticsNum+"");
            statisticsNum = 0;
        }
        body.add(" ");
        bodyList2.add(body);
        bodyList.addAll(bodyList2);
    }




    // 访问前缀
    String file = HaoNiuConfig.getProfile();
    // 访问地址（返给前端的地址)
    String fileName  = "/" + DateUtils.datePath() + "/" + DateUtils.dateTimeNow() + "_" + title+".xlsx";
    // 全路径
    String fileUrl = file + fileName;
    // 查询文件，不存在就下载
    File desc = new File(fileUrl);
    if (!desc.getParentFile().exists()) {
        desc.getParentFile().mkdirs();
    }
    if (!desc.exists()) {
        try {
            desc.createNewFile();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //创建EXCEL对象
    ExcelWriterBuilder builder = EasyExcel.write(fileUrl);
    //设置处理器，合并单元格，列宽处理器等
    builder.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
    //获取writer对象
    ExcelWriter writer= builder.build();
    WriteSheet sheet = EasyExcel.writerSheet(0, title).build();
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    //
    headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    // 字体策略
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short) 14);
    headWriteCellStyle.setWriteFont(contentWriteFont);
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    contentWriteCellStyle.setWrapped(true);
    contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
    contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
    contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
    contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    WriteSheet writeSheet = EasyExcel.writerSheet(1, "")
            .registerWriteHandler(horizontalCellStyleStrategy)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            .head(head)
            .build();

    //写入数据，
    writer.write(bodyList,writeSheet);
    writer.finish();

    // 前端访问地址
    return "profile"+fileName;
}

 

 表头sql

SELECT
    option_content
FROM
    `app_vote_option`
WHERE vote_id = 63
  AND del_flag = 0 

 

内容

SELECT
    (SELECT
         owner_name
     FROM
         `app_user_community`
     WHERE community_id = a.`community_id`
       AND user_id = a.`user_id`
       AND del_flag = 0) AS userName,
    (SELECT
         room_ident_no
     FROM
         `app_user_community`
     WHERE community_id = a.`community_id`
       AND user_id = a.`user_id`
       AND del_flag = 0) AS roomIdentNo,
    b.option_content AS optionContent,
    a.create_time AS createTime
FROM
    `app_vote_record` AS a
        LEFT JOIN `app_vote_option` AS b
                  ON a.`vote_option_id` = b.`vote_option_id`
                      AND b.`del_flag` = 0
WHERE a.vote_id = #{voteId}
  AND a.del_flag = 0
ORDER BY a.create_time

 

<el-button
  size="mini"
  type="text"
  icon="el-icon-download"
  @click="handleExportDetailed(scope.row)"
>导出</el-button>

前端下载 

 

/** 导出按钮操作 */
handleExportDetailed(row) {
  exportDetailed(row.voteId).then(response => {
    var fileUrl  = process.env.VUE_APP_BASE_API+ response.msg;
    // 创建一个隐藏的<a>元素
    var element = document.createElement('a');
    element.setAttribute('href', fileUrl);
    var lastSlashIndex = fileUrl.lastIndexOf('/');
    var filename = ""
    if (lastSlashIndex !== -1) {
      filename  =   fileUrl.substring(lastSlashIndex + 1);
    }
    element.setAttribute('download', filename);
    // 模拟点击下载
    element.style.display = 'none';
    document.body.appendChild(element);
    element.click();
    document.body.removeChild(element);

  });

},