JAVA后端实现Excel文件导入导出数据库

JAVA后端实现Excel文件导入导出数据库

添加EasyExcel依赖

// gradle添加依赖
// Excel文件读写数据库 依赖包
implementation 'com.alibaba:easyexcel:2.2.6'

写Entity

/**
 *
 * @author 曳戈泰尔
 * @ExcelProperty 映射Excel字段
 * @since 2021-09-04
 */
@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="User对象", description="")
public class User implements Serializable {

    private static final long serialVersionUID=1L;

    @ExcelProperty(index = 0, value = "id")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ExcelProperty(index = 1, value = "用户名")
    @ApiModelProperty(value = "用户名")
    private String name;

    @ExcelProperty(index = 2, value = "用户密码")
    @ApiModelProperty(value = "用户密码")
    private String password;

    @ExcelProperty(index = 3, value = "性别")
    @ApiModelProperty(value = "性别")
    private boolean sex;

    @ExcelProperty(index = 4, value = "年龄")
    @ApiModelProperty(value = "年龄")
    private Integer age;
}

写mapper

通过mybatis-plus生成的UserMapper，
满足基本的增删查改操作。

写service方法

/**
 * @author 曳戈泰尔
 * @date 2021-09-13
 * @describe Excel文件数据上传数据表user，数据表user数据下载到Excel文件
 */
@Slf4j
@Service
@Transactional(rollbackFor = Exception.class)
public class UserExcelService extends AnalysisEventListener<User> {

    // 间隔BATCH_COUNT条存储数据库
    private static final int BATCH_COUNT = 100;
    List<User> list = new ArrayList<User>();
    // 上传Excel文件写数据表 变量
    private UserMapper userMapper;

    // 下载Excel文件
    @Autowired
    private UserMapper userDownExcelMapper;

    // 构造函数：spring中传入mapper
    public UserExcelService(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    // 解析数据调用
    @Override
    public void invoke(User data, AnalysisContext context) {
        list.add(data);
        // 达到BATCH_COUNT，存储一次数据库
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 清理list
            list.clear();
        }
    }

    // 所有数据解析完，调用
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 确保遗留数据存储到数据库
        saveData();
    }

    // 数据存储数据库
    private void saveData() {
        for(User user : list){
            userMapper.insert(user);
        }
    }


    // 下载数据表对应的Excel文件
    public void downExcel(HttpServletResponse response) throws IOException {
        try{
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("user", "UTF-8").replace("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream(), User.class).autoCloseStream(Boolean.FALSE).sheet("sheet1")
                    .doWrite(userDownExcelMapper.selectList(null));
        }catch (Exception e){
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
}

controller层方法

@Autowired
UserMapper userMapper;
@Autowired
UserExcelService userExcelService;

/**
 *
 * @author 曳戈泰尔
 * @param response
 * @return Excel文件流
 * @throws IOException
 * @describe 下载数据表对应的Excel文件
 * @date 2021-09-14
 */
@GetMapping("/download/user")
@ApiOperation("获取数据表user对应的Excel文件")
public void downloadUser(HttpServletResponse response) throws IOException {
    userExcelService.downExcel(response);
}

    /**
 * @author 曳戈泰尔
 * @param file
 * @return success / fail
 * @throws IOException
 * @describe 前端上传Excel文件，处理写入数据库相应的数据表
 * @date 2021-09-13
 */
@PostMapping(value = "/upload/user")
@ApiOperation("上传user的Excel文件写入数据表")
public JsonResult uploadUserFile(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), User.class, new UserExcelService(userMapper)).sheet().doRead();
    return new JsonResult("1");
}

JsonResult

public class JsonResult {

    // 响应业务状态
    private Integer code;

    // 响应消息
    private String message;

    // 响应中的数据
    private Object data;

    public JsonResult(Object data) {
        this.code = 200;
        this.message = "success";
        this.data = data;
    }
}