添加pom依赖

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version>
</dependency>
<dependency><groupId>org.apache.ant</groupId><artifactId>ant</artifactId><version>1.10.5</version>
</dependency>
<dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.21</version>
</dependency>
<!--工具类-->
<dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.23</version>
</dependency>

实体类

java">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 goodsCategory;private String goodsName;private Integer num;private BigDecimal price;public TemplateGoodsExcel(String goodsCategory,String goodsName, BigDecimal price, Integer num) {this.goodsName = goodsName;this.price = price;this.num = num;this.goodsCategory = goodsCategory;}}

导出模版

zip下载工具类

java">package com.example.mybatismysql8demo.utils;import cn.hutool.core.io.FileUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.tools.zip.ZipEntry;
import org.apache.tools.zip.ZipOutputStream;
import org.springframework.stereotype.Component;import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;@Component
@Slf4j
public class ZipUtil {/*** 字节数组长度*/private static final int BYTE_ARRAY_LENGTH = 1024;/*** 创建压缩文件* @param srcFile 源文件列表* @param zipFile 压缩文件*/public static void zipFiles(File[] srcFile, File zipFile) {byte[] buf = new byte[BYTE_ARRAY_LENGTH];FileInputStream in = null;try (ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipFile))) {for (File file : srcFile) {try {in = new FileInputStream(file);out.putNextEntry(new ZipEntry(file.getName()));// 支持中文  解决文件名乱码问题out.setEncoding("GBK");int len;while ((len = in.read(buf)) > 0) {out.write(buf, 0, len);}} catch (Exception e) {log.error(String.format("压缩文件出错 01：{}%s", e));} finally {out.closeEntry();assert in != null;in.close();//删除源文件FileUtil.del(file);}}} catch (IOException e) {log.error(String.format("压缩文件出错 02：{}%s", e));}finally {if(in!=null){try {in.close();} catch (IOException e) {log.error(String.format("压缩文件出错 02：{}%s", e));}}}}/*** 从浏览器下载压缩文件* @param file 文件对象* @param response 响应对象* @param isDelete 是否删除源文件* @throws IOException io异常*/public static void downloadZipFile(File file, HttpServletResponse response, boolean isDelete) throws IOException{OutputStream toClient = null;try(InputStream inputStream =new FileInputStream(file)) {response.reset();toClient = new BufferedOutputStream(response.getOutputStream());response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(file.getName(), StandardCharsets.UTF_8));int len;byte[] buffer = new byte[1024];while((len=inputStream.read(buffer))>0){toClient.write(buffer,0,len);}toClient.flush();} catch (IOException ex) {log.error(ex.getMessage());} finally{if(toClient!= null){toClient.close();}}//在所有流都关闭后再删除文件if(isDelete && file.exists()) {FileUtil.del(file);}}
}

执行方法

java">package com.example.mybatismysql8demo.controller;import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.mybatismysql8demo.excel.TemplateGoodsExcel;
import com.example.mybatismysql8demo.utils.ZipUtil;
import lombok.extern.slf4j.Slf4j;
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.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;@Slf4j
@RestController
public class EasyExcelController {/*** 浏览器下载* @param response*/@RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)private void browserDownload(HttpServletResponse response){//数据List<TemplateGoodsExcel> data = new ArrayList<>();data.add(new TemplateGoodsExcel("零食","辣条",new BigDecimal(10),100));data.add(new TemplateGoodsExcel("水果","葡萄",new BigDecimal(16),50));data.add(new TemplateGoodsExcel("水果","苹果",new BigDecimal(20),150));//按照分类分组Map<String, List<TemplateGoodsExcel>> collect = data.stream().collect(Collectors.groupingBy(TemplateGoodsExcel::getGoodsCategory));//模版路径String templatePath = "E:\\模板\\商品.xls";//文件下载路径String path = "E:\\下载\\";//文件集合File[] fileArray = new File[collect.size()];//小标int index = 0;for (Map.Entry<String, List<TemplateGoodsExcel>> value : collect.entrySet()) {//判断文件夹是否存在File filePath = new File(path);if (!filePath.exists()) {filePath.mkdir();}//导出文件名String fileName = path +  value.getKey() + "_" + System.currentTimeMillis() + ".xls";//导出ExcelWriter excelWriter = EasyExcelFactory.write(fileName).excelType(ExcelTypeEnum.XLS).withTemplate(templatePath).build();WriteSheet writeSheet = EasyExcelFactory.writerSheet().build();//表格数据excelWriter.fill(value.getValue(), writeSheet);Map<String,Object> map = new HashMap<>(1);map.put("goodsName", value.getKey());excelWriter.fill(map, writeSheet);excelWriter.finish();fileArray[index] = new File(fileName);index++;}//打包文件名String zipPath = path + System.currentTimeMillis() + "_" + "物品信息.zip";//文件打包ZipUtil.zipFiles(fileArray, new File(zipPath));//浏览器下载File file = new File(zipPath);try {ZipUtil.downloadZipFile(file, response, true);} catch (IOException e) {System.out.println(e.getMessage());}}
}