全文直接复制粘贴即可，测试无误
一、注解类
1、ExcelSelected.java
设置下拉框
@Documented
@Target({ElementType.FIELD})//用此注解用在属性上。
@Retention(RetentionPolicy.RUNTIME)//注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
public @interface ExcelSelected {
/**
* 固定下拉内容
*/
String[] source() default {};
/**
* 方式二：提供动态下拉选项的类
*/
Class<? extends WhExcelDynamicSelect>[] sourceClass() default {};
/**
* 设置下拉框的起始行，默认为第二行
*/
int firstRow() default 1;
/**
* 设置下拉框的结束行，默认为最后一行
*/
int lastRow() default 0x10000;
}
2、TextColumn.java
设置单元格为文本格式
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TextColumn {
int index(); // 列索引
}
二、Controller类
分为导入数据与导出模板
@Tag(name = "xxxx管理")
@RestController
@RequestMapping("/api/v1/project/people")
@RequiredArgsConstructor
@Slf4j
public class WhProjectPeopleController {
private final WhProjectPeopleService whProjectPeopleService;
@GetMapping("/exportDownload")
@Operation(summary = "导出excel模板")
public void downloadExcel(HttpServletResponse response) throws IOException {
//获取输入流，原始模板位置
String name = "人员信息模板";
//假如以中文名下载的话，设置下载文件名称
String filename = "人员信息模板.xlsx";
//转码，免得文件名中文乱码
filename = URLEncoder.encode(filename, "UTF-8");
//设置文件下载头
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType("multipart/form-data");
ExcelUtils.writeExcel(response, filename, name, WhProjectPeopleVO.class, null);
}
@PostMapping("/importExcel")
@Operation(summary = "导入人员数据")
public Result importExcel(MultipartFile file, HttpServletRequest request) throws IOException {
if (file.isEmpty()) {
throw new SzException("excel文件内容为空！");
}
List<WhProjectPeopleVO> vos = ExcelUtils.read(file, WhProjectPeopleVO.class);
if (CollectionUtils.isEmpty(vos)){
throw new SzException("导入数据失败！");
}
Map<String,Integer> result = whProjectPeopleService.importExcel(vos);
return Result.success(result);
}
}
三、Service方法
向excel导入数据
@Override
@Transactional
public Map<String, Integer> importExcel(List<WhProjectPeopleVO> vos) {
Map<String, Integer> params = new HashMap<>();
if (CollectionUtils.isEmpty(vos)) {
throw new SzException("数据导入失败！");
}
log.info("导入数据：{}" + 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;
}
}
四、工具类
1、ExcelUtils.java
public class ExcelUtils {
/**
* 将列表以 Excel 响应给前端
*
* @param response 响应
* @param filename 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表
* @param <T> 泛型，保证 head 和 data 类型的一致性
* @throws IOException 写入失败的情况
*/
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);
// 输出 Excel
EasyExcel.write(response.getOutputStream(), head)
// 不要自动关闭，交给 Servlet 自己处理
.autoCloseStream(false)
//设置表头和填充内容的样式
.registerWriteHandler(cellStyleStrategy)
//设置填充内容单元格格式为文本格式
.registerWriteHandler(new CustomSheetWriteHandler())
.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
.sheet(sheetName).doWrite(data);
// 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
}
/**
* 将列表以 Excel 响应给前端
*
* @param response 响应
* @param filename 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表
* @param <T> 泛型，保证 head 和 data 类型的一致性
* @throws IOException 写入失败的情况
*/
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());
// 输出 Excel
EasyExcel.write(response.getOutputStream(), head)
.autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
.registerWriteHandler(cellStyleStrategy)
.sheet(sheetName).doWrite(data);
// 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
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)
// 不要自动关闭，交给 Servlet 自己处理
.autoCloseStream(false)
.doReadAllSync();
}
/**
* 解析表头类中的下拉注解
* @param head 表头类
* @param <T> 泛型
* @return Map<下拉框列索引, 下拉框内容> map
*/
private static <T> Map<Integer, WhExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
Map<Integer, WhExcelSelectedResolve> selectedMap = new HashMap<>();
// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
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
*/
@Getter
private static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
SpringContextUtil.applicationContext = applicationContext;
}
/**
* 通过class获取Bean
*/
public static <T> T getBean(Class<T> clazz) {
return applicationContext.getBean(clazz);
}
/**
* 通过name以及class获取Bean
*/
public static <T> T getBean(String name, Class<T> clazz) {
return applicationContext.getBean(name, clazz);
}
}
五、转换器
1、WhProjectExcelConverter.java
项目id与项目名称互相转化
public class WhProjectExcelConverter implements Converter<String> {
private final WhProjectService whProjectService = SpringContextUtil.getBean(WhProjectService.class);
private final List<WhProject> list = whProjectService.list();
@Override
public Class<?> supportJavaTypeKey() {
//实体类对象属性类型
return String.class;
}
/**
* 将单元格中数据转化为java对象属性
* @param context
* @return
* @throws Exception
*/
@Override
public String convertToJavaData(ReadConverterContext<?> context) throws Exception {
//创建集合用于存储项目id和名称对应关系
Map<String,String> mapToJavaData = new HashMap<>();
//遍历项目列表将项目id和名称放入map
for (WhProject sysDeptEntity : list) {
mapToJavaData.put(sysDeptEntity.getProjectName(),sysDeptEntity.getId());
}
//从cellData中读取数据 转换为实体类中的对象数值
return mapToJavaData.get(context.getReadCellData().getStringValue());
}
/**
* 将java对象转化为excel单元格数据
* @param context
* @return
* @throws Exception
*/
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {
Map<String,String> mapToExcelData = new HashMap<>();
for (WhProject sysDeptEntity : list) {
mapToExcelData.put(sysDeptEntity.getId(),sysDeptEntity.getProjectName());
}
//将java属性转换为excel对应属性类型
return new WriteCellData<>(mapToExcelData.get(context.getValue()));
}
}
2、WhWorkTypeExcelConverter.java
int类型type值与String类型type名称相互转化
public class WhWorkTypeExcelConverter implements Converter<Integer> {
private final WhDictItemService whDictItemService = SpringContextUtil.getBean(WhDictItemService.class);
private final List<WhDictItem> list = whDictItemService.listByCode(WORK_TYPE);
@Override
public Class<?> supportJavaTypeKey() {
//实体类对象属性类型
return Integer.class;
}
/**
* 将单元格中数据转化为java对象属性
* @param context
* @return
* @throws Exception
*/
@Override
public Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {
//创建集合用于存储部门id和名称对应关系
Map<String,String> mapToJavaData = new HashMap<>();
//遍历数据字典将属性名称和属性值放入map
for (WhDictItem sysDeptEntity : list) {
mapToJavaData.put(sysDeptEntity.getItemName(),sysDeptEntity.getItemValue());
}
//从cellData中读取数据 转换为实体类中的对象数值
return Integer.valueOf(mapToJavaData.get(context.getReadCellData().getStringValue()));
}
/**
* 将java对象转化为excel单元格数据
* @param context
* @return
* @throws Exception
*/
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
Map<String,String> mapToExcelData = new HashMap<>();
for (WhDictItem sysDeptEntity : list) {
mapToExcelData.put(sysDeptEntity.getItemValue(),sysDeptEntity.getItemName());
}
//将java属性转换为excel对应属性类型
return new WriteCellData<>(mapToExcelData.get(context.getValue()));
}
}
六、Handler类
1、SelectedSheetWriteHandler.java
设置下拉列表相关
@Data
public class SelectedSheetWriteHandler implements SheetWriteHandler {
/**
* 构建下拉选的map
*/
private final Map<Integer, WhExcelSelectedResolve> selectedMap;
private final int columnSelectMaxLength = 255;
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
selectedMap.forEach((k, v) -> {
// 设置下拉列表的行： 首行，末行，首列，末列
CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
// 设置下拉列表的值
DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(validation);
});
}
}
2、CustomSheetWriteHandler.java
设置文本格式、表格式
public class CustomSheetWriteHandler implements SheetWriteHandler {
// 设置100列column
private static final Integer COLUMN = 100;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public 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)) {
// 设置为文本格式
CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();
// 49为文本格式
cellStyle.setDataFormat((short) 49);
// i为列，一整列设置为文本格式
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类
@Data
@Slf4j
public class WhExcelSelectedResolve {
/**
* 下拉内容
*/
private String[] source;
/**
* 设置下拉框的起始行，默认为第二行
*/
private int firstRow;
/**
* 设置下拉框的结束行，默认为最后一行
*/
private int lastRow;
public String[] resolveSelectedSource(ExcelSelected excelSelected) {
if (excelSelected == null) {
return null;
}
// 获取固定下拉框的内容
String[] source = excelSelected.source();
if (source.length > 0) {
return source;
}
// 获取动态下拉框的内容
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("解析动态下拉框数据异常", e);
}
}
return null;
}
}
八、实体类
@Data
@Schema(description = "项目随行人员表")
@HeadRowHeight(30)
@ContentRowHeight(18)
public class WhProjectPeopleVO {
@ExcelIgnore
@Schema(description = "id")
private String id;
@ExcelIgnore
@Schema(description = "人员类型 0随行人员1负责人 默认0")
private Integer type;
@ExcelProperty(value = "姓名", index = 0)
@Schema(description = "姓名")
private String name;
@ColumnWidth(20)
@TextColumn(index = 1)
@ExcelProperty(value = "身份证号", index = 1)
@Schema(description = "身份证号")
private String identityId;
@ColumnWidth(20)
@ExcelProperty(value = "手机号", index = 2)
@Schema(description = "手机号")
private String mobile;
@ColumnWidth(20)
@ExcelSelected(sourceClass = WhProjectSelect.class)
@ExcelProperty(value = "所属项目", index = 3, converter = WhProjectExcelConverter.class)
@Schema(description = "项目id")
private String projectId;
@ExcelIgnore
@Schema(description = "微信openId")
private String openId;
@ExcelIgnore
@Schema(description = "微信unionId")
private String unionId;
@ExcelIgnore
@Schema(description = "开始时间")
private Date startTime;
@ExcelIgnore
@Schema(description = "结束时间")
private Date endTime;
@ExcelIgnore
@Schema(description = "项目名称")
private String projectName;
@ExcelIgnore
@Schema(description = "最近入场时间")
private Date lastArrivalTime;
@ExcelSelected(sourceClass = WhWorkTypeSelect.class)
@ExcelProperty(value = "工种", index = 4, converter = WhWorkTypeExcelConverter.class)
@Schema(description = "工种")
private Integer workType;
}
测试结果
![[AI] 深度学习的“黑箱”探索：从解释性到透明性](https://i-blog.csdnimg.cn/direct/9db1e601fa0747798dd343cd4c2958a5.jpeg)
