EasyExcel+MapStruct 实现导入导出

前置条件

了解mapStruct 可通过官网，或者我以前写的博客传送门：

依赖


        org.springframework.boot
        spring-boot-starter-parent
        2.4.11
         
    
    com.example
    demo
    0.0.1-SNAPSHOT
    demo
    easyExcel+lombok
    
        11
        1.3.0.Final
    
    
        
            org.springframework.boot
            spring-boot-starter
        
        
            org.springframework.boot
            spring-boot-starter-web
        
        
            org.springframework.boot
            spring-boot-starter-test
            test
        
        
            org.mapstruct
            mapstruct
            ${org.mapstruct.version}
        

        
            org.mapstruct
            mapstruct-processor
            ${org.mapstruct.version}
        
        
            org.projectlombok
            lombok
            1.18.10
            true
        

        
        
            com.baomidou
            mybatis-plus-boot-starter
            3.4.0
        
        
            mysql
            mysql-connector-java
            8.0.22
            runtime
        

        
            com.alibaba
            easyexcel
            2.2.7
        

        
            cn.hutool
            hutool-all
            5.4.7

都是常用的依赖，没有什么好介绍的

写操作

vo对象

@Data
public class UserExportVo {

    @ColumnWidth(10)
    private String id;
    
    @ExcelProperty(value = "姓名")
    private String name;

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

    
    @ExcelProperty(value = "邮箱")
    @ColumnWidth(10)
    private String email;

    
    @ExcelProperty(value = "直属上级id")
    private String managerId;

    
    @ExcelProperty(value = "创建时间")
    private Date createTime;

    
    @ExcelProperty(value = "修改时间")
    private Date updateTime;

    
    @ExcelProperty(value = "时间戳")
    @DateTimeFormat("yyyy:MM:dd HH:mm:ss")
    private String timestamp;
}

** controller层**

 @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        List voList = userService.testMapStruct();

        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.ms-excel");
        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");
        // 设置样式
        WriteCellStyle headStyle = StyleUtils.getHeadStyle();
        WriteCellStyle contentStyle = StyleUtils.getContentStyle();
        HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);

        EasyExcel.write(response.getOutputStream(), UserExportVo.class).sheet("模板").registerWriteHandler(styleStrategy).doWrite(voList);
    }

设置表头以及单元格样式

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;

public class StyleUtils {

    public static WriteCellStyle getHeadStyle(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("黑体");//设置字体名字
        headWriteFont.setFontHeightInPoints((short)12);//设置字体大小
        headWriteFont.setBold(true);//字体加粗
        headWriteCellStyle.setWriteFont(headWriteFont); //在样式用应用设置的字体;

        // 样式
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        headWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        headWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        headWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        headWriteCellStyle.setTopBorderColor((short) 0); //设置顶边框颜色;


        headWriteCellStyle.setWrapped(true);  //设置自动换行;


        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);  //设置垂直对齐的样式为居中对齐;


        //        headWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适

        return headWriteCellStyle;
    }

    public static WriteCellStyle getContentStyle(){
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

        // 背景绿色
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);

        // 设置字体
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 9);//设置字体大小
        contentWriteFont.setFontName("宋体"); //设置字体名字
        contentWriteCellStyle.setWriteFont(contentWriteFont);//在样式用应用设置的字体;

        //设置样式;
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置底边框;
        contentWriteCellStyle.setBottomBorderColor((short) 0);//设置底边框颜色;
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);  //设置左边框;
        contentWriteCellStyle.setLeftBorderColor((short) 0);//设置左边框颜色;
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);//设置右边框;
        contentWriteCellStyle.setRightBorderColor((short) 0);//设置右边框颜色;
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);//设置顶边框;
        contentWriteCellStyle.setTopBorderColor((short) 0); ///设置顶边框颜色;

        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 水平居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        contentWriteCellStyle.setWrapped(true); //设置自动换行;

        contentWriteCellStyle.setDataFormat((short)49);//设置单元格格式是：文本格式，方式长数字文本科学计数法

        // contentWriteCellStyle.setShrinkToFit(true);//设置文本收缩至合适

        return contentWriteCellStyle;
    }

}

mapStruct 的转换

** 定义接口**

import org.mapstruct.Mapper;
import org.mapstruct.Mapping;
import org.mapstruct.Mappings;

import java.util.List;

@Mapper(componentModel = "spring",uses = {StructHelper.class})
public interface MapStructConverter {


    @Mappings({
            @Mapping(source = "timestamp",target = "timestamp",qualifiedByName = StructConstant.TIME_MILLIS_TO_DATE_STR)
    })
    UserExportVo userToUserVo(User entity);

