package com.crm.manager.service.impl;

import com.crm.manager.service.SalesPerformanceExcelService;
import com.crm.rely.backend.core.enums.SalesType;
import com.crm.rely.backend.core.pojo.table.SalesPerformanceInfoTable;
import com.crm.rely.backend.exception.ServiceException;
import com.crm.rely.backend.util.DateUtil;
import com.crm.rely.backend.util.FloatFormat;
import org.apache.commons.compress.utils.Lists;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.List;

@Service
public class SalesPerformanceExcelServiceImpl implements SalesPerformanceExcelService {


    @Value("${web.export-path}")
    private String exportPath;

    @Override
    public void createExcelByPerformanceId(Long id) throws ServiceException {
        //获取绩效信息

    }

    @Override
    public String createExcelByPerformanceInfo(SalesPerformanceInfoTable infoTable) throws ServiceException {
        //普通销售
        if (SalesType.COMMON_SALES.getCode().equals(infoTable.getSalesType())) {
            return createCommonSalesExcel(infoTable);
        }
        //销售经理
        else if (SalesType.SALES_MANAGER.getCode().equals(infoTable.getSalesType())) {
            return createSalesManagerExcel(infoTable);
        } else {
            return null;
        }
    }

    String createCommonSalesExcel(SalesPerformanceInfoTable infoTable) throws ServiceException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月");
        String reportYm = sdf.format(infoTable.getReportDate());
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("销售部绩效计划考评表" + reportYm);
        XSSFCellStyle titleStyle = createTitleCellStyle(wb);
        XSSFCellStyle contentStyle = createContentCellStyle(wb);

