添加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>
实体类
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下载工具类
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);
}
}
}
执行方法
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());
}
}
}
