yys 销售台账导入接口

maven

9 天以前 5fa5d9beee2594461871ae6bd6e24ddefc12b8ff

 src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java

@@ -95,6 +95,8 @@
{

    private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);



    public static final String SEPARATOR = ",";



    public static final String FORMULA_REGEX_STR = "=|-|\\+|@";



    public static final String[] FORMULA_STR = { "=", "-", "+", "@" };

@@ -360,7 +362,7 @@
            throw new IOException("文件sheet不存在");

        }

        boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);

        Map<String, PictureData> pictures;

        Map<String, List<PictureData>> pictures = null;

        if (isXSSFWorkbook)

        {

            pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);

@@ -426,7 +428,7 @@
                    if (String.class == fieldType)

                    {

                        String s = Convert.toStr(val);

                        if (StringUtils.endsWith(s, ".0"))

                        if (s.matches("^\\d+\\.0$"))

                        {

                            val = StringUtils.substringBefore(s, ".0");

                        }

@@ -504,16 +506,185 @@
                        }

                        else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))

                        {

                            PictureData image = pictures.get(row.getRowNum() + "_" + entry.getKey());

                            if (image == null)

                            StringBuilder propertyString = new StringBuilder();

                            List<PictureData> images = pictures.get(row.getRowNum() + "_" + entry.getKey());

                            for (PictureData picture : images)

                            {

                                val = "";

                                byte[] data = picture.getData();

                                String fileName = FileUtils.writeImportBytes(data);

                                propertyString.append(fileName).append(SEPARATOR);

                            }

                            else

                            {

                                byte[] data = image.getData();

                                val = FileUtils.writeImportBytes(data);

                            val = StringUtils.stripEnd(propertyString.toString(), SEPARATOR);

                        }

                        ReflectUtils.invokeSetter(entity, propertyName, val);

                    }

                }

                list.add(entity);

            }

        }

        return list;

    }



    /**

     * 扩展：读取Excel中多个指定Sheet的数据

     * @param sheetNameList 要读取的Sheet名称列表（null则读取所有Sheet）

     * @param is 输入流

     * @param titleNum 标题占用行数

     * @return Map<Sheet名称, 对应Sheet的数据列表>

     */

    public Map<String, List<T>> importExcelMultiSheet(List<String> sheetNameList, InputStream is, int titleNum) {

        Map<String, List<T>> resultMap = new HashMap<>();

        try {

            this.type = Type.IMPORT;

            this.wb = WorkbookFactory.create(is);



            // 1. 确定要读取的Sheet列表

            List<Sheet> sheetsToRead = new ArrayList<>();

            if (sheetNameList != null && !sheetNameList.isEmpty()) {

                // 读取指定名称的Sheet

                for (String sheetName : sheetNameList) {

                    Sheet sheet = wb.getSheet(sheetName);

                    if (sheet != null) {

                        sheetsToRead.add(sheet);

                    } else {

                        log.warn("指定的Sheet名称不存在：{}", sheetName);

                    }

                }

            } else {

                // 读取所有Sheet

                int sheetCount = wb.getNumberOfSheets();

                for (int i = 0; i < sheetCount; i++) {

                    sheetsToRead.add(wb.getSheetAt(i));

                }

            }



            // 2. 遍历每个Sheet，复用原有导入逻辑

            for (Sheet sheet : sheetsToRead) {

                String sheetName = wb.getSheetName(wb.getSheetIndex(sheet));

                // 复用原有核心导入逻辑（关键：将原有方法拆分为可复用的内部方法）

                List<T> sheetData = importExcelBySheet(sheet, titleNum);

                resultMap.put(sheetName, sheetData);

            }

        } catch (Exception e) {

            log.error("导入多Sheet Excel异常{}", e.getMessage());

            throw new UtilException(e.getMessage());

        } finally {

            IOUtils.closeQuietly(is);

        }

        return resultMap;

    }



    /**

     * 内部复用方法：根据指定Sheet对象读取数据（提取原有importExcel的核心逻辑）

     */

    private List<T> importExcelBySheet(Sheet sheet, int titleNum) throws Exception {

        List<T> list = new ArrayList<T>();

        if (sheet == null) {

            return list;

        }



        boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);

        Map<String, List<PictureData>> pictures = null;

        if (isXSSFWorkbook) {

            pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) wb);

        } else {

            pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) wb);

        }



        int rows = sheet.getLastRowNum();

        if (rows > 0) {

            Map<String, Integer> cellMap = new HashMap<String, Integer>();

            Row heard = sheet.getRow(titleNum);

            for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++) {

                Cell cell = heard.getCell(i);

                if (StringUtils.isNotNull(cell)) {

                    String value = this.getCellValue(heard, i).toString();

                    cellMap.put(value, i);

                } else {

                    cellMap.put(null, i);

                }

            }



            List<Object[]> fields = this.getFields();

            Map<Integer, Object[]> fieldsMap = new HashMap<Integer, Object[]>();

            for (Object[] objects : fields) {

                Excel attr = (Excel) objects[1];

                Integer column = cellMap.get(attr.name());

                if (column != null) {

                    fieldsMap.put(column, objects);

                }

            }



            for (int i = titleNum + 1; i <= rows; i++) {

                Row row = sheet.getRow(i);

                if (isRowEmpty(row)) {

                    continue;

                }

                T entity = null;

                for (Map.Entry<Integer, Object[]> entry : fieldsMap.entrySet()) {

                    Object val = this.getCellValue(row, entry.getKey());

                    entity = (entity == null ? clazz.newInstance() : entity);

                    Field field = (Field) entry.getValue()[0];

                    Excel attr = (Excel) entry.getValue()[1];

                    Class<?> fieldType = field.getType();



                    // 以下是原有数据类型转换、字典解析等逻辑（完全复用）

                    if (String.class == fieldType) {

                        String s = Convert.toStr(val);

                        if (s.matches("^\\d+\\.0$")) {

                            val = StringUtils.substringBefore(s, ".0");

                        } else {

                            String dateFormat = field.getAnnotation(Excel.class).dateFormat();

                            if (StringUtils.isNotEmpty(dateFormat)) {

                                val = parseDateToStr(dateFormat, val);

                            } else {

                                val = Convert.toStr(val);

                            }

                        }

                    } else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {

                        val = Convert.toInt(val);

                    } else if ((Long.TYPE == fieldType || Long.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {

                        val = Convert.toLong(val);

                    } else if (Double.TYPE == fieldType || Double.class == fieldType) {

                        val = Convert.toDouble(val);

                    } else if (Float.TYPE == fieldType || Float.class == fieldType) {

                        val = Convert.toFloat(val);

                    } else if (BigDecimal.class == fieldType) {

                        val = Convert.toBigDecimal(val);

                    } else if (Date.class == fieldType) {

                        if (val instanceof String) {

                            val = DateUtils.parseDate(val);

                        } else if (val instanceof Double) {

                            val = DateUtil.getJavaDate((Double) val);

                        }

                    } else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {

                        val = Convert.toBool(val, false);

                    }



                    if (StringUtils.isNotNull(fieldType)) {

                        String propertyName = field.getName();

                        if (StringUtils.isNotEmpty(attr.targetAttr())) {

                            propertyName = field.getName() + "." + attr.targetAttr();

                        }

                        if (StringUtils.isNotEmpty(attr.readConverterExp())) {

                            val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());

                        } else if (StringUtils.isNotEmpty(attr.dictType())) {

                            if (!sysDictMap.containsKey(attr.dictType() + val)) {

                                String dictValue = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());

                                sysDictMap.put(attr.dictType() + val, dictValue);

                            }

                            val = sysDictMap.get(attr.dictType() + val);

                        } else if (!attr.handler().equals(ExcelHandlerAdapter.class)) {

                            val = dataFormatHandlerAdapter(val, attr, null);

                        } else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures)) {

                            StringBuilder propertyString = new StringBuilder();

                            List<PictureData> images = pictures.get(row.getRowNum() + "_" + entry.getKey());

                            for (PictureData picture : images) {

                                byte[] data = picture.getData();

                                String fileName = FileUtils.writeImportBytes(data);

                                propertyString.append(fileName).append(SEPARATOR);

                            }

                            val = StringUtils.stripEnd(propertyString.toString(), SEPARATOR);

                        }

                        ReflectUtils.invokeSetter(entity, propertyName, val);

                    }

