Springboot整合easyexcel进行excel文件的导入和导出

①.

已于 2023-12-05 16:34:44 修改

阅读量554

点赞数 8

文章标签： excel java

于 2023-12-05 16:33:20 首次发布

本文链接：https://blog.csdn.net/weixin_48160141/article/details/134810441

版权

1.创建User实体类

package com.lhy.pojo;

import java.io.Serializable;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * <p>
 * 用户信息
 * </p>
 *
 * @author lhy
 * @since 2023-11-28
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(value="User对象", description="用户表")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "主键id")
    private Long id;

    @ApiModelProperty(value = "姓名")
    private String name;

    @ApiModelProperty(value = "性别")
    private Integer sex;

    @ApiModelProperty(value = "年龄")
    private Integer age;

    @ApiModelProperty(value = "启用状态")
    private String state;

}

2.创建UserExcel实体类

package com.lhy.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import com.lhy.common.listen.GenderConverter;
import lombok.Data;

@Data
public class UserExcel {
    /**
     * index指定在excel中列数
     */
    @ExcelProperty(value = "序号" ,index = 0)
    private Long id;

    @ExcelProperty(value = "姓名" ,index = 1)
    private String name;

    @ExcelProperty(value = "性别",index = 2,converter = GenderConverter.class)
    private Integer sex;

    @ExcelProperty(value = "年龄" ,index = 3)
    private int age;

}

3.创建字段属性转换器

package com.lhy.common.listen;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

public class GenderConverter implements Converter<Integer> {
    private static final String MAN = "男";
    private static final String WOMAN = "女";
    @Override
    public Class supportJavaTypeKey() {
        // 实体类中对象属性类型
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        // Excel中对应的CellData属性类型
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 从Excel中读取数据
        String gender = cellData.getStringValue();
        // 判断Excel中的值，将其转换为预期的数值
        if(MAN.equals(gender)){
            return 0;
        } else if (WOMAN.equals(gender)) {
            return 1;
        }
        return null;
    }

    @Override
    public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 判断实体类中获取的值，转换为Excel预期的值，并封装为CellData对象
        if(integer == null){
            return new CellData("");
        } else if(integer == 0){
            return new CellData(MAN);
        } else if(integer == 1){
            return new CellData(WOMAN);
        }
        return new CellData("");
    }
}

4.创建监听器

导入的时候读取excel需要自定义监听器，可以进行处理，或者存储到数据库，导出时不需要

package com.lhy.common.listen;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.lhy.pojo.User;
import com.lhy.pojo.UserExcel;
import com.lhy.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

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

@Component
@Slf4j
public class ExcelListener extends AnalysisEventListener<UserExcel> {
    /**
     * 每隔2条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 2;

    //创建list集合封装最终的数据
    List<User> userList = new ArrayList();

    private static UserService userService;

    @Autowired
    public void setUserService(UserService userService){
        this.userService = userService;
    }

    @Override
    public void invoke(UserExcel userExcel, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(userExcel));
        User user = new User();
        user.setState("Y");
        BeanUtils.copyProperties(userExcel,user);
        userList.add(user);
        if(userList.size() > BATCH_COUNT){
            userService.insertBach(userList);
            log.info("用户信息存储数据库成功");
            userList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("解析完所有数据");
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头信息："+headMap);
    }
    //一行一行去读取excle内容

}

此处要注意，普通方法上面的注解@Component 和 @Autowired 有时候无法注入service层的对象，会显示null。可以采用注入构造方法的方式解决该问题。

5.代码

此处列出我的service层和controller层的代码，主要就是实现导入和导出

package com.lhy.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.lhy.common.exception.MyException;
import com.lhy.common.listen.ExcelListener;
import com.lhy.common.listen.SubjectListener;
import com.lhy.common.listen.UserListener;
import com.lhy.common.result.Result;
import com.lhy.common.result.ResultCodeEnum;
import com.lhy.pojo.Student;
import com.lhy.pojo.User;
import com.lhy.mapper.UserMapper;
import com.lhy.pojo.UserExcel;
import com.lhy.service.UserService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 用户信息 服务实现类
 * </p>
 *
 * @author lhy
 * @since 2023-11-28
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Override
    public Boolean insertBach(List<User> users) {
        return this.saveBatch(users);
    }

    @Override
    public Result upload(MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
//            String filename = "C:\\Users\\lhy06\\Desktop\\用户信息.xlsx";
            EasyExcel.read(inputStream, UserExcel.class, new ExcelListener()).sheet("用户信息").doRead();
            inputStream.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return Result.ok();
    }

    @Override
    public void export(HttpServletResponse response,List<User> users) {
        // 设置下载信息
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // URLEncoder.encode可以防止中文乱码
        String fileName= null;
        try {
            fileName = URLEncoder.encode("用户信息","UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");

        //将User映射为导出的实体类
        List<UserExcel> userExcelList=new ArrayList<>();
        for (User user : users) {
            UserExcel userExcel = new UserExcel();
            BeanUtils.copyProperties(user,userExcel);
            userExcelList.add(userExcel);
        }

        //调用方法进行写操作
        try {
            EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("用户信息").doWrite(userExcelList);
        } catch (IOException e) {
            throw new MyException(ResultCodeEnum.DATA_ERROR);
        }
    }

}

package com.lhy.controller;


import com.alibaba.excel.EasyExcel;
import com.lhy.common.result.Result;
import com.lhy.pojo.User;
import com.lhy.pojo.UserExcel;
import com.lhy.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>
 * 用户信息 前端控制器
 * </p>
 *
 * @author lhy
 * @since 2023-11-28
 */
@RestController
@RequestMapping("//user")
public class UserController {
    @Autowired
    private UserService userService;

    /**
     *
     * 读取excel文件并存储到数据库
     */
    @PostMapping("/upload")
    public Result upload(MultipartFile file) {
        return userService.upload(file);
    }


    /**
     * 下载用户表excel模板
     */
    @GetMapping("/downLoadExcelTemplate")
    public void downLoadTemplate(HttpServletResponse response) throws IOException {
        ArrayList<User> users = new ArrayList<>();
        users.add(new User(1L, "lhy", 0, 23, "Y"));
        userService.export(response, users);
    }

    /**
     * 导出用户表所有信息
     */
    @GetMapping("/exportAll")
    public void exportAll(HttpServletResponse response) throws IOException {
        List<User> users = userService.list();
        userService.export(response, users);
    }

    /**
     * 导出指定用户信息
     */
    @GetMapping("/downLoadSelected")
    public void downLoadSelected(HttpServletResponse response, List<User> users) throws IOException {
        userService.export(response, users);
    }

}

导出的时候可以下载到本地，也可以根据浏览器的响应进行下载。