package org.jeecg.modules.excel;
|
|
import com.google.common.base.Joiner;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.ss.usermodel.CellStyle;
|
import org.apache.poi.ss.usermodel.IndexedColors;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.jeecg.common.api.vo.Result;
|
import org.jeecg.common.util.DateUtils;
|
import org.jeecg.modules.feign.SystemClient;
|
import org.jeecg.modules.feign.model.SysUser;
|
import org.jeecg.modules.project.entity.ProProject;
|
import org.jeecg.modules.project.entity.ProProjectLink;
|
import org.jeecg.modules.project.entity.ProSummary;
|
import org.jeecg.modules.project.vo.ProjectVo;
|
import org.jxls.command.CellDataUpdater;
|
import org.jxls.common.CellData;
|
import org.jxls.common.CellRef;
|
import org.jxls.common.Context;
|
import org.jxls.transform.poi.PoiCellData;
|
import org.jxls.transform.poi.PoiTransformer;
|
import org.jxls.transform.poi.PoiUtil;
|
import org.jxls.util.JxlsHelper;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.stereotype.Component;
|
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
import java.util.stream.Collectors;
|
|
@Component
|
public class ExcelUtils {
|
@Autowired
|
private SystemClient systemClient;
|
@Value(value = "${jeecg.path.upload}")
|
private String uploadpath;
|
|
//读取的excel 转为项目bean
|
public ProProject CoverProProject(List<Map<Integer, String>> listMap) {
|
Map<String, String> usersMap = new HashMap<>();
|
ProProject project = new ProProject();
|
ProSummary summary = new ProSummary();
|
|
Map<Integer, String> map0 = listMap.get(0);
|
project.setXmmc(map0.get(2));//项目名称
|
|
String xmfzr = map0.get(4);
|
|
parseUsers(xmfzr, project, usersMap, null, 1);
|
|
|
project.setBgrq(getDate(map0.get(6)));// 报告日期
|
project.setXmbh(map0.get(8));//项目编号
|
|
Map<Integer, String> map1 = listMap.get(1);
|
project.setXmbj(map1.get(2));//项目背景
|
|
Map<Integer, String> map2 = listMap.get(2);
|
project.setXmmb(map2.get(2));//项目目标
|
|
Map<Integer, String> map3 = listMap.get(3);
|
summary.setJqmb(map3.get(2));//近期目标
|
|
Map<Integer, String> map4 = listMap.get(4);
|
|
project.setKsrq(getDate(map4.get(2))); // 开始日期
|
|
|
Map<Integer, String> map5 = listMap.get(5);
|
project.setJsrq(getDate(map5.get(2)));// 结束日期
|
|
Map<Integer, String> map6 = listMap.get(6);
|
summary.setBgsx(map6.get(2));// 变更事项
|
Map<Integer, String> map7 = listMap.get(7);
|
//项目集code
|
project.setGroupCode(map7.get(9));
|
|
Map<Integer, String> map8 = listMap.get(8);//概述
|
summary.setGs(map8.get(2));
|
summary.setJdsfzc(map8.get(4));
|
summary.setSfxyzc(map8.get(5));
|
summary.setSfyxdd(map8.get(6));
|
summary.setFxyc(map8.get(7));
|
|
Map<Integer, String> map9 = listMap.get(9);//预测
|
summary.setYc(map9.get(2));
|
|
|
|
|
|
|
/* Map<Integer, String> map4 = listMap.get(4);
|
String regEx = "[^0-9]";
|
Pattern p = Pattern.compile(regEx);
|
Matcher m = p.matcher(map4.get(1));
|
project.setSysj(Integer.parseInt(m.replaceAll("").trim())); //剩余时间*/
|
|
/* Map<Integer, String> map5 = listMap.get(5);
|
project.setBzgz(map5.get(1));//本周工作
|
|
Map<Integer, String> map6 = listMap.get(6);
|
project.setXzgz(map6.get(1));//下周工作*/
|
|
//从excel第9行开始读'项目环节'
|
List<ProProjectLink> linkList = new ArrayList<>();
|
for (int i = 11; i < listMap.size(); i++) {
|
Map<Integer, String> map = listMap.get(i);
|
ProProjectLink link = new ProProjectLink();
|
if (map.get(0) == null && map.get(1) == null && map.get(2) == null && map.get(3) == null && map.get(4) == null) {
|
//如果多列都没数据，说明下方为空白
|
break;
|
}
|
link.setSxh(map.get(1));//原始序号
|
if (map.containsKey(2))
|
link.setXmhj(map.get(2)); //项目环节
|
if (map.containsKey(3))
|
link.setQssj(getDate(map.get(3))); //起始时间
|
if (map.containsKey(4))
|
link.setYjwcsj(getDate(map.get(4))); //预计完成时间
|
if (map.containsKey(5))
|
|
link.setCxsj(getDay(map.get(5)));//持续时间
|
if (map.containsKey(6))
|
link.setSjwcsj(getDate(map.get(6)));//实际完成时间
|
if (map.containsKey(7))
|
if (!StringUtils.isEmpty(map.get(7)) && map.get(7).equals("√"))
|
link.setWcjd(1);
|
else
|
link.setWcjd(0);
|
//TODO 资源名称为用户真实姓名 存入系统后无法对应
|
if (map.containsKey(8))
|
parseUsers(map.get(8), null, usersMap, link, 2);
|
|
//根据序号判断菜单层级
|
String sxh = link.getSxh();
|
//没有序号则不导入
|
if (StringUtils.isEmpty(sxh)) continue;
|
char[] chars = sxh.toCharArray();
|
int dotCount = 0;
|
char dot = '.'; //根据有几个点判断是几级菜单
|
for (int j = 0; j < chars.length; j++) {
|
if (chars[j] == dot) dotCount++;
|
}
|
|
//判断等级
|
//1级菜单
|
if (dotCount == 0) {
|
link.setLevel(1);
|
link.setLevel1(getDay(link.getSxh()));
|
}
|
//2级菜单
|
else if (dotCount == 1) {
|
String str1 = link.getSxh().substring(0, link.getSxh().indexOf("."));
|
String str2 = link.getSxh().substring(link.getSxh().indexOf(".") + 1);
|
link.setLevel(2);
|
link.setLevel1(getDay(str1));
|
link.setLevel2(getDay(str2));
|
}
|
//设置3级目录序号
|
else if (dotCount == 2) {
|
String str1 = link.getSxh().substring(0, link.getSxh().indexOf("."));
|
String str2 = link.getSxh().substring(link.getSxh().indexOf(".") + 1, link.getSxh().lastIndexOf("."));
|
String str3 = link.getSxh().substring(link.getSxh().lastIndexOf(".") + 1);
|
link.setLevel(3);
|
link.setLevel1(getDay(str1));
|
link.setLevel2(getDay(str2));
|
link.setLevel3(getDay(str3));
|
}
|
//4级菜单
|
else if (dotCount == 3) {
|
String substring1 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring2 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring3 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring4 = sxh;
|
link.setLevel(4);
|
link.setLevel1(getDay(substring1));
|
link.setLevel2(getDay(substring2));
|
link.setLevel3(getDay(substring3));
|
link.setLevel4(getDay(substring4));
|
}
|
//5级菜单
|
else if (dotCount == 4) {
|
String substring1 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring2 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring3 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring4 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring5 = sxh;
|
link.setLevel(5);
|
link.setLevel1(getDay(substring1));
|
link.setLevel2(getDay(substring2));
|
link.setLevel3(getDay(substring3));
|
link.setLevel4(getDay(substring4));
|
link.setLevel5(getDay(substring5));
|
}
|
//4级菜单
|
else if (dotCount == 5) {
|
String substring1 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring2 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring3 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring4 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring5 = sxh.substring(0, sxh.indexOf("."));
|
sxh = sxh.substring(sxh.indexOf(".") + 1);
|
String substring6 = sxh;
|
link.setLevel(6);
|
link.setLevel1(getDay(substring1));
|
link.setLevel2(getDay(substring2));
|
link.setLevel3(getDay(substring3));
|
link.setLevel4(getDay(substring4));
|
link.setLevel5(getDay(substring5));
|
link.setLevel6(getDay(substring6));
|
}
|
|
linkList.add(link);
|
}
|
|
|
project.setLinkList(linkList);
|
project.setSummary(summary);
|
|
return project;
|
}
|
|
/**
|
* @param userStr 真实姓名
|
* @param project 项目
|
* @param usersMap 缓存用户真实姓名查出来的username
|
* @param type 1-项目负责人 2-项目成员
|
*/
|
private void parseUsers(String userStr, ProProject project, Map<String, String> usersMap, ProProjectLink link, int type) {
|
List<String> userList = new ArrayList<>();
|
if (!StringUtils.isEmpty(userStr)) {
|
String[] split = userStr.split(",");
|
if (split != null && split.length > 0) {
|
List<String> list = Arrays.asList(split);
|
list = list.stream().filter(x -> x.length() > 1).collect(Collectors.toList());
|
for (int i = 0; i < list.size(); i++) {
|
String item = list.get(i);
|
if (usersMap.containsKey(item)) {
|
userList.add(usersMap.get(item));
|
} else {
|
Result<SysUser> user = systemClient.queryByName(item);
|
if (user != null && user.getResult() != null && user.getResult().getUsername() != null) {
|
userList.add(user.getResult().getUsername());
|
usersMap.put(item, user.getResult().getUsername());
|
}
|
}
|
|
}
|
if (userList.size() > 0) {
|
String join = Joiner.on(",").join(userList);
|
if (type == 1) {
|
project.setXmfzr(join);
|
} else {
|
link.setZymc(join);
|
}
|
|
}
|
|
|
}
|
|
}
|
}
|
|
public static Integer getDay(String str) {
|
if (!StringUtils.isEmpty(str)) {
|
try {
|
String regEx = "[^0-9]";
|
Pattern p = Pattern.compile(regEx);
|
Matcher m = p.matcher(str);
|
String res = m.replaceAll("").trim();
|
return Integer.parseInt(res);
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
|
}
|
return null;
|
}
|
|
public static Date getDate(String str) {
|
if (!StringUtils.isEmpty(str)) {
|
try {
|
if (str.contains("年")) {
|
|
return DateUtils.str2Date(str, new SimpleDateFormat("yyyy年MM月dd日"));
|
} else {
|
return DateUtils.str2Date(str, new SimpleDateFormat("yyyy/MM/dd"));
|
}
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
return null;
|
}
|
|
|
public String exportProject(List<Map<String, Object>> pmo, List<String> sheetNames,List<Map<String, Object>> lcb, List<String> lcbSheetNames,List<Map<String, Object>> ylb, List<String> ylbSheetNames,List<ProjectVo> dtProList, List<String> dtSheetNames) throws Exception {
|
// String start = DateUtils.weekToDayStartStr(DateUtils.getCurYear(), DateUtils.getCurWeek(), "yyyy-MM-dd");
|
// String weekInMonth = DateUtils.getWeekInMonth(DateUtils.str2Date(start, DateUtils.date_sdf.get()));
|
String end = DateUtils.weekToFriDayEndStr(DateUtils.getCurYear(),DateUtils.getCurWeek(), "yyyy-MM-dd");
|
String weekInMonth = DateUtils.getWeekInMonth(end,2);
|
String templateFileName = uploadpath + File.separator + "exportTemplate" + File.separator + "pro" + File.separator + "tp_project.xlsx";
|
String filePath = "export" + File.separator + "项目工作看板-" + weekInMonth + DateUtils.date2Str(new Date(),DateUtils.timeFormat.get()) + ".xlsx";
|
|
String fileName = uploadpath + File.separator + filePath;
|
File templateFile = new File(templateFileName);
|
|
templateFile.deleteOnExit();
|
InputStream is = new FileInputStream(templateFile);
|
File file = new File(fileName);
|
// 流
|
OutputStream os = new BufferedOutputStream(new FileOutputStream(file));
|
|
|
Context context = PoiTransformer.createInitialContext();
|
context.putVar("departments", pmo);
|
context.putVar("sheetNames", sheetNames);
|
|
context.putVar("lcbProList", lcb);
|
context.putVar("lcbSheetNames", lcbSheetNames);
|
|
context.putVar("ylbProList", ylb);
|
context.putVar("ylbSheetNames", ylbSheetNames);
|
|
context.putVar("dtProList", dtProList);
|
context.putVar("dtSheetNames", dtSheetNames);
|
|
|
JxlsHelper
|
.getInstance()
|
.setUseFastFormulaProcessor(false)
|
.setDeleteTemplateSheet(true)
|
.processTemplate(is, os, context);
|
|
return filePath;
|
// context.putVar("myCellUpdater", new MyCellUpdater());
|
/* // 获取文件配置
|
JxlsHelper helper = JxlsHelper.getInstance();
|
// 绑定 输入输出
|
PoiTransformer transformer = (PoiTransformer) helper.createTransformer(is, os);
|
// 获取 工作簿
|
Workbook workbook = transformer.getWorkbook();
|
// 获取表
|
Sheet sheet = workbook.getSheetAt(0);
|
// 强制 表格执行公式
|
sheet.setForceFormulaRecalculation(true);
|
// 放入数据
|
helper.processTemplate(context, transformer);*/
|
|
|
}
|
|
public static class MyCellUpdater implements CellDataUpdater {
|
|
@Override
|
public void updateCellData(CellData cellData, CellRef targetCell, Context context) {
|
|
|
PoiCellData poiCellData = (PoiCellData) cellData;
|
|
poiCellData.getCellStyle().setFillForegroundColor(IndexedColors.YELLOW.getIndex());
|
System.err.println(poiCellData);
|
}
|
}
|
}
|
