Java easypoi 模板方式导出excel 并合并相关列

在项目开发中经常会使用到合并列，格式如下：

1.引入easypoi

cn.afterturn easypoi-annotation cn.afterturn easypoi-base

2.定义一个实体类PersonDto

import cn.afterturn.easypoi.excel.annotation.Excel;import lombok.Data;import java.io.Serializable;@Datapublic class PersonDto implements Serializable { private static final long serialVersionUID = 1L; @Excel(name = "省份", mergeVertical = true, width = 50) private String province; @Excel(name = "城市", mergeVertical = true, width = 50) private String city; @Excel(name = "民族", mergeVertical = true, width = 50) private String national; @Excel(name = "姓名", width = 20) private String userName; @Excel(name = "年龄", needMerge = true) private int age; @Excel(name = "地址", width = 50) private String address;}

 3.定义一个Excel模板test.xlsx（名称随意），模板格式如下：

$fe:list

 4.定义控制器ExportController，在控制器中需要注意的是map.put("名称")必须与上图模板中开头的fe:list名称一样

 控制器完整代码：

import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.TemplateExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import cn.afterturn.easypoi.util.PoiMergeCellUtil;import com.boot.basic.annotation.security.PreAuth;import com.boot.web.vo.PersonDto;import io.swagger.annotations.Api;import io.swagger.annotations.ApiOperation;import lombok.RequiredArgsConstructor;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Workbook;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse;import java.io.*;import java.net.URLEncoder;import java.util.*;@RestController@RequestMapping("/excel")public class ExcelController { @Autowired protected HttpServletResponse response; /** * 数据导出excel */ @ApiOperation(value = "Get方式导出Excel") @RequestMapping(value = "/export", method = RequestMethod.GET, produces = "application/octet-stream") protected void exportExcel() { try { Map map = new HashMap(); List list = new ArrayList(); for (int i = 0; i < 10; i++) { PersonDto entity = new PersonDto(); entity.setProvince("广东"); entity.setCity("广州"); entity.setNational("汉族"); entity.setUserName("张三_" + i); if (i > 4) { entity.setUserName("李四"); } entity.setAge(16 + i); entity.setAddress("广东省广州市_" + i); list.add(entity); } for (int i = 0; i < 10; i++) { PersonDto entity = new PersonDto(); entity.setProvince("广西"); entity.setCity("南宁"); entity.setNational("壮族"); entity.setUserName("王五_" + i); entity.setAddress("广西南宁_" + i); if (i > 2) { entity.setNational("汉族"); } if (i > 4) { entity.setCity("桂林"); entity.setUserName("王五"); entity.setAddress("广西桂林_" + i); } entity.setAge(21 + i); list.add(entity); } map.put("list", list); TemplateExportParams params = new TemplateExportParams("E:/test/test.xlsx"); map.put("type", ExcelType.HSSF); // 导出excel Workbook workbook = ExcelExportUtil.exportExcel(params, map); if (workbook == null) { throw new Exception("文件导出失败，workbook为空"); } PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), 1, 0, 1, 2); // 重置响应对象 response.reset(); // 指定下载的文件名--设置响应头 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("人员信息表" + System.currentTimeMillis(), "UTF-8") + ".xlsx"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Pragma", "no-cache"); response.setHeader("Cache-Control", "no-cache"); response.setDateHeader("Expires", 0); workbook.write(response.getOutputStream()); } catch (Exception e) { e.printStackTrace(); log.error("数据导出失败：" + e.getMessage()); } }}