package cn.com.countrygarden.bi.main.service.dup.impl;

import cn.com.countrygarden.bi.main.core.Result;
import cn.com.countrygarden.bi.main.dto.*;
import cn.com.countrygarden.bi.main.dto.ReviewExcel.ReviewRinquDTO;
import cn.com.countrygarden.bi.main.dto.ReviewExcel.ReviewjudgeGradeDTO;
import cn.com.countrygarden.bi.main.model.PageData;
import cn.com.countrygarden.bi.main.model.common.CommData;
import cn.com.countrygarden.bi.main.model.dup.*;
import cn.com.countrygarden.bi.main.model.manager.map.ManagerMapCache;
import cn.com.countrygarden.bi.main.model.marketing.CommReportWeeklySummary;
import cn.com.countrygarden.bi.main.model.routine.RoutineManager;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * Created by chenweijiang on 2017/9/13.
 */
public class ExcelHelper {


    public void createCell(String value, CellStyle style, Row row, int col) {
        Cell cell = row.createCell(col);
        cell.setCellStyle(style);
        if (value != null) {
            cell.setCellValue(value);
        }
    }

    public void createManagerHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
//        createCell("周数", headerStyle, row, 1);
        createCell("月数", headerStyle, row, 1);
        createCell("区域", headerStyle, row, 2);
        createCell("项目", headerStyle, row, 3);
        createCell("管家姓名", headerStyle, row, 4);

        createCell("管家绩效", headerStyle, row, 5);
        createCell("业户需求受理（含投诉）", headerStyle, row, 16);
        createCell("云经营数据", headerStyle, row, 19);
        createCell("业户表扬记录", headerStyle, row, 22);
        createCell(null, headerStyle, row, 26);
        rowIndex += 1;
        row = sheet.createRow(rowIndex);
        createCell(null, headerStyle, row, 0);
        createCell(null, headerStyle, row, 1);
        createCell(null, headerStyle, row, 2);
        createCell(null, headerStyle, row, 3);
        createCell(null, headerStyle, row, 4);

        createCell("管家所辖户数", headerStyle, row, 5);
        createCell("微信添加户数", headerStyle, row, 6);
        createCell("微信添加量", headerStyle, row, 7);
        createCell("号码存储率", headerStyle, row, 8);
        createCell("微信添加率", headerStyle, row, 9);
        createCell("微信覆盖率", headerStyle, row, 10);
        createCell("业户识别率", headerStyle, row, 11);
        createCell("业户满意率", headerStyle, row, 12);
        createCell("收缴率目标值", headerStyle, row, 13);
        createCell("收缴率", headerStyle, row, 14);
        createCell("完成率", headerStyle, row, 15);

        createCell("业户需求受理(条)", headerStyle, row, 16);
        createCell("业户需求解决(条)", headerStyle, row, 17);
        createCell("微信表扬次数(次)", headerStyle, row, 18);

        createCell("团购业务(元)", headerStyle, row, 19);
        createCell("车位销售(元)", headerStyle, row, 20);
        createCell("房屋中介(元)", headerStyle, row, 21);

        createCell("表扬信(封)", headerStyle, row, 22);
        createCell("锦旗(面)", headerStyle, row, 23);
        createCell("其他途径表扬(条)", headerStyle, row, 24);
        createCell("公众号推送", headerStyle, row, 25);
        createCell("最后修改时间", headerStyle, row, 26);

        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 5, 15));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 18));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 19, 21));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 22, 25));

    }

    public void createManageBody(PageData pageData, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 2;
        List<ManagerData> dataList = (List<ManagerData>) pageData.getData();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");

        if (dataList == null || dataList.size() == 0) {
            return;
        }
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(rowIndex);
            ManagerData data = dataList.get(i);
            writeManageDate(row, style, data, i, format);
            rowIndex += 1;
        }

    }

    public void writeManageDate(Row row, CellStyle style, ManagerData data, int rowNum, SimpleDateFormat format) {
        if (ManagerDataConstant.ManagerDataStatus.sum.equals(data.getStatus())) {
            createCell("合计", style, row, 0);
        } else {
            createCell(rowNum + "", style, row, 0);
        }
        createCell(data.getDateRang(), style, row, 1);
        createCell(data.getOrganName(), style, row, 2);
        createCell(data.getCommName(), style, row, 3);
        if (ManagerDataConstant.ManagerDataStatus.unfill.equals(data.getStatus())) {
            for (int i = 4; i < 26; i++) {
                createCell("-", style, row, i);
            }
        } else {
            createCell(data.getManagerName(), style, row, 4);
            createCell(data.getCustomres() + "", style, row, 5);
            createCell(data.getWechatCustomers() + "", style, row, 6);
            createCell(data.getAddedWechat() + "", style, row, 7);

            createCell(data.getPhonePercentage() + "%", style, row, 8);
            createCell(data.getWechatPercentage() + "%", style, row, 9);
            createCell(data.getWechatCoverPercentage() + "%", style, row, 10);
            createCell(data.getRecognitionPercentage() + "%", style, row, 11);
            createCell(data.getSatisfaction() + "%", style, row, 12);

            if (data.getCollectionRateTarget()!=null){
                createCell(data.getCollectionRateTarget() + "", style, row, 13);
            }else {
                createCell(0+ "", style, row, 13);
            }

            if (data.getCollectionRate()!=null){
                createCell(data.getCollectionRate() + "", style, row, 14);
            }else {
                createCell(0 + "", style, row, 14);
            }

            if (data.getCompletionRate()!=null){
                createCell(data.getCompletionRate() + "", style, row, 15);
            }else {
                createCell(0 + "", style, row, 15);
            }

            createCell(data.getRequestReceived() + "", style, row, 16);
            createCell(data.getRequestSolved() + "", style, row, 17);
            createCell(data.getWechatPraise() + "", style, row, 18);

             if (data.getGroupBusiness()!=null){
                 createCell(data.getGroupBusiness() + "", style, row, 19);
             }else {
                 createCell(0 + "", style, row, 19);
             }
            if (data.getParkingSale()!=null){
             createCell(data.getParkingSale() + "", style, row, 20);
            }else {
                createCell(0 + "", style, row, 20);
            }
             if (data.getHouseAgency()!=null){
                 createCell(data.getHouseAgency() + "", style, row, 21);
             }else {
                 createCell(0 + "", style, row, 21);
             }
            createCell(data.getLetterPraise() + "", style, row, 22);
            createCell(data.getBannerNum() + "", style, row, 23);
            createCell(data.getOtherPraise() + "", style, row, 24);
            createCell(data.getWechatSubscription() + "", style, row, 25);
            if (data.getUpdatedDate() != null) {
                createCell(format.format(data.getUpdatedDate()), style, row, 26);
            }
        }
    }

    public void createBuoyBody(PageData pageData, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 2;
        List<BuoyQueryData> dataList = (List<BuoyQueryData>) pageData.getData();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        for (BuoyQueryData data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(rowIndex - 1 + "", style, row, 0);
            createCell(data.getOrganName(), style, row, 1);
            createCell(data.getCommName(), style, row, 2);
            createCell(data.getDateRang(), style, row, 3);
            if (data.getManagerNum() == null || data.getManagerNum() <= 0) {
                createCell("-", style, row, 10);
                createCell("-", style, row, 11);
                createCell("-", style, row, 12);
                createCell("-", style, row, 13);
                createCell("-", style, row, 14);
                createCell("-", style, row, 16);

                createCell("-", style, row, 17);
                createCell("-", style, row, 18);
                createCell("-", style, row, 19);
                createCell("-", style, row, 20);
                createCell("-", style, row, 21);
                createCell("-", style, row, 22);

                createCell("-", style, row, 23);

                createCell("-", style, row, 25);
                createCell("-", style, row, 26);
                createCell("-", style, row, 27);
                createCell("-", style, row, 28);
                createCell("-", style, row, 29);
            } else {
                createCell(data.getPhonePercentage() + "%", style, row, 10);
                createCell(data.getWechatPercentage() + "%", style, row, 11);
                createCell(data.getWechatCoverPercentage() + "%", style, row, 12);
                createCell(data.getManagerRecognitionPercentage() + "%", style, row, 13);
                createCell(data.getSatisfaction() + "%", style, row, 14);

                createCell(data.getRequestReceived() + "", style, row, 16);
                createCell(data.getReceivedQuickEvents() + "", style, row, 17);
                createCell(data.getReceivedRepairEvents() + "", style, row, 18);
                createCell(data.getReceivedComplaintEvents() + "", style, row, 19);
                createCell(data.getSolvedPercentage() + "%", style, row, 20);
                createCell(data.getSolvedQuickEvents() + "", style, row, 21);
                createCell(data.getSolvedRepairEvents() + "", style, row, 22);
                createCell(data.getSolvedComplaintEvents() + "", style, row, 23);
                createCell(data.getWechatPraise() + "", style, row, 25);
                createCell(data.getLetterPraise() + "", style, row, 26);
                createCell(data.getBannerNum() + "", style, row, 27);
                createCell(data.getOtherPraise() + "", style, row, 28);
                createCell(data.getWechatSubscription() + "", style, row, 29);
            }

            if (BuoyDataConstant.BuoyDataStatus.unfill.equals(data.getStatus())) {
                createCell("-", style, row, 4);
                createCell("-", style, row, 5);
                createCell("-", style, row, 6);
                createCell("-", style, row, 7);
                createCell("-", style, row, 8);
                createCell("-", style, row, 9);

                createCell("-", style, row, 15);
                createCell("-", style, row, 24);
                createCell("-", style, row, 30);
            } else {
                createCell(data.getRecognitionPercentage() + "%", style, row, 4);
                createCell(data.getVisitorNum() + "", style, row, 5);
                createCell(data.getTraditionVisitors() + "", style, row, 6);

                createCell(data.getAppointmentVisitors() + "", style, row, 7);
                createCell(data.getAppointmentByDevice() + "", style, row, 8);
                createCell(data.getOtherVisitors() + "", style, row, 9);

                createCell(data.getVisitCustomerNum() + "", style, row, 15);
                createCell(data.getVisitSatisfaction() + "%", style, row, 24);
                if (data.getUpdatedDate() != null) {
                    createCell(format.format(data.getUpdatedDate()), style, row, 30);
                } else {
                    createCell("-", style, row, 30);
                }
            }
            rowIndex++;
        }
    }

    public void createContractBody(List<ContractStatistical> dataList, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        for (ContractStatistical data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(data.getBipNum(), style, row, 0);
            createCell(data.getContName(), style, row, 1);
            if("1".equals(data.getIsAuditing())) {
                data.setIsAuditing("已审核");
            }else if("0".equals(data.getIsAuditing())) {
                data.setIsAuditing("未审核");
            }else {
                data.setIsAuditing("");
            }
            createCell(data.getIsAuditing(), style, row, 2);
            //将日期转换为对应的 格式
            DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //HH表示24小时制；
            String formatDate = null;
            if(data.getRegDate()!=null) {
                formatDate = dFormat.format(data.getRegDate());
            }
            createCell(formatDate, style, row, 3);
            createCell(data.getCommName(), style, row, 4);
            createCell(data.getOrganName(), style, row, 5);

            String businessType="";
            if(data.getBusinessType()!=null){
            switch (data.getBusinessType()){
                case "1":
                    businessType="房屋配套";
                    break;
                case "2":
                    businessType="汽车服务";
                    break;
                case "3":
                    businessType="生活团购";
                    break;
                case "4":
                    businessType=" 保险";
                    break;
                case "5":
                    businessType="场地运营";
                    break;
                case "6":
                    businessType="社区广告";
                    break;
                case "7":
                    businessType="短租服务";
                    break;
                default:
                    break;
            }
          } 
            createCell(businessType, style, row, 6);
            createCell(data.getFinalJudgmentDate(), style, row, 7);

            rowIndex++;
        }
    }


    public void createproblemexport(List<ReviewjudgeGradeDTO> reviewjudgegrade,ReviewRinquDTO reviewrinqu
            ,List<ReviewQuestionnaireProblemDTO> questionoblemsex,List<ReviewScoreProblemDTO> scoreproblemexcl
            ,List<ReviewQuestionnaireProblemDTO> questionoblemswenex,List<ReviewScoreProblemDTO> scoreproblemwenexcl
            ,Workbook workbook, Sheet sheet) {

        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 0;
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 60 * 256);

        createCell("与评估人关系", style, row, 0);
        createCell(" ", style, row, 1);
        createCell(" ", style, row, 2);

        for (int i = 0; i < reviewjudgegrade.size(); i++) {
            createCell(reviewjudgegrade.get(i).getRgname(), style, row, 3+i);
        }
        row = sheet.createRow((short) 1);
        createCell("评选时间", style, row, 0);

        for (int i = 0; i < reviewjudgegrade.size(); i++) {
            sheet.setColumnWidth(3+i, 20 * 256);
            DateFormat dFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); //HH表示24小时制；
            String formatDate = null;
            if(reviewjudgegrade.get(i).getCreateTime()!=null) {
                formatDate = dFormat.format(reviewjudgegrade.get(i).getCreateTime());
            }
            createCell(formatDate, style, row, 3+i);
        }
        row = sheet.createRow((short) 2);
        createCell("序号", style, row, 0);
        createCell("核心素质项", style, row, 1);
        createCell(reviewrinqu.getRqname(), style, row, 2);

        for (int i = 0; i < reviewjudgegrade.size(); i++) {
            createCell(reviewjudgegrade.get(i).getRjname(), style, row, 3+i);
        }


