easyExcel导出表格(有标题、单元格合并)
1、前言
最近工作上遇到了xlsx格式报表的导出,导出的数据存在父子关系,即相当于树形数据,有单元格合并和标题形式的要求,查阅了一些资料,总算是弄出来了,这里另写一个小样简单分享一下关于easyExcel导出具有合并单元格和标题的小结 代码,也算记录一下自己的工作学习。
2、代码
(目录及代码仅为实现,勿纠结格式和规范问题/捂脸)
easyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
controller层
package easyexceldemo.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import easyexceldemo.dto.BizMergeStrategy;
import easyexceldemo.dto.RowRangeDto;
import easyexceldemo.dto.TitleSheetWriteHandler;
import easyexceldemo.entity.User;
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.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* <p>
* 该类的描述
* </p>
*
* @author
* @Modified By:
* @since 2020/11/23 11:31
*/
@RestController
@RequestMapping("/user")
public class EasyExcelController {
@GetMapping("/excel")
public void excel(HttpServletResponse response) throws IOException {
Map<String, List<RowRangeDto>> strategyMap = BizMergeStrategy.addAnnualMerStrategy(data());
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String filename = URLEncoder.encode("用户表测试", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xlsx");
EasyExcel.write(response.getOutputStream(), User.class)
.excelType(ExcelTypeEnum.XLSX).head(User.class)
.registerWriteHandler(new TitleSheetWriteHandler("我是一个小标题",2)) // 标题及样式,lastCol为标题第0列到底lastCol列的宽度
//设置默认样式及写入头信息开始的行数
.relativeHeadRowIndex(1)
.registerWriteHandler(new BizMergeStrategy(strategyMap)) // 注册合并策略
.registerWriteHandler(BizMergeStrategy.CellStyleStrategy()) // 设置样式
.sheet(