导入依赖
<!-- 阿里开源的excel操作依赖 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
工具类编写
package com.ajie.utils;
import com.ajie.constant.MessageConstant;
import com.ajie.result.Result;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.yaml.snakeyaml.util.UriEncoder;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 对excel操作的工具类
* 可以根据自己用的类去生成
* @Author 阿杰
* @create 2021-03-31 15:50
*/
public class EasyExcelUtil {
/**
* 读取excel
* @param file 导入的文件流
* @param model 生成的类
* @param <T>
* @return 对象数组
*/
public static<T> List<T> readExcel(InputStream file, Class<T> model) {
List<T> list = new ArrayList<>();
EasyExcel
//读取的文件
.read(file)
//反射获取类型
.head(model)
//excel类型
.excelType(ExcelTypeEnum.XLSX)
//读取的excel左下角的名字
.sheet(0)
//注册监听器
.registerReadListener(new AnalysisEventListener<T>() {
@Override
public void invoke(T t, AnalysisContext analysisContext) {
list.add(t);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("读取完毕" + model);
}
}).doRead();
return list;
}
/**
* 响应给浏览器的excel文件
* @param response servlet响应对象
* @param fileName 设置文件明
* @param list 数据列表
* @param clazz 响应类
* @param <T>
* @throws IOException
*/
public static<T> void writerExcel(HttpServletResponse response, String fileName, List<T> list, Class<T> clazz) throws IOException {
try {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("UTF-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
String encode = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encode + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz)
//设置不自动关闭流
.autoCloseStream(Boolean.FALSE)
.sheet(fileName)
.doWrite(list);
} catch (Exception e) {
//重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(new Result(false, MessageConstant.RESPONSE_OUT_FAIL, map));
}
}
}
easyExcel开源地址附上
https://www.yuque.com/easyexcel/doc/write