使用Hutool工具实现数据导入和导出

使用Hutool工具实现数据导入和导出

1.所需依赖

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-core</artifactId>
    <version>5.6.3</version>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-poi</artifactId>
    <version>4.6.17</version>
</dependency>

2.利用jvm反射机制实现数据导入(这里只适合简单表头, 复杂多级表头可能不适用)

2.1 首先根据导入Excel的表头字段新建一个实体类(这里偷懒,导入导出就共用一个实体类了)

@Data
public class ImportUserBaseEntity implements Serializable{

    @ApiModelProperty(value = "用户ID")
    private String userId;

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

    @ApiModelProperty(value = "昵称")
    private String nikeName;

    @ApiModelProperty(value = "头像")
    private String headPic;

    @ApiModelProperty(value = "年龄")
    private Integer age;
    
    @ApiModelProperty(value = "地址")
    private String address;

    @ApiModelProperty(value = "爱好")
    private String hobby;

}

2.2 解析Excel表格数据

@Override
public CommonResult importExcel(MultipartFile file) throws IOException {
    List<UserBaseEntity> dataList = new ArrayList<>();
    if(file.isEmpty()){
        return CommonResult.fail(ReturnCode.FILE_NOT_EXIST);
    }
    InputStream inputStream = file.getInputStream();
    //调用工具解析Excel数据
    List<ImportUserBaseEntity> entityList = HuToolExcelUtils.importExcelByStream(inputStream, ImportUserBaseEntity.class);
    if(!CollectionUtils.isEmpty(entityList)){
        for (ImportUserBaseEntity entity : entityList) {
            UserBaseEntity userBase = new UserBaseEntity();
            BeanUtils.copyProperties(entity, userBase);
            dataList.add(userBase);
        }
    }
    if(!CollectionUtils.isEmpty(dataList)){
        this.saveOrUpdateBatch(dataList);
    }
    return CommonResult.success();
}

3.3 封装导入方法（这里是自己封装的导入方法, 传入的文件可以是文件路径, 文件流的方式, 这里用的是文件流的形式）

/**
 * 根据对象字段解析Excel数据
 *
 * @param inputStream Excel文件流数据
 * @param tClass 解析数据封装对象
 * @return
 */
public static <T> List<T> importExcelByStream(InputStream inputStream, Class<T> tClass){
    List<T> readList = new ArrayList<>();
    Field[] declaredFields = tClass.getDeclaredFields();
    Map<String, String> headerAlias = new LinkedHashMap<>();
    for (Field field : declaredFields) {
        ApiModelProperty annotation = field.getAnnotation(ApiModelProperty.class);
        if (annotation != null ){
            //这里key值对应的是表头中文名, value值对应封装实体类的字段名
            headerAlias.put(annotation.value(),field.getName());
        }
    }
    ExcelReader reader = ExcelUtil.getReader(inputStream);
    if ( ! headerAlias.isEmpty()) {
        reader.setHeaderAlias(headerAlias);
    }
    readList = reader.read(2, 3, tClass);

    return readList;
}

3.导出实现

3.1 创造需要导出的数据集合(这里只是随手创建的示例数据)

@Override
public void exportUserBaseList() {
	List<ImportUserBaseEntity> dataList = new ArrayList<>();
	ImportUserBaseEntity userBase = new ImportUserBaseEntity();
	userBase.setUserId("USERID123456789");
	userBase.setName("赵一");
	userBase.setNikeName("阳光大男孩");
	userBase.setHeadPic("https://image.baidu.com/search/detail?ct=123456xxxx");
	userBase.setAge(21);
	userBase.setAddress("广州市天河区xxx街道xx号");
	userBase.setHobby("唱、跳、rap、篮球");

	ImportUserBaseEntity userBase2 = new ImportUserBaseEntity();
	userBase2.setUserId("USERID987654321");
	userBase2.setName("钱二");
	userBase2.setNikeName("无敌大肥宅");
	userBase2.setHeadPic("https://image.baidu.com/search/detail?ct=654321xxxx");
	userBase2.setAge(23);
	userBase2.setAddress("广州市海珠区xxx街道xx号");
	userBase2.setHobby("小说、漫画、动漫、电影");

	ImportUserBaseEntity userBase3 = new ImportUserBaseEntity();
	userBase3.setUserId("USERID132457689");
	userBase3.setName("孙三");
	userBase3.setNikeName("清风揽月");
	userBase3.setHeadPic("https://image.baidu.com/search/detail?ct=132456xxxx");
	userBase3.setAge(24);
	userBase3.setAddress("广州市黄埔xxx街道xx号");
	userBase3.setHobby("健身、游泳、画画");

	dataList.add(userBase);
	dataList.add(userBase2);
	dataList.add(userBase3);
    
    //调用HutoolExcelUtils导出方法
    HuToolExcelUtils.list2xlsx(dataList, "用户基本信息数据导出");
    
}

3.2 封装导出方法

/**
 * <p>
 * 将数据生成Excel（xlsx格式）直接输出到response.getOutputStream()流
 * Excel表头会根据泛型中每个字段的Swagger注解@ApiModelProperty的value生成对应的中文
 *      1) 如果某字段没有标注@ApiModelProperty,则该字段不会被写入Excel
 *      2) 如果泛型中没有任何字段标注@ApiModelProperty,则默认全部字段导出,表头为字段名
 *  注意事项：
 *      hutool-poi:4.6.17 在这个版本中发现LocalDateTime类型的导出后格式为：-> 2023-04-28T16:06:00
 *      单元格格式为常规，所以日期类型的字段建议使用{@link java.util.Date},这样单元格格式是日期
 *      不知道新版hutool-poi中是否存在此问题？<br>
 * </p>
 * @param data      被导出的Excel数据
 * @param fileName  文件名(请不要带后缀)
 * @param <T>
 * @return          导出结果 成功=true;失败=false;
 */
public static  <T> boolean list2xlsx(List<T> data,String fileName){
    if (data == null || data.isEmpty()){
        log.info("Excel导出数据为空");
        return false;
    }
    T t = data.get(0);
    if (t == null){
        log.info("Excel导出数据异常,index(0) = null");
        return false;
    }
    Class<?> aClass = t.getClass();
    Field[] declaredFields = aClass.getDeclaredFields();
    Map<String, String> headerAlias = new LinkedHashMap<>();
    for (Field field : declaredFields) {
        ApiModelProperty annotation = field.getAnnotation(ApiModelProperty.class);
        if (annotation != null ){
            headerAlias.put(field.getName(),annotation.value());
        }
    }
    ExcelWriter excelWriter = ExcelUtil.getWriter(true);
    if ( ! headerAlias.isEmpty()) {
        excelWriter.setHeaderAlias(headerAlias);
        excelWriter.setOnlyAlias(true);
    }
    excelWriter.write(data, true);
    try {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = Objects.requireNonNull(servletRequestAttributes).getResponse();
        Objects.requireNonNull(response).setHeader(HttpHeaders.CONTENT_DISPOSITION, URLUtil.encode("attachment;fileName="+fileName+".xlsx"));
        excelWriter.flush(response.getOutputStream(), true);
        return true;
    } catch (IOException e) {
        log.error("excel导出失败 inputParams:{} and errorMessage:{}",data,e.getMessage(),e);
    }finally {
        excelWriter.close();
    }
    return false;
}

此文章仅供参考!!! 感谢阅览!!!