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;
                }
            }
        }
    }

}