EasyPoi通过模板生成Excel文件

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

之前用easypoi实现过导出简单的excel文件，最近又有需求导出固定格式的excel文件，所以在网上搜索学习了一下，现将学会后写的demo记录一下方便以后回顾。

首先照例引入maven依赖（这里版本要注意一下，之前引入4.3.0导出图片会失败，后面在网上看到有人遇到同样的问题，换成4.2.0后就可以正常导出图片了）：

<!-- easypoi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.2.0</version>
        </dependency>

然后在resources路径下创建一个用来存放模板的文件夹：template

接着在配置文件application.yml里写上该模板文件的路径，方便代码引用：

template:
  city-rail-report: template/city_rail_report_template.xlsx

然后创建一个导出excel的工具类：

package com.wl.standard.utils;
 
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
 
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Map;
 
/**
 * @author wl
 * @date 2022/1/7
 */
@Slf4j
public class ExcelUtils {
    /**
     * 通过模板生成Excel文件
     * @param templateFileName 模板文件
     * @param fileName 目标文件
     * @param map 数据
     */
    public static void buildExcelByTemplate(String templateFileName, String fileName, Map map) {
        OutputStream outStream = null;
        try {
            outStream = new FileOutputStream(fileName);
            TemplateExportParams param = new TemplateExportParams(templateFileName, 0);
            Workbook workbook = ExcelExportUtil.exportExcel(param, map);
            workbook.write(outStream);
        } catch (IOException e) {
            log.error("根据模板生成Excel文件失败, 失败原因: {}", e);
        } finally {
            try {
                if (outStream != null) {
                    outStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

Controller层：

package com.wl.standard.controller;
 
import com.wl.standard.common.result.HttpResult;
import com.wl.standard.common.result.HttpResultWithPageInfo;
import com.wl.standard.entity.City;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import com.wl.standard.service.CityRailService;
 
/**
 * @author wl
 * @date 2021/7/14
 */
@Api(tags = "城市轨道交通信息")
@RestController
@RequestMapping("/rail")
public class CityRailController {
	private final CityRailService cityRailService;
 
	@Autowired
	public CityRailController(CityRailService cityRailService) {
		this.cityRailService = cityRailService;
	}
 
 
	@ApiOperation("生成Excel")
	@GetMapping("/build/report")
	public HttpResult buildReport() {
		cityRailService.buildReport();
		return HttpResult.success();
	}
}

Service实现层（本例引用了Mybatis-plus，仅做参考）：

package com.wl.standard.service.impl;
 
import cn.afterturn.easypoi.entity.ImageEntity;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.wl.standard.common.PageInfoWrapper;
import com.wl.standard.entity.City;
import com.wl.standard.entity.CityRail;
import com.wl.standard.entity.vo.CityRailVO;
import com.wl.standard.utils.CommonUtils;
import com.wl.standard.utils.ExcelUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.stereotype.Service;
import com.wl.standard.mapper.CityRailMapper;
import com.wl.standard.service.CityRailService;
import lombok.extern.slf4j.Slf4j;
 
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;
 
/**
 * @author wl
 * @date 2021/7/15
 */
@Slf4j
@Service
@EnableAsync
public class CityRailServiceImpl extends ServiceImpl<CityRailMapper, CityRail> implements CityRailService {
	@Value("${template.city-rail-report}")
	private String templateFile;
 
	@Override
	@Async
	public void buildReport() {
		Map<String, Object> data = new HashMap<>();
		List<CityRailVO> cityRailList = baseMapper.getCityRail();
		List<JSONObject> cityRailArray = new ArrayList<>();
		//插入图片
		cityRailList.forEach(cityRailVO -> {
			JSONObject cityRailObject = CommonUtils.toJsonObject(cityRailVO);
			if (StringUtils.isNotEmpty(cityRailVO.getImg())) {
				File imgFile = new File(cityRailVO.getImg());
				if (imgFile.exists()) {
					ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
					try {
						BufferedImage bufferImg = ImageIO.read(imgFile);
						ImageIO.write(bufferImg, "png", byteArrayOut);
						ImageEntity imageEntity = new ImageEntity(byteArrayOut.toByteArray(), 1000, 1000);
						cityRailObject.put("img", imageEntity);
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}
			cityRailArray.add(cityRailObject);
		});
		data.put("cityRail", cityRailArray);
		data.put("mileage", statistics(cityRailList));
		String now = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss").format(new Date());
		String fileName = String.format("%s.xlsx", now);
		ExcelUtils.buildExcelByTemplate(templateFile, fileName, data);
	}
 
	/**
	 * 统计
	 * @param cityRailList
	 * @return
	 */
	private JSONObject statistics(List<CityRailVO> cityRailList) {
		JSONObject mileage = new JSONObject();
		Long top = cityRailList.stream().filter(cityRailVO -> cityRailVO.getMileage() > 500).count();
		Long middle = cityRailList.stream().filter(cityRailVO -> cityRailVO.getMileage() > 300 && cityRailVO.getMileage() < 500).count();
		Long generally = cityRailList.stream().filter(cityRailVO -> cityRailVO.getMileage() > 100 && cityRailVO.getMileage() < 300).count();
		mileage.put("top", top);
		mileage.put("middle", middle);
		mileage.put("generally", generally);
		return mileage;
	}
}

模板文件内容：

对于list，需要采用{{ $fe: list t.xxx}}的格式，其中list是你的list对象名，如我的模板里list的对象名为cityRail。而t.xxx，xxx则是属性名称。

对于一般的对象，即可采用第2行的格式。

另外本demo采用了异步的方式，不用等文件生成成功就可以返回响应了，具体可以去了解@Async和@EnableAsync注解。

启动项目，调用接口，生成文件：