使用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 {
public static List
二、实体类添加注解，使实体类与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";
@PostMapping("/importExcelBefore")
@ResponseBody
public Map importExcelBefore(@RequestParam(value = "file") List files, HttpServletRequest request) throws Exception {
Map 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 importExcelBefore(List
采用批量插入的方法导入数据库，mapper里用foearch ，serverImpl代码如下：
@Service
public class BiExcelServiceImpl implements BiExcelService {
@Resource
private BiFinanceFeeDetailMapper biFinanceFeeDetailMapper;
@Resource
private BiFinanceCustPayMapper biFinanceCustPayMapper;
@Resource
private BiFinanceFeePersonDetailMapper biFinanceFeePersonDetailMapper;
@Override
public Map importExcelBefore(List
五、mapper层代码
代码如下：
public interface BiFinanceFeeDetailMapper {
int batchSave(@Param("lists") List
mapperImpl代码：
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) 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
总结
使用阿里的EasyExcel插件可以非常简单高效的实现excel的导入和导出。