//		人员信息
        String[] judgeAry=new String[reviewjudgegrade.size()];
        for (int i = 0; i < reviewjudgegrade.size(); i++) {
            judgeAry[i]=reviewjudgegrade.get(i).getRjname();
        }

//		构建量表单选列表数据
        List<ProblemDetailDTO> problemDetailDTOS=new ArrayList<>();
        if(questionoblemsex.size()!=0) {
            for (int i = 0; i < questionoblemsex.size(); i++) {

                ProblemDetailDTO detail=new ProblemDetailDTO();

                detail.setDescribedname(questionoblemsex.get(i).getName());
                detail.setDescribed(questionoblemsex.get(i).getQuestionnaireProblemClassificationId());
                detail.setQuestionnaireId(questionoblemsex.get(i).getId()+"");
                detail.setTitleDescription(questionoblemsex.get(i).getTitleDescription());
                List<String> list = new ArrayList<>(judgeAry.length);
                for (int i1 = 0; i1 < judgeAry.length; i1++) {
                    list.add("");
                }
                detail.setAnswer(list);
                problemDetailDTOS.add(detail);
            }

//			组装量表单选数据
            for (ReviewScoreProblemDTO problem : scoreproblemexcl) {

                int judgeIndex=findAryIndex(problem.getName(),judgeAry);
                int detailIndex=findDetailAryIndex(problem.getQuestionnaireId(),problemDetailDTOS);
                ProblemDetailDTO detailDTO = problemDetailDTOS.get(detailIndex);
                if(problem.getScore()!=null) {
                    detailDTO.getAnswer().set(judgeIndex,problem.getScore()+"");

                }
            }
            int rowIndexwti = 3;
            Row rowrow;
            if(problemDetailDTOS.size()!=0) {
                for (int i = 0; i < problemDetailDTOS.size(); i++) {
                    rowrow = sheet.createRow(rowIndexwti);
                    createCell(String.valueOf(i+1), style, rowrow, 0);
                    createCell(problemDetailDTOS.get(i).getDescribedname(),style, rowrow, 1);
                    createCell(problemDetailDTOS.get(i).getTitleDescription(),style, rowrow, 2);
                    for (int j = 0; j < problemDetailDTOS.get(i).getAnswer().size(); j++) {

                        createCell(problemDetailDTOS.get(i).getAnswer().get(j),style, rowrow, 3+j);
                    }


                    rowIndexwti++;

                }
            }

        }
        List<ProblemDetailDTO> problemDetailwenDTOS=new ArrayList<>();
        if (questionoblemsex.size()!=0&&questionoblemswenex.size()!=0) {


//			构建问答列表数据
            for (int i = 0; i < questionoblemswenex.size(); i++) {

                ProblemDetailDTO detail=new ProblemDetailDTO();

                detail.setDescribedname(questionoblemswenex.get(i).getName());
                detail.setDescribed(questionoblemswenex.get(i).getQuestionnaireProblemClassificationId());
                detail.setQuestionnaireId(questionoblemswenex.get(i).getId()+"");
                detail.setTitleDescription(questionoblemswenex.get(i).getTitleDescription());
                List<String> list = new ArrayList<>(judgeAry.length);
                for (int i1 = 0; i1 < judgeAry.length; i1++) {
                    list.add("");
                }
                detail.setAnswer(list);
                problemDetailwenDTOS.add(detail);
            }
//			组装量问答数据

            for (ReviewScoreProblemDTO problem : scoreproblemwenexcl) {

                int judgeIndex=findAryIndex(problem.getName(),judgeAry);
                int detailIndex=findDetailAryIndex(problem.getQuestionnaireId(),problemDetailwenDTOS);
                ProblemDetailDTO detailDTO = problemDetailwenDTOS.get(detailIndex);
                if(problem.getAnswer()!=null) {
                    detailDTO.getAnswer().set(judgeIndex,problem.getAnswer()+"");

                }
            }
            int rowIndexwenti = problemDetailDTOS.size()+3;
            Row rowrowen;
            for (int i = 0; i < problemDetailwenDTOS.size(); i++) {
                rowrowen = sheet.createRow(rowIndexwenti);
                createCell(String.valueOf(problemDetailDTOS.size()+i+1), style, rowrowen, 0);
                createCell("问答题",style, rowrowen, 1);
                createCell(problemDetailwenDTOS.get(i).getTitleDescription(),style, rowrowen, 2);
                for (int j = 0; j < problemDetailwenDTOS.get(i).getAnswer().size(); j++) {

                    createCell(problemDetailwenDTOS.get(i).getAnswer().get(j),style, rowrowen, 3+j);
                }


                rowIndexwenti++;

            }

        }else {

//			构建问答列表数据
            for (int i = 0; i < questionoblemswenex.size(); i++) {

                ProblemDetailDTO detail=new ProblemDetailDTO();

                detail.setDescribedname(questionoblemswenex.get(i).getName());
                detail.setDescribed(questionoblemswenex.get(i).getQuestionnaireProblemClassificationId());
                detail.setQuestionnaireId(questionoblemswenex.get(i).getId()+"");
                detail.setTitleDescription(questionoblemswenex.get(i).getTitleDescription());
                List<String> list = new ArrayList<>(judgeAry.length);
                for (int i1 = 0; i1 < judgeAry.length; i1++) {
                    list.add("");
                }
                detail.setAnswer(list);
                problemDetailwenDTOS.add(detail);
            }
//				组装量问答数据

            for (ReviewScoreProblemDTO problem : scoreproblemwenexcl) {

                int judgeIndex=findAryIndex(problem.getName(),judgeAry);
                int detailIndex=findDetailAryIndex(problem.getQuestionnaireId(),problemDetailwenDTOS);
                ProblemDetailDTO detailDTO = problemDetailwenDTOS.get(detailIndex);
                if(problem.getAnswer()!=null) {
                    detailDTO.getAnswer().set(judgeIndex,problem.getAnswer()+"");

                }
            }

            int rowIndexwentiw = 3;
            Row rowrowenw;
            for (int i = 0; i < problemDetailwenDTOS.size(); i++) {
                rowrowenw = sheet.createRow(rowIndexwentiw);
                createCell(String.valueOf(i+1), style, rowrowenw, 0);
                createCell("问答题",style, rowrowenw, 1);
                createCell(problemDetailwenDTOS.get(i).getTitleDescription(),style, rowrowenw, 2);
                for (int j = 0; j < problemDetailwenDTOS.get(i).getAnswer().size(); j++) {

                    createCell(problemDetailwenDTOS.get(i).getAnswer().get(j),style, rowrowenw, 3+j);
                }


                rowIndexwentiw++;

            }

        }


        //      for (ReviewScoreProblemDTO data : dataList) {
