The home page is the dependency of the pom file to import EasyExcel
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.2.0</version> </dependency>
Add three fields to mysql for testing
custom exception class
package com.example.demo.config;
import lombok.Data;
import lombok.EqualsAndHashCode;
@EqualsAndHashCode(callSuper = true)
@Data
public class ImportException extends RuntimeException {
private String msg;
private String code;
public ImportException() {
super();
}
public ImportException(String code, String message) {
super(message);
this.code = code;
this.msg = message;
}
public ImportException(String message) {
super(message);
this.msg = message;
}
public String getCode() {
return code;
}
public String getMessage() {
return msg;
}
}
package com.example.demo.config;
import com.example.demo.response.BaseResponse;
import com.example.demo.response.RespGenerator;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
@RestControllerAdvice
public class GlobalExceptionHandler {
private static final Log logger = LogFactory. getLog(GlobalExceptionHandler. class);
@ExceptionHandler(ImportException. class)
public BaseResponse<Object> businessException(ImportException importException) {
return RespGenerator. fail(importException. getCode(), importException. getMsg());
}
}
BaseResponse
package com.example.demo.response;
import lombok.Data;
@Data
public class BaseResponse<T> {
private String code;
private String message;
private T data;
/**
*
* Default constructor
*
* @param code
* status code
* @param message
* interface information
* @param data
* interface data
*/
public BaseResponse(String code, String message, T data) {
super();
this.code = code;
this. message = message;
this.data = data;
}
/**
* Default constructor
*/
public BaseResponse() {
super();
}
}
Entity class User
The value in value corresponds to the header name in Excel, and the index corresponds to the header position in Excel
package com.example.demo.model.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.*;
import lombok.experimental.Accessors;
import java.io.Serializable;
@Data
@EqualsAndHashCode(callSuper = true)
//@Accessors(chain = true)
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class User extends Model implements Serializable {
private static final long serialVersionUID = 1L;
// value corresponds to the index through the title text, and corresponds to the index through the text line number
@ExcelProperty(value = "id", index = 0)
@TableField("uid")
private String uid;
@ExcelProperty(value = "Name", index = 1)
@TableField("userName")
private String userName;
@ExcelProperty(value = "password", index = 2)
@TableField("password")
private String password;
}
controller
The dependency of @Slf4j can be imported by Baidu
package com.example.demo.controller;
import com.example.demo.response.BaseResponse;
import com.example.demo.service.ImportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@RestController
@Slf4j
@RequestMapping(value = "/import")
public class ImportController {
@Resource
private ImportService importService;
/**
* Import Excel
* @param file
* @return
*/
@PostMapping("/upLoad")
public BaseResponse upLoad(@RequestParam("file") MultipartFile file) {
return importService.importExcel(file);
}
}
service
import com.example.demo.response.BaseResponse;
import org.springframework.web.multipart.MultipartFile;
public interface ImportService {
BaseResponse importExcel(MultipartFile file);
}
ImportServiceImpl
package com.example.demo.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.example.demo.config.ImportException;
import com.example.demo.config.UserReadListener;
import com.example.demo.model.entity.User;
import com.example.demo.response.BaseResponse;
import com.example.demo.service.IUserService;
import com.example.demo.service.ImportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
@Service
@Slf4j
public class ImportServiceImpl implements ImportService {
/**
* Store the database every 2 items, 3000 items can be used in actual use, and then clean up the list to facilitate memory recovery
*/
private static final int BATCH_COUNT = 2;
@Resource
private IUserService userService;
@Transactional
@Override
public BaseResponse importExcel(MultipartFile file){
// Check if the file is empty
if (ObjectUtils.isEmpty(file) || file.getSize() <= 0) {
throw new ImportException("upload file size is empty");
}
// get the file name
String originalFilename = file. getOriginalFilename();
String s = originalFilename.toLowerCase();
String s1 = s.substring(s.indexOf('.') + 1).toLowerCase();
// check file format
if (!"xlsx".equals(s1) & amp; & amp; !"xls".equals(s1)) {
throw new ImportException("File format error");
}
List<User> dataList = null;
try(InputStream inputStream = file. getInputStream()) {
// easyexcel imports files new UserReadListener: a listener written by ourselves
dataList = EasyExcel.read(inputStream, User.class, new UserReadListener()).sheet().doReadSync();
} catch (Exception e) {
log.error("Failed to import file: {}", e.getMessage(), e);
throw new ImportException("Failed to import file", e.getMessage());
}
log.info("{} pieces of data, start storing the database!", dataList.size());
// Batch adding method using mybatis plus
userService. saveBatch(dataList, BATCH_COUNT);
log.info("Database stored successfully!");
// return format for splicing
BaseResponse response = new BaseResponse();
response.setCode("200");
response.setMessage("Imported successfully");
return response;
}
}
UserReadListener
The point is in this listening class, if we need to verify data, we will do data verification in the invoke method
package com.example.demo.config;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import com.example.demo.model.entity.User;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
@Slf4j
@Component
public class UserReadListener<T extends Model> extends AnalysisEventListener<T> {
private static final int BATCH_COUNT = 5;
/**
* Custom is used to temporarily store data.
* This value can be obtained through the instance
*/
private final List<T> dataList = new ArrayList<>();
/**
* This method will be called once for each piece of data
* @param analysisContext can get information about the currently read table
*/
@Override
public void invoke(T object, AnalysisContext analysisContext) {
log.info("invoke---start verifying table data");
validChoiceInfo(object, analysisContext);
log.info("invoke---start parsing table data");
dataList. add(object);
// When BATCH_COUNT is reached, it is necessary to store the database once to prevent tens of thousands of data from being stored in memory, which is easy to OOM
/*if (dataList. size() >= BATCH_COUNT) {
saveData(); // persistence logic
// store cleanup list
dataList. clear();
}*/
}
private static<T> void validChoiceInfo(T object, AnalysisContext context) {
// generic for user
if(object instanceof User) {
// Generic conversion to entity type
Object temp = object;
User user = (User) temp;
if(StringUtils.isBlank(user.getUid())){
log.info("Upload failed: the ID information of line {} is empty", context.readRowHolder().getRowIndex());
throw new ImportException("Upload failed: No. " + context.readRowHolder().getRowIndex().toString() + "Row ID information is empty");
}
if(StringUtils.isBlank(user.getUserName())){
log.info(String.format("Upload failed: user name information on line {} is empty", context.readRowHolder().getRowIndex()));
throw new ImportException(String.format("Upload failed: the user name information on line {} is empty", context.readRowHolder().getRowIndex()));
}
if(StringUtils.isBlank(user.getPassword())){
log.info(String.format("Upload failed: the password information on line {} is empty", context.readRowHolder().getRowIndex()));
throw new ImportException(String.format("Upload failed: the password information on line {} is empty", context.readRowHolder().getRowIndex()));
}
}
}
/**
* All data analysis will be called
*/
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// Also save the data here to ensure that the last remaining data is also stored in the database
log.info("doAfterAllAnalysed---start processing");
if(dataList.isEmpty() || dataList.size() == 0) {
throw new ImportException("Upload failed, there is no data in Excel");
}
log.info("All data analysis completed!");
}
public List<T> getData() {
return dataList;
}
}
IUserService
package com.example.demo.service;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.example.demo.model.entity.User;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.demo.model.bo.UserDetailBO;
import com.example.demo.model.bo.UserLoginBO;
import com.example.demo.model.vo.GetUserVO;
import com.example.demo.response.BaseResponse;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
/**
* <p>
* Service class
*</p>
*
* @since 2023-03-16
*/
public interface IUserService extends IService<User> {
}
UserServiceImpl
package com.example.demo.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.config.BaseErrorEnum;
import com.example.demo.config.BaseException;
import com.example.demo.model.entity.User;
import com.example.demo.mapper.UserMapper;
import com.example.demo.model.bo.UserDetailBO;
import com.example.demo.model.bo.UserLoginBO;
import com.example.demo.model.vo.GetUserVO;
import com.example.demo.service.IUserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.demo.utils.RedisUtils;
import com.example.demo.utils.TokenUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.TimeUnit;
/**
* <p>
* Service implementation class
*</p>
* @since 2023-03-16
*/
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {
}
UserMapper
package com.example.demo.mapper;
import com.example.demo.model.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* <p>
* Mapper interface
*</p>
*
* @since 2023-03-16
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
postman test, select file
Imported successfully
