JAVA实现easyExcel模版导出

easyExcel文档

模板注意：

• 用 {} 来表示你要用的变量 ，如果本来就有"{“,”}" ，特殊字符用"{“,”}"代替
• {} 代表普通变量
• {.}代表是list的变量

添加pom依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>

<!--工具类-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.1</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.21</version>
</dependency>

第一种：简单模版导出

实体类

package com.example.mybatismysql8demo.excel;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.math.BigDecimal;

@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@Data
public class TemplateGoodsExcel implements Serializable {

    private String goodsName;

    private Integer num;

    private BigDecimal price;

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

}

导出模版

执行方法

package com.example.mybatismysql8demo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.example.mybatismysql8demo.excel.TemplateGoodsExcel;
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.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

@RestController
public class EasyExcelController {

    /**
     * 本地下载
     * @param args
     */
    public static void main(String[] args) {
        //模版路径
        String path = "E:\\模板\\商品.xls";
        //文件下载路径
        String fileName = "E:\\" + System.currentTimeMillis() + ".xls";
        //数据
        List<TemplateGoodsExcel> data = new ArrayList<>();
        data.add(new TemplateGoodsExcel("苹果",new BigDecimal(10),100));
        data.add(new TemplateGoodsExcel("香蕉",new BigDecimal(8),200));
        ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(path).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        //列表数据
        excelWriter.fill(data, writeSheet);
        //基础数据
        Map<String, Object> map = new HashMap<>(2);
        map.put("goodsName", "商品信息");
        excelWriter.fill(map, writeSheet);
        excelWriter.finish();
    }


    /**
     * 浏览器下载
     * @param response
     */
    @RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)
    private void browserDownload(HttpServletResponse response){
        OutputStream out = null;
        try {
            out = getOutputStream("商品信息",response);
            ExcelWriter excelWriter =  EasyExcel.write(out)
                    .withTemplate("E:\\模板\\商品.xls")
                    .excelType(ExcelTypeEnum.XLS)
                    .build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //列表数据
            List<TemplateGoodsExcel> data = new ArrayList<>();
            data.add(new TemplateGoodsExcel("苹果",new BigDecimal(10),100));
            data.add(new TemplateGoodsExcel("香蕉",new BigDecimal(8),200));
            excelWriter.fill(data, writeSheet);
            //基础数据
            Map<String,Object> map = new HashMap<>(1);
            map.put("goodsName", "商品信息");
            excelWriter.fill(map, writeSheet);
            excelWriter.finish();
        } finally {
            try {
                if (out != null){
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                System.out.println(e.getMessage());
            }
        }
    }

    /**
     * 此段代码是工具类ExcelUtils中的 用于输出Excel流
     */
    private OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

}

第二种：复杂模版导出

导出模版

执行方法

package com.example.mybatismysql8demo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.example.mybatismysql8demo.excel.TemplateGoodsExcel;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
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.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;

@RestController
public class EasyExcelController {

    /**
     * 浏览器下载
     * @param response
     */
    @RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)
    private void browserDownload(HttpServletResponse response){
        OutputStream out = null;
        try {
            out = getOutputStream("商品信息",response);
            ExcelWriter excelWriter =  EasyExcel.write(out)
                    .withTemplate("E:\\模板\\商品.xls")
                    .excelType(ExcelTypeEnum.XLS)
                    .build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //这里注意入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            //forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            //简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            //列表数据
            List<TemplateGoodsExcel> data = new ArrayList<>();
            data.add(new TemplateGoodsExcel("辣条",new BigDecimal(10),100));
            data.add(new TemplateGoodsExcel("娃哈哈",new BigDecimal(8),200));
            List<TemplateGoodsExcel> data2 = new ArrayList<>();
            data2.add(new TemplateGoodsExcel("葡萄",new BigDecimal(16),50));
            data2.add(new TemplateGoodsExcel("榴莲",new BigDecimal(30),10));
            //如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1，然后多个list必须用 FillWrapper包裹
            excelWriter.fill(new FillWrapper("dataOne", data), fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("dataTwo", data2), fillConfig, writeSheet);
            //基础数据
            Map<String,Object> map = new HashMap<>(1);
            map.put("goodsName", "食品信息");
            map.put("name", "水果信息");
            map.put("date", "2019年10月9日13:28:28");
            excelWriter.fill(map, writeSheet);
            excelWriter.finish();
        } finally {
            try {
                if (out != null){
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                System.out.println(e.getMessage());
            }
        }
    }

    /**
     * 此段代码是工具类ExcelUtils中的 用于输出Excel流
     */
    private OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

}