前置条件
了解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;
}
当然若,读取的内容,和实体没有区别,可以直接用entity类当做dto传入。然后就没有必要转来转去了。