POI操作excel

package com.zte.springbootframe.util;

import com.alibaba.fastjson.JSONObject;
import com.zte.crm.ccs.core.application.ParamModelService;
import com.zte.crm.ccs.core.domain.entity.ParamModel;
import com.zte.crm.ccs.core.web.dto.*;
import com.zte.springbootframe.config.SpringContextUtil;
import com.zte.springbootframe.tools.csharpparser.CsConverter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.*;
import java.util.concurrent.Future;
import java.util.concurrent.TimeUnit;

/**
 * description: excel帮助类
 *
 * @author: 胡俊10242248
 * @date: 2018/10/23 10:17
 */
public final class ExcelHelper
{
    /**
     * description:二维表父节点名称
     */
    private static String parentHeader;
    /**
     * description:excel公式
     */
    private static FormulaEvaluator formulaEvaluator=null;
    /**
     * description:存放sheet名、参数编码及参数编码所在位置
     */
    private static Map<String,Map<String,String>> excelDataMap=null;
    /**
     * description:开始行
     */
    private static int firstRow=14;
    /**
     * description:开始列
     */
    private static int firstColumn=6;
    /**
     * description:结束列
     */
    private static int endColumn=25;
    /**
     * description:结束行
     */
    private static int lastRow;

    private static ParamModelService paramModelService= SpringContextUtil.getBean(ParamModelService.class);
    /**
     * description:单例模式
     */
    private ExcelHelper()
    {
    }

    private static class SingletonHolder
    {
        private final static ExcelHelper INSTANCE = new ExcelHelper();
    }

    public static ExcelHelper getInstance()
    {
        return SingletonHolder.INSTANCE;
    }

    /**
     * 解析Excel单元格的值
     */
    private static String parseCellValue(Cell cell)
    {

        if (null == cell)
        {
            return StringUtils.EMPTY;
        }

        int cellType = cell.getCellType();
        String retValue = StringUtils.EMPTY;
        switch (cellType)
        {
            default:
                break;
            //数字  0
            case Cell.CELL_TYPE_NUMERIC:
                if(String.valueOf(cell.getNumericCellValue()).indexOf("E")==-1){
                    retValue = String.valueOf(cell.getNumericCellValue());
                }else {
                    retValue =  new DecimalFormat("#").format(cell.getNumericCellValue());
                }
                break;
            //1
            case Cell.CELL_TYPE_STRING:
                retValue = cell.getRichStringCellValue().getString();
                break;
            //公式  2
            case Cell.CELL_TYPE_FORMULA:
                retValue = cell.getCellFormula();
                break;
            //空值  3
            case Cell.CELL_TYPE_BLANK:
                retValue = cell.getStringCellValue();
                break;
            //4
            case Cell.CELL_TYPE_BOOLEAN:
                retValue = String.valueOf(cell.getBooleanCellValue());
                break;
            //5
            case Cell.CELL_TYPE_ERROR:
                break;
        }

        return retValue.trim();
    }

    /**
     * 列名称--中转英文
     */
    private static String matchTableIOS(String columnName)
    {
        columnName=columnName.toLowerCase();
        if (columnName.contains(DatasourceTools.Constant.utfName.getState()) || "".equals(columnName) || columnName
            .contains(DatasourceTools.Constant.iosName.getState()))
        {
            columnName = "paramName";
        }
        if (columnName.contains("数值")||columnName.contains(DatasourceTools.Constant.utfValue.getState()) || columnName
            .contains(DatasourceTools.Constant.iosValue.getState()) || columnName.contains(DatasourceTools.Constant.utfResult.getState()))
        {
            columnName = "paramValue";
        }
        if (columnName.contains(DatasourceTools.Constant.utfValueDefault.getState()) || columnName
            .contains(DatasourceTools.Constant.iosValueDefault.getState()))
        {
            columnName = "paramValueDefault";
        }
        if (columnName.contains(DatasourceTools.Constant.utfRemark.getState()) || columnName
            .contains(DatasourceTools.Constant.iosRemark.getState()) || columnName
            .contains(DatasourceTools.Constant.utfDescription.getState()))
        {
            columnName = "remarkValue";
        }
        if (columnName.contains(DatasourceTools.Constant.utfFormula.getState()) || columnName
            .contains(DatasourceTools.Constant.iosFormula.getState()))
        {
            columnName = "formulaContent";
        }
        return columnName;
    }

    /**
     * 单元格添加注释
     */
    public static void addComment(XSSFWorkbook workbook, XSSFSheet sheet, Cell cell, String content)
    {
        //1.得到一个POI的工具类
        CreationHelper factory = workbook.getCreationHelper();
        //2.得到一个换图的对象
        Drawing drawing = sheet.createDrawingPatriarch();
        //3.ClientAnchor是附属在WorkSheet上的一个对象，其固定在一个单元格的左上角和右下角.
        ClientAnchor anchor = factory.createClientAnchor();
        //4.对这个单元格加上注解
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(content);
        comment.setString(str);
        cell.setCellComment(comment);
    }

