package com.thhy.materials.modules.biz.concret.service.impl;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.thhy.general.common.BasicResult;
import com.thhy.general.config.SysUserInfo;
import com.thhy.general.utils.UserInfoUtils;
import com.thhy.materials.modules.biz.concret.entity.SysSteel;
import com.thhy.materials.modules.biz.concret.entity.SysSteelRecord;
import com.thhy.materials.modules.biz.concret.entity.dto.RecordDto;
import com.thhy.materials.modules.biz.concret.entity.dto.RecordOutDto;
import com.thhy.materials.modules.biz.concret.entity.dto.SteelStatisticsDto;
import com.thhy.materials.modules.biz.concret.entity.dto.SteelStatisticsExcel;
import com.thhy.materials.modules.biz.concret.mapper.SysSteelMapper;
import com.thhy.materials.modules.biz.concret.mapper.SysSteelRecordMapper;
import com.thhy.materials.modules.biz.concret.service.SysSteelRecordService;
import com.thhy.materials.modules.biz.utils.ExcelUtil;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.*;

@Service
public class SysSteelRecordServiceImpl implements SysSteelRecordService {
    @Resource
    private SysSteelRecordMapper recordMapper;
    @Resource
    private SysSteelMapper steelMapper;

    @Override
    @Transactional
    public BasicResult recordInsert(HashMap<String, Object> values) {

        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String userId = sysUserInfo.getUserId();
        String companyId = sysUserInfo.getCompanyId();
        values.put("createUser",userId);
        values.put("companyId",companyId);
        String steelId = values.get("steelId").toString();
        Double changeStock = Double.valueOf(values.get("changeStock").toString());
        Double stocks = recordMapper.steelSelectStock(steelId);
        BigDecimal b1 = new BigDecimal(Double.toString(changeStock));
        double stock =0;
        if(stocks!=null && !stocks.equals("")&&!stocks.equals("0.0")&&Math.abs(stocks)>0.000000000001){
            BigDecimal b2 = new BigDecimal(Double.toString(stocks));
             stock = b1.add(b2).doubleValue();
        }else {
            stock=b1.doubleValue();
        }
        recordMapper.steelStockUpdate(steelId,stock);
        values.put("id","");
        recordMapper.recordInsert(values);
        return BasicResult.success("入库成功");
    }

    @Override
    public BasicResult recordList(Map<String, Object> values) {
        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String companyId = sysUserInfo.getCompanyId();
        values.put("companyId",companyId);

        Integer pageNum = Integer.valueOf(values.get("pageNum").toString());
        Integer pageSize = Integer.valueOf(values.get("pageSize").toString());
        PageHelper.startPage(pageNum,pageSize);
        List<SysSteelRecord> recordList = recordMapper.recordList(values);
        recordList.stream().forEach(record -> {
            DecimalFormat df = new DecimalFormat("#.####");
            String formattedNumber = df.format(record.getChangeStock());
            record.setChangeStock(Double.valueOf(formattedNumber));
        });
        PageInfo<SysSteelRecord> sysSteelRecordPageInfo = new PageInfo<>(recordList);
        return BasicResult.success(sysSteelRecordPageInfo);
    }

    @Override
    public BasicResult recordThingJsList(String token) {
        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo(token);
        String companyId = sysUserInfo.getCompanyId();
        List<SysSteelRecord> recordList = recordMapper.recordThingJsList(companyId);
        return BasicResult.success(recordList);
    }

    @Override
    @Transactional
    public BasicResult recordDel(String id) {
        SysSteelRecord recordInfo = recordMapper.recordInfo(id);

        Double stocks = recordMapper.steelSelectStock(recordInfo.getSteelId());
        Double changeStock = recordInfo.getChangeStock();
        BigDecimal bd1 = new BigDecimal(Double.toString(stocks));

        BigDecimal bd2 = new BigDecimal(Double.toString(changeStock));

        double stockss =0;
        if(stocks!=null && !stocks.equals("")&&!stocks.equals("0.0")&&Math.abs(stocks)>0.000000000001){
            stockss = bd1.subtract(bd2).doubleValue();
        }else {
            stockss=(0-bd2.doubleValue());
        }


        //修改实时库存
        recordMapper.updateStockSteelId(recordInfo.getSteelId(),stockss);
        recordMapper.recordDel(id);
        return BasicResult.success("删除成功");
    }

