# LayoutParser.java

    package cloud.wconnect.finance.service.pl.parseLayout;

    import cloud.wconnect.finance.pojo.dto.pl.LayoutElement;

    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;

    /**
     * layout.xls解析器
     */
    public class LayoutParser {

        /**
         * 解析Layout.xls文件
         *
         * @param fileInputStream 文件输入流
         * @return java.util.List<cloud.wconnect.finance.pojo.dto.pl.LayoutElement>
         * @author lichen
         * @date 2025/9/25
         */
        public static List<LayoutElement> parseLayoutFile(InputStream fileInputStream) throws Exception {
            return parseWithJExcelApi(fileInputStream);
        }

        /**
         * 解析Layout.xls文件
         *
         * @param fileInputStream 文件流
         * @return java.util.List<cloud.wconnect.finance.pojo.dto.pl.LayoutElement>
         * @author lichen
         * @date 2025/9/25
         */
        private static List<LayoutElement> parseWithJExcelApi(InputStream fileInputStream) throws Exception {
            List<LayoutElement> elements = new ArrayList<>();
            LayoutElement previousElement = null;
            try {
                jxl.Workbook workbook = jxl.Workbook.getWorkbook(fileInputStream);
                jxl.Sheet sheet = workbook.getSheet(0);

                // Find all data blocks between "#" and "Run on" markers
                List<int[]> dataBlocks = new ArrayList<>();
                int startRow = -1;

                for (int i = 0; i < sheet.getRows(); i++) {
                    jxl.Cell cellA = sheet.getCell(0, i);
                    if (cellA != null) {
                        String cellValue = cellA.getContents();
                        if ("#".equals(cellValue)) {
                            startRow = i;
                        } else if (cellValue != null && cellValue.startsWith("Run on")) {
                            if (startRow != -1) {
                                dataBlocks.add(new int[]{startRow, i});
                                startRow = -1; // Reset for next block
                            }
                        }
                    }
                }

                if (dataBlocks.isEmpty()) {
                    throw new IllegalArgumentException("Could not find any data blocks (# ... Run on) in the file");
                }

                // Process all data blocks
                for (int[] block : dataBlocks) {
                    int blockStart = block[0];
                    int blockEnd = block[1];

                    // Process rows between start and end of this block
                    previousElement = null; // Reset for each block
                    for (int i = blockStart + 1; i < blockEnd; i++) {
                        LayoutElement element = parseJxlRow(sheet, i, previousElement);
                        if (element != null) {
                            // This is a new element (with rowId in correct format)
                            elements.add(element);
                            previousElement = element;
                        } else if (previousElement != null) {
                            // This was a continuation row that was processed
                            // The previousElement has been updated in place
                        }
                    }
                }

                workbook.close();
            } catch (Exception e) {
                throw new IOException("Error reading Excel file with JExcelApi: " + e.getMessage(), e);
            }

            return elements;
        }

        /**
         * 解析Excel中的行数据，生成LayoutElement对象
         *
         * @param sheet           Excel sheet
         * @param rowIndex        行索引
         * @param previousElement 上一个元素
         * @return cloud.wconnect.finance.pojo.dto.pl.LayoutElement
         * @author lichen
         * @date 2025/9/25
         */
        private static LayoutElement parseJxlRow(jxl.Sheet sheet, int rowIndex, LayoutElement previousElement) {
            // Get cells from required columns
            jxl.Cell cellA = sheet.getCell(0, rowIndex); // rowId
            jxl.Cell cellC = sheet.getCell(2, rowIndex); // type
            jxl.Cell cellE = sheet.getCell(4, rowIndex); // name
            jxl.Cell cellG = sheet.getCell(6, rowIndex); // format
            jxl.Cell cellJ = sheet.getCell(9, rowIndex); // option

            // Handle continuation rows (empty cellA or non-conforming rowId)
            if (cellA == null || cellA.getContents().trim().isEmpty()) {
                // This is a continuation row with empty cellA, merge with previous element
                if (previousElement != null) {
                    processJxlContinuationRow(sheet, rowIndex, previousElement);
                }
                return null; // Don't create a new element for continuation rows
            }

            String rowId = cellA.getContents();

            // Check if rowId follows the expected "xx:xx" format
            // If not, this is a continuation of the previous logical row
            if (!rowId.matches("\\d+:\\d+")) {
                // This is a split row, concatenate with previous element
                if (previousElement != null) {
                    processJxlContinuationRow(sheet, rowIndex, previousElement);
                }
                return null; // Don't create a new element for continuation rows
            }

            String type = cellC != null ? cellC.getContents() : "";
            String name = cellE != null ? cellE.getContents() : "";
            String format = cellG != null ? cellG.getContents() : "";
            String option = cellJ != null ? cellJ.getContents() : "";
            String formula = ""; // Will be set if type is Formula
            String glCode = "Item".equalsIgnoreCase(type) ? parserGlCode(name) : "";

            return new LayoutElement(rowId, type, name, format, option, formula, glCode);
        }

        /**
         * 处理Excel中的连续行数据，合并到前一个元素中
         *
         * @param sheet           Excel sheet
         * @param rowIndex        行索引
         * @param previousElement 上一个元素
         * @author lichen
         * @date 2025/9/25
         */
        private static void processJxlContinuationRow(jxl.Sheet sheet, int rowIndex, LayoutElement previousElement) {
            jxl.Cell cellA = sheet.getCell(0, rowIndex); // rowId
            jxl.Cell cellC = sheet.getCell(2, rowIndex); // type/content
            jxl.Cell cellE = sheet.getCell(4, rowIndex); // name
            jxl.Cell cellG = sheet.getCell(6, rowIndex); // format
            jxl.Cell cellJ = sheet.getCell(9, rowIndex); // option

            // Concatenate cell values with previous element
            previousElement.setRowId(concatenateStrings(previousElement.getRowId(),
                    cellA != null ? cellA.getContents() : "", ""));
            previousElement.setName(concatenateStrings(previousElement.getName(),
                    cellE != null ? cellE.getContents() : ""));
            previousElement.setFormat(concatenateStrings(previousElement.getFormat(),
                    cellG != null ? cellG.getContents() : ""));
            previousElement.setOption(concatenateStrings(previousElement.getOption(),
                    cellJ != null ? cellJ.getContents() : ""));

            // If previous element type was Formula, this row contains the formula
            if ("Formula".equalsIgnoreCase(previousElement.getType())) {
                previousElement.setFormula(concatenateStrings(previousElement.getFormula(),
                        cellC != null ? cellC.getContents() : ""));
            }

            String cellCValue = cellC != null ? cellC.getContents() : "";
            if ("Sub-Total".equalsIgnoreCase(cellCValue)) {
                previousElement.setType(concatenateStrings(previousElement.getType(), "Sub-Total"));
            }

            if ("Item".equalsIgnoreCase(previousElement.getType())) {
                previousElement.setGlCode(parserGlCode(previousElement.getName()));
            }
        }

        /**
         * 解析GL代码
         *
         * @param name 名称
         * @return java.lang.String
         * @author lichen
         * @date 2025/9/25
         */
        private static String parserGlCode(String name) {
            if (name == null || name.isEmpty()) {
                return "";
            }
            int startIdx = name.indexOf('(');
            int endIdx = name.indexOf(')');
            if (startIdx != -1 && endIdx != -1 && endIdx > startIdx) {
                return name.substring(startIdx + 1, endIdx).trim();
            } else {
                String[] parts = name.split("-");
                return parts[parts.length - 1].trim();
            }
        }

        /**
         * 安全地连接两个字符串：
         * 如果第一个字符串为空或null，直接返回第二个字符串
         * 如果第二个字符串为空或null，直接返回第一个字符串
         * 否则将两个字符串用指定的连接符拼接后返回
         *
         * @param s1        字符串1
         * @param s2        字符串2
         * @return java.lang.String
         * @author lichen
         * @date 2025/9/25
         */
        private static String concatenateStrings(String s1, String s2) {
            return concatenateStrings(s1, s2, " ");
        }

        /**
         * 安全地连接两个字符串：
         * 如果第一个字符串为空或null，直接返回第二个字符串
         * 如果第二个字符串为空或null，直接返回第一个字符串
         * 否则将两个字符串用指定的连接符拼接后返回
         *
         * @param s1        字符串1
         * @param s2        字符串2
         * @param connector 连接符
         * @return java.lang.String
         * @author lichen
         * @date 2025/9/25
         */
        private static String concatenateStrings(String s1, String s2, String connector) {
            if (s1 == null || s1.isEmpty()) {
                return s2;
            }
            if (s2 == null || s2.isEmpty()) {
                return s1;
            }
            return s1 + connector + s2;
        }
    }