    /**
     * 获取下拉框数据
     */
    private static String getDropdownText(XSSFSheet sheet, int rowNum, int column)
    {
        //获取单元格默认值
        String rowValueDefault = parseCellValue(sheet.getRow(rowNum).getCell(column));
        String excelFormula;
        //如果单元格存在公式
        if (sheet.getRow(rowNum).getCell(column).getCellType() == Cell.CELL_TYPE_FORMULA)
        {
            excelFormula = rowValueDefault;
            rowValueDefault = ExcelHelper.getCellValueFormula(sheet.getRow(rowNum).getCell(column));
            if(rowValueDefault.equals("0.0")){
                rowValueDefault=String.valueOf(formulaEvaluator.evaluate(sheet.getRow(rowNum).getCell(column)).getStringValue());
            }
            return rowValueDefault + "&" + excelFormula;
        }
        List<XSSFDataValidation> validations = sheet.getDataValidations();
        for (XSSFDataValidation validation : validations)
        {
            CellRangeAddressList addressList = validation.getRegions();
            if (null == addressList || addressList.getSize() == 0)
            {
                continue;
            }
            //获取单元格行位置
            int rowDownBox = addressList.getCellRangeAddress(0).getFirstRow();
            //获取单元格列位置
            int columnDownBox = addressList.getCellRangeAddress(0).getFirstColumn();
            if (rowNum == rowDownBox && column == columnDownBox)
            {
                DataValidationConstraint constraint = validation.getValidationConstraint();
                //获取单元格数组
                String[] strs = constraint.getExplicitListValues();
                //逗号分隔
                String rowValue = StringUtils.join(strs, "#");
                rowValue = rowValueDefault + ":" + rowValue;
                //去掉双引号
                rowValue = rowValue.replace("\"", "");
                return rowValue;
            }
        }
        return rowValueDefault;
    }

    /**
     * description:处理excel单元格公式
     *
     * @author: 胡俊10242248
     * @date: 2018/10/29 16:48
     */
    private static String getCellValueFormula(Cell cell)
    {
        if (cell == null || formulaEvaluator == null)
        {
            return null;
        }
        return String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
    }

    /**
     * 通过注释获取一维excel数据
     */
    private static String[] headNames;
    private static ArrayList<String> oneDimensionalExcelToJson(XSSFSheet sheet, ExcelCommentDto commentDTO) throws IOException
    {
        ArrayList<String> jsonString = new ArrayList<>();
        //列名称
        if(commentDTO==null){
            headNames=new String[]{"物料编码","物料描述","单位","类型","配置原则","机型代码","机型","中间报价项代码","中间报价项","基础报价项代码","基础报价项",
                    "基础报价项代号","货到现场目录单价（含税）","折扣率","折扣后货到现场单价（含税）","税率或征收率","折扣后货到现场单价（不含税）","税额(单价)","理论数量","实际数量"};
        }else {
            headNames = ExcelHelper.getColumnName(sheet, firstRow, firstColumn,endColumn);
        }
        //每行
        for (int i = firstRow; i < lastRow; i++)
        {
            int n = 0;
            //构造json
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("{");
            ExcelHelper.createJson(sheet,headNames, i,n, stringBuilder);
            //加入
            if(commentDTO==null){
                stringBuilder.append("\"").append("groupNameKey").append("\"").append(":").append("\"")
                        .append("C-S配置").append("\"");
            }else {
                stringBuilder.append("\"").append("groupNameKey").append("\"").append(":").append("\"")
                        .append(commentDTO.getAreaName()).append("\"");
            }
            stringBuilder.append("}");
            jsonString.add(stringBuilder.toString());
        }
        return jsonString;
    }

    /**
     * description:将excel转成string形式json数据
     *
     * @return: void
     * @author: 胡俊10242248
     * @date: 2018/10/22 10:20
     */
    private static void createJson(XSSFSheet sheet,String[] headNames, int i,int n, StringBuilder stringBuilder)
    {
        for (int j = firstColumn - 1; j <endColumn; j++)
        {
            String key = ExcelHelper.matchTableIOS(headNames[n++]);
            String value=ExcelHelper.isCombineCell(ExcelHelper.getCombineCell(sheet),sheet.getRow(i).getCell(j),sheet);
            if(DatasourceTools.isAllEmpty(value)){
                value = ExcelHelper.getDropdownText(sheet, i, j).replaceAll("[\\n\\r\"]", " ");
            }
            stringBuilder.append("\"").append(key).append("\"").append(":").append("\"").append(value).append("\"")
                    .append(",");
        }
    }

    private static ArrayList<String> oneDimensionalExcelToJson(XSSFSheet sheet, int firstRow ,int lastRow,int firstColumn,int endColumn ) throws IOException
    {
        ArrayList<String> jsonString = new ArrayList<>();
        headNames = ExcelHelper.getColumnName(sheet, firstRow, firstColumn,endColumn);
        //每行
        for (int i = firstRow; i < lastRow; i++)
        {
            int n = 0;
            //构造json
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.append("{");
            ExcelHelper.createJson(sheet,headNames, i,n, stringBuilder,firstColumn,endColumn);
            //加入
            stringBuilder.append("\"").append("groupNameKey").append("\"").append(":").append("\"")
                        .append("相关参数").append("\"");
            stringBuilder.append("}");
            jsonString.add(stringBuilder.toString());
        }
        return jsonString;
    }

