JAVA实现微软project文件导入
前言
功能需求:
1、读取project四级文件,一级是项目标题,二级是阶段,三级是模块,四级是任务项;
2、三级模块可不存在;
3、需判断是否为project文件;
4、相同项目名称唯一性;
project示例文件如下所
一、pom.xml引用jar包
提示:只读取project 2010 以下的版本,2010以上的版本会报错
<!--解析project文件包-->
<dependency>
<groupId>net.sf.mpxj</groupId>
<artifactId>mpxj</artifactId>
<version>9.7.0</version>
</dependency>
二、实现代码
1.实体类(对应自己的数据库表字段)
实体类PmsProjectPlanning代码如下:
/**
* 描述:项目计划实体类定义
* 表:pms_project_planning
* 作者:ghd
* 日期:2022-08-11 10:14:37
*/
import com.baomidou.mybatisplus.annotation.FieldStrategy;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonCreator;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.apache.ibatis.type.JdbcType;
@Setter
@Getter
@Accessors(chain = true)
@TableName(value = "pms_project_planning")
public class PmsProjectPlanning extends BaseExtEntity<java.lang.String> {
@JsonCreator
public PmsProjectPlanning() {
}
//主键
@TableId(value = "ID_",type = IdType.INPUT)
private String id;
//创建人名称
@TableField(value = "CREATE_BY_NAME",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String createByName;
//前置任务
@TableField(value = "FRONT_TASK_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String frontTask;
//流程实例ID
@TableField(value = "INST_ID_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String instId;
//状态
@TableField(value = "INST_STATUS_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String instStatus;
//父ID
@TableField(value = "PARENT_ID_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String parentId;
//项目名称
@TableField(value = "PROJECT_ID_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String projectId;
//项目名称
@TableField(value = "PROJECT_NAME_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String projectName;
//外键
@TableField(value = "REF_ID_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String refId;
//实际完成日期
@org.springframework.format.annotation.DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@com.alibaba.fastjson.annotation.JSONField(format="yyyy-MM-dd HH:mm:ss")
@com.fasterxml.jackson.annotation.JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
@TableField(value = "TASK_ACTUAL_FINISH_DATE_",jdbcType=JdbcType.TIMESTAMP, updateStrategy = FieldStrategy.IGNORED)
private java.util.Date taskActualFinishDate;
//实际开始日期
@org.springframework.format.annotation.DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@com.alibaba.fastjson.annotation.JSONField(format="yyyy-MM-dd HH:mm:ss")
@com.fasterxml.jackson.annotation.JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
@TableField(value = "TASK_ACTUAL_START_DATE_",jdbcType=JdbcType.TIMESTAMP, updateStrategy = FieldStrategy.IGNORED)
private java.util.Date taskActualStartDate;
//实际阶段工作量
@TableField(value = "TASK_ACTUAL_WORK_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskActualWork;
//实际工作量单位
@TableField(value = "TASK_ACTUAL_WORK_UNITS_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskActualWorkUnits;
//阶段计划工期
@TableField(value = "TASK_DURATION_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskDuration;
//计划工期单位
@TableField(value = "TASK_DURATION_UNITS_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskDurationUnits;
//计划完成日期
@org.springframework.format.annotation.DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@com.alibaba.fastjson.annotation.JSONField(format="yyyy-MM-dd HH:mm:ss")
@com.fasterxml.jackson.annotation.JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
@TableField(value = "TASK_FINISH_DATE_",jdbcType=JdbcType.TIMESTAMP, updateStrategy = FieldStrategy.IGNORED)
private java.util.Date taskFinishDate;
//任务ID
@TableField(value = "TASK_ID_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskId;
//任务项负责人
@TableField(value = "TASK_LEADER_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskLeader;
//模块名称
@TableField(value = "TASK_MODEL_NAME_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskModelName;
//任务项名称
@TableField(value = "TASK_NAME_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskName;
//阶段负责人
@TableField(value = "TASK_OPERATOR_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskOperator;
//任务级别
@TableField(value = "TASK_OUTLINE_LEVEL_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskOutlineLevel;
//父任务ID
@TableField(value = "TASK_PARENT_DEF_ID_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskParentDefId;
//阶段完成百分比
@TableField(value = "TASK_PERCENTAGE_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskPercentage;
//阶段名称
@TableField(value = "TASK_PHASE_NAME_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskPhaseName;
//任务流
@TableField(value = "TASK_PREDECESSORS_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskPredecessors;
//备注
@TableField(value = "TASK_REMARK_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskRemark;
//计划开始日期
@org.springframework.format.annotation.DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
@com.alibaba.fastjson.annotation.JSONField(format="yyyy-MM-dd HH:mm:ss")
@com.fasterxml.jackson.annotation.JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
@TableField(value = "TASK_START_DATE_",jdbcType=JdbcType.TIMESTAMP, updateStrategy = FieldStrategy.IGNORED)
private java.util.Date taskStartDate;
//任务唯一ID
@TableField(value = "TASK_UNIQUE_ID_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskUniqueId;
//WBS码
@TableField(value = "TASK_WBS_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskWbs;
//阶段计划工作量
@TableField(value = "TASK_WORK_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskWork;
//阶段状态
@TableField(value = "TASK_WORK_STATUS_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskWorkStatus;
//计划工作量单位
@TableField(value = "TASK_WORK_UNITS_",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String taskWorkUnits;
//更新人名称
@TableField(value = "UPDATE_BY_NAME",jdbcType=JdbcType.VARCHAR, updateStrategy = FieldStrategy.IGNORED)
private String updateByName;
//版本号
@TableField(value = "UPDATE_VERSION_",jdbcType=JdbcType.NUMERIC, updateStrategy = FieldStrategy.IGNORED)
private Long updateVersion;
@Override
public String getPkId() {
return id;
}
@Override
public void setPkId(String pkId) {
this.id=pkId;
}
/**
* 生成子表属性的Array List
*/
}
2.业务服务类(读取project文件并存表)
2.1 上传方法(addProject)如下:
/**
* @Description: 上传Project文件
* @author: hd_gao
* @date: 2022/8/11 15:04
* @param: request
**/
public JsonResult addProject(MultipartHttpServletRequest request) {
//解析project文件,获取数据并判断格式是否正确
Map map = (Map) checkFile(request, "addProject").getData();
String message = (String) map.get("message");
if (StringUtils.isNotBlank(message)) {
return JsonResult.Success().setMessage(message);
}
//获取任务项
List<Task> takList = (List<Task>) map.get("taskList");
//获取项目名称
String projectName = takList.get(1).getName();
//项目数据 一级标题
List<PmsProjectPlanning> taskProjectList = new ArrayList<PmsProjectPlanning>();
//阶段数据 二级标题
List<PmsProjectPlanning> taskPhaseList = new ArrayList<PmsProjectPlanning>();
//模块数据 三级标题
List<PmsProjectPlanning> taskModelList = new ArrayList<PmsProjectPlanning>();
//任务项数据 四级标题
List<PmsProjectPlanning> taskItemList = new ArrayList<PmsProjectPlanning>();
/** 整体思路:判断任务等级,等级2=阶段,等级3=模块,等级4=任务项,通过等级3是否存在子数据确定他是模块还是任务项,最后保存数据
* 解析文件数据,保存到数据库中,从 i=1开始循环获取任务项
* i=0为 project产品信息:[Task id=0 uniqueID=0 name=koa标准产品实施项目]
* i=1为项目标题的信息 [Task id=1 uniqueID=1 name=XXX项目]
* i>=2为项目阶段或模块名称或项目任务项名称
*/
for (int i = 1; i < takList.size(); i++) {
Task task = takList.get(i);
//父任务ID 上级任务ID
Integer task_parent_def_id = task.getParentTask().getID();
//任务等级
Integer task_outline_level = task.getOutlineLevel();
//解析project文件 获取任务项数据
Map pmsProjectPlanningMap = (Map) analysisFile(task, projectName).getData();
//获取任务项数据
PmsProjectPlanning pmsProjectPlanning = (PmsProjectPlanning) pmsProjectPlanningMap.get("pmsProjectPlanning");
//判断任务等级并存储数据 1:项目 2:阶段 3:模块(可能不存在) 4:任务项
if (task_outline_level == 1) {
//保存到【项目】数组中
taskProjectList.add(pmsProjectPlanning);
}
if (task_outline_level == 2) {
//根据父级ID匹配【项目】的ID,并给【阶段】的refID赋值
for (PmsProjectPlanning taskProject : taskProjectList) {
if (task_parent_def_id.toString().equals(taskProject.getTaskId())) {
pmsProjectPlanning.setRefId(taskProject.getId());
pmsProjectPlanning.setTaskPhaseName(pmsProjectPlanning.getTaskName());
break;
}
}
//保存到【阶段】数组中
taskPhaseList.add(pmsProjectPlanning);
}
//任务等级是3 ,需要判断是否存在子任务,存在子任务就是【模块】,不存在子任务就是【任务项】
if (task_outline_level == 3) {
//根据父级ID匹配【阶段项】的ID,并给【模块】的refID赋值
for (PmsProjectPlanning taskPhases : taskPhaseList) {
if (task_parent_def_id.toString().equals(taskPhases.getTaskId())) {
pmsProjectPlanning.setRefId(taskPhases.getId()); //【模块】refId = 【阶段】ID
pmsProjectPlanning.setTaskPhaseName(taskPhases.getTaskPhaseName()); //阶段名称
break;
}
}
//获取子任务数组
List<Task> childTasks = task.getChildTasks();
//判断子任务是否存在,不存在代表他是【任务项】,存在代表他是【模块】
if (childTasks.size() > 0) {
//保存到【模块】数组中
taskModelList.add(pmsProjectPlanning);
} else {
//保存到【任务】数组中
taskItemList.add(pmsProjectPlanning);
}
}
//任务等级是4 ,代表他是任务项
if (task_outline_level == 4) {
///根据父级ID匹配【模块】的ID,并给【任务项】的refID赋值
for (PmsProjectPlanning taskModels : taskModelList) {
if (task_parent_def_id.toString().equals(taskModels.getTaskId())) {
pmsProjectPlanning.setRefId(taskModels.getId()); //refID == 【模块】ID
pmsProjectPlanning.setTaskPhaseName(taskModels.getTaskPhaseName()); //阶段名称
pmsProjectPlanning.setTaskModelName(taskModels.getTaskName()); //模块名称
break;
}
}
//保存到【任务】数组中
taskItemList.add(pmsProjectPlanning);
}
}
// 批量保存
this.saveBatch(taskProjectList, taskProjectList.size()); // 一级,项目
this.saveBatch(taskPhaseList, taskPhaseList.size()); // 二级,阶段
this.saveBatch(taskModelList, taskModelList.size()); //三级,模块
this.saveBatch(taskItemList, taskItemList.size()); //四级,任务项
return JsonResult.Success().setMessage("导入成功");
}
2.2 检查文件方法(checkFile)如下:
/**
* @Description: 解析project文件,获取数据并判断格式是否正确
* @author: hd_gao
* @date: 2022/8/11 15:05
* @param: request
* @param: type
**/
private JsonResult checkFile(MultipartHttpServletRequest request, String type) {
Map map = new HashMap<>();
//获取上传的文件
List<MultipartFile> files = request.getFiles("files[]");
//只取第一个文件
MultipartFile file = files.get(0);
//获取文件名称的后缀并判断后缀是否为“mpp”
String extension = FilenameUtils.getExtension(file.getOriginalFilename());
String message = "";
//type=addProject 是导入,trype=updateProject 是更新 非 mpp 结尾的文件返回报错
if (!"mpp".equals(extension)) {
if ("addProject".equals(type)) {
message = "导入失败,文件名后缀必须为“mpp”!";
} else {
message = "更新失败,文件名后缀必须为“mpp”!";
}
map.put("message", message);
return JsonResult.Fail().setData(map);
}
try {
//获取文件路径
InputStream inputStream = file.getInputStream();
//使用通用项目阅读器 自动识别文件类型
UniversalProjectReader reader = new UniversalProjectReader();
//根据文件路径查找文件并解析文件数据
ProjectFile project = reader.read(inputStream);
//获取project中所有的任务
List<Task> taskList = project.getTasks();
//获取项目名称
String projectName = taskList.get(1).getName();
//判断是否有项目名称
if (StringUtils.isBlank(projectName)) {
map.put("message", "项目名称为空,请填写项目名称!");
return JsonResult.Fail().setData(map);
}
//根据项目名称查询是否已存在相同名称的项目 type=addProject 是导入,trype=updateProject 是更新
List<PmsProjectPlanning> projectList = this.list(new QueryWrapper<PmsProjectPlanning>().eq("PROJECT_NAME_", projectName));
if ("addProject".equals(type) && (projectList.size() != 0)) {
map.put("message", "项目名称已存在,不可重复导入!");
return JsonResult.Fail().setData(map);
}
if ("updateProject".equals(type) && (projectList.size() == 0)) {
map.put("message", "项目名称不存在,无法更新!");
return JsonResult.Fail().setData(map);
}
map.put("taskList", taskList); //任务项
map.put("projectList", projectList); //已存表任务项
return JsonResult.Success().setData(map);
} catch (MPXJException e) {
map.put("message", "MPXJUtils.method [readFile]: MPXJException-" + e);
return JsonResult.Fail().setData(map);
} catch (Exception e) {
map.put("message", "MPXJUtils.method [readFile]: Exception-" + e);
return JsonResult.Fail().setData(map);
}
}
2.3 读取project文件数据并组装数据方法(analysisFile)如下:
/**
* @Description: 获取project中的数据,并组装到项目计划实体中
* @author: hd_gao
* @date: 2022/8/11 15:05
* @param: task
* @param: projectName
**/
private JsonResult analysisFile(Task task, String projectName) {
//任务项ID
Integer task_id = task.getID();
//任务唯一ID
Integer task_unique_id = task.getUniqueID();
//父级任务项ID
Integer task_parent_def_id = task.getParentTask().getID();
//任务项等级
Integer task_outline_level = task.getOutlineLevel();
//wbs
String task_wbs = task.getWBS();
//任务项名称
String task_name = task.getName();
//计划工作量
double task_work = task.getWork().getDuration();
//计划工作量单位
String task_work_units = task.getWork().getUnits().getName();
//计划工期
double task_duration = task.getDuration().getDuration();
//计划工期单位
String task_duration_units = task.getDuration().getUnits().getName();
//计划开始日期
Date task_start_date = task.getStart();
//计划结束日期
Date task_finish_date = task.getFinish();
//责任人
String task_operator = task.getText(1);
//获取前置任务(任务流)
List<Relation> task_predecessors = task.getPredecessors();
StringBuffer sb = new StringBuffer();
if (task_predecessors != null) {
if (task_predecessors.size() > 0) {
for (Relation relation : task_predecessors) {
Integer targetTaskId = relation.getTargetTask().getID();
if (sb.length() == 0) {
sb.append(targetTaskId);
} else {
sb.append("," + targetTaskId);
}
}
}
}
//前置任务
String task_predecessors_str = sb.toString();
//任务项负责人
String task_leader = "";
List<ResourceAssignment> resourceAssignments = task.getResourceAssignments();
if (resourceAssignments != null && resourceAssignments.size() != 0) {
Boolean flag = true;
for (ResourceAssignment resourceAssignment : resourceAssignments) {
if (resourceAssignment.getResource() == null) {
flag = false;
break;
}
task_leader = task_leader + "," + resourceAssignment.getResource().getName();
}
if (flag) {
task_leader = task_leader.substring(1, task_leader.length());
}
}
//完成百分比
String task_percentage = String.valueOf(task.getPercentageComplete().byteValue()) + "%";
//组装项目计划数据
PmsProjectPlanning pmsProjectPlanning = new PmsProjectPlanning();
pmsProjectPlanning.setId(IdGenerator.getIdStr()); //id
pmsProjectPlanning.setTaskId(task_id.toString()); //任务ID
pmsProjectPlanning.setTaskUniqueId(task_unique_id.toString()); //任务唯一ID
pmsProjectPlanning.setTaskParentDefId(task_parent_def_id.toString()); //父任务ID
pmsProjectPlanning.setTaskOutlineLevel(task_outline_level.toString());//任务级别
pmsProjectPlanning.setTaskWbs(task_wbs); //WBS码
pmsProjectPlanning.setProjectName(projectName); //项目名称
pmsProjectPlanning.setTaskName(task_name); //任务名称
pmsProjectPlanning.setTaskWork(String.valueOf(task_work)); //计划工作量
pmsProjectPlanning.setTaskWorkUnits(task_work_units); //计划工作量单位
pmsProjectPlanning.setTaskActualWorkUnits(task_work_units); //实际工作量单位
pmsProjectPlanning.setTaskDuration(String.valueOf(task_duration)); //计划工期
pmsProjectPlanning.setTaskDurationUnits(task_duration_units); //计划工期单位
pmsProjectPlanning.setTaskStartDate(task_start_date); //计划开始时间
pmsProjectPlanning.setTaskFinishDate(task_finish_date); //计划结束时间
pmsProjectPlanning.setTaskOperator(task_operator); //负责人
pmsProjectPlanning.setTaskWorkStatus("未开始"); //任务状态
pmsProjectPlanning.setFrontTask(task_predecessors_str); //前置任务
pmsProjectPlanning.setTaskLeader(task_leader); //任务负责人
pmsProjectPlanning.setTaskPercentage(task_percentage); //完成百分比
Map map = new HashMap<>();
map.put("pmsProjectPlanning", pmsProjectPlanning);
return JsonResult.Success().setData(map);
}
3.实现效果
提示:上传project文件后的效果
4.代码目录
总结
1、特别注意只能上传project2010以下的版本,2010以上的版本会报错;
2、如果project内容全部都是一级标题,可以省去很多判断代码,直接for循环读取每行数据即可;
3、因为我需要在页面显示四级任务项目,并且需要树形显示,所以要判断数据是几级任务,加上相应的父级关联关系;