前言

使用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;
}
}

二、实体类添加注解，使实体类与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;
}

三、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;
}

四、server层代码

代码如下：

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

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

@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层代码

代码如下：

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>

总结

使用阿里的EasyExcel插件可以非常简单高效的实现excel的导入和导出。

最后

以上就是甜甜野狼为你收集整理的阿里EasyExcel解析excel并导入数据库（相较于poi可防止内存溢出）前言一、EasyExcel解析excel的工具类二、实体类添加注解，使实体类与excel对应三、controller调用，解析excel四、server层代码五、mapper层代码总结的全部内容，希望文章能够帮你解决阿里EasyExcel解析excel并导入数据库（相较于poi可防止内存溢出）前言一、EasyExcel解析excel的工具类二、实体类添加注解，使实体类与excel对应三、controller调用，解析excel四、server层代码五、mapper层代码总结所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。