JAVA实现Easypoi模版导出

下面列举下EasyPoi支持的指令以及作用，最主要的就是各种fe的用法

• !fe: 遍历数据不创建row
• $fe: 下移插入,把当前行,下面的行全部下移.size()行,然后插入
• #fe: 横向遍历
• v_fe: 横向遍历值
• !if: 删除当前列 {{!if:(test)}}
• 单引号表示常量值 ‘’ 比如’1’ 那么输出的就是 1
• &NULL& 空格
• &INDEX& 表示循环中的序号,自动添加
• ]] 换行符 多行遍历导出
• sum： 统计数据
• cal: 基础的±X% 计算
• dict: 字典
• i18n: 国际化

在两个大括号里写对应的数据名称，单个元素数据（默认t，不需要写），fe用来遍历数据，fe的写法 fe标志 : list数据 {{$fe:maplist t.id }}

• pom依赖

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

实体类

package com.example.demo.vo;
import lombok.Data;
import java.math.BigDecimal;

@Data
public class GoodsImportVo{

    private String goodsName;

    private BigDecimal price;

    private Integer num;
    
    private Integer type;

    public GoodsImportVo(String goodsName, BigDecimal price,  Integer num) {
        this.goodsName = goodsName;
        this.price = price;
        this.num = num;
    }
}

• 模板配置
  

• 实现方法

package com.example.mybatismysql8demo.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;

import com.example.mybatismysql8demo.vo.GoodsImportVo;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;

@RestController
public class EasypoiController {

    /**
     * 本地下载
     */
    public void exportLocal(){
        //模板路径（服务器路径："/home/www/backend/excel/xxx.xlsx"）
        TemplateExportParams templatePath = new TemplateExportParams("E:\\模板\\商品.xls");
        Map<String, Object> map = new HashMap<>(3);
        map.put("goodsName", "商品信息");
        //list数据
        List<GoodsImportVo> goodsImportVos = new ArrayList<>();
        goodsImportVos.add(new GoodsImportVo("苹果",new BigDecimal(10),11));
        goodsImportVos.add(new GoodsImportVo("香蕉",new BigDecimal(10),11));
        map.put("goodsList", goodsImportVos);
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(templatePath, map);
            //本地下载
            FileOutputStream outStream = new FileOutputStream("E:\\模板\\good.xls");
            workbook.write(outStream);
            //关闭流
            outStream.flush();
            outStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

	/**
     * 浏览器下载
     */
    @PostMapping("/excelTemplate")
    public void makeExcelTemplate(HttpServletResponse response) throws Exception {
        //模板路径（服务器路径："/home/www/backend/excel/follow_truck.xlsx"）
        TemplateExportParams templatePath = new TemplateExportParams("E:\\模板\\商品.xls");
        Map<String, Object> map = new HashMap<>(3);
        map.put("goodsName", "商品信息");
        //list数据
        List<GoodsImportVo> goodsImportVos = new ArrayList<>();
        goodsImportVos.add(new GoodsImportVo("苹果",new BigDecimal(10),11));
        goodsImportVos.add(new GoodsImportVo("香蕉",new BigDecimal(8),12));
        map.put("goodsList", goodsImportVos);
        Workbook workbook = ExcelExportUtil.exportExcel(templatePath, map);
        try {
            //.xls格式
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            //前端存在跨域不成功，设置可访问
            response.setHeader("Access-Control-Allow-Origin","*");
            //设置不要缓存
            response.setHeader("Pragma", "No-cache");
            // 这里URLEncoder.encode可以防止中文乱码
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("goodsList", "UTF-8") + ".xls");
            //输出流
            OutputStream outStream = response.getOutputStream();
            //浏览器下载
            workbook.write(outStream);
            //关闭流
            outStream.flush();
            outStream.close();
        } catch (IOException e) {
            throw new IllegalArgumentException(e.getMessage());
        }
    }
}  

结果打印

workbook 对列数据进行处理

     /**
     * 浏览器下载
     */
    @PostMapping("/excelTemplate")
    public void makeExcelTemplate(HttpServletResponse response) {
        //模板路径（服务器路径："/home/www/backend/excel/follow_truck.xlsx"）
        TemplateExportParams templatePath = new TemplateExportParams("E:\\模板\\商品.xls");
        Map<String, Object> map = new HashMap<>(3);
        map.put("goodsName", "商品信息");
        //list数据
        List<GoodsImportVo> goodsImportVos = new ArrayList<>();
        goodsImportVos.add(new GoodsImportVo("苹果",new BigDecimal(10),11));
        goodsImportVos.add(new GoodsImportVo("香蕉",new BigDecimal(8),12));
        map.put("goodsList", goodsImportVos);
        Workbook workbook = ExcelExportUtil.exportExcel(templatePath, map);
        //对某列值进行处理
        workbook.setForceFormulaRecalculation(true);
        Sheet sheet = workbook.getSheetAt(0);
        for (int num = 0;num < goodsImportVos.size();num++){
            //存在标题2行，从第三行开始
            for (Cell c : sheet.getRow(Integer.sum(num,2))){
                //需要替换的列(从0开始)
                if (Objects.equals(1, c.getColumnIndex())){
                    if (Integer.parseInt(c.getStringCellValue()) >= 10){
                        c.setCellValue(c.getStringCellValue() + "（特价）");
                    }
                }
            }
        }
        try {
            //.xls格式
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            //前端存在跨域不成功，设置可访问
            response.setHeader("Access-Control-Allow-Origin","*");
            //设置不要缓存
            response.setHeader("Pragma", "No-cache");
            // 这里URLEncoder.encode可以防止中文乱码
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("goodsList", "UTF-8") + ".xls");
            //输出流
            OutputStream outStream = response.getOutputStream();
            //浏览器下载
            workbook.write(outStream);
            //关闭流
            outStream.flush();
            outStream.close();
        } catch (IOException e) {
            throw new IllegalArgumentException(e.getMessage());
        }
    }

结果打印