使用Hutool对表格进行带别名的写入
目录
1.创建别名的注解类@ExcelAlias
package com.hsh.excelutil.Anno;
import java.lang.annotation.*;
/**
* @Author hsh
* @DateTime 2023/09/05 17:15
**/
@Target({ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelAlias {
String value() default "";
}
2.创建实体类Student,其中name字段设置别名为“名字”
package com.hsh.excelutil.pojo;
import com.hsh.excelutil.Anno.ExcelAlias;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @Author hsh
* @DateTime 2023/09/02 20:51
**/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
@ExcelAlias(value = "名字")
private String name;
@ExcelAlias(value = "年龄")
private Integer age;
}
3.创建工具类HuToolExcelUtil,其中有三个方法:redaExcel(),writeExcel(),getHeaderAlias()
package com.hsh.excelutil.util;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.hsh.excelutil.Anno.ExcelAlias;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.ReflectPermission;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Author hsh
* @DateTime 2023/09/09 14:56
**/
public class HuToolExcelUtil {
/**
* 读取Excel并转化为list
* 需要指定返回时元素的类
*
* @param path 需要读取的Excel 的路径aa
* @param beanType 指定的类,
* @return list
*/
public static <T> List<T> redaExcel(String path, Class<T> beanType) {
File file = new File(path);
ExcelReader reader = ExcelUtil.getReader(file);
reader.setHeaderAlias(getHeaderAlias(beanType, true));
List<T> list = reader.readAll(beanType);
reader.close();
return list;
}
/**
* 将list写入Excel中
*
* @param path 需要写入的Excel 的路径
* @param list 写入的内容
* @return
*/
public static <T> void writeExcel(String path, List<T> list, Class<T> beanType) {
Map headerAlias = getHeaderAlias(beanType, false);
try (OutputStream out = new FileOutputStream(path); ExcelWriter writer = ExcelUtil.getWriter()) {
writer.setHeaderAlias(headerAlias);
// 写入并刷盘
writer.write(list)
.flush(out);
} catch (FileNotFoundException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 获得一个类所有 有别名的 字段 和 别名
*
* @param beanType 类
* @param isRead 每个键值对格式为 ("已有的","想改的")
* true 读取时 每个键值对格式为:(“别名”, “字段名”), 从excel中读取到“已有的(别名)”, 改为“想改的(字段名)”
* false 写入时 每个键值对格式为:(“字段名”, “别名”), 每个字段对应的“已有的(字段名)”, 改为“想改的(别名)”
* @param <T>
* @return
*/
static <T> Map<String, String> getHeaderAlias(Class<T> beanType, boolean isRead) {
Map headerAlias = new HashMap<String, String>();
// Hutool 获取字段集合的方法,无需try/catch
List<Field> fields = Arrays.asList(ReflectUtil.getFields(beanType));
if (fields.isEmpty()) {
return headerAlias;
}
for (Field field : fields) {
// 获得每个字段的 @ExcelAlias注解
ExcelAlias anno = field.getAnnotation(ExcelAlias.class);
if (anno == null || "".equals(anno.value())) {
continue;
}
if (isRead) {
// 读取时 每个键值对格式为:(“别名”, “字段名”)
headerAlias.put(anno.value(), field.getName());
} else {
// 写入时 每个键值对格式为:(“字段名”, “别名”)
headerAlias.put(field.getName(), anno.value());
}
}
return headerAlias;
}
}
编写controller层测试
package com.hsh.excelutil.controller;
import com.hsh.excelutil.pojo.Student;
import com.hsh.excelutil.util.HuToolExcelUtil;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.ArrayList;
import java.util.List;
/**
* @Author hsh
* @DateTime 2023/08/29 17:39
**/
@RestController
public class TestController {
@GetMapping("/test")
public void test() {
List<Student> students1 = new ArrayList<>();
for (int i = 0; i < 5; i++) {
students1.add(new Student("名字"+i,i));
}
//写入
HuToolExcelUtil.writeExcel("E:\\1.xlsx", students1, Student.class);
//读取
students1 = HuToolExcelUtil.redaExcel("E:\\1.xlsx", Student.class);
//控制台打印
System.out.println(students1);
}
}
测试结果