EasyExcel实现Excel的导入导出

前言：最近练习简单项目过程中使用Excel的导入导出较多，特来记录一下相关代码逻辑

 

准备工作

1. 首先在pom.xml文件中引入相关依赖

 

其他依赖若有需要可以引入

 		<!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!--mybatis-plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
        </dependency>

        <!--mybatis-plus 代码生成器-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
        </dependency>
        <!--mybatis-plus 代码模板生成器引擎-->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
        </dependency>

        <!--   junit     -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <scope>test</scope>
        </dependency>

        <!--阿里巴巴Easy Excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
        </dependency>

        <!-- xml -->
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.5</version>
        </dependency>


        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

 

 

2. 在controller层中创建一个AdminAttendanceDictController.java文件

相关代码如下

package com.xxx.student.core.controller.dict;

import com.alibaba.excel.EasyExcel;
import com.sun.deploy.net.URLEncoder;
import com.xxx.student.common.exception.BusinessException;
import com.xxx.student.common.result.R;
import com.xxx.student.common.result.ResponseEnum;
import com.xxx.student.core.pojo.dto.AttendanceExcelDTO;
import com.xxx.student.core.service.AttendanceService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;

/**
 * @author Li
 */
@Api(tags = "数据字典管理")
@RestController
@Slf4j
@CrossOrigin
@RequestMapping("/admin/core/dictAttendance")
public class AdminAttendanceDictController {

    @Resource
    private AttendanceService attendanceService;

    @ApiOperation("Excel日常考勤信息数据的导出")
    @GetMapping("/export")
    public void export(HttpServletResponse response) throws IOException {

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyExcel没有关系
        String fileName = URLEncoder
                .encode("日常考勤数据字典列表", "UTF-8").replaceAll("\\+", "%20");
        response
                .setHeader("Content-disposition", "attachment;filename*=utf-8''" +
                        fileName + ".xlsx");
        EasyExcel
                .write(response.getOutputStream(), AttendanceExcelDTO.class)
                .sheet("数据字典")
                .doWrite(attendanceService.listDictData());
    }

    @ApiOperation("Excel日常考勤信息数据的批量导入")
    @PostMapping("/import")
    public R batchImport(@ApiParam(value = "Excel数据字典文件", required = true)
                         @RequestParam("file") MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            attendanceService.importData(inputStream);
            return R.ok().message("数据字典批量导入成功");
        } catch (Exception e) {
            // 文件上传错误
            throw new BusinessException(ResponseEnum.UPLOAD_ERROR, e);
        }
    }

}

 

 

3.我们注意到AdminAttendanceExcelDto.java还未定义

所以我们需要新建一个AdminAttendanceExcelDto.java文件，代码如下

package com.xxx.student.core.pojo.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.xxx.student.core.util.LocalDateTimeConverter;
import lombok.Data;
import java.time.LocalDateTime;

/**
 * <p>
 * 
 * </p>
 *
 * @author lsh
 * @since 2021-07-25
 */
@Data
@ContentRowHeight(15) //设置行高
@HeadRowHeight(20)  //设置表头高度
@ColumnWidth(20)  //设置列宽
public class AttendanceExcelDTO {

    @ExcelProperty(index = 0,value = "学生学号")
    private Long studentNo;

    @ExcelProperty(index = 1,value = "考勤时间",converter = LocalDateTimeConverter.class)
    private LocalDateTime attendanceTime;

    @ExcelProperty(index = 2,value = "考勤地点")
    private String attendanceLocation;

    @ExcelProperty(index = 3,value = "宿舍号")
    private Integer dormitoryNo;


    @ExcelIgnore
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty(value = "创建时间")
    private LocalDateTime createTime;

    @ExcelIgnore
    @ExcelProperty(value = "更新时间")
    private LocalDateTime updateTime;
    @ExcelIgnore
    @ExcelProperty(value = "逻辑删除")
    private Boolean deleted;
}

 

4. 这里我们又使用到了一个用于转换LocalDateTime的一个工具类

package com.xxx.student.core.util;

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;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * 自定义转换器，转换LocalDateTime
 * @author Li
 */
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
    @Override
    public Class<LocalDateTime> supportJavaTypeKey() {
        return LocalDateTime.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
    @Override
    public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
                                           GlobalConfiguration globalConfiguration) {
        return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
    @Override
    public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
                                               GlobalConfiguration globalConfiguration) {
        return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }
}

 

5.我们还需要创建一个AttendanceExcelDictDTOListener.java作为监听器来帮助我们检测导入的情况

继承 AnalysisEventListener<AttendanceExcelDTO>，泛型为我们要实现的ExcelDTO对象

package com.xxx.student.core.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.util.CollectionUtils;
import com.xxx.student.core.mapper.AttendanceMapper;
import com.xxx.student.core.pojo.dto.AttendanceExcelDTO;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;

/**
 * @author Li
 */
