前言
对于网页上面的一些导入excel功能,我们尽量是需要给用户固定的excel模板才能将模板里面的数据跟我们的系统内容所对应起来,而且有些字段的输入内容的是需要我们固定好只能在我们的给定的内容选择输入,如遇到这种情况,那么我们就需要适当限制用户的随机自己输入,将固定好的选项固定好在下拉框让用户自行选择。
一、项目需求
需要让客户导入excel将数据存入我们系统,但是为了一些特定字段又不能让用户胡乱输入,所以我们得加下限制。
比如,在导入的excel中有如下一些字段是需要加以限制的:
- 分类字段(A类,B类,C类),但是在系统中我是以一个字段存取的,1代表A,2代表B,3代表C
- 接触类型字段,这个字段在系统中是通过全局字典自行定义的,所以在一条数据中存的是对应的接触类型的字典的id
- 是否佩戴口罩字段,同1
二、需求分析
导出excel我们可以使用easyExcel,用过easyExcel的应该知道,在导出的时候可以添加一些处理器进行我们上述需求的处理。
我们可以自定义一个注解,用来放在需要固定下拉框的字段上面,然后获取到相应的下拉框的值在处理器里面将值复制进去。
三、 代码实现导出模板
- 引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
- 自定义注解,下拉框的内容大致分为三类,我们都做了处理
- 固定下拉,我们直接写死的内容
- 通过一个字典id在字典表获取内容放在下拉
- 有些特殊情况需要在excel里面选择单位或者人员,我们需要将单位或者人员放在下拉,这些都是固定的直接根据sql查询到放进去即可
import java.lang.annotation.*;
/**
* @author bingshao
* @date 2022/3/14
**/
@Documented
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownField {
/**
* 固定下拉内容
*/
String[] source() default {};
/**
* 动态下拉内容,标识是DICT还是DB或其他;1:dict,2:db
*/
String type() default "1";
/**
* 若是dict则需要传入dictId
*/
String dictId() default "";
/**
* 若是db,则需要传入db获取的数据类型,1:人员,2:部门
*/
String dataType() default "1";
}
- 注解的实现类,到时候excel所有的下拉框都是 value-id 形式,value是为了让用户选择的时候看的清楚,id是为了跟我们的系统关联起来,导入的时候根据-截取前后内容获取id进行业务操作即可
@Component
public class ResolveDropAnnotationImpl implements ResolveDropAnnotation {
@Resource
private SystemFeign systemFeign;
@Resource
private DictItemMapper dictItemMapper;
@Override
public String[] resolve(DropDownField dropDownField) {
Assert.notNull(LoginUserHolder.getCurrentUser(), "请登录用户");
if (!Optional.ofNullable(dropDownField).isPresent()) {
return new String[0];
}
/**
* 固定下拉数据
*/
String[] source = dropDownField.source();
if (source.length > 0) {
return source;
}
/**
* 动态下拉数据
*/
Assert.isTrue(StringUtils.isNotBlank(dropDownField.type()), "请确认下拉框标识!");
String type = dropDownField.type();
if ("1".equals(type)) {
Assert.isTrue(StringUtils.isNotBlank(dropDownField.dictId()), "请传入字典id!");
List<DictItemVo> dictItemVoList = dictItemMapper.getDictItemListByDictId(Long.valueOf(dropDownField.dictId()));
if (CollectionUtil.isNotEmpty(dictItemVoList)) {
List<String> list = new LinkedList<>();
dictItemVoList.forEach(dictItemVo -> {
StringBuilder sb = new StringBuilder();
sb.append(dictItemVo.getDictValue());
sb.append("-");
sb.append(dictItemVo.getId());
list.add(sb.toString());
});
return list.toArray(new String[0]);
}
}
if ("2".equals(type)) {
Assert.isTrue(StringUtils.isNotBlank(dropDownField.dataType()), "请传入数据类型!");
Long deptId = Long.valueOf(LoginUserHolder.getCurrentUser().getDeptId());
Long id = LoginUserHolder.getCurrentUser().getId();
if ("1".equals(dropDownField.dataType())) {
//获取人员信息
Response<List<UserNameVo>> allUserData = systemFeign.getAllUserByDeptId(deptId);
if (CollectionUtil.isNotEmpty(allUserData.getData())) {
List<String> list = new LinkedList<>();
allUserData.getData().forEach(userNameVo -> {
StringBuilder sb = new StringBuilder();
sb.append(userNameVo.getName());
sb.append("-");
sb.append(userNameVo.getId());
list.add(sb.toString());
});
return list.toArray(new String[0]);
}
}
if ("2".equals(dropDownField.dataType())) {
//获取单位信息
}
}
return new String[0];
}
}
- 导出模板表头所对应的实体类设置,可以看下实体类所用的我们自定义注解的用法
@Data
public class CloseCasesTemplate implements Serializable {
private static final long serialVersionUID = 6845341493853321272L;
@ExcelProperty(value = "归属风险点", index = 0)
@ColumnWidth(15)
private String location;
@ExcelProperty(value = "序号", index = 1)
@ColumnWidth(15)
private String no;
@ExcelProperty(value = "归属确证病例", index = 2)
@ColumnWidth(15)
private String casesId;
@ExcelProperty(value = "密接名称", index = 3)
@ColumnWidth(15)
private String name;
@ExcelProperty(value = "联系电话", index = 4)
@ColumnWidth(15)
private String phone;
@ExcelProperty(value = "身份证号码", index = 5)
@ColumnWidth(20)
private String credentialsNo;
@ExcelProperty(value = "现住址", index = 6)
@ColumnWidth(25)
private String address;
@ExcelProperty(value = "工作单位", index = 7)
@ColumnWidth(15)
private String deptName;
@ExcelProperty(value = "分类(A类、B类及C类)", index = 8)
@ColumnWidth(15)
@DropDownField(source = {"A类-1", "B类-2", "C类-3"})
private String classification;
@ExcelProperty(value = "接触类型", index = 9)
@ColumnWidth(25)
@DropDownField(dictId = "1561885839184347138")
private String touchType;
@ExcelProperty(value = "与密接关系", index = 10)
@ColumnWidth(15)
private String relation;
@ExcelProperty(value = "首次暴露时间", index = 11)
@ColumnWidth(15)
private String firstExposed;
@ExcelProperty(value = "末次暴露时间", index = 12)
@ColumnWidth(15)
private String lastestExposed;
@ExcelProperty(value = "接触时长", index = 13)
@ColumnWidth(15)
private String touchDuration;
@ExcelProperty(value = "接触地点", index = 14)
@ColumnWidth(15)
private String touchPlace;
@ExcelProperty(value = "防护情况(是否佩戴口罩)", index = 15)
@ColumnWidth(15)
@DropDownField(source = {"是-1", "否-0"})
private String protectionSituation;
@ExcelProperty(value = "管控时间", index = 16)
@ColumnWidth(15)
private String controlTime;
@ExcelProperty(value = "管控地点", index = 17)
@ColumnWidth(15)
private String controlPlace;
}
- 方法的使用
public void downloadCloseTemplate(HttpServletResponse response) throws Exception {
String fileName = URLEncoder.encode("closeTemplate.xlsx", StandardCharsets.UTF_8.toString());
try {
// 获取该类声明的所有字段
Field[] fields = CloseCasesTemplate.class.getDeclaredFields();
// 响应字段对应的下拉集合
Map<Integer, String[]> map = new HashMap<>(8);
Field field;
// 循环判断哪些字段有下拉数据集,并获取
for (int i = 0; i < fields.length; i++) {
field = fields[i];
// 解析注解信息
DropDownField dropDownField = field.getAnnotation(DropDownField.class);
if (null != dropDownField) {
String[] sources = resolveDropAnnotation.resolve(dropDownField);
if (null != sources && sources.length > 0) {
map.put(i, sources);
}
}
}
response.setContentType("application/force-download");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
EasyExcel.write(response.getOutputStream(), CloseCasesTemplate.class)
.autoCloseStream(true)
.registerWriteHandler(new SelectSheetWriteHandler(map))
.excelType(ExcelTypeEnum.XLSX)
.sheet("密接模板")
.doWrite(new LinkedList());
} catch (Throwable t) {
log.error("导出模板异常!");
throw t;
}
}
- excel字段处理器SelectSheetWriteHandler
public class SelectSheetWriteHandler implements SheetWriteHandler {
private final Map<Integer, String[]> map;
public SelectSheetWriteHandler(Map<Integer, String[]> map) {
this.map = map;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// k 为存在下拉数据集的单元格下表 v为下拉数据集
map.forEach((k, v) -> {
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k - 1, k - 1);
//定义sheet的名称
//1.创建一个隐藏的sheet 名称为 hidden + k
String sheetName = "hidden" + k;
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet hiddenSheet = workbook.createSheet(sheetName);
for (int i = 0, length = v.length; i < length; i++) {
// 开始的行数i,列数k
hiddenSheet.createRow(i).createCell(k).setCellValue(v[i]);
}
Name category1Name = workbook.createName();
category1Name.setNameName(sheetName);
String excelLine = getExcelLine(k);
// =hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组
String refers = "=" + sheetName + "!$" + excelLine + "$1:$" + excelLine + "$" + (v.length + 1);
// 将刚才设置的sheet引用到你的下拉列表中
DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
DataValidation dataValidation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.setShowErrorBox(true);
dataValidation.setSuppressDropDownArrow(true);
dataValidation.createErrorBox("提示", "此值与单元格定义格式不一致");
writeSheetHolder.getSheet().addValidationData(dataValidation);
// 设置存储下拉列值得sheet为隐藏
int hiddenIndex = workbook.getSheetIndex(sheetName);
if (!workbook.isSheetHidden(hiddenIndex)) {
workbook.setSheetHidden(hiddenIndex, true);
}
});
}
/**
* 返回excel列标A-Z-AA-ZZ
*
* @param num 列数
* @return java.lang.String
*/
private String getExcelLine(int num) {
String line = "";
int first = num / 26;
int second = num % 26;
if (first > 0) {
line = (char) ('A' + first - 1) + "";
}
line += (char) ('A' + second) + "";
return line;
}
}
- 导出结果展示,表头太长展示不下,贴图展示部分内容
![在这里插入图片描述](https://img-blog.csdnimg.cn/6180b8e79b594d4890f7a8a6bd9b850a.png
四、代码导入excel
- 方法
@Resource
private CasesService casesServiceImpl;
public void importCloseCases(Long casesId, MultipartFile file) throws IOException {
Cases cases = casesMapper.selectById(casesId);
Assert.isNotNull(cases, "数据错误,无此个案信息");
Assert.isNotNull(LoginUserHolder.getCurrentUser(), "登录过期");
EasyExcel.read(file.getInputStream(), ImportCloseCasesExcelVo.class, new CloseCasesListener(casesServiceImpl, cases.getId(), cases.getTaskId(), cases.getEventId())).sheet().doRead();
}
- ImportCloseCasesExcelVo实体类
@Data
@ApiModel("导入密接")
public class ImportCloseCasesExcelVo implements Serializable {
private static final long serialVersionUID = -1290783546422626610L;
@ExcelProperty(value = "归属风险点")
private String location;
@ExcelProperty(value = "序号")
private String no;
@ExcelProperty(value = "归属确证病例")
private String casesId;
@ExcelProperty(value = "密接名称")
private String name;
@ExcelProperty(value = "联系电话")
private String phone;
@ExcelProperty(value = "身份证号码")
private String credentialsNo;
@ExcelProperty(value = "现住址")
private String address;
@ExcelProperty(value = "工作单位")
private String deptName;
@ExcelProperty(value = "分类(A类、B类及C类)")
private String classification;
@ExcelProperty(value = "接触类型")
private String touchType;
@ExcelProperty(value = "与密接关系")
private String relation;
@ExcelProperty(value = "首次暴露时间")
private String firstExposed;
@ExcelProperty(value = "末次暴露时间")
private String lastestExposed;
@ExcelProperty(value = "接触时长")
private String touchDuration;
@ExcelProperty(value = "接触地点")
private String touchPlace;
@ExcelProperty(value = "防护情况(是否佩戴口罩)")
private String protectionSituation;
@ExcelProperty(value = "管控时间")
private String controlTime;
@ExcelProperty(value = "管控地点")
private String controlPlace;
}
- CloseCasesListener监听器
@Data
public class CloseCasesListener extends AnalysisEventListener<ImportCloseCasesExcelVo> {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
private List<ImportCloseCasesExcelVo> importCloseCasesExcelVoList = new LinkedList<>();
private CasesService casesService;
private Long id;
private Long taskId;
private Long eventId;
public CloseCasesListener(CasesService casesService, Long id, Long taskId, Long eventId) {
this.casesService = casesService;
this.id = id;
this.taskId = taskId;
this.eventId = eventId;
}
@Override
public void invoke(ImportCloseCasesExcelVo importCloseCasesExcelVo, AnalysisContext analysisContext) {
if (importCloseCasesExcelVo != null) {
importCloseCasesExcelVoList.add(importCloseCasesExcelVo);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
}
private void saveData() {
if (CollectionUtil.isNotEmpty(importCloseCasesExcelVoList)) {
List<Cases> casesList = new LinkedList<>();
importCloseCasesExcelVoList.forEach(importCloseCasesExcelVo -> {
Cases cases = new Cases();
cases.setId(SnowFlakeUtil.getId());
cases.setDeleted(false);
cases.setCreateTime(new Date());
cases.setInvestigateDate(new Date());
cases.setParentId(id);
cases.setTaskId(taskId);
cases.setEventId(eventId);
cases.setName(importCloseCasesExcelVo.getName());
cases.setDeptId(Long.valueOf(LoginUserHolder.getCurrentUser().getDeptId()));
cases.setPhone(importCloseCasesExcelVo.getPhone());
cases.setCredentialsNo(importCloseCasesExcelVo.getCredentialsNo());
cases.setAddress(importCloseCasesExcelVo.getAddress());
cases.setDeptAddress(importCloseCasesExcelVo.getDeptName());
cases.setCreateBy(LoginUserHolder.getCurrentUser().getId());
cases.setCredentialsType(Constants.credentialsType);
if (StringUtil.isNotBlank(importCloseCasesExcelVo.getClassification())) {
String[] split = importCloseCasesExcelVo.getClassification().split("-");
cases.setClassification(split[1]);
}
if (StringUtil.isNotBlank(importCloseCasesExcelVo.getTouchType())) {
String[] split = importCloseCasesExcelVo.getTouchType().split("-");
cases.setTouchType(Long.valueOf(split[1]));
}
cases.setRelation(importCloseCasesExcelVo.getRelation());
try {
cases.setFirstExposed(StringUtil.isNotBlank(importCloseCasesExcelVo.getFirstExposed()) ? sdf.parse(importCloseCasesExcelVo.getFirstExposed()) : null);
} catch (ParseException e) {
e.printStackTrace();
}
try {
cases.setLastestExposed(StringUtil.isNotBlank(importCloseCasesExcelVo.getLastestExposed()) ? sdf.parse(importCloseCasesExcelVo.getLastestExposed()) : null);
} catch (ParseException e) {
e.printStackTrace();
}
cases.setTouchDuration(importCloseCasesExcelVo.getTouchDuration());
cases.setTouchPlace(importCloseCasesExcelVo.getTouchPlace());
if (StringUtil.isNotBlank(importCloseCasesExcelVo.getProtectionSituation())) {
String[] split = importCloseCasesExcelVo.getProtectionSituation().split("-");
cases.setProtectionSituation(split[1]);
}
try {
cases.setControlTime(StringUtil.isNotBlank(importCloseCasesExcelVo.getControlTime()) ? sdf.parse(importCloseCasesExcelVo.getControlTime()) : null);
} catch (ParseException e) {
e.printStackTrace();
}
cases.setControlPlace(importCloseCasesExcelVo.getControlPlace());
Cases casesPo = casesService.getCasesPoById(id);
cases.setType("2");
cases.setCcType("1");
if ("1".equals(casesPo.getType())) {
cases.setType("2");
cases.setCcType("1");
}
if ("2".equals(casesPo.getType())) {
cases.setType("3");
cases.setCcType("0");
}
if ("3".equals(casesPo.getType())) {
cases.setType("3");
cases.setCcType("0");
}
casesList.add(cases);
});
casesService.saveBatch(casesList);
}
}
}
总结
难点就是处理器SelectSheetWriteHandler的写法,不太明白的请详细查看第五步和第六步的获取map和传递map的方法处理。