package cn.com.countrygarden.bi.main.service.extension;

import cn.com.countrygarden.bi.main.dao.extension.ExtensionDupBindingMapper;
import cn.com.countrygarden.bi.main.dao.extension.ExtensionErpBindingMapper;
import cn.com.countrygarden.bi.main.dao.extension.ExtensionReportMapper;
import cn.com.countrygarden.bi.main.dto.extension.*;
import cn.com.countrygarden.bi.main.service.AccessTokenService;
import cn.com.countrygarden.bi.main.util.ExtensionReportUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @ClassName ExtensionExportService
 * @Description 外拓绑定 --> 导出
 * @Author Sjiang
 * @Date 2019/1/23 16:40
 * @Version 1.0
 **/
@Service
public class ExtensionExportService {

    @Autowired
    private ExtensionErpBindingMapper erpMapper;
    @Autowired
    private ExtensionDupBindingMapper dupMapper;
    @Autowired
    private ExtensionReportMapper reportMapper;
    @Autowired
    private ExtensionReportService reportService;
    @Resource
    @Qualifier("taskExecutor")
    private ThreadPoolTaskExecutor poolTaskExecutor;
    BigDecimal bigDecimal = new BigDecimal(0);

    /**
     * @return void
     * @Author Sjiang
     * @Description 导出 签约已上会
     * @Date 20:24 2019/1/23
     * @Param [dto, response]
     **/
    public void meetDupExport(ExtensionDupDTO dto, AccessTokenService accessTokenService, HttpServletResponse response, HttpServletRequest request) {
        String userCode = ExtensionReportUtils.getUserCode(accessTokenService, request);
        // 获取 organCode ，如果为空则是总部权限
        String organCode = reportService.isExtensionAdmin(userCode);
        String title = "签约已上会DUP";
        // 表头
        String[] cellTitles = {"编号", "拓展区域", "项目名称", "接管区域", "项目性质", "新/二手", "合作模式", "签约日期"};
        List<ExtensionDupDTO> list = dupMapper.selectExportByDto(dto, organCode);
        List<ArrayList<Object>> data = new ArrayList<>(list.size());
        if (!list.isEmpty()) {
            list.forEach(d -> {
                ArrayList<Object> rowData = new ArrayList<>();
                // 编号
                rowData.add(d.getId());
                // 拓展区域
                rowData.add(d.getOrganName());
                // 项目名称
                rowData.add(d.getProjectName());
                // 接管区域
                rowData.add(d.getProposalOrganName());
                // 项目性质
                rowData.add(d.getProjectNature());
                // 新 / 二手
                if (d.getIsNew().intValue() == 1) {
                    rowData.add("新");
                } else if (d.getIsNew().intValue() == 2){
                    rowData.add("二手");
                }else {
                    rowData.add("");
                }
                // 合作模式
                rowData.add(d.getCorpType());
                // 签约日期
                rowData.add(d.getSignTime());
                data.add(rowData);
            });
        }
        export(title, cellTitles, "sheet1", data, response, request);


    }

    /**
     * @return void
     * @Author Sjiang
     * @Description 导出 已录入ERP
     * @Date 20:24 2019/1/23
     * @Param [dto, response]
     **/
    public void erpRecordedExport(ExtensionErpDTO dto, AccessTokenService accessTokenService, HttpServletResponse response, HttpServletRequest request) {
        String userCode = ExtensionReportUtils.getUserCode(accessTokenService, request);
        // 获取 organCode ，如果为空则是总部权限
        String organCode = reportService.isExtensionAdmin(userCode);
        String title = "已录入ERP";
        // 表头
        String[] cellTitles = {"物业区域", "物业区域编号", "物业项目", "项目性质", "物业项目编号", "项目类型", "省级行政区"};
        List<ExtensionErpDTO> list = erpMapper.selectExportByDto(dto, organCode);
        List<ArrayList<Object>> data = new ArrayList<>(list.size());
        if (!list.isEmpty()) {
            list.forEach(d -> {
                ArrayList<Object> rowData = new ArrayList<>();
                // 物业区域
                rowData.add(d.getPropertyArea());
                // 物业区域编号
                rowData.add(d.getAreaNumber());
                // 物业项目
                rowData.add(d.getPropertyProject());
                // 项目性质
                rowData.add(d.getCommKind());
                // 物业项目编号
                rowData.add(d.getProjectNumber());
                // 项目类型
                rowData.add(d.getProjectType());
                // 省级行政区
                rowData.add(d.getProvince());
                data.add(rowData);
            });
        }
        export(title, cellTitles, "sheet1", data, response, request);
    }


    /**
     * @return void
     * @Author Sjiang
     * @Description 封装工具
     * @Date 20:51 2019/1/23
     * @Param [title, cellTitles, sheetName, data, response]
     **/

