EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel
1.导入Maven
<!--EasyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.16</version>
</dependency>
2.封装类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: 海绵宝宝
* @Explain: easyExcel导入导出工具类
* @DateTime: 2022/6/2 12:58
* @Params:
* @Return
*/
@Slf4j
public class ExcelUtil {
public ExcelUtil() {
}
/**
* 导出execl
*
* @param response 响应体
* @param list 数据
* @param clazz 类
* @param <T>
*/
public static <T> void downExcelUtil(HttpServletResponse response, List<T> list, Class<T> clazz) {
String fileName = System.currentTimeMillis() + "";
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
try {
int i = 0;
if (!list.isEmpty()) {
EasyExcel.write(response.getOutputStream(), clazz)
.registerWriteHandler(createTableStyle())
.sheet("data" + (i++))
.doWrite(list);
}
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 文件上传
* <p>
* 1. 创建excel对应的实体对象
* <p>
* 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,
* <p>
* 3. 直接读即可
*/
public static <T> List<T> uploadExcel(MultipartFile file) throws IOException {
final List<T> rows = new ArrayList<T>();
ReadListener readListener = new AnalysisEventListener<T>() {
@Override
public void invoke(T data, AnalysisContext context) {
rows.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("read {} rows", rows.size());
}
};
EasyExcel.read(file.getInputStream(), null, readListener).sheet().doRead();
return rows;
}
public static <T> List<T> uploadExcel(MultipartFile file, Class<T> tClass) throws IOException {
final List<T> rowss = new ArrayList<T>();
ReadListener readListener = new AnalysisEventListener<T>() {
@Override
public void invoke(T data, AnalysisContext context) {
rowss.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("read {} rows", rowss.size());
}
};
EasyExcel.read(file.getInputStream(), tClass, readListener).sheet().doRead();
return rowss;
}
private static WriteHandler createTableStyle() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 设置字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
headWriteFont.setBold(false);
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short) 10);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}
3.实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @Author: 海绵宝宝
* @Explain: 菜单对应表
* @DateTime: 2022/6/1 9:53
* @Params:
* @Return
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Menus {
@ExcelProperty(value = "菜单ID", index = 0,converter = TimestampConverter.class)
private Integer id;
@ExcelProperty(value = "菜单标题", index = 1,converter = TimestampConverter.class)
private String menuTitle;
@ExcelProperty(value = "菜单名", index = 2,converter = TimestampConverter.class)
private String menuName;
}
4.调用示例
// 导出模版
@GetMapping("/template")
public void template(HttpServletResponse response) {
Menus menus = new Menus();
ArrayList<Menus> list = new ArrayList<>();
for (int i=0;i<=3;i++){
menus.setId(123);
menus.setMenuTitle("123");
menus.setMenuName("123");
list.add(menus);
}
ExcelUtil.downExcelUtil(response, list, Menus.class);
}
// 上传文件
@PostMapping("/upload")
public void test02(@RequestParam("file") MultipartFile file) {
try {
List<Object> objects = ExcelUtil.uploadExcel(file);
log.info("studnets:{}", objects);
} catch (IOException e) {
e.printStackTrace();
}
}
5.日期(Timestamp)转换器
package com.iet.ustb.project.system.util;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
/**
* @Author: 海绵宝宝
* @Explain: Timestamp时间转换器(EasyExcel)
* @DateTime: 2022/7/12 16:04
* @Params:
* @Return
*/
public class TimestampConverter implements Converter<Timestamp> {
@Override
public Class<Timestamp> supportJavaTypeKey() {
return Timestamp.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public WriteCellData<String> convertToExcelData(Timestamp timestamp, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
return new WriteCellData<String>(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(timestamp));
}
}