一、需求
POI导入excel,模块动态上传,模板字段动态配置,字段类型可配置化,类型包括但是不局限字符类型、数字、日期、字典、用户、机构。报错信息保存展示。同时保存导入批次信息。
二、技术
java版本:1.8
poi版本:4.0.0(模板格式:xlsx)
三、数据库设计
1、导入模板信息维护表
导入模板表设计:维护模板信息,包括模板名称及保存相关路径
CREATE TABLE FILE_TEMPLATE_INFO (
TEMPLATE_ID VARCHAR2(50) NOT NULL,
TEMPLATE_NAME VARCHAR2(100),
SAVE_PATH VARCHAR2(255),
CREATE_TIME TIMESTAMP,
UPDATE_TIME TIMESTAMP,
DEL_FLAG NUMBER(1,0),
CREATE_BY VARCHAR2(50),
UPDATE_BY VARCHAR2(50),
STATUS NUMBER(1,0),
OBJECT_ID VARCHAR2(50),
CONSTRAINT PK_FILE_TEMPLATE_INFO PRIMARY KEY (TEMPLATE_ID)
);
CREATE UNIQUE INDEX INDEX33556782 ON FILE_TEMPLATE_INFO (TEMPLATE_ID);
2、模板信息关联字段信息表
模板信息关联字段信息表设计:包括模板信息、模板字段名称、对应对象字段名、是否必填、是否字典、是否人员、是否为部门、如果是ID需要转存的字段名、字段解析保存存放的字段名
CREATE TABLE FILE_IMPORT_FIELD_INFO (
ST_ID VARCHAR2(32) NOT NULL,
ST_TEMPLATE_ID VARCHAR2(32),
ST_NAME VARCHAR2(32),
ST_DB_FIELD_NAME VARCHAR2(32),
ST_FIELD_NAME VARCHAR2(32),
ST_TYPE VARCHAR2(8),
IS_REQUIRED NUMBER(1,0),
IS_DIC NUMBER(1,0),
ST_DIC_CODE VARCHAR2(26),
IS_BOOLEAN NUMBER(1,0),
ST_MEMO VARCHAR2(32),
NM_ORDER_NUM NUMBER(10,0),
IS_RECEIVE_DEPT NUMBER(1,0),
DEL_FLAG NUMBER(1,0) DEFAULT 0,
IS_USER NUMBER(1,0),
IS_DEPT NUMBER(1,0),
ID_FIELD_NAME VARCHAR2(32),
ERR_FIELD_NAME VARCHAR2(32),
IS_TASK_TYPE NUMBER(1,0),
CONSTRAINT PK_FILE_IMPORT_FIELD_INFO PRIMARY KEY (ST_ID)
);
CREATE UNIQUE INDEX INDEX33556683 ON FILE_IMPORT_FIELD_INFO (ST_ID);
3、批次信息表
批次信息包含批次号、上传时间、数据类型、文件名称以及文件保存路径
CREATE TABLE FILE_IMPORT_BATCH_INFO (
BATCH_ID VARCHAR2(32) NOT NULL,
IMPORT_TIME TIMESTAMP,
IMPORT_BY VARCHAR2(32),
INFO_TYPE VARCHAR2(32),
BATCH_NO VARCHAR2(32),
FILE_NAME VARCHAR2(100),
FILE_PATH VARCHAR2(255),
CONSTRAINT PK_FILE_IMPORT_BATCH_INFO PRIMARY KEY (BATCH_ID)
);
CREATE UNIQUE INDEX INDEX33556671 ON FILE_IMPORT_BATCH_INFO (BATCH_ID);
四、开发思路
1、模板动态上传
保存至Apache httpd文件服务器,代码略。
2、模板字段拆分维护
信息包含字段名、描述、类型等,代码略。
3、API接口
使用form表单请求的方式,导入模板
template_id:模板ID,用来查询模板字段
inputType:数据类型,用于保存批次信息
/**
* 附件上传
* @param request
* @return: 总共上传了XX条数据,导入成功XX条,导入失败XX条
* @throws UnsupportedEncodingException
*/
@RequestMapping(value="/importFile", method = RequestMethod.POST, headers = "Content-Type=multipart/form-data" )
@ResponseBody
public Rcode importFile(HttpServletRequest request,
HttpServletResponse response, @RequestParam(value = "templateId") String templateId,
@RequestParam(value = "inputType") String inputType,
MultipartFile file, @AuthLogin SasPubUser user) throws UnsupportedEncodingException {
request.setCharacterEncoding("utf-8");
if(StringUtils.isEmpty(templateId)) {
return Rcode.error("导入模板不能为空");
}
if(StringUtils.isEmpty(inputType)) {
return Rcode.error("导入任务类型不能为空");
}
if(null == file) {
return Rcode.error("请选择上传文件");
}
try {
String appId = request.getHeader(AuthReqUtils.APP_ID);
return Rcode.ok().put(ApiAdminConfig.RETURN_PARAMS, fileService.importData(templateId,inputType, file, user, appId));
} catch (Exception e) {
return Rcode.error(e.getMessage());
}
}
4、业务逻辑处理
(1)首先将文件上传至文件服务器,保存批次信息
FileInfo fileInfo = null;
try {
fileInfo = uploadFile(file, "uploadTask");
} catch (IOException e) {
e.printStackTrace();
}
if(null == fileInfo) {
throw new CommonException("文件上传失败");
}
//保存导入批次信息
String batchId = saveBatch(inputType, fileInfo, user);
(2)解析导入的文件,判断数据是否为空,是否超出最大数量,校验之后存放至对象中
int bRowNum = FileConstant.HEADER_BROWNUM;
int eRowNum = FileConstant.HEADER_EROWNUM;
XSSFWorkbook wb = null;
XSSFSheet sheet = null;
try {
wb = new XSSFWorkbook(file.getInputStream());
sheet = wb.getSheetAt(0);
} catch (IOException e) {
throw new CommonException(e.getMessage());
}
if(sheet == null) {
throw new CommonException("表格工作表为空");
}
List<String> headers = readHeader(sheet, bRowNum, eRowNum);
if(null == headers || headers.size() <= 0) {
throw new CommonException("文件格式错误:表格列头出现错误,请下载系统中模板");
}
List<XSSFRow> rows = readData(sheet, eRowNum);
if(null == rows || rows.size() <= 0) {
throw new CommonException("导入的数据为空");
}
if(rows.size() > importFileNumOnce){
throw new CommonException("导入数据太多,数据的总数不能超过"+ importFileNumOnce +"条,请分开导入!");
}
读取文档header,记录是否有必填字眼
public List<String> readHeader(XSSFSheet sheet, int bRowNum, int eRowNum) {
// 得到上传excel表头
List<String> headers = new ArrayList<>();
// 最大列数
XSSFRow headerRow = sheet.getRow(bRowNum);
if(headerRow==null){
return null;
}
int maxColumn =headerRow.getPhysicalNumberOfCells();
for (int j = 0; j < maxColumn; j++) {
XSSFCell cell = headerRow.getCell(j);
String name = getCellFormatValue(cell);
if(!StringUtils.isEmpty(name)) {
if(name.indexOf("(")>0){
name = name.substring(0, name.indexOf("("));
}
name = name.replace("\n", "");
name = name.replace("*", "");
headers.add(j, name);
}
}
return headers;
}
读取文档数据
public List<XSSFRow> readData(XSSFSheet sheet, int bRowNum) {
// 得到上传excel内容
List<XSSFRow> rows = new ArrayList<>();
int maxRow = sheet.getPhysicalNumberOfRows();
for (int i = bRowNum; i < maxRow; i++) {// 第bRowNum行开始取
XSSFRow row = sheet.getRow(i);
if (row == null || "".equals(row)) {
break;
} else {
XSSFCell cell = row.getCell(0);// 第一列的值
if (cell == null || "".equals(cell)) {
break;// 查到为空时停止,以防getPhysicalNumberOfRows多取值
} else {
String id = getCellFormatValue(cell);
if ("".equals(id) || id == null) {
break;
}
rows.add(row);
}
}
}
return rows;
}
(3)根据templateId获取模板字段
//根据templateId获取字段,及字段配置,是否必填等
Map<String, Object> fieldParams = new HashMap<>();
fieldParams.put("delFlag", 0);
fieldParams.put("templateId", templateId);
List<FileImportFieldInfo> fieldList = fileImportFieldInfoService.getList(fieldParams);
if(null == fieldList || fieldList.size() == 0) {
throw new CommonException("关联的模板尚未配置,请联系管理员");
}
(4)根据字段配置,匹配导入数据,并保存
//将字段配置信息转换成map,key值为字段描述,方便根据文档header获取保存的字段信息
Map<String, FileImportFieldInfo> fieldInfoMap = parseMap(fieldList);
if(null != fieldInfoMap) {
//按照header的顺序,得到字段名称与字段配置信息
Map<String, FileImportFieldInfo> headerFieldMap = parseHeaderFieldMap(headers, fieldInfoMap);
if(headerFieldMap.isEmpty()) {
throw new CommonException("文档字段与模板尚未关联,请联系管理员");
}
for (XSSFRow row : rows) {
Object object = getObjectByType(templateId);
if(null == object) {
throw new CommonException("模板对应对象尚未配置,请联系管理员");
}
try {
TaskImportResult importResult = checkData(row, headerFieldMap, object, batchId, inputType, appId);
result.setSuccessCount(result.getSuccessCount() + 1);
importResultList.add(importResult);
} catch (Exception e) {
e.printStackTrace();
List<Integer> failRows = result.getFailRows();
if(failRows == null) {
failRows = new ArrayList<>();
}
failRows.add(row.getRowNum());
result.setFailCount(result.getFailCount() + 1);
continue;
}
}
result.setTaskImportResultList(importResultList);
}else {
result.setFailCount(rows.size());
}
result.setCount(rows.size());
result.setBatchId(batchId);
return result;
checkData,字段处理
/**
* 检查数据,先全部存放在TaskInfo中,如果字段设置成了人员、部门,则根据值查询ID保存
* 如果是字典,根据字典名称,字典code,字典类型查询字典值;有值,则返回,没有值,则返回缺少字典提醒
* 如果是人员,根据人员名字,模糊匹配查询人员信息,有且只有1个人,则返回人员ID存放至idFieldName设置的字段中,没有或者超过1人,则返回人员异常提醒
* 如果是单位,根据单位名字,模糊匹配查询单位信息,有且只有1个单位,则返回单位ID存放在idFieldName设置的字段中,没有或者超过1个,则返回单位异常提醒
* 如果是任务接收单位,用,拆分单位,然后根据单位名字,模糊查询单位信息,有且只有1个单位,则返回单位ID,创建TaskSendInfo保存,没有或者超过1个,则返回单位异常提醒
* @param row
* @param headerFieldMap
* @param inputType :类型大类
*/
private TaskImportResult checkData(XSSFRow row, Map<String, FileImportFieldInfo> headerFieldMap,
Object object, String batchId, String inputType, String appId) {
TaskImportResult result = new TaskImportResult();
ErrorWarn errorWarn = new ErrorWarn();
List<TaskSendInfo> sendInfo = new ArrayList<>();
List<String> errorMsg = new ArrayList<>();
int index = 0;
for (String header : headerFieldMap.keySet()) {
FileImportFieldInfo fieldInfo = headerFieldMap.get(header);
if(null != fieldInfo) {
String fieldName = fieldInfo.getFieldName().replace("\n", "").trim();
XSSFCell cell = row.getCell(index);
String value = getCellFormatValue(cell);
String transId = "";//根据名称查找ID
String transValue = "";//校验具体的名称,主要是单位,例如导入的数据单位名称是关键字,通过查询获取正确的名称
//解析当前字段错误信息
List<String> fieldErrMsg = new ArrayList<>();
//是否必填
if(StringUtils.isBlank(value) && null != fieldInfo.getIsRequired() && fieldInfo.getIsRequired()) {
errorMsg.add(header + ":" +value+":为必填字段");
fieldErrMsg.add(header + ":" +value+":为必填字段");
}else if (StringUtils.isNotBlank(value)) {
value = CommonUtils.replaceBlank(value);
//是否是字典
if(null != fieldInfo.getIsDic() && fieldInfo.getIsDic()) {
transId = getTranslateSpecialValue(value, fieldInfo.getDicCode(), appId);//翻译字典
if(StringUtils.isEmpty(transId)) {
errorMsg.add(header + ":" +value+":找不到字典项");
fieldErrMsg.add(header + ":" +value+":找不到字典项");
}
//是否是用户
}else if(null != fieldInfo.getIsUser() && fieldInfo.getIsUser()) {
List<AdminUser> userList = adminUserService.getList(new StringMap().put("delFlag", 0).put("name", value).map());
if(null == userList || userList.size() == 0) {
errorMsg.add(header + ":" +value+":没有找到对应的用户");
fieldErrMsg.add(header + ":" +value+":没有找到对应的用户");
}else if(userList.size() > 1) {
errorMsg.add(header + ":" +value+":有多个对应的用户");
fieldErrMsg.add(header + ":" +value+":有多个对应的用户");
}else {
transId = userList.get(0).getId() + "";
transValue = userList.get(0).getName();
}
//是否是单位
}else if(null != fieldInfo.getIsDept() && fieldInfo.getIsDept()) {
String[] depts = value.split("、");
for (int i = 0; i < depts.length; i ++) {
String dept = depts[i];
if (StringUtils.isBlank(dept)) {
continue;
}
List<AdminDept> deptList = adminDeptService.getList(new StringMap().put("delFlag", 0).put("name", dept).map());
if (null == deptList || deptList.size() == 0) {
errorMsg.add(header + ":" + dept + ":没有找到对应的单位");
fieldErrMsg.add(header + ":" + dept + ":没有找到对应的单位");
} else if (deptList.size() > 1) {
errorMsg.add(header + ":" + dept + ":有多个对应的单位");
fieldErrMsg.add(header + ":" + dept + ":有多个对应的单位");
} else {
transId += deptList.get(0).getId() + ",";
transValue += deptList.get(0).getSname() + ",";
}
}
//是否是接收单位
}else if(null != fieldInfo.getIsReceiveDept() && fieldInfo.getIsReceiveDept()) {
String[] depts = value.split("、");
for (int i = 0; i < depts.length; i ++) {
String dept = depts[i];
if(StringUtils.isBlank(dept)) {
continue;
}
List<AdminDept> deptList = adminDeptService.getList(new StringMap().put("delFlag", 0).put("name", dept).map());
if(null == deptList || deptList.size() == 0) {
errorMsg.add(header + ":" +dept+":没有找到对应的单位");
fieldErrMsg.add(header + ":" +dept+":没有找到对应的单位");
}else if(deptList.size() > 1) {
errorMsg.add(header + ":" +dept+":有多个对应的单位");
fieldErrMsg.add(header + ":" +dept+":没有找到对应的单位");
}else {
transId += deptList.get(0).getId() + ",";
transValue += deptList.get(0).getSname() + ",";
}
}
//是否是任务类型
}else if(null != fieldInfo.getIsTaskType() && fieldInfo.getIsTaskType()) {
List<TaskType> taskTypeList = taskTypeService.getList(new StringMap().put("removed", 0).put("name", value).put("noParent", "1").map());
if(null == taskTypeList || taskTypeList.size() == 0) {
errorMsg.add(header + ":" +value+":没有找到对应的任务类型");
fieldErrMsg.add(header + ":" +value+":没有找到对应的任务类型");
}else if(taskTypeList.size() > 1) {
errorMsg.add(header + ":" +value+":有多个对应的任务类型");
fieldErrMsg.add(header + ":" +value+":有多个对应的任务类型");
}else {
transId = taskTypeList.get(0).getId() + "";
}
}
}
String dataType = fieldInfo.getType();
//如果有需转换的字段, 将转换的值存入对应的字段
if(!StringUtils.isEmpty(transId)) {
if(transId.endsWith(",")) {
transId = transId.substring(0, transId.lastIndexOf(","));
}
this.setFieldValue(object, fieldInfo.getIdFieldName(), transId);
}
if ("Date".equals(dataType)) {
Date date = new Date();
try {
//
date = CommonUtils.parseDate(value);
if(fieldErrMsg.size() <= 0) {
this.setFieldValue(object, fieldName, date);
}else {
this.setFieldValue(object, fieldInfo.getErrFieldName(), date);
}
} catch (Exception e) {
errorMsg.add(header + ":" +value+":输入的时间格式不符");
fieldErrMsg.add(header + ":" +value+":输入的时间格式不符");
}
}else if ("Number".equals(dataType)) {
Integer number = Integer.valueOf(value);
if(fieldErrMsg.size() <= 0) {
this.setFieldValue(object, fieldName, number);
}else {
this.setFieldValue(object, fieldInfo.getErrFieldName(), number);
}
}else{
if(fieldErrMsg.size() <= 0) {
if(StringUtils.isNotBlank(transValue)) {
if(transValue.endsWith(",")) {
transValue = transValue.substring(0, transValue.lastIndexOf(","));
}
this.setFieldValue(object, fieldName, transValue);
}else {
this.setFieldValue(object, fieldName, value);
}
}else {
//主要处理导入单位有正确的,有错误的,还是需要把正确的存下来
if(StringUtils.isNotBlank(transValue)) {
if(transValue.endsWith(",")) {
transValue = transValue.substring(0, transValue.lastIndexOf(","));
}
this.setFieldValue(object, fieldName, transValue);
}
this.setFieldValue(object, fieldInfo.getErrFieldName(), value);
}
}
}else {
//如果根据head没有找到配置值,则返回告诉管理员
}
index ++;
}
TaskInfoTemp taskInfo = (TaskInfoTemp) object;
taskInfo.init();
taskInfo.setBatchId(batchId);
taskInfo.setTaskTypeFirst(inputType);
result.setTaskInfo(taskInfo);
if(errorMsg.size() > 0) {
errorWarn.setTaskName(taskInfo.getTaskName());
errorWarn.setErrorMsg(errorMsg);
taskInfo.setErrInfo(JSONObject.toJSONString(errorMsg));
}
taskInfoTempService.save(taskInfo);
result.setErrorWarn(errorWarn);
return result;
}
日期转换方法
/**
* 字符串转时间, 可识别常见类型
* @param str 要转换的格式
* @return 转化后的时间
* @throws Exception .
*/
public static Date parseDate(String str) throws Exception {
str = str.trim();
String pattern = "\\d{4}([^\\d]?)\\d{1,2}\\1\\d{1,2}( \\d{1,2}([^\\d])\\d{1,2})?";
// 创建 Pattern 对象
Pattern r = Pattern.compile(pattern);
// 现在创建 matcher 对象
Matcher m = r.matcher(str);
String dateSplit, timeSplit;
if (m.find()) {
dateSplit = m.group(1);
timeSplit = m.group(3);
String formatStr = String.format("yyyy%sMM%sdd", dateSplit, dateSplit);
if (timeSplit != null) {
String timeStr = str.substring(str.indexOf(" "));
String[] split = timeStr.split(timeSplit);
if (split.length == 2) {
formatStr += String.format(" HH%smm", timeSplit);
}
if (split.length > 2) {
formatStr += String.format(" HH%smm%sss", timeSplit, timeSplit);
}
}else {
str += " 23:59:59";
formatStr += String.format(" HH:mm:ss");
}
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(formatStr);
return simpleDateFormat.parse(str);
} else {
throw new Exception("输入的时间格式不符");
}
}
相关实体类
/**
* 导入结果
*/
@Data
public class ImportResult {
private String batchId;
private int count;
private int failCount;
private List<Integer> failRows;
private int successCount;
private List<TaskImportResult> taskImportResultList;
}
/**
* 任务导入返回值
*/
@Data
public class TaskImportResult {
//保存对象
private TaskInfoTemp taskInfo;
private ErrorWarn errorWarn;
}
/**
* 错误提醒
*
*/
@Data
public class ErrorWarn {
private String taskName;
private List<String> errorMsg;
}