//        	  Row row = sheet.createRow(rowIndex);
//        	  if(data.getRgname()==1) {
//        		  createCell("上级", style, row, 0);
//        	  }
//        	  if(data.getRgname()==2) {
//        		  createCell("同级", style, row, 0);
//        	  }
//        	  if(data.getRgname()==3) {
//        		  createCell("下级", style, row, 0);
//        	  }
//
//
//              createCell(data.getTitleDescription(), style, row, 1);
//              if(data.getDanxu()==null) {
//            	  createCell("暂无", style, row, 2);
//              }else {
//            	  createCell(data.getDanxu(), style, row, 2);
//              }
//              if(data.getScore()==null) {
//            	  createCell("暂无".toString(), style, row, 3);
//              }else {
//            	  createCell(data.getScore().toString(), style, row, 3);
//              }
//
//              if(data.getAnswer()==null) {
//            	  createCell("暂无", style, row, 4);
//              }else {
//            	  createCell(data.getAnswer(), style, row, 4);
//              }
//
//
//              createCell(data.getName(), style, row, 5);
//              //将日期转换为对应的 格式
//              DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //HH表示24小时制；
//              String formatDate = null;
//              if(data.getCreateTime()!=null) {
//              formatDate = dFormat.format(data.getCreateTime());
//              }
//              createCell(formatDate, style, row, 6);
//


        // rowIndex++;
        //}
    }

    private int findDetailAryIndex(String id, List<ProblemDetailDTO> problemDetailDTOS) {
        for (int i = 0; i < problemDetailDTOS.size(); i++) {
            if(problemDetailDTOS.get(i).getQuestionnaireId().equalsIgnoreCase(id)){
                return i;
            }
        }
        return -1;
    }

    public int findAryIndex(String name,String[] ary){
        for (int i = 0; i < ary.length; i++) {
            if(ary[i].equalsIgnoreCase(name)){
                return i;
            }
        }
        return -1;
    }

    /**
     * 导出评分问题
     * @param workbook
     * @param sheet
     */
    public void createreviewscore(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        sheet.setColumnWidth(3, 80 * 256);

        row.setHeight((short) (250 * 2));
        createCell("与评估人关系", headerStyle, row, 0);
        createCell(" ", headerStyle, row, 1);
        createCell(" ", headerStyle, row, 2);
        createCell("  ", headerStyle, row, 3);
//         createCell("答案", headerStyle, row, 4);
//         createCell("评选人", headerStyle, row, 5);
//         createCell("评选时间", headerStyle, row, 6);
//         createCell("项目名称", headerStyle, row, 4);
//         createCell("区域", headerStyle, row, 5);

    }


    /**
     * 创建导出所有合同统计
     * @param workbook
     * @param sheet
     */
    public void createContractHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("BIP流程编号", headerStyle, row, 0);
        createCell("合同名称", headerStyle, row, 1);
        createCell("审核状态", headerStyle, row, 2);
        createCell("录入时间", headerStyle, row, 3);
        createCell("项目名称", headerStyle, row, 4);
        createCell("区域", headerStyle, row, 5);
        createCell("业务类型", headerStyle, row, 6);
        createCell("合同终审日期", headerStyle, row, 7);

    }
    /**
    *
    * @param workbook
    * @param sheet
    */
   public void createSiteResourcesHeader(Workbook workbook, Sheet sheet) {
       int rowIndex = 0;
       CellStyle headerStyle = createHeaderStyle(workbook);
       Row row = sheet.createRow(rowIndex);
       row.setHeight((short) (250 * 2));
       
       createCell("区域名称", headerStyle, row, 0);
       createCell("项目名称", headerStyle, row, 1);
       createCell("区域编码", headerStyle, row, 2);
       createCell("项目编码", headerStyle, row, 3);
       
       createCell("项目城市级别", headerStyle, row, 4);
       createCell("面积", headerStyle, row, 5);
       createCell("面积明细", headerStyle, row, 6);

       //createCell("公共资源类型", headerStyle, row, 5);
       createCell("资源名称", headerStyle, row, 7);
       createCell("资源具体位置", headerStyle, row, 8);
       createCell("使用状态", headerStyle, row, 9);
       
       createCell("添加时间", headerStyle, row, 10);
       
       //createCell("数量", headerStyle, row, 10);
       createCell("单位", headerStyle, row, 11);
      // createCell("规格型号", headerStyle, row, 12);
       
       //createCell("合作方", headerStyle, row, 13);
       //createCell("价格", headerStyle, row, 14);
       //createCell("出租时限", headerStyle, row, 15);
       //createCell("资源闲置情况说明", headerStyle, row, 16);
       createCell("备注", headerStyle, row, 12);
       //这里有图片服务器再做!
       createCell("图片", headerStyle, row, 13);
       
/*        createCell("区域", headerStyle, row, 0);
       createCell("项目", headerStyle, row, 1);
       
       createCell("项目城市级别", headerStyle, row, 2);
       createCell("面积", headerStyle, row, 3);
       createCell("面积明细", headerStyle, row, 4);

       createCell("公共资源类型", headerStyle, row, 5);
       createCell("资源名称", headerStyle, row, 6);
       createCell("资源具体位置", headerStyle, row, 7);
       createCell("使用状态", headerStyle, row, 8);
       
       createCell("添加时间", headerStyle, row, 9);
       
       createCell("数量", headerStyle, row, 10);
       createCell("单位", headerStyle, row, 11);
       createCell("规格型号", headerStyle, row, 12);
       
       createCell("合作方", headerStyle, row, 13);
       createCell("价格", headerStyle, row, 14);
       createCell("出租时限", headerStyle, row, 15);
       createCell("资源闲置情况说明", headerStyle, row, 16);
       createCell("备注", headerStyle, row, 17);
       createCell("图片", headerStyle, row, 18);*/
   }
   public void createSiteResourcesBody(List<SiteResources> dataList, Workbook workbook, Sheet sheet,Map<String,List<Map<String, String>>> map) {
       final CellStyle style = workbook.createCellStyle();
       style.setBorderBottom(CellStyle.BORDER_THIN);
       style.setBorderLeft(CellStyle.BORDER_THIN);
       style.setBorderRight(CellStyle.BORDER_THIN);
       style.setBorderTop(CellStyle.BORDER_THIN);
       style.setWrapText(false);
       int rowIndex = 1;
       
      // List<Map<String, String>> resourceType = map.get("resourceType");
       List<Map<String, String>> resourceName = map.get("resourceName");
       List<Map<String, String>> useStatus = map.get("useStatus");
       List<Map<String, String>> commCityLevel = map.get("commCityLevel");
       List<Map<String, String>> siteUnit = map.get("siteUnit");
       
       for (SiteResources data : dataList) {
           Row row = sheet.createRow(rowIndex);
           
           createCell(data.getOrganName(), style, row, 0);
           createCell(data.getCommName(), style, row, 1);
           
           createCell(data.getOrganCode(), style, row, 2);
           createCell(String.valueOf(data.getCommId()), style, row, 3);
           
           Optional<Map<String, String>> findFirstcommCityLevel = commCityLevel.stream().filter(m -> m.get("value").equals(data.getCommCityLevel())).findFirst();
           if(findFirstcommCityLevel.isPresent()){
               data.setCommCityLevel(findFirstcommCityLevel.get().get("name"));
           }
           

           createCell(data.getCommCityLevel(), style, row, 4);
           createCell(data.getArea().toString(), style, row, 5);
           createCell(data.getAreaDetail(), style, row, 6);
           
  
/*            Optional<Map<String, String>> findFirst = resourceType.stream().filter(m -> m.get("value").equals(data.getPublicResourcesType())).findFirst();
           if(findFirst.isPresent()){
               data.setPublicResourcesType(findFirst.get().get("name"));
           }
           
           createCell(data.getPublicResourcesType(), style, row, 5);*/

           Optional<Map<String, String>> findFirstName= resourceName.stream().filter(m -> m.get("value").equals(data.getName())).findFirst();
           if(findFirstName.isPresent()){
               data.setName(findFirstName.get().get("value")+findFirstName.get().get("name"));
           }

           createCell(data.getName(), style, row, 7);
           createCell(data.getLocation(), style, row, 8);
           
           Optional<Map<String, String>> findFirstuseStatus= useStatus.stream().filter(m -> m.get("value").equals(data.getForthputtingStatus())).findFirst();
           if(findFirstuseStatus.isPresent()){
               data.setForthputtingStatus(findFirstuseStatus.get().get("name"));
           }

           createCell(data.getForthputtingStatus(), style, row, 9);
           
           createCell(data.getAddDate(), style, row, 10);

           //createCell(data.getNumber()+"", style, row, 10);
           Optional<Map<String, String>> findSiteUnit= siteUnit.stream().filter(m -> m.get("value").equals(data.getUnit())).findFirst();
           if(findSiteUnit.isPresent()){
               data.setUnit(findSiteUnit.get().get("name"));
           }
           createCell(data.getUnit(), style, row, 11);
           //createCell(data.getSpecification(), style, row, 12);

          // createCell(data.getPartner(), style, row, 13);
           //createCell(data.getPrice()+"", style, row, 14);
           //createCell(data.getLeaseTimeLimit()+"", style, row, 15);
           //createCell(data.getIdleResourcesDirections(), style, row, 16);
           createCell(data.getRemark(), style, row, 12);
           
           createCell(data.getImage(), style, row, 13);
           rowIndex++;
       }
   }

    public void createBuoyHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("区域", headerStyle, row, 1);
        createCell("项目", headerStyle, row, 2);
        createCell("周数", headerStyle, row, 3);
        createCell("门岗", headerStyle, row, 4);
        createCell("管家绩效", headerStyle, row, 10);
        createCell("诉求", headerStyle, row, 16);
        createCell("业户表扬记录", headerStyle, row, 25);
        createCell("", headerStyle, row, 30);

        rowIndex += 1;
        row = sheet.createRow(rowIndex);
        createCell(null, headerStyle, row, 0);
        createCell(null, headerStyle, row, 1);
        createCell(null, headerStyle, row, 2);
        createCell(null, headerStyle, row, 3);
        createCell("常住业主识别率", headerStyle, row, 4);
        createCell("访客总量(人/周)", headerStyle, row, 5);
        createCell("传统通传放行(人/周)", headerStyle, row, 6);
        createCell("管家提前预约(人/周)", headerStyle, row, 7);
        createCell("智能化终端(人/周)", headerStyle, row, 8);
        createCell("其他方式(人/周)", headerStyle, row, 9);
        createCell("号码存储率", headerStyle, row, 10);
        createCell("微信添加率", headerStyle, row, 11);
        createCell("微信覆盖率", headerStyle, row, 12);
        createCell("业户识别率", headerStyle, row, 13);
        createCell("业户满意率", headerStyle, row, 14);
        createCell("业户拜访量(户)", headerStyle, row, 15);
        createCell("诉求总量(起)", headerStyle, row, 16);
        createCell("受理快速处理(起)", headerStyle, row, 17);
        createCell("受理报事报修(起)", headerStyle, row, 18);
        createCell("受理业户投诉(起)", headerStyle, row, 19);
        createCell("关闭率", headerStyle, row, 20);
        createCell("解决快速处理(起)", headerStyle, row, 21);
        createCell("解决报事报修(起)", headerStyle, row, 22);
        createCell("解决业户投诉(起)", headerStyle, row, 23);
        createCell("回访满意率", headerStyle, row, 24);
        createCell("微信表扬次数(次)", headerStyle, row, 25);
        createCell("表扬信(封)", headerStyle, row, 26);
        createCell("锦旗(面)", headerStyle, row, 27);
        createCell("其他途径表扬(条)", headerStyle, row, 28);
        createCell("公众号推送", headerStyle, row, 29);
        createCell("最后修改时间", headerStyle, row, 30);


        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 9));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 15));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 16, 24));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 25, 29));
    }


    public CellStyle createBodyStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        return style;
    }

    public CellStyle createRedBodyStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        return style;
    }

    public CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setWrapText(true);
        headerStyle.setBorderBottom(BorderStyle.THIN); //下边框
        headerStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
        headerStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headerStyle.setBorderTop(BorderStyle.THIN);//上边框
        headerStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        headerStyle.setBorderRight(BorderStyle.THIN);//右边框
        headerStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        return headerStyle;
    }


    public void createWeeklyReportHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("周期", headerStyle, row, 0);
        createCell("拓展区域", headerStyle, row, 1);
        createCell("项目/公司名称", headerStyle, row, 2);
        createCell("编号", headerStyle, row, 3);
        createCell("项目类型", headerStyle, row, 4);
        createCell("合作类型", headerStyle, row, 5);
        createCell("跟进人", headerStyle, row, 6);
        createCell("规划面积(万㎡)", headerStyle, row, 7);
        createCell("状态", headerStyle, row, 8);
        createCell("本周工作内容", headerStyle, row, 9);
        createCell("下周计划", headerStyle, row, 10);
        createCell("需协助事项", headerStyle, row, 11);
    }

    public void createWeeklyReportBody(PageData pageData, Workbook workbook, Sheet sheet) {
        DecimalFormat df = new DecimalFormat("0.##");
        CellStyle style = createBodyStyle(workbook);
        CellStyle redStyle = createRedBodyStyle(workbook);
        int rowIndex = 1;
        List<CommReportWeeklySummary> dataList = (List<CommReportWeeklySummary>) pageData.getData();
        for (CommReportWeeklySummary data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(data.getDateRange(), style, row, 0);
            createCell(data.getOrganName() == null ? "" : data.getOrganName(), style, row, 1);
            createCell(data.getProjectName() == null ? "" : data.getProjectName(), style, row, 2);
            createCell(data.getSerialNumber() == null ? "" : data.getSerialNumber(), style, row, 3);
            createCell(data.getProjectType() == null ? "" : data.getProjectType(), style, row, 4);
            createCell(data.getCooperationTypes() == null ? "" : data.getCooperationTypes(), style, row, 5);
            createCell(data.getContact() == null ? "" : data.getContact(), style, row, 6);
            if (data.getPlanningArea() != null) {
                String area = df.format(data.getPlanningArea());
                createCell(area, style, row, 7);
            } else {
                createCell("", style, row, 7);
            }

            if (data.getStateChange() != null && data.getStateChange() == true) {
                createCell(data.getState() == null ? "" : data.getState() + "", redStyle, row, 8);
            } else {
                createCell(data.getState() == null ? "" : data.getState() + "", style, row, 8);
            }

            createCell(data.getWeekWorkContent() == null ? "-" : data.getWeekWorkContent() + "", style, row, 9);
            createCell(data.getNextWeekPlan() == null ? "-" : data.getNextWeekPlan() + "", style, row, 10);
            createCell(data.getAssistantContent() == null ? "-" : data.getAssistantContent() + "", style, row, 11);
            rowIndex++;
        }
    }

    /**
     * 创建项目列表Excel头部header
     *
     * @param workbook
     * @param sheet
     */
    public void createProjectListHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("拓展区域", headerStyle, row, 0);
        createCell("接管区域", headerStyle, row, 1);
        createCell("项目/公司名称", headerStyle, row, 2);
        createCell("编号", headerStyle, row, 3);
        createCell("项目类型", headerStyle, row, 4);
        createCell("合作类型", headerStyle, row, 5);
        createCell("跟进人", headerStyle, row, 6);
        createCell("规划面积(万㎡)", headerStyle, row, 7);
        createCell("签约面积(万㎡)", headerStyle, row, 8);
        createCell("收费面积(万㎡)", headerStyle, row, 9);
        createCell("状态", headerStyle, row, 10);

    }

    /**
     * 创建项目列表的Excel正文体
     *
     * @param pageData
     * @param workbook
     * @param sheet
     */
    public void createProjectListBody(PageData pageData, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        List<CommData> dataList = (List<CommData>) pageData.getData();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        for (CommData data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(data.getOrganName() == null ? "" : data.getOrganName(), style, row, 0);
            createCell(data.getTakeOverOrganName() == null ? "" : data.getTakeOverOrganName(), style, row, 1);
            createCell(data.getProjectName() == null ? "" : data.getProjectName(), style, row, 2);
            createCell(data.getSerialNumber() == null ? "" : data.getSerialNumber(), style, row, 3);
            createCell(data.getProjectType() == null ? "" : data.getProjectType(), style, row, 4);
            createCell(data.getCooperationType() == null ? "" : data.getCooperationType(), style, row, 5);
            createCell(data.getContact() == null ? "" : data.getContact(), style, row, 6);
            createCell(data.getPlanningArea() == null ? "" : data.getPlanningArea() + "", style, row, 7);
            createCell(data.getContractArea() == null ? "" : data.getContractArea() + "" + "", style, row, 8);
            createCell(data.getChargeArea() == null ? "" : data.getChargeArea() + "", style, row, 9);
            createCell(data.getStatus() == null ? "" : data.getStatus() + "", style, row, 10);
            rowIndex++;
        }
    }


    /**
     * 创建案场基础信息表头
     *
     * @param workbook
     * @param sheet
     */
    public void createCaseFieldHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("月份", headerStyle, row, 1);
        createCell("区域", headerStyle, row, 2);
        createCell("项目", headerStyle, row, 3);
        createCell("案场", headerStyle, row, 4);
        createCell("顾问协议起止时间", headerStyle, row, 5);
        createCell("项目负责人", headerStyle, row, 6);
        createCell("案场负责人", headerStyle, row, 7);
        createCell("案场负责人所属岗位", headerStyle, row, 8);
        createCell("案场编制人数", headerStyle, row, 9);
        createCell("在岗人数", headerStyle, row, 10);
        createCell("展示区开放面积", headerStyle, row, 11);
        createCell("巡游/两会展示", headerStyle, row, 13);
        createCell("开盘配合", headerStyle, row, 17);
        createCell("创新服务举措", headerStyle, row, 22);
        createCell("表扬记录", headerStyle, row, 25);

        rowIndex += 1;
        row = sheet.createRow(rowIndex);
        createCell(null, headerStyle, row, 0);
        createCell(null, headerStyle, row, 1);
        createCell(null, headerStyle, row, 2);
        createCell(null, headerStyle, row, 3);
        createCell(null, headerStyle, row, 4);
        createCell(null, headerStyle, row, 5);
        createCell(null, headerStyle, row, 6);
        createCell(null, headerStyle, row, 7);
        createCell(null, headerStyle, row, 8);
        createCell(null, headerStyle, row, 9);
        createCell(null, headerStyle, row, 10);
        createCell(null, headerStyle, row, 11);

        createCell("展示次数", headerStyle, row, 12);
        createCell("人数", headerStyle, row, 13);
        createCell("巡游/两会图片", headerStyle, row, 14);

        createCell("当月开荒次数", headerStyle, row, 15);
        createCell("开荒参与人数", headerStyle, row, 16);
        createCell("当月开盘次数", headerStyle, row, 17);
        createCell("开盘支援人数", headerStyle, row, 18);
        createCell("开盘配合满意度", headerStyle, row, 19);
        createCell("开盘配合满意度扫描件", headerStyle, row, 20);

        createCell("创新服务举措", headerStyle, row, 21);
        createCell("创新服务图片", headerStyle, row, 22);

        createCell("先进事迹", headerStyle, row, 23);
        createCell("先进事迹（图片）", headerStyle, row, 24);
        createCell("表扬信封数", headerStyle, row, 25);
        createCell("锦旗面数", headerStyle, row, 26);
        createCell("其他途径表扬次数", headerStyle, row, 27);
        createCell("公众号推送", headerStyle, row, 28);
        createCell("创建人", headerStyle, row, 29);
        createCell("创建时间", headerStyle, row, 30);
        createCell("更新人", headerStyle, row, 31);
        createCell("更新时间", headerStyle, row, 32);
        createCell("审核状态", headerStyle, row, 33);
        createCell("审核意见", headerStyle, row, 34);

    }

    /**
     * 创建案场基础信息Excel正文体
     *
     * @param result
     * @param workbook
     * @param sheet
     */
    public void createCaseFieldBody(Result result, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 2;
        PageData pageData = (PageData) result.getData();
        List<Map<String, Object>> dataList = (List<Map<String, Object>>) pageData.getData();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(rowIndex - 1 + "", style, row, 0);
            createCell(data.get("monthDate") == null ? "" : String.valueOf(data.get("monthDate")), style, row, 1);
            createCell(data.get("organName") == null ? "" : String.valueOf(data.get("organName")), style, row, 2);
            createCell(data.get("commName") == null ? "" : String.valueOf(data.get("commName")), style, row, 3);
            createCell(data.get("caseFieldName") == null ? "" : String.valueOf(data.get("caseFieldName")), style, row, 4);
            String saleBeginDate = data.get("saleBeginDate") == null ? "" : String.valueOf(data.get("saleBeginDate"));
            String saleEndDate = data.get("saleEndDate") == null ? "" : String.valueOf(data.get("saleEndDate"));
            createCell(saleBeginDate + "至" + saleEndDate, style, row, 5);
            createCell(data.get("commManager") == null ? "" : String.valueOf(data.get("commManager")), style, row, 6);
            createCell(data.get("caseFieldManager") == null ? "" : String.valueOf(data.get("caseFieldManager")), style, row, 7);
            createCell(data.get("position") == null ? "" : String.valueOf(data.get("position")), style, row, 8);
            createCell(data.get("caseFieldAuthorized") == null ? "" : String.valueOf(data.get("caseFieldAuthorized")), style, row, 9);
            createCell(data.get("ondutyPeopleCount") == null ? "" : String.valueOf(data.get("ondutyPeopleCount")), style, row, 10);
            createCell(data.get("exhibitionArea") == null ? "" : String.valueOf(data.get("exhibitionArea")), style, row, 11);
            createCell(data.get("showCount") == null ? "" : String.valueOf(data.get("showCount")), style, row, 12);
            createCell(data.get("peopleCount") == null ? "" : String.valueOf(data.get("peopleCount")), style, row, 13);


            String image = data.get("image") == null ? "" : String.valueOf(data.get("image"));
            String[] images = image.split(",");
            StringBuilder imageSb = new StringBuilder();
            if (images != null) {

                for (String im : images) {
                    imageSb.append(im + "\n");
                }
                //如果只有一张图片，那么直接使用超链接，如果多张就无法使用超链接
                if (images.length == 1) {
                    Cell cellImage = row.createCell(14);
                    cellImage.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    String imagestr = String.valueOf(data.get("image"));
                    if ("".equals(data.get("image"))) {
                        cellImage.setCellValue("");
                    } else {

                        cellImage.setCellFormula("HYPERLINK(\"" + data.get("image") + "\",\"" + data.get("image") + "\")");//巡游/两会图片
                    }
                } else {

                    createCell(imageSb.toString(), style, row, 14);
                }
            } else {
                createCell("", style, row, 14);
            }

//            createCell(data.get("image") == null ? "" : String.valueOf(data.get("image")), style, row, 14);

            createCell(data.get("monthlyDevelopCount") == null ? "" : String.valueOf(data.get("monthlyDevelopCount")), style, row, 15);
            createCell(data.get("developManCount") == null ? "" : String.valueOf(data.get("developManCount")), style, row, 16);
            createCell(data.get("monthlyOpenCount") == null ? "" : String.valueOf(data.get("monthlyOpenCount")), style, row, 17);
            createCell(data.get("openHelpCount") == null ? "" : String.valueOf(data.get("openHelpCount")), style, row, 18);
            createCell(data.get("openCoordinateRate") == null ? "" : String.valueOf(data.get("openCoordinateRate")), style, row, 19);

//            Cell cellOpenCoordinateRateFiles = row.createCell(20);
//            cellOpenCoordinateRateFiles.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//            cellOpenCoordinateRateFiles.setCellFormula("HYPERLINK(\"" + data.get("openCoordinateRateFiles") + "\",\"" + data.get("openCoordinateRateFiles") + "\")");//创新服务图片

            String openCoordinateRateFiles = data.get("openCoordinateRateFiles") == null ? "" : String.valueOf(data.get("openCoordinateRateFiles"));
            String[] openCoordinateRateFilesArray = openCoordinateRateFiles.split(",");
            StringBuilder openCoordinateRateFilesSb = new StringBuilder();
            if (openCoordinateRateFilesArray != null) {

                for (String im : openCoordinateRateFilesArray) {
                    openCoordinateRateFilesSb.append(im + "\n");
                }
                if (openCoordinateRateFilesArray.length == 1) {
                    Cell cellOpenCoordinateRateFiles = row.createCell(20);
                    cellOpenCoordinateRateFiles.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    if ("".equals(data.get("openCoordinateRateFiles"))) {
                        cellOpenCoordinateRateFiles.setCellValue("");
                    } else {

                        cellOpenCoordinateRateFiles.setCellFormula("HYPERLINK(\"" + data.get("openCoordinateRateFiles") + "\",\"" + data.get("openCoordinateRateFiles") + "\")");//创新服务图片
                    }
                } else {
                    createCell(openCoordinateRateFilesSb.toString(), style, row, 20);
                }
            } else {
                createCell("", style, row, 20);
            }
//            createCell(data.get("openCoordinateRateFiles") == null ? "" : String.valueOf(data.get("openCoordinateRateFiles")), style, row, 20);

            createCell(data.get("innovationService") == null ? "" : String.valueOf(data.get("innovationService")), style, row, 21);


//            Cell cellInnovationServiceImages = row.createCell(22);
//            cellInnovationServiceImages.setCellType(HSSFCell.CELL_TYPE_FORMULA);
//            cellInnovationServiceImages.setCellFormula("HYPERLINK(\"" + data.get("innovationServiceImages") + "\",\"" + data.get("innovationServiceImages") + "\")");//创新服务图片

            String innovationServiceImages = data.get("innovationServiceImages") == null ? "" : String.valueOf(data.get("innovationServiceImages"));
            String[] innovationServiceImagesArray = null;
            if ("".equals(innovationServiceImages)) {
                innovationServiceImagesArray = null;
            } else {
                innovationServiceImagesArray = innovationServiceImages.split(",");
            }
            StringBuilder innovationServiceImagesSb = new StringBuilder();
            if (innovationServiceImagesArray != null) {
                for (String im : innovationServiceImagesArray) {
                    innovationServiceImagesSb.append(im + "\n");
                }
                if (innovationServiceImagesArray.length == 1) {
                    Cell cellInnovationServiceImages = row.createCell(22);
                    cellInnovationServiceImages.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    if ("".equals(data.get("innovationServiceImages"))) {
                        cellInnovationServiceImages.setCellValue("");
                    } else {

                        cellInnovationServiceImages.setCellFormula("HYPERLINK(\"" + data.get("innovationServiceImages")
                                + "\",\"" + data.get("innovationServiceImages") + "\")");//创新服务图片
                    }
                } else {
                    createCell(innovationServiceImagesSb.toString(), style, row, 22);
                }
            } else {
                createCell("", style, row, 22);
            }

//            createCell(data.get("innovationServiceImages") == null ? "" : String.valueOf(data.get("innovationServiceImages")), style, row, 22);

            createCell(data.get("advancedDeeds") == null ? "" : String.valueOf(data.get("advancedDeeds")), style, row, 23);

            String deedsImages = data.get("deedsImages") == null ? "" : String.valueOf(data.get("deedsImages"));
            String[] deedsImagesArray = innovationServiceImages.split(",");
            StringBuilder deedsImagesSb = new StringBuilder();

            if (deedsImagesArray != null) {

                for (String im : deedsImagesArray) {
                    deedsImagesSb.append(im + "\n");
                }
                if (deedsImagesArray.length == 1) {
                    Cell deedsImagesCell = row.createCell(24);
                    deedsImagesCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    if ("".equals(data.get("deedsImages"))) {
                        deedsImagesCell.setCellValue("");
                    } else {

                        deedsImagesCell.setCellValue(data.get("deedsImages").toString());//创新服务图片
                    }
                } else {
                    createCell(deedsImagesSb.toString(), style, row, 24);
                }
            } else {
                createCell("", style, row, 24);
            }


//            createCell(data.get("deedsImages") == null ? "" : String.valueOf(data.get("deedsImages")), style, row, 24);

            createCell(data.get("praiseLetterCount") == null ? "" : String.valueOf(data.get("praiseLetterCount")), style, row, 25);
            createCell(data.get("silkBannerCount") == null ? "" : String.valueOf(data.get("silkBannerCount")), style, row, 26);
            createCell(data.get("otherPaiseCount") == null ? "" : String.valueOf(data.get("otherPaiseCount")), style, row, 27);
            createCell(data.get("officialAccountPushCount") == null ? "" : String.valueOf(data.get("officialAccountPushCount")), style, row, 28);
            createCell(data.get("createdName") == null ? "" : String.valueOf(data.get("createdName")), style, row, 29);
            createCell(data.get("createDate") == null ? "" : String.valueOf(data.get("createDate")), style, row, 30);
            createCell(data.get("updatedName") == null ? "" : String.valueOf(data.get("updatedName")), style, row, 31);
            createCell(data.get("updateDate") == null ? "" : String.valueOf(data.get("updateDate")), style, row, 32);

            Integer as = Integer.parseInt(String.valueOf(data.get("approvalState")));
            if(as!=null) {
                if(as == 0) {
                    createCell("待审批", style, row, 33);
                }
                if(as == 1) {
                    createCell("审批未通过", style, row, 33);
                }
                if(as == 2) {
                    createCell("审批通过", style, row, 33);
                }
            }

            createCell(data.get("approvalOpinion") == null ? "" : String.valueOf(data.get("approvalOpinion")), style, row, 34);

            rowIndex++;
        }
    }

    public void createDivertStaticalHeader(List<String> list,Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);

        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("区域", headerStyle, row, 1);
        createCell("项目名", headerStyle, row, 2);

        createCell("省级", headerStyle, row, 3);
        createCell("地市级", headerStyle, row, 4);
        createCell("县区级", headerStyle, row, 5);


        createCell("首次收楼时间", headerStyle, row, 6);
        createCell("项目总规划套数", headerStyle, row, 7);
        createCell("累计已收楼", headerStyle, row, 8);
        createCell("项目编号", headerStyle, row, 9);
        createCell("财务账套", headerStyle, row, 10);
        createCell("常住率", headerStyle, row, 11);

        createCell("步梯洋房", headerStyle, row, 12);
        createCell("电梯洋房", headerStyle, row, 13);
        createCell("独立别墅", headerStyle, row, 14);
        createCell("双拼别墅", headerStyle, row, 15);
        createCell("四拼别墅", headerStyle, row, 16);
        createCell("联体别墅", headerStyle, row, 17);
        createCell("商铺", headerStyle, row, 18);
        createCell("车位", headerStyle, row, 19);
        createCell("其它", headerStyle, row, 20);
        createCell("公园", headerStyle, row, 21);
        createCell("写字楼", headerStyle, row, 22);
        createCell("景区", headerStyle, row, 23);
        createCell("学校", headerStyle, row, 24);
        createCell("公寓", headerStyle, row, 25);
        createCell("商场", headerStyle, row, 26);
        createCell("办公楼", headerStyle, row, 27);
        createCell("酒店", headerStyle, row, 28);
        createCell("工业厂房", headerStyle, row, 29);
        createCell("产业用房", headerStyle, row, 30);
        createCell("仓库", headerStyle, row, 31);
        createCell("医院", headerStyle, row, 32);
        createCell("体育馆", headerStyle, row, 33);
        createCell("图书馆", headerStyle, row, 34);
        createCell("会展中心", headerStyle, row, 35);
        createCell("停车场", headerStyle, row, 36);
        createCell("赛马场", headerStyle, row, 37);
        createCell("高尔夫球场", headerStyle, row, 38);
        createCell("汽车加油站", headerStyle, row, 39);
        createCell("飞机场", headerStyle, row, 40);
        createCell("车站", headerStyle, row, 41);
        createCell("码头", headerStyle, row, 42);
        createCell("市政道路", headerStyle, row, 43);
        createCell("桥梁", headerStyle, row, 44);
        createCell("隧道", headerStyle, row, 45);
    }

    public void createDivertStaticalBody(List<DivertStatistical> dataList,List<String> list, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        int a=1;
        for (DivertStatistical data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(a+"", style, row, 0);
            a++;
            createCell(data.getOrganName(), style, row, 1);
            createCell(data.getCommName(), style, row, 2);

            createCell(data.getProvince(), style, row, 3);
            createCell(data.getCity(), style, row, 4);
            createCell(data.getBorough(), style, row, 5);

            //转换时间
            if(data.getActualProperty()!=null) {
                SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
                data.setActualPropertys(formatter.format(data.getActualProperty()));
            }
            createCell(data.getActualPropertys(), style, row, 6);
            createCell(data.getHouseQuantity()+"", style, row, 7);
            createCell(data.getReceiptsFloor()+"", style, row, 8);
            createCell(data.getProjectNumber(), style, row, 9);
            createCell(data.getNCSystemNumber(), style, row, 10);
            if(org.springframework.util.StringUtils.hasText(data.getDwellRatio())){
                BigDecimal bigDecimal = new BigDecimal(data.getDwellRatio()).setScale(2);
                createCell(bigDecimal.toString()+"%", style, row, 11);
            }else{
                createCell(0+"%", style, row, 11);
            }

            createCell(data.getStepLadderHouse()+"", style, row, 12);

            createCell(data.getElevatorHouse()+"", style, row, 13);
            createCell(data.getAutocephalyVilla()+"", style, row, 14);
            createCell(data.getDoubleSpellVilla()+"", style, row, 15);
            createCell(data.getFourSpellVilla()+"", style, row, 16);
            createCell(data.getConjoinedVilla()+"", style, row, 17);
            createCell(data.getShop()+"", style, row, 18);
            createCell(data.getParkingSpaces()+"", style, row, 19);
            createCell(data.getOther()+"", style, row, 20);
            createCell(data.getPark()+"", style, row, 21);
            createCell(data.getWritingFloor()+"", style, row, 22);
            createCell(data.getScenicArea()+"", style, row, 23);
            createCell(data.getSchool()+"", style, row, 24);
            createCell(data.getApartment()+"", style, row, 25);
            createCell(data.getMarket()+"", style, row, 26);
            createCell(data.getOfficebuilding()+"", style, row, 27);
            createCell(data.getWineshop()+"", style, row, 28);
            createCell(data.getIndustrialPlants()+"", style, row, 29);
            createCell(data.getIndustryHouse()+"", style, row, 30);
            createCell(data.getWarehouse()+"", style, row, 31);
            createCell(data.getHospital()+"", style, row, 32);
            createCell(data.getStadium()+"", style, row, 33);
            createCell(data.getLibrary()+"", style, row, 34);
            createCell(data.getExhibitionCenter()+"", style, row, 35);
            createCell(data.getCarpark()+"", style, row, 36);
            createCell(data.getRacecourse()+"", style, row, 37);
            createCell(data.getGolfCourse()+"", style, row, 38);
            createCell(data.getCarGAS()+"", style, row, 39);
            createCell(data.getAirport()+"", style, row, 40);
            createCell(data.getBusStation()+"", style, row, 41);
            createCell(data.getPier()+"", style, row, 42);
            createCell(data.getMunicipalRoads()+"", style, row, 43);
            createCell(data.getBridge()+"", style, row, 44);
            createCell(data.getTunnel()+"", style, row, 45);
            rowIndex++;
        }
    }

    /**
     * 创建导出所有合同统计
     * @param workbook
     * @param sheet
     */
    public void createMediaResourcesHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("区域", headerStyle, row, 0);
        createCell("项目名", headerStyle, row, 1);
        createCell("媒体类型", headerStyle, row, 2);
        createCell("位置", headerStyle, row, 3);
        createCell("规格", headerStyle, row, 4);
        createCell("已出租", headerStyle, row, 5);
        createCell("内部自用数", headerStyle, row, 6);
        createCell("闲置数", headerStyle, row, 7);
        createCell("总数", headerStyle, row, 8);
    }

    public void createMediaResourcesBody(List<MediaResources> dataList, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        for (MediaResources data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(data.getOrganName(), style, row, 0);
            createCell(data.getCommName(), style, row, 1);
            createCell(data.getMediaType(), style, row, 2);
            createCell(data.getLocation(), style, row, 3);
            createCell(data.getSpecification(), style, row, 4);
            createCell(data.getWhetherRet()+"", style, row, 5);
            createCell(data.getIdleNumber()+"", style, row, 6);
            createCell(data.getInternalUseNumber()+"", style, row, 7);
            createCell(data.getTotal()+"", style, row, 8);
            rowIndex++;
        }

    }

    /**
     * 创建导出周收入统计表头
     * @param workbook
     * @param sheet
     */
    public void createWeekIncomeHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));


        createCell("区域", headerStyle, row, 0);
        createCell("项目", headerStyle, row, 1);
        createCell("财务账套", headerStyle, row,2);
        createCell("含税实收",headerStyle, row,3);
        createCell("周收入", headerStyle, row, 4);
        //createCell("周签约数", headerStyle, row, 5);

    }

    /**
     * 创建周收入表格
     *
     * @param dataList
     * @param workbook
     * @param sheet
     */
    public void createWeekIncomeBody(List<WeekIncome> dataList, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;

        for (WeekIncome income : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(income.getOrganName(), style, row, 0);    //创建区域名称
            createCell(income.getCommName(), style, row, 1);    //创建项目名称
            createCell(income.getNCSystemNumber(), style, row, 2);
            createCell(income.getReceipts() == null ? "" : income.getReceipts().toString(), style, row, 3);
            createCell(income.getWeekAmount() == null ? "" : income.getWeekAmount().toString(), style, row, 4);    //创建周收入
            createCell(income.getContractNumber() == null ? "" : income.getContractNumber().toString(), style, row, 5);    //创建周签约数
            rowIndex++;
        }

    }

    /**
     *
     * @param workbook
     * @param sheet
     */
    public void createSellDataManagementHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("区域", headerStyle, row, 1);
        createCell("楼盘", headerStyle, row, 2);
        createCell("认购套数", headerStyle, row, 3);
        createCell("认购金额(含券)", headerStyle, row, 4);
        createCell("签约套数", headerStyle, row, 5);
        createCell("签约金额(含券)", headerStyle, row, 6);
        createCell("认购时间", headerStyle, row, 7);
        createCell("签约时间", headerStyle, row, 8);

    }

    /**
     * 营销数据
     * @param list
     * @param workbook
     * @param sheet
     */
    public void createSellDataManagementBody(List<SellDataManagement> list,Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        int a=1;
        for (SellDataManagement data : list) {
            Row row = sheet.createRow(rowIndex);
            createCell(a+"", style, row, 0);
            a++;
            createCell(data.getOrganName(), style, row, 1);
            createCell(data.getCommName(), style, row, 2);
//            	  createCell(data.getRoom(), style, row, 2);
            createCell(data.getPurchasingNumber()+"", style, row, 3);
            createCell(data.getPurchasingMoney()+"", style, row, 4);
            createCell(data.getSignatureNumber()+"", style, row, 5);
            createCell(data.getSignatureMoney()+"", style, row, 6);
            //转换时间
            String b="";
            if(data.getPurchasingDate()!=null) {
                SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
                b=formatter.format(data.getPurchasingDate());
            }
            createCell(b, style, row, 7);
            //转换时间
            String c="";
            if(data.getSignatureDate()!=null) {
                SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd");
                c=formatter.format(data.getSignatureDate());
            }
            createCell(c, style, row, 8);
            //审核状态
//            	  if("0".equals(data.getVerifytype())) {
//            		 createCell("否", style, row, 9);
//            	  }else {
//            		 createCell("是", style, row, 9);
//            	  }
//            	  createCell(data.getName(), style, row, 11);
            rowIndex++;
        }
    }

    /**
     * 案场名称Excel导出头
     *
     * @param workbook
     * @param sheet
     */
    public void createCaseFieldInfoHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("区域", headerStyle, row, 1);
        createCell("项目", headerStyle, row, 2);
        createCell("案场名称", headerStyle, row, 3);
        createCell("备注", headerStyle, row, 4);

    }

    /**
     * 案场名称Excel导出体
     *
     * @param result
     * @param workbook
     * @param sheet
     */
    public void createCaseFieldInfoBody(Result result, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        PageData pageData = (PageData) result.getData();
        List<Map<String, Object>> dataList = (List<Map<String, Object>>) pageData.getData();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(rowIndex + "", style, row, 0);
            createCell(data.get("organName") == null ? "" : String.valueOf(data.get("organName")), style, row, 1);
            createCell(data.get("commName") == null ? "" : String.valueOf(data.get("commName")), style, row, 2);
            createCell(data.get("caseFieldName") == null ? "" : String.valueOf(data.get("caseFieldName")), style, row, 3);
            createCell(data.get("remark") == null ? "" : String.valueOf(data.get("remark")), style, row, 4);

            rowIndex++;
        }
    }

    /**
     * 创建管家信息Excel正文体
     * ly
     * @param result
     * @param workbook
     * @param sheet
     */
    public void createHouseKeeperInfoBody(List<HouseKeeperDTO> dataList, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 2;
        if (dataList == null || dataList.size() == 0) {
            return;
        }
        for (HouseKeeperDTO data : dataList) {
            String phoneIsfixed = null;
            if(data.getPhoneIsfixed()!=null&&data.getPhoneIsfixed()!=0) {
                if(data.getPhoneIsfixed()==1) {
                    phoneIsfixed = "私人电话";
                }else if(data.getPhoneIsfixed()==2) {
                    phoneIsfixed = "手机绑定管家岗位";
                }else if(data.getPhoneIsfixed()==3) {
                    phoneIsfixed = "不随人员变动而变化";
                }
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String joinWorkDate = "";
            if(data.getJoinworkDate()!=null){
                joinWorkDate = sdf.format(data.getJoinworkDate());
            }

            Row row = sheet.createRow(rowIndex);
            createCell(rowIndex - 1 + "", style, row, 0);
            createCell(data.getOrganName() == null ? "" : String.valueOf(data.getOrganName()), style, row, 1);
            createCell(data.getPK_ORG() == null ? "" : String.valueOf(data.getPK_ORG()), style, row, 2);
            createCell(data.getCommName() == null ? "" : String.valueOf(data.getCommName()), style, row, 3);
            createCell(data.getDevelopmentSubject() == null ? "" : String.valueOf(data.getDevelopmentSubject()), style, row, 4);
            createCell(data.getNAME() == null ? "" : String.valueOf(data.getNAME()), style, row, 5);
            createCell(data.getSEX() == 1 ? "男" :"女" , style, row, 6);
            createCell(data.getJobLv() == null ? "" : String.valueOf(data.getJobLv()), style, row, 7);
            createCell(data.getBIRTHDATE() == null ? "" : String.valueOf(data.getBIRTHDATE()), style, row, 8);
            createCell(joinWorkDate == null ? "" : joinWorkDate, style, row, 9);
            createCell(data.getJurisdictionForMonth() == null ? "无" : String.valueOf(data.getJurisdictionForMonth()), style, row, 10);
            createCell(data.getGxhs() == null ? "无" : String.valueOf(data.getGxhs()), style, row, 11);
            createCell(data.getGardenDistrict() == null ? "无" : String.valueOf(data.getGardenDistrict()), style, row,12);
            createCell(data.getHousekeeperPhone() == null ? "无" : String.valueOf(data.getHousekeeperPhone()), style, row, 13);
            createCell(data.getServiceCenterHotline() == null ? "" : String.valueOf(data.getServiceCenterHotline()), style, row, 14);
            createCell(data.getHotlineConnectionTime() == null ? "" : String.valueOf(data.getHotlineConnectionTime()), style, row, 15);
            createCell(data.getRemark() == null ? "" : String.valueOf(data.getRemark()), style, row, 16);
            rowIndex++;
        }
    }



    /**
     * 创建管家信息表头
     * ly
     * @param workbook
     * @param sheet
     */
    public void createHouseKeeperInfoHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("基本信息", headerStyle, row, 0);
        createCell("管辖苑区范围", headerStyle, row, 10);
        createCell("服务热线", headerStyle, row, 13);
        createCell("备注", headerStyle, row, 16);

        rowIndex += 1;
        row = sheet.createRow(rowIndex);
        createCell("序号", headerStyle, row, 0);
        createCell("物业区域", headerStyle, row, 1);
        createCell("已收楼项目", headerStyle, row, 2);
        createCell("对应ERP项目", headerStyle, row, 3);
        createCell("项目类型", headerStyle, row, 4);
        createCell("姓名", headerStyle, row, 5);
        createCell("性别", headerStyle, row, 6);
        createCell("岗位级别", headerStyle, row, 7);
        createCell("出生年份", headerStyle, row, 8);
        createCell("入职时间", headerStyle, row, 9);
        createCell("任职管家时长（月）", headerStyle, row, 10);
        createCell("管辖户数", headerStyle, row, 11);
        createCell("苑区（详细到楼栋）", headerStyle, row, 12);
        createCell("管家服务电话", headerStyle, row, 13);
        createCell("服务中心服务热线", headerStyle, row, 14);
        createCell("服务热线接线时间", headerStyle, row, 15);
        createCell(null, headerStyle, row, 16);


        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 10, 12));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 13, 15));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 16, 16));

    }
    
    /**
     * 校园评测Excel导出
     *
     * @param result
     * @param workbook
     * @param sheet
     */
    public void expersonnelCampuse(Workbook workbook, Sheet sheet) {
    	 int rowIndex = 0;
         CellStyle headerStyle = createHeaderStyle(workbook);
         Row row = sheet.createRow(rowIndex);
         row.setHeight((short) (300 * 2));
         createCell("用户编号", headerStyle, row, 0);
         createCell("用户姓名", headerStyle, row, 1);
         createCell("区域", headerStyle, row, 2);
         createCell("标签", headerStyle, row, 3);
         createCell("测评分数", headerStyle, row, 4);
         createCell("附件名称", headerStyle, row, 5);
         createCell("附件路径", headerStyle, row, 6);
         createCell("创建人", headerStyle, row, 7);
         createCell("创建时间", headerStyle, row, 8);
    	
    }
    
    /**
     * 校园评测Excel导出
     *
     * @param result
     * @param workbook
     * @param sheet
     */
    public void excreatnelCampuse(List<PersonnelCampusevaluationDTO> exxlspersonnelcampus,Workbook workbook, Sheet sheet) {
    	   
    	   CellStyle style = createBodyStyle(workbook);
    	   int rowIndex = 1;
    	   DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //HH表示24小时制； 
    	   String formatDate = null;
    	for (int i = 0; i < exxlspersonnelcampus.size(); i++) {
    		Row row = sheet.createRow(rowIndex);
    		 sheet.setColumnWidth(0, 20 * 256);
    		 sheet.setColumnWidth(2, 20 * 256);
    		 sheet.setColumnWidth(4, 20 * 300);
    		 sheet.setColumnWidth(5, 20 * 300);
    		 sheet.setColumnWidth(6, 20 * 300);
    		 createCell(exxlspersonnelcampus.get(i).getUserId(), style, row, 0);
    		 createCell(exxlspersonnelcampus.get(i).getPersonnelName(), style, row, 1);
    		 createCell(exxlspersonnelcampus.get(i).getRegionName(), style, row, 2);
    		 createCell(exxlspersonnelcampus.get(i).getIdentity(), style, row, 3);
    		 createCell(exxlspersonnelcampus.get(i).getMatchinglevel(), style, row, 4);
    		 createCell(exxlspersonnelcampus.get(i).getFilesame(), style, row, 5);
    		 createCell(exxlspersonnelcampus.get(i).getFiles(), style, row, 6);
    		 createCell(exxlspersonnelcampus.get(i).getCreatedName(), style, row, 7);
    		 if(exxlspersonnelcampus.get(i).getCreatedDate()!=null) {
                 formatDate = dFormat.format(exxlspersonnelcampus.get(i).getCreatedDate());  
                 }
    		 createCell(formatDate, style, row, 8);
    		 rowIndex++;    		           
		}
    }


    /**
     * 饭堂满意度调查统计Excel导出
     *
     * @param result
     * @param workbook
     * @param sheet
     */
    public void excanteworkbookscore(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (300 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("区域", headerStyle, row, 1);
        createCell("区域经理", headerStyle, row, 2);
        createCell("项目", headerStyle, row, 3);
        createCell("项目经理", headerStyle, row, 4);
        createCell("问卷名称", headerStyle, row, 5);
        createCell("调查名称", headerStyle, row, 6);
        createCell("总得分", headerStyle, row, 7);
        createCell("评估人数", headerStyle, row, 8);
        createCell("平均分", headerStyle, row, 9);
        createCell("平均排名", headerStyle, row, 10);
        createCell("问卷时间", headerStyle, row, 11);

    }

    /**
     * 饭堂满意度调查统计Excel导出
     *
     * @param result
     * @param workbook
     * @param sheet
     */
    public void excantesocxlsa(List<CanteenScorestatisticsDTO> canteensorcedto, Workbook workbook, Sheet sheet) {

        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //HH表示24小时制；
        String formatDate = null;

        for (int i = 0; i < canteensorcedto.size(); i++) {
            Row row = sheet.createRow(rowIndex);
            sheet.setColumnWidth(1, 20 * 300);
            sheet.setColumnWidth(3, 20 * 300);
            sheet.setColumnWidth(5, 20 * 300);
            sheet.setColumnWidth(6, 20 * 300);
            sheet.setColumnWidth(11, 20 * 300);
            createCell(String.valueOf(i+1), style, row, 0);
            createCell(canteensorcedto.get(i).getAreaname(), style, row, 1);
            createCell(canteensorcedto.get(i).getArname(), style, row, 2);
            createCell(canteensorcedto.get(i).getProjectname(), style, row, 3);
            createCell(canteensorcedto.get(i).getPrname(), style, row, 4);
            createCell(canteensorcedto.get(i).getRqname(), style, row, 5);
            createCell(canteensorcedto.get(i).getCiname(), style, row, 6);
            createCell(String.valueOf(canteensorcedto.get(i).getSumscore()), style, row, 7);
            createCell(String.valueOf(canteensorcedto.get(i).getSumbeing()), style, row, 8);
            createCell(String.valueOf(canteensorcedto.get(i).getAvgscore()), style, row, 9);
            createCell(String.valueOf(i+1), style, row, 10);

            if(canteensorcedto.get(i).getCreateTime()!=null) {
                formatDate = dFormat.format(canteensorcedto.get(i).getCreateTime());
            }
            createCell(formatDate,style, row, 11);
            rowIndex++;

        }
    }
    
    /**
     * 导师测评Excel导出
    *
    * @param result
    * @param workbook
    * @param sheet
    */
   public void exxlspersonnelAdvisor(List<PersonnelAdvisorDTO> exxlspersonnelAdvisor,Workbook workbook, Sheet sheet) {
   	 int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (300 * 2));
        createCell("人员编码", headerStyle, row, 0);
        createCell("姓名", headerStyle, row, 1);
        createCell("区域", headerStyle, row, 2);
        createCell("标签", headerStyle, row, 3);
		createCell("导师类型", headerStyle, row, 4);
		createCell("导师编码", headerStyle, row, 5);
		createCell("导师姓名", headerStyle, row, 6);
		createCell("部门", headerStyle, row, 7);
		createCell("岗位", headerStyle, row, 8);
        createCell("转正评估", headerStyle, row, 9);
        createCell("转正时间", headerStyle, row, 10);  
        createCell("转正评分", headerStyle, row, 11);  
        createCell("定岗评估", headerStyle, row, 12);
        createCell("定岗时间", headerStyle, row, 13);
        createCell("定岗评分", headerStyle, row, 14);  
        createCell("开始时间", headerStyle, row, 15);
        createCell("结束时间", headerStyle, row, 16);
        
        
      
        DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //HH表示24小时制； 
 	      String formatDate = null;
        
        int rowIndexpad = 1;
        CellStyle style = createBodyStyle(workbook);
        for (int j = 0; j < exxlspersonnelAdvisor.size(); j++) {
              Row rowad = sheet.createRow(rowIndexpad);
              sheet.setColumnWidth(0, 20 * 300);              
              sheet.setColumnWidth(4, 20 * 300);
              sheet.setColumnWidth(5, 20 * 300);
              sheet.setColumnWidth(6, 20 * 300);
              sheet.setColumnWidth(7, 20 * 300);
              sheet.setColumnWidth(8, 20 * 300);
              sheet.setColumnWidth(9, 20 * 300);
              sheet.setColumnWidth(10, 20 * 300);               
              sheet.setColumnWidth(12, 20 * 300);
              sheet.setColumnWidth(13, 20 * 300);             
              sheet.setColumnWidth(15, 20 * 300);
              sheet.setColumnWidth(16, 20 * 300);
              createCell(exxlspersonnelAdvisor.get(j).getUserId(), style, rowad, 0);
              createCell(exxlspersonnelAdvisor.get(j).getName(), style, rowad, 1);
              createCell(exxlspersonnelAdvisor.get(j).getRegionName(), style, rowad, 2);
              createCell(exxlspersonnelAdvisor.get(j).getIdentity(), style, rowad, 3);
              if(StringUtils.isNotBlank(exxlspersonnelAdvisor.get(j).getAdvisortype())) {
            	  createCell(exxlspersonnelAdvisor.get(j).getAdvisortype(), style, rowad, 4);
              }else {
            	  createCell("暂无", style, rowad, 4);
			  }
              if(StringUtils.isNotBlank(exxlspersonnelAdvisor.get(j).getAdvisorUserId())) {
            	  createCell(exxlspersonnelAdvisor.get(j).getAdvisorUserId(), style, rowad, 5);
              }else {
            	  createCell("暂无", style, rowad, 5);
			  }
              if (StringUtils.isNotBlank(exxlspersonnelAdvisor.get(j).getAdvisorUserName())) {
            	  createCell(exxlspersonnelAdvisor.get(j).getAdvisorUserName(), style, rowad, 6);
			  }else {
				  createCell("暂无", style, rowad, 6);
			  }
              if (StringUtils.isNotBlank(exxlspersonnelAdvisor.get(j).getDept())) {
            	  createCell(exxlspersonnelAdvisor.get(j).getDept(), style, rowad, 7);
			  }else {
				  createCell("暂无", style, rowad, 7);
			  }
              if (StringUtils.isNotBlank(exxlspersonnelAdvisor.get(j).getMentorpost())) {
            	  createCell(exxlspersonnelAdvisor.get(j).getMentorpost(), style, rowad, 8);
			  }else {
				  createCell("暂无", style, rowad, 8);
			  }
              if (StringUtils.isNotBlank(exxlspersonnelAdvisor.get(j).getPositivevaluation())) {
            	  createCell(exxlspersonnelAdvisor.get(j).getPositivevaluation(), style, rowad, 9);
			  }else {
				  createCell("暂无", style, rowad, 9);
			  }
              
              if(exxlspersonnelAdvisor.get(j).getNormalTime()!=null) {
            	  formatDate=dFormat.format(exxlspersonnelAdvisor.get(j).getNormalTime());  
            	  createCell(formatDate, style, rowad, 10);
              }else {
            	  createCell("暂无", style, rowad, 10);
			  }
             
              createCell(exxlspersonnelAdvisor.get(j).getPositivescore(), style, rowad, 11);
              
              if (StringUtils.isNotBlank(exxlspersonnelAdvisor.get(j).getFixedpostevaluation())) {
            	  createCell(exxlspersonnelAdvisor.get(j).getFixedpostevaluation(), style, rowad, 12);
			  }else {
				  createCell("暂无", style, rowad, 12);
			  } 
              
              if(exxlspersonnelAdvisor.get(j).getFixedTime()!=null) {
            	  formatDate=dFormat.format(exxlspersonnelAdvisor.get(j).getFixedTime());  
            	  createCell(formatDate, style, rowad, 13);
              }else {
            	  createCell("暂无", style, rowad, 13);
			  }
              
              createCell(exxlspersonnelAdvisor.get(j).getFixedscore(), style, rowad, 14);
              
              if(exxlspersonnelAdvisor.get(j).getStartTime()!=null) {
           	    formatDate=dFormat.format(exxlspersonnelAdvisor.get(j).getStartTime());  
           	    createCell(formatDate, style, rowad, 15);
              }else {
            	  createCell("暂无", style, rowad, 15);
              }
              if(exxlspersonnelAdvisor.get(j).getTerminalTime()!=null) {
             	    formatDate=dFormat.format(exxlspersonnelAdvisor.get(j).getTerminalTime());  
             	    createCell(formatDate, style, rowad, 16);
              }else {
            	  createCell("暂无", style, rowad, 16);
			  }
              
              rowIndexpad++;
        }
   }
   
   public void perassexport(List<ReviewjudgeGradeDTO> reviewjudgegrade,ReviewRinquDTO reviewrinqu
           ,List<ReviewQuestionnaireProblemDTO> questionoblemsex,List<ReviewScoreProblemDTO> scoreproblemexcl
           ,List<ReviewQuestionnaireProblemDTO> questionoblemswenex,List<ReviewScoreProblemDTO> scoreproblemwenexcl
           ,Workbook workbook, Sheet sheet) {

       CellStyle style = createBodyStyle(workbook);
       int rowIndex = 0;
       Row row = sheet.createRow(rowIndex);
       row.setHeight((short) (250 * 2));
       sheet.setColumnWidth(0, 20 * 256);
       sheet.setColumnWidth(1, 20 * 256);
       sheet.setColumnWidth(2, 60 * 256);

       createCell("与评估人关系", style, row, 0);
       createCell(" ", style, row, 1);
       createCell(" ", style, row, 2);

       for (int i = 0; i < reviewjudgegrade.size(); i++) {
           createCell(reviewjudgegrade.get(i).getRgname(), style, row, 3+i);
       }
       row = sheet.createRow((short) 1);
       createCell("评选时间", style, row, 0);

       for (int i = 0; i < reviewjudgegrade.size(); i++) {
           sheet.setColumnWidth(3+i, 20 * 256);
           DateFormat dFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); //HH表示24小时制；
           String formatDate = null;
           if(reviewjudgegrade.get(i).getCreateTime()!=null) {
               formatDate = dFormat.format(reviewjudgegrade.get(i).getCreateTime());
           }
           createCell(formatDate, style, row, 3+i);
       }
       row = sheet.createRow((short) 2);
       createCell("序号", style, row, 0);
       createCell("核心素质项", style, row, 1);
       createCell(reviewrinqu.getRqname(), style, row, 2);

       for (int i = 0; i < reviewjudgegrade.size(); i++) {
           createCell(reviewjudgegrade.get(i).getRjname(), style, row, 3+i);
       }


