导入，将错误数据保存文件

ApiOperation(value = "通过excel导入数据", notes = "通过excel导入数据")
    @RequestMapping(value = "/importExcelNew", method = RequestMethod.POST)
    @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
    public Result<?> importExcelNew(HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();

        //导入数据
        List<Category> temp = new ArrayList();
        //记录要删除的行数
        ArrayList<Integer> rowsNum = new ArrayList<>();
        //记录要移动的行数
        ArrayList<Integer> rowsNumZC = new ArrayList<>();
        
        HashMap<String, Integer> categoryMap = new HashMap<>();

        HashMap<String, String> locationNameMapList = ExcelTemplateUtil.getLocationNameMapList(inStockService);         //查询所有

        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            InputStream inputStream = file.getInputStream();
            //导入表格
            HSSFWorkbook sheets = ExcelTemplateUtil.getimportExcel(inputStream);
            Sheet rows = sheets.getSheetAt(0);
			//校验导入表格
            for (Row row : rows) {
                if (row.getRowNum() == 0) {
                    Cell cell = row.getCell(0);//获取第一个单元格
                    String stringCellValue = cell.getStringCellValue();
                    if ("xxxxx信息".equals(stringCellValue) || "Inbound Material Details".equals(stringCellValue)) {
                        continue;
                    }
                    return Result.error("导入的模板不符合,请下载模板重新导入!");
                }
            }
            //判断文档中某数据出现次数
            for (Row r : rows) {
                if (r.getRowNum() <= 2) {
                    continue;
                }
                String key = r.getCell(0).getStringCellValue();
                if (categoryMap.containsKey(key)){
                    Integer num = categoryMap.get(key);
                    categoryMap.put(key,num+1);
                }else {
                    categoryMap.put(key,1);
                }
            }

            //对Sheet中的每一行进行迭代
            boolean flagAllData = false;
            boolean flag = false;
            Cell cell = null;
            for (Row r : rows) {
                flag = false;
                String errMsg = "";
				// 表头标题添加列
                if (r.getRowNum() == 2) {
                    //错误原因
                    cell = r.createCell(7);
                    cell.setCellValue("错误原因");//设置单元格类型
                    continue;
                }
                //如果当前行的行号（从0开始）未达到4（第三行）则从新循环
                if (r.getRowNum() <= 2) {
                    continue;
                }
                //判断当前行是否为空
                if (ExcelTemplateUtil.isRowEmpty(r)) {
                    continue;
                }
				//每行数据
                Category category = new Category();
                //物料编码
                cell = r.getCell(0);
                String key = cell.getStringCellValue();
                if (categoryMap.get(key)>1){
                    log.error("导入数据存在相同数据");
                    errMsg += "导入数据存在相同数据";
                    flag = true;
                    flagAllData = true;
                }
                if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
                    cell.setCellType(CellType.STRING);//设置单元格类型
                    Category category1 = categoryMapper.selectByCode(cell.getStringCellValue().trim());
                    if (category1 == null) {
                        log.error("物料编码查询不到对应信息;");
                        errMsg += "物料编码查询不到对应信息;";
                        flag = true;
                        flagAllData = true;
                    }else{
                        category.setCategoryId(category1.getId());
                        category.setCode(category1.getCode());
                        category.setTeileNumber(category1.getTeileNumber()); //导出专用字段
                        category.setEngName(category1.getEngName()); //导出专用字段
                        category.setChsName(category1.getChsName()); //导出专用字段
                        category.setPosNumber(category1.getPosNumber()); //导出专用字段
                        category.setMaterial(category1.getMaterial()); //导出专用字段
                        category.setIsValuables(category1.getIsValuables()); //导出专用字段
                        category.setTeileSet(category1.getTeileSet()); //导出专用字段
                    }
                } else {
                    log.error("物料编码不允许为空;");
                    errMsg += "物料编码不允许为空;";
                    flag = true;
                    flagAllData = true;
                }
                //货位
                cell = r.getCell(1);
                if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
                    cell.setCellType(CellType.STRING);//设置单元格类型
                    category.setLocationName(cell.getStringCellValue());
                    category.setLocationId(locationNameMapList.get(cell.getStringCellValue()));
                } else {
                    log.error("货位不允许为空;");
                    errMsg += "货位不允许为空;";
                    flag = true;
                    flagAllData = true;
                }
                //入库数量
                cell = r.getCell(2);
                if (Objects.nonNull(cell) && Objects.nonNull(cell.getNumericCellValue())) {
                    cell.setCellType(CellType.NUMERIC);//设置单元格类型
                    category.setQuantity((int) cell.getNumericCellValue());
                }
                //价格(美元)
                cell = r.getCell(3);
                if (Objects.nonNull(cell) && Objects.nonNull(cell.getNumericCellValue())) {
                    cell.setCellType(CellType.NUMERIC);//设置单元格类型
                    category.setPrice(BigDecimal.valueOf(cell.getNumericCellValue()).setScale(2, RoundingMode.HALF_UP));
                }
                //供应类别
                cell = r.getCell(4);
                if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
                    cell.setCellType(CellType.STRING);//设置单元格类型
                    //翻译  名称-》值
                    category.setPurchaseType_dictText(cell.getStringCellValue());
                    category.setPurchaseType(purchaseType.get(cell.getStringCellValue()));
                }
                //供应商名称
                cell = r.getCell(5);
                if (Objects.nonNull(cell) && Objects.nonNull(cell.getStringCellValue()) && !("".equals(cell.getStringCellValue()))) {
                    cell.setCellType(CellType.STRING);//设置单元格类型
                    category.setSupplierName(cell.getStringCellValue());
                    category.setSupplierId(supplierMapList.get(cell.getStringCellValue()));
                }
                //生产时间
                cell = r.getCell(6);
                if (Objects.nonNull(cell) && Objects.nonNull(getStringCellValue(cell))) {
                    try {
                        String date = getStringCellValue(cell);
                        SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
                        Date parse = sim.parse(date);
                        category.setProductDate(parse);
                    } catch (Exception e) {
                        log.error("生产时间格式错误;");
                        errMsg += "生产时间格式错误;";
                        flag = true;
                        flagAllData = true;
                    }
                }
                //单位
                category.setTeileSet(teileSetMap == null ? "" : teileSetMap.get(category.getTeileSet()));
                //材质
                category.setMaterial_dictText(materialMap == null ? "" : materialMap.get(category.getMaterial()));
                //inventoryNum
                Integer count = inventoryMapper.selectCountByCategoryId(category.getId()).intValue();
                category.setInventoryNum(count);
                //ExistQuantity
                Integer countExist = inventoryMapper.selectExistCountByCategoryId(category.getId()).intValue();
                category.setExistQuantity(countExist);

                //错误原因
                cell = r.createCell(7);
                cell.setCellValue(errMsg);

                //开始记录当前行数据没有问题
                if (!flag) {
                    //人员
                    temp.add(category);
                    //记录要删除的行数
                    rowsNum.add(r.getRowNum() );
                }else{
                    rowsNumZC.add(r.getRowNum());
                }
            }
            inputStream.close();
            
            //添加数据
            boolean b = categoryService.saveBatch(temp);
            
            //检测数据是否存在问题
            if (flagAllData) {
                //如果记录行数不为空的话 开始删除表格中指定的行数
                if (Objects.nonNull(rowsNum)) {
                    for (Integer integer : rowsNum) {
                        //此方法只能删除行内容
                        rows.removeRow(rows.getRow(integer));
                    }
                    for (Integer integeryd : rowsNumZC) {
                        int yd = 0;
                        for (Integer integer : rowsNum) {
                            if(integer>integeryd){
                                break;
                            }
                            yd++;
                        }
                        rows.shiftRows(integeryd,integeryd,-yd);
                    }
                }
                //导出文件
                String fileName = "InStockImportExcel_" + System.currentTimeMillis() + ".xls";
                sheets.write(new File(uploadpath + File.separator + fileName));
                return Result.error(500,"导入失败", fileName);
            }
        }
        return Result.OK("导入成功", temp);
    }