@@ -1037,12 +1208,15 @@
        else if (ColumnType.IMAGE == attr.cellType())

        {

            ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) cell.getColumnIndex(), cell.getRow().getRowNum(), (short) (cell.getColumnIndex() + 1), cell.getRow().getRowNum() + 1);

            String imagePath = Convert.toStr(value);

            if (StringUtils.isNotEmpty(imagePath))

            String propertyValue = Convert.toStr(value);

            if (StringUtils.isNotEmpty(propertyValue))

            {

                byte[] data = ImageUtils.getImage(imagePath);

                getDrawingPatriarch(cell.getSheet()).createPicture(anchor,

                        cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));

                List<String> imagePaths = StringUtils.str2List(propertyValue, SEPARATOR);

                for (String imagePath : imagePaths)

                {

                    byte[] data = ImageUtils.getImage(imagePath);

                    getDrawingPatriarch(cell.getSheet()).createPicture(anchor, cell.getSheet().getWorkbook().addPicture(data, getImageType(data)));

                }

            }

        }

    }

@@ -1148,6 +1322,7 @@
                String dictType = attr.dictType();

                if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))

                {

                    cell.getCellStyle().setDataFormat(this.wb.getCreationHelper().createDataFormat().getFormat(dateFormat));

                    cell.setCellValue(parseDateToStr(dateFormat, value));

                }

                else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))

