一.需求
动态生成Excel列，因为Excel列是通过类对象字段注解来添加，在不确定Excel列数的情况下，就需要动态生成列，对应类对象字段也需要动态生成；
二.ByteBuddy字节码增强动态创建类
1.依赖
java"><dependencies>
<dependency>
<groupId>net.bytebuddy</groupId>
<artifactId>byte-buddy</artifactId>
<version>1.11.17</version>
</dependency>
</dependencies>2.动态创建类
java">public Class<? extends ExcelRecordDynamicDTO> getDynamicClass(ChatBatchRequestVO chatBatchRequest) throws NoSuchFieldException, IllegalAccessException, InstantiationException {
DynamicType.Builder<ExcelRecordDynamicDTO> dynamicClass = new ByteBuddy()
.subclass(ExcelRecordDynamicDTO.class);
dynamicClass = dynamicClass.defineField("question", String.class, Visibility.PUBLIC)
.annotateField(AnnotationDescription.Builder.ofType(ExcelColumn.class)
.define("value", new String("问题"))
.define("col", 1)
.build())
.defineMethod("getQuestion", String.class, Visibility.PUBLIC)
.intercept(FieldAccessor.ofBeanProperty())
.defineMethod("setQuestion", void.class, Visibility.PUBLIC)
.withParameters(String.class)
.intercept(FieldAccessor.ofBeanProperty());
for (int i = 0; i < chatBatchRequest.getModelTypeId().length; i++) {
dynamicClass = dynamicClass.defineField("answer" + i, String.class, Visibility.PUBLIC)
.annotateField(AnnotationDescription.Builder.ofType(ExcelColumn.class)
.define("value", new String(ChatModelTypeEnum.getmodelShowNameByType(chatBatchRequest.getModelTypeId()[i])))
.define("col", i + 2)
.build())
.defineMethod("getAnswer" + i, String.class, Visibility.PUBLIC)
.intercept(FieldAccessor.ofBeanProperty())
.defineMethod("setAnswer" + i, void.class, Visibility.PUBLIC)
.withParameters(String.class)
.intercept(FieldAccessor.ofBeanProperty());
}
Class<? extends ExcelRecordDynamicDTO> d = dynamicClass.make().load(ExcelRecordDynamicDTO.class.getClassLoader()).getLoaded();
return d;
}
java">动态类如下所示：
public class ExcelRecordDynamicDTO implements Serializable {
//动态生成的字段和注解如下所示
//@ExcelColum(value="第一列", col=1)
//private String col1;
}
3.实例化赋值
java">//实例化
Class<? extends ExcelRecordDynamicDTO> dy = getDynamicClass(chatBatchRequest);
ExcelRecordDynamicDTO dtp = dy.newInstatnce();
//赋值
Field[] cityCode = dto.getClass().getDeclaredFields();
for (Filed f : cityCode) {
f.setAccessible(true)
f.set(dto, "赋值");
}
//获取值
dto.getClass().getDeclaredField("question").get(dto)4.创建Excel
java">List<ExcelRecordDynamicDTO> resultList = new ArrayList<>();
resultList.add(dto);
ExcelUtil.writeExcel(resultList,dy.newInstance());三.Excel工具类
1.依赖
java"><dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
</dependencies>2.Excel代码
java">注解如下：
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {
//标题
String value() default "";
//Excel从左往右排列位置
int col() default 0;
}java">Excel工具类：使用apache.poi
public class ExcelUtil {
private final static String EXCEL2003 = "xls";
private final static EXCEL2007 = "xlsx";
public static <T> List<T> readExcel(String path, Class<T> cls, File file) {
String fileName = file.getName();
if (!fileName.matches("^.+\\.)?i)(xls)$") && !fineName.matches("^.+\\.(?i)(xlsx)$")) {
log.error("格式错误");
}
List<T> dataList = new ArrayList<>();
Workbook workbook = null;
try {
if (fileName.endsWith(EXCEL2007)) {
InputStream is = new FileInputStream(new File(path));
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith(EXCEL2003)) {
InputStream is = new FileInputStream(new File(path));
workbook = new HSSFWorkbook(is);
}
if (workbook != null ) {
Map<String, List<Field>> classMap = new HashMap<>();
List<Field> fields = Stream.of(cls.getDeclaredFields()).collectors.toList());
fields.forEach(field -> {
ExcelColumn annotaion = field.getAnnotation(ExcelColum.class);
if (annotaion != null) {
String value = annotation.value();
if (StringUtils.isBlank(value)) {
return;
}
if (!classMap.containsKey(value)) {
classMap.put(value, new ArrayList<>());
}
field.setAccessible(true);
classMap.get(value).add(field);
}
});
//索引---columns
Map<Integer, List<Field>> refectionMap = new HashMap<>(16);
//默认读取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
booleasn firstRow = true;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++)
{
Row row = sheet.getRow(i);
//首行 提取注解
if (firstRow) {
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum();
j++) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (classMap.containsKey(cellValue) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
firstRow false;
} else {
//忽略空白行
if (row == null) {
continue;
}
try {
T t = cls.newInstance();
//判断是否为空白行
boolean allBlank = true;
for (int j = row.getFirstCellNum(); j <=
row.getLastCellNum(); j++) {
if (reflectionMap.containsKey(j)) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (StringUtils.isNotBlank(cellValue)) {
allBlank = false;
}
List<Field> fieldList = reflectionMap.get(j);
fieldList.foeEach(x -> {
try {
handleField(t, cellValue, x);
} catch (Exception e) {
log.error(e);
}
});
}
}
if (!allBlank) {
dataList.add(t);
} else {
log.warn("ignore!");
}
} catch (Exception e) {
log.error(e);
}
}
}
}
} catch (Exception e) {
log.error(e);
} finally {
if (workbook != null） {
try {
workbook.close();
} catch (Exception e) {
log.error(e);
}
}
}
return dataList;
}
public static <T> List<T> readExcel(Class<T> cls, MultipartFile file) {
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.)?i)(xls)$") && !fineName.matches("^.+\\.(?i)(xlsx)$")) {
log.error("格式错误");
}
List<T> dataList = new ArrayList<>();
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
if (fileName.endsWith(EXCEL2007)) {
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith(EXCEL2003)) {
workbook = new HSSFWorkbook(is);
}
if (workbook != null ) {
Map<String, List<Field>> classMap = new HashMap<>();
List<Field> fields = Stream.of(cls.getDeclaredFields()).collectors.toList());
fields.forEach(field -> {
ExcelColumn annotaion = field.getAnnotation(ExcelColum.class);
if (annotaion != null) {
String value = annotation.value();
if (StringUtils.isBlank(value)) {
return;
}
if (!classMap.containsKey(value)) {
classMap.put(value, new ArrayList<>());
}
field.setAccessible(true);
classMap.get(value).add(field);
}
});
//索引---columns
Map<Integer, List<Field>> refectionMap = new HashMap<>(16);
//默认读取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
booleasn firstRow = true;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++)
{
Row row = sheet.getRow(i);
//首行 提取注解
if (firstRow) {
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum();
j++) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (classMap.containsKey(cellValue) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
firstRow false;
} else {
//忽略空白行
if (row == null) {
continue;
}
try {
T t = cls.newInstance();
//判断是否为空白行
boolean allBlank = true;
for (int j = row.getFirstCellNum(); j <=
row.getLastCellNum(); j++) {
if (reflectionMap.containsKey(j)) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (StringUtils.isNotBlank(cellValue)) {
allBlank = false;
}
List<Field> fieldList = reflectionMap.get(j);
fieldList.foeEach(x -> {
try {
handleField(t, cellValue, x);
} catch (Exception e) {
log.error(e);
}
});
}
}
if (!allBlank) {
dataList.add(t);
} else {
log.warn("ignore!");
}
} catch (Exception e) {
log.error(e);
}
}
}
}
} catch (Exception e) {
log.error(e);
} finally {
if (workbook != null） {
try {
workbook.close();
} catch (Exception e) {
log.error(e);
}
}
}
return dataList;
}
private static <T> void handleField(T t, String value, Field field) trows Exception
{
Class<?> type = field.getType();
if (type == null || type== void.class || StringUtils.isBlank(value)) {
return;
}
if (type == Object.class) {
field.set(t, value);
} else if (type.getSuperclass() == null || type.getSuperclass() == Number.calss)
{
if (type == int.class || type == Integer.class) {
field.set(t, NumberUtils.toInt(value));
} else if (type == long.class || type == Long.calss ) {
field.set(t, NumberUtils.toLong(value));
} else if (type == byte.class || type == Byte.calss ) {
field.set(t, NumberUtils.toByte(value));
} else if (type == short.class || type == Short.calss ) {
field.set(t, NumberUtils.toShort(value));
} else if (type == double.class || type == Double.calss ) {
field.set(t, NumberUtils.toDouble(value));
} else if (type == float.class || type == Float.calss ) {
field.set(t, NumberUtils.toFloat(value));
} else if (type == char.class || type == Character.calss ) {
field.set(t, NumberUtils.toChar(value));
} else if (type == boolean.class) {
field.set(t, NumberUtils.toBoolean(value));
} else if (type == BigDecimal.class) {
field.set(t, new BigDecimal(value));
}
} else if (type == Boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == Data.class) {
SimpleDateFprmat format = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US);
field.set(t, format.parse(value));
} else if (type == String.class) {
field.set(t, value);
} else {
Constructor<?> constructor = type.getConstructor(String.class);
field.set(t, constructor .newInstance(value));
}
}
private static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return HSSDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
}
else {
return new BigDecimal(cell.getNumericCellValue()).toString();
}
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return StringUtils.trimToEmpty(cell.getStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return StringUtils.trimToEmpty(cell.getCellFormula());
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return "";
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return StringUtils.trimToEmpty(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
return "ERROR";
} else {
return cell.toString().trim;
}
}
public static <T> void writeExcel(String filePathName, List<T> dataList, ExcelRecordDynamicDTO excelRecordDTO) {
Field[] fields = excelRecordDTO.getClass().getDeclaredFields();
List<Field> fieldList = Arrays.stream(fields).filter(field -> {
ExcelColumn annotaion = field.getAnnoation(ExcelColum.class);
if (annotation != null && annotation.col() > 0) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int col = 0;
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
col = annotation.col();
}
return col;
})).collect(Collectors.toList());
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
AtomicInteger ai = new AtomicInteger();
{
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
//写入头部
fieldList.forEach(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String columnName = "";
if (annotation != null) {
columnName = annotation.value();
}
Cell cell = row.createCell(aj.getAndIncreament());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellSty;e.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellSty;e.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
if (CollectionUtils.isNatEmpty(dataList)) {
dataList.forEach(t -> {
Row row1 = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
fieldList.forEach(field -> {
Class<?> type = field.getType();
Object value = "";
try {
value = field.get(t);
} catch (Exception e) {
e.printStackTrace();
}
Cell cell = row1.createCell(aj.getAndIncrement());
if (value != null) {
if (type == Date.class) {
cell.setCellValue(value.toString());
} else {
cell.setCellValue(value.toString());
}
cell.setCellValue(value.toString());
}
});
});
}
//冻结窗格
wb.getSheet("Sheet1").createFreezePane(0, 1, 0, 1);
//浏览器下载excel
//buildExcelDocument("abbot.xlsx", wb, response);
//生成Excel文件
buildExcelFile(filePathName, wb);
}
private static void buildExcelFile(String path, Workbook wb) {
File file = new File(path);
if (file.exists()) {
file.delete();
}
try {
wb.write(newFileOutputStream(file));
} catch (Exception e) {
e.printStackTrace();
}
}
}四.附
使用ClassPool动态生成类，实测暂未解决的问题：
1.无法给字段注解属性为int类型的赋值；
2.只能一次创建，无法清除上次动态创建的字段
代码如下：
java"> <dependency>
<groupId>javassist</groupId>
<artifactId>javassist</artifactId>
<version>3.12.1.GA</version>
</dependency>
java">public Object getExcelRecordDynamicClass(ChatBatchRequestVO chatBatchRequest) throws NotFoundException, CannotCompileException, IllegalAccessException, InstantiationException {
//默认的类搜索路径
ClassPool pool = ClassPool.getDefault();
//获取一个ctClass对象 com.example.demo.excel.entity.CitiesVo 这个是包的相对路径
// CtClass ctClass = pool.makeClass("ExcelRecordDynamicDTO");
CtClass ctClass = pool.get("cn.com.wind.ai.platform.aigc.test.pojo.excel.ExcelRecordDynamicDTO");
for (int i = 0; i < chatBatchRequest.getModelTypeId().length; i++) {
//添加属性
ctClass.addField(CtField.make("public String answer"+ i +";", ctClass));
//添加set方法
ctClass.addMethod(CtMethod.make("public void setAnswer"+ i +"(String answer"+ i +"){this.answer"+ i +" = answer"+ i +";}", ctClass));
//添加get方法
ctClass.addMethod(CtMethod.make("public String getAnswer"+ i +"(){return this.answer"+ i +";}", ctClass));
//获取这个字段
CtField answer = ctClass.getField("answer"+ i);
FieldInfo fieldInfo = answer.getFieldInfo();
ConstPool cp = fieldInfo.getConstPool();
AnnotationsAttribute attribute = (AnnotationsAttribute) fieldInfo.getAttribute(AnnotationsAttribute.visibleTag);
//这里进行了判断 如果说当前字段没有注解的时候 AnnotationsAttribute 这个对象是为空的
//所以要针对这个进行新创建 一个 AnnotationsAttribute 对象
if(ObjectUtils.isEmpty(attribute)){
List<AttributeInfo> attributeInfos =fieldInfo.getAttributes();
attribute = !attributeInfos.isEmpty()?(AnnotationsAttribute) attributeInfos.get(0):
new AnnotationsAttribute(fieldInfo.getConstPool(), AnnotationsAttribute.visibleTag);
}
// Annotation 默认构造方法 typeName:表示的是注解的路径
Annotation bodyAnnot = new Annotation("cn.com.wind.ai.platform.aigc.test.framework.aop.excel.ExcelColumn", cp);
// name 表示的是自定义注解的 方法 new StringMemberValue("名字", cp) 表示给name赋值
bodyAnnot.addMemberValue("value", new StringMemberValue(ChatModelTypeEnum.getmodelShowNameByType(chatBatchRequest.getModelTypeId()[i]), cp));
// IntegerMemberValue colValue = new IntegerMemberValue(i + 2, cp);
// bodyAnnot.addMemberValue("col", colValue);
attribute.addAnnotation(bodyAnnot);
fieldInfo.addAttribute(attribute);
}
pool.clearImportedPackages();
return ctClass.toClass().newInstance();
}
