阿里EasyExcel解析excel并导入数据库（相较于poi可防止内存溢出）前言一、EasyExcel解析excel的工具类二、实体类添加注解，使实体类与excel对应三、controller调用，解析excel四、server层代码五、mapper层代码总结

119 阅读 0 评论 79 点赞

我是靠谱客的博主甜甜野狼，这篇文章主要介绍阿里EasyExcel解析excel并导入数据库（相较于poi可防止内存溢出）前言一、EasyExcel解析excel的工具类二、实体类添加注解，使实体类与excel对应三、controller调用，解析excel四、server层代码五、mapper层代码总结，现在分享给大家，希望可以做个参考。

前言

使用poi进行excel文件的解析时，容易出现内存溢出的问题，前面用阿里的EasyExcel实现了excel的导出，亦可使用EasyExcel对excel文件进行解析。

一、EasyExcel解析excel的工具类

多种解析类型的供选择，具体代码如下：

复制代码

package com.gettop.bi.core.util;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
public class ExcelUtil {
/**
* 读取 Excel(多个 sheet)
*
* @param excel
文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws Exception {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
for (Sheet sheet : reader.getSheets()) {
if (rowModel != null) {
sheet.setClazz(rowModel.getClass());
}
reader.read(sheet);
}
return excelListener.getDatas();
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel
文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @param sheetNo
sheet 的序号 从1开始
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws Exception {
return readExcel(excel, rowModel, sheetNo, 1);
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel
文件
* @param rowModel
实体类映射，继承 BaseRowModel 类
* @param sheetNo
sheet 的序号 从1开始
* @param headLineNum 表头行数，默认为1
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo,
int headLineNum) throws Exception {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
return excelListener.getDatas();
}
/**
* 导出 Excel ：一个 sheet，带表头
*
* @param response
HttpServletResponse
* @param list
数据 list，每个元素为一个 BaseRowModel
* @param fileName
导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object
映射实体类，Excel 模型
*/
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object) {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出文件时为Writer生成OutputStream
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
//创建本地文件
String filePath = fileName + ".xlsx";
File dbfFile = new File(filePath);
try {
if (!dbfFile.exists() || dbfFile.isDirectory()) {
dbfFile.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
/**
* 返回 ExcelReader
*
* @param excel
需要解析的 Excel 文件
* @param excelListener new ExcelListener()
*/
private static ExcelReader getReader(MultipartFile excel,
ExcelListener excelListener) throws Exception {
String filename = excel.getOriginalFilename();
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
throw new Exception("文件格式错误！");
}
InputStream inputStream;
try {
inputStream = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(inputStream, null, excelListener, false);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
package com.gettop.bi.core.util;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
public class ExcelUtil {
/**
* 读取 Excel(多个 sheet)
*
* @param excel
文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) throws Exception {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
for (Sheet sheet : reader.getSheets()) {
if (rowModel != null) {
sheet.setClazz(rowModel.getClass());
}
reader.read(sheet);
}
return excelListener.getDatas();
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel
文件
* @param rowModel 实体类映射，继承 BaseRowModel 类
* @param sheetNo
sheet 的序号 从1开始
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) throws Exception {
return readExcel(excel, rowModel, sheetNo, 1);
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel
文件
* @param rowModel
实体类映射，继承 BaseRowModel 类
* @param sheetNo
sheet 的序号 从1开始
* @param headLineNum 表头行数，默认为1
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo,
int headLineNum) throws Exception {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
return excelListener.getDatas();
}
/**
* 导出 Excel ：一个 sheet，带表头
*
* @param response
HttpServletResponse
* @param list
数据 list，每个元素为一个 BaseRowModel
* @param fileName
导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object
映射实体类，Excel 模型
*/
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object) {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出文件时为Writer生成OutputStream
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
//创建本地文件
String filePath = fileName + ".xlsx";
File dbfFile = new File(filePath);
try {
if (!dbfFile.exists() || dbfFile.isDirectory()) {
dbfFile.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
/**
* 返回 ExcelReader
*
* @param excel
需要解析的 Excel 文件
* @param excelListener new ExcelListener()
*/
private static ExcelReader getReader(MultipartFile excel,
ExcelListener excelListener) throws Exception {
String filename = excel.getOriginalFilename();
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
throw new Exception("文件格式错误！");
}
InputStream inputStream;
try {
inputStream = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(inputStream, null, excelListener, false);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}

二、实体类添加注解，使实体类与excel对应

使用EasyExcel里的@ExcelProperty注解，具体代码如下：

复制代码

package com.gettop.bi.web.model;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.ToString;
import java.math.BigDecimal;
@Data
@ToString
public class BiFinanceFeeDetail extends BaseRowModel {
private String id;
@ExcelProperty(index = 0)
private String versionCode;
@ExcelProperty(index = 1)
private String propertyCode;
@ExcelProperty(index = 2)
private String sbuCode;
@ExcelProperty(index = 3)
private String projectCode;
@ExcelProperty(index = 4)
private String departmentBigSort;
@ExcelProperty(index = 5)
private String departmentMotivateSort;
@ExcelProperty(index = 6)
private String departmentSort;
@ExcelProperty(index = 7)
private String budgetEntity;
@ExcelProperty(index = 8)
private String subjectLevel1;
@ExcelProperty(index = 9)
private String subjectLevel2;
@ExcelProperty(index = 10)
private String feeSplitSort;
@ExcelProperty(index = 11)
private String feeSort;
@ExcelProperty(index = 12)
private String yearMonth;
@ExcelProperty(index = 13)
private BigDecimal feeMoney;
@ExcelProperty(index = 14)
private String remark;
@ExcelProperty(index = 15)
private String quotationProjectName;
@ExcelProperty(index = 16)
private String buInnerExternal;
@ExcelProperty(index = 17)
private String ghWorkshop;
@ExcelProperty(index = 18)
private BigDecimal zfApportionRate;
@ExcelProperty(index = 19)
private String yfShowDepartment;
@ExcelProperty(index = 20)
private String iyear;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
package com.gettop.bi.web.model;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.ToString;
import java.math.BigDecimal;
@Data
@ToString
public class BiFinanceFeeDetail extends BaseRowModel {
private String id;
@ExcelProperty(index = 0)
private String versionCode;
@ExcelProperty(index = 1)
private String propertyCode;
@ExcelProperty(index = 2)
private String sbuCode;
@ExcelProperty(index = 3)
private String projectCode;
@ExcelProperty(index = 4)
private String departmentBigSort;
@ExcelProperty(index = 5)
private String departmentMotivateSort;
@ExcelProperty(index = 6)
private String departmentSort;
@ExcelProperty(index = 7)
private String budgetEntity;
@ExcelProperty(index = 8)
private String subjectLevel1;
@ExcelProperty(index = 9)
private String subjectLevel2;
@ExcelProperty(index = 10)
private String feeSplitSort;
@ExcelProperty(index = 11)
private String feeSort;
@ExcelProperty(index = 12)
private String yearMonth;
@ExcelProperty(index = 13)
private BigDecimal feeMoney;
@ExcelProperty(index = 14)
private String remark;
@ExcelProperty(index = 15)
private String quotationProjectName;
@ExcelProperty(index = 16)
private String buInnerExternal;
@ExcelProperty(index = 17)
private String ghWorkshop;
@ExcelProperty(index = 18)
private BigDecimal zfApportionRate;
@ExcelProperty(index = 19)
private String yfShowDepartment;
@ExcelProperty(index = 20)
private String iyear;
}

三、controller调用，解析excel

具体代码如下：

复制代码

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
@Controller
@Transactional
@RequestMapping(value = "/excel")
public class BiExcelController extends Thread{
@Resource
private BiExcelService biExcelService;
private static final String FILE_PATH="D:\caijing_file";
/**
* 导入
*
* @param
* @return
*/
@PostMapping("/importExcelBefore")
@ResponseBody
public Map<String, Object> importExcelBefore(@RequestParam(value = "file") List<MultipartFile> files, HttpServletRequest request) throws Exception {
Map<String, Object> result = new HashMap<>();
if (files != null && files.size() > 0) {
MultipartFile file = files.get(0);
String name = file.getOriginalFilename();
File targetDir = new File(FILE_PATH);
if (!targetDir.exists()) targetDir.mkdirs();
File targetFile = new File(FILE_PATH + File.separator + name);
IOUtils.write(IOUtils.toByteArray(file.getInputStream()), new FileOutputStream(targetFile));
String filePath = targetFile.toString();
if(file.getOriginalFilename().equals("xx.xlsx")){
List list=
ExcelUtil.readExcel(file,new BiFinanceFeeDetail());
result = biExcelService.importExcelBefore(list, name);
}else if(file.getOriginalFilename().equals("xx.xlsx")){
List list=
ExcelUtil.readExcel(file, new BiFinanceCustPay());
result = biExcelService.importExcelBefore(list, name);
}else if(file.getOriginalFilename().equals("xx.xlsx")){
List list=
ExcelUtil.readExcel(file, new BiFinanceFeePersonDetail());
result = biExcelService.importExcelBefore(list, name);
}else{
result.put("fail",1);
}
result.put("filePath",targetFile.getAbsolutePath());
}
return result;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
@Controller
@Transactional
@RequestMapping(value = "/excel")
public class BiExcelController extends Thread{
@Resource
private BiExcelService biExcelService;
private static final String FILE_PATH="D:\caijing_file";
/**
* 导入
*
* @param
* @return
*/
@PostMapping("/importExcelBefore")
@ResponseBody
public Map<String, Object> importExcelBefore(@RequestParam(value = "file") List<MultipartFile> files, HttpServletRequest request) throws Exception {
Map<String, Object> result = new HashMap<>();
if (files != null && files.size() > 0) {
MultipartFile file = files.get(0);
String name = file.getOriginalFilename();
File targetDir = new File(FILE_PATH);
if (!targetDir.exists()) targetDir.mkdirs();
File targetFile = new File(FILE_PATH + File.separator + name);
IOUtils.write(IOUtils.toByteArray(file.getInputStream()), new FileOutputStream(targetFile));
String filePath = targetFile.toString();
if(file.getOriginalFilename().equals("xx.xlsx")){
List list=
ExcelUtil.readExcel(file,new BiFinanceFeeDetail());
result = biExcelService.importExcelBefore(list, name);
}else if(file.getOriginalFilename().equals("xx.xlsx")){
List list=
ExcelUtil.readExcel(file, new BiFinanceCustPay());
result = biExcelService.importExcelBefore(list, name);
}else if(file.getOriginalFilename().equals("xx.xlsx")){
List list=
ExcelUtil.readExcel(file, new BiFinanceFeePersonDetail());
result = biExcelService.importExcelBefore(list, name);
}else{
result.put("fail",1);
}
result.put("filePath",targetFile.getAbsolutePath());
}
return result;
}

四、server层代码

代码如下：

复制代码

1
2
3
4
public interface BiExcelService {
Map<String, Object> importExcelBefore(List<Object> list, String name);
}

采用批量插入的方法导入数据库，mapper里用foearch ，serverImpl代码如下：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
@Service
public class BiExcelServiceImpl implements BiExcelService {
@Resource
private BiFinanceFeeDetailMapper biFinanceFeeDetailMapper;
@Resource
private BiFinanceCustPayMapper biFinanceCustPayMapper;
@Resource
private BiFinanceFeePersonDetailMapper biFinanceFeePersonDetailMapper;
@Override
public Map<String, Object> importExcelBefore(List<Object> list, String name) {
Map<String, Object> messageMap = new HashMap<>();
int nums = 1000;
int times = (int)Math.ceil((float)list.size() / nums);// 插入次数
/** 获取当前系统时间*/
long startTime =
System.currentTimeMillis();
try {
for (int i = 0; i < times; i++){
if(i == times - 1){
biFinanceFeeDetailMapper.batchSave(list.subList(i * nums, list.size()));
messageMap.put("success",1);
}else {
biFinanceFeeDetailMapper.batchSave(list.subList(i * nums, (i+1) * nums));
messageMap.put("success",1);
}
}
long endTime =
System.currentTimeMillis();
long usedTime = (endTime-startTime)/1000;
System.out.println(usedTime);
}
}

五、mapper层代码

代码如下：

复制代码

1
2
3
4
public interface BiFinanceFeeDetailMapper {
int batchSave(@Param("lists") List<Object> list);
}

mapperImpl代码：

复制代码

<insert id="batchSave" parameterType="com.gettop.bi.web.model.BiFinanceFeeDetail" useGeneratedKeys="false">
insert into BI_FINANCE_FEE_DETAIL (ID,VERSION_CODE, PROPERTY_CODE,
SBU_CODE, PROJECT_CODE, DEPARTMENT_BIG_SORT,
DEPARTMENT_MOTIVATE_SORT, DEPARTMENT_SORT,
BUDGET_ENTITY, SUBJECT_LEVEL1, SUBJECT_LEVEL2,
FEE_SPLIT_SORT, FEE_SORT, YEAR_MONTH,
FEE_MONEY, REMARK, QUOTATION_PROJECT_NAME,
BU_INNER_EXTERNAL, GH_WORKSHOP, ZF_APPORTION_RATE,
YF_SHOW_DEPARTMENT, IYEAR)
<foreach collection="lists" item="list" index="index" separator="union all">
SELECT
sys_guid(),
#{list.versionCode,jdbcType=VARCHAR}, #{list.propertyCode,jdbcType=VARCHAR},
#{list.sbuCode,jdbcType=VARCHAR}, #{list.projectCode,jdbcType=VARCHAR}, #{list.departmentBigSort,jdbcType=VARCHAR},
#{list.departmentMotivateSort,jdbcType=VARCHAR}, #{list.departmentSort,jdbcType=VARCHAR},
#{list.budgetEntity,jdbcType=VARCHAR}, #{list.subjectLevel1,jdbcType=VARCHAR}, #{list.subjectLevel2,jdbcType=VARCHAR},
#{list.feeSplitSort,jdbcType=VARCHAR}, #{list.feeSort,jdbcType=VARCHAR}, #{list.yearMonth,jdbcType=VARCHAR},
#{list.feeMoney,jdbcType=DECIMAL}, #{list.remark,jdbcType=VARCHAR}, #{list.quotationProjectName,jdbcType=VARCHAR},
#{list.buInnerExternal,jdbcType=VARCHAR}, #{list.ghWorkshop,jdbcType=VARCHAR}, #{list.zfApportionRate,jdbcType=DECIMAL},
#{list.yfShowDepartment,jdbcType=VARCHAR}, #{list.iyear,jdbcType=VARCHAR}
FROM dual
</foreach>
</insert>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

<insert id="batchSave" parameterType="com.gettop.bi.web.model.BiFinanceFeeDetail" useGeneratedKeys="false">
insert into BI_FINANCE_FEE_DETAIL (ID,VERSION_CODE, PROPERTY_CODE,
SBU_CODE, PROJECT_CODE, DEPARTMENT_BIG_SORT,
DEPARTMENT_MOTIVATE_SORT, DEPARTMENT_SORT,
BUDGET_ENTITY, SUBJECT_LEVEL1, SUBJECT_LEVEL2,
FEE_SPLIT_SORT, FEE_SORT, YEAR_MONTH,
FEE_MONEY, REMARK, QUOTATION_PROJECT_NAME,
BU_INNER_EXTERNAL, GH_WORKSHOP, ZF_APPORTION_RATE,
YF_SHOW_DEPARTMENT, IYEAR)
<foreach collection="lists" item="list" index="index" separator="union all">
SELECT
sys_guid(),
#{list.versionCode,jdbcType=VARCHAR}, #{list.propertyCode,jdbcType=VARCHAR},
#{list.sbuCode,jdbcType=VARCHAR}, #{list.projectCode,jdbcType=VARCHAR}, #{list.departmentBigSort,jdbcType=VARCHAR},
#{list.departmentMotivateSort,jdbcType=VARCHAR}, #{list.departmentSort,jdbcType=VARCHAR},
#{list.budgetEntity,jdbcType=VARCHAR}, #{list.subjectLevel1,jdbcType=VARCHAR}, #{list.subjectLevel2,jdbcType=VARCHAR},
#{list.feeSplitSort,jdbcType=VARCHAR}, #{list.feeSort,jdbcType=VARCHAR}, #{list.yearMonth,jdbcType=VARCHAR},
#{list.feeMoney,jdbcType=DECIMAL}, #{list.remark,jdbcType=VARCHAR}, #{list.quotationProjectName,jdbcType=VARCHAR},
#{list.buInnerExternal,jdbcType=VARCHAR}, #{list.ghWorkshop,jdbcType=VARCHAR}, #{list.zfApportionRate,jdbcType=DECIMAL},
#{list.yfShowDepartment,jdbcType=VARCHAR}, #{list.iyear,jdbcType=VARCHAR}
FROM dual
</foreach>
</insert>