    /**
     * description:得到sheet页内所有公式内容
     * author: 胡俊10242248
     * date: 2018/11/1 9:47
     */
    private static XSSFWorkbook workbook;
    private static XSSFSheet sheet;
    public static Map<String,String> getAllFormulaInSheet(InputStream fileInput) throws Exception{
        workbook = new XSSFWorkbook(fileInput);
        Map<String,String> map=new HashMap<>(10);
        sheet=workbook.getSheetAt(0);
        for(int i=0;i<=sheet.getLastRowNum();i++){
            Row row=sheet.getRow(i);
            if(row==null){
                continue;
            }
            for(int j = 0; j<row.getPhysicalNumberOfCells()+ DatasourceTools.ten; j++){
                if(row.getCell(j)!=null&&row.getCell(j).getCellType()==Cell.CELL_TYPE_FORMULA){
                    map.put((i+1)+"",ExcelHelper.parseCellValue(row.getCell(j)));
                }
            }
        }
        return map;
    }

    private static void createJson(XSSFSheet sheet,String[] headNames, int i,int n, StringBuilder stringBuilder,int firstColumn,int endColumn)
    {
        for (int j = firstColumn - 1; j <endColumn; j++)
        {
            String key = ExcelHelper.matchTableIOS(headNames[n++]);
            String value=ExcelHelper.isCombineCell(ExcelHelper.getCombineCell(sheet),sheet.getRow(i).getCell(j),sheet);
            if(DatasourceTools.isAllEmpty(value)){
                value = ExcelHelper.getDropdownText(sheet, i, j).replaceAll("[\\n\\r\"]", " ");
            }
            stringBuilder.append("\"").append(key).append("\"").append(":").append("\"").append(value).append("\"")
                    .append(",");
        }
    }

    /**
     * description:判断单元数据类型WidgetType
     *
     * @return: void
     * @author: 胡俊10242248
     * @date: 2018/10/22 15:58
     */
    private static  void judgeDataType(CellParamDto cellParamDto)
    {
        if(cellParamDto.getParamValue()==null){
            cellParamDto.setParamWidgetType("input");
            cellParamDto.setParamValueType("string");
        } else if (cellParamDto.getParamValue().contains(DatasourceTools.listFlag)){
            String paraValue = DatasourceTools.getStringBeforeFlag(cellParamDto.getParamValue(),":");
            String paraDefaultValue = DatasourceTools.getStringAfterFlag(cellParamDto.getParamValue(),":")
                .replace(DatasourceTools.listFlag, ",");
            paraDefaultValue= DatasourceTools.arrayToJsonString(paraDefaultValue.split(","));
            cellParamDto.setParamValue(paraValue);
            cellParamDto.setParamValueDefault(paraDefaultValue);
            if (DatasourceTools.isNumeric(paraValue)){
                cellParamDto.setParamValueType("number");
            }else
            {
                cellParamDto.setParamValueType("string");
            }
            cellParamDto.setParamWidgetType("list");
        }else if (DatasourceTools.isNumeric(DatasourceTools.getStringBeforeFlag(cellParamDto.getParamValue(),"&"))){
            cellParamDto.setParamWidgetType("input");
            cellParamDto.setParamValueType("number");
        }else {
            cellParamDto.setParamWidgetType("input");
            cellParamDto.setParamValueType("string");
        }
    }

    private static  void judgeDataType(ParamModelParamRankDto cellParamDto)
    {
        if(cellParamDto.getParamValue()==null){
            cellParamDto.setParamWidgetType("input");
            cellParamDto.setParamValueType("string");
        } else if (cellParamDto.getParamValue().contains(DatasourceTools.listFlag)){
            String paraValue = DatasourceTools.getStringBeforeFlag(cellParamDto.getParamValue(),":");
            String paraDefaultValue = DatasourceTools.getStringAfterFlag(cellParamDto.getParamValue(),":")
                    .replace(DatasourceTools.listFlag, ",");
            paraDefaultValue= DatasourceTools.arrayToJsonString(paraDefaultValue.split(","));
            cellParamDto.setParamValue(paraValue);
            cellParamDto.setParamValueDefault(paraDefaultValue);
            if (DatasourceTools.isNumeric(paraValue)){
                cellParamDto.setParamValueType("number");
            }else
            {
                cellParamDto.setParamValueType("string");
            }
            cellParamDto.setParamWidgetType("list");
        }else if (DatasourceTools.isNumeric(DatasourceTools.getStringBeforeFlag(cellParamDto.getParamValue(),"&"))){
            cellParamDto.setParamWidgetType("input");
            cellParamDto.setParamValueType("number");
        }else {
            cellParamDto.setParamWidgetType("input");
            cellParamDto.setParamValueType("string");
        }
    }

