EasyExcel+MapStruct 实现导入导出

关注

原创 2022-02-12 12:27:14 1k 阅读 AI 写同款· GEO 优化 ›

告别“大泥球”：我在 Spring Boot 单体架构中实践的模块化隔离本文探讨了在电信网络优化系统中采用单体模块化架构而非微服务的原因。作者指出，该系统的核心需求是逻辑闭环而非并发扩展，因此采用"物理隔离、逻辑内聚"的策略。具体做法包括：按业务域垂直切分代码结构、实体类反映业务概念而非数据库结构、强制接口隔离为未来扩展留余地、通用功能下沉到公共模块、通过主题隔离实现低成本差异化等。作者建议维护者严守模块边界、逐步重构前端、定期清理公共模块，并强调架构选择应服务于业务需求，而非盲目追求技术时髦。这种模块化单体架构在强逻辑关联的网优系统中展现出更高的性价比和可阅读详情

前置条件

了解mapStruct 可通过官网，或者我以前写的博客传送门：https://mapstruct.org/
若使用了 Lombok +MapStruct 注意版本问题

概览

使用MapStruct 可以减少导入导出 excel 与entity直接的不对应
使用 reflections 可以做到，枚举code -> 枚举名称的相互转换的问题。枚举管理类，实现code与name相互转换

更多看 gitee仓库：https://gitee.com/codingBug001/study-test.git

依赖

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.11</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>easyExcel+lombok</description>
    
    <properties>
        <java.version>11</java.version>
        <org.mapstruct.version>1.3.0.Final</org.mapstruct.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct</artifactId>
            <version>${org.mapstruct.version}</version>
        </dependency>

        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct-processor</artifactId>
            <version>${org.mapstruct.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
            <optional>true</optional>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.baomidou/mybatis-plus-boot-starter -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.7</version>
        </dependency>
        <dependency>
            <groupId>org.reflections</groupId>
            <artifactId>reflections</artifactId>
            <version>0.9.11</version>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.4.7</version>
        </dependency>

    </dependencies>

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

写操作

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;

    /**
     * 直属上级id
     */
    @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<UserExportVo> 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 cn.hutool.core.util.ObjectUtil;
import com.zx.easy.enums.EnumManager;
import com.zx.easy.enums.LanguagesEnum;
import com.zx.easy.pojo.dto.UserReadDto;
import com.zx.easy.pojo.entity.User;
import com.zx.easy.pojo.vo.UserExportVo;
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),
            @Mapping(target = "languages",expression = "java(getLanguagesEnum(entity))")
    })
    UserExportVo userToUserVo(User entity);
    
    List<UserExportVo> userListToVoList(List<User> 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<User> userVoListToUserList(List<UserReadDto> voList);
    
    
    default  String getLanguagesEnum(User entity){
        if (ObjectUtil.isNull(entity.getLanguages())){
            return "";
        }
        
       return EnumManager.getCodeName(LanguagesEnum.class,entity.getLanguages());
    }
}

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

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

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


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

    /**
     * 时间搓转换程日期格式
     * @param timeMillis
     * @return
     */
    @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;

/**
 * 模板的读取类
 *
 * @author Jiaju Zhuang
 */
@Slf4j
public class UploadDataListener extends AnalysisEventListener<UserReadDto> {
    
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<UserReadDto> list = new ArrayList<UserReadDto>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private UserService userService;



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

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(UserReadDto data, AnalysisContext context) {
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }



    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.error("{}条数据，开始存储数据库！", list.size());
        List<User> userList = Mappers.getMapper(MapStructConverter.class).userVoListToUserList(list);
        // List<User> 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;

    /**
     * 直属上级id
     */
    @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;

}