import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.enums.WriteDirectionEnum;
|
import com.alibaba.excel.metadata.Head;
|
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
|
import com.alibaba.excel.write.metadata.WriteSheet;
|
import com.alibaba.excel.write.metadata.fill.FillConfig;
|
import com.alibaba.excel.write.metadata.fill.FillWrapper;
|
import com.alibaba.fastjson.JSON;
|
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
|
import io.swagger.models.auth.In;
|
import lombok.Data;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.jeecg.JeecgWeeklyCloudApplication;
|
import org.jeecg.common.api.vo.Result;
|
import org.jeecg.common.util.DateUtils;
|
import org.jeecg.modules.weekly.entity.WekEvaluate;
|
import org.jeecg.modules.weekly.feign.SystemClient;
|
import org.jeecg.modules.weekly.feign.model.SysUser;
|
import org.jeecg.modules.weekly.service.IWekEvaluateService;
|
import org.junit.Test;
|
import org.junit.runner.RunWith;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.boot.test.context.SpringBootTest;
|
import org.springframework.test.context.junit4.SpringRunner;
|
|
import java.io.*;
|
import java.text.SimpleDateFormat;
|
import java.time.LocalDate;
|
import java.time.temporal.TemporalAdjusters;
|
import java.util.*;
|
import java.util.concurrent.atomic.AtomicBoolean;
|
import java.util.concurrent.atomic.AtomicInteger;
|
import java.util.concurrent.atomic.AtomicReference;
|
import java.util.stream.Collectors;
|
|
@RunWith(SpringRunner.class)
|
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT, classes = JeecgWeeklyCloudApplication.class)
|
public class WeeklyTest {
|
@Value(value = "${jeecg.path.upload}")
|
private String uploadpath;
|
@Autowired
|
private IWekEvaluateService wekEvaluateService;
|
@Autowired
|
private SystemClient systemClient;
|
|
/**
|
* 获取一年的周
|
*/
|
@Test
|
public void getWeekInYear() {
|
List<Map<String, Object>> res = new ArrayList<>();
|
//当前年
|
int curYear = DateUtils.getCurYear();
|
//统计一年有多少周
|
int yearWeekCount = 0;
|
//统计一年有多少周
|
int seasonCount = 0;
|
//一年12个月
|
for (int i = 1; i <= 12; i++) {
|
|
//月
|
Map<String, Object> monthMap = new HashMap<>();
|
monthMap.put("titile", i + "月");
|
List<Map<String, Object>> weekList = new ArrayList<>();
|
monthMap.put("children", weekList);
|
res.add(monthMap);
|
//季
|
if (i % 3 == 0) {
|
seasonCount++;
|
Map<String, Object> seasonMap = new HashMap<>();
|
seasonMap.put("titile", "第" + seasonCount + "季度");
|
seasonMap.put("column", "s" + seasonCount);
|
res.add(seasonMap);
|
}
|
|
|
LocalDate date = LocalDate.of(curYear, i, 1);
|
//获取当月第一天是星期几
|
int week = date.getDayOfWeek().getValue();
|
//这个月最后一天
|
date = date.with(TemporalAdjusters.lastDayOfMonth());
|
//获取这天是这个月的第几天
|
int dayOfMonth = date.getDayOfMonth();
|
//统计一个月有多少周 （TODO ：此处分周逻辑保持和周报获取周逻辑一致，定义周日在哪个月则设定本周属于哪个月）
|
int monthWeekCount = 0;
|
for (int j = 1; j <= dayOfMonth; j++) {
|
LocalDate everyDay = LocalDate.of(curYear, i, j);
|
int d = everyDay.getDayOfWeek().getValue();
|
//System.err.println("日期：" + everyDay + "-星期：" + d);
|
if (d % 7 == 0) {
|
monthWeekCount++;
|
yearWeekCount++;
|
String weekToDayStartStr = DateUtils.weekToDayStartStr(yearWeekCount);
|
String weekToDayEndStr = DateUtils.weekToDayEndStr(yearWeekCount);
|
System.out.println(i + "月：" + monthWeekCount + "周-No:" + yearWeekCount + "------" + weekToDayStartStr + "==" + weekToDayEndStr);
|
//添加每个月的周
|
Map<String, Object> weekMap = new HashMap<>();
|
weekMap.put("title", i + "月第" + monthWeekCount + "周");
|
weekMap.put("column", "m" + i + "w" + monthWeekCount);
|
weekMap.put("wstart", weekToDayStartStr);
|
weekMap.put("wend", weekToDayEndStr);
|
weekMap.put("wno", yearWeekCount);
|
weekList.add(weekMap);
|
|
}
|
}
|
|
}
|
}
|
|
|
@Test
|
public void exportWeekly() {
|
String roopath = System.getProperty("user.dir");
|
roopath = roopath.substring(0, roopath.indexOf("/lb-boot/") + 8);
|
//模板路径
|
String templateFileName = roopath + File.separator + "export-template" + File.separator + "weekly" + File.separator + "depart_users_weekly.xlsx";
|
//生成文件路径
|
String fileName = roopath + File.separator + "export" + File.separator + "depart_users_weekly" + System.currentTimeMillis() + ".xlsx";
|
|
|
}
|
|
|
@Test
|
public void demo1() {
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
|
Calendar cal = Calendar.getInstance();
|
// 设置每周的开始日期
|
cal.setFirstDayOfWeek(Calendar.MONDAY);
|
cal.setMinimalDaysInFirstWeek(0);
|
cal.set(Calendar.YEAR, 2023);
|
cal.set(Calendar.WEEK_OF_YEAR, 1);
|
cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek());
|
String beginDate = sdf.format(cal.getTime());
|
|
cal.add(Calendar.DAY_OF_WEEK, 6);
|
String endDate = sdf.format(cal.getTime());
|
System.out.println(2023 + "年第" + 1 + "周起止时间：" + beginDate + "=----" + endDate);
|
|
}
|
|
//测试周的计算逻辑
|
@Test
|
public void demo2() {
|
String sheet11 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-02", DateUtils.date_sdf.get()));
|
String sheet12 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-09", DateUtils.date_sdf.get()));
|
String sheet13 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-16", DateUtils.date_sdf.get()));
|
String sheet14 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-23", DateUtils.date_sdf.get()));
|
String sheet15 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-01-30", DateUtils.date_sdf.get()));
|
String sheet1 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-11-27", DateUtils.date_sdf.get()));
|
String sheet2 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-04", DateUtils.date_sdf.get()));
|
String sheet3 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-11", DateUtils.date_sdf.get()));
|
String sheet4 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-18", DateUtils.date_sdf.get()));
|
String sheet5 = DateUtils.getWeekInMonth(DateUtils.str2Date("2023-12-25", DateUtils.date_sdf.get()));
|
String sheet = DateUtils.getWeekInMonth(DateUtils.str2Date("2024-01-01", DateUtils.date_sdf.get()));
|
System.out.println(sheet11);
|
System.out.println(sheet12);
|
System.out.println(sheet13);
|
System.out.println(sheet14);
|
System.out.println(sheet15);
|
System.out.println(sheet1);
|
System.out.println(sheet2);
|
System.out.println(sheet3);
|
System.out.println(sheet4);
|
System.out.println(sheet5);
|
System.out.println(sheet);
|
|
}
|
|
public String getWeekInMonth(Date date) {
|
Calendar calendar = Calendar.getInstance();
|
calendar.setTime(date);
|
calendar.setFirstDayOfWeek(Calendar.MONDAY);
|
calendar.setMinimalDaysInFirstWeek(7); //获取当前日期所在周周日
|
int week = calendar.get(Calendar.WEEK_OF_MONTH);
|
int month = calendar.get(Calendar.MONTH) + 1;
|
int year = calendar.get(Calendar.YEAR);
|
|
return year + "年" + month + "月第" + week + "周";
|
|
}
|
|
private Map<String, Object> createStatisticsYearTitle() {
|
Map<String, Object> root = new HashMap<>();
|
//title
|
List<Map<String, Object>> res = new ArrayList<>();
|
//周号对应colmun
|
Map<Integer, String> map = new HashMap<>();
|
//需要合并的行或列信息
|
TreeMap<Integer, Integer> mergeSeason = new TreeMap<>();
|
TreeMap<Integer, Integer> mergeMonth = new TreeMap<>();
|
//当前年
|
int curYear = DateUtils.getCurYear();
|
//统计一年有多少周
|
int yearWeekCount = 0;
|
int sort = 0;
|
//季
|
int seasonCount = 1;
|
|
|
//一年12个月
|
for (int i = 1; i <= 12; i++) {
|
|
|
LocalDate date = LocalDate.of(curYear, i, 1);
|
//获取当月第一天是星期几
|
int week = date.getDayOfWeek().getValue();
|
//这个月最后一天
|
date = date.with(TemporalAdjusters.lastDayOfMonth());
|
//获取这天是这个月的第几天
|
int dayOfMonth = date.getDayOfMonth();
|
//统计一个月有多少周 （TODO ：此处分周逻辑保持和周报获取周逻辑一致，定义周日在哪个月则设定本周属于哪个月）
|
int monthWeekCount = 0;
|
for (int j = 1; j <= dayOfMonth; j++) {
|
LocalDate everyDay = LocalDate.of(curYear, i, j);
|
int d = everyDay.getDayOfWeek().getValue();
|
//System.err.println("日期：" + everyDay + "-星期：" + d);
|
if (d % 7 == 0) {
|
monthWeekCount++;
|
yearWeekCount++;
|
sort++;
|
String weekToDayStartStr = DateUtils.weekToDayStartStr(yearWeekCount);
|
String weekToDayEndStr = DateUtils.weekToDayEndStr(yearWeekCount);
|
System.out.println(i + "月：" + monthWeekCount + "周-No:" + yearWeekCount + "------" + weekToDayStartStr + "==" + weekToDayEndStr);
|
//添加每个月的周
|
Map<String, Object> weekMap = new HashMap<>();
|
weekMap.put("title", i + "月第" + monthWeekCount + "周");
|
weekMap.put("month", i + "月");
|
weekMap.put("week", "第" + monthWeekCount + "周");
|
weekMap.put("season", seasonCount + "季度");
|
weekMap.put("column", "{data.w" + (curYear * 100 + yearWeekCount) + "_1}");
|
weekMap.put("wstart", weekToDayStartStr);
|
weekMap.put("wend", weekToDayEndStr);
|
weekMap.put("wno", yearWeekCount);
|
weekMap.put("type", "自评");
|
weekMap.put("sort", sort);
|
//weekMap.put("curWeek", DateUtils.getCurWeek());
|
res.add(weekMap);
|
|
sort++;
|
Map<String, Object> weekMap2 = new HashMap<>();
|
weekMap2.put("title", i + "月第" + monthWeekCount + "周");
|
weekMap2.put("month", i + "月");
|
weekMap2.put("week", "第" + monthWeekCount + "周");
|
weekMap2.put("season", seasonCount + "季度");
|
weekMap2.put("column", "{data.w" + (curYear * 100 + yearWeekCount) + "_2}");
|
weekMap2.put("wstart", weekToDayStartStr);
|
weekMap2.put("wend", weekToDayEndStr);
|
weekMap2.put("wno", yearWeekCount);
|
weekMap2.put("type", "考评");
|
weekMap2.put("sort", sort);
|
res.add(weekMap2);
|
map.put(yearWeekCount, "m" + i + "w" + monthWeekCount);
|
|
}
|
}
|
|
sort++;
|
//每个月加考评平均分
|
//月度
|
Map<String, Object> mon1 = new HashMap<>();
|
mon1.put("season", seasonCount + "季度");
|
mon1.put("week", "月度");
|
mon1.put("month", i + "月绩效");
|
mon1.put("column", "{data.m" + i + "_1}");
|
mon1.put("type", "月度");
|
mon1.put("sort", sort);
|
res.add(mon1);
|
|
sort++;
|
//绩效等级
|
Map<String, Object> mon2 = new HashMap<>();
|
mon2.put("season", seasonCount + "季度");
|
mon2.put("week", "绩效等级");
|
mon2.put("month", i + "月绩效");
|
mon2.put("column", "{data.m" + i + "_2}");
|
mon2.put("type", "绩效等级");
|
mon2.put("sort", sort);
|
res.add(mon2);
|
|
sort++;
|
//发放比例
|
Map<String, Object> mon3 = new HashMap<>();
|
mon3.put("season", seasonCount + "季度");
|
mon3.put("week", "发放比例");
|
mon3.put("month", i + "月绩效");
|
mon3.put("column", "{data.m" + i + "_3}");
|
mon3.put("type", "发放比例");
|
mon3.put("sort", sort);
|
res.add(mon3);
|
//季
|
if (i % 3 == 0) {
|
|
sort++;
|
//发放比例
|
Map<String, Object> s1 = new HashMap<>();
|
s1.put("season", seasonCount + "季度");
|
s1.put("week", "季度");
|
s1.put("month", seasonCount + "季度绩效");
|
s1.put("column", "{data.s" + seasonCount + "_1}");
|
s1.put("type", "季度");
|
s1.put("sort", sort);
|
res.add(s1);
|
|
sort++;
|
//发放比例
|
Map<String, Object> s2 = new HashMap<>();
|
s2.put("season", seasonCount + "季度");
|
s2.put("week", "绩效等级");
|
s2.put("month", seasonCount + "季度绩效");
|
s2.put("column", "{data.s" + seasonCount + "_2}");
|
s2.put("type", "绩效等级");
|
s2.put("sort", sort);
|
res.add(s2);
|
|
sort++;
|
//发放比例
|
Map<String, Object> s3 = new HashMap<>();
|
s3.put("season", seasonCount + "季度");
|
s3.put("week", "发放比例");
|
s3.put("month", seasonCount + "季度绩效");
|
s3.put("column", "{data.s" + seasonCount + "_3}");
|
s3.put("type", "发放比例");
|
s3.put("sort", sort);
|
res.add(s3);
|
seasonCount++;
|
}
|
}
|
root.put("colmuns", res);
|
root.put("config", map);
|
System.err.println(res);
|
System.err.println(JSON.toJSON(res));
|
//计算合并数据
|
//1.季度
|
Map<Object, List<Map<String, Object>>> seasonMap = res.stream().collect(Collectors.groupingBy(item -> item.get("season")));
|
seasonMap.forEach((season, list) -> {
|
//季度
|
Integer s = Integer.parseInt(season.toString().replaceAll("\\D+", ""));
|
int size = list.size();
|
mergeSeason.put(s, size);
|
|
});
|
//2.月
|
List<MergeBean> monMergeList = new ArrayList<>();
|
List<MergeBean> wekMergeList = new ArrayList<>();
|
AtomicReference<Integer> monMergeIndex = new AtomicReference<>(1);
|
res.forEach(item -> {
|
String month = (String) item.get("month");
|
MergeBean bean = new MergeBean();
|
bean.setCol(month);
|
bean.setSort(monMergeIndex.get());
|
monMergeIndex.getAndSet(monMergeIndex.get() + 1);
|
monMergeList.add(bean);
|
|
String week = (String) item.get("week");
|
MergeBean bean2 = new MergeBean();
|
bean2.setCol(week);
|
bean2.setSort(monMergeIndex.get());
|
monMergeIndex.getAndSet(monMergeIndex.get() + 1);
|
wekMergeList.add(bean2);
|
|
});
|
Map<String, List<MergeBean>> monGroupList = monMergeList.stream().collect(Collectors.groupingBy(
|
item -> item.getCol(),
|
LinkedHashMap::new,
|
Collectors.toList()));
|
|
List<Integer> mergeWeek = new ArrayList<>();
|
LinkedHashMap<String, List<Map<String, Object>>> collect = res.stream().collect(Collectors.groupingBy(item -> item.get("month").toString(), LinkedHashMap::new, Collectors.toList()));
|
collect.forEach((m, list) -> {
|
LinkedHashMap<String, List<Map<String, Object>>> week = list.stream().collect(Collectors.groupingBy(item -> item.get("week").toString(), LinkedHashMap::new, Collectors.toList()));
|
week.forEach((w, weekList) -> {
|
mergeWeek.add(weekList.size());
|
});
|
});
|
|
|
monGroupList.forEach((key, list) -> {
|
mergeMonth.put(mergeMonth.size(), list.size());
|
});
|
|
|
root.put("mergeSeason", mergeSeason);
|
root.put("mergeMonth", mergeMonth);
|
root.put("mergeWeek", mergeWeek);
|
|
|
return root;
|
}
|
|
@Test
|
public void createYearWeekData() {
|
Map<String, Object> statisticsYearTitle = createStatisticsYearTitle();
|
System.out.println(JSON.toJSON(statisticsYearTitle));
|
|
|
}
|
|
|
@Test
|
public void exportEvaTemplate() {
|
|
String roopath = System.getProperty("user.dir");
|
roopath = roopath.substring(0, roopath.indexOf("/lb-boot/") + 8);
|
//模板路径
|
String templateFileName = roopath + File.separator + "export-template" + File.separator + "weekly" + File.separator + "weeklyEva.xlsx";
|
//生成文件路径
|
String fileName = roopath + File.separator + "export" + File.separator + "weeklyEva" + System.currentTimeMillis() + ".xlsx";
|
String fileName2 = roopath + File.separator + "export" + File.separator + "weeklyRes" + System.currentTimeMillis() + ".xlsx";
|
|
|
Map<String, Object> res = createStatisticsYearTitle();
|
List<Map<String, Object>> colmuns = new ArrayList<>();
|
if (res.containsKey("colmuns")) {
|
colmuns = (List<Map<String, Object>>) res.get("colmuns");
|
}
|
Map<String, Object> config = new HashMap<>();
|
if (res.containsKey("config")) {
|
config = (Map<String, Object>) res.get("config");
|
}
|
TreeMap<Integer, Integer> mergeSeason = new TreeMap<>();
|
if (res.containsKey("mergeSeason")) {
|
mergeSeason = (TreeMap<Integer, Integer>) res.get("mergeSeason");
|
}
|
TreeMap<Integer, Integer> mergeMonth = new TreeMap<>();
|
if (res.containsKey("mergeMonth")) {
|
mergeMonth = (TreeMap<Integer, Integer>) res.get("mergeMonth");
|
}
|
List<Integer> mergeWeek = new ArrayList<>();
|
if (res.containsKey("mergeWeek")) {
|
mergeWeek = (List<Integer>) res.get("mergeWeek");
|
}
|
System.err.println("导出");
|
System.err.println(colmuns);
|
|
File templateFile = new File(templateFileName);
|
try {
|
FileInputStream fileInputStream = new FileInputStream(templateFile);
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
//原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
|
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
|
//设置模板的第一个sheet的名称
|
// workbook.setSheetName(1, basisinfo.get("sheet").toString());
|
//写到流里
|
workbook.write(bos);
|
byte[] bArray = bos.toByteArray();
|
InputStream is = new ByteArrayInputStream(bArray);
|
//输出文件路径
|
ExcelWriter excelWriter = EasyExcel.write(fileName)
|
.registerWriteHandler(new MyHandler(mergeSeason, mergeMonth, mergeWeek))
|
.withTemplate(is).build();
|
WriteSheet writeSheet = EasyExcel.writerSheet().build();
|
|
|
FillConfig horConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
|
horConfig.setForceNewRow(true);
|
excelWriter.fill(new FillWrapper("colmuns", colmuns), horConfig, writeSheet);
|
|
|
//构建模版的 序号 部门 项目 职务
|
Map<String, Object> basInfo = new HashMap<>();
|
basInfo.put("no", "{data.no}");
|
basInfo.put("depart", "{data.depart}");
|
basInfo.put("name", "{data.name}");
|
basInfo.put("post", "{data.post}");
|
basInfo.put("year", DateUtils.getCurYear());
|
excelWriter.fill(basInfo, writeSheet);
|
// 关闭流
|
excelWriter.finish();
|
|
List<Map<String, Object>> testData = createTestData(colmuns);
|
List<Map<String, Object>> data = createWeeklyEvaData(colmuns);
|
|
exportEvaData(data, fileName, fileName2);
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* 创建绩效数据
|
*
|
* @param colmuns
|
* @return
|
*/
|
private List<Map<String, Object>> createWeeklyEvaData(List<Map<String, Object>> colmuns) {
|
List<Map<String, Object>> res = new ArrayList<>();
|
//查询当前年的所有记录 TODO 动态传入年份信息
|
int year = DateUtils.getCurYear();
|
QueryWrapper<WekEvaluate> wekEvaluateQueryWrapper = new QueryWrapper<>();
|
wekEvaluateQueryWrapper.lambda().eq(WekEvaluate::getYear, year);
|
List<WekEvaluate> wekEvaluateList = wekEvaluateService.list(wekEvaluateQueryWrapper);
|
|
if (wekEvaluateList.isEmpty()) {
|
return res;
|
}
|
//按照个人分组组装数据
|
Map<String, List<WekEvaluate>> wekEvaluategroup = wekEvaluateList.stream().collect(Collectors.groupingBy(item -> item.getZp()));
|
|
AtomicInteger index = new AtomicInteger(1); //数据行数
|
wekEvaluategroup.forEach((user, evaluateList) -> {
|
|
Map<String, Object> item = new HashMap<>();
|
item.put("no", index.get()); //序号
|
item.put("name", evaluateList.get(0).getZpName()); //姓名
|
|
//个人周报数据再按月分组
|
Map<Integer, List<WekEvaluate>> monEvaMap = evaluateList.stream().collect(Collectors.groupingBy(month -> month.getMonth()));
|
|
TreeMap<Integer, List<Integer>> seasonScoreMap = new TreeMap<>();
|
for (int i = 1; i <= 4; i++) {
|
List<Integer> list = new ArrayList<>();
|
seasonScoreMap.put(i, list);
|
}
|
|
monEvaMap.forEach((mon, monList) -> {
|
List<Integer> monthScoreList = new ArrayList<>();
|
//个人周报数据再按周分组
|
Map<Integer, List<WekEvaluate>> weekEvaMap = monList.stream().collect(Collectors.groupingBy(week -> week.getWeek()));
|
weekEvaMap.forEach((w, wekList) -> {
|
WekEvaluate eva = wekList.get(0);
|
if (eva == null) {
|
return;
|
}
|
String zpPlan = eva.getZpPlan();
|
String zpAmount = eva.getZpAmount();
|
String zpEffe = eva.getZpEffe();
|
String zpQuality = eva.getZpQuality();
|
if (StringUtils.isNotEmpty(zpPlan) && StringUtils.isNotEmpty(zpAmount) && StringUtils.isNotEmpty(zpEffe) && StringUtils.isNotEmpty(zpQuality)) {
|
int score = calcEvaScore(zpPlan, zpAmount, zpEffe, zpQuality);
|
//注意 此处生成key要和excel生成head中colnum一致
|
item.put("w" + eva.getYearWeek() + "_1", score);
|
}
|
|
String kpPlan = eva.getKpPlan();
|
String kpAmount = eva.getKpAmount();
|
String kpEffe = eva.getKpEffe();
|
String kpQuality = eva.getKpQuality();
|
if (StringUtils.isNotEmpty(kpPlan) && StringUtils.isNotEmpty(kpAmount) && StringUtils.isNotEmpty(kpEffe) && StringUtils.isNotEmpty(kpQuality)) {
|
int score = calcEvaScore(kpPlan, kpAmount, kpEffe, kpQuality);
|
//注意 此处生成key要和excel生成head中colnum一致
|
item.put("w" + eva.getYearWeek() + "_2", score);
|
monthScoreList.add(score);
|
}
|
|
|
});
|
|
//月平均
|
double score = monthScoreList.stream().mapToInt(Integer::intValue)
|
.average()
|
.orElse(0);
|
int avg = (int) Math.round(score);
|
if(avg == 0){
|
item.put("m" + mon + "_1", "");
|
}else {
|
item.put("m" + mon + "_1", avg);
|
}
|
//月绩效等级
|
String monLevel = "C";
|
String monPay = "100%";
|
if (avg == 0) {
|
monLevel = "";
|
monPay = "";
|
} else if (avg >= 95) {
|
monPay = "140%";
|
monLevel = "A";
|
} else if (avg <= 95 && avg >= 85) {
|
monLevel = "B";
|
monPay = "110%";
|
} else if (avg <= 84 && avg >= 75) {
|
monLevel = "C";
|
monPay = "100%";
|
} else if (avg <= 74 && avg >= 60) {
|
monLevel = "D";
|
monPay = "90%";
|
} else if (avg <= 59) {
|
monLevel = "E";
|
monPay = "不合格";
|
}
|
item.put("m" + mon + "_2", monLevel);
|
item.put("m" + mon + "_3", monPay);
|
|
//根据季添加考评分
|
if (mon <= 3) {
|
seasonScoreMap.get(1).add(avg);
|
} else if (mon >= 4 && mon <= 6) {
|
seasonScoreMap.get(2).add(avg);
|
} else if (mon >= 7 && mon <= 9) {
|
seasonScoreMap.get(3).add(avg);
|
} else if (mon >= 10 && mon <= 12) {
|
seasonScoreMap.get(4).add(avg);
|
}
|
|
});
|
|
seasonScoreMap.forEach((season, seasonScoreList) -> {
|
//季平均
|
double score = seasonScoreList.stream().mapToInt(Integer::intValue)
|
.average()
|
.orElse(0);
|
int avg = (int) Math.round(score);
|
if(avg==0){
|
item.put("s" + season + "_1", "");
|
}else {
|
item.put("s" + season + "_1", avg);
|
}
|
|
//季绩效等级
|
String seasonLevel = "C";
|
String seasonPay = "100%";
|
if (avg == 0) {
|
seasonLevel = "";
|
seasonPay = "";
|
} else if (avg >= 95) {
|
seasonLevel = "A";
|
seasonPay = "140%";
|
} else if (avg <= 95 && avg >= 85) {
|
seasonLevel = "B";
|
seasonPay = "110%";
|
} else if (avg <= 84 && avg >= 75) {
|
seasonLevel = "C";
|
seasonPay = "100%";
|
} else if (avg <= 74 && avg >= 60) {
|
seasonLevel = "D";
|
seasonPay = "90%";
|
} else if (avg <= 59) {
|
seasonLevel = "E";
|
seasonPay = "不合格";
|
}
|
item.put("s" + season + "_2", seasonLevel);
|
item.put("s" + season + "_3", seasonPay);
|
|
|
});
|
|
|
res.add(item);
|
index.getAndIncrement();
|
});
|
|
|
//对不存在的数据添加空数据，避免导出excel不渲染
|
for (int i = 0; i < res.size(); i++) {
|
Map<String, Object> d = res.get(i);
|
for (int j = 0; j < colmuns.size(); j++) {
|
Map<String, Object> c = colmuns.get(j);
|
String column = c.get("column").toString().replace("{data.", "").replace("}", "");
|
String colStr = "";
|
for (String key : d.keySet()) {
|
colStr +=key;
|
colStr +=",";
|
}
|
if(!colStr.contains(column)){
|
res.get(i).put(column, "");
|
}
|
}
|
|
}
|
|
//补齐部门和职务数据
|
|
|
|
|
System.err.println("补齐数据");
|
System.err.println(colmuns);
|
System.err.println(res);
|
|
return res;
|
}
|
|
|
|
private int calcEvaScore(String a, String b, String c, String d) {
|
int i = calScore(a, 1);
|
int i1 = calScore(b, 1);
|
int i2 = calScore(c, 1);
|
int i3 = calScore(d, 2);
|
return i + i1 + i2 + i3;
|
|
}
|
|
private int calScore(String kp, int type) {
|
int score = 0;
|
switch (kp) {
|
case "A":
|
score = 19 * type;
|
break;
|
case "B":
|
score = 17 * type;
|
break;
|
case "C":
|
score = 15 * type;
|
break;
|
case "D":
|
score = 12 * type;
|
break;
|
case "E":
|
score = 5 * type;
|
break;
|
}
|
|
return score;
|
|
}
|
|
private void exportEvaData(List<Map<String, Object>> data, String templateFileName, String exportPath) {
|
File templateFile = new File(templateFileName);
|
try {
|
FileInputStream fileInputStream = new FileInputStream(templateFile);
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
//原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
|
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
|
//设置模板的第一个sheet的名称
|
// workbook.setSheetName(1, basisinfo.get("sheet").toString());
|
//写到流里
|
workbook.write(bos);
|
byte[] bArray = bos.toByteArray();
|
InputStream is = new ByteArrayInputStream(bArray);
|
//输出文件路径
|
ExcelWriter excelWriter = EasyExcel.write(templateFileName)
|
.withTemplate(is).build();
|
WriteSheet writeSheet = EasyExcel.writerSheet().build();
|
|
|
FillConfig horConfig = new FillConfig();
|
horConfig.setForceNewRow(true);
|
excelWriter.fill(new FillWrapper("data", data), horConfig, writeSheet);
|
|
// 关闭流
|
excelWriter.finish();
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
|
public List<Map<String, Object>> createTestData(List<Map<String, Object>> colmuns) {
|
List<Map<String, Object>> list = new ArrayList<>();
|
for (int i = 0; i < 10; i++) {
|
Map<String, Object> item = new HashMap<>();
|
for (int j = 0; j < colmuns.size(); j++) {
|
String column = colmuns.get(j).get("column").toString();
|
column = column.replace("{", "").replace("}", "").replace("data.", "");
|
item.put(column, "测试" + i + "-" + j);
|
|
}
|
item.put("no", i);
|
item.put("depart", "研发部门");
|
item.put("name", "张三" + i);
|
item.put("post", "软件工程师" + i);
|
list.add(item);
|
}
|
|
|
return list;
|
}
|
|
@Data
|
public class MergeBean {
|
private String col;
|
private String alias;
|
private Integer count;
|
private Integer sort;
|
}
|
|
|
class MyHandler extends AbstractMergeStrategy {
|
|
|
private Map<Integer, Integer> mergeSeason;
|
private Map<Integer, Integer> mergeMonth;
|
private List<Integer> mergeWeek;
|
|
public MyHandler(Map<Integer, Integer> mergeSeason, Map<Integer, Integer> mergeMonth, List<Integer> mergeWeek) {
|
this.mergeSeason = mergeSeason;
|
this.mergeMonth = mergeMonth;
|
this.mergeWeek = mergeWeek;
|
}
|
|
|
@Override
|
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
|
Integer seasonStartRow = 1;
|
AtomicReference<Integer> seasonStartCol = new AtomicReference<>(4);
|
AtomicReference<Integer> seasonEndCol = new AtomicReference<>(0);
|
mergeSeason.forEach((type, value) -> {
|
//1.合并四季的title
|
seasonEndCol.set(seasonStartCol.get() + (value - 1));
|
if (cell.getRowIndex() == seasonStartRow && cell.getColumnIndex() == seasonStartCol.get()) {
|
CellRangeAddress cellAddresses = new CellRangeAddress(seasonStartRow, seasonStartRow, seasonStartCol.get(), seasonEndCol.get());
|
sheet.addMergedRegionUnsafe(cellAddresses);
|
}
|
seasonStartCol.set(seasonStartCol.get() + (value - 1) + 1);
|
});
|
Integer monthStartRow = 2;
|
AtomicReference<Integer> monthStartCol = new AtomicReference<>(4);
|
AtomicReference<Integer> monthEndCol = new AtomicReference<>(0);
|
mergeMonth.forEach((type, value) -> {
|
//1.合并月的title
|
monthEndCol.set(monthStartCol.get() + (value - 1));
|
if (cell.getRowIndex() == monthStartRow && cell.getColumnIndex() == monthStartCol.get()) {
|
CellRangeAddress cellAddresses = new CellRangeAddress(monthStartRow, monthStartRow, monthStartCol.get(), monthEndCol.get());
|
sheet.addMergedRegionUnsafe(cellAddresses);
|
}
|
monthStartCol.set(monthStartCol.get() + (value - 1) + 1);
|
});
|
|
|
Integer weekStartRow = 3;
|
AtomicReference<Integer> weekStartCol = new AtomicReference<>(4);
|
AtomicReference<Integer> weekEndCol = new AtomicReference<>(0);
|
mergeWeek.forEach((value) -> {
|
//1.合并周的title
|
weekEndCol.set(weekStartCol.get() + (value - 1));
|
if (cell.getRowIndex() == weekStartRow && cell.getColumnIndex() == weekStartCol.get()) {
|
if (value == 1) {
|
CellRangeAddress cellAddresses = new CellRangeAddress(weekStartRow, weekStartRow + 1, weekStartCol.get(), weekEndCol.get());
|
sheet.addMergedRegionUnsafe(cellAddresses);
|
} else {
|
CellRangeAddress cellAddresses = new CellRangeAddress(weekStartRow, weekStartRow, weekStartCol.get(), weekEndCol.get());
|
sheet.addMergedRegionUnsafe(cellAddresses);
|
}
|
|
}
|
weekStartCol.set(weekStartCol.get() + (value - 1) + 1);
|
|
});
|
|
|
if (relativeRowIndex == null || relativeRowIndex == 0) {
|
return;
|
}
|
|
|
}
|
}
|
|
}
|