    @Override
    public BasicResult recordInfo(String id) {
      SysSteelRecord recordInfo = recordMapper.recordInfo(id);
        return BasicResult.success(recordInfo);
    }

    @Override
    public void recordExport(Map<String, Object> values, HttpServletResponse response) {
        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String companyId = sysUserInfo.getCompanyId();
        values.put("companyId",companyId);
        List<SysSteelRecord> recordList = recordMapper.recordList(values);
//        if (recordList.size()<=0){
//            response.setStatus(400);
//            return;
//        }
        List<RecordDto> recordDtos = new ArrayList<>();

        for (SysSteelRecord steelRecord : recordList){
            RecordDto recordDto = new RecordDto();
            recordDto.setCreateTime(steelRecord.getCreateTime());
            recordDto.setSupplierName(steelRecord.getSupplierName());
            recordDto.setFactoryName(steelRecord.getFactoryName());
            recordDto.setBrand(steelRecord.getBrand());
            recordDto.setSteelName(steelRecord.getSteelName());
            recordDto.setSteelModel(steelRecord.getSteelModel());
            recordDto.setChangeStock(steelRecord.getChangeStock());
            recordDto.setUnit(steelRecord.getUnit());
            recordDto.setRealName(steelRecord.getRealName());
            recordDtos.add(recordDto);
        }

        Integer stockType = Integer.valueOf(values.get("stockType").toString());

        ExcelWriter excel = new ExcelWriter();//定义excel
        if(stockType==1){
            excel.addHeaderAlias("createTime", "入库时间");
        }else {
            excel.addHeaderAlias("createTime", "质检合格时间");
        }
        excel.addHeaderAlias("supplierName", "供应商");
        excel.addHeaderAlias("factoryName", "厂家");
        excel.addHeaderAlias("brand", "品牌");
        excel.addHeaderAlias("steelName", "钢筋名称");
        excel.addHeaderAlias("steelModel", "规格型号");

        if(stockType==1){
            excel.addHeaderAlias("changeStock", "入库重量");
        }else {
            excel.addHeaderAlias("changeStock", "出库重量");
        }
        excel.addHeaderAlias("unit", "计量单位");
        if(stockType==1){
            excel.addHeaderAlias("realName", "入库人");
        }else {
            excel.addHeaderAlias("realName", "出库人");
        }
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xls");
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            excel.write(recordDtos);
            excel.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            excel.close();
        }
        IoUtil.close(out);
    }

    @Override
    public void variateExcelTemplate(HttpServletResponse response) {
        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String companyId = sysUserInfo.getCompanyId();
        List<String> supplierList = recordMapper.supplierPull(companyId);
        System.out.println("----------------------------------||||||-----------------------"+supplierList);


        if(CollectionUtils.isEmpty(supplierList)){
             BasicResult.faild("11111","该公司没有供应商","该公司没有供应商");
        }

        String[] gys = supplierList.toArray(new String[supplierList.size()]);

        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet sheet = book.createSheet();
        XSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("入库时间");
        row.createCell(1).setCellValue("车牌号");
        row.createCell(2).setCellValue("供应商");
//        row.createCell(2).setCellValue("厂家");
//        row.createCell(3).setCellValue("品牌");
        row.createCell(3).setCellValue("钢筋名称--规格型号");
        row.createCell(4).setCellValue("单位");
        row.createCell(5).setCellValue("入库重量");
        //row.createCell(6).setCellValue("计量单位");
       // row.createCell(6).setCellValue("入库人");

        //供应商
//        CellRangeAddressList regions = new CellRangeAddressList(1, 500, 2, 2);
//        DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
//        DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(gys);
//        DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
//        sheet.addValidationData(createValidation);
        //隐藏页
        Sheet hidden = book.createSheet("供应商");
        Cell cell = null;
        for (int i = 0; i < gys.length; i++) {
            String name = gys[i];
            Row row1 = hidden.createRow(i);
            cell = row1.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = book.createName();
        namedCell.setNameName("供应商");
        namedCell.setRefersToFormula("供应商" + "!$A$1:$A$" + gys.length);
        //sheet设置为隐藏
        book.setSheetHidden(book.getSheetIndex(hidden), true);
        CellRangeAddressList addressList = new CellRangeAddressList(1, 500, 2, 2);
        DataValidationHelper dvHelper = sheet.getDataValidationHelper();
        DataValidationConstraint gysList = dvHelper.createFormulaListConstraint("供应商");
        DataValidation validation = dvHelper.createValidation(gysList, addressList);
        sheet.addValidationData(validation);
       // createDropDownListWithHiddenSheet(sheet,1, 500, 1, 2,gys,book,"供应商");



        Map<String, Object> values = new HashMap<>();
        values.put("companyId",companyId);
        List<SysSteel> steels =  steelMapper.steelList(values);
        if(CollectionUtils.isEmpty(steels)){
             BasicResult.faild("11111","请先添加钢筋类型","请先添加钢筋类型");
        }
        List<String> lx = new ArrayList<>();
        steels.forEach(sysSteel -> {
            lx.add(sysSteel.getSteelName()+"-"+sysSteel.getSteelModel());
        });
        String[] gjlx = lx.toArray(new String[lx.size()]);


        //钢筋类型
        CellRangeAddressList regions1 = new CellRangeAddressList(1, 500, 3, 3);
        DataValidationHelper dataValidationHelper1 = sheet.getDataValidationHelper();
        DataValidationConstraint createExplicitListConstraint1 = dataValidationHelper1.createExplicitListConstraint(gjlx);
        DataValidation createValidation1 = dataValidationHelper1.createValidation(createExplicitListConstraint1, regions1);
        sheet.addValidationData(createValidation1);
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xls");
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            book.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(out);
        }
         BasicResult.success();
    }

    @Override
    public BasicResult variateExcel(MultipartFile file, HttpServletRequest request) {
        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();


        List<List<Object>> lists = null;//去工具类处理，返回excel中的数据
        try {
            lists = ExcelUtil.getUserListByExcel(file.getInputStream(), file.getOriginalFilename());
        } catch (Exception e) {
            e.printStackTrace();
        }
        lists.remove(0);
//      row.createCell(0).setCellValue("入库时间");
//        row.createCell(1).setCellValue("车牌号");
//        row.createCell(2).setCellValue("供应商");
////        row.createCell(2).setCellValue("厂家");
////        row.createCell(3).setCellValue("品牌");
//        row.createCell(3).setCellValue("钢筋名称--规格型号");
//        row.createCell(4).setCellValue("单位");
//        row.createCell(5).setCellValue("入库重量");

        if(lists.size()>0){
            for (List<Object> list : lists){
                HashMap<String, Object> values = new HashMap<>();
                //入库时间
                String createTime = list.get(0).toString();
                values.put("createTime",createTime);
                //车牌号
                String carName = list.get(1).toString();
                values.put("carName",carName);

                //供应商
                String supplierName = list.get(2).toString();
                String supplierId = recordMapper.supplierIdSelect(supplierName);
                values.put("supplierId",supplierId);
                values.put("stockType",1);

//                //厂家
//                String factoryName = list.get(2).toString();
//                values.put("factoryName",factoryName);
//                //品牌
//                String brand = list.get(3).toString();
//                values.put("brand",brand);
                //钢筋-规格型号
                String steel_id = list.get(3).toString();
                String[] split = steel_id.split("-");
                //钢筋类型
                String steelName = split[0];
                //钢筋型号
                String steelModel = split[1];
                SysSteel steelIds = recordMapper.steelNameAndSteelModelSelectSteelId(steelName,steelModel);
                values.put("steelId",steelIds.getSteelId());
                //入库重量
                Double changeStock = Double.valueOf(list.get(5).toString());
                values.put("changeStock",changeStock);
                //计量单位
                //入库人
                //SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
                String userId = sysUserInfo.getUserId();
                values.put("createUser",userId);
                values.put("companyId",sysUserInfo.getCompanyId());
                //入库重量
                BigDecimal b1 = new BigDecimal(Double.toString(changeStock));
                Double stocks = steelIds.getStock();
                double stock =0;
                if(stocks!=null && !stocks.equals("")&&!stocks.equals("0.0")&&Math.abs(stocks)>0.000000000001){
                    BigDecimal b2 = new BigDecimal(Double.toString(stocks));
                    stock = b1.add(b2).doubleValue();
                }else {
                    stock=b1.doubleValue();
                }

                recordMapper.steelStockUpdate(steelIds.getSteelId(),stock);
                values.put("id","");
                recordMapper.recordInsert(values);
            }
        }
        return BasicResult.success("导入成功");
    }

    @Override
    public BasicResult steelStatistics(Map<String, Object> values) {
        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String companyId = sysUserInfo.getCompanyId();
        values.put("companyId",companyId);
        String dateType = values.get("dateType").toString();
        List<SysSteel>steels = recordMapper.steelPull(values);

        String strTime = values.get("strTime").toString();
        String endTime = values.get("endTime").toString();
        if(dateType.equals("1")){
            List<String> betweenDate = getBetweenDate(strTime, endTime);
            if(steels.size()>0){
                for (SysSteel s :steels){
                    values.put("steelId",s.getSteelId());
                    List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsDay(values);
                    s.setSteelStatisticsDtos(steelStatisticsDtos);
                    s.setBetweenDate(betweenDate);
                }
            }
            //日
            return BasicResult.success(steels);
        }else if (dateType.equals("2")){//月

            List<String> monthBetweenDate = getMonthBetweenDate(strTime, endTime);
            if(steels.size()>0){
                for (SysSteel s :steels){
                    values.put("steelId",s.getSteelId());
                    List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsMonth(values);
                    s.setSteelStatisticsDtos(steelStatisticsDtos);
                    s.setBetweenDate(monthBetweenDate);
                }
            }
            return BasicResult.success(steels);
        }
        return BasicResult.faild("11111","类型错误","error");
    }

    @Override
    public BasicResult recordSum(Map<String, Object> values) {

        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String companyId = sysUserInfo.getCompanyId();
        values.put("companyId",companyId);
        String dateType = values.get("dateType").toString();

        if(dateType.equals("1")){
            //日
            List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordSumDay(values);
            return BasicResult.success(steelStatisticsDtos);
        }else if (dateType.equals("2")){//月
                    List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordSumMonth(values);
            return BasicResult.success(steelStatisticsDtos);
        }
        return BasicResult.faild("11111","类型错误","error");
    }

    @Override
    public BasicResult recordStatisticsExcel(Map<String, Object> values,HttpServletResponse response) {

        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String companyId = sysUserInfo.getCompanyId();
        values.put("companyId",companyId);
        String dateType = values.get("dateType").toString();
        List<SteelStatisticsExcel> steelStatisticsExcels = new ArrayList<>();

        if(dateType.equals("1")){
            //日
            List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsDay(values);
            for (SteelStatisticsDto sd : steelStatisticsDtos){
                SteelStatisticsExcel sse = new SteelStatisticsExcel();
                sse.setSteelName(sd.getSteelName());
                sse.setSteelModel(sd.getSteelModel());
                sse.setGetDate(sd.getGetDate());
                sse.setChangeStock(sd.getChangeStock());
                steelStatisticsExcels.add(sse);
            }

        }else if (dateType.equals("2")){//月
            List<SteelStatisticsDto> steelStatisticsDtos = recordMapper.recordStatisticsMonth(values);
            for (SteelStatisticsDto sd : steelStatisticsDtos){
                SteelStatisticsExcel sse = new SteelStatisticsExcel();
                sse.setSteelName(sd.getSteelName());
                sse.setSteelModel(sd.getSteelModel());
                sse.setGetDate(sd.getGetDate());
                sse.setChangeStock(sd.getChangeStock());
                steelStatisticsExcels.add(sse);
            }
        }
        ExcelWriter excel = new ExcelWriter();//定义excel
        excel.addHeaderAlias("steelName", "钢筋名称");
        excel.addHeaderAlias("steelModel", "规格型号");
        excel.addHeaderAlias("getDate", "日期");
        excel.addHeaderAlias("changeStock", "数量");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xls");
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            excel.write(steelStatisticsExcels);
            excel.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            excel.close();
        }
        IoUtil.close(out);
        return BasicResult.success();
    }

    @Override
    public void recordOutExport(Map<String, Object> values, HttpServletResponse response) {
        SysUserInfo sysUserInfo = UserInfoUtils.getInstance().getUserInfo();
        String companyId = sysUserInfo.getCompanyId();
        values.put("companyId",companyId);
        List<SysSteelRecord> recordList = recordMapper.recordList(values);
//        if (recordList.size()<=0){
//            response.setStatus(400);
//            return;
//        }
        List<RecordOutDto> recordDtos = new ArrayList<>();

        for (SysSteelRecord steelRecord : recordList){
            RecordOutDto recordDto = new RecordOutDto();
            recordDto.setCreateTime(steelRecord.getCreateTime());
            recordDto.setSteelName(steelRecord.getSteelName());
            recordDto.setSteelModel(steelRecord.getSteelModel());
            recordDto.setChangeStock(steelRecord.getChangeStock());
            recordDto.setUnit(steelRecord.getUnit());
            recordDto.setLineCode(steelRecord.getLineCode());
            recordDto.setProName(steelRecord.getProName());
            recordDtos.add(recordDto);
        }

        Integer stockType = Integer.valueOf(values.get("stockType").toString());

        ExcelWriter excel = new ExcelWriter();//定义excel
        if(stockType==1){
            excel.addHeaderAlias("createTime", "入库时间");
        }else {
            excel.addHeaderAlias("createTime", "质检合格时间");
        }
        excel.addHeaderAlias("steelName", "钢筋名称");
        excel.addHeaderAlias("steelModel", "规格型号");
        excel.addHeaderAlias("unit", "计量单位");

        if(stockType==1){
            excel.addHeaderAlias("changeStock", "入库重量");
        }else {
            excel.addHeaderAlias("changeStock", "出库重量");
        }
        excel.addHeaderAlias("lineCode", "钢筋笼编号");
        excel.addHeaderAlias("proName", "项目名称");


        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xls");
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            excel.write(recordDtos);
            excel.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 关闭writer，释放内存
            excel.close();
        }
        IoUtil.close(out);
    }

    public static List<String> getBetweenDate(String startTime, String endTime){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        // 声明保存日期集合
        List<String> list = new ArrayList<String>();
        try {
            // 转化成日期类型
            Date startDate = sdf.parse(startTime);
            Date endDate = sdf.parse(endTime);

            //用Calendar 进行日期比较判断
            Calendar calendar = Calendar.getInstance();
            while (startDate.getTime()<=endDate.getTime()){
                // 把日期添加到集合
                list.add(sdf.format(startDate));
                // 设置日期
                calendar.setTime(startDate);
                //把日期增加一天
                calendar.add(Calendar.DATE, 1);
                // 获取增加后的日期
                startDate=calendar.getTime();
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取两个日期之间的所有月份 (年月)
     *
     * @param startTime
     * @param endTime
     * @return：list
     */
    public static List<String> getMonthBetweenDate(String startTime, String endTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
        // 声明保存日期集合
        List<String> list = new ArrayList<>();
        try {
            // 转化成日期类型
            Date startDate = sdf.parse(startTime);
            Date endDate = sdf.parse(endTime);

            //用Calendar 进行日期比较判断
            Calendar calendar = Calendar.getInstance();
            while (startDate.getTime() <= endDate.getTime()) {

                // 把日期添加到集合
                list.add(sdf.format(startDate));

                // 设置日期
                calendar.setTime(startDate);

                //把月数增加 1
                calendar.add(Calendar.MONTH, 1);

                // 获取增加后的日期
                startDate = calendar.getTime();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    /**
     * 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)
     *
     * @return
     */
    public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,
                                                  int firstCol, int endRow,
                                                  int endCol, String[] dataArray,
                                                  Workbook wbCreat,
                                                  String hidddenSheetName) {
        Sheet hidden = wbCreat.createSheet(hidddenSheetName);
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }
        Name namedCell = wbCreat.createName();
        namedCell.setNameName(hidddenSheetName);
        namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);
        //sheet设置为隐藏
        wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
        //加载数据,将名称为hidden的
        DataValidationConstraint constraint = null;
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
        // 创建 DataValidation
        DataValidation validation = null;
        if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
            validation = dvHelper.createValidation(constraint, addressList);
        } else {
            constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
            validation = new HSSFDataValidation(addressList, constraint);
        }
        if (validation instanceof HSSFDataValidation ) {
            validation .setSuppressDropDownArrow(false);
        } else {
            validation .setSuppressDropDownArrow(true);
            validation .setShowErrorBox(true);
        }
        sheet.addValidationData(validation);
    }




}