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

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

1.引入easypoi

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
        </dependency>

2.定义一个实体类PersonDto

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

import java.io.Serializable;

@Data
public 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<String, Object> map = new HashMap<String, Object>();
            List<PersonDto> 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());
        }
    }
}