全文直接復(fù)制粘貼即可，測(cè)試無(wú)誤

一、注解類(lèi)

1、ExcelSelected.java

設(shè)置下拉框

@Documented
@Target({ElementType.FIELD})//用此注解用在屬性上。
@Retention(RetentionPolicy.RUNTIME)//注解不僅被保存到class文件中，jvm加載class文件之后，仍然存在；
public @interface ExcelSelected {/*** 固定下拉內(nèi)容*/String[] source() default {};/*** 方式二：提供動(dòng)態(tài)下拉選項(xiàng)的類(lèi)*/Class<? extends WhExcelDynamicSelect>[] sourceClass() default {};/*** 設(shè)置下拉框的起始行，默認(rèn)為第二行*/int firstRow() default 1;/*** 設(shè)置下拉框的結(jié)束行，默認(rèn)為最后一行*/int lastRow() default 0x10000;
}

2、TextColumn.java

設(shè)置單元格為文本格式

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TextColumn {int index(); // 列索引
}

二、Controller類(lèi)

分為導(dǎo)入數(shù)據(jù)與導(dǎo)出模板

@Tag(name = "xxxx管理")
@RestController
@RequestMapping("/api/v1/project/people")
@RequiredArgsConstructor
@Slf4j
public class WhProjectPeopleController {private final WhProjectPeopleService whProjectPeopleService;@GetMapping("/exportDownload")@Operation(summary = "導(dǎo)出excel模板")public void downloadExcel(HttpServletResponse response) throws IOException {//獲取輸入流，原始模板位置String name = "人員信息模板";//假如以中文名下載的話(huà)，設(shè)置下載文件名稱(chēng)String filename = "人員信息模板.xlsx";//轉(zhuǎn)碼，免得文件名中文亂碼filename = URLEncoder.encode(filename, "UTF-8");//設(shè)置文件下載頭response.addHeader("Content-Disposition", "attachment;filename=" + filename);response.setContentType("multipart/form-data");ExcelUtils.writeExcel(response, filename, name, WhProjectPeopleVO.class, null);}@PostMapping("/importExcel")@Operation(summary = "導(dǎo)入人員數(shù)據(jù)")public Result importExcel(MultipartFile file, HttpServletRequest request) throws IOException {if (file.isEmpty()) {throw new SzException("excel文件內(nèi)容為空！");}List<WhProjectPeopleVO> vos = ExcelUtils.read(file, WhProjectPeopleVO.class);if (CollectionUtils.isEmpty(vos)){throw new SzException("導(dǎo)入數(shù)據(jù)失敗！");}Map<String,Integer> result = whProjectPeopleService.importExcel(vos);return Result.success(result);}}

三、Service方法

向excel導(dǎo)入數(shù)據(jù)