package org.jeecg.modules.util;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.vo.DictModel;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.modules.system.entity.SysDepart;
import org.jeecg.modules.system.entity.SysDictItem;
import org.jeecg.modules.system.service.impl.SysDepartServiceImpl;
import org.jeecg.modules.teilemgmt.entity.Location;
import org.jeecg.modules.teilemgmt.entity.Supplier;
import org.jeecg.modules.teilemgmt.service.IInStockService;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.*;

/**
* @Description: 自定义Excel模板工具类
*/
public class ExcelTemplateUtil {

    /**
    * @Description:  获取 HSSFWorkbook 对象
    */
    public static HSSFWorkbook CreateWorkbook(){
        HSSFWorkbook wb = new HSSFWorkbook();
        return wb;
    }

    /**
    * @Description: 自定义模板标题
    * @Parame: wb 对象HSSFWorkbook
    * @Parame: sheetName 工作簿表格名称
    * @Parame: titleName 表格标题
    * @Parame: fixedCol 固定列数
    */
    public static HSSFSheet CreateTemplateTitle(HSSFWorkbook wb, String sheetName, String titleName, Integer fixedCol, String warn) {
        HSSFSheet sheet = wb.createSheet(sheetName);
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell = row1.createCell(0);
        //设置系统提示
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell1 = row2.createCell(0);
        cell1.setCellValue(warn);
        HSSFCellStyle Style = wb.createCellStyle();
        HSSFFont font2 = wb.createFont();
        font2.setFontHeightInPoints((short) 15);
        Style.setFont(font2);
        cell1.setCellStyle(Style);
        row2.setHeightInPoints(20);
        row1.setHeightInPoints(60);//设置高度
//        sheet.createFreezePane(1, 0);//固定
//        sheet.createFreezePane(1, 1);
//        sheet.createFreezePane(1, 2);
//        sheet.createFreezePane(1, 3);
        //设置单元格内容
        cell.setCellValue(titleName);
        //设置样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 25);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        cell.setCellStyle(cellStyle);
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,fixedCol));
        sheet.addMergedRegion(new CellRangeAddress(1,1,0,fixedCol));
        return sheet;
    }

    /**
    * @Description: 自定义属性
    * @Parame: wb 对象HSSFWorkbook
    * @Parame: sheet 对象HSSFSheet
    * @Parame: param 自定义属性集合
    */
    public static void CreateProperty(HSSFWorkbook wb, HSSFSheet sheet, List<String> param) {
        HSSFRow row2 = sheet.createRow(2);
        //设置样式
        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        HSSFFont font2 = wb.createFont();
        font2.setFontHeightInPoints((short) 10);
        cellStyle2.setFont(font2);
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  //填充单元格
        cellStyle2.setFillForegroundColor(HSSFColor.GOLD.index);    //填充黄色
        cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
        cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        for (int i=0;i<param.size();i++){
            row2.createCell(i).setCellValue(param.get(i));
            row2.getCell(i).setCellStyle(cellStyle2);
        }
    }
    /**
     * @Description: 输出文件
     */
    public static void CreateFile(HSSFWorkbook wb,HttpServletResponse response) throws IOException {
        //输出Excel文件
        OutputStream output=response.getOutputStream();
        response.reset();
        response.setHeader("Access-control-Allow-Origin", ("*"));
        response.setHeader("Access-Control-Allow-Methods", "GET,POST,OPTIONS,PUT,DELETE");
        response.setHeader("Access-Control-Allow-Credentials", "true");
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
    }
    /**
     * @Description: 封装创建下拉组件
     * @Parame: wb 对象HSSFWorkbook
     * @Parame: sheet 对象HSSFSheet
     * @Parame: namebook 下拉组件名称
     * @Parame: textlist 自定义组件内容
     * @Parame: firstRow 起始行
     * @Parame: endRow 终止行
     * @Parame: firstCo 起始列
     * @Parame: endCol 终止列
     * @Parame: index 下拉组件索引从1开始
     */
    public static HSSFSheet setHSSFValidation(HSSFWorkbook wb, HSSFSheet sheet, String namebook,
                                              String[] textlist, int firstRow, int endRow, int firstCol,
                                              int endCol, Integer index) {
        HSSFSheet hidden = wb.createSheet(namebook);
        wb.setSheetHidden(index, true);
        HSSFCell cell = null;
        for (int i = 0, length = textlist.length; i < length; i++) {
            String name = textlist[i];
            HSSFRow row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        Name namedCell = wb.createName();
        namedCell.setNameName(namebook);
        namedCell.setRefersToFormula(namebook+"!$A$1:$A$" + textlist.length);
        //加载数据,将名称为hidden的
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(namebook);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,endCol);
        HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
        //将第二个sheet设置为隐藏
        /*wbCreat.setSheetHidden(1, true);*/
        if (null != validation) {
            sheet.addValidationData(validation);
        }
        return sheet;
    }

    /**
     * @Description: 设置文本类型的单元格
     * @Params: sheet  sheet对象
     * @Params: firstRow 开始行数
     * @Params: endRow  结束行数
     * @Params: firstCol 开始列
     * @Params: endCol  结束列
    */
    public static void setTesxtType(HSSFWorkbook wb, HSSFSheet sheet, Integer Col) {
        CellStyle css = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        css.setDataFormat(format.getFormat("@"));
        sheet.setDefaultColumnStyle(Col, css);//设置第0列为文本格式
    }

    /**
    * @Description: 所属部门
    */
    public static String[] getSysOrg(SysDepartServiceImpl sysDepartService, ISysBaseAPI sysBaseAPI, HttpServletRequest request) {
        //查询所有的部门
        List<SysDepart> list5 = sysDepartService.list();
        ArrayList<String> strings = new ArrayList<>();
        ArrayList<String> stringName = new ArrayList<>();
        for (SysDepart sysDepart : list5) {
            String str[] = getFaAdmCheckStandard(sysDepart.getId(), list5, new String[]{"",""});
            if(str[1].length()<18){//最大18个层级，多了导出模板报错
                String temp = "";
                for(int i = str[1].length();i<18;i++){
                    temp += "0";
                }
                str[1] = str[1]+temp;//数组的【1】是序号，末尾补充0，用于大小排序
            }
            strings.add(str[1]);
            stringName.add(str[0].substring(0,str[0].length()-1));
        }
        String [] strArr =  strings.toArray(new String[]{});
        String [] strArrName =  stringName.toArray(new String[]{});
        return arraySort(strArr,strArrName);//冒泡排序，根据序号升序排列
    }

    /**
     * @Description: 排序方法
     * @Params:
    */
    public static String[] arraySort(String[] input,String[] inputName){
        for (int i=0;i<input.length-1;i++){
            for (int j=0;j<input.length-i-1;j++) {
                if(input[j].compareTo(input[j+1])>0){
                    String temp=input[j];
                    input[j]=input[j+1];
                    input[j+1]=temp;

                    String tempName=inputName[j];
                    inputName[j]=inputName[j+1];
                    inputName[j+1]=tempName;
                }
            }
        }
        return inputName;
    }

    /**
     * @Description: 递归查询父级部门名称
     */
    public static String[] getFaAdmCheckStandard(String id, List<SysDepart> listPart, String[] endIds) {
        if(listPart != null && listPart.size()>0) {
            for(SysDepart m : listPart) {
                if(m.getId().equals(id)) {
                    if(oConvertUtils.isNotEmpty(m.getId())) {
                        endIds[0] = m.getDepartName()+"_"+endIds[0];
                        endIds[1] = m.getDepartOrder()+endIds[1];
                        endIds = getFaAdmCheckStandard(m.getParentId(),listPart,endIds);
                    }
                }
            }
        }
        return endIds;
    }

    /**
    * @Description: 导入excel
    */
    public static Sheet importExcel(InputStream inputStream) throws IOException {
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        Workbook wb0 = new HSSFWorkbook(inputStream);
        //获取Excel文档中的第一个表单
        return wb0.getSheetAt(0);
    }

    /**
    * @Description: 获取工作簿
    */
    public static HSSFWorkbook getimportExcel(InputStream inputStream) throws IOException {
        //根据指定的文件输入流导入Excel从而产生Workbook对象
        HSSFWorkbook sheets = new HSSFWorkbook(inputStream);
        //获取Excel文档中的第一个表单
        return sheets;
    }

    /**
     * @Description: 判断当前行是否为空
     * @Params:
    */
    public static boolean isRowEmpty(Row row){
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK){
                return false;
            }
        }
        return true;
    }
    /**
     * 给sheet页  添加级联下拉列表
     * @param workbook    excel
     * @param targetSheet sheet页
     * @param options     要添加的下拉列表内容  ， keys 是下拉列表1中的内容，每个Map.Entry.Value 是对应的级联下拉列表内容
     * @param keyColumn   下拉列表1位置
     * @param valueColumn 级联下拉列表位置
     * @param fromRow     级联限制开始行
     * @param endRow      级联限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Map<String, List<String>> options, char keyColumn, char valueColumn, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        List<String> firstLevelItems = new ArrayList<>();

        int rowIndex = 0;
        for (Map.Entry<String, List<String>> entry : options.entrySet()) {
            String parent = formatNameName(entry.getKey());
            firstLevelItems.add(parent);
            List<String> children = entry.getValue();

            int columnIndex = 0;
            Row row = hiddenSheet.createRow(rowIndex++);
            Cell cell = null;

            for (String child : children) {
                cell = row.createCell(columnIndex++);
                cell.setCellValue(child);
            }

            char lastChildrenColumn = (char) ((int) 'A' + children.size() - 1);
            createName(workbook, parent, String.format(hiddenSheetName + "!$A$%s:$%s$%s", rowIndex, lastChildrenColumn, rowIndex));

            DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($" + keyColumn + "1)");
            CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, valueColumn - 'A', valueColumn - 'A');
            targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
        }

        addValidationToSheet(workbook, targetSheet, firstLevelItems.toArray(), keyColumn, fromRow, endRow);

    }
    /**
     * 给sheet页，添加下拉列表
     * @param workbook    excel文件，用于添加Name
     * @param targetSheet 级联列表所在sheet页
     * @param options     级联数据 ['百度','阿里巴巴']
     * @param column      下拉列表所在列 从'A'开始
     * @param fromRow     下拉限制开始行
     * @param endRow      下拉限制结束行
     */
    public static void addValidationToSheet(Workbook workbook, Sheet targetSheet, Object[] options, char column, int fromRow, int endRow) {
        String hiddenSheetName = "sheet" + workbook.getNumberOfSheets();
        Sheet optionsSheet = workbook.createSheet(hiddenSheetName);
        String nameName = column + "_parent";
        int rowIndex = 0;
        for (Object option : options) {
            int columnIndex = 0;
            Row row = optionsSheet.createRow(rowIndex++);
            Cell cell = row.createCell(columnIndex++);
            cell.setCellValue(option.toString());
        }
        createName(workbook, nameName, hiddenSheetName + "!$A$1:$A$" + options.length);
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(nameName);
        CellRangeAddressList regions = new CellRangeAddressList(fromRow, endRow, (int) column - 'A', (int) column - 'A');
        targetSheet.addValidationData(new HSSFDataValidation(regions, constraint));
    }
    private static Name createName(Workbook workbook, String nameName, String formula) {
        Name name = workbook.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(formula);
        return name;
    }
    /**
     * 不可数字开头
     * @param name
     * @return
     */
    static String formatNameName(String name) {
        name = name.replaceAll(" ", "").replaceAll("-", "_").replaceAll(":", ".");
        if (Character.isDigit(name.charAt(0))) {
            name = "_" + name;
        }
        return name;
    }

    public static String[] getLocationNameList(IInStockService inStockService) {
        //查询所有的货位
        List<Location> list = inStockService.findLocationList();
        ArrayList<String> strings = new ArrayList<>();
        for (Location location : list) {
            strings.add(location.getName());
        }
        String [] strArr =  strings.toArray(new String[]{});
        return strArr;
    }
    public static HashMap getLocationNameMapList(IInStockService inStockService) {
        //查询所有货位
        List<Location> list = inStockService.findLocationList();
        HashMap<String, String> map = new HashMap<>();
        for (Location location : list) {
            map.put(location.getName(),location.getId());
        }
        return map;
    }

    public static String[] getSysDictItem(IInStockService inStockService, String dictCode,String locale) {
        //查询所有的部门
        List<SysDictItem> list = inStockService.findSysDictItemByDictCode(dictCode);
        ArrayList<String> strings = new ArrayList<>();
        for (SysDictItem sysDictItem : list) {
            strings.add(getLanguageDictItem(sysDictItem,locale));
        }
        String [] strArr =  strings.toArray(new String[]{});
        return strArr;
    }

    private static String getLanguageDictItem(SysDictItem sysDictItem, String language) {
        if (StringUtils.isEmpty(language)){
            //默认返回中文
            return sysDictItem.getItemText();
        }
        if (CommonConstant.LANGUAGE_ZH_CN.equals(language)){
            return sysDictItem.getItemText();
        }else if (CommonConstant.LANGUAGE_EN_US.equals(language)){
            return sysDictItem.getEngItemText();
        }else{
            //默认返回中文
            return sysDictItem.getItemText();
        }
    }

    public static String[] getSupplierList(IInStockService inStockService) {
        //查询所有的供应商
        List<Supplier> list = inStockService.getSupplierList();
        ArrayList<String> strings = new ArrayList<>();
        for (Supplier supplier : list) {
            strings.add(supplier.getName());
        }
        String [] strArr =  strings.toArray(new String[]{});
        return strArr;
    }

    public static void setDateType(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
        CellStyle css = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        css.setDataFormat(format.getFormat("yyyy-m-d"));
        sheet.setDefaultColumnStyle(Col, css);//设置第0列为文本格式
    }

    public static void setNumberType(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
        CellStyle css = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        css.setDataFormat(format.getFormat("0"));
        sheet.setDefaultColumnStyle(Col, css);//设置第0列为文本格式
    }

    public static void setDecimalType(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
        CellStyle css = wb.createCellStyle();
        css.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        sheet.setDefaultColumnStyle(Col, css);//设置第0列为文本格式
    }

    public static HashMap<String, String> getLanguageSysDictMap(IInStockService inStockService, String dictCode,String language) {
        List<SysDictItem> list = inStockService.findSysDictItemByDictCode(dictCode);
        HashMap<String, String> map = new HashMap<>();
        for (SysDictItem sysDictItem : list) {
            if (StringUtils.isEmpty(language)){
                map.put(sysDictItem.getItemText(),sysDictItem.getItemValue());
            }
            if (CommonConstant.LANGUAGE_ZH_CN.equals(language)){
                map.put(sysDictItem.getItemText(),sysDictItem.getItemValue());
            }else if (CommonConstant.LANGUAGE_EN_US.equals(language)){
                map.put(sysDictItem.getEngItemText(),sysDictItem.getItemValue());
            }else {
                map.put(sysDictItem.getItemText(),sysDictItem.getItemValue());
            }
        }
        return map;
    }

    public static HashMap<String, String> getSupplierMapList(IInStockService inStockService) {
        //查询所有供应商
        List<Supplier> list = inStockService.getSupplierList();
        HashMap<String, String> map = new HashMap<>();
        for (Supplier supplier : list) {
            map.put(supplier.getName(),supplier.getId());
        }
        return map;
    }

    public static HashMap<String, String> getLanguageSysDictMapByValue(IInStockService inStockService, String dictCode,String language) {
        List<SysDictItem> list = inStockService.findSysDictItemByDictCode(dictCode);
        HashMap<String, String> map = new HashMap<>();
        for (SysDictItem sysDictItem : list) {
            if (StringUtils.isEmpty(language)){
                map.put(sysDictItem.getItemValue(),sysDictItem.getItemText());
            }
            if (CommonConstant.LANGUAGE_ZH_CN.equals(language)){
                map.put(sysDictItem.getItemValue(),sysDictItem.getItemText());
            }else if (CommonConstant.LANGUAGE_EN_US.equals(language)){
                map.put(sysDictItem.getItemValue(),sysDictItem.getEngItemText());
            }else {
                map.put(sysDictItem.getItemValue(),sysDictItem.getItemText());
            }
        }
        return map;
    }
}