//		人员信息
       String[] judgeAry=new String[reviewjudgegrade.size()];
       for (int i = 0; i < reviewjudgegrade.size(); i++) {
           judgeAry[i]=reviewjudgegrade.get(i).getRjname();
       }

//		构建量表单选列表数据
       List<ProblemDetailDTO> problemDetailDTOS=new ArrayList<>();
       if(questionoblemsex.size()!=0) {
           for (int i = 0; i < questionoblemsex.size(); i++) {

               ProblemDetailDTO detail=new ProblemDetailDTO();

               detail.setDescribedname(questionoblemsex.get(i).getName());
               detail.setDescribed(questionoblemsex.get(i).getQuestionnaireProblemClassificationId());
               detail.setQuestionnaireId(questionoblemsex.get(i).getId()+"");
               detail.setTitleDescription(questionoblemsex.get(i).getTitleDescription());
               List<String> list = new ArrayList<>(judgeAry.length);
               for (int i1 = 0; i1 < judgeAry.length; i1++) {
                   list.add("");
               }
               detail.setAnswer(list);
               problemDetailDTOS.add(detail);
           }

//			组装量表单选数据
           for (ReviewScoreProblemDTO problem : scoreproblemexcl) {

               int judgeIndex=findAryIndex(problem.getName(),judgeAry);
               int detailIndex=findDetailAryIndex(problem.getQuestionnaireId(),problemDetailDTOS);
               ProblemDetailDTO detailDTO = problemDetailDTOS.get(detailIndex);
               if(problem.getScore()!=null) {
                   detailDTO.getAnswer().set(judgeIndex,problem.getScore()+"");

               }
           }
           int rowIndexwti = 3;
           Row rowrow;
           if(problemDetailDTOS.size()!=0) {
               for (int i = 0; i < problemDetailDTOS.size(); i++) {
                   rowrow = sheet.createRow(rowIndexwti);
                   createCell(String.valueOf(i+1), style, rowrow, 0);
                   createCell(problemDetailDTOS.get(i).getDescribedname(),style, rowrow, 1);
                   createCell(problemDetailDTOS.get(i).getTitleDescription(),style, rowrow, 2);
                   for (int j = 0; j < problemDetailDTOS.get(i).getAnswer().size(); j++) {

                       createCell(problemDetailDTOS.get(i).getAnswer().get(j),style, rowrow, 3+j);
                   }


                   rowIndexwti++;

               }
           }

       }
       List<ProblemDetailDTO> problemDetailwenDTOS=new ArrayList<>();
       if (questionoblemsex.size()!=0&&questionoblemswenex.size()!=0) {


//			构建问答列表数据
           for (int i = 0; i < questionoblemswenex.size(); i++) {

               ProblemDetailDTO detail=new ProblemDetailDTO();

               detail.setDescribedname(questionoblemswenex.get(i).getName());
               detail.setDescribed(questionoblemswenex.get(i).getQuestionnaireProblemClassificationId());
               detail.setQuestionnaireId(questionoblemswenex.get(i).getId()+"");
               detail.setTitleDescription(questionoblemswenex.get(i).getTitleDescription());
               List<String> list = new ArrayList<>(judgeAry.length);
               for (int i1 = 0; i1 < judgeAry.length; i1++) {
                   list.add("");
               }
               detail.setAnswer(list);
               problemDetailwenDTOS.add(detail);
           }
//			组装量问答数据

           for (ReviewScoreProblemDTO problem : scoreproblemwenexcl) {

               int judgeIndex=findAryIndex(problem.getName(),judgeAry);
               int detailIndex=findDetailAryIndex(problem.getQuestionnaireId(),problemDetailwenDTOS);
               ProblemDetailDTO detailDTO = problemDetailwenDTOS.get(detailIndex);
               if(problem.getAnswer()!=null) {
                   detailDTO.getAnswer().set(judgeIndex,problem.getAnswer()+"");

               }
           }
           int rowIndexwenti = problemDetailDTOS.size()+3;
           Row rowrowen;
           for (int i = 0; i < problemDetailwenDTOS.size(); i++) {
               rowrowen = sheet.createRow(rowIndexwenti);
               createCell(String.valueOf(problemDetailDTOS.size()+i+1), style, rowrowen, 0);
               createCell("问答题",style, rowrowen, 1);
               createCell(problemDetailwenDTOS.get(i).getTitleDescription(),style, rowrowen, 2);
               for (int j = 0; j < problemDetailwenDTOS.get(i).getAnswer().size(); j++) {

                   createCell(problemDetailwenDTOS.get(i).getAnswer().get(j),style, rowrowen, 3+j);
               }


               rowIndexwenti++;

           }

       }else {

//			构建问答列表数据
           for (int i = 0; i < questionoblemswenex.size(); i++) {

               ProblemDetailDTO detail=new ProblemDetailDTO();

               detail.setDescribedname(questionoblemswenex.get(i).getName());
               detail.setDescribed(questionoblemswenex.get(i).getQuestionnaireProblemClassificationId());
               detail.setQuestionnaireId(questionoblemswenex.get(i).getId()+"");
               detail.setTitleDescription(questionoblemswenex.get(i).getTitleDescription());
               List<String> list = new ArrayList<>(judgeAry.length);
               for (int i1 = 0; i1 < judgeAry.length; i1++) {
                   list.add("");
               }
               detail.setAnswer(list);
               problemDetailwenDTOS.add(detail);
           }
//				组装量问答数据

           for (ReviewScoreProblemDTO problem : scoreproblemwenexcl) {

               int judgeIndex=findAryIndex(problem.getName(),judgeAry);
               int detailIndex=findDetailAryIndex(problem.getQuestionnaireId(),problemDetailwenDTOS);
               ProblemDetailDTO detailDTO = problemDetailwenDTOS.get(detailIndex);
               if(problem.getAnswer()!=null) {
                   detailDTO.getAnswer().set(judgeIndex,problem.getAnswer()+"");

               }
           }

           int rowIndexwentiw = 3;
           Row rowrowenw;
           for (int i = 0; i < problemDetailwenDTOS.size(); i++) {
               rowrowenw = sheet.createRow(rowIndexwentiw);
               createCell(String.valueOf(i+1), style, rowrowenw, 0);
               createCell("问答题",style, rowrowenw, 1);
               createCell(problemDetailwenDTOS.get(i).getTitleDescription(),style, rowrowenw, 2);
               for (int j = 0; j < problemDetailwenDTOS.get(i).getAnswer().size(); j++) {

                   createCell(problemDetailwenDTOS.get(i).getAnswer().get(j),style, rowrowenw, 3+j);
               }


               rowIndexwentiw++;

           }

       }


   }
   
   /**
    * 270Excel导出
   *
   * @param result
   * @param workbook
   * @param sheet
   */
   public void exxlspersonnelAssessment(String personnelAssesDTOS,Workbook workbook, Sheet sheet) {
	   
	   int rowIndex = 0;
       CellStyle headerStyle = createHeaderStyle(workbook);
       Row row = sheet.createRow(rowIndex);
       row.setHeight((short) (300 * 2));
       createCell("用户编号", headerStyle, row, 0);
       createCell("用户姓名", headerStyle, row, 1);
       createCell("区域", headerStyle, row, 2);
    
       String pernnelAsse="["+personnelAssesDTOS+"]";
       String idIntegers;
       String[] idIns;
       int cind=0;
       JSONArray qclassAry =  (JSONArray) JSON.parseArray(pernnelAsse);
       for (int i = 0; i < qclassAry.size(); i++) {
    	   JSONObject classificationObj = qclassAry.getJSONObject(i);
     	    idIntegers=classificationObj.getString("rqc").substring(1,classificationObj.getString("rqc").length() -1);

    	    idIntegers=idIntegers.replace("\"", "");
    	    idIns=idIntegers.split(",");

    	   for (int c = 0; c < idIns.length; c++) {
	    	createCell(idIns[c], headerStyle, row, c+3);
	    	cind=c+3;
			}
    	   
       }
       createCell("总分", headerStyle, row, cind+1);
       createCell("评价", headerStyle, row, cind+2);
       createCell("创建人", headerStyle, row, cind+3);
       createCell("创建时间", headerStyle, row, cind+4);
       
       DateFormat dFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //HH表示24小时制； 
	      String formatDate = null;
       
       int rowIndexpad = 1;
       CellStyle style = createBodyStyle(workbook);
       String renxin;
       JSONArray qclas=null;
       Row rowad=null;
       for (int i = 0; i < qclassAry.size(); i++) {
    	   JSONObject classificationObj = qclassAry.getJSONObject(i);
    	    rowad = sheet.createRow(rowIndexpad);
    	   sheet.setColumnWidth(0, 20 * 300);              
           sheet.setColumnWidth(1, 20 * 300);
           sheet.setColumnWidth(2, 20 * 300);
           
           sheet.setColumnWidth(cind+1, 20 * 300);
           sheet.setColumnWidth(cind+2, 20 * 300);
           sheet.setColumnWidth(cind+3, 20 * 300);
           
            renxin=classificationObj.getString("data");
         
            qclas =  (JSONArray) JSON.parseArray(renxin);

       }
       for (int j = 0; j < qclas.size(); j++) {
    	   rowad = sheet.createRow(rowIndexpad);
    	   JSONObject clanObj = qclas.getJSONObject(j);
    	   createCell(clanObj.getString("userId"), style, rowad, 0);
    	   createCell(clanObj.getString("name"), style, rowad, 1);
    	   createCell(clanObj.getString("regionName"), style, rowad, 2);
    	   
    	         String avgsorce=clanObj.getString("avgsorce").substring(1, clanObj.getString("avgsorce").length()-1);
    	   
    	         avgsorce=avgsorce.replace("\"", "");
    	         String[] scravg=avgsorce.split(",");
    	         
    	         for (int i = 0; i < scravg.length; i++) {
    	        	 createCell(scravg[i], style, rowad, 3+i);
				 }
    	   
    	         createCell(clanObj.getString("score"), style, rowad, cind+1);
    	   if (StringUtils.isNoneBlank(clanObj.getString("evaluation"))) {
    		   String evaluation=clanObj.getString("evaluation").replace("<p>", "");
    	    	  evaluation=evaluation.replace("</p>", "");
    	    	   createCell(evaluation, style, rowad, cind+2);
    	    	   
		   }else {
			   createCell("", style, rowad, cind+2);
		   }
    	
    	   createCell(clanObj.getString("createdName"), style, rowad, cind+3);
    	   if(clanObj.getString("createdDate")!=null||!clanObj.getString("createdDate").equals("")) {
    		   long lt = new Long(clanObj.getString("createdDate"));
    		   Date date = new Date(lt);
        	    formatDate=dFormat.format(date);  
        	    createCell(formatDate, style, rowad, cind+4);
            }else {
       	      createCell("", style, rowad, cind+4);
		    }
    	  
    	   rowIndexpad++; 
       }
       
	   
   }

    /**
     * 创建常规管家信息Excel正文体
     * ly
     * @param workbook
     * @param sheet
     */
    public void createRoutineManagerBody(List<RoutineManager> dataList, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 4;
        if (dataList == null || dataList.size() == 0) {
            return;
        }
        for (RoutineManager data : dataList) {
            Row row = sheet.createRow(rowIndex);
//            createCell(data.getOrganName() == null ? "" : String.valueOf(data.getOrganName()), style, row, 0);
//            createCell(data.getOnjobNumber() == null  ? "" : String.valueOf(data.getOnjobNumber()), style, row, 1);
//            createCell(data.getHightManager() == null ? "" : String.valueOf(data.getHightManager()), style, row, 2);
//            createCell(data.getManager() == null ? "" : String.valueOf(data.getManager()), style, row, 3);
//            createCell(data.getDeputyManager() == null ? "" : String.valueOf(data.getDeputyManager()), style, row, 4);
//            createCell(data.getSupervisor() == null ? "" : String.valueOf(data.getSupervisor()), style, row, 5);
//            createCell(data.getDeputySupervisor() == null ? "" : String.valueOf(data.getDeputySupervisor()), style, row, 6);
//            createCell(data.getTrainee() == null ? "" : String.valueOf(data.getTrainee()), style, row, 7);
//            createCell(data.getFenghuangManager() == null ? "" : String.valueOf(data.getFenghuangManager()), style, row, 8);
//            createCell(data.getMiddleSchool() == null ? "" : String.valueOf(data.getMiddleSchool()), style, row, 9);
//            createCell(data.getHighSchool() == null ? "" : String.valueOf(data.getHighSchool()), style, row, 10);
//            createCell(data.getCollege() == null ? "" : String.valueOf(data.getCollege()), style, row,11);
//            createCell(data.getUndergraduateCollege() == null ? "" : String.valueOf(data.getUndergraduateCollege()), style, row, 12);
//            createCell(data.getAfter60() == null ? "" : String.valueOf(data.getAfter60()), style, row, 13);
//            createCell(data.getAfter70() == null ? "" : String.valueOf(data.getAfter70()), style, row, 14);
//            createCell(data.getAfter80() == null ? "" : String.valueOf(data.getAfter80()), style, row, 15);
//            createCell(data.getAfter90() == null ? "" : String.valueOf(data.getAfter90()), style, row, 16);
//            createCell(data.getAfter00() == null ? "" : String.valueOf(data.getAfter00()), style, row, 17);
//            createCell(data.getDimissionNumberMonth() == null ? "" : String.valueOf(data.getDimissionNumberMonth()), style, row, 18);
//            createCell(StringUtils.isBlank(data.getDimissionNumberTotal())? "" : String.valueOf(data.getDimissionNumberTotal()), style, row, 19);
//            createCell(StringUtils.isBlank(data.getWastageRateMonth())? "" : data.getWastageRateMonth(), style, row, 20);
//            createCell(StringUtils.isBlank(data.getWastageRateTotal())? "" : String.valueOf(data.getWastageRateTotal()), style, row, 21);

            createCell(data.getOrderNumber() == null ? "" : data.getOrderNumber(), style, row, 0);
            createCell(data.getOrganName() == null ? "" : String.valueOf(data.getOrganName()), style, row, 1);
            createCell(data.getOnjobNumber() == null  ? "" : String.valueOf(data.getOnjobNumber()), style, row, 2);
            createCell(data.getHightManager() == null ? "" : String.valueOf(data.getHightManager()), style, row, 3);
            createCell(data.getManager() == null ? "" : String.valueOf(data.getManager()), style, row, 4);
            createCell(data.getDeputyManager() == null ? "" : String.valueOf(data.getDeputyManager()), style, row, 5);
            createCell(data.getSupervisor() == null ? "" : String.valueOf(data.getSupervisor()), style, row, 6);
            createCell(data.getDeputySupervisor() == null ? "" : String.valueOf(data.getDeputySupervisor()), style, row, 7);
            createCell(data.getTrainee() == null ? "" : String.valueOf(data.getTrainee()), style, row, 8);
            createCell(data.getFenghuangManager() == null ? "" : String.valueOf(data.getFenghuangManager()), style, row, 9);
            createCell(data.getMiddleSchool() == null ? "" : String.valueOf(data.getMiddleSchool()), style, row, 10);
            createCell(data.getHighSchool() == null ? "" : String.valueOf(data.getHighSchool()), style, row, 11);
            createCell(data.getCollege() == null ? "" : String.valueOf(data.getCollege()), style, row,12);
            createCell(data.getUndergraduateCollege() == null ? "" : String.valueOf(data.getUndergraduateCollege()), style, row, 13);
            createCell(data.getAfter60() == null ? "" : String.valueOf(data.getAfter60()), style, row, 14);
            createCell(data.getAfter70() == null ? "" : String.valueOf(data.getAfter70()), style, row, 15);
            createCell(data.getAfter80() == null ? "" : String.valueOf(data.getAfter80()), style, row, 16);
            createCell(data.getAfter90() == null ? "" : String.valueOf(data.getAfter90()), style, row, 17);
            createCell(data.getAfter00() == null ? "" : String.valueOf(data.getAfter00()), style, row, 18);
            createCell(data.getDimissionNumberMonth() == null ? "" : String.valueOf(data.getDimissionNumberMonth()), style, row, 19);
            createCell(StringUtils.isBlank(data.getDimissionNumberTotal())? "" : String.valueOf(data.getDimissionNumberTotal()), style, row, 20);
            createCell(StringUtils.isBlank(data.getWastageRateMonth())? "" : data.getWastageRateMonth(), style, row, 21);
            createCell(StringUtils.isBlank(data.getWastageRateTotal())? "" : String.valueOf(data.getWastageRateTotal()), style, row, 22);
            rowIndex++;
        }
    }



    /**
     * 创建常规管家信息表头
     * ly
     * @param workbook
     * @param sheet
     * @param month
     */
    public void createRoutineManagerHeader(Workbook workbook, Sheet sheet, Integer year, Integer month) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell(year+"年"+month+"月常规管家情况明细", headerStyle, row, 0);

        rowIndex += 1;
        row = sheet.createRow(rowIndex);
        createCell("序号", headerStyle, row, 0);
        createCell("区域名称", headerStyle, row, 1);
        createCell("在职人数", headerStyle, row, 2);
        createCell("管家职级", headerStyle, row, 3);
        createCell("学历", headerStyle, row, 10);
        createCell("年龄结构", headerStyle, row, 14);
        createCell(year+"人员管控", headerStyle, row, 19);

        rowIndex += 1;
        row = sheet.createRow(rowIndex);
        createCell("null", headerStyle, row, 0);
        createCell("null", headerStyle, row, 1);
        createCell("null", headerStyle, row, 2);
        createCell("高级经理", headerStyle, row, 3);
        createCell("经理", headerStyle, row, 4);
        createCell("副经理", headerStyle, row, 5);
        createCell("主管", headerStyle, row, 6);
        createCell("副主管", headerStyle, row, 7);
        createCell("见习", headerStyle, row, 8);
        createCell("凤凰管家", headerStyle, row, 9);
        createCell("初中及以下", headerStyle, row, 10);
        createCell("高中/中专/技校", headerStyle, row, 11);
        createCell("大专", headerStyle, row, 12);
        createCell("本科及以上", headerStyle, row, 13);
        createCell("60后", headerStyle, row, 14);
        createCell("70后", headerStyle, row, 15);
        createCell("80后", headerStyle, row, 16);
        createCell("90后", headerStyle, row, 17);
        createCell("00后", headerStyle, row, 18);
        createCell("流失情况", headerStyle, row, 19);
        createCell("成才情况", headerStyle, row, 21);

        rowIndex += 1;
        row = sheet.createRow(rowIndex);
        createCell("null", headerStyle, row, 0);
        createCell("null", headerStyle, row, 1);
        createCell("null", headerStyle, row, 2);
        createCell("null", headerStyle, row, 3);
        createCell("null", headerStyle, row, 4);
        createCell("null", headerStyle, row, 5);
        createCell("null", headerStyle, row, 6);
        createCell("null", headerStyle, row, 7);
        createCell("null", headerStyle, row, 8);
        createCell("null", headerStyle, row, 9);
        createCell("null", headerStyle, row, 10);
        createCell("null", headerStyle, row, 11);
        createCell("null", headerStyle, row, 12);
        createCell("null", headerStyle, row, 13);
        createCell("null", headerStyle, row, 14);
        createCell("null", headerStyle, row, 15);
        createCell("null", headerStyle, row, 16);
        createCell("null", headerStyle, row, 17);
        createCell("null", headerStyle, row, 18);
        createCell("流失人数", headerStyle, row, 19);
        createCell("流失率", headerStyle, row, 20);
        createCell("成才人数", headerStyle, row, 21);
        createCell("成才率", headerStyle, row, 22);