        // 行号
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        XSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 700);// 设置行高

        String title = "销售部绩效计划考评表" + reportYm;
        XSSFCell c00 = row0.createCell(0);
        c00.setCellValue(title);
        c00.setCellStyle(titleStyle);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));//标题合并单元格操作，6为总列数
        // 第二行
        XSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short) 500);
        XSSFCellStyle nameLeftHeadCellStyle = createNameLeftHeadCellStyle(wb);
        XSSFCellStyle nameRightHeadCellStyle = createNameRightHeadCellStyle(wb);
        String[] row_first = {"姓名：" + infoTable.getSalesName() + " ", "", "", "", "", "", "", " 合计（总分100分） ", "", ""};
        for (int i = 0; i < row_first.length; i++) {
            XSSFCell tempCell = row1.createCell(i);
            if (i == 0) {
                tempCell.setCellValue(row_first[i]);
                tempCell.setCellStyle(nameLeftHeadCellStyle);
            } else if (i == 7) {
                tempCell.setCellValue(row_first[i]);
                tempCell.setCellStyle(nameRightHeadCellStyle);
            } else {
                tempCell.setCellValue(row_first[i]);
            }
        }

        // 合并
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 9));

        //第三行
        XSSFRow row3 = sheet.createRow(rowNum++);
        row3.setHeight((short) 700);
        XSSFCellStyle pinkTitleCellStyle = createGrayTitleCellStyle(wb);
        String[] row_third = {"", "关键考核指标", "项目达成目标", "实施详细计划", "计划完成时间", "分值", "完成情况", "自评得分15%", "组长评分85%", "人事部评分"};
        for (int i = 0; i < row_third.length; i++) {
            XSSFCell tempCell = row3.createCell(i);
            tempCell.setCellValue(row_third[i]);
            tempCell.setCellStyle(pinkTitleCellStyle);
        }
        //评分栏目样式
        XSSFCellStyle sumCellStyle = createSumCellStyle(wb);

        List<List<String>> dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("个人指标", "月度净入金目标", String.format("%.2fUSD", infoTable.getPureDepositGoal()), "目标完成50%以上根据完成比例计分，50%以下不得分；完成达100%以上得满分", "月底", "40", String.valueOf(FloatFormat.formatFloat(infoTable.getPureDeposit())), "", "", "/"));
        dataList.add(Arrays.asList("个人指标", "月度毛入金目标", String.format("%.2fUSD", infoTable.getTotalDepositGoal()), "目标完成50%以上根据完成比例计分，50%以下不得分；完成达100%以上得满分", "月底", "20", String.valueOf(FloatFormat.formatFloat(infoTable.getTotalDeposit())), "", "", "/"));
        dataList.add(Arrays.asList("个人指标", "激活一级代理数", "2", "入金即激活", "月底", "15", "", "", "", "/"));
        dataList.add(Arrays.asList("个人指标", "品牌推广", "100%执行", "配合市场进行品牌推广，CWG公众号回复，推广，未完成者按比例得分", "月底", "10", "", "", "", "/"));

        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 5) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(3, 6, 0, 0));


        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("人事部考核", "迟到情况", "遵守公司规定", "违反公司规定一次扣0.5分", "月底", "3", "", "/", "/", ""));
        dataList.add(Arrays.asList("人事部考核", "不按规定请假", "遵守公司规定", "违反公司规定一次扣0.5分", "月底", "2", "", "/", "/", ""));

        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 5) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(7, 8, 0, 0));

        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("组长综合考评", "当月具体各方面的表现，包括意向客户的质量等情况", "按照组长以及公司要求完成各项工作", "工作能力、工作态度、人际关系、基本能力以及素质、个人品质等", "月底", "10", "", "/", "", "/"));

        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 5) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }

        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("加分项", "激活一级代理数", "超量完成部分", "根据当月实际工作日计算，超出部分5分/个（入金即激活）", "月底", "", "", "", "", "/"));

        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 5) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }


        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("合计", "", "", "", "", "100", "", "", "", ""));
        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                tempCell.setCellStyle(sumCellStyle);
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(11, 11, 0, 4));

        XSSFCellStyle signCellStyle = createSignCellStyle(wb);
        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("计划确认签字", "员工签字:                  组长签字:                    总经理签字: ", "", "", "", "", "", "", "", ""));
        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 0) {
                    tempCell.setCellStyle(contentStyle);
                } else {
                    tempCell.setCellStyle(signCellStyle);
                }

                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(12, 12, 1, 9));

        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("绩效结果确认签字", "员工签字:                 组长签字:                    人事行政经理签字:                     总经理签字:                   ", "", "", "", "", "", "", "", ""));

        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 0) {
                    tempCell.setCellStyle(contentStyle);
                } else {
                    tempCell.setCellStyle(signCellStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }

        sheet.addMergedRegion(new CellRangeAddress(13, 13, 1, 9));

        String fileName = String.format("%s_%s_%s.xls", infoTable.getSalesNo(),infoTable.getSalesName(), reportYm);
        FileOutputStream fos = null;
        try {

            fos = new FileOutputStream(exportPath + File.separator + fileName);
            wb.write(fos);
            if (fos != null) {
                fos.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return fileName;
    }

    String createSalesManagerExcel(SalesPerformanceInfoTable infoTable) throws ServiceException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月");
        String reportYm = sdf.format(infoTable.getReportDate());
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("销售经理绩效计划考核表" + reportYm);
        XSSFCellStyle titleStyle = createTitleCellStyle(wb);
        XSSFCellStyle contentStyle = createContentCellStyle(wb);

        // 行号
        int rowNum = 0;
        // 创建第一页的第一行，索引从0开始
        XSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 700);// 设置行高

        String title = "销售经理绩效计划考核表" + reportYm;
        XSSFCell c00 = row0.createCell(0);
        c00.setCellValue(title);
        c00.setCellStyle(titleStyle);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));//标题合并单元格操作，6为总列数
        // 第二行
        XSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short) 500);
        XSSFCellStyle nameLeftHeadCellStyle = createNameLeftHeadCellStyle(wb);
        XSSFCellStyle nameRightHeadCellStyle = createNameRightHeadCellStyle(wb);
        String[] row_first = {"姓名：" + infoTable.getSalesName() + " ", "", "", "", "", "", "", " 合计（总分100分） ", "", "", ""};
        for (int i = 0; i < row_first.length; i++) {
            XSSFCell tempCell = row1.createCell(i);
            if (i == 0) {
                tempCell.setCellValue(row_first[i]);
                tempCell.setCellStyle(nameLeftHeadCellStyle);
            } else if (i == 7) {
                tempCell.setCellValue(row_first[i]);
                tempCell.setCellStyle(nameRightHeadCellStyle);
            } else {
                tempCell.setCellValue(row_first[i]);
            }
        }

        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 10));

        XSSFRow row3 = sheet.createRow(rowNum++);
        row3.setHeight((short) 700);
        XSSFCellStyle pinkTitleCellStyle = createGrayTitleCellStyle(wb);
        String[] row_third = {"", "关键考核指标", "项目达成目标", "实施详细计划", "计划完成时间", "分值", "", "完成情况", "自评得分15%", "总经理评分85%", "人事部考核"};
        for (int i = 0; i < row_third.length; i++) {
            XSSFCell tempCell = row3.createCell(i);
            tempCell.setCellValue(row_third[i]);
            tempCell.setCellStyle(pinkTitleCellStyle);
        }
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 5, 6));
        //评分栏目样式
        XSSFCellStyle sumCellStyle = createSumCellStyle(wb);

        List<List<String>> dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("个人指标", "月度净入金目标", String.format("%.2fUSD", infoTable.getPureDepositGoal()), "目标完成50%以上根据完成比例计分，50%以下不得分；完成达100%以上得满分", "月底", "15", "", String.valueOf(FloatFormat.formatFloat(infoTable.getPureDeposit())), "", "", "/"));
        dataList.add(Arrays.asList("个人指标", "月度毛入金目标", String.format("%.2fUSD", infoTable.getTotalDepositGoal()), "团队月度总毛入金目标，按比例得分，完成可得满分", "月底", "10", "", String.valueOf(FloatFormat.formatFloat(infoTable.getTotalDeposit())), "", "", "/"));
        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 0 || j == 5 || j == 6) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(3, 4, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 5, 6));

        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("团队指标", "团队净入金目标", String.format("%.2fUSD", infoTable.getTeamPureDepositGoal()), "目标完成50%以上根据完成比例计分，50%以下不得分；完成达100%以上得满分", "月底", "30", "", String.valueOf(FloatFormat.formatFloat(infoTable.getTeamPureDeposit())), "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队月度毛入金目标", String.format("%.2fUSD", infoTable.getTeamTotalDepositGoal()), "团队月度总毛入金目标，按比例得分，完成可得满分", "月底", "20", "", String.valueOf(FloatFormat.formatFloat(infoTable.getTeamTotalDeposit())), "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队品牌推广", "100%执行", "配合市场进行品牌推广，CWG公众号回复推广，未完成者按比例得分", "月底", "10", "", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增代理总数", String.format("%d个", infoTable.getFreshAgentAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "100%以上（不含100%）", "4", String.valueOf(infoTable.getFreshAgentAmount()), "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增代理总数", String.format("%d个", infoTable.getFreshAgentAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "100%-90%", "3", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增代理总数", String.format("%d个", infoTable.getFreshAgentAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "90%-70%", "2", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增代理总数", String.format("%d个", infoTable.getFreshAgentAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "70%-50%", "1", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增代理总数", String.format("%d个", infoTable.getFreshAgentAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "50%以下", "0", "", "", "", "/"));

        dataList.add(Arrays.asList("团队指标", "团队新增直客总数", String.format("%d个", infoTable.getFreshCustomAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "100%以上（不含100%）", "4", String.valueOf(infoTable.getFreshCustomAmount()), "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增直客总数", String.format("%d个", infoTable.getFreshCustomAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "100%-90%", "3", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增直客总数", String.format("%d个", infoTable.getFreshCustomAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "90%-70%", "2", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增直客总数", String.format("%d个", infoTable.getFreshCustomAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "70%-50%", "1", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队新增直客总数", String.format("%d个", infoTable.getFreshCustomAmountGoal()), "根据完成比例计分（四舍五入取整）", "月底", "50%以下", "0", "", "", "", "/"));
        dataList.add(Arrays.asList("团队指标", "团队培训工作", "100%执行", "每月至少四次疑难解答，两次客户分析", "月底", "2", "", "", "", "", "/"));
        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 0 || j == 5 || j == 6) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(5, 18, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(8, 12, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(13, 17, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(18, 18, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(5, 5, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(6, 6, 5, 6));
        sheet.addMergedRegion(new CellRangeAddress(7, 7, 5, 6));

        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("人事部考核", "迟到/不按规定请假/不戴工牌/仪容仪表未按规定", "遵守公司规定", "违反公司规定一次扣0.5分", "月底", "5", "", "", "", "/", ""));

        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 0 || j == 5) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(19, 19, 5, 6));

        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("", "团队成员毛入金目标达成率", "100%", "每月团队成员都达标可得分", "月底", "5", "", "", "", "", "/"));

        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 0 || j == 5) {
                    tempCell.setCellStyle(sumCellStyle);
                } else {
                    tempCell.setCellStyle(contentStyle);
                }
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(20, 20, 5, 6));

        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("合计", "", "", "", "", "100", "", "", "", "", ""));
        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                tempCell.setCellStyle(sumCellStyle);
                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 4));
        sheet.addMergedRegion(new CellRangeAddress(21, 21, 5, 6));

        XSSFCellStyle signCellStyle = createSignCellStyle(wb);
        dataList = Lists.newArrayList();
        dataList.add(Arrays.asList("计划确认签字", "", "员工签字:                                      总经理签字: ", "", "", "", "", "", "", "", ""));
        for (List<String> list : dataList) {
            XSSFRow tempRow = sheet.createRow(rowNum++);
            tempRow.setHeight((short) 500);
            // 循环单元格填入数据
            for (int j = 0; j < list.size(); j++) {
                XSSFCell tempCell = tempRow.createCell(j);
                if (j == 0) {
                    tempCell.setCellStyle(contentStyle);
                } else {
                    tempCell.setCellStyle(signCellStyle);
                }

                tempCell.setCellValue(list.get(j));
            }
        }
        sheet.addMergedRegion(new CellRangeAddress(22, 22, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(22, 22, 2, 10));
        String fileName = String.format("%s_%s_%s.xls", infoTable.getSalesNo(),infoTable.getSalesName(), reportYm);
        FileOutputStream fos = null;
        try {

            fos = new FileOutputStream(exportPath + File.separator + fileName);
            wb.write(fos);
            if (fos != null) {
                fos.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return fileName;
    }

    private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("宋体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 20); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        return cellStyle;
    }

    private static XSSFCellStyle createNameLeftHeadCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);

        XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("宋体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    private static XSSFCellStyle createNameRightHeadCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);

        XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("宋体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    private static XSSFCellStyle createGrayTitleCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("宋体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        XSSFFont font = wb.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static XSSFCellStyle createSumCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setFontName("宋体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    private static XSSFCellStyle createSignCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.LEFT);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        XSSFFont font = wb.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        return cellStyle;
    }

    private static XSSFCellStyle createScoreCellStyle(XSSFWorkbook wb) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        XSSFFont font = wb.createFont();
        font.setColor((short) 8);
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        return cellStyle;
    }
}