    List userListToVoList(List userList);
    @Mappings({
            @Mapping(source = "timestamp",target = "timestamp",qualifiedByName = StructConstant.DATE_STR_TO_TIME_MILLIS),
            @Mapping(source = "createTime",target="createTime",qualifiedByName=StructConstant.DATE_STR_TO_LOCAL_DATE),
            @Mapping(source = "updateTime",target="updateTime",qualifiedByName=StructConstant.DATE_STR_TO_LOCAL_DATE)
    })
    User userVoToUserEntity(UserReadDto vo );

    List userVoListToUserList(List voList);
}

注意点
1-@Mapper注解是MapStruct的而不是MP的
2-虽然把这个注解交个了Spring管理。但是有的时候你使用的类，没有被Spring管理，此时 @Autowired 是没有的，为空可使用以下代码解决。

Mappers.getMapper(MapStructConverter.class).userVoListToUserList(list);

一句话：此转换器和使用此转换器都被spring管理，可直接注入。否则使用上面代码。
转换工具类StructHelper

@Component
@Named(value = "StructHelper")
public class StructHelper {

    
    @Named(value = StructConstant.TIME_MILLIS_TO_DATE_STR)
    public static String dateStrToTimeMillis(Long timeMillis) {

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return format.format(timeMillis);
    }


    @Named(value = StructConstant.DATE_STR_TO_TIME_MILLIS)
    public static Long timeMillisToDateStr(String dateStr) {
        long time1 = DateUtil.parseDateTime(dateStr).getTime();
        return time1;
    }

    @Named(value=StructConstant.DATE_STR_TO_LOCAL_DATE)
    public static LocalDateTime dateStrToLocalDateTime(String dateStr) {
        LocalDateTime localDateTime = DateUtil.parseLocalDateTime(dateStr);
        return localDateTime;
    }




}

为了方便点定义了常量** StructConstant**，

public class StructConstant {
    public static final String TIME_MILLIS_TO_DATE_STR ="timeMillisToDateStr";
    public static final String DATE_STR_TO_TIME_MILLIS ="dateStrToTimeMillis";

    public static final String DATE_STR_TO_LOCAL_DATE ="dateStrToLocalDateTime";
}

当然你若嫌麻烦，也可以不定义。使用 expression 以及 qualifiedByName也是一样的效果

    @Mappings({
            @Mapping(source = "timestamp",target = "timestamp",qualifiedByName = StructConstant.DATE_STR_TO_TIME_MILLIS),
            @Mapping(source = "createTime",target="createTime",qualifiedByName=StructConstant.DATE_STR_TO_LOCAL_DATE),
            // @Mapping(source = "updateTime",target="updateTime",qualifiedByName=StructConstant.DATE_STR_TO_LOCAL_DATE)
            @Mapping(source = "updateTime",target="updateTime",expression = "java()")
    })
    User userVoToUserEntity(UserReadDto vo );

导出效果

读操作

    @PostMapping("upload")
    public String upload(@RequestBody MultipartFile file) throws IOException {
       return userService.upload(file);
    }
    // service
    @Override
    public String upload(MultipartFile file) throws IOException {

        EasyExcel.read(file.getInputStream(), UserReadDto.class, new UploadDataListener(this)).sheet().doRead();
        return "success";

    }

UploadDataListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.demo.dto.UserReadDto;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import com.example.demo.struct.MapStructConverter;
import lombok.extern.slf4j.Slf4j;
import org.mapstruct.factory.Mappers;

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


@Slf4j
public class UploadDataListener extends AnalysisEventListener {
    
    
    private static final int BATCH_COUNT = 5;
    List list = new ArrayList();
    
    private UserService userService;



    
    public UploadDataListener(UserService userService) {
        this.userService = userService;
    }

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



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

    
    private void saveData() {
        log.error("{}条数据，开始存储数据库！", list.size());
        List userList = Mappers.getMapper(MapStructConverter.class).userVoListToUserList(list);
        // List userList = mapStructConver.userVoListToUserList(list);
        userService.saveBatch(userList);
        log.info("存储数据库成功！");
    }
}

** dto对象**

@Data
public class UserReadDto {

    @ColumnWidth(10)
    private String id;
    
    @ExcelProperty(value = "姓名")
    private String name;

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

    
    @ExcelProperty(value = "邮箱")
    @ColumnWidth(10)
    private String email;

    
    @ExcelProperty(value = "直属上级id")
    private String managerId;

    
    @ExcelProperty(value = "创建时间")
    private String createTime;

    
    @ExcelProperty(value = "修改时间")
    private String updateTime;

    
    @ExcelProperty(value = "时间戳")
    @DateTimeFormat("yyyy:MM:dd HH:mm:ss")
    private String timestamp;

}

当然若，读取的内容，和实体没有区别，可以直接用entity类当做dto传入。然后就没有必要转来转去了。

EasyExcel+MapStruct 实现导入导出

Java相关栏目本月热门文章