//        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 22));
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(1, 3, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 9));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 13));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 18));
//        sheet.addMergedRegion(new CellRangeAddress(1, 1, 19, 22));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 19, 20));
//        sheet.addMergedRegion(new CellRangeAddress(2, 2, 21, 22));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 11, 11));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 12, 12));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 13, 13));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 14, 14));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 15, 15));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 16, 16));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 17, 17));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 18, 18));

//        合并单元格，需要调整样式的
        //        标题
        CellRangeAddress cellRangePlanNo = new CellRangeAddress(0, 0, 0, 22);
        sheet.addMergedRegion(cellRangePlanNo);
        setBorderForMergeCell(1, cellRangePlanNo, sheet, workbook);
//        人员管控
        CellRangeAddress cellRangePlanNo1 = new CellRangeAddress(1, 1, 19, 22);
        sheet.addMergedRegion(cellRangePlanNo1);
        setBorderForMergeCell(1, cellRangePlanNo1, sheet, workbook);
//        成才情况
        CellRangeAddress cellRangePlanNo2 = new CellRangeAddress(2, 2, 21, 22);
        sheet.addMergedRegion(cellRangePlanNo2);
        setBorderForMergeCell(1, cellRangePlanNo2, sheet, workbook);

    }

    /**
     * 创建管家地图首页Excel表头
     * ly
     * @param workbook
     * @param sheet
     * @param month
     */
    public void createManagerMapExcelHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("区域名称", headerStyle, row, 1);
        createCell("项目数", headerStyle, row, 2);
        createCell("已接管户数", headerStyle, row, 3);
        createCell("管家数", headerStyle, row, 4);
        createCell("人均管理户数", headerStyle, row, 5);
    }

    /**
     * 创建管家地图首页Excel正文题
     * ly
     */
    public void createManagerMapExcelBody(List<ManagerMapCache> dataList, Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 1;
        if (dataList == null || dataList.size() == 0) {
            return;
        }
        int index = 1;
        for (ManagerMapCache data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(String.valueOf(index), style, row, 0);
            createCell(data.getOrganName() == null  ? "" : String.valueOf(data.getOrganName()), style, row, 1);
            createCell(data.getCommCount() == null ? "" : String.valueOf(data.getCommCount()), style, row, 2);
            createCell(data.getManagerNumCount() == null ? "" : String.valueOf(data.getManagerNumCount()), style, row, 3);
            createCell(data.getHousekeeperCount() == null ? "" : String.valueOf(data.getHousekeeperCount()), style, row, 4);
            createCell(data.getManagerNumAvg() == null ? "" : String.valueOf(data.getManagerNumAvg()), style, row, 5);
            rowIndex++;
            index++;
        }
    }

    /**
     * 合并单元格设置边框
     * @param i
     * @param cellRangeTitle
     * @param sheet
     * @param workBook
     */
    private static void setBorderForMergeCell(int i, CellRangeAddress cellRangeTitle, Sheet sheet, Workbook workBook){
        RegionUtil.setBorderBottom(i, cellRangeTitle, sheet, workBook);
        RegionUtil.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex(), cellRangeTitle, sheet, workBook);
        RegionUtil.setBorderLeft(i, cellRangeTitle, sheet, workBook);
        RegionUtil.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex(), cellRangeTitle, sheet, workBook);
        RegionUtil.setBorderRight(i, cellRangeTitle, sheet, workBook);
        RegionUtil.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex(), cellRangeTitle, sheet, workBook);
        RegionUtil.setBorderTop(i, cellRangeTitle, sheet, workBook);
        RegionUtil.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex(), cellRangeTitle, sheet, workBook);
    }
}
