当前位置：首页 > news >正文

mybatis-plus 枚举实现模版，导入，导出

news 2025/5/2 8:47:50

基于上一篇的枚举操作扩展一下

1.导出模版

excel类

package sjgjtask.databasetest.util;import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.http.HttpHeaders;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;public class ExcelUtil {/*** 通过class获取实体类中ExcelProperty注解设置的excel表头名称** @param clazz* @return*/public static List<String> getExcelHeadListByEntityExcelProperty(Class clazz) {List<String> result = new ArrayList<>();//获取类中所有的属性for (Field declaredField : clazz.getDeclaredFields()) {declaredField.setAccessible(true);//获取根据注解的方式获取ExcelProperty修饰的字段ExcelProperty excelProperty = declaredField.getAnnotation(ExcelProperty.class);if (excelProperty != null) {String[] values = excelProperty.value();StringBuilder value = new StringBuilder();for (String s : values) {value.append(s);}result.add(value.toString());}}return result;}public static <T> void templateExport(HttpServletResponse response, String name, Class<T> clazz) throws IOException {templateExport(response, name + "导入模板", clazz, null);}public static <T> void templateExport(HttpServletResponse response, String name, Class<T> clazz, List<T> dataList) throws IOException {if (CollUtil.isEmpty(dataList)) {dataList = new ArrayList<>();}Field[] fields = clazz.getDeclaredFields();List<SelectItem> selectItemData = new ArrayList<>();int excelIndex = 0;for (int i = 0; i < fields.length; i++) {Field field = fields[i];field.setAccessible(true);if (field.isAnnotationPresent(ExcelProperty.class)) {excelIndex++;// 确保 fieldType 是 Enum 类型并且实现了 IBaseEnum 接口Class<?> fieldType = field.getType();if (!isImplementingInterface(fieldType, IBaseEnum.class)) {continue;}@SuppressWarnings("unchecked")Class<? extends IBaseEnum<?>> enumType = (Class<? extends IBaseEnum<?>>) fieldType;IBaseEnum<?>[] enums = enumType.getEnumConstants();if (enums != null) {selectItemData = new ArrayList<>();SelectItem selectItem = new SelectItem(excelIndex - 1);List<String> enumMessages = Arrays.stream(enums).map(enumObject -> enumObject.getMessage().toString()).collect(Collectors.toList());selectItem.addDataItem(enumMessages);selectItemData.add(selectItem);}}}
//        if (selectItemData.size() > 0) {
//            selectWriteHandler = new SelectWriteHandler(selectItemData);
//        }response.setContentType("application/octet-stream");response.addHeader(HttpHeaders.CONTENT_DISPOSITION, String.format("attachment;filename*=%s", URLEncoder.encode(name, StandardCharsets.UTF_8.toString()) + ".xlsx"));// 下拉框数据ExcelWriterBuilder excelWriterBuilder = EasyExcelFactory.write(response.getOutputStream(), clazz).inMemory(Boolean.TRUE).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new SheetHeaderCommentWriteHandler(clazz));if (!selectItemData.isEmpty()) {excelWriterBuilder.registerWriteHandler(new SelectWriteHandler(selectItemData));}excelWriterBuilder.sheet(name + "配置数据").doWrite(dataList);}public static <T> void export(HttpServletResponse response, String name, List<T> dataList) throws IOException {if (CollUtil.isEmpty(dataList)) {throw new IOException("无数据");}Class<T> t = (Class<T>) dataList.get(0).getClass();templateExport(response, name, t, dataList);}private static boolean isImplementingInterface(Class<?> clazz, Class<?> interfaceClass) {if (clazz == null || interfaceClass == null || !interfaceClass.isInterface()) {return false;}// 检查当前类是否实现了接口if (Arrays.asList(clazz.getInterfaces()).contains(interfaceClass)) {return true;}// 递归检查父类return clazz.getSuperclass() != null && isImplementingInterface(clazz.getSuperclass(), interfaceClass);}}

枚举下拉框类

package sjgjtask.databasetest.util;import cn.hutool.core.lang.Assert;
import lombok.Data;import java.util.ArrayList;
import java.util.List;
import java.util.UUID;/*** excel下拉框数据项*/
@Data
public class SelectItem {/*** 下拉框所在列的索引，从0开始*/private Integer columnIndex;/*** 下拉框的值列表*/private List<DataItem> dataItems;/*** 子级对应的下拉框数据*/private SelectItem subSelect;public SelectItem(Integer columnIndex) {this.columnIndex = columnIndex;}public void addDataItem(List<String> values) {this.addDataItem("_" + UUID.randomUUID().toString().replaceAll("-", ""), values);}public void addDataItem(String mappingKey, List<String> values) {if (this.dataItems == null) {this.dataItems = new ArrayList<>();}this.dataItems.add(new DataItem(mappingKey, values));}@Datapublic static class DataItem {/*** 关联上级的key*/private String mappingKey;/*** 当前下拉框的值*/private List<String> values;/*** 当前下拉框的引用，隐藏页单元格地址*/private String hiddenFormulaRef;public DataItem(String mappingKey, List<String> values) {Assert.notBlank(mappingKey, "mappingKey is not blank");Assert.notEmpty(values, "values is not empty");this.mappingKey = mappingKey;this.values = values;}}}

下拉框填写类

package sjgjtask.databasetest.util;import cn.hutool.core.lang.Assert;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;import java.util.HashSet;
import java.util.List;
import java.util.Set;public class SelectWriteHandler implements SheetWriteHandler, CellWriteHandler {// 下拉框从哪一行结束private static final int ROW_SIZE = 10000;// 红色字体private final WriteFont redFont;// 下拉框数据private final List<SelectItem> selectItems;// 下拉框数据隐藏sheet名称private final String HIDDEN_SHEET_NAME = "hidden_sheet";// 拉框所在列的索引，从0开始private final Set<Integer> selectColumns = new HashSet<>();private boolean isLoadSelectColumns = false;private int rowIndex = 0;public SelectWriteHandler(List<SelectItem> selectItems) {Assert.notEmpty(selectItems, "selectItems can not be empty");this.selectItems = selectItems;redFont = getRedFont();}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {Workbook workbook = writeWorkbookHolder.getWorkbook();Sheet hiddenSheet = workbook.getSheet(HIDDEN_SHEET_NAME);if (hiddenSheet != null) {return;}hiddenSheet = workbook.createSheet(HIDDEN_SHEET_NAME);workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);Sheet sheet = writeSheetHolder.getSheet();for (SelectItem selectItem : selectItems) {buildHiddenSheetSelectRef(workbook, sheet, hiddenSheet, selectItem, null);}if (!isLoadSelectColumns) {isLoadSelectColumns = true;}}private void buildHiddenSheetSelectRef(Workbook workbook, Sheet sheet, Sheet hiddenSheet, SelectItem selectItem, String formulaRef) {if (!isLoadSelectColumns) {selectColumns.add(selectItem.getColumnIndex());}List<SelectItem.DataItem> dataItems = selectItem.getDataItems();for (SelectItem.DataItem dataItem : dataItems) {setDataAndName(workbook, hiddenSheet, dataItem);}// 单元格地址引用if (formulaRef == null || formulaRef.isEmpty()) {formulaRef = dataItems.get(0).getHiddenFormulaRef();}// 创建检验器DataValidation dataValidation = getDataValidation(sheet, selectItem, formulaRef);sheet.addValidationData(dataValidation);SelectItem subSelect = selectItem.getSubSelect();if (subSelect != null) {buildHiddenSheetSelectRef(workbook, sheet, hiddenSheet, subSelect, getInDirectFormulaRef(selectItem.getColumnIndex()));}}private DataValidation getDataValidation(Sheet sheet, SelectItem selectItem, String formulaRef) {DataValidationHelper helper = sheet.getDataValidationHelper();DataValidationConstraint constraint = helper.createFormulaListConstraint(formulaRef);CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, ROW_SIZE, selectItem.getColumnIndex(), selectItem.getColumnIndex());DataValidation dataValidation = helper.createValidation(constraint, rangeAddressList);dataValidation.setShowErrorBox(true);return dataValidation;}private void setDataAndName(Workbook workbook, Sheet hiddenSheet, SelectItem.DataItem dataItem) {// 构建隐藏数据Row row = hiddenSheet.createRow(rowIndex);List<String> values = dataItem.getValues();for (int i = 0; i < values.size(); i++) {row.createCell(i).setCellValue(values.get(i));}// 创建名称命名器Name name = workbook.createName();name.setNameName(dataItem.getMappingKey());name.setRefersToFormula(getFormulaRef(row));dataItem.setHiddenFormulaRef(name.getRefersToFormula());rowIndex++;}private String getInDirectFormulaRef(Integer columnIndex) {CellReference slectCellReference = new CellReference(1, columnIndex);return "INDIRECT(" + joinFormulaRef(slectCellReference, false) + ")";}@Overridepublic void afterCellDispose(CellWriteHandlerContext context) {if (!context.getHead()) {Integer columnIndex = context.getColumnIndex();if (selectColumns.contains(columnIndex)) {// 设置红色字体context.getFirstCellData().getOrCreateStyle().setWriteFont(redFont);}}CellWriteHandler.super.afterCellDispose(context);}private String getFormulaRef(Row prvRow) {Cell startCell = prvRow.getCell(prvRow.getFirstCellNum());Cell endCell = prvRow.getCell(prvRow.getLastCellNum() - 1);return HIDDEN_SHEET_NAME + "!" + joinFormulaRef(new CellReference(startCell), true) + ":" + joinFormulaRef(new CellReference(endCell), true);}public String joinFormulaRef(CellReference cellReference, boolean isAbsolute) {StringBuilder sb = new StringBuilder();String[] refs = cellReference.getCellRefParts();for (int i = refs.length - 1; i >= 1; i--) {if (isAbsolute) {sb.append("$");}sb.append(refs[i]);}return sb.toString();}/*** 返回一个红色字体** @return*/private WriteFont getRedFont() {WriteFont redFont = new WriteFont();redFont.setColor(IndexedColors.RED.getIndex());return redFont;}
}

表头填写类

package sjgjtask.databasetest.util;import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;/*** excel表头批注*/
public class SheetHeaderCommentWriteHandler implements RowWriteHandler {private Class clazz;public SheetHeaderCommentWriteHandler(Class clazz) {this.clazz = clazz;}@Overridepublic void afterRowDispose(RowWriteHandlerContext context) {}public void writeComment(RowWriteHandlerContext context, int i, String commentInfo) {Sheet sheet = context.getWriteSheetHolder().getSheet();Drawing<?> drawingPatriarch = sheet.createDrawingPatriarch();// 在第一行 第二列创建一个批注Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) i, 0, (short) i + 1, 1));// 输入批注信息comment.setString(new XSSFRichTextString(commentInfo));// 确保表头行存在Row headerRow = sheet.getRow(0);if (headerRow == null) {headerRow = sheet.createRow(0);}// 确保单元格存在Cell cell = headerRow.getCell(i);if (cell == null) {cell = headerRow.createCell(i);}// 将批注添加到单元格对象中cell.setCellComment(comment);}
}

实体类

package sjgjtask.databasetest.entity;import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import sjgjtask.databasetest.enums.Type;
import sjgjtask.databasetest.util.EnumTypeHandler;import java.util.List;@Data
@TableName("newtable")
public class NewTable {@ExcelProperty("唯一序列")private String id;@ExcelProperty("名称")private String name; // 名称@ExcelProperty("类型")private Type type; // 修改为枚举类型@ExcelProperty("类型数组")@TableField(typeHandler = EnumTypeHandler.class)private List<Type> arr; // 修改为枚举类型
}

最后导出方法类

/*** 利用ExcelUtil导出模板*/@GetMapping("/template")public void template(HttpServletResponse response) throws IOException {ExcelUtil.templateExport(response, "newtable", NewTable.class);}

导出结果如下