    private void export(String title, String[] cellTitles, String sheetName, List<ArrayList<Object>> data, HttpServletResponse response, HttpServletRequest request) {
        // 创建空的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置宽度
        for (int i = 0; i < cellTitles.length; i++) {
            sheet.setColumnWidth(i, 16 * 330);
        }
        if (StringUtils.isNotBlank(title)) {
            // 合并单元格
            if (cellTitles.length == 1) {
                // 防止 出现 -1
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellTitles.length));
            } else {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellTitles.length - 1));
            }

            /*  第一行标题处理 */
            XSSFRow row01 = sheet.createRow(0);
            XSSFCell cell01 = row01.createCell(0);
            // 设置单元格内容
            cell01.setCellValue(title);
            // 通过工作簿创建样式
            XSSFCellStyle cellStyle = createCellStyle(workbook, "Courier New", (short) 20, true, true);

            // 设置单元格样式
            cell01.setCellStyle(cellStyle);
            /*  第一行标题处理  END */

            /*  第二行标题处理 */

            // 通过工作簿创建样式
            XSSFCellStyle cellStyle2 = createCellStyle(workbook, "Courier New", (short) 15, true, true);
            XSSFRow row02 = sheet.createRow(StringUtils.isNotBlank(title) ? 1 : 0);
            // for 循环创建单元格并赋值和设置样式
            for (int i = 0; i < cellTitles.length; i++) {
                XSSFCell titleCell = row02.createCell(i);
                titleCell.setCellValue(cellTitles[i]);
                titleCell.setCellStyle(cellStyle2);
            }
            // 冻结窗格
            sheet.createFreezePane(0, 2, 0, 1);
            /*  第二行标题处理  END */

            /** 中间行的处理  */
            // 创建单元格样式
            XSSFCellStyle cellStyle03 = createCellStyle(workbook, "微软雅黑", (short) 12, false, true);
            int temp = StringUtils.isNotBlank(title) ? 2 : 1;
            // for循环处理
            for (int i = 0; i < data.size(); i++) {
                XSSFRow row = sheet.createRow(i + temp);
                for (int j = 0; j < data.get(i).size(); j++) {
                    XSSFCell cell = row.createCell(j);
                    Object value = data.get(i).get(j);
                    cell.setCellValue(value == null ? "" : value.toString());
                    cell.setCellStyle(cellStyle03);
                }
            }

        }
        String fileName = new SimpleDateFormat("yyyy/MM/dd").format(new Date()) + title;

        ExtensionReportUtils.responseDown(fileName, workbook, response, request);


    }


    private XSSFCellStyle createCellStyle(XSSFWorkbook workbook, String fontName, short fontSize,
                                          boolean isBlod, boolean isBorder) {
        // 通过工作簿创建样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // 设置水平和垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        if (isBorder) {
            cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
            cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        }
        // 通过工作簿创建字体
        XSSFFont font = workbook.createFont();
        // 设置字体
        if (null != fontName && !"".equals(fontName)) {
            font.setFontName(fontName);
        }
        // 设置字体大小
        font.setFontHeightInPoints(fontSize);
        // 设置字体是否加粗
        font.setBold(isBlod);
        // 把字体set到样式中
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * @return void
     * @Author Sjiang
     * @Description 导出 已绑定
     * @Date 20:24 2019/1/23
     * @Param [dto, response]
     **/
    public void bindingExport(ExtensionReportDTO dto, AccessTokenService accessTokenService, HttpServletResponse response, HttpServletRequest request) {
        String userCode = ExtensionReportUtils.getUserCode(accessTokenService, request);
        // 获取 organCode ，如果为空则是总部权限
        String organCode = reportService.isExtensionAdmin(userCode);
        String title = "外拓项目台账信息-已绑定";

        List<ExtensionReportDTO> list = reportMapper.selectAllByDTO(dto, organCode);
        int jointSize = 0;
        for (ExtensionReportDTO reportDTO : list) {
            List<ExtensionJointInfoDTO> jointDTO = reportDTO.getJointDTO();
            jointSize = jointDTO != null && jointDTO.size() > jointSize ? jointDTO.size() : jointSize;
        }
        // 数据长度根据子对象长度来定
        // 表头
        String[] before = {"编号", "拓展区域", "项目名称", "接管区域", "项目性质", "合作模式", "签约日期"};
        String[] after = {"物业区域", "物业区域编号", "物业项目", "项目性质", "物业项目编号"};
        int size = before.length + after.length + jointSize * 2;
        ArrayList<String> strings = new ArrayList<>(size);

        for (int i = 0; i < before.length; i++) {
            strings.add(before[i]);
        }
        for (int i = 0; i < jointSize; i++) {
            strings.add("甲方对接人");
            strings.add("联系方式");

        }
        int count = 0;
        for (int i = jointSize * 2 + before.length; i < size; i++) {
            strings.add(after[count++]);
        }

        List<ArrayList<Object>> data = new ArrayList<>(list.size());
        if (!list.isEmpty()) {
            for (int i = 0; i < list.size(); i++) {
                ExtensionReportDTO reportDTO = list.get(i);
                ArrayList<Object> rowData = new ArrayList<>();
                // 编号
                rowData.add(reportDTO.getId());
                // 拓展区域
                rowData.add(reportDTO.getOrganName());
                // 项目名称
                rowData.add(reportDTO.getProjectName());
                // 接管区域
                rowData.add(reportDTO.getProposalOrganName());
                // 项目性质
                rowData.add(reportDTO.getProjectNature());
                // 合作模式
                rowData.add(reportDTO.getCorpType());
                // 签约日期
                rowData.add(reportDTO.getContractSingDate());
                // 甲方联系人
                List<ExtensionJointInfoDTO> jointDTOs = reportDTO.getJointDTO();
                // n < jointSize 是为了能插入正确的列数
                for (int n = 0; n < jointSize; n++) {
                    // 如果 jointDTOs 没有数据则直接插入空字符串
                    if (jointDTOs != null && jointDTOs.size() > 0 && jointDTOs.size() > n) {
                        // 当前的 jointDTOs size 不大于 n 就表示当前的子对象已遍历完 后面的需要插入空数据了
                        rowData.add(jointDTOs.get(n).getJointName());
                        rowData.add(jointDTOs.get(n).getJointTel());
                    }
                    // 无子对象则直接插入空
                    else {
                        rowData.add("");
                        rowData.add("");
                    }
                }
                // 物业区域
                rowData.add(reportDTO.getPropertyArea());
                // 物业区域编号
                rowData.add(reportDTO.getAreaNumber());
                // 物业项目
                rowData.add(reportDTO.getPropertyProject());
                // 项目性质
                rowData.add(reportDTO.getCommKind());
                // 物业项目编号
                rowData.add(reportDTO.getProjectNumber());
                data.add(rowData);

            }
        }
        String[] cellTitles = new String[strings.size()];
        for (int i = 0; i < strings.size(); i++) {
            cellTitles[i] = strings.get(i);
        }
        export(title, cellTitles, "sheet1", data, response, request);
    }

    /**
     * @return void
     * @Author Sjiang
     * @Description 导出 未绑定的 DUP 数据
     * @Date 20:24 2019/1/23
     * @Param [dto, response]
     **/
    public void dupUnbindingExport(ExtensionDupDTO dto, AccessTokenService accessTokenService, HttpServletResponse response, HttpServletRequest request) {
        String userCode = ExtensionReportUtils.getUserCode(accessTokenService, request);
        // 获取 organName ，如果为空则是总部权限
        String organName = reportService.isExtensionAdmin(userCode);
        String title = "未绑定-ERP";
        // 表头
        String[] cellTitles = {"编号", "拓展区域", "项目名称", "接管区域", "项目性质", "新/二手", "合作模式", "签约日期"};
        List<Long> ids = reportMapper.selectDupBindingId(organName);
        List<ExtensionDupDTO> list = dupMapper.selectUnbindingExportByDto(dto,organName, ids);
        List<ArrayList<Object>> data = new ArrayList<>(list.size());
        if (!list.isEmpty()) {
            list.forEach(d -> {
                ArrayList<Object> rowData = new ArrayList<>();
                // 编号
                rowData.add(d.getId());
                // 拓展区域
                rowData.add(d.getOrganName());
                // 项目名称
                rowData.add(d.getProjectName());
                // 接管区域
                rowData.add(d.getProposalOrganName());
                // 项目性质
                rowData.add(d.getProjectNature());
                // 新 / 二手
                if (d.getIsNew().intValue() == 1) {
                    rowData.add("新");
                } else {
                    rowData.add("二手");
                }
                // 合作模式
                rowData.add(d.getCorpType());
                // 签约日期
                rowData.add(d.getSignTime());
                data.add(rowData);
            });
        }
        export(title, cellTitles, "sheet1", data, response, request);
    }

    /**
     * @return void
     * @Author Sjiang
     * @Description 导出 未绑定的 ERP 数据
     * @Date 20:24 2019/1/23
     * @Param [dto, response]
     **/
    public void erpUnbindingExport(ExtensionErpDTO dto, AccessTokenService accessTokenService, HttpServletResponse response, HttpServletRequest request) {
        String userCode = ExtensionReportUtils.getUserCode(accessTokenService, request);
        // 获取 organName ，如果为空则是总部权限
        String organName = reportService.isExtensionAdmin(userCode);
        String title = "未绑定-DUP";
        // 表头
        String[] cellTitles = {"物业区域", "物业区域编号", "物业项目", "项目性质", "物业项目编号", "项目类型", "省级行政区"};
        List<Long> ids = reportMapper.selectErpBindingId(organName);
        List<ExtensionErpDTO> list = erpMapper.selectUnbindingExportByDto(dto, organName, ids);
        List<ArrayList<Object>> data = new ArrayList<>(list.size());
        if (!list.isEmpty()) {
            list.forEach(d -> {
                ArrayList<Object> rowData = new ArrayList<>();
                // 物业区域
                rowData.add(d.getPropertyArea());
                // 物业区域编号
                rowData.add(d.getAreaNumber());
                // 物业项目
                rowData.add(d.getPropertyProject());
                // 项目性质
                rowData.add(d.getCommKind());
                // 物业项目编号
                rowData.add(d.getProjectNumber());
                // 项目类型
                rowData.add(d.getProjectType());
                // 省级行政区
                rowData.add(d.getProvince());
                data.add(rowData);
            });
        }
        export(title, cellTitles, "sheet1", data, response, request);
    }

    /**
     * @return void
     * @Author Sjiang
     * @Description 外拓报表 导出
     * @Date 20:24 2019/1/23
     * @Param [dto, response]
     **/
    public void reportExport(ExtensionReportDTO dto, AccessTokenService accessTokenService, HttpServletResponse response, HttpServletRequest request) {
//        long startTime = System.currentTimeMillis();
        String userCode = ExtensionReportUtils.getUserCode(accessTokenService, request);
        // 获取 organCode ，如果为空则是总部权限
        String organCode = reportService.isExtensionAdmin(userCode);
        List<ExtensionReportDTO> list = reportMapper.selectExportByDto(dto,organCode);
        int maxCount = 100;
        int fileNum = 0;
        List<XSSFWorkbook> workbooks = Lists.newArrayList();
        if (list != null || list.size() > 0) {
            int size = list.size();
            fileNum = size % maxCount == 0 ? size / maxCount : size / maxCount + 1;
            CountDownLatch countDownLatch = new CountDownLatch(fileNum);
            for (int i = 0; i < fileNum; i++) {
                int start = i * maxCount ;
                int end = (start + maxCount) > size ? size : (start + maxCount) ;
                XSSFWorkbook workbook = new XSSFWorkbook();
//                workbooks.add(reportExport(list, start, end));

                // 合并 有多行物业构成情况 单元格
                poolTaskExecutor.execute(new ExtensionExportServiceTask(start, end, countDownLatch, list, workbook));
                workbooks.add(workbook);
            }
            try {
                countDownLatch.await();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
//        long startCode = System.currentTimeMillis();
//        System.err.println("start1:" + (startCode - startTime));
        try {
            String title = "拓后管理报表";
            response.setContentType("application/octet-stream");
            String downFileName = new SimpleDateFormat("yyyy/MM/dd").format(new Date()) + title + ".zip";
            if ("FF".equals(getBrowser(request))) {
                // 针对火狐浏览器处理方式不一样了
                downFileName = new String(downFileName.getBytes("GB2312"), "ISO-8859-1");
            }else{
                //encode解决中文乱码
                downFileName = URLEncoder.encode(downFileName, "UTF-8");
            }

            //如果输出的是中文名的文件，在此处就要用URLEncoder.encode方法进行处理
            response.setHeader("Content-Disposition", "attachment;filename=" + downFileName);
            ZipOutputStream zipOutputStream = new ZipOutputStream(response.getOutputStream());
//            // 转换流
//            byte[] buf = new byte[1024];
//            int len;
            int workbookSize = workbooks.size();
            for (int i = 0; i < workbookSize; i++) {
                String fileName = new StringBuffer()
                        .append(title)
                        .append("_")
                        .append(i+1)
                        .toString();
//                ZipEntry zipEntry = ;
                zipOutputStream.putNextEntry(new ZipEntry(fileName + ".xlsx"));
//                File file = File.createTempFile("fileName_" + i, ".xlsx");
//                FileOutputStream outputStream = FileUtils.openOutputStream(file);
                ByteArrayOutputStream bos = new ByteArrayOutputStream();
                workbooks.get(i).write(bos);
                bos.writeTo(zipOutputStream);
//                FileInputStream in = new FileInputStream(file);
//                while((len = in.read(buf)) > 0){
//                    zipOutputStream.write(buf, 0, len);
//                }
//                zipOutputStream.closeEntry();
//                in.close();
//                //清除临时文档
//                file.delete();
            }
//            long startCode2 = System.currentTimeMillis();
//            System.err.println("start1:" + (startCode2 - startCode));
            zipOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     *  以下为服务器端判断客户端浏览器类型的方法
     * @param request
     * @return
     */
    private String getBrowser(HttpServletRequest request) {
        String UserAgent = request.getHeader("USER-AGENT").toLowerCase();
        if (UserAgent != null) {
            if (UserAgent.indexOf("msie") != -1)
                return "IE";
            if (UserAgent.indexOf("firefox") != -1)
                return "FF";
            if (UserAgent.indexOf("safari") != -1)
                return "SF";
        }
        return null;
    }




    public void reportExportBackup(ExtensionReportDTO dto, AccessTokenService accessTokenService, HttpServletResponse response, HttpServletRequest request) {
//        long start = System.currentTimeMillis();
        String userCode = ExtensionReportUtils.getUserCode(accessTokenService, request);
        // 获取 organCode ，如果为空则是总部权限
        String organCode = reportService.isExtensionAdmin(userCode);
//        long startCode = System.currentTimeMillis();
//        System.err.println("startCode:" + (startCode - start));
        String title = "拓后管理报表";
        String sheetName = "sheet1";
        // 表头
        String[] titles1 = {"物业区域", "物业区域编号", "物业项目", "项目性质", "物业项目编号", "省级行政区",
                "地级行政区域", "县级行政区", "乡级行政区域", "项目类型", "项目营业执照的\r\n物业公司全称", "项目营业执照的\r\n母公司全称", "NC账套全称",
                "NC账套编码", "NC人力组织编码", "物业公司资金类型", "开发主体\r\n（房产核算全称）"};
        String[] titles2 = {"业委会成立情况", "项目开发主体", "物业业态", "项目开发状态", "接管状态", "拓展人员信息", "", "合同性质",
                "签约日期", "合同生效日期", "合同终止时间", "实际进驻时间", "收楼状态", "常规物业收费模式", "常规物业服务模式", "销售案场收费模式",
                "销售案场服务模式", "计划开盘时间(首期)", "实际开盘时间(首期)", "合同收楼时间(首批)", "实际收楼时间(首批)", "案场结束时间",
                "常规物业管理退场时间", "接管时间", "规划户数", "合同签约面积（㎡)", "物业构成情况", "", "", "", "上会年饱和\r\n收入(万元）",
                "上会经营指标", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "合同特殊约定", "", "", ""};
        List<ExtensionReportDTO> list = reportMapper.selectExportByDto(dto,organCode);
//        long start2 = System.currentTimeMillis();
//        System.err.println("start1:" + (start2 - start));
//        System.err.println("start1:" + (start2 - startCode));
        int jointSize = 1;
        int leaderSize = 1;
        for (ExtensionReportDTO reportDTO : list) {
            List<ExtensionJointInfoDTO> jointDTO = reportDTO.getJointDTO();
            List<ExtensionProjectLeaderDTO> leaderDTO = reportDTO.getLeaderDTO();
            jointSize = jointDTO != null && jointDTO.size() > jointSize ? jointDTO.size() : jointSize;
            leaderSize = leaderDTO != null && leaderDTO.size() > leaderSize ? leaderDTO.size() : leaderSize;
        }
        // 数组总大小
        int size = titles1.length + titles2.length + jointSize * 2 + leaderSize * 2;
        ArrayList<String> targetList = new ArrayList<>(size);
        // 合并的计数器
        HashMap<String, Integer> mergeMap = Maps.newHashMap();
        // 甲方对接人前半部分标题
        for (int i = 0; i < titles1.length; i++) {
            targetList.add(titles1[i]);
        }
        for (int i = 0; i < jointSize; i++) {
            targetList.add("甲方对接人" + i);
            targetList.add("联系方式" + i );
        }
        for (int i = 0; i < titles2.length; i++) {
            targetList.add(titles2[i]);
            if ("规划户数".equals(titles2[i])) {
                mergeMap.put("规划户数", targetList.indexOf(titles2[i]));
            }
        }
        for (int i = 0; i < leaderSize; i++) {
            targetList.add("项目负责人");
            targetList.add("");
        }
        List<ArrayList<Object>> data = new ArrayList<>(list.size());

        // 创建空的工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置宽度
        for (int i = 0; i < size; i++) {
            sheet.setColumnWidth(i, 16 * 330);
        }
        if (StringUtils.isNotBlank(title)) {
            // 合并单元格
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, size - 1);
            // 设置单元格格式
            sheet.addMergedRegion(setCellRangAddress(0, 0, 0, size - 1, cellRangeAddress));
            /*  第一行标题处理 */
            XSSFRow row01 = sheet.createRow(0);
            XSSFCell cell01 = row01.createCell(0);
            // 设置单元格内容
            cell01.setCellValue(title);
            // 通过工作簿创建样式
            XSSFCellStyle cellStyle = createCellStyle(workbook, "Courier New", (short) 15, true, true);
            // 设置单元格样式
            cell01.setCellStyle(cellStyle);
            /*  第一行标题处理  END */

            /*  第二行标题处理 */

            // 通过工作簿创建样式
            XSSFCellStyle cellStyle2 = createCellStyle(workbook, "Courier New", (short) 15, true, true);
            XSSFRow row02 = sheet.createRow(StringUtils.isNotBlank(title) ? 1 : 0);
            XSSFRow row03 = sheet.createRow(StringUtils.isNotBlank(title) ? 2 : 1);
            XSSFRow row04 = sheet.createRow(StringUtils.isNotBlank(title) ? 3 : 2);


            /*  第二行标题处理  END */
            // 获取 物业构成情况所在列 为后面的合并单元格做准备
            int propertyCol = 0;
//            long start3 = System.currentTimeMillis();
//            System.err.println("start2:" + (start3 - start2));
            for (int i = 0; i < size; i++) {
                String val = targetList.get(i);
                XSSFCell titleCell = row02.createCell(i);
                titleCell.setCellValue(val);
                titleCell.setCellStyle(cellStyle2);
                if ("拓展人员信息".equals(val)) {
                    XSSFCell cell = row03.createCell(i);
                    cell.setCellValue("姓名");
                    cell.setCellStyle(cellStyle2);
                    XSSFCell cell1 = row03.createCell(i + 1);
                    cell1.setCellValue("联系电话");
                    cell1.setCellStyle(cellStyle2);
                    // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
                    // 设置单元格格式
                    sheet.addMergedRegion(setCellRangAddress(1, 1, i, i + 1, cellRangeAddress));
//                    sheet.addMergedRegion(new CellRangeAddress(2, 3, i, i));
                    sheet.addMergedRegion(setCellRangAddress(2, 3, i, i, cellRangeAddress));
                    sheet.addMergedRegion(setCellRangAddress(2, 3, i + 1, i + 1, cellRangeAddress));
//                    sheet.addMergedRegion(new CellRangeAddress(2, 3, i + 1, i + 1));
                    row04.createCell(i).setCellStyle(cellStyle2);
                    i++;
                } else if ("物业构成情况".equals(val)) {
                    propertyCol = i;
                    XSSFCell cell = row03.createCell(i);
                    cell.setCellValue("业态名称");
                    cell.setCellStyle(cellStyle2);
                    XSSFCell cell1 = row03.createCell(i + 1);
                    cell1.setCellValue("可收费面积㎡");
                    cell1.setCellStyle(cellStyle2);
                    XSSFCell cell2 = row03.createCell(i + 2);
                    cell2.setCellValue("单价（元/㎡/个）");
                    cell2.setCellStyle(cellStyle2);
                    XSSFCell cell3 = row03.createCell(i + 3);
                    cell3.setCellValue("月饱和收入（元）");
                    cell3.setCellStyle(cellStyle2);
                    sheet.addMergedRegion(setCellRangAddress(1, 1, i, i + 3, cellRangeAddress));
                    for (int j = 0; j < 4; j++) {
                        sheet.addMergedRegion(setCellRangAddress(2, 3, i + j, i + j, cellRangeAddress));
                    }
                    row04.createCell(i).setCellStyle(cellStyle2);
                    i += 3;
                } else if ("上会经营指标".equals(val)) {
                    XSSFCell cell = row03.createCell(i);
                    cell.setCellValue("第一年");
                    cell.setCellStyle(cellStyle2);
                    XSSFCell cell1 = row03.createCell(i + 6);
                    cell1.setCellValue("第二年");
                    cell1.setCellStyle(cellStyle2);
                    XSSFCell cell2 = row03.createCell(i + 6 + 6);
                    cell2.setCellValue("第三年");
                    cell2.setCellStyle(cellStyle2);
                    for (int j = 0; j < 19; j += 6) {
                        XSSFCell cell3 = row04.createCell(i + j);
                        cell3.setCellValue("主营收缴率（%）");
                        cell3.setCellStyle(cellStyle2);
                        XSSFCell cell4 = row04.createCell(i + j + 1);
                        cell4.setCellValue("主营收入（元）");
                        cell4.setCellStyle(cellStyle2);
                        XSSFCell cell5 = row04.createCell(i + j + 2);
                        cell5.setCellValue("主营利润（元）");
                        cell5.setCellStyle(cellStyle2);
                        XSSFCell cell8 = row04.createCell(i + j + 3);
                        cell8.setCellValue("补贴(元)");
                        cell8.setCellStyle(cellStyle2);
                        XSSFCell cell6 = row04.createCell(i + j + 4);
                        cell6.setCellValue("总成本（元）");
                        cell6.setCellStyle(cellStyle2);
                        XSSFCell cell7 = row04.createCell(i + j + 5);
                        cell7.setCellValue("综合净利润（%）");
                        cell7.setCellStyle(cellStyle2);
                    }

                    sheet.addMergedRegion(setCellRangAddress(1, 1, i, i + 17, cellRangeAddress));

                    sheet.addMergedRegion(setCellRangAddress(2, 2, i, i + 5, cellRangeAddress));
                    sheet.addMergedRegion(setCellRangAddress(2, 2, i + 6, i + 6 + 5, cellRangeAddress));
                    sheet.addMergedRegion(setCellRangAddress(2, 2, i + 6 + 6, i + 6 + 6 + 5, cellRangeAddress));
                    i += 17;
                } else if ("合同特殊约定".equals(val)) {
                    XSSFCell cell = row03.createCell(i);
                    cell.setCellValue("空置房费用\r\n打折收取约定");
                    cell.setCellStyle(cellStyle2);
                    XSSFCell cell1 = row03.createCell(i + 1);
                    cell1.setCellValue("物业费单价约定");
                    cell1.setCellStyle(cellStyle2);
                    XSSFCell cell2 = row03.createCell(i + 2);
                    cell2.setCellValue("开办费用约定");
                    cell2.setCellStyle(cellStyle2);
                    XSSFCell cell3 = row03.createCell(i + 3);
                    cell3.setCellValue("其他特殊约定");
                    cell3.setCellStyle(cellStyle2);
                    sheet.addMergedRegion(setCellRangAddress(1, 1, i, i + 3, cellRangeAddress));
                    for (int j = 0; j < 4; j++) {
                        sheet.addMergedRegion(setCellRangAddress(2, 3, i + j, i + j, cellRangeAddress));
                    }
                    row04.createCell(i).setCellStyle(cellStyle2);
                    i += 3;
                } else if ("项目负责人".equals(val)) {
                    XSSFCell cell = row03.createCell(i);
                    cell.setCellValue("姓名");
                    cell.setCellStyle(cellStyle2);
                    XSSFCell cell1 = row03.createCell(i + 1);
                    cell1.setCellValue("联系方式");
                    cell1.setCellStyle(cellStyle2);

                    sheet.addMergedRegion(setCellRangAddress(1, 1, i, i + 1, cellRangeAddress));
                    sheet.addMergedRegion(setCellRangAddress(2, 3, i, i, cellRangeAddress));
                    sheet.addMergedRegion(setCellRangAddress(2, 3, i + 1, i + 1, cellRangeAddress));
                    row04.createCell(i).setCellStyle(cellStyle2);
                    i += 1;
                } else {
                    sheet.addMergedRegion(setCellRangAddress(1, 3, i, i, cellRangeAddress));
                    row03.createCell(i).setCellStyle(cellStyle2);
                    row04.createCell(i).setCellStyle(cellStyle2);
                }


            }
//            long start4 = System.currentTimeMillis();
//            System.err.println("start3:" + (start4 - start3));
            // 冻结窗格
//            sheet.createFreezePane(0, 4, 0, 1);
            // 合并之后的数据
            //     规划户数
            Map<Long, BigDecimal> planNumberMergeData = Maps.newHashMap();
            //     合同签约面积
            Map<Long, BigDecimal> contractAreaMergeData = Maps.newHashMap();
            //     可收费面积
            Map<Long, BigDecimal> ratesAreaMergeData = Maps.newHashMap();
            //     月饱和收入
            Map<Long, BigDecimal> monthIncomeMergeData = Maps.newHashMap();
            //     上会年饱和收入
            Map<Long, BigDecimal> yearIncomeMergeData = Maps.newHashMap();
            //     第一年 主营收入
            Map<Long, BigDecimal> firstSalesRevenueMergeData = Maps.newHashMap();
            //     第一年 主营利润
            Map<Long, BigDecimal> firstMainBusinessProfitMergeData = Maps.newHashMap();
            //     第一年 补贴
            Map<Long, BigDecimal> firstSubsidyMergeData = Maps.newHashMap();
            //     第一年 总成本
            Map<Long, BigDecimal> firstTotalCostMergeData = Maps.newHashMap();

            //     第二年 主营收入
            Map<Long, BigDecimal> secondSalesRevenueMergeData = Maps.newHashMap();
            //     第二年 主营利润
            Map<Long, BigDecimal> secondMainBusinessProfitMergeData = Maps.newHashMap();
            //     第二年 补贴
            Map<Long, BigDecimal> secondSubsidyMergeData = Maps.newHashMap();
            //     第二年 总成本
            Map<Long, BigDecimal> secondTotalCostMergeData = Maps.newHashMap();

            //     第三年 主营收入
            Map<Long, BigDecimal> thirdlySalesRevenueMergeData = Maps.newHashMap();
            //     第三年 主营利润
            Map<Long, BigDecimal> thirdlyMainBusinessProfitMergeData = Maps.newHashMap();
            //     第三年 补贴
            Map<Long, BigDecimal> thirdlySubsidyMergeData = Maps.newHashMap();
            //     第三年 总成本
            Map<Long, BigDecimal> thirdlyTotalCostMergeData = Maps.newHashMap();

            /** 中间行的处理  */
            // 数据处理
            if (!list.isEmpty()) {
                for (ExtensionReportDTO d : list) {
//                    createData(d, leaderSize, jointSize, data, planNumberMergeData, contractAreaMergeData,
//                            ratesAreaMergeData, monthIncomeMergeData, yearIncomeMergeData, firstSalesRevenueMergeData,
//                            firstMainBusinessProfitMergeData, firstSubsidyMergeData, firstTotalCostMergeData, secondSalesRevenueMergeData,
//                            secondMainBusinessProfitMergeData, secondSubsidyMergeData, secondTotalCostMergeData, thirdlySalesRevenueMergeData,
//                            thirdlyMainBusinessProfitMergeData, thirdlySubsidyMergeData, thirdlyTotalCostMergeData);
                }
            }
//            long start5 = System.currentTimeMillis();
//            System.err.println("start4:" + (start5 - start4));
            // 创建单元格样式
            XSSFCellStyle cellStyle03 = createCellStyle(workbook, "微软雅黑", (short) 12, false, true);
            int temp = StringUtils.isNotBlank(title) ? 4 : 3;
            // for循环处理
            int dataSize = data.size();
            for (int i = 0; i < dataSize; i++) {
                XSSFRow row = sheet.createRow(i + temp);
                int sizeForData = data.get(i).size();
                for (int j = 0; j < sizeForData; j++) {
                    XSSFCell cell = row.createCell(j);
                    Object value = data.get(i).get(j);
                    cell.setCellValue(value == null ? "" : value.toString());
                    cell.setCellStyle(cellStyle03);
                }
            }


//            long start6 = System.currentTimeMillis();
//            System.err.println("start5:" + (start6 - start5));
            CountDownLatch countDownLatch = new CountDownLatch(1);
            // 合并 有多行物业构成情况 单元格
//            poolTaskExecutor.execute(new ExtensionExportServiceTask(propertyCol, cellRangeAddress, sheet, data, countDownLatch, list));

//            long start7 = System.currentTimeMillis();
//            System.err.println("start6:" + (start7 - start6));
            // 合计
            XSSFRow row = sheet.createRow(dataSize + temp);
            for (int i = 0; i < size; i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
            }
            XSSFCell cell = row.createCell(0);
            cell.setCellValue("合计");
            cell.setCellStyle(cellStyle);
//            // 规划户数
            Integer number = mergeMap.get("规划户数");
            mergeSum(row, number, cellStyle, planNumberMergeData);

            // 合同签约面积
            mergeSum(row, number + 1, cellStyle, contractAreaMergeData);
            // 可收费面积
            mergeSum(row, number + 3, cellStyle, ratesAreaMergeData);
            // 月饱和收入
            mergeSum(row, number + 5, cellStyle, monthIncomeMergeData);
            // 年饱和收入
            mergeSum(row, number + 6, cellStyle, yearIncomeMergeData);

            // 第一年 主营收入
            mergeSum(row, number + 8, cellStyle, firstSalesRevenueMergeData);
            // 第一年 主营利润
            mergeSum(row, number + 8 + 1, cellStyle, firstMainBusinessProfitMergeData);
            // 第一年 补贴
            mergeSum(row, number + 8 + 2, cellStyle, firstSubsidyMergeData);
            // 第一年 总成本
            mergeSum(row, number + 8 + 3, cellStyle, firstTotalCostMergeData);
            // 第二年 主营收入
            mergeSum(row, number + 14, cellStyle, secondSalesRevenueMergeData);
            // 第二年 主营利润
            mergeSum(row, number + 14 + 1, cellStyle, secondMainBusinessProfitMergeData);
            // 第二年 补贴
            mergeSum(row, number + 14 + 2, cellStyle, secondSubsidyMergeData);
            // 第二年 总成本
            mergeSum(row, number + 14 + 3, cellStyle, secondTotalCostMergeData);
            // 第三年 主营收入
            mergeSum(row, number + 20, cellStyle, thirdlySalesRevenueMergeData);
            // 第三年 主营利润
            mergeSum(row, number + 20 + 1, cellStyle, secondMainBusinessProfitMergeData);
            // 第三年 补贴
            mergeSum(row, number + 20 + 2, cellStyle, thirdlySubsidyMergeData);
            // 第三年 总成本
            mergeSum(row, number + 20 + 3, cellStyle, thirdlyTotalCostMergeData);
            try {
                countDownLatch.await();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
//            long start8 = System.currentTimeMillis();
//            System.err.println("start7:" + (start8 - start7));
//            System.err.println("startEnd:" + (start8 - start));

        }

        String fileName = new SimpleDateFormat("yyyy/MM/dd").format(new Date()) + title;
        ExtensionReportUtils.responseDown(fileName, workbook, response, request);
    }




    // 设置单元格格式
    private CellRangeAddress setCellRangAddress(int firstRow, int lastRow, int firstCol, int lastCol, CellRangeAddress cellRangeAddress) {
        cellRangeAddress.setFirstRow(firstRow);
        cellRangeAddress.setLastRow(lastRow);
        cellRangeAddress.setFirstColumn(firstCol);
        cellRangeAddress.setLastColumn(lastCol);
        return cellRangeAddress;
    }




    // 当 bigDecimal 为 null 的时候返回 0
    private BigDecimal bigDecimalCheckNull(BigDecimal param){
        return param == null ? bigDecimal : param;
    }

    /**
     * @return void
     * @Author sjiang
     * @Description // 合计
     * @Date 21:28 2019-02-14
     * @Param [row, number, cellStyle, params]
     **/

    private void mergeSum(XSSFRow row, int number, XSSFCellStyle cellStyle, Map<Long, BigDecimal> params) {
        XSSFCell cell = row.createCell(number);
        cell.setCellStyle(cellStyle);
        BigDecimal sum = new BigDecimal(0);
        for (Long aLong : params.keySet()) {
            if (params.get(aLong) != null) {
                sum = sum.add(params.get(aLong));
            }
        }
        cell.setCellValue(sum.toString());
    }
}


