Poi4 读取导入Excel文件数据，动态匹配表头索引、验证表头必要性（V1）
时间：2024-6-7 16:56 作者：紫琪软件工作室 分类： POI
Poi4 读取导入Excel文件数据
实现概要
- 反射机制
- 动态匹配表头索引
- 验证表头必要性
- 返回失败记录信息、失败原因
- 泛型返回值
-
返回实体类内部自定义验证方法
关键依赖
- Apache Poi 4.1.2
- Fastjson2
-
自定义PoiExcel工具类（文末附）
表头JSON配置参考示例
{
"sheet": "Sheet1",
"baseInfo": {
"title": "设备设施基本信息",
"keyword": "基本信息",
"must": true,
"children": {
"facilityNum": {"title": "设备设施编号", "keyword": "编号", "must": true},
"facilityName": {"title": "设备设施名称", "keyword": "名称", "must": true},
"facilityTypeName": {"title": "设备设施类型", "keyword": "设施类型", "must": true},
"specification": {"title": "规格/型号", "keyword": "型号", "must": false},
"placeName": {"title": "区域/位置", "keyword": "位置", "must": true},
"remark": {"title": "备注", "keyword": "备注", "must": false}
}
},
"riskIdentInfo": {
"title": "风险辨识信息",
"keyword": "风险辨识",
"must": true,
"children": {
"nonCompliance": {"title": "危险有害因素", "keyword": "有害因素", "must": true},
"majorConsequences": {"title": "事故类型", "keyword": "事故类型", "must": true}
}
},
"fixedEvaluation": {
"title": "固有风险评价",
"keyword": "固有风险",
"must": true,
"children": {
"existingMeasures": {"title": "现有管控措施", "keyword": "现有管控", "must": true},
"riskAssMethods": {"title": "评价方法", "keyword": "评价方法", "must": true},
"existingLlm": {"title": "L/L/M", "keyword": "L/L/M", "must": true},
"existingSee": {"title": "S/E/E", "keyword": "S/E/E", "must": true},
"existingCs": {"title": "C/S", "keyword": "C/S", "must": true},
"existingRiskValue": {"title": "风险值", "keyword": "风险值", "must": false},
"existingRiskColor": {"title": "风险程度", "keyword": "风险程度", "must": false},
"existingRiskLevel": {"title": "风险等级", "keyword": "风险等级", "must": false}
}
},
"deptName": {
"title": "管控部门/单位",
"keyword": "管控部门/单位",
"must": true
},
"residueEvaluation": {
"title": "剩余风险评价",
"keyword": "剩余风险",
"must": true,
"children": {
"suggestedEngineeringMeasures": {"title": "建议新增管控措施", "keyword": "新增管控", "must": false},
"suggestedLlm": {"title": "L/L/M", "keyword": "L/L/M", "must": true},
"suggestedSee": {"title": "S/E/E", "keyword": "S/E/E", "must": true},
"suggestedCs": {"title": "C/S", "keyword": "C/S", "must": true},
"suggestedRiskValue": {"title": "风险值", "keyword": "风险值", "must": false},
"suggestedRiskColor": {"title": "风险程度", "keyword": "风险程度", "must": false},
"suggestedRiskLevel": {"title": "风险等级", "keyword": "风险等级", "must": false}
}
}
}测试用例 testRiskFacility
public static void testRiskFacility() throws IOException {
JSONObject modelHeaderConfig = JSONObject.parseObject("{\"sheet\":\"Sheet1\",\"baseInfo\":{\"title\":\"设备设施基本信息\",\"keyword\":\"基本信息\",\"must\":true,\"children\":{\"facilityNum\":{\"title\":\"设备设施编号\",\"keyword\":\"编号\",\"must\":true},\"facilityName\":{\"title\":\"设备设施名称\",\"keyword\":\"名称\",\"must\":true},\"facilityTypeName\":{\"title\":\"设备设施类型\",\"keyword\":\"设施类型\",\"must\":true},\"specification\":{\"title\":\"规格/型号\",\"keyword\":\"型号\",\"must\":false},\"placeName\":{\"title\":\"区域/位置\",\"keyword\":\"位置\",\"must\":true},\"remark\":{\"title\":\"备注\",\"keyword\":\"备注\",\"must\":false}}},\"riskIdentInfo\":{\"title\":\"风险辨识信息\",\"keyword\":\"风险辨识\",\"must\":true,\"children\":{\"nonCompliance\":{\"title\":\"危险有害因素\",\"keyword\":\"有害因素\",\"must\":true},\"majorConsequences\":{\"title\":\"事故类型\",\"keyword\":\"事故类型\",\"must\":true}}},\"fixedEvaluation\":{\"title\":\"固有风险评价\",\"keyword\":\"固有风险\",\"must\":true,\"children\":{\"existingEngineeringMeasures\":{\"title\":\"现有管控措施\",\"keyword\":\"现有管控\",\"must\":true},\"riskAssMethods\":{\"title\":\"评价方法\",\"keyword\":\"评价方法\",\"must\":true},\"existingLlm\":{\"title\":\"L/L/M\",\"keyword\":\"L/L/M\",\"must\":true},\"existingSee\":{\"title\":\"S/E/E\",\"keyword\":\"S/E/E\",\"must\":true},\"existingCs\":{\"title\":\"C/S\",\"keyword\":\"C/S\",\"must\":true},\"existingRiskValue\":{\"title\":\"风险值\",\"keyword\":\"风险值\",\"must\":false},\"existingRiskColor\":{\"title\":\"风险程度\",\"keyword\":\"风险程度\",\"must\":false},\"existingRiskLevel\":{\"title\":\"风险等级\",\"keyword\":\"风险等级\",\"must\":false}}},\"deptName\":{\"title\":\"管控部门/单位\",\"keyword\":\"管控部门/单位\",\"must\":true},\"residueEvaluation\":{\"title\":\"剩余风险评价\",\"keyword\":\"剩余风险\",\"must\":true,\"children\":{\"suggestedEngineeringMeasures\":{\"title\":\"建议新增管控措施\",\"keyword\":\"新增管控\",\"must\":false},\"suggestedLlm\":{\"title\":\"L/L/M\",\"keyword\":\"L/L/M\",\"must\":true},\"suggestedSee\":{\"title\":\"S/E/E\",\"keyword\":\"S/E/E\",\"must\":true},\"suggestedCs\":{\"title\":\"C/S\",\"keyword\":\"C/S\",\"must\":true},\"suggestedRiskValue\":{\"title\":\"风险值\",\"keyword\":\"风险值\",\"must\":false},\"suggestedRiskColor\":{\"title\":\"风险程度\",\"keyword\":\"风险程度\",\"must\":false},\"suggestedRiskLevel\":{\"title\":\"风险等级\",\"keyword\":\"风险等级\",\"must\":false}}}}");
PoiExcel excel = new PoiExcel("E:\\qinghao\\工作资料\\风险点清单、隐患排查治理优化\\设备设施导入模版Temp新.xlsx");
verifyHeaderConfig(excel, modelHeaderConfig);
System.out.println("\nmodelHeaderConfig : \n" +modelHeaderConfig);
JSONObject validHeadIndexJson = handleValidHead(modelHeaderConfig);
System.out.println("\nvalidHeadIndexJson\n" + validHeadIndexJson);
List<RiskFacilityImportVo> riskFacilityList = new ArrayList<>();
try {
String errorMsg = handleImportData(excel, modelHeaderConfig, validHeadIndexJson, riskFacilityList, RiskFacilityImportVo.class);
System.out.println("\nerrorMsg: \n" + errorMsg);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("\nriskFacilityList : " + riskFacilityList.size());
for (RiskFacilityImportVo riskFacilityVo : riskFacilityList) {
System.out.println(riskFacilityVo);
}
excel.close();
}工具类ReadRiskInventoryExcelUtil 完整代码
package com.qinghao.riskManage.utils;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.qinghao.common.exception.UtilException;
import com.qinghao.common.utils.StringUtils;
import com.qinghao.common.utils.poi.PoiExcel;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.IOException;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;
/**
* @Author: asus
* @Date: 2024/6/7 9:05
* @Description: 读取导入清单Excel文件数据
* @Version: 1.0
*/
public class ReadRiskInventoryExcelUtil {
private ReadRiskInventoryExcelUtil() {}
/**
* 查找并验证标题
* @param excel excel 对象
* @param headerConfig 模板配置
*/
public static void verifyHeaderConfig(PoiExcel excel, JSONObject headerConfig) {
// 查找sheet
if(!headerConfig.containsKey("sheetIndex")) {
if (!headerConfig.containsKey("sheet")) {
throw new UtilException("模板配置中缺少sheetIndex或者sheet");
}
Integer sheetIndex = excel.getSheetIndexBySheetName(headerConfig.getString("sheet"));
headerConfig.put("sheetIndex", sheetIndex);
}
// 查找标题起始行
int headIndex = getHeadBeginIndex(excel, headerConfig);
headerConfig.put("headIndex", headIndex);
verifyFirstLevelHead(excel, headerConfig);
if(headerConfig.containsKey("haveSecondLevelHeadIndex") && headerConfig.getBoolean("haveSecondLevelHeadIndex")) {
verifySecondLevelHead(excel, headerConfig);
}
}
/**
* 查找标题起始行
* @param excel excel 对象
* @param headerConfig 模板配置
* @return 行号
* @throws UtilException 找不到表头抛出异常
*/
private static int getHeadBeginIndex(PoiExcel excel, JSONObject headerConfig) {
int sheetIndex = headerConfig.getInteger("sheetIndex");
int lastRowNum = excel.getSheet(sheetIndex).getLastRowNum();
lastRowNum = Math.min(lastRowNum, 10);
for (int i=0; i < lastRowNum; i++) {
String title = excel.getStringVal(sheetIndex, i, 0);
if (title.equals(headerConfig.getJSONObject("baseInfo").getString("title")) ||
title.equals(headerConfig.getJSONObject("riskIdentInfo").getString("title")) ||
title.equals(headerConfig.getJSONObject("fixedEvaluation").getString("title")) ||
title.equals(headerConfig.getJSONObject("riskIdentInfo").getString("title")) ||
title.equals(headerConfig.getJSONObject("fixedEvaluation").getString("title"))) {
return i;
}
if (title.contains(headerConfig.getJSONObject("baseInfo").getString("keyword")) ||
title.contains(headerConfig.getJSONObject("riskIdentInfo").getString("keyword")) ||
title.contains(headerConfig.getJSONObject("fixedEvaluation").getString("keyword")) ||
title.contains(headerConfig.getJSONObject("riskIdentInfo").getString("keyword")) ||
title.contains(headerConfig.getJSONObject("fixedEvaluation").getString("keyword"))) {
return i;
}
}
throw new UtilException("未能定位表头单元格，请检查导入文件表头格式！");
}
/**
* 验证一级表头
* @param excel excel 对象
* @param headerConfig 模板配置
* @throws UtilException 验证失败抛出异常
*/
private static void verifyFirstLevelHead(PoiExcel excel, JSONObject headerConfig) {
int sheetIndex = headerConfig.getInteger("sheetIndex");
int headIndex = headerConfig.getInteger("headIndex");
System.out.println("sheetIndex: " + sheetIndex);
System.out.println("headIndex: " + headIndex);
int secondLevelHeadIndex = -1;
Sheet sheet = excel.getSheet(sheetIndex);
Row row = sheet.getRow(headIndex);
// 工作簿全部合并单元格
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
// 获取一级表头信息
JSONArray firstLevelHeadInfo = getRowMergedRegionInfo(row, mergedRegions);
Set<String> headConfigKeySet = headerConfig.keySet();
boolean haveSecondLevelHead = false;
for (String key : headConfigKeySet) {
if (key.toLowerCase().contains("sheet") || key.toLowerCase().contains("index")) {
continue;
}
JSONObject headerConfigItem = headerConfig.getJSONObject(key);
String configTitle = headerConfigItem.getString("title");
String configKeyword = headerConfigItem.getString("keyword");
if(!haveSecondLevelHead && headerConfigItem.containsKey("children")) {
haveSecondLevelHead = true;
}
for (Object infoObj : firstLevelHeadInfo) {
JSONObject itemInfo = (JSONObject) infoObj;
if (itemInfo.getString("title").equals(configTitle) || itemInfo.getString("title").contains(configKeyword)) {
headerConfigItem.put("cellInfo", itemInfo);
secondLevelHeadIndex = Math.max(secondLevelHeadIndex, itemInfo.getInteger("lastRow"));
break;
}
}
}
// 验证必有表头
StringBuilder errorMsg = new StringBuilder();
for (String key : headConfigKeySet) {
if (key.toLowerCase().contains("index") || key.toLowerCase().contains("sheet")) {
continue;
}
if (headerConfig.getJSONObject(key).containsKey("must") &&
headerConfig.getJSONObject(key).getBoolean("must") &&
!headerConfig.getJSONObject(key).containsKey("cellInfo")) {
errorMsg.append(headerConfig.getJSONObject(key).getString("title")).append("、");
}
}
if (errorMsg.length() > 0) {
errorMsg.deleteCharAt(errorMsg.length() - 1);
throw new UtilException("导入文件缺少以下表头：" + errorMsg);
}
if (secondLevelHeadIndex < headIndex) {
throw new UtilException("未能定位表头单元格，请检查导入文件表头格式！");
}
if(secondLevelHeadIndex == headIndex) {
secondLevelHeadIndex++;
}
headerConfig.put("haveSecondLevelHeadIndex", haveSecondLevelHead);
if(haveSecondLevelHead) {
headerConfig.put("secondLevelHeadIndex", secondLevelHeadIndex);
} else {
headerConfig.put("dataIndex", secondLevelHeadIndex);
}
}
/**
* 验证二级表头
* @param excel excel 对象
* @param headerConfig 模板配置
* @throws UtilException 验证失败抛出异常
*/
private static void verifySecondLevelHead(PoiExcel excel, JSONObject headerConfig) {
int sheetIndex = headerConfig.getInteger("sheetIndex");
int secondLevelHeadIndex = headerConfig.getInteger("secondLevelHeadIndex");
System.out.println("secondLevelHeadIndex: " + secondLevelHeadIndex);
Sheet sheet = excel.getSheet(sheetIndex);
Row row = sheet.getRow(secondLevelHeadIndex);
// 工作簿全部合并单元格
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
// 获取行信息
JSONArray headInfo = getRowMergedRegionInfo(row, mergedRegions);
Set<String> headConfigKeySet = headerConfig.keySet();
for (String key : headConfigKeySet) {
if (key.toLowerCase().contains("sheet") || key.toLowerCase().contains("index")) {
continue;
}
JSONObject headerConfigItem = headerConfig.getJSONObject(key);
if (!headerConfigItem.containsKey("children")) {
continue;
}
JSONObject parentCellInfo = headerConfigItem.getJSONObject("cellInfo");
JSONObject children = headerConfigItem.getJSONObject("children");
Set<String> childrenKeySet = children.keySet();
for (String childrenKey : childrenKeySet) {
JSONObject childrenItem = children.getJSONObject(childrenKey);
String configTitle = childrenItem.getString("title");
String configKeyword = childrenItem.getString("keyword");
for (Object infoObj : headInfo) {
JSONObject itemInfo = (JSONObject) infoObj;
String itemInfoTitle = itemInfo.getString("title");
if(StringUtils.isEmpty(itemInfo)) {
continue;
}
if (itemInfoTitle.equals(configTitle) || itemInfoTitle.contains(configKeyword)) {
// 判断子标题是否在副标题中
if (itemInfo.getIntValue("firstCol") >= parentCellInfo.getIntValue("firstCol") && itemInfo.getIntValue("lastCol") <= parentCellInfo.getIntValue("lastCol")) {
childrenItem.put("cellInfo", itemInfo);
break;
}
}
}
}
}
// 验证必有表头
StringBuilder errorMsg = new StringBuilder();
for (String key : headConfigKeySet) {
if (key.toLowerCase().contains("sheet") || key.toLowerCase().contains("index")) {
continue;
}
JSONObject headerConfigItem = headerConfig.getJSONObject(key);
if (!headerConfigItem.containsKey("children")) {
continue;
}
StringBuilder childErrorMsg = new StringBuilder();
JSONObject children = headerConfigItem.getJSONObject("children");
Set<String> childrenKeySet = children.keySet();
for (String childrenKey : childrenKeySet) {
JSONObject childrenItem = children.getJSONObject(childrenKey);
if (childrenItem.containsKey("must") &&
childrenItem.getBoolean("must") &&
!childrenItem.containsKey("cellInfo")) {
childErrorMsg.append(childrenItem.getString("title")).append("、");
}
}
if (childErrorMsg.length() > 0) {
childErrorMsg.deleteCharAt(childErrorMsg.length() - 1);
errorMsg.append(headerConfigItem.getString("title")).append("【").append(childErrorMsg).append("】、");
}
}
if (errorMsg.length() > 0) {
errorMsg.deleteCharAt(errorMsg.length() - 1);
throw new UtilException("导入文件缺少以下表头：" + errorMsg);
}
headerConfig.put("dataIndex", secondLevelHeadIndex + 1);
System.out.println("dataIndex: " + (secondLevelHeadIndex + 1));
}
/**
* 获取行信息
* @param row 行
* @param mergedRegions 全部合并单元格
* @return 表头信息
*/
private static JSONArray getRowMergedRegionInfo(Row row, List<CellRangeAddress> mergedRegions) {
JSONArray result = new JSONArray();
short lastCellNum = row.getLastCellNum();
for (int i=0; i < lastCellNum; ) {
Cell cell = row.getCell(i);
if (cell == null) {
continue;
}
JSONObject mergedRegionInfo = getMergedRegionInfo(cell, mergedRegions);
result. add(mergedRegionInfo);
// 跳过合并单元格
i = mergedRegionInfo.getIntValue("lastCol") + 1;
}
return result;
}
/**
* 获取合并单元格信息
* @param cell 单元格
* @param mergedRegions 全部合并单元格
* @return 合并单元格信息
*/
private static JSONObject getMergedRegionInfo(Cell cell, List<CellRangeAddress> mergedRegions) {
JSONObject jsonObject = new JSONObject();
jsonObject.put("title", cell.getStringCellValue());
for (CellRangeAddress mergedRegion : mergedRegions) {
if (mergedRegion.isInRange(cell)) {
jsonObject.put("merged", true);
jsonObject.put("firstRow", mergedRegion.getFirstRow());
jsonObject.put("lastRow", mergedRegion.getLastRow());
jsonObject.put("firstCol", mergedRegion.getFirstColumn());
jsonObject.put("lastCol", mergedRegion.getLastColumn());
return jsonObject;
}
}
jsonObject.put("merged", false);
jsonObject.put("firstRow", cell.getRowIndex());
jsonObject.put("lastRow", cell.getRowIndex());
jsonObject.put("firstCol", cell.getColumnIndex());
jsonObject.put("lastCol", cell.getColumnIndex());
return jsonObject;
}
/**
* 处理有效表头
* @param headerConfig 表头配置
* @return JSONObject
*/
public static JSONObject handleValidHead(JSONObject headerConfig) {
JSONObject result = new JSONObject();
Set<String> firstLevelKeySet = headerConfig.keySet();
for (String firstLevelKey : firstLevelKeySet) {
if (firstLevelKey.toLowerCase().contains("sheet") || firstLevelKey.toLowerCase().contains("index")) {
continue;
}
JSONObject firstLevelItem = headerConfig.getJSONObject(firstLevelKey);
if (!firstLevelItem.containsKey("children")) {
if(firstLevelItem.containsKey("cellInfo") && firstLevelItem.getJSONObject("cellInfo").containsKey("firstCol")) {
result.put(firstLevelKey, JSONObject.of("title", firstLevelItem.getString("title"),
"index", firstLevelItem.getJSONObject("cellInfo").getIntValue("firstCol"),
"must", firstLevelItem.getBoolean("must")));
}
continue;
}
JSONObject children = firstLevelItem.getJSONObject("children");
Set<String> childrenKeySet = children.keySet();
for (String childrenKey : childrenKeySet) {
JSONObject childrenItem = children.getJSONObject(childrenKey);
if (childrenItem.containsKey("cellInfo") && childrenItem.getJSONObject("cellInfo").containsKey("firstCol")) {
result.put(childrenKey, JSONObject.of("title", childrenItem.getString("title"),
"index", childrenItem.getJSONObject("cellInfo").getIntValue("firstCol"),
"must", childrenItem.getBoolean("must"),
"parent", firstLevelItem.getString("title")));
}
}
}
return result;
}
/**
* 处理导入数据
* @param excel poiExcel
* @param headConfig 表头配置
* @param validHead 有效表头
* @param result 结果集
* @param clazz 实体类
* @param <T> 返回对象泛型
* @return List
*/
public static <T> String handleImportData(PoiExcel excel, JSONObject headConfig, JSONObject validHead, List<T> result, Class<T> clazz) throws Exception {
int sheetIndex = headConfig.getIntValue("sheetIndex");
int dataIndex = headConfig.getIntValue("dataIndex");
int endFlag = 0;
StringBuilder errorMsg = new StringBuilder();
while (true) {
String cellValue = excel.getStringVal(sheetIndex, dataIndex, 0);
// 结束判断
if (StringUtils.isEmpty(cellValue)) {
endFlag++;
if (endFlag > 5) {
break;
}
dataIndex++;
continue;
} else {
endFlag = 0;
}
T t = instantiate(clazz);
Set<String> property = validHead.keySet();
JSONObject rowErrorMsgJson = new JSONObject();
for (String prop : property) {
JSONObject propInfo = validHead.getJSONObject(prop);
Field field;
try {
field = clazz.getDeclaredField(prop);
} catch (NoSuchFieldException e) {
try {
field = clazz.getSuperclass().getDeclaredField(prop);
} catch (NoSuchFieldException e1) {
if(propInfo.getBoolean("must")) {
recordErrorMsg(rowErrorMsgJson, propInfo, "实体属性不存在、");
}
continue;
}
}
field.setAccessible(true);
Object val = excel.getObjVal(sheetIndex, dataIndex, propInfo.getIntValue("index"));
if (null != val) {
try {
setFieldValue(t, field, val);
} catch (Exception e) {
recordErrorMsg(rowErrorMsgJson, propInfo, "数据类型错误、");
}
}
if(null == val && propInfo.getBoolean("must")) {
try {
Method method = clazz.getMethod("differenceVerifyMust", String.class);
Object invoke = method.invoke(t, prop);
if (null == invoke || !(boolean) invoke) {
recordErrorMsg(rowErrorMsgJson, propInfo, "值为空、");
}
} catch (Exception ignored) {}
}
}
if (rowErrorMsgJson.isEmpty()) {
try {
Method method = clazz.getMethod("verifyCombinationMust");
method.invoke(t);
result.add(t);
} catch (InvocationTargetException ite) {
Throwable targetException = ite.getCause();
errorMsg.append("第").append(dataIndex + 1).append("行导入失败，失败原因【").append(targetException.getMessage()).append("】\n");
} catch (Exception ignored) {}
} else {
StringBuilder rowErrorMsg = new StringBuilder();
rowErrorMsgJson.forEach((k, v) -> {
if(!"msg".equals(k)) {
rowErrorMsg.append(k).append(" -> (");
rowErrorMsg.append(v.toString(), 0, v.toString().length() - 1).append(")；");
} else {
rowErrorMsg.append(v.toString(), 0, v.toString().length() - 1);
}
});
errorMsg.append("第").append(dataIndex + 1).append("行导入失败，失败原因【").append(rowErrorMsg).append("】\n");
}
dataIndex++;
}
return errorMsg.toString();
}
/**
* 记录错误信息
* @param rowErrorMsgJson 错误信息
* @param propInfo 属性信息
* @param msg 错误信息
*/
private static void recordErrorMsg(JSONObject rowErrorMsgJson, JSONObject propInfo, String msg) {
if(propInfo.containsKey("parent")) {
if(rowErrorMsgJson.containsKey(propInfo.getString("parent"))) {
rowErrorMsgJson.put(propInfo.getString("parent"), rowErrorMsgJson.getString(propInfo.getString("parent")) + propInfo.getString("title") + msg);
} else {
rowErrorMsgJson.put(propInfo.getString("parent"), propInfo.getString("title") + msg);
}
} else {
if(rowErrorMsgJson.containsKey("msg")) {
rowErrorMsgJson.put("msg", rowErrorMsgJson.getString("msg") + propInfo.getString("title") + msg);
} else {
rowErrorMsgJson.put("msg", propInfo.getString("title") + msg);
}
}
}
/**
* 使用反射根据给定的Class对象实例化泛型类型T的对象。
*
* @param clazz 要实例化的类型Class对象
* @param <T> 实例化的类型
* @return 类型T的一个新实例
* @throws Exception 反射操作可能抛出的异常
*/
public static <T> T instantiate(Class<T> clazz) throws Exception {
// 获取无参构造函数
Constructor<T> constructor = clazz.getDeclaredConstructor();
// 确保构造函数是可访问的（对于私有构造函数）
constructor.setAccessible(true);
// 实例化对象
return constructor.newInstance();
}
/**
* 设置字段的值
* @param t 实体类
* @param field 字段
* @param val 值
* @param <T> 实体类泛型
* @throws Exception 反射操作可能抛出的异常
*/
public static <T> void setFieldValue(T t, Field field, Object val) throws Exception {
if (field.getType().equals(String.class)) {
field.set(t, val.toString());
} else if (field.getType().equals(Integer.class)) {
field.set(t, Integer.valueOf(val.toString()));
} else if (field.getType().equals(Double.class)) {
field.set(t, Double.valueOf(val.toString()));
} else if (field.getType().equals(Date.class)) {
field.set(t, DateUtil.parseDate(val.toString()));
} else if (field.getType().equals(Boolean.class)) {
field.set(t, Boolean.valueOf(val.toString()));
} else if (field.getType().equals(BigDecimal.class)) {
field.set(t, new BigDecimal(val.toString()));
} else if (field.getType().equals(Long.class)) {
field.set(t, Long.valueOf(val.toString()));
} else {
field.set(t, val);
}
}
返回实体类基类RiskImportBaseVo.java
验证方法（differenceVerifyMust、verifyCombinationMust）为核心，属性克自定义
- public Boolean differenceVerifyMust(String property) // 必填项值为空时，进行差异验证
-
public void verifyCombinationMust() // 必要信息验证通过后进行组合验证
eg:
package com.qinghao.riskManage.domain.vo;
import com.qinghao.common.exception.UtilException;
import lombok.Data;
/**
* 风险点清单导入信息对象基类
*
* @author zhangji
* @date 2024-06-06 15:09:01
*/
@Data
public class RiskImportBaseVo {
/** 区域名称 **/
private String placeName;
/** 备注 */
private String remark;
/** 现有管控措施 */
private String existingEngineeringMeasures;
/** 风险评价方法 */
private String riskAssMethods;
/** 固有风险评价 L/L/M S/E/E C/S 风险值 */
private Double existingLlm, existingSee, existingCs, existingRiskValue;
/** 固有风险评价 风险程度 风险等级*/
private String existingRiskColor, existingRiskLevel;
/** 管辖部门/单位*/
private String deptName;
/** 建议新增管控措施 */
private String suggestedEngineeringMeasures;
/** 剩余风险评价 L/L/M S/E/E C/S 风险值 */
private Double suggestedLlm, suggestedSee, suggestedCs, suggestedRiskValue;
/** 固有风险评价 风险程度 风险等级*/
private String suggestedRiskColor, suggestedRiskLevel;
/**
* 必填项值为空时，进行差异验证
* @param property 属性名
* @return 验证结果 <font color='green'>true: 通过</font>、<font color='#FF0000'> false: 不通过</font>
*/
public Boolean differenceVerifyMust(String property) {
if("existingLlm".equals(property) || "existingSee".equals(property)) {
return "直接判定法".equals(this.riskAssMethods);
}
if("suggestedLlm".equals(property) || "suggestedSee".equals(property)) {
return "直接判定法".equals(this.riskAssMethods);
}
if("existingCs".equals(property) || "suggestedCs".equals(property)) {
return "LS".equals(this.riskAssMethods) || "直接判定法".equals(this.riskAssMethods);
}
return false;
}
/**
* 必要信息验证通过后进行组合验证
* @throws UtilException 验证失败，抛出失败信息
*/
public void verifyCombinationMust() {
if("直接判定法".equals(this.riskAssMethods)) {
if(null == this.existingRiskColor || null == this.suggestedRiskColor) {
throw new UtilException("风险评价方法为直接判定法时，风险程度为空");
}
this.riskAssMethods = "DD";
switch (this.existingRiskColor) {
case "低风险": this.existingRiskColor = "0"; this.existingRiskLevel = "四级"; break;
case "一般风险": this.existingRiskColor = "1"; this.existingRiskLevel = "三级"; break;
case "较大风险": this.existingRiskColor = "2"; this.existingRiskLevel = "二级"; break;
case "重大风险": this.existingRiskColor = "3"; this.existingRiskLevel = "一级"; break;
default: throw new UtilException("风险评价方法为直接判定法时，风险程度值错误");
}
} else {
switch (this.riskAssMethods) {
case "LS": this.existingRiskValue = this.existingLlm + this.existingSee; break;
case "LEC":
case "MES": this.existingRiskValue = this.existingLlm + this.existingSee + this.existingCs; break;
default: throw new UtilException("风险评价方法错误");
}
}
}
}
测试实体类 RiskFacilityImportVo.java
package com.qinghao.riskManage.domain.vo;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;
/**
* 设备设施清单导入信息对象
*
* @author zhangji
* @date 2024-06-06 15:09:01
*/
@Data
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
public class RiskFacilityImportVo extends RiskImportBaseVo {
/** 设备设施编号 */
private String facilityNum;
/** 设备设施名称 */
private String facilityName;
/** 设备类型名称 */
private String facilityTypeName;
/** 规格型号 */
private String specification;
/** 危险有害因素 */
private String nonCompliance;
/** 事故类型 */
private String majorConsequences;
}
附文：PoiExcel.java
package com.qinghao.common.utils.poi;
import com.qinghao.common.exception.UtilException;
import com.qinghao.common.utils.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.util.Date;
/**
* excel 操作工具类
* @author ZhangJi
*/
public class PoiExcel {
private Workbook workBook;
private String filePath;
public PoiExcel() {
this.workBook = new XSSFWorkbook();
this.workBook.createSheet("sheet1");
}
public PoiExcel(String filePath) throws IOException {
this(new File(filePath));
}
public PoiExcel(File file) throws IOException {
this.filePath = file.getAbsolutePath();
this.verifyXls(file);
InputStream inputStream = Files.newInputStream(file.toPath());
// 根据不同的后缀，创建不同的对象
if(RegExpLib.matcher(file.getName(), ".*\\.xlsx$")) {
this.workBook = new XSSFWorkbook(inputStream);
} else{
this.workBook = new HSSFWorkbook(inputStream);
}
}
public PoiExcel(InputStream inputStream, String fileName) throws IOException {
if(!RegExpLib.matcher(fileName, ".*\\.xls$|.*\\.xlsx$")) {
throw new UtilException("导入文件类型错误！");
}
// 根据不同的后缀，创建不同的对象
if(RegExpLib.matcher(fileName, ".*\\.xlsx$")) {
this.workBook = new XSSFWorkbook(inputStream);
} else {
this.workBook = new HSSFWorkbook(inputStream);
}
}
/**
* 获取单元格值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return String
*/
public Object getObjVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Cell cell = this.getSheet(sheetIndex).getRow(rowIndex).getCell(cellIndex);
if(null == cell) {
return null;
}
switch (cell.getCellType()) {
case STRING: return cell.getStringCellValue();
case NUMERIC:
case FORMULA:
if(DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}
return cell.getNumericCellValue();
case BOOLEAN: return cell.getBooleanCellValue();
default: return null;
}
}
/**
* 获取单元格String值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return String
*/
public String getStringVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
return null != cellVal?cellVal.toString(): "";
}
/**
* 获取单元格double值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return String
*/
public Double getDoubleVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
return null == cellVal?.0: RegExpLib.matcher(cellVal.toString(), RegExpLib.REG_FLOAT)?Double.parseDouble(cellVal.toString()): .0;
}
/**
* 获取单元格Integer值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return Integer
*/
public Integer getIntegerVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
if(null == cellVal) {
return 0;
}
String valStr = cellVal.toString();
if(!RegExpLib.matcher(valStr, RegExpLib.REG_FLOAT)) {
return 0;
}
if(valStr.contains(".")) {
valStr = valStr.substring(0, valStr.indexOf("."));
}
return Integer.parseInt(valStr);
}
/**
* 获取boolean值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return boolean
*/
public boolean getBooleanVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
return null != cellVal && (boolean) cellVal;
}
/**
* 获取单元格Date值
* @param sheetIndex 工作簿索引
* @param rowIndex 行索引
* @param cellIndex 列索引
* @return Date
*/
public Date getDateVal(Integer sheetIndex, Integer rowIndex, Integer cellIndex) {
Object cellVal = this.getObjVal(sheetIndex, rowIndex, cellIndex);
if(null == cellVal) {
return null;
}
if(cellVal instanceof Date) {
return (Date) cellVal;
}
return DateUtils.parseDate(cellVal.toString());
}
/**
* 设置字符串值
* @param cellIndex 列索引
* @param value 值
*/
public void setStringValue(Row row, Integer cellIndex, String value, CellStyle style) {
Cell cell = row.getCell(cellIndex);
if(null == cell) {
cell = row.createCell(cellIndex, CellType.STRING);
}
cell.setCellStyle(style);
cell.setCellValue(value);
}
/**
* 设置double值
* @param cellIndex 列索引
* @param value 值
*/
public void setDoubleValue(Row row, Integer cellIndex, double value, CellStyle style) {
Cell cell = row.getCell(cellIndex);
if(null == cell) {
cell = row.createCell(cellIndex, CellType.NUMERIC);
}
cell.setCellStyle(style);
cell.setCellValue(value);
}
public void setIntegerValue(Row row, Integer cellIndex, int value, CellStyle style) {
Cell cell = row.createCell(cellIndex, CellType.NUMERIC);
cell.setCellStyle(style);
cell.setCellValue(value);
}
/**
* 获取边框样式
* @return CellStyle
*/
public CellStyle getBorderStyle() {
CellStyle style = this.workBook.createCellStyle();
style.setBorderTop(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
return style;
}
/**
* 获取默认样式
* @return CellStyle
*/
public CellStyle createNormalStyle() {
return this.workBook.createCellStyle();
}
/**
* 创建字体
* @return Font
*/
public Font createFont() {
return this.workBook.createFont();
}
public void save() throws IOException {
FileOutputStream fileOutputStream = new FileOutputStream(this.filePath);
this.workBook.write(fileOutputStream);
this.workBook.close();
fileOutputStream.close();
this.workBook = null;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public void close() throws IOException {
if(null != this.workBook) {
this.workBook.close();
}
}
public Sheet getSheet(Integer sheetIndex) {
return this.workBook.getSheetAt(sheetIndex);
}
public Sheet getSheetBySheetName(String sheetName) {
return this.getSheet(this.getSheetIndexBySheetName(sheetName));
}
/**
* 获取sheet索引<br />
* 优先完全匹配，如完全匹配未找到，再包含匹配
* @param sheetName sheet名称
* @return Integer
*/
public Integer getSheetIndexBySheetName(String sheetName) {
int numbers = this.workBook.getNumberOfSheets();
// 完全匹配
for (int i=0; i<numbers; i++) {
Sheet sheet = this.workBook.getSheetAt(i);
if(sheet.getSheetName().equals(sheetName)) {
return i;
}
}
// 包含匹配
for (int i=0; i<numbers; i++) {
Sheet sheet = this.workBook.getSheetAt(i);
if(sheet.getSheetName().contains(sheetName)) {
return i;
}
}
throw new UtilException("sheetName ["+sheetName+"] not found");
}
public void setMergedRegionValue(Integer sheetIndex, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol, String value, CellStyle style) {
for (int beginRowIndex=firstRow; beginRowIndex <= lastRow; beginRowIndex++) {
Row row = this.getSheet(sheetIndex).getRow(beginRowIndex);
for (int beginCellIndex=firstCol; beginCellIndex <= lastCol; beginCellIndex++) {
this.setStringValue(row, beginCellIndex, "", style);
}
}
this.getSheet(sheetIndex).addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
this.setStringValue(this.getSheet(sheetIndex).getRow(firstRow), firstCol, value, style);
}
public void setMergedRegionImage(Integer sheetIndex, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol,
InputStream inputStream, int format, CellStyle style) throws IOException {
for (int beginRowIndex=firstRow; beginRowIndex <= lastRow; beginRowIndex++) {
Row row = this.getSheet(sheetIndex).getRow(beginRowIndex);
for (int beginCellIndex=firstCol; beginCellIndex <= lastCol; beginCellIndex++) {
this.setStringValue(row, beginCellIndex, "", style);
}
}
byte[] imageBytes = IOUtils.toByteArray(inputStream);
int pictureIdx = this.workBook.addPicture(imageBytes, format);
inputStream.close();
this.getSheet(sheetIndex).addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, firstCol, firstRow, lastCol, lastRow + 1); // 图片位置和大小
// 创建绘图对象
Drawing<?> drawing = this.getSheet(sheetIndex).createDrawingPatriarch();
Picture picture = drawing.createPicture(anchor, pictureIdx);
picture.resize(2,1);
}
private void verifyXls(File file){
if(!file.exists() || file.isDirectory()) {
throw new UtilException("文件不存在！");
}
if(!RegExpLib.matcher(file.getName(), ".*\\.xls$|.*\\.xlsx$")) {
throw new UtilException("导入文件类型错误！");
}
}
public Workbook getWorkBook() {
return workBook;
}
}
](https://blog.jjzqkj.cn/content/uploadfile/202406/abf01717751022.png](https://blog.jjzqkj.cn/)[](https://blog.jjzqkj.cn/content/uploadfile/202406/abf01717751022.png){kind=link}