@@ -1286,7 +1461,7 @@
    public static String convertByExp(String propertyValue, String converterExp, String separator)

    {

        StringBuilder propertyString = new StringBuilder();

        String[] convertSource = converterExp.split(",");

        String[] convertSource = converterExp.split(SEPARATOR);

        for (String item : convertSource)

        {

            String[] itemArray = item.split("=");

@@ -1323,7 +1498,7 @@
    public static String reverseByExp(String propertyValue, String converterExp, String separator)

    {

        StringBuilder propertyString = new StringBuilder();

        String[] convertSource = converterExp.split(",");

        String[] convertSource = converterExp.split(SEPARATOR);

        for (String item : convertSource)

        {

            String[] itemArray = item.split("=");

@@ -1448,8 +1623,7 @@
     */

    public String encodingFilename(String filename)

    {

        filename = UUID.randomUUID() + "_" + filename + ".xlsx";

        return filename;

        return UUID.randomUUID() + "_" + filename + ".xlsx";

    }



    /**

@@ -1750,30 +1924,24 @@
     * @param workbook 工作簿对象

     * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData

     */

    public static Map<String, PictureData> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)

    public static Map<String, List<PictureData>> getSheetPictures03(HSSFSheet sheet, HSSFWorkbook workbook)

    {

        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();

        Map<String, List<PictureData>> sheetIndexPicMap = new HashMap<>();

        List<HSSFPictureData> pictures = workbook.getAllPictures();

        if (!pictures.isEmpty())

        if (!pictures.isEmpty() && sheet.getDrawingPatriarch() != null)

        {

            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren())

            {

                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();

                if (shape instanceof HSSFPicture)

                {

                    HSSFPicture pic = (HSSFPicture) shape;

                    int pictureIndex = pic.getPictureIndex() - 1;

                    HSSFPictureData picData = pictures.get(pictureIndex);

                    HSSFClientAnchor anchor = (HSSFClientAnchor) pic.getAnchor();

                    String picIndex = anchor.getRow1() + "_" + anchor.getCol1();

                    sheetIndexPicMap.put(picIndex, picData);

                    sheetIndexPicMap.computeIfAbsent(picIndex, k -> new ArrayList<>()).add(pic.getPictureData());

                }

            }

            return sheetIndexPicMap;

        }

        else

        {

            return sheetIndexPicMap;

        }

        return sheetIndexPicMap;

    }



    /**

@@ -1783,16 +1951,15 @@
     * @param workbook 工作簿对象

     * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData

     */

    public static Map<String, PictureData> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)

    public static Map<String, List<PictureData>> getSheetPictures07(XSSFSheet sheet, XSSFWorkbook workbook)

    {

        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();

        Map<String, List<PictureData>> sheetIndexPicMap = new HashMap<>();

        for (POIXMLDocumentPart dr : sheet.getRelations())

        {

            if (dr instanceof XSSFDrawing)

            {

                XSSFDrawing drawing = (XSSFDrawing) dr;

                List<XSSFShape> shapes = drawing.getShapes();

                for (XSSFShape shape : shapes)

                for (XSSFShape shape : drawing.getShapes())

                {

                    if (shape instanceof XSSFPicture)

                    {

@@ -1800,7 +1967,7 @@
                        XSSFClientAnchor anchor = pic.getPreferredSize();

                        CTMarker ctMarker = anchor.getFrom();

                        String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();

                        sheetIndexPicMap.put(picIndex, pic.getPictureData());

                        sheetIndexPicMap.computeIfAbsent(picIndex, k -> new ArrayList<>()).add(pic.getPictureData());

                    }

                }

            }