    /**
     * 通过注释获取列名
     */
    private static String[] getColumnName(XSSFSheet sheet, int firstRow, int firstColumn,int endColumn)
    {
        //列名称
        String[] headNames;
        //列标题所在行
        XSSFRow row = sheet.getRow(firstRow - 1);
        headNames = new String[row.getPhysicalNumberOfCells()];
        int j = 0;
        if (firstColumn - 1 > 0)
        {
            for (int i = firstColumn - 1; i <endColumn; i++)
            {
                //每列
                Cell cell = row.getCell(i);
                headNames[j++] = ExcelHelper.parseCellValue(cell);
            }
        }
        else
        {
            for (int i = 0; i < endColumn; i++)
            {
                Cell cell = row.getCell(i);
                headNames[i] = ExcelHelper.parseCellValue(cell);
            }
        }
        return headNames;
    }

    /**
     * 合并单元格处理,获取合并行
     * @param sheet
     * @return List<CellRangeAddress>
     */
    private static List<CellRangeAddress> getCombineCell(XSSFSheet sheet)
    {
        List<CellRangeAddress> list = new ArrayList<>();
        //获得一个 sheet 中合并单元格的数量
        int sheetMergerCount = sheet.getNumMergedRegions();
        //遍历合并单元格
        for(int i = 0; i<sheetMergerCount;i++)
        {
            //获得合并单元格加入list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     * @param listCombineCell 存放合并单元格的list
     * @param cell 需要判断的单元格
     * @param sheet sheet
     * @return String
     */
    private static String isCombineCell(List<CellRangeAddress> listCombineCell,XSSFCell cell,XSSFSheet sheet)
    {
        String cellValue = null;
        for(CellRangeAddress ca:listCombineCell)
        {
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            int firstC = ca.getFirstColumn();
            int lastC = ca.getLastColumn();
            int firstR = ca.getFirstRow();
            int lastR = ca.getLastRow();
            if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
            {
                if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
                {
                    XSSFRow fRow = sheet.getRow(firstR);
                    XSSFCell fCell = fRow.getCell(firstC);
                    cellValue = ExcelHelper.parseCellValue(fCell);
                    //获取单元格默认值
                    String excelFormula;
                    //如果单元格存在公式
                    if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)
                    {
                        excelFormula = cellValue;
                        cellValue = ExcelHelper.getCellValueFormula(cell);
                        cellValue=cellValue + "&" + excelFormula;
                    }
                    break;
                }
            }
            else
            {
                cellValue = "";
            }
        }
        return cellValue;
    }

    /**
     * description:广办模板解析
     * author: 胡俊10242248
     * date: 2019/3/12 10:25
     */
    public static List<CBOMDto> importSusParam(InputStream fileInput, ThreadService threadService) throws Exception {
        Map<String,String> map=ExcelHelper.getAllFormulaInSheet(fileInput);
        formulaEvaluator = new XSSFFormulaEvaluator(workbook);
        lastRow=sheet.getLastRowNum()+1;
        int customerRow=0;
        int paramLastRow=0;
        for(int i=0;i<lastRow;i++){
            if(sheet.getRow(i)!=null) {
                if ("参数：".equals(ExcelHelper.parseCellValue(sheet.getRow(i).getCell(0)))) {
                    customerRow = i + 2;
                }
                if ("名称".equals(ExcelHelper.parseCellValue(sheet.getRow(i).getCell(0)))) {
                    firstRow = i + 6;
                    break;
                }
            }else if(paramLastRow==0){
                paramLastRow=i;
            }
        }
        //配置参数sheet页参数编码
        Map<String,String> paramCodes=ExcelHelper.getRowNumAndParameter1(sheet,paramLastRow);
        ArrayList<String> jsons= ExcelHelper.oneDimensionalExcelToJson(sheet,customerRow,paramLastRow,1,4);
        //获取配置参数相关信息
        List<ParamModelParamRankDto> paramDtos = ExcelHelper.getConfigParams(jsons);
        ArrayList<String> jsonStr=ExcelHelper.oneDimensionalExcelToJson(sheet,null);
        //获取C-S参数信息
        List<ParamModelParamRankDto> cellParamDtos = ExcelHelper.getCSBOMDtos(map, paramCodes, jsonStr);
        List<List<ParamModelParamRankDto>> datas=new ArrayList<>();
        ExcelHelper.mergedChildNodes(cellParamDtos, datas);
        for(int i=0;i<datas.size();i++){
            for(int j=0;j<datas.get(i).size();j++){
                //C下面的机型
                List<ParamModelParamRankDto> chis=datas.get(i).get(j).getChildrenParams();
                List<ParamModelParamRankDto> childs=new ArrayList<>(chis);
                List<List<ParamModelParamRankDto>> temp=new ArrayList<>();
                ExcelHelper.mergedChildNodes(childs, temp);
                List<ParamModelParamRankDto> c=new ArrayList<>();
                for(int x=0;x<temp.size();x++){
                    c.add(temp.get(x).get(0));
                }
                datas.get(i).get(j).setChildrenParams(c);
                List<ParamModelParamRankDto> chis1=datas.get(i).get(j).getChildrenParams();
                for(int x=0;x<chis1.size();x++){
                    //中间报价项
                    List<ParamModelParamRankDto> middles=chis1.get(x).getChildrenParams();
                    List<ParamModelParamRankDto> mdls=new ArrayList<>(middles);
                    List<List<ParamModelParamRankDto>> temp1=new ArrayList<>();
                    ExcelHelper.mergedChildNodes(mdls, temp1);
                    List<ParamModelParamRankDto> m=new ArrayList<>();
                    for(int y=0;y<temp1.size();y++){
                        m.add(temp1.get(y).get(0));
                    }
                    chis1.get(x).setChildrenParams(m);
                }
            }
        }
        Future<List<CBOMDto>> futureResults=null;
        List<CBOMDto> cbomDtoList=new ArrayList<>();
        for(int i=0;i<datas.size();i++){
            //生成参数模型需要的数据
            futureResults= threadService.batchModels(paramDtos, datas, cbomDtoList, i);
        }
        cbomDtoList=futureResults.get(10,TimeUnit.SECONDS);
        return cbomDtoList;
    }

    /**
     * description:参数编码相同则合并
     * return:
     * author: 胡俊10242248
     * date: 2019/3/14 13:55
     */
    private static void mergedChildNodes(List<ParamModelParamRankDto> childs, List<List<ParamModelParamRankDto>> temp) {
        while (childs.size()>0){
            List<ParamModelParamRankDto> entities=new ArrayList<>();
            entities.add(childs.get(0));
            childs.remove(0);
            for(int x=0;x<childs.size();x++){
                if(entities.get(0).getParamCode().equals(childs.get(x).getParamCode())){
                    entities.get(0).setChildrenParams(Arrays.asList(DatasourceTools.arrayMerge(entities.get(0).getChildrenParams().toArray(new ParamModelParamRankDto[entities.get(0).getChildrenParams().size()]),
                            childs.get(x).getChildrenParams().toArray(new ParamModelParamRankDto[childs.get(x).getChildrenParams().size()]))));
                    childs.remove(x);
                    x=-1;
                }
            }
            temp.add(entities);
        }
    }

    /**
     * description:生成参数模型需要的数据格式
     * return:
     * author: 胡俊10242248
     * date: 2019/3/14 14:24
     */
    public static CBOMDto newParamModelData(List<ParamModelParamRankDto> paramDtos,
                                          List<List<ParamModelParamRankDto>> datas,
                                          int i)throws Exception {
        //CBOM
        ParamModelParamRankDto p=datas.get(i).get(0);

        List<ParamModel> paramModels=paramModelService.getCBOMDtoByModelName(p.getParamSourceId());
        //机型
        List<ParamModelParamRankDto> models=p.getChildrenParams();
        List<String> modelSourceNos=new ArrayList<>();
        List<String> modelSourceNames=new ArrayList<>();
        for(ParamModelParamRankDto pr:models){
            modelSourceNos.add(pr.getParamSourceId());
            modelSourceNames.add(pr.getParamName());
        }
        String modelSourceNo= DatasourceTools.listToStr(modelSourceNos,",");
        ParamModelReq paramModelReq=new ParamModelReq();
        ParamModelInfoDto paramModelInfo=new ParamModelInfoDto();
        paramModelReq.setOperType("0");
        paramModelInfo.setModelVersionId(0L);
        if(paramModels==null){

        }else {
            ParamModel paramModel=paramModelService.getRankOfParamModel(paramModels,false).get(0);
            paramModelReq.setOperType("1");
            paramModelInfo.setModelVersionId(paramModel.getModelVersionId());
        }
        //参数模型信息
        paramModelInfo.setModelName("C-S模型"+p.getParamSourceId()+"_"+ DateHelper.format(new Date(),DateHelper.FULL_DATE_FORMAT));
        paramModelInfo.setModelRemark(p.getParamName());
        paramModelInfo.setModelType("C003");
        paramModelInfo.setOwner("CCG");
        paramModelInfo.setSourceNo(modelSourceNo);
        paramModelInfo.setModelTypeId(2L);
        paramModelInfo.setSourceName(DatasourceTools.listToStr(modelSourceNames,","));
        //分类信息
        List<ParamModelGroupDto> paramGroupList=new ArrayList<>();
        ParamModelGroupDto pg=new ParamModelGroupDto();
        pg.setParamModelParamRankList(datas.get(i));
        pg.setGroupId(0L);
        pg.setGroupName("C-S参数");
        paramGroupList.add(pg);
        ParamModelGroupDto pg1=new ParamModelGroupDto();
        List<ParamModelParamRankDto> paramRankDtos=DatasourceTools.getAllContainSpeficElement(paramDtos,datas.get(i).get(0).getParamSourceId());
        pg1.setParamModelParamRankList(paramRankDtos);
        pg1.setGroupId(1L);
        pg1.setGroupName("相关配置参数");
        paramGroupList.add(pg1);
        //保存参数模型所需数据
        paramModelReq.setParamGroupList(paramGroupList);
        paramModelReq.setParamModelInfo(paramModelInfo);
        //保存并发布参数模型
        Long paramModelID=paramModelService.newParamModel(paramModelReq,"zh_CN");
        paramModelService.changeParamModelVersionStatus(paramModelID,"C003","");
        CBOMDto cbomDto=new CBOMDto();
        cbomDto.setModelVersionId(paramModelID);
        cbomDto.setCbomCode(p.getParamSourceId());
        cbomDto.setModelSourceNo(modelSourceNo);
        //返回CBOM及对应的模型ID
        return cbomDto;
    }

    /**
     * description:获取C-S参数信息
     * return:
     * author: 胡俊10242248
     * date: 2019/3/14 13:54
     */
    private static List<ParamModelParamRankDto> getCSBOMDtos(Map<String, String> map, Map<String, String> paramCodes, ArrayList<String> jsonStr) {
        List<ParamModelParamRankDto> cellParamDtos=new ArrayList<>();
        for(int i=0;i<jsonStr.size();i++){
            ParamModelParamRankDto cellParamDto=new ParamModelParamRankDto();
            JSONObject obj= JSONObject.parseObject(jsonStr.get(i));
            cellParamDto.setParamName(obj.get(headNames[1]).toString());
            cellParamDto.setRemarkValue(obj.get(headNames[4]).toString());
            cellParamDto.setParamCode("C"+obj.get(headNames[0]).toString());
            cellParamDto.setParamSourceId(obj.get(headNames[0]).toString());
            cellParamDto.setParamSource("CBOM");
            ExcelHelper.judgeDataType(cellParamDto);

            ParamModelParamRankDto child_0=new ParamModelParamRankDto();
            child_0.setParamCode("机型"+ DatasourceTools.specialStringConversion(obj.get(headNames[5]).toString()));
            child_0.setParamName(obj.get(headNames[6]).toString());
            child_0.setParamSource("机型");
            child_0.setParamSourceId(obj.get(headNames[5]).toString());
            ExcelHelper.judgeDataType(child_0);
            List<ParamModelParamRankDto> cells_0=new ArrayList<>();
            cells_0.add(child_0);
            cellParamDto.setChildrenParams(cells_0);

            ParamModelParamRankDto child_1=new ParamModelParamRankDto();
            child_1.setParamCode("中间"+DatasourceTools.specialStringConversion(obj.get(headNames[7]).toString()));
            child_1.setParamName(obj.get(headNames[8]).toString());
            child_1.setParamSource("中间报价项");
            child_1.setParamSourceId(obj.get(headNames[7]).toString());
            ExcelHelper.judgeDataType(child_1);
            List<ParamModelParamRankDto> cells_1=new ArrayList<>();
            cells_1.add(child_1);
            child_0.setChildrenParams(cells_1);

            ParamModelParamRankDto child_2=new ParamModelParamRankDto();
            child_2.setParamCode(child_1.getParamCode()+"基础"+DatasourceTools.specialStringConversion(obj.get(headNames[9]).toString()));
            child_2.setParamName(obj.get(headNames[10]).toString());
            child_2.setRemarkValue(obj.get(headNames[19]).toString());
            if(child_2.getRemarkValue().contains(":")){
                child_2.setRemarkValue(DatasourceTools.getStringBeforeFlag(child_2.getRemarkValue(),":"));
            }
            child_2.setParamSource("基础报价项");
            child_2.setParamSourceId(obj.get(headNames[9]).toString());
            ExcelHelper.judgeDataType(child_2);
            if(DatasourceTools.isNumeric(obj.get(headNames[18]).toString())){
                child_2.setFormulaContent("return "+cellParamDto.getParamCode()+"*"+obj.get(headNames[18]).toString());
                List<String> refCodes=new ArrayList<>();
                refCodes.add(cellParamDto.getParamCode());
                child_2.setRelParamCodes(refCodes);
            }else {
                String tempFormula=map.get(String.valueOf(i+firstRow+1));
                String[] temps=tempFormula.split(DatasourceTools.expressionPlus);
                List<String> codes=new ArrayList<>();
                for (String s:temps){
                    if(DatasourceTools.isLetterAndDigit(s)&& DatasourceTools.getLongInString(s)<500&&!DatasourceTools.checkCountChinese(s)){
                        codes.add(s);
                    }
                }
                List<String> refCodes=new ArrayList<>();
                for(int j=0;j<codes.size();j++){
                    tempFormula=tempFormula.replace(codes.get(j),
                            paramCodes.get(String.valueOf(DatasourceTools.getNumberInString(codes.get(j)))));
                    refCodes.add(paramCodes.get(String.valueOf(DatasourceTools.getNumberInString(codes.get(j)))));
                }
                tempFormula = DatasourceTools.excelFormulaToJS(tempFormula);
                child_2.setFormulaContent(tempFormula);
                child_2.setRelParamCodes(refCodes);
            }
            if(!child_2.getParamCode().contains("无对应内部SBOM")){
                List<ParamModelParamRankDto> cells_2=new ArrayList<>();
                cells_2.add(child_2);
                child_1.setChildrenParams(cells_2);
            }
            cellParamDtos.add(cellParamDto);
        }
        return cellParamDtos;
    }

    /**
     * description:获取Excel手key配置参数
     * return:
     * author: 胡俊10242248
     * date: 2019/3/14 13:42
     */
    private static List<ParamModelParamRankDto> getConfigParams(ArrayList<String> jsons) {
        List<ParamModelParamRankDto> paramDtos=new ArrayList<>();
        for(String s:jsons){
            ParamModelParamRankDto cellParamDto=new ParamModelParamRankDto();
            JSONObject obj= JSONObject.parseObject(s);
            cellParamDto.setParamCode("编码_"+ DatasourceTools.specialStringConversion(obj.get(matchTableIOS(headNames[0])).toString()+obj.get(matchTableIOS(headNames[2])).toString()));
            cellParamDto.setParamName(obj.get(matchTableIOS(headNames[2])).toString());
            cellParamDto.setParamValue(obj.get(matchTableIOS(headNames[3])).toString());
            cellParamDto.setParamSourceId(obj.get(matchTableIOS(headNames[0])).toString());
            ExcelHelper.judgeDataType(cellParamDto);
            paramDtos.add(cellParamDto);
        }
        return paramDtos;
    }

    /**
     * description:保存sheet所在行以及所在行参数名称
     *
     * author: 胡俊10242248
     * date: 2018/10/29 15:29
     */
    private static Map<String, String> getRowNumAndParameter1(XSSFSheet sheet,int lastRow)
    {
        Map<String, String> map = new HashMap<>(10);
        Cell cell;
        //得到Excel工作表的行 
        for (int i = 2; i <=lastRow; i++)
        {
            //读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null)
            {
                // 获取到列的值
                String value=ExcelHelper.isCombineCell(ExcelHelper.getCombineCell(sheet),sheet.getRow(i).getCell(0),sheet);
                map.put(row.getRowNum() + 1+"", "编码_"+DatasourceTools.specialStringConversion(value+ExcelHelper.parseCellValue(row.getCell(2))));
            }
        }
        return map;
    }

    /**
     * description:配置参数导入
     */
    public static ArrayList<InteractionParamsDto> importConfigParam(MultipartFile file) throws Exception {
        ImportResultData<CellParamDto> importResultData= EasyPoiUtil.importExcel(file,1,1, CellParamDto.class);
        List<CellParamDto> excelEntities=importResultData.getSuccessList();
        ArrayList<RepeatCode> repeatCodes=new ArrayList<>();
        for(int i=0;i<excelEntities.size();i++){
            ExcelHelper.judgeDataType(excelEntities.get(i));
            excelEntities.get(i).setParamCode(DatasourceTools.specialStringConversion("配置_"+excelEntities.get(i).getParamName()).replace("\\","_"));
            //公式转换
            if(StringUtils.isNotEmpty(excelEntities.get(i).getFormulaContent())){
                List<String> refCodes= DatasourceTools.extractMessageByRegular(excelEntities.get(i).getFormulaContent());
                String tempFormula=CsConverter.convert2Js(excelEntities.get(i).getFormulaContent());
                List<String> refParamCodes=new ArrayList<>();
                for(String s:refCodes){
                    String temp=DatasourceTools.specialStringConversion("配置_"+s).replace("\\","_");
                    refParamCodes.add(temp);
                    tempFormula=tempFormula.replace(s,temp);
                }
                excelEntities.get(i).setFormulaContent(tempFormula);
                excelEntities.get(i).setRelParamCodes(refParamCodes.toArray(new String[refParamCodes.size()]));
            }
            RepeatCode repeatCode=new RepeatCode();
            repeatCode.setParamCode(excelEntities.get(i).getParamCode());
            repeatCode.setGroupName("配置参数");
            repeatCode.setRowOrder((i+2)+"");
            repeatCodes.add(repeatCode);
        }
        List<List<CellParamDto>> datas=new ArrayList<>();
        while (excelEntities.size()>0){
            List<CellParamDto> entities=new ArrayList<>();
            entities.add(excelEntities.get(0));
            excelEntities.remove(0);
            for(int i=0;i<excelEntities.size();i++){
                if(entities.get(0).getGroupNameKey().equals(excelEntities.get(i).getGroupNameKey())){
                    entities.add(excelEntities.get(i));
                    excelEntities.remove(i);
                    i=-1;
                }
            }
            datas.add(entities);
        }
        ArrayList<InteractionParamsDto> interactionParamsDtos=new ArrayList<>();
        for(int i=0;i<datas.size();i++){
            InteractionParamsDto interactionParamsDto=new InteractionParamsDto();
            interactionParamsDto.setParamModelParamRankList(datas.get(i));
            interactionParamsDto.setGroupName(datas.get(i).get(0).getGroupNameKey());
            interactionParamsDtos.add(interactionParamsDto);
        }
        interactionParamsDtos.get(0).setRepeatCodes(repeatCodes);
        return interactionParamsDtos;
    }

    /**
     * description:客户到CBOM模板解析
     * return:
     * author: 胡俊10242248
     * date: 2019/3/22 11:25
     */
    public static List<CBOMDto> importClientParams(InputStream in) throws Exception
    {
        XSSFWorkbook wb = new XSSFWorkbook(in);
        formulaEvaluator = new XSSFFormulaEvaluator(wb);
        ArrayList<ParamModelParamRankDto> paramDtos = new ArrayList<>();
        Map<String,String> map=new HashMap<>(16);
        String sheetName = ExcelHelper.setParamCode(wb,paramDtos, map);
        for (ParamModelParamRankDto cellParamDto : paramDtos) {
            ExcelHelper.setParamFormulaContent(map, cellParamDto);
            cellParamDto.setParamValue(null);
        }
        InteractionParamsDto<ParamModelParamRankDto> interactionParamsDto=new InteractionParamsDto<>();
        interactionParamsDto.setParamModelParamRankList(paramDtos);
        interactionParamsDto.setGroupName(sheetName);

        List<CBOMDto> cbomDtoList=new ArrayList<>();
        //参数模型保存
        ParamModelReq paramModelReq=new ParamModelReq();
        ParamModelInfoDto paramModelInfo=new ParamModelInfoDto();
        paramModelReq.setOperType("0");
        paramModelInfo.setModelVersionId(0L);
        //参数模型信息
        paramModelInfo.setModelName("C-CBOM模型"+"_"+sheetName+ DateHelper.format(new Date(),DateHelper.FULL_DATE_FORMAT));
        paramModelInfo.setModelRemark(sheetName);
        paramModelInfo.setModelType("C003");
        paramModelInfo.setOwner("Client_C");
        paramModelInfo.setModelTypeId(2L);
        //分类信息
        List<ParamModelGroupDto> paramGroupList=new ArrayList<>();
        ParamModelGroupDto pg=new ParamModelGroupDto();
        pg.setParamModelParamRankList(interactionParamsDto.getParamModelParamRankList());
        pg.setGroupId(0L);
        pg.setGroupName(interactionParamsDto.getGroupName());
        paramGroupList.add(pg);
        //保存参数模型所需数据
        paramModelReq.setParamGroupList(paramGroupList);
        paramModelReq.setParamModelInfo(paramModelInfo);
        //保存并发布参数模型
        Long paramModelID=paramModelService.newParamModel(paramModelReq,"zh_CN");
        paramModelService.changeParamModelVersionStatus(paramModelID,"C003","");
        CBOMDto cbomDto=new CBOMDto();
        cbomDto.setModelVersionId(paramModelID);
        //返回CBOM及对应的模型ID
        cbomDtoList.add(cbomDto);
        return cbomDtoList;
    }

    /**
     * description:设置参数对象并保存参数编码
     * return:
     * author: 胡俊10242248
     * date: 2019/3/21 15:50
     */
    private static String setParamCode(XSSFWorkbook wb, ArrayList<ParamModelParamRankDto> paramDtos,
                                       Map<String, String> map)
    {
        XSSFSheet sheet=wb.getSheetAt(0);
        String sheetName=sheet.getSheetName();
        Row row=sheet.getRow(1);
        //保存参数编码
        for(int j=1;j<row.getPhysicalNumberOfCells();j++){
            String letter= DatasourceTools.excelColumnNumToLetter(j);
            map.put(letter,
                DatasourceTools.specialStringConversion(sheetName+"_"+letter+"_"+ExcelHelper.parseCellValue(row.getCell(j)))
                    .replace("\\","_"));
            ParamModelParamRankDto cellParamDto=new ParamModelParamRankDto();
            cellParamDto.setParamValue(ExcelHelper.getDropdownText(sheet,2,j));
            ExcelHelper.judgeDataType(cellParamDto);
            cellParamDto.setParamName(ExcelHelper.parseCellValue(row.getCell(j)));
            cellParamDto.setParamCode(map.get(letter));
            paramDtos.add(cellParamDto);
        }
        return sheetName;
    }

    /**
     * description:设置参数公式
     * return:
     * author: 胡俊10242248
     * date: 2019/3/21 15:47
     */
    private static void setParamFormulaContent(Map<String, String> map, ParamModelParamRankDto cellParamDto)
    {
        List<String> relParamCodes=new ArrayList<>();
        String tempFormula;
        //是否存在excel单元格公式
        if (cellParamDto.getParamValue()!=null&&cellParamDto.getParamValue().contains("&")) {
            String valueAndFormula = cellParamDto.getParamValue();
            tempFormula = DatasourceTools.getStringAfterFlag(valueAndFormula, "&").replace("$","");
            String[] temps=tempFormula.split(DatasourceTools.expressionPlus);
            Set<String> codes=new HashSet<>();
            for (String s:temps){
                if(DatasourceTools.isLetterAndDigit(s)&& DatasourceTools.getLongInString(s)<500){
                    codes.add(s);
                }
            }
            for(String s:codes){
                String value=map.get(DatasourceTools.getLetterInString(s));
                if(value!=null){
                    tempFormula=tempFormula.replace(s,value);
                    relParamCodes.add(value);
                }
            }
            tempFormula = DatasourceTools.excelFormulaToJS(tempFormula);
            cellParamDto.setFormulaContent(tempFormula);
            cellParamDto.setRelParamCodes(relParamCodes);
        }
    }
}