package org.jeecg.modules.weekly.controller;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.annotation.ExcelProperty;
|
import com.alibaba.excel.enums.WriteDirectionEnum;
|
import com.alibaba.excel.metadata.Head;
|
import com.alibaba.excel.util.ListUtils;
|
import com.alibaba.excel.write.handler.CellWriteHandler;
|
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 com.baomidou.mybatisplus.core.metadata.IPage;
|
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
|
import io.swagger.annotations.Api;
|
import io.swagger.annotations.ApiOperation;
|
import lombok.EqualsAndHashCode;
|
import lombok.Getter;
|
import lombok.Setter;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.ss.util.RegionUtil;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.apache.shiro.SecurityUtils;
|
import org.jeecg.common.api.vo.Result;
|
import org.jeecg.common.aspect.annotation.AutoLog;
|
import org.jeecg.common.system.base.controller.JeecgController;
|
import org.jeecg.common.system.query.QueryGenerator;
|
import org.jeecg.common.system.vo.LoginUser;
|
import org.jeecg.common.util.DateUtils;
|
import org.jeecg.modules.weekly.entity.WekEvaluate;
|
import org.jeecg.modules.weekly.entity.WekRecord;
|
import org.jeecg.modules.weekly.feign.SystemClient;
|
import org.jeecg.modules.weekly.feign.model.SysDepartTreeModel;
|
import org.jeecg.modules.weekly.feign.model.SysUser;
|
import org.jeecg.modules.weekly.service.IWekEvaluateService;
|
import org.jeecg.modules.weekly.vo.*;
|
import org.jeecg.modules.weekly.entity.ProProject;
|
import org.jeecg.modules.weekly.service.IWekRecordService;
|
import org.springframework.beans.BeanUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.web.bind.annotation.*;
|
import org.springframework.web.client.RestTemplate;
|
|
import javax.servlet.http.HttpServletRequest;
|
import java.io.*;
|
import java.time.LocalDate;
|
import java.time.LocalDateTime;
|
import java.util.*;
|
import java.util.concurrent.atomic.AtomicInteger;
|
import java.util.stream.Collectors;
|
|
@Slf4j
|
@Api(tags = "周报表")
|
@RestController
|
@RequestMapping("/wek/record")
|
public class WekRecordController extends JeecgController<WekRecord, IWekRecordService> {
|
@Autowired
|
IWekRecordService wekRecordService;
|
@Autowired
|
IWekEvaluateService wekEvaluateService;
|
@Autowired
|
SystemClient systemClient;
|
@Autowired
|
private RestTemplate template;
|
@Value(value = "${jeecg.path.upload}")
|
private String uploadpath;
|
|
@ApiOperation(value = "获取周报列表", notes = "获取所有周报列表")
|
@AutoLog(value = "获取周报列表")
|
@RequestMapping(value = "/list", method = RequestMethod.GET)
|
public Result<IPage<WekRecord>> queryPageList(WekRecord wekRecord,
|
@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
|
@RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
|
HttpServletRequest req) {
|
Result<IPage<WekRecord>> result = new Result<IPage<WekRecord>>();
|
QueryWrapper<WekRecord> queryWrapper = QueryGenerator.initQueryWrapper(wekRecord, req.getParameterMap());
|
Page<WekRecord> page = new Page<WekRecord>(pageNo, pageSize);
|
IPage<WekRecord> pageList = wekRecordService.page(page, queryWrapper);
|
result.setSuccess(true);
|
result.setResult(pageList);
|
return result;
|
}
|
|
@RequestMapping(value = "/listByYear", method = RequestMethod.GET)
|
public List<WekRecord> queryListByYear() {
|
QueryWrapper<WekRecord> queryWrapper = new QueryWrapper<>();
|
queryWrapper.lambda().in(WekRecord::getYear, DateUtils.getCurYear(), DateUtils.getCurYear() - 1, DateUtils.getCurYear() - 2);
|
List<WekRecord> list = wekRecordService.list(queryWrapper);
|
return list;
|
}
|
|
|
/**
|
* 查询个人本周和上周周报记录
|
*
|
* @param wekRecord
|
* @return
|
*/
|
@ApiOperation(value = "查询个人本周和上周周报信息", notes = "查询个人本周和上周周报信息")
|
@AutoLog(value = "查询个人本周和上周周报信息")
|
@RequestMapping(value = "/querySingleThisWeek", method = RequestMethod.GET)
|
public Result<List<WekWeekBean>> querySingleThisWeek(WekRecord wekRecord) {
|
Result<List<WekWeekBean>> result = new Result<List<WekWeekBean>>();
|
LoginUser loginUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
|
List<WekWeekBean> wekWeekBeans = wekRecordService.querySingleThisWeek(wekRecord);
|
//补充周报绩效评价信息
|
wekWeekBeans.forEach(item -> {
|
if (item.getYear() != null && item.getYear() > 0 && item.getWeek() != null && item.getWeek() > 0) {
|
String yearWeek = loginUser.getUsername() + (item.getYear() * 100 + item.getWeek());
|
WekEvaluate wekEvaluate = wekEvaluateService.getById(yearWeek);
|
if(wekEvaluate!=null) {
|
WekEvaluateBean wekEvaluateBean = new WekEvaluateBean();
|
BeanUtils.copyProperties(wekEvaluate,wekEvaluateBean);
|
item.setWekEvaluate(wekEvaluateBean);
|
}
|
}
|
if(item.getWekEvaluate()==null){
|
item.setWekEvaluate(new WekEvaluateBean());
|
}
|
});
|
result.setResult(wekWeekBeans);
|
result.success("查询成功");
|
return result;
|
}
|
|
|
/**
|
* 查询本人本年本周周报信息
|
*
|
* @param
|
* @return
|
*/
|
@ApiOperation(value = "按周查询个人周报", notes = "按周查询个人周报")
|
@AutoLog(value = "按周查询个人周报")
|
@RequestMapping(value = "/queryWeeklyListByWeekNo", method = RequestMethod.GET)
|
public Result<List<WekWeekBean>> queryWeeklyListByWeekNo(Integer year, Integer week, String username) {
|
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
|
//个人历史周报（我的周报）查询使用此接口，但不传username
|
//领导查看下级周报或查看部门人员周报使用此接口，传username
|
if(StringUtils.isEmpty(username)){
|
username = sysUser.getUsername();
|
}
|
Result<List<WekWeekBean>> result = new Result<List<WekWeekBean>>();
|
List<WekWeekBean> wekWeekBeans = wekRecordService.queryWeeklyListByWeekNo(year, week, username);
|
//补充周报绩效评价信息
|
String finalUsername = username;
|
wekWeekBeans.forEach(item -> {
|
if (item.getYear() != null && item.getYear() > 0 && item.getWeek() != null && item.getWeek() > 0) {
|
String yearWeek = finalUsername + (item.getYear() * 100 + item.getWeek());
|
WekEvaluate wekEvaluate = wekEvaluateService.getById(yearWeek);
|
if(wekEvaluate!=null) {
|
WekEvaluateBean wekEvaluateBean = new WekEvaluateBean();
|
BeanUtils.copyProperties(wekEvaluate,wekEvaluateBean);
|
item.setWekEvaluate(wekEvaluateBean);
|
}
|
|
}
|
if(item.getWekEvaluate()==null){
|
item.setWekEvaluate(new WekEvaluateBean());
|
}
|
});
|
result.setResult(wekWeekBeans);
|
result.success("查询成功");
|
|
|
return result;
|
}
|
|
|
/**
|
* 查询部门所有人本周和上周周报
|
*
|
* @param
|
* @return
|
*/
|
@ApiOperation(value = "查询部门成员本上周周报", notes = "查询部门成员本上周周报")
|
@AutoLog(value = "查询部门成员本上周周报")
|
@RequestMapping(value = "/queryDepartUserWeeklyList", method = RequestMethod.GET)
|
public Result<List<WekWeekBean>> queryDepartUserWeeklyList(String orgCode, int page) {
|
Result<List<WekWeekBean>> result = new Result<List<WekWeekBean>>();
|
List<WekWeekBean> wekWeekBeans = wekRecordService.queryDepartUserWeeklyList(orgCode, page);
|
//补充周报绩效评价信息
|
wekWeekBeans.forEach(item -> {
|
if (item.getUsername()!=null && item.getYear() != null && item.getYear() > 0 && item.getWeek() != null && item.getWeek() > 0) {
|
String yearWeek = item.getUsername() + (item.getYear() * 100 + item.getWeek());
|
WekEvaluate wekEvaluate = wekEvaluateService.getById(yearWeek);
|
if(wekEvaluate!=null) {
|
WekEvaluateBean wekEvaluateBean = new WekEvaluateBean();
|
BeanUtils.copyProperties(wekEvaluate,wekEvaluateBean);
|
item.setWekEvaluate(wekEvaluateBean);
|
}
|
|
}
|
if(item.getWekEvaluate()==null){
|
item.setWekEvaluate(new WekEvaluateBean());
|
}
|
});
|
result.setResult(wekWeekBeans);
|
result.success("查询成功");
|
|
return result;
|
}
|
|
|
@ApiOperation(value = "下级周报绩效评价", notes = "下级周报绩效评价")
|
@AutoLog(value = "下级周报绩效评价")
|
@RequestMapping(value = "/queryJuniorBatchWeekly", method = RequestMethod.GET)
|
public Result<List<WekWeekBean>> queryJuniorBatchWeekly(String usernames, int page) {
|
Result<List<WekWeekBean>> result = new Result<List<WekWeekBean>>();
|
List<WekWeekBean> wekWeekBeans = wekRecordService.queryJuniorBatchWeekly(usernames, page);
|
//补充周报绩效评价信息
|
wekWeekBeans.forEach(item -> {
|
if (item.getUsername()!=null && item.getYear() != null && item.getYear() > 0 && item.getWeek() != null && item.getWeek() > 0) {
|
String yearWeek = item.getUsername() + (item.getYear() * 100 + item.getWeek());
|
WekEvaluate wekEvaluate = wekEvaluateService.getById(yearWeek);
|
if(wekEvaluate!=null) {
|
WekEvaluateBean wekEvaluateBean = new WekEvaluateBean();
|
BeanUtils.copyProperties(wekEvaluate,wekEvaluateBean);
|
item.setWekEvaluate(wekEvaluateBean);
|
}
|
|
}
|
if(item.getWekEvaluate()==null){
|
item.setWekEvaluate(new WekEvaluateBean());
|
}
|
});
|
result.setResult(wekWeekBeans);
|
result.success("查询成功");
|
|
return result;
|
}
|
|
@ApiOperation(value = "根据项目查询周报", notes = "根据项目查询周报")
|
@RequestMapping(value = "/queryProjectWeekly", method = RequestMethod.GET)
|
public Result<List<WekProject>> queryProjectWeekly(String xm, int page) {
|
Result<List<WekProject>> result = new Result<>();
|
List<WekProject> wekProjects = wekRecordService.queryProjectWeekly(xm, page);
|
result.setResult(wekProjects);
|
return result;
|
}
|
|
@ApiOperation(value = "根据所有项目查询周报", notes = "根据所有项目查询周报")
|
@RequestMapping(value = "/queryAllProjectWeekly", method = RequestMethod.GET)
|
public Result<List<WekProject>> queryAllProjectWeekly(int page) {
|
Result<List<WekProject>> result = new Result<>();
|
List<WekProject> wekProjects = wekRecordService.queryAllProjectWeekly(page);
|
result.setResult(wekProjects);
|
return result;
|
}
|
|
|
/**
|
* 查询项目列表
|
*
|
* @param project
|
* @return
|
*/
|
@ApiOperation(value = "查询项目列表tree", notes = "查询项目列表tree")
|
@RequestMapping(value = "/projectTreeList", method = RequestMethod.GET)
|
public Result<List<ProjectTreeVo>> projectTreeList(ProProject project) {
|
Result<List<ProjectTreeVo>> result = new Result<>();
|
List<ProjectTreeVo> proProjects = wekRecordService.projectTreeList(project);
|
result.success("查询成功");
|
result.setResult(proProjects);
|
return result;
|
}
|
|
|
@RequestMapping(value = "/addBatch", method = RequestMethod.POST)
|
@AutoLog(value = "批量添加周报")
|
public Result<WekResultBean> addBatch(@RequestBody WekResultBean resultBean, HttpServletRequest request) {
|
Result<WekResultBean> result = new Result<WekResultBean>();
|
LoginUser loginUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
|
String orgCode = loginUser.getOrgCode();
|
if (StringUtils.isEmpty(orgCode)) {
|
return result.error500("请先设置用户部门信息，并重新登录!");
|
}
|
try {
|
|
|
List<WekWeekBean> weekList = resultBean.getWeekList();
|
for (int i = 0; i < weekList.size(); i++) {
|
List<WekTypeBean> typeList = weekList.get(i).getTypeList();
|
for (int j = 0; j < typeList.size(); j++) {
|
List<WekRecord> wekList = typeList.get(j).getWekList();
|
for (int k = 0; k < wekList.size(); k++) {
|
wekList.get(k).setSysOrgCode(orgCode);
|
|
}
|
|
}
|
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
wekRecordService.insertBatch(resultBean);
|
result.success("添加成功！");
|
result.setResult(resultBean);
|
return result;
|
}
|
|
|
@RequestMapping(value = "/add", method = RequestMethod.POST)
|
@AutoLog(value = "添加")
|
public Result<?> add(@RequestBody WekRecord record, HttpServletRequest request) {
|
LoginUser loginUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
|
String orgCode = loginUser.getOrgCode();
|
if (StringUtils.isEmpty(orgCode)) {
|
return Result.error("请先设置用户部门信息，并重新登录!");
|
} else {
|
record.setSysOrgCode(orgCode);
|
}
|
record.setUser(loginUser.getUsername());
|
record.setYearWeek(record.getYear() * 100 + record.getWeek());
|
wekRecordService.saveOrUpdate(record);
|
wekRecordService.calcWbsProgress(record);
|
return Result.OK("添加成功!");
|
}
|
|
@AutoLog(value = "删除一行周报记录")
|
@RequestMapping(value = "/delete", method = RequestMethod.DELETE)
|
public Result<?> delete(@RequestParam(name = "id", required = true) String id) {
|
wekRecordService.removeById(id);
|
return Result.ok("删除记录成功");
|
}
|
|
|
/******************************统计**********************************/
|
|
@ApiOperation(value = "周报提交概览", notes = "周报提交概览")
|
@RequestMapping(value = "/submitReview", method = RequestMethod.GET)
|
public Result<List<SysDepartTreeModel>> submintReview(Integer year, Integer week, HttpServletRequest req) {
|
Result<List<SysDepartTreeModel>> result = new Result<>();
|
QueryWrapper<WekRecord> queryWrapper = new QueryWrapper<>();
|
queryWrapper.lambda().eq(WekRecord::getYear, year);
|
queryWrapper.lambda().eq(WekRecord::getWeek, week);
|
List<WekRecord> wekList = wekRecordService.list(queryWrapper);
|
List<String> users = wekList.stream().map(WekRecord::getUser).collect(Collectors.toList());
|
//去重
|
LinkedHashSet<String> userNameList = new LinkedHashSet<>(users);
|
//部门tree
|
List<SysDepartTreeModel> departTreeList = systemClient.queryTreeList(null).getResult();
|
if (departTreeList == null) {
|
return result.error500("获取部门数据失败");
|
}
|
fitUserList(departTreeList, userNameList);
|
result.setResult(departTreeList);
|
|
return result;
|
}
|
|
//1.统计部门人员；2.判断人员周报是否提交
|
private void fitUserList(List<SysDepartTreeModel> departTreeList, LinkedHashSet<String> userNameList) {
|
departTreeList.forEach(item -> {
|
Result<List<SysUser>> result = systemClient.queryUserByDepId(item.getId(), "");
|
if (result.getResult() != null) {
|
List<SysUser> userList = result.getResult();
|
userList = userList.stream().filter(user->user.getService()==null || user.getService()!=2).collect(Collectors.toList());
|
userList.forEach(user -> {
|
user.setWSubmit(userNameList.contains(user.getUsername()));
|
});
|
item.setUserList(userList);
|
}
|
if (!item.getIsLeaf() && item.getChildren() != null) {
|
fitUserList(item.getChildren(), userNameList);
|
}
|
|
});
|
}
|
|
|
@ApiOperation(value = "统计周报周、月、季度工时", notes = "统计周报周、月、季度工时")
|
@RequestMapping(value = "/statisticsList", method = RequestMethod.GET)
|
public Result<List<Map<String, Object>>> statisticsList(WekRecord wekRecord, HttpServletRequest req) {
|
Result<List<Map<String, Object>>> result = new Result<>();
|
|
Result<List<Map<String, Object>>> listResult = wekRecordService.statisticsList(wekRecord);
|
if (listResult != null) {
|
result = listResult;
|
result.setSuccess(true);
|
}
|
return result;
|
}
|
|
@ApiOperation(value = "统计周报年度工时", notes = "统计周报年度工时")
|
@RequestMapping(value = "/statisticsYearList", method = RequestMethod.GET)
|
public Result<List<Map<String, Object>>> statisticsYearList(WekRecord wekRecord, HttpServletRequest req) {
|
Result<List<Map<String, Object>>> result = new Result<>();
|
|
Result<List<Map<String, Object>>> listResult = wekRecordService.statisticsYearList(wekRecord);
|
if (listResult != null) {
|
result = listResult;
|
result.setSuccess(true);
|
}
|
return result;
|
}
|
|
|
/****************************导出周报***************************/
|
|
@GetMapping(value = "/exportAweekly")
|
public Result<Map<String, Object>> exportAweekly(WekRecord wekRecord) {
|
Result<Map<String, Object>> result = new Result<>();
|
Map<String, Object> data = new HashMap<>();
|
if (wekRecord.getYear() == null || wekRecord.getWeek() == null || wekRecord.getUser() == null) {
|
return result.error500("未查询到数据!");
|
}
|
Map<String, Object> map = wekRecordService.exportAweekly(wekRecord);
|
Map<String, Object> basisinfo = wekRecordService.queryUserInfoByName(wekRecord.getUser());
|
String start = DateUtils.weekToDayStartStr(wekRecord.getYear(), wekRecord.getWeek(), "yyyy-MM-dd");
|
String end = DateUtils.weekToFriDayEndStr(wekRecord.getYear(), wekRecord.getWeek(), "yyyy-MM-dd");
|
String sheet = DateUtils.getWeekInMonth(end,2);
|
if (basisinfo != null) {
|
basisinfo.put("start", start);
|
basisinfo.put("end", end);
|
basisinfo.put("sheet", sheet);
|
map.put("basisinfo", basisinfo);
|
}
|
if (map == null || map.isEmpty()) {
|
return result.error500("未查询到数据!");
|
}
|
String filePath = exportAweekExcel(map);
|
int size = map.containsKey("size") ? (int) map.get("size") : 0;
|
data.put("size", size);
|
if (filePath == null || size == 0) {
|
return result.error500("未查询到数据,导出失败");
|
}
|
data.put("filePath", filePath);
|
result.setResult(data);
|
return result;
|
|
}
|
|
|
@GetMapping(value = "/exportWeekly")
|
public Result<Map<String, Object>> customExportWeekly(WekRecord wekRecord) {
|
System.err.println(wekRecord.getDetailFlag());
|
Result<Map<String, Object>> result = new Result<>();
|
Map<String, Object> data = new HashMap<>();
|
System.err.println("开始："+ LocalDateTime.now());
|
Map<String, Object> map = wekRecordService.customExportWeekly(wekRecord);
|
System.err.println("组装数据完成："+LocalDateTime.now());
|
String filePath = exportToExcel(map,wekRecord);
|
System.err.println("导出文件完成："+LocalDateTime.now());
|
data.put("filePath", filePath);
|
int size = 0;
|
if (map.containsKey("data")) {
|
List<Map<String, Object>> list = (List<Map<String, Object>>) map.get("data");
|
size = list.size();
|
}
|
data.put("size", size);
|
if (filePath == null || size == 0) {
|
return result.error500("未查询到数据,导出失败");
|
}
|
result.setResult(data);
|
return result.success("导出成功!");
|
}
|
|
/**
|
* @param res excel数据
|
* @return excel文件路径
|
*/
|
private String exportToExcel(Map<String, Object> res,WekRecord wekRecord) {
|
List<Map<String, Object>> userList = (List<Map<String, Object>>) res.get("users");
|
List<Map<String, Object>> data = (List<Map<String, Object>>) res.get("data");
|
|
String templateFileName = uploadpath + File.separator + "exportTemplate" + File.separator + "summaryWeekly.xlsx";
|
//只导出汇总表
|
if(wekRecord.getDetailFlag()==0){
|
templateFileName = uploadpath + File.separator + "exportTemplate" + File.separator + "weeklyHz.xlsx";
|
}
|
String filePath = "export" + File.separator + "周报汇总-" + DateUtils.date2Str(new Date(), DateUtils.yyyymmddhhmmss.get()) + ".xlsx";
|
|
String fileName = uploadpath + File.separator + filePath;
|
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(0, "汇总表");
|
|
|
//写到流里
|
workbook.write(bos);
|
byte[] bArray = bos.toByteArray();
|
InputStream is = new ByteArrayInputStream(bArray);
|
//输出文件路径
|
ExcelWriter excelWriter = EasyExcel.write(fileName)
|
// .registerWriteHandler(new MyHandler(5,data().size() +4,0,0))
|
.withTemplate(is).build();
|
|
|
// 写入list之前的数据
|
Map<String, Object> map = new HashMap<String, Object>();
|
map.put("no", "{pro.xmbh}");
|
map.put("name", "{pro.xmmc}");
|
map.put("title", res.get("title"));
|
|
// FillConfig fillConfig = new FillConfig();
|
// fillConfig.setForceNewRow(true);
|
|
System.err.println("填充汇总表-开始："+LocalDateTime.now());
|
|
WriteSheet writeSheet = EasyExcel.writerSheet("汇总表").build();
|
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
|
fillConfig.setForceNewRow(true);
|
excelWriter.fill(map, writeSheet);
|
excelWriter.fill(new FillWrapper("users", userList), fillConfig, writeSheet);
|
|
FillConfig fillConfig2 = new FillConfig();
|
fillConfig2.setForceNewRow(true);
|
excelWriter.fill(new FillWrapper("pro", data), fillConfig2, writeSheet);
|
|
// 关闭流
|
excelWriter.finish();
|
System.err.println("填充汇总表-结束："+LocalDateTime.now());
|
|
List<Map<String, Object>> usersweeklys = (List<Map<String, Object>>) res.get("usersweeklys");
|
//只填充汇总表
|
if(usersweeklys==null || usersweeklys.size()<1){
|
return filePath;
|
}
|
|
String exportPath = exportUsersExcel(res, fileName, filePath);
|
|
return exportPath;
|
|
|
} catch (FileNotFoundException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
return null;
|
|
|
}
|
|
private String exportUsersExcel(Map<String, Object> res, String templateFileName, String exportPath) {
|
List<Map<String, Object>> usersweeklys = (List<Map<String, Object>>) res.get("usersweeklys");
|
|
|
/* List<Map<String, Object>> data1 = new ArrayList<>();
|
if(res.containsKey("data1")){
|
data1 = (List<Map<String, Object>>) res.get("data1");
|
}
|
List<Map<String, Object>> data2 = new ArrayList<>();
|
if(res.containsKey("data2")){
|
data2 = (List<Map<String, Object>>) res.get("data2");
|
}
|
List<Map<String, Object>> data3 = new ArrayList<>();
|
if(res.containsKey("data3")){
|
data3 = (List<Map<String, Object>>) res.get("data3");
|
}
|
List<Map<String, Object>> data4 = new ArrayList<>();
|
if(res.containsKey("data4")){
|
data4 = (List<Map<String, Object>>) res.get("data4");
|
}*/
|
|
/* Map<String, Object> basisinfo = new HashMap<>();
|
if(res.containsKey("basisinfo")){
|
basisinfo = (Map<String, Object>) res.get("basisinfo");
|
}*/
|
|
/* Map<String, Object> zgs = new HashMap<>();
|
if(res.containsKey("zgs")){
|
zgs = (Map<String, Object>) res.get("zgs");
|
}*/
|
|
|
String roopath = System.getProperty("user.dir");
|
String filePath = "export" + File.separator + "weekly2-" + System.currentTimeMillis() + ".xlsx";
|
|
String fileName = uploadpath + File.separator + filePath;
|
File templateFile = new File(templateFileName);
|
try {
|
System.err.println("创建子模板-开始："+LocalDateTime.now());
|
FileInputStream fileInputStream = new FileInputStream(templateFile);
|
ByteArrayOutputStream bos = new ByteArrayOutputStream();
|
|
//原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
|
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
|
//设置模板的第一个sheet的名称
|
Map<String, Object> basisinfo = (Map<String, Object>) usersweeklys.get(0).get("basisinfo");
|
workbook.setSheetName(1, basisinfo.get("sheet").toString());
|
if (usersweeklys != null && usersweeklys.size() > 0) {
|
for (int i = 0; i < usersweeklys.size(); i++) {
|
if (i == 0) continue;
|
Map<String, Object> aweek = usersweeklys.get(i);
|
Map<String, Object> basis = (Map<String, Object>) aweek.get("basisinfo");
|
workbook.cloneSheet(1, basis.get("sheet").toString());
|
}
|
|
}
|
System.err.println("创建子模板-结束："+LocalDateTime.now());
|
//写到流里
|
workbook.write(bos);
|
byte[] bArray = bos.toByteArray();
|
InputStream is = new ByteArrayInputStream(bArray);
|
//输出文件路径
|
ExcelWriter excelWriter = EasyExcel.write(templateFileName)
|
//.registerWriteHandler(new MyHandler(params, 5, 4, 0, 0))
|
.withTemplate(is).build();
|
|
System.err.println("填充子模板"+"-开始："+LocalDateTime.now());
|
if (usersweeklys != null && usersweeklys.size() > 0) {
|
for (int i = 0; i < usersweeklys.size(); i++) {
|
Map<String, Object> aweek = usersweeklys.get(i);
|
Map<String, Object> basis = new HashMap<>();
|
if (aweek.containsKey("basisinfo")) {
|
basis = (Map<String, Object>) aweek.get("basisinfo");
|
}
|
|
Map<String, Object> zgs = new HashMap<>();
|
if (aweek.containsKey("zgs")) {
|
zgs = (Map<String, Object>) aweek.get("zgs");
|
}
|
|
|
List<Map<String, Object>> data1 = new ArrayList<>();
|
if (aweek.containsKey("data1")) {
|
data1 = (List<Map<String, Object>>) aweek.get("data1");
|
}
|
List<Map<String, Object>> data2 = new ArrayList<>();
|
if (aweek.containsKey("data2")) {
|
data2 = (List<Map<String, Object>>) aweek.get("data2");
|
}
|
List<Map<String, Object>> data3 = new ArrayList<>();
|
if (aweek.containsKey("data3")) {
|
data3 = (List<Map<String, Object>>) aweek.get("data3");
|
}
|
List<Map<String, Object>> data4 = new ArrayList<>();
|
if (aweek.containsKey("data4")) {
|
data4 = (List<Map<String, Object>>) aweek.get("data4");
|
}
|
|
Map<String, Object> eva = new HashMap<>();
|
if (aweek.containsKey("eva")) {
|
eva = (Map<String, Object>) aweek.get("eva");
|
}
|
|
TreeMap<String, Integer> params = new TreeMap<>();
|
params.put("data1", data1.size());
|
params.put("data2", data2.size());
|
params.put("data3", data3.size());
|
params.put("data4", data4.size());
|
|
WriteSheet writeSheet = EasyExcel.writerSheet(basis.get("sheet").toString())
|
.registerWriteHandler(new MyHandler(params, 5, 4, 0, 0))
|
.build();
|
|
excelWriter.fill(basis, writeSheet);
|
excelWriter.fill(zgs, writeSheet);
|
excelWriter.fill(eva, writeSheet);
|
FillConfig fillConfig = new FillConfig();
|
fillConfig.setForceNewRow(true);
|
excelWriter.fill(new FillWrapper("data1", data1), fillConfig, writeSheet);
|
excelWriter.fill(new FillWrapper("data2", data2), fillConfig, writeSheet);
|
excelWriter.fill(new FillWrapper("data3", data3), fillConfig, writeSheet);
|
excelWriter.fill(new FillWrapper("data4", data4), fillConfig, writeSheet);
|
|
|
}
|
|
}
|
|
|
// 关闭流
|
excelWriter.finish();
|
System.err.println("填充子模板"+"-结束："+LocalDateTime.now());
|
return exportPath;
|
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
return exportPath;
|
}
|
|
|
}
|
|
private String exportAweekExcel(Map<String, Object> res) {
|
List<Map<String, Object>> data1 = new ArrayList<>();
|
if (res.containsKey("data1")) {
|
data1 = (List<Map<String, Object>>) res.get("data1");
|
}
|
List<Map<String, Object>> data2 = new ArrayList<>();
|
if (res.containsKey("data2")) {
|
data2 = (List<Map<String, Object>>) res.get("data2");
|
}
|
List<Map<String, Object>> data3 = new ArrayList<>();
|
if (res.containsKey("data3")) {
|
data3 = (List<Map<String, Object>>) res.get("data3");
|
}
|
List<Map<String, Object>> data4 = new ArrayList<>();
|
if (res.containsKey("data4")) {
|
data4 = (List<Map<String, Object>>) res.get("data4");
|
}
|
|
Map<String, Object> basisinfo = new HashMap<>();
|
if (res.containsKey("basisinfo")) {
|
basisinfo = (Map<String, Object>) res.get("basisinfo");
|
}
|
|
Map<String, Object> zgs = new HashMap<>();
|
if (res.containsKey("zgs")) {
|
zgs = (Map<String, Object>) res.get("zgs");
|
}
|
Map<String, Object> eva = new HashMap<>();
|
if (res.containsKey("eva")) {
|
eva = (Map<String, Object>) res.get("eva");
|
}
|
|
|
TreeMap<String, Integer> params = new TreeMap<>();
|
params.put("data1", data1.size());
|
params.put("data2", data2.size());
|
params.put("data3", data3.size());
|
params.put("data4", data4.size());
|
|
String templateFileName = uploadpath + File.separator + "exportTemplate" + File.separator + "aweekly.xlsx";
|
String filePath = "export" + File.separator + basisinfo.get("sheet").toString() + "-" + basisinfo.get("realname") + ".xlsx";
|
|
String fileName = uploadpath + File.separator + filePath;
|
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(params, 5, 4, 0, 0))
|
.withTemplate(is).build();
|
|
|
WriteSheet writeSheet = EasyExcel.writerSheet(basisinfo.get("sheet").toString()).build();
|
|
|
excelWriter.fill(basisinfo, writeSheet);
|
excelWriter.fill(zgs, writeSheet);
|
excelWriter.fill(eva, writeSheet);
|
|
FillConfig fillConfig = new FillConfig();
|
fillConfig.setForceNewRow(true);
|
excelWriter.fill(new FillWrapper("data1", data1), fillConfig, writeSheet);
|
excelWriter.fill(new FillWrapper("data2", data2), fillConfig, writeSheet);
|
excelWriter.fill(new FillWrapper("data3", data3), fillConfig, writeSheet);
|
excelWriter.fill(new FillWrapper("data4", data4), fillConfig, writeSheet);
|
|
// 关闭流
|
excelWriter.finish();
|
|
return filePath;
|
|
|
} catch (FileNotFoundException e) {
|
e.printStackTrace();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
return null;
|
|
|
}
|
|
|
private List<Map<String, Object>> createDemoData() {
|
List<Map<String, Object>> list = new ArrayList<>();
|
for (int i = 0; i < 10; i++) {
|
Map<String, Object> map = new HashMap<>();
|
map.put("no", "12121" + i);
|
map.put("name", "测试项目" + i);
|
map.put("zhuguifei", 20 + i);
|
map.put("baoshiwei", 20 + i);
|
map.put("admin", 20 + i);
|
map.put("zhaofeifei", 20 + i);
|
|
list.add(map);
|
}
|
|
|
return list;
|
}
|
|
|
@Getter
|
@Setter
|
@EqualsAndHashCode
|
public class DownloadData {
|
@ExcelProperty("字符串标题")
|
private String string;
|
@ExcelProperty("日期标题")
|
private Date date;
|
@ExcelProperty("数字标题")
|
private Double doubleData;
|
}
|
|
|
private List<DownloadData> cdata() {
|
List<DownloadData> list = ListUtils.newArrayList();
|
for (int i = 0; i < 10; i++) {
|
DownloadData data = new DownloadData();
|
data.setString("字符串" + i);
|
data.setDate(new Date());
|
data.setDoubleData(0.56);
|
list.add(data);
|
}
|
return list;
|
}
|
|
|
|
class MyHandler extends AbstractMergeStrategy {
|
//合并坐标
|
/**
|
* 合并的开始行
|
*/
|
private int firstRow;
|
/**
|
* 合并的结束行
|
*/
|
private int lastRow;
|
/**
|
* 合并的开始列
|
*/
|
private int firstColumn;
|
/**
|
* 合并的结束列
|
*/
|
private int lastColumn;
|
private Map<String, Integer> params;
|
|
public MyHandler(Map<String, Integer> params, int firstRow, int lastRow, int firstColumn, int lastColumn) {
|
this.firstRow = firstRow;
|
this.lastRow = lastRow;
|
this.firstColumn = firstColumn;
|
this.lastColumn = lastColumn;
|
this.params = params;
|
|
}
|
|
|
@Override
|
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
|
AtomicInteger startRow = new AtomicInteger(firstRow);
|
AtomicInteger endRow = new AtomicInteger(lastRow);
|
Sheet finalSheet = sheet;
|
params.forEach((k, v) -> {
|
endRow.addAndGet(v);
|
if (v > 1) {
|
//System.err.println("数组："+k+"===大小："+v);
|
|
if (cell.getRowIndex() == startRow.get() && cell.getColumnIndex() == this.firstColumn) {
|
CellRangeAddress cellAddresses = new CellRangeAddress(startRow.get(), endRow.get(), this.firstColumn, this.lastColumn);
|
finalSheet.addMergedRegionUnsafe(cellAddresses);
|
}
|
// System.err.println(startRow.get());
|
// System.err.println(endRow.get());
|
|
}
|
endRow.addAndGet(2);
|
startRow.set(endRow.get());
|
startRow.addAndGet(1);
|
});
|
/* Integer data1 = params.get("data1");
|
Integer data2 = params.get("data2");
|
Integer data3 = params.get("data3");
|
Integer data4 = params.get("data4");
|
int megEndRow = lastRow + data1;
|
int megStartRow = firstRow;
|
//列数大于1才合并
|
if (data1 > 1) {
|
*//*这个 if判断很重要，否则每个单元格都会合并一次*//*
|
if (cell.getRowIndex() == this.firstRow && cell.getColumnIndex() == this.firstColumn) {
|
CellRangeAddress cellAddresses = new CellRangeAddress(this.firstRow, megEndRow, this.firstColumn, this.lastColumn);
|
sheet.addMergedRegionUnsafe(cellAddresses);
|
}
|
}
|
|
//合并相同列 TODO 动态计算列
|
if (data2 > 1) {
|
megEndRow += data2;
|
if (cell.getRowIndex() == megEndRow && cell.getColumnIndex() == this.firstColumn) {
|
CellRangeAddress cellAddresses = new CellRangeAddress(megEndRow, megEndRow, this.firstColumn, this.lastColumn);
|
sheet.addMergedRegionUnsafe(cellAddresses);
|
}
|
}*/
|
|
if (relativeRowIndex == null || relativeRowIndex == 0) {
|
return;
|
}
|
//System.err.println("cell:"+cell);
|
int rowIndex = cell.getRowIndex();
|
// System.err.println("rowIndex:"+rowIndex);
|
int colIndex = cell.getColumnIndex();
|
// System.err.println("colIndex:"+colIndex);
|
sheet = cell.getSheet();
|
//System.err.println("sheet:"+sheet);
|
Row preRow = sheet.getRow(rowIndex - 1);
|
|
Cell preCell = preRow.getCell(colIndex);//获取上一行的该格
|
|
|
//设置样式
|
List<CellRangeAddress> list = sheet.getMergedRegions();
|
CellStyle cs = cell.getCellStyle();
|
cell.setCellStyle(cs);
|
for (int i = 0; i < list.size(); i++) {
|
CellRangeAddress cellRangeAddress = list.get(i);
|
if (cellRangeAddress.containsRow(preCell.getRowIndex()) && cellRangeAddress.containsColumn(preCell.getColumnIndex()) && colIndex != 0) {
|
int lastColIndex = cellRangeAddress.getLastColumn();
|
int firstColIndex = cellRangeAddress.getFirstColumn();
|
CellRangeAddress cra = new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), firstColIndex, lastColIndex);
|
sheet.addMergedRegion(cra);
|
RegionUtil.setBorderBottom(BorderStyle.THIN, cra, sheet);
|
RegionUtil.setBorderLeft(BorderStyle.THIN, cra, sheet);
|
RegionUtil.setBorderRight(BorderStyle.THIN, cra, sheet);
|
RegionUtil.setBorderTop(BorderStyle.THIN, cra, sheet);
|
return;
|
}
|
}
|
}
|
}
|
|
}
|