    @Override@Transactionalpublic Map<String, Integer> importExcel(List<WhProjectPeopleVO> vos) {Map<String, Integer> params = new HashMap<>();if (CollectionUtils.isEmpty(vos)) {throw new SzException("數(shù)據(jù)導(dǎo)入失敗！");}log.info("導(dǎo)入數(shù)據(jù)：{}" + JSON.toJSONString(vos));int count = 0;List<WhProjectPeople> peoples = whProjectPeopleConverter.voListToEntity(vos);for (WhProjectPeople people : peoples) {WhProjectPeopleForm whProjectPeopleForm = whProjectPeopleConverter.entityToForm(people);saveProjectPeople(whProjectPeopleForm);count++;params.put("insert", count);}return params;}
}

四、工具類(lèi)

1、ExcelUtils.java

public class ExcelUtils {/*** 將列表以 Excel 響應(yīng)給前端** @param response  響應(yīng)* @param filename  文件名* @param sheetName Excel sheet 名* @param head      Excel head 頭* @param data      數(shù)據(jù)列表* @param <T>       泛型，保證 head 和 data 類(lèi)型的一致性* @throws IOException 寫(xiě)入失敗的情況*/public static <T> void writeExcel(HttpServletResponse response, String filename, String sheetName,Class<T> head, List<T> data) throws IOException {CellStyleStrategy cellStyleStrategy =new CellStyleStrategy(new WriteCellStyle());Map<Integer, WhExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);// 輸出 ExcelEasyExcel.write(response.getOutputStream(), head)// 不要自動(dòng)關(guān)閉，交給 Servlet 自己處理.autoCloseStream(false)//設(shè)置表頭和填充內(nèi)容的樣式.registerWriteHandler(cellStyleStrategy)//設(shè)置填充內(nèi)容單元格格式為文本格式.registerWriteHandler(new CustomSheetWriteHandler()).registerWriteHandler(new SelectedSheetWriteHandler(selectedMap)).sheet(sheetName).doWrite(data);// 設(shè)置 header 和 contentType。寫(xiě)在最后的原因是，避免報(bào)錯(cuò)時(shí)，響應(yīng) contentType 已經(jīng)被修改了response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));response.setContentType("application/vnd.ms-excel;charset=UTF-8");}/*** 將列表以 Excel 響應(yīng)給前端** @param response  響應(yīng)* @param filename  文件名* @param sheetName Excel sheet 名* @param head      Excel head 頭* @param data      數(shù)據(jù)列表* @param <T>       泛型，保證 head 和 data 類(lèi)型的一致性* @throws IOException 寫(xiě)入失敗的情況*/public static <T> void write(HttpServletResponse response, String filename, String sheetName,Class<T> head, List<T> data) throws IOException {CellStyleStrategy cellStyleStrategy =new CellStyleStrategy(new WriteCellStyle());// 輸出 ExcelEasyExcel.write(response.getOutputStream(), head).autoCloseStream(false) // 不要自動(dòng)關(guān)閉，交給 Servlet 自己處理.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 長(zhǎng)度，自動(dòng)適配。最大 255 寬度.registerWriteHandler(cellStyleStrategy).sheet(sheetName).doWrite(data);// 設(shè)置 header 和 contentType。寫(xiě)在最后的原因是，避免報(bào)錯(cuò)時(shí)，響應(yīng) contentType 已經(jīng)被修改了response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));response.setContentType("application/vnd.ms-excel;charset=UTF-8");}public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {return EasyExcel.read(file.getInputStream(), head, null)// 不要自動(dòng)關(guān)閉，交給 Servlet 自己處理.autoCloseStream(false).doReadAllSync();}/*** 解析表頭類(lèi)中的下拉注解* @param head 表頭類(lèi)* @param <T> 泛型* @return Map<下拉框列索引, 下拉框內(nèi)容> map*/private static <T> Map<Integer, WhExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {Map<Integer, WhExcelSelectedResolve> selectedMap = new HashMap<>();// getDeclaredFields(): 返回全部聲明的屬性；getFields(): 返回public類(lèi)型的屬性Field[] fields = head.getDeclaredFields();for (int i = 0; i < fields.length; i++){Field field = fields[i];// 解析注解信息ExcelSelected selected = field.getAnnotation(ExcelSelected.class);ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (selected != null) {WhExcelSelectedResolve excelSelectedResolve = new WhExcelSelectedResolve();String[] source = excelSelectedResolve.resolveSelectedSource(selected);if (source != null && source.length > 0){excelSelectedResolve.setSource(source);excelSelectedResolve.setFirstRow(selected.firstRow());excelSelectedResolve.setLastRow(selected.lastRow());if (property != null && property.index() >= 0){selectedMap.put(property.index(), excelSelectedResolve);} else {selectedMap.put(i, excelSelectedResolve);}}}}return selectedMap;}}

2、SpringContextUtil.java

@Component
public class SpringContextUtil implements ApplicationContextAware {/*** 獲取ApplicationContext*/@Getterprivate static ApplicationContext applicationContext;@Overridepublic void setApplicationContext(ApplicationContext applicationContext) throws BeansException {SpringContextUtil.applicationContext = applicationContext;}/*** 通過(guò)class獲取Bean*/public static <T> T getBean(Class<T> clazz) {return applicationContext.getBean(clazz);}/*** 通過(guò)name以及class獲取Bean*/public static <T> T getBean(String name, Class<T> clazz) {return applicationContext.getBean(name, clazz);}
}

五、轉(zhuǎn)換器

1、WhProjectExcelConverter.java

項(xiàng)目id與項(xiàng)目名稱(chēng)互相轉(zhuǎn)化

public class WhProjectExcelConverter implements Converter<String> {private final WhProjectService whProjectService = SpringContextUtil.getBean(WhProjectService.class);private final List<WhProject> list = whProjectService.list();@Overridepublic Class<?> supportJavaTypeKey() {//實(shí)體類(lèi)對(duì)象屬性類(lèi)型return String.class;}/*** 將單元格中數(shù)據(jù)轉(zhuǎn)化為java對(duì)象屬性* @param context* @return* @throws Exception*/@Overridepublic String convertToJavaData(ReadConverterContext<?> context) throws Exception {//創(chuàng)建集合用于存儲(chǔ)項(xiàng)目id和名稱(chēng)對(duì)應(yīng)關(guān)系Map<String,String> mapToJavaData = new HashMap<>();//遍歷項(xiàng)目列表將項(xiàng)目id和名稱(chēng)放入mapfor (WhProject sysDeptEntity : list) {mapToJavaData.put(sysDeptEntity.getProjectName(),sysDeptEntity.getId());}//從cellData中讀取數(shù)據(jù) 轉(zhuǎn)換為實(shí)體類(lèi)中的對(duì)象數(shù)值return mapToJavaData.get(context.getReadCellData().getStringValue());}/*** 將java對(duì)象轉(zhuǎn)化為excel單元格數(shù)據(jù)* @param context* @return* @throws Exception*/@Overridepublic WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {Map<String,String> mapToExcelData = new HashMap<>();for (WhProject sysDeptEntity : list) {mapToExcelData.put(sysDeptEntity.getId(),sysDeptEntity.getProjectName());}//將java屬性轉(zhuǎn)換為excel對(duì)應(yīng)屬性類(lèi)型return new WriteCellData<>(mapToExcelData.get(context.getValue()));}
}

2、WhWorkTypeExcelConverter.java

int類(lèi)型type值與String類(lèi)型type名稱(chēng)相互轉(zhuǎn)化

public class WhWorkTypeExcelConverter implements Converter<Integer> {private final WhDictItemService whDictItemService = SpringContextUtil.getBean(WhDictItemService.class);private final List<WhDictItem> list = whDictItemService.listByCode(WORK_TYPE);@Overridepublic Class<?> supportJavaTypeKey() {//實(shí)體類(lèi)對(duì)象屬性類(lèi)型return Integer.class;}/*** 將單元格中數(shù)據(jù)轉(zhuǎn)化為java對(duì)象屬性* @param context* @return* @throws Exception*/@Overridepublic Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {//創(chuàng)建集合用于存儲(chǔ)部門(mén)id和名稱(chēng)對(duì)應(yīng)關(guān)系Map<String,String> mapToJavaData = new HashMap<>();//遍歷數(shù)據(jù)字典將屬性名稱(chēng)和屬性值放入mapfor (WhDictItem sysDeptEntity : list) {mapToJavaData.put(sysDeptEntity.getItemName(),sysDeptEntity.getItemValue());}//從cellData中讀取數(shù)據(jù) 轉(zhuǎn)換為實(shí)體類(lèi)中的對(duì)象數(shù)值return Integer.valueOf(mapToJavaData.get(context.getReadCellData().getStringValue()));}/*** 將java對(duì)象轉(zhuǎn)化為excel單元格數(shù)據(jù)* @param context* @return* @throws Exception*/@Overridepublic WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {Map<String,String> mapToExcelData = new HashMap<>();for (WhDictItem sysDeptEntity : list) {mapToExcelData.put(sysDeptEntity.getItemValue(),sysDeptEntity.getItemName());}//將java屬性轉(zhuǎn)換為excel對(duì)應(yīng)屬性類(lèi)型return new WriteCellData<>(mapToExcelData.get(context.getValue()));}
}

六、Handler類(lèi)

1、SelectedSheetWriteHandler.java

設(shè)置下拉列表相關(guān)

@Data
public class SelectedSheetWriteHandler implements SheetWriteHandler {/*** 構(gòu)建下拉選的map*/private final Map<Integer, WhExcelSelectedResolve> selectedMap;private final int columnSelectMaxLength = 255;@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 這里可以對(duì)cell進(jìn)行任何操作Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();selectedMap.forEach((k, v) -> {// 設(shè)置下拉列表的行： 首行，末行，首列，末列CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);// 設(shè)置下拉列表的值DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());// 設(shè)置約束DataValidation validation = helper.createValidation(constraint, rangeList);// 阻止輸入非下拉選項(xiàng)的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox("提示", "請(qǐng)輸入下拉選項(xiàng)中的內(nèi)容");sheet.addValidationData(validation);});}
}

2、CustomSheetWriteHandler.java

設(shè)置文本格式、表格式

public class CustomSheetWriteHandler implements SheetWriteHandler {// 設(shè)置100列columnprivate static final Integer COLUMN = 100;@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 獲取帶有 @TextColumn 注解的列索引Map<Integer, String> textColumns = getTextColumns();SXSSFSheet sxssfSheet = (SXSSFSheet) writeSheetHolder.getSheet();for (int i = 0; i < COLUMN; i++) {if (textColumns.containsKey(i)) {// 設(shè)置為文本格式CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();// 49為文本格式cellStyle.setDataFormat((short) 49);// i為列，一整列設(shè)置為文本格式sxssfSheet.setDefaultColumnStyle(i, cellStyle);}}}private Map<Integer, String> getTextColumns() {Map<Integer, String> textColumns = new HashMap<>();Class<?> dataClass = WhProjectPeopleVO.class;Field[] fields = dataClass.getDeclaredFields();for (Field field : fields) {if (field.isAnnotationPresent(TextColumn.class)) {int columnIndex = field.getAnnotation(TextColumn.class).index();textColumns.put(columnIndex, field.getName());}}return textColumns;}
}

七、SelectedResolve類(lèi)

@Data
@Slf4j
public class WhExcelSelectedResolve {/*** 下拉內(nèi)容*/private String[] source;/*** 設(shè)置下拉框的起始行，默認(rèn)為第二行*/private int firstRow;/*** 設(shè)置下拉框的結(jié)束行，默認(rèn)為最后一行*/private int lastRow;public String[] resolveSelectedSource(ExcelSelected excelSelected) {if (excelSelected == null) {return null;}// 獲取固定下拉框的內(nèi)容String[] source = excelSelected.source();if (source.length > 0) {return source;}// 獲取動(dòng)態(tài)下拉框的內(nèi)容Class<? extends WhExcelDynamicSelect>[] classes = excelSelected.sourceClass();if (classes.length > 0) {try {WhExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();String[] dynamicSelectSource = excelDynamicSelect.getSource();if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {return dynamicSelectSource;}} catch (InstantiationException | IllegalAccessException e) {log.error("解析動(dòng)態(tài)下拉框數(shù)據(jù)異常", e);}}return null;}
}

八、實(shí)體類(lèi)

@Data
@Schema(description = "項(xiàng)目隨行人員表")
@HeadRowHeight(30)
@ContentRowHeight(18)
public class WhProjectPeopleVO {@ExcelIgnore@Schema(description = "id")private String id;@ExcelIgnore@Schema(description = "人員類(lèi)型 0隨行人員1負(fù)責(zé)人 默認(rèn)0")private Integer type;@ExcelProperty(value = "姓名", index = 0)@Schema(description = "姓名")private String name;@ColumnWidth(20)@TextColumn(index = 1)@ExcelProperty(value = "身份證號(hào)", index = 1)@Schema(description = "身份證號(hào)")private String identityId;@ColumnWidth(20)@ExcelProperty(value = "手機(jī)號(hào)", index = 2)@Schema(description = "手機(jī)號(hào)")private String mobile;@ColumnWidth(20)@ExcelSelected(sourceClass = WhProjectSelect.class)@ExcelProperty(value = "所屬項(xiàng)目", index = 3, converter = WhProjectExcelConverter.class)@Schema(description = "項(xiàng)目id")private String projectId;@ExcelIgnore@Schema(description = "微信openId")private String openId;@ExcelIgnore@Schema(description = "微信unionId")private String unionId;@ExcelIgnore@Schema(description = "開(kāi)始時(shí)間")private Date startTime;@ExcelIgnore@Schema(description = "結(jié)束時(shí)間")private Date endTime;@ExcelIgnore@Schema(description = "項(xiàng)目名稱(chēng)")private String projectName;@ExcelIgnore@Schema(description = "最近入場(chǎng)時(shí)間")private Date lastArrivalTime;@ExcelSelected(sourceClass = WhWorkTypeSelect.class)@ExcelProperty(value = "工種", index = 4, converter = WhWorkTypeExcelConverter.class)@Schema(description = "工種")private Integer workType;
}

測(cè)試結(jié)果