easyexcel

最新推荐文章于 2024-07-29 22:09:34 发布

李绍军4510

最新推荐文章于 2024-07-29 22:09:34 发布

阅读量66

点赞数

文章标签： java spring 开发语言

本文链接：https://blog.csdn.net/m0_63793462/article/details/130489362

版权

该代码示例展示了如何在SpringBoot应用中使用MyBatis-Plus和EasyExcel进行Excel模板下载以及数据的批量导入。服务层处理ExcelDto与实体类的转换，使用MetaObjectHandler自动填充数据创建时间，监听器UserImportDataListener负责分批存储导入数据到数据库。

摘要由CSDN通过智能技术生成

com.jun.execl.demos.service;

package com.jun.execl.demos.service;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jun.execl.demos.dto.UserExcelDto;
import com.jun.execl.demos.mapper.UserMapper;
import com.jun.execl.demos.pojo.User;
import org.ehcache.core.util.CollectionUtil;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;


import java.util.ArrayList;
import java.util.List;


@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;
    /**
     * excel模板下载
     * @return
     */
    @Override
    public List getImportUserData() {
        List<UserExcelDto> list = new ArrayList<>();
        UserExcelDto userExcelDto = new UserExcelDto();
        userExcelDto.setUserName("请输入名称");
        userExcelDto.setPassword("请输入密码");
//        userExcelDto.setAge("年龄");
//        userExcelDto.setSex("性别");
//        userExcelDto.setAddress("地址");
        userExcelDto.setEmail("邮件");
        userExcelDto.setPhone("电话");
        userExcelDto.setStatus(1);
        list.add(userExcelDto);
        return list;
    }

    /**
     * excel批量导入
     * @param list
     */
    @Override
    public void AddImportUserData(List<UserExcelDto> list) {
        //实体类Entity与dto的互相转换
        ModelMapper modelMapper = new ModelMapper();
        if (list.size() >0 && list != null){

            list.forEach(obj->{
                QueryWrapper<User> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("username",obj.getUserName());
                List<User> users = userMapper.selectList(queryWrapper);
                System.out.println(queryWrapper);
                queryWrapper.clear();
                if(CollectionUtils.isEmpty(users)){
                    User user = new User();
                    modelMapper.map(obj,user);
                    System.out.println(obj);
                    userMapper.insert(user);
                }
            });
        }
    }
    @Override
    public User query(Integer id) {
        User user = userMapper.selectById(id);
        return user;
    }




}

com.jun.execl.demos.config;

package com.jun.execl.demos.config;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    private Object Date;

    //优势: 以后操作数据表无需手动操作时间!!! 都会自动填充
    @Override
    public void insertFill(MetaObject metaObject) {
        Date date = new Date();
        this.setFieldValByName("created",date,metaObject);
        this.setFieldValByName("updated",date,metaObject);


    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updated",new Date(),metaObject);
    }
}

com.jun.execl.demos.controller;

package com.jun.execl.demos.controller;



import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;

import com.jun.execl.demos.dto.Result;
import com.jun.execl.demos.dto.UserExcelDto;
import com.jun.execl.demos.excelListener.UserImportDataListener;
import com.jun.execl.demos.pojo.User;
import com.jun.execl.demos.service.UserService;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;


import javax.servlet.http.HttpServletResponse;
import javax.xml.ws.Response;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

@RestController
@RequestMapping("/test/download")
public class DownloadController {

    @Autowired
    private UserService userService;

    /**
     * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     *
     * @since 2.1.1
     */
    @GetMapping("/downloadImport")
    public void downloadImportGoods(HttpServletResponse response) throws IOException {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系  第一个参数是设置excel导出后名字
            String fileName = URLEncoder.encode("导入用户上传模板", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流                                                                                         这里是excel左下角名字
            EasyExcel.write(response.getOutputStream(), UserExcelDto.class).autoCloseStream(Boolean.FALSE).sheet("导入用户上传模板")
                    .doWrite(userService.getImportUserData());
        } catch (Exception e) {
            // 重置response
            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));
        }
    }

    /***
     * 批量导入商品
     * @param file
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "/batchImportGoods", method = RequestMethod.POST)
    public Result batchImportGoods(@RequestPart("file") MultipartFile file) throws Exception {

        EasyExcel.read(file.getInputStream(), UserExcelDto.class, new UserImportDataListener(userService)).sheet().doRead();
        // Map resultInfo = redisCache.getCacheObject("importGoodsResult");
        // redisCache.deleteObject("importGoodsResult");
        //
         return Result.success(null);
        //return Response.class.newInstance();
    }
    //查询测试
    @GetMapping("/query")
    public Result query(Integer id){
        User user = userService.query(id);
        System.out.println(user);
        return Result.success(user);
    }
}

com.jun.execl.demos.excelListener;

package com.jun.execl.demos.excelListener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jun.execl.demos.dto.UserExcelDto;
import com.jun.execl.demos.service.UserService;


import java.util.ArrayList;
import java.util.List;
//监听器
public class UserImportDataListener extends AnalysisEventListener<UserExcelDto> {


    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 3000;
    /**
     * 缓存的数据
     */
    private List<UserExcelDto> list = new ArrayList<>(BATCH_COUNT);

    private UserService userService;
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param userService
     */
    public UserImportDataListener(UserService userService) {
        this.userService = userService;
    }

    @Override
    public void invoke(UserExcelDto data, AnalysisContext analysisContext) {
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {

            saveData();
            // 存储完成清理 list
            list = new ArrayList<>(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        //调用service接口方法存储数据
        userService.AddImportUserData(list);
    }
}

com.jun.execl.demos.service;

package com.jun.execl.demos.service;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.jun.execl.demos.dto.UserExcelDto;
import com.jun.execl.demos.mapper.UserMapper;
import com.jun.execl.demos.pojo.User;
import org.ehcache.core.util.CollectionUtil;
import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;


import java.util.ArrayList;
import java.util.List;


@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;
    /**
     * excel模板下载
     * @return
     */
    @Override
    public List getImportUserData() {
        List<UserExcelDto> list = new ArrayList<>();
        UserExcelDto userExcelDto = new UserExcelDto();
        userExcelDto.setUserName("请输入名称");
        userExcelDto.setPassword("请输入密码");
//        userExcelDto.setAge("年龄");
//        userExcelDto.setSex("性别");
//        userExcelDto.setAddress("地址");
        userExcelDto.setEmail("邮件");
        userExcelDto.setPhone("电话");
        userExcelDto.setStatus(1);
        list.add(userExcelDto);
        return list;
    }

    /**
     * excel批量导入
     * @param list
     */
    @Override
    public void AddImportUserData(List<UserExcelDto> list) {
        //实体类Entity与dto的互相转换
        ModelMapper modelMapper = new ModelMapper();
        if (list.size() >0 && list != null){

            list.forEach(obj->{
                QueryWrapper<User> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("username",obj.getUserName());
                List<User> users = userMapper.selectList(queryWrapper);
                System.out.println(queryWrapper);
                queryWrapper.clear();
                if(CollectionUtils.isEmpty(users)){
                    User user = new User();
                    modelMapper.map(obj,user);
                    System.out.println(obj);
                    userMapper.insert(user);
                }
            });
        }
    }
    @Override
    public User query(Integer id) {
        User user = userMapper.selectById(id);
        return user;
    }




}