各层代码如下，有疑问评论区欢迎讨论~~
- controller
/**
* 导入Excel
*
* @param file 导入文件
* @return
*/
@PostMapping("/uploadExcel")
public ActionResult uploadExcel(MultipartFile file) {
Assert.notNull(file, "导入文件不能为空");
//获取文件名后缀
String extension = FileUtil.extName(file.getOriginalFilename());
if ("xlsx".equals(extension) || "xls".equals(extension)) {
return ok(service.uploadExcel(file));
} else {
return badRequest("请导入正确的文件类型");
}
}
- serviceimpl
/**
* 导入Excel
*
* @param file 导入文件
* @return
*/
@Override
public List<DrillingHoleParametersDto> uploadExcel(MultipartFile file) {
// 一个文件一个reader
ExcelReader excelReader = null;
//返给前端的对象list
List<DrillingHoleParametersDto> resultList = new ArrayList<>();
try {
//extraRead,额外信息，可以处理超链接，批注和合并单元格
excelReader = EasyExcelFactory.read(file.getInputStream()).extraRead(CellExtraTypeEnum.MERGE).build();
// 不同的功能必须用不同的Listener（需要自己建）
DrillingUploadExcelRoofTrendListener listener = new DrillingUploadExcelRoofTrendListener(modelMapper, resultList);
//处理第一个sheet，注意：隐藏的sheet也会读到！！！
ReadSheet drillingSheet = EasyExcelFactory.readSheet(0).headRowNumber(3).head(DrillingUploadExcelRoofTrend.class)
.registerReadListener(listener).build();
// 获取所有的合并单元格
List<CellExtra> mergeInfoList = listener.getExtraMergeInfoList();
// 解析合并单元格并赋值到对于的表数据中
EasyExcelUtil.explainMergeData(resultList, mergeInfoList, 3);
// 读取一个sheet
excelReader.read(drillingSheet);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (excelReader != null) {
// 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
excelReader.finish();
}
}
return resultList;
}
- DrillingUploadExcelRoofTrendListener
/**
* @Description 顶板走向钻孔导入Listener
*/
public class DrillingUploadExcelRoofTrendListener implements ReadListener<DrillingUploadExcelRoofTrend> {
private final List<DrillingUploadExcelRoofTrend> roofTrendList;
/**
* -- GETTER --
* 获取合并单元格
*/
// 合并单元格
@Getter
private final List<CellExtra> extraMergeInfoList = new ArrayList<>();
public DrillingUploadExcelRoofTrendListener(List<DrillingUploadExcelRoofTrend> roofTrendList) {
this.roofTrendList = roofTrendList;
}
@Override
public void invoke(DrillingUploadExcelRoofTrend drillingUploadExcelRoofTrend, AnalysisContext context) {
//ObjectIsNullUtil是自定义的判断工具类
if (!ObjectIsNullUtil.checkObjectFieldIsNull(drillingUploadExcelRoofTrend)) {
//ValidatorUtils验证约束的工具类
ValidatorUtils.validateExcelEntity(drillingUploadExcelRoofTrend, context);
String sealTime;
try {
//EasyExcelUtil是处理数据的工具类，此处用于格式化时间，可以自行修改
sealTime = EasyExcelUtil.matchSealTime(drillingUploadExcelRoofTrend.getSealTime());
} catch (ParseException e) {
throw new RuntimeException(e);
}
drillingUploadExcelRoofTrend.setSealTime(sealTime);
roofTrendList.add(drillingUploadExcelRoofTrend);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
//获取单元格类型
CellExtraTypeEnum type = extra.getType();
if (Objects.requireNonNull(type) == CellExtraTypeEnum.MERGE) {
extraMergeInfoList.add(extra);
}
}
}
- ObjectIsNullUtil
@Slf4j
public class ObjectIsNullUtil {
private ObjectIsNullUtil() {
}
/**
* 判断对象中属性值是否全为空，需要提出个别的字段进行判断
*/
public static <T> boolean checkObjectFieldIsNull(T t, String ignoreFileName) {
if (t == null) {
log.error("The target object is empty.");
return true;
}
try {
for (Field field : t.getClass().getDeclaredFields()) {
field.setAccessible(true);
if (!field.getName().equals(ignoreFileName) && !ObjectUtils.isEmpty(field.get(t))) {
log.error("The property in the object is not null. attribute name: {}", field.getName());
return false;
}
}
} catch (Exception e) {
log.error("Object and attribute values failed. error: {}", e.getMessage());
return false;
}
return true;
}
public static <T> boolean checkObjectFieldIsNull(T t) {
return checkObjectFieldIsNull(t, "orderNumber");
}
}
- ValidatorUtils
public class ValidatorUtils {
private static final Validator VALIDATOR;
static {
VALIDATOR = Validation.buildDefaultValidatorFactory().getValidator();
}
/**
* 验证方法
* <p>
* 同一个pojo类，可能会被多个controller使用验证，而每个controller的验证规则有不同，
* 这是就需要分组验证，其实就是几个要分组的空接口，指定属性A属于哪个组，属性B又属于
* 哪个组，这样在controller验证时就指定我要验证哪个组
* </p>
*
* @param t 被校验的对象
* @param groups 被校验的组
* @throws ValidationException 校验不通过抛出自定义异常
*/
public static <T> void validateEntity(T t, Class<?>... groups) throws ValidationException {
// 用验证器执行验证，返回一个违反约束的set集合
Set<ConstraintViolation<Object>> violationSet = VALIDATOR.validate(t, groups);
// 判断是否为空，空：说明验证通过，否则就验证失败
if (!violationSet.isEmpty()) {
// 获取第一个验证失败的属性
ConstraintViolation<Object> violation = violationSet.iterator().next();
// 抛出自定义异常
throw new ValidationException(violation.getMessage());
}
}
public static <T> void validateExcelEntity(T t, AnalysisContext context) throws ValidationException {
// 用验证器执行验证，返回一个违反约束的set集合
Set<ConstraintViolation<Object>> violationSet = VALIDATOR.validate(t);
// 判断是否为空，空：说明验证通过，否则就验证失败
if (!violationSet.isEmpty()) {
// 获取第一个验证失败的属性
ConstraintViolation<Object> violation = violationSet.iterator().next();
// 抛出自定义异常
String message = "不规则数据位置:"
+ "sheet页名称：【" + context.readSheetHolder().getSheetName() + "】 \r\n"
+ "行号：" + (context.readSheetHolder().getRowIndex() + 1) + "\r\n"
+ "错误提示：" + violation.getMessage();
throw new ValidationException(message);
}
}
}
- EasyExcelUtil
@Slf4j
public class EasyExcelUtil {
/**
* 处理合并单元格
*
* @param data 解析数据
* @param extraMergeInfoList 合并单元格信息
* @param headRowNumber 起始行
* @return 填充好的解析数据
*/
public static <T> List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
// 循环所有合并单元格信息
extraMergeInfoList.forEach(cellExtra -> {
//跳过标题行的合并单元格
if (cellExtra.getFirstRowIndex() >= headRowNumber) {
int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
int firstColumnIndex = cellExtra.getFirstColumnIndex();
int lastColumnIndex = cellExtra.getLastColumnIndex();
// 获取初始值
Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
// 设置值
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
setInitValueToList(initValue, i, j, data);
}
}
}
});
return data;
}
/**
* 设置合并单元格的值
*
* @param filedValue 值
* @param rowIndex 行
* @param columnIndex 列
* @param data 解析数据
*/
private static <T> void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
if (rowIndex >= data.size()) return;
T object = data.get(rowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
// 提升反射性能，关闭安全检查
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == columnIndex) {
try {
field.set(object, filedValue);
break;
} catch (IllegalAccessException e) {
log.error("设置合并单元格的值异常：{}", e.getMessage());
}
}
}
}
}
/**
* 获取合并单元格的初始值
* rowIndex对应list的索引
* columnIndex对应实体内的字段
*
* @param firstRowIndex 起始行
* @param firstColumnIndex 起始列
* @param data 列数据
* @return 初始值
*/
private static <T> Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
Object filedValue = null;
T object = data.get(firstRowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
// 提升反射性能，关闭安全检查
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == firstColumnIndex) {
try {
filedValue = field.get(object);
break;
} catch (IllegalAccessException e) {
log.error("设置合并单元格的初始值异常：{}", e.getMessage());
}
}
}
}
return filedValue;
}
/**
* 格式化日期为YYYY-MM-DD
*
* @param sealTime
* @return
*/
public static String matchSealTime(String sealTime) throws ParseException {
//提取sealtime中的日期字符串
String patternString = "(\\d{4}([-/.])\\d{1,2}([-/.])\\d{1,2})"; // 匹配YYYY-MM-DD YYYY.MM.DD YYYY/MM/DD格式的日期
Pattern pattern = Pattern.compile(patternString);
Matcher matcher = pattern.matcher(sealTime);
//匹配到日期字符串
if (matcher.find()) {
String desiredFormat = "yyyy-MM-dd"; // 目标格式
// 创建一个SimpleDateFormat对象来解析原始格式的日期
SimpleDateFormat originalFormat = null;
// 判断日期的分隔符
String patternString1 = "(\\d{4})([-/.])(\\d{1,2})([-/.])(\\d{1,2})"; // 匹配常见的日期格式
Pattern pattern1 = Pattern.compile(patternString1);
Matcher matcher1 = pattern1.matcher(matcher.group(1));
//匹配到日期分隔符
if (matcher1.matches()) {
String separator = matcher.group(2);
switch (separator) {
case ".":
originalFormat = new SimpleDateFormat("yyyy.MM.dd");
break;
case "/":
originalFormat = new SimpleDateFormat("yyyy/MM/dd");
break;
case "-":
originalFormat = new SimpleDateFormat("yyyy-MM-dd");
break;
}
assert originalFormat != null;
// 解析字符串为Date对象
Date date = originalFormat.parse(matcher.group(1));
// 使用目标格式创建另一个SimpleDateFormat对象来格式化日期
SimpleDateFormat targetFormat = new SimpleDateFormat(desiredFormat);
// 将Date对象格式化为目标字符串格式
return targetFormat.format(date);
}
}
return "";
}
}
957
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
