SpringBoot中EasyExcel实现Excel⽂件的导⼊导出
前⾔
在我们⽇常的开发过程中经常会使⽤Excel⽂件的形式来批量地上传下载系统数据，我们最常⽤的⼯具是Apache poi，但是如果数据到底上百万时，将会造成内存溢出的问题，那么我们怎么去实现百万数据批量导⼊导出。
正⽂
Easyexcel
Easyexcel 是阿⾥巴巴的开源项⽬，⽤来优化Excel⽂件处理过程：
poi消耗内存严重：Java解析、⽣成Excel⽐较有名的框架有Apache poi、jxl。但他们都存在⼀个严重的问题就是⾮常的耗内存，poi有⼀套SAX模式的API可以⼀定程度的解决⼀些内存溢出的问题，但poi还是有⼀些缺陷，⽐如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很⼤。
easyexcel针对内存做出了优化：重写了poi对07版Excel的解析，能够原本⼀个3M的excel⽤POI sax依然需要100M左右内存降低到⼏M，并且再⼤的excel不会出现内存溢出。
SpringBoot+ EasyExcel实现Excel⽂件的导⼊导出
导⼊依赖
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
<optional>true</optional>
</dependency>
<!--easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beat1</version>
</dependency>
<!--fastjson-->
<dependency>
<groupId>com.</groupId>
<artifactId>jackson-databind</artifactId>
<exclusions>
<exclusion>
<groupId>com.</groupId>
<artifactId>jackson-annotations</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.</groupId>
<artifactId>jackson-annotations</artifactId>
</dependency>
为了防⽌Excel⽂件被破坏在l添加以下内容
<build>
<plugins>
<!-- 让maven不编译xls⽂件，但仍将其打包 -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<nonFilteredFileExtensions>
<nonFilteredFileExtension>xls</nonFilteredFileExtension>
<nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
</nonFilteredFileExtensions>
</configuration>
</plugin>
</plugins>
</build>
application.propertis：配置⽂件
#temp files
在SpringBoot启动类添加临时⽂件设置
@Value("${p.files.path}")
public String filesPath;
@Bean
MultipartConfigElement multipartConfigElement() {
MultipartConfigFactory factory = new MultipartConfigFactory();
//设置路径xxx
factory.setLocation(filesPath);
ateMultipartConfig();
}
ExcelUtil:Excel⼯具类
@Slf4j
public class ExcelUtil {
private static Sheet initSheet;
static {
initSheet = new Sheet(1, 0);
initSheet.setSheetName("sheet");
//设置⾃适应宽度
initSheet.setAutoWidth(Boolean.TRUE);
}
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + de(fileName, "UTF-8"));
workbook.OutputStream());
} catch (IOException e) {
// throw new Message());
}
}
/**
* 读取少于1000⾏数据
*
* @param filePath ⽂件绝对路径
* @return
*/
public static List<Object> readLessThan1000Row(String filePath) {
return readLessThan1000RowBySheet(filePath, null);
}
/**
* 读⼩于1000⾏数据, 带样式
* filePath ⽂件绝对路径
* initSheet ：
* sheetNo: sheet页码，默认为1
* headLineMun: 从第⼏⾏开始读取数据，默认为0, 表⽰从第⼀⾏开始读取
* clazz: 返回数据List<Object> 中Object的类名
*/
public static List<Object> readLessThan1000RowBySheet(String filePath, Sheet sheet) {
if (!StringUtils.hasText(filePath)) {
return null;
}
sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;
try {
fileStream = new FileInputStream(filePath);
ad(fileStream, sheet);
} catch (FileNotFoundException e) {
log.info("不到⽂件或⽂件路径错误, ⽂件：{}", filePath);
} finally {
try {
if (fileStream != null) {
fileStream.close();
}
} catch (IOException e) {
log.info("excel⽂件读取失败, 失败原因：{}", e);
}
}
return null;
}
/**
* 读⼤于1000⾏数据
*
* @param filePath ⽂件觉得路径
* @return
*/
public static List<Object> readMoreThan1000Row(String filePath) {
return readMoreThan1000RowBySheet(filePath, null);
}
/**spring到底是干啥的
* 读⼤于1000⾏数据, 带样式
*
* @param filePath ⽂件觉得路径
* @return
*/
public static List<Object> readMoreThan1000RowBySheet(String filePath, Sheet sheet) {
if (!StringUtils.hasText(filePath)) {
return null;
}
sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;
try {
fileStream = new FileInputStream(filePath);
ExcelListener excelListener = new ExcelListener();
Datas();
} catch (FileNotFoundException e) {
<("不到⽂件或⽂件路径错误, ⽂件：{}", filePath);
} finally {
try {
if (fileStream != null) {
fileStream.close();
}
} catch (IOException e) {
<("excel⽂件读取失败, 失败原因：{}", e);
}
}
return null;
}
/**
* 读⼤于1000⾏数据, 带样式
*
* @return
*/
public static List<Object> readMoreThan1000RowBySheetFromInputStream(InputStream inputStream, Sheet sheet) { sheet = sheet != null ? sheet : initSheet;
InputStream fileStream = null;
ExcelListener excelListener = new ExcelListener();
Datas();
}
/**
* ⽣成excle
*
* @param filePath 绝对路径
* @param data 数据源
* @param head 表头
*/
public static void writeBySimple(String filePath, List<List<Object>> data, List<String> head) {
writeSimpleBySheet(filePath, data, head, null);
}
/**
* ⽣成excle
*
* @param filePath 路径
* @param data 数据源
* @param sheet excle页⾯样式
* @param head 表头
*/
public static void writeSimpleBySheet(String filePath, List<List<Object>> data, List<String> head, Sheet sheet) { sheet = (sheet != null) ? sheet : initSheet;
if (head != null) {
List<List<String>> list = new ArrayList<>();
head.forEach(h -> list.add(Collections.singletonList(h)));
sheet.setHead(list);
}
OutputStream outputStream = null;
ExcelWriter writer = null;
try {
outputStream = new FileOutputStream(filePath);
writer = Writer(outputStream);
writer.write1(data, sheet);
} catch (FileNotFoundException e) {
<("不到⽂件或⽂件路径错误, ⽂件：{}", filePath);
} finally {
try {
if (writer != null) {
writer.finish();
}
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
<("excel⽂件导出失败, 失败原因：{}", e);
}
}
}
/**
* ⽣成excle
*
* @param filePath 路径
* @param data 数据源
*/
public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data) {
writeWithTemplateAndSheet(filePath, data, null);
}
/**
* ⽣成excle
*
* @param filePath 路径
* @param data 数据源
* @param sheet excle页⾯样式
*/
public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet) { if (CollectionUtils.isEmpty(data)) {
return;
}
sheet = (sheet != null) ? sheet : initSheet;
sheet.(0).getClass());
OutputStream outputStream = null;
ExcelWriter writer = null;
try {
outputStream = new FileOutputStream(filePath);
writer = Writer(outputStream);
writer.write(data, sheet);
} catch (FileNotFoundException e) {
<("不到⽂件或⽂件路径错误, ⽂件：{}", filePath);
} finally {
try {
if (writer != null) {
writer.finish();
}
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
<("excel⽂件导出失败, 失败原因：{}", e);
}
}
}
* @param filePath 路径
* @param multipleSheelPropetys
*/
public static void writeWithMultipleSheel(String filePath, List<MultipleSheelPropety> multipleSheelPropetys) { if (CollectionUtils.isEmpty(multipleSheelPropetys)) {
return;
}
OutputStream outputStream = null;
ExcelWriter writer = null;
try {
outputStream = new FileOutputStream(filePath);
writer = Writer(outputStream);
for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
Sheet sheet = Sheet() != null ? Sheet() : initSheet;
if (!CollectionUtils.Data())) {
sheet.Data().get(0).getClass());
}
writer.Data(), sheet);
}
} catch (FileNotFoundException e) {
<("不到⽂件或⽂件路径错误, ⽂件：{}", filePath);
} finally {
try {
if (writer != null) {
writer.finish();
}
if (outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
<("excel⽂件导出失败, 失败原因：{}", e);
}
}
}
/*********************匿名内部类开始，可以提取出去******************************/
@Data
public static class MultipleSheelPropety {
private List<? extends BaseRowModel> data;
private Sheet sheet;
}
/**
* 解析，
* 每解析⼀⾏会回调invoke()⽅法。
* 整个excel解析结束会执⾏doAfterAllAnalysed()⽅法
*
* @author: chenmingjian
* @date: 19-4-3 14:11
*/
@Getter
@Setter
public static class ExcelListener extends AnalysisEventListener {
private List<Object> datas = new ArrayList<>();
/**
* 逐⾏解析
* object : 当前⾏的数据
*/
@Override
public void invoke(Object object, AnalysisContext context) {
//当前⾏
// CurrentRowNum()
if (object != null) {
datas.add(object);
}
}
/**
* 解析完所有数据后会调⽤该⽅法
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
//解析结束销毁不⽤的资源
}
}
/
************************匿名内部类结束，可以提取出去***************************/
}
CommonUtil:⼯具类
public class CommonUtil {
/**
* ⽣成32位编码，不含横线
*
* @return uuid串
*/
public static String getUUID() {
String uuid = UUID.randomUUID().toString().trim().replaceAll("-", "");
UpperCase();
}
/**
* 得到当前⽇期格式化后的字符串，格式：yyyy-MM-dd(年-⽉-⽇)
* @return 当前⽇期格式化后的字符串
*/
public static String getTodayStr(){
return new SimpleDateFormat("yyyy-MM-dd").format(new Date()) ;
}
* @param t
* @return
* @throws JsonProcessingException
*/
public static <T> String toJson(T t) throws JsonProcessingException {
return ().writeValueAsString(t);
}
}
UserPojoRes：实体类
@Setter
@Getter
@ToString
public class UserPojoRes extends BaseRowModel implements Serializable {
private static final long serialVersionUID = -2145503717390503506L;
/**
* 主键
*/
@ExcelProperty(value = "ID", index = 0)
private String id;
/**
* 姓名
*/
@ExcelProperty(value = "⽤户名", index = 1)
private String name;
public UserPojoRes(String id, String name) {
this.id = id;
this.name = name;
}
public UserPojoRes(){
}
}
验证
模板下载
这⾥将模板⽂件放在resources中
@GetMapping("/exportExcelTempalte")
@ApiOperation(value = "下载导⼊模板")
public void exportExcelTempalte(HttpServletResponse response) throws Exception {
//Resource⽬录中的⽂件
String filePath = "/excels/导⼊模板.xlsx";
ClassPathResource classPathResource = new ClassPathResource(filePath);
Workbook InputStream());
ExcelUtil.downLoadExcel("导⼊模板.xlsx", response, workbook);
}
Excel⽂件导⼊
@PostMapping("/importExcel")
@ApiOperation(value = "Excel⽂件导⼊")
public Response importExcel(HttpServletRequest request, MultipartFile file, HttpServletResponse response) throws Exception { List<Object> objects = InputStream(),null);
List<UserPojoRes> list = new ArrayList<>();
for (Object o : objects) {
UserPojoRes userPojoRes = new UserPojoRes();
List<String> stringList = (List<String>) o;
userPojoRes.(0) != null ? (0).toString() : "");
userPojoRes.(1) != null ? (0).toString() : "");
list.add(userPojoRes);
}
String json = Json(list);
return new Response(json);
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论