提示:文章如有错误的地方请指出,以免误人子弟!
easyexcel 导入
提示:以下是本篇文章正文内容,下面案例可供参考
一、导入maven jar包
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
二、直接上代码
easyexcel 官网
本文章只展示单个sheet读取。
多个sheet 读取,其实就是新加一个读sheet方法以及sheet数据类型不同,需要重写多个监听器。
- controller
@Autowired
private SysEmpInfoMapper sysEmpInfoMapper;
@PostMapping("/importEmpExcel")
public ResponseResult importEmpExcelInfo(MultipartFile multipartFile) {
try {
// 文件名称
String fileName = multipartFile.getOriginalFilename();
// 截取文件后缀
String fileSuffix = null;
if (StringUtils.isNotEmpty(fileName)) {
fileSuffix = fileName.substring(fileName.indexOf("."));
}
// 判断文件是否是excel文件
if (FinalCode.EXCEL_SUFFIX.equals(fileSuffix) || FinalCode.EXCEL_SUFFIX_PLUS.equals(fileSuffix)) {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(multipartFile.getInputStream());
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
// 验证是否是指定的模板
ResponseResult result = excelModel(sheet);
if (!StringCommons.isEmpty(result)) {
return result;
}
else {
// 判断文件是否为空
if (sheet.getPhysicalNumberOfRows() == 1) {
return ResponseResult.create().success(false).msg(FinalCode.ABNORMAL_CODE, "模板数据为空");
}
EmpExcelListener empExcelListener = new EmpExcelListener(sysEmpInfoMapper);
EasyExcel.read(multipartFile.getInputStream(), EmpInfoExcelModel.class, empExcelListener).sheet("用户信息").doRead();
}
}
} catch (IOException e) {
e.printStackTrace();
}
return ResponseResult.create().success(true).data("success");
}
验证模板的封装方法
private ResponseResult excelModel(XSSFSheet sheet) {
XSSFRow row = null;
ResponseResult result = null;
boolean flagWait = true;
for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
boolean flag = false;
if (i == 0) {
flag = true;
}
row = sheet.getRow(i);
if (flag) {
if (!StringCommons.isEmpty(row.getCell(0))) {
String stringCellValue = row.getCell(0).getStringCellValue();
if (!"工号".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(1))) {
String stringCellValue = row.getCell(1).getStringCellValue();
if (!"昵称".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(2))) {
String stringCellValue = row.getCell(2).getStringCellValue();
if (!"姓名".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(3))) {
String stringCellValue = row.getCell(3).getStringCellValue();
if (!"性别".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(4))) {
String stringCellValue = row.getCell(4).getStringCellValue();
if (!"年龄".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(5))) {
String stringCellValue = row.getCell(5).getStringCellValue();
if (!"出生日期".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(6))) {
String stringCellValue = row.getCell(6).getStringCellValue();
if (!"手机号".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(7))) {
String stringCellValue = row.getCell(7).getStringCellValue();
if (!"电子邮箱".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(8))) {
String stringCellValue = row.getCell(8).getStringCellValue();
if (!"身份证号".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(9))) {
String stringCellValue = row.getCell(9).getStringCellValue();
if (!"学历".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(10))) {
String stringCellValue = row.getCell(10).getStringCellValue();
if (!"毕业院校".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(11))) {
String stringCellValue = row.getCell(11).getStringCellValue();
if (!"省市区".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(12))) {
String stringCellValue = row.getCell(12).getStringCellValue();
if (!"详细地址".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(13))) {
String stringCellValue = row.getCell(13).getStringCellValue();
if (!"入职时间".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(14))) {
String stringCellValue = row.getCell(14).getStringCellValue();
if (!"部门名称".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(15))) {
String stringCellValue = row.getCell(15).getStringCellValue();
if (!"职位名称".equals(stringCellValue)) {
flagWait = false;
break;
}
}
if (!StringCommons.isEmpty(row.getCell(16))) {
String stringCellValue = row.getCell(16).getStringCellValue();
if (!"创建时间".equals(stringCellValue)) {
flagWait = false;
break;
}
}
}
}
if (!flagWait) {
result = ResponseResult.create().success(false).msg(FinalCode.ABNORMAL_CODE, "请使用正确模板填写");
}
return result;
}
EmpInfoExcelModel.class
这个就是接收excel sheet数据类型所需的实体类
/**
* @author
* @date 2021/01/27 11:00
* HeadStyle:表头背景颜色为白色
* ContentRowHeight:内容行高
* HeadRowHeight:表头行高
* ContentStyle:内容水平居中
*/
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 9)
@ContentRowHeight(18)
@HeadRowHeight(25)
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER)
@Data
@JsonInclude(value = JsonInclude.Include.NON_NULL)
public class EmpInfoExcelModel implements Serializable {
/**
* description: @ExcelIgnore不导出该字段
*/
@ExcelIgnore
@ColumnWidth(8)
@ExcelProperty(value = "ID", index = 0)
private String id;
@ColumnWidth(10)
@ExcelProperty(value = "工号")
private String jobNumber;
@ColumnWidth(15)
@ExcelProperty(value = "昵称")
private String userName;
@ColumnWidth(10)
@ExcelProperty(value = "姓名")
private String fullName;
@ColumnWidth(8)
@ExcelProperty(value = "性别")
private String gender;
@ColumnWidth(8)
@ExcelProperty(value = "年龄")
private Integer age;
@ColumnWidth(17)
@ExcelProperty(value = "出生日期")
private String birthday;
@ColumnWidth(15)
@ExcelProperty(value = "手机号")
private String userPhone;
@ColumnWidth(20)
@ExcelProperty(value = "电子邮箱")
private String userMail;
@ColumnWidth(25)
@ExcelProperty(value = "身份证号")
private String idNumber;
@ColumnWidth(10)
@ExcelProperty(value = "学历")
private String education;
@ColumnWidth(30)
@ExcelProperty(value = "毕业院校")
private String graduationSchool;
@ColumnWidth(25)
@ExcelProperty(value = "省市区")
private String address;
@ColumnWidth(35)
@ExcelProperty(value = "详细地址")
private String detailedAddress;
@ColumnWidth(22)
@ExcelProperty(value = "入职时间")
private String entryTime;
@ColumnWidth(18)
@ExcelProperty(value = "部门名称")
private String depName;
@ColumnWidth(18)
@ExcelProperty(value = "职位名称")
private String roleName;
// @ColumnWidth(18)
// @ExcelProperty(value = "权限名称")
// private String perName;
@ColumnWidth(22)
@ExcelProperty(value = "创建时间")
private String createTime;
}
监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import com.hqll.system.entity.doo.system.SysEmpInfoDO;
import com.hqll.system.entity.dto.system.EmpInfoExcelModel;
import com.hqll.system.mapper.system.SysEmpInfoMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import java.util.List;
import java.util.UUID;
/**
* @author Tigger
* @date 2023/2/3 19:55
*/
@Slf4j
public class EmpExcelListener implements ReadListener<EmpInfoExcelModel> {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
*/
private List<SysEmpInfoDO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private SysEmpInfoMapper sysEmpInfoMapper;
/**
* description: 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param sysEmpInfoMapper sysEmpInfoMapper
* @return
* @author Tigger
*/
public EmpExcelListener(SysEmpInfoMapper sysEmpInfoMapper) {
this.sysEmpInfoMapper = sysEmpInfoMapper;
}
/**
* description: 这个每一条数据解析都会来调用
*
* @param empInfoExcelModel 表格数据
* @param analysisContext analysisContext
* @return void
* @author Tigger
*/
@Override
public void invoke(EmpInfoExcelModel empInfoExcelModel, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(empInfoExcelModel));
empInfoExcelModel.setBirthday(empInfoExcelModel.getBirthday().replace("/", "-"));
empInfoExcelModel.setEntryTime(empInfoExcelModel.getEntryTime().replace("/", "-"));
empInfoExcelModel.setCreateTime(empInfoExcelModel.getCreateTime().replace("/", "-"));
SysEmpInfoDO sysEmpInfoDO = new SysEmpInfoDO();
BeanUtils.copyProperties(empInfoExcelModel, sysEmpInfoDO);
sysEmpInfoDO.setId(UUID.randomUUID().toString());
// 性别
String gender = sysEmpInfoDO.getGender();
if (StringUtils.isNotEmpty(gender)) {
String genderName = "女";
String name;
if (genderName.equals(gender)) {
name = "x";
}
else {
name = "y";
}
sysEmpInfoDO.setGender(name);
}
// 查询部门id
if (StringUtils.isNotEmpty(empInfoExcelModel.getDepName())) {
String depName = empInfoExcelModel.getDepName();
Integer depId = sysEmpInfoMapper.getDepartmentId(depName);
sysEmpInfoDO.setDepId(depId);
}
// 查询角色id
if (StringUtils.isNotEmpty(empInfoExcelModel.getRoleName())) {
String roleName = empInfoExcelModel.getRoleName();
Integer roleId = sysEmpInfoMapper.getRoleId(roleName);
sysEmpInfoDO.setRoleId(roleId);
}
cachedDataList.add(sysEmpInfoDO);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* description: 所有数据解析完成了 都会来调用
*
* @param analysisContext
* @return void
* @author Tigger
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
if (cachedDataList.size() > 0) {
saveData();
}
log.info("所有数据解析完成!");
}
/**
* description: 加上存储数据库
*
* @param
* @return void
* @author Tigger
*/
private void saveData() {
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
int i = sysEmpInfoMapper.addEmpInfoList(cachedDataList);
if (i > 0) {
log.info("存储数据库成功!");
}
}
}
- mapper
/**
* description: 批量添加用户信息
*
* @param list 用户集合
* @return int
* @author Tigger
*/
int addEmpInfoList(List<SysEmpInfoDO> list);
- sql
<insert id="addEmpInfoList" parameterType="java.util.List">
insert into sys_emp_info (
id,
job_number,
userName,
full_name,
gender,
birthday,
userPhone,
userMail,
id_number,
education,
graduation_school,
address,
detailed_address,
entry_time,
depId,
roleId,
create_time
)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},
#{item.jobNumber},
#{item.userName},
#{item.fullName},
#{item.gender},
#{item.birthday},
#{item.userPhone},
#{item.userMail},
#{item.idNumber},
#{item.education},
#{item.graduationSchool},
#{item.address},
#{item.detailedAddress},
#{item.entryTime},
#{item.depId},
#{item.roleId},
#{item.createTime})
</foreach>
</insert>
希望对你有所帮助!