湖南网站制作电话世界军事新闻

一.需求

      动态生成Excel列，因为Excel列是通过类对象字段注解来添加，在不确定Excel列数的情况下，就需要动态生成列，对应类对象字段也需要动态生成；

二.ByteBuddy字节码增强动态创建类

1.依赖

<dependencies><dependency><groupId>net.bytebuddy</groupId><artifactId>byte-buddy</artifactId><version>1.11.17</version></dependency>
</dependencies>

2.动态创建类

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;}

​动态类如下所示：
public class ExcelRecordDynamicDTO implements Serializable {//动态生成的字段和注解如下所示//@ExcelColum(value="第一列", col=1)//private String col1;
}​

3.实例化赋值

//实例化
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

List<ExcelRecordDynamicDTO> resultList = new ArrayList<>();
resultList.add(dto);ExcelUtil.writeExcel(resultList,dy.newInstance());

三.Excel工具类

                   

1.依赖

<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代码

注解如下：
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {//标题String value() default "";//Excel从左往右排列位置int col() default 0;
}

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);}});//索引---columnsMap<Integer, List<Field>> refectionMap = new HashMap<>(16);//默认读取第一个sheetSheet 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);}});//索引---columnsMap<Integer, List<Field>> refectionMap = new HashMap<>(16);//默认读取第一个sheetSheet 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.只能一次创建，无法清除上次动态创建的字段

代码如下：

 <dependency><groupId>javassist</groupId><artifactId>javassist</artifactId><version>3.12.1.GA</version></dependency>

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();}