@Slf4j
@NoArgsConstructor
public class AttendanceExcelDictDTOListener extends AnalysisEventListener<AttendanceExcelDTO> {
    // 数据列表
    ArrayList<AttendanceExcelDTO> list = new ArrayList<>();

    private AttendanceMapper attendanceMapper;

    // 每隔10条数据批量存入一次数据
    static final int BATCH_COUNT = 5;

    // 每次创建Listener的时候需要把spring管理的类传进来

    public AttendanceExcelDictDTOListener(AttendanceMapper attendanceMapper){
        this.attendanceMapper = attendanceMapper;
    }

    @Override
    public void invoke(AttendanceExcelDTO data, AnalysisContext context) {
        log.info("解析到一条记录:{}", data);
        // 将数据存入到数据列表
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if(list.size() >= BATCH_COUNT){
            // 调用mapper层的save方法
            savaData();
            // 存储完成清理 list
            list.clear();
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context .
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 当剩余的记录数不足BATCH_COUNT时，最终一次性存储数据
        savaData();
        log.info("所有数据解析完成");
    }

    private void savaData(){
        log.info("{} 条数据存储到数据库", list.size());

        // 调用mapper层的save方法 SAVE list对象
        if(!CollectionUtils.isEmpty(list)) {

            attendanceMapper.insertBatch(list);
        }

        //TODO
        log.info("{} 条数据被存储到数据库成功", list.size());
    }
}

 

 6.service层创建一个名为AttendanceService的接口文件部分代码如下

package com.xxx.student.core.service;

import com.xxx.student.core.pojo.dto.AttendanceExcelDTO;
import com.xxx.student.core.pojo.entity.Attendance;
import com.baomidou.mybatisplus.extension.service.IService;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  服务类
 * </p>
 *
 * @author lsh
 */
public interface AttendanceService extends IService<Attendance> {

    /**
     *
     * @return Excel导出
     */
    List<AttendanceExcelDTO> listDictData();

    /**
     *
     * @param inputStream Excel导入
     */
    void importData(InputStream inputStream);
}

 

7.ServiceImpl部分代码如下

@Slf4j
@Service
public class AttendanceServiceImpl extends ServiceImpl<AttendanceMapper, Attendance> implements AttendanceService {

    private final QueryWrapper<Attendance> attendanceQueryWrapper = new QueryWrapper<>();

    @Resource
    private AttendanceMapper attendanceMapper;

    @Override
    public List<AttendanceExcelDTO> listDictData() {
        List<Attendance> list = baseMapper.selectList(null);
        //创建ExcelDictDTO列表，将Dict列表转换成ExcelDictDTO列表
        ArrayList<AttendanceExcelDTO> excelDictDTOList = new ArrayList<>(list.size());
        // 遍历dict列表
        list.forEach(dict -> {
            AttendanceExcelDTO attendanceExcelDTO = new AttendanceExcelDTO();
            BeanUtils.copyProperties(dict, attendanceExcelDTO);
            excelDictDTOList.add(attendanceExcelDTO);
        });
        return excelDictDTOList;
    }

    @Override
    public void importData(InputStream inputStream) {
        EasyExcel
                .read(inputStream, AttendanceExcelDTO.class, new AttendanceExcelDictDTOListener(baseMapper))
                .excelType(ExcelTypeEnum.XLSX)
                .sheet()
                .doRead();
        log.info("Excel导入成功");
    }
}

 

8.mapper层

/**
     *
     * @param list 批量插入
     */
    void insertBatch(ArrayList<AttendanceExcelDTO> list);

 

9.mapper.xml文件

    <insert id="insertBatch">
        INSERT INTO `student_teacher_info`.`attendance`(`student_No`, `attendance_Time`, `attendance_Location`, `dormitory_No`)
        VALUES<foreach collection="list" separator="," item="item" index="index">(#{item.studentNo},
        #{item.attendanceTime},
        #{item.attendanceLocation},
        #{item.dormitoryNo})</foreach>
    </insert>

 

10.实体类Attendance

@Data
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="Attendance对象", description="日常考勤")
public class Attendance implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "学生学号")
    @TableId(value = "student_No", type = IdType.INPUT) // 用户输入
    private Long studentNo;

    @JsonFormat(shape = JsonFormat.Shape.STRING,pattern = "yyyy-MM-dd HH:mm:ss")
    @ApiModelProperty(value = " 考勤时间")
    @TableField("attendance_Time")
    private LocalDateTime attendanceTime;

    @ApiModelProperty(value = "考勤地点")
    @TableField("attendance_Location")
    private String attendanceLocation;

    @ApiModelProperty(value = "宿舍号")
    @TableField("dormitory_No")
    private Integer dormitoryNo;

    @ApiModelProperty(value = "创建时间",example = " ")
    private LocalDateTime createTime;

    @ApiModelProperty(value = "更新时间",example = " ")
    private LocalDateTime updateTime;

    @ApiModelProperty(value = "逻辑删除(1:已删除，0:未删除)")
    @TableField("is_deleted")
    @TableLogic
    private Boolean deleted;

}

 

至此：导入导出模块基本完成