A method and system for identifying BOQ data in international engineering projects

Abstract

This application discloses a method and system for identifying BOQ data in international engineering projects, aiming to address the problem that existing technologies are not suitable for international engineering. This application uses frozen rows and columns in an Excel file to display the data columns and the types and hierarchies of data rows; it determines the position of the table header area through table structure identification; it provides two modes: incremental identification and re-identification; it automatically identifies the type of each data row through rule item matching, and identifies the hierarchy of chapter rows and list rows based on coding features and text similarity; it provides a method for progressively adding rule items and multiple identifications to identify complex sheet page data; and it organizes, displays, and saves the identification results in a tree structure, providing bidirectional positioning of data rows in the Excel file and nodes in the tree structure. This application achieves efficient and accurate identification of BOQ data for international engineering projects, effectively reducing manual modification workload and improving the work efficiency of quotation personnel.

Description

Technical Field

[0001] This application relates to the field of international engineering quotation technology, and in particular to a method and system for identifying BOQ data in international engineering projects. Background Technology

[0002] In the bidding process for international engineering projects, users need to identify the data in the BOQ (Bill of Quantities) provided by the client to accurately quote prices. The client's BOQ file is usually in Excel format (or an Excel file converted from a PDF file). The BOQ data refers to the valid data rows closely related to the BOQ pricing, typically including the BOQ, notes, and headings (excluding auxiliary data rows related to data references and summaries, as well as invalid rows without valid information). This data is closely related to the accuracy of the bidding process, and quickly and accurately identifying this data and the logical and hierarchical relationships between them is particularly important for bidding in international engineering projects.

[0003] Currently, existing technologies for BOQ data identification mainly follow the common practices used for identifying BOQs in domestic engineering projects. This involves identifying BOQs based on their five elements (code, name, item characteristics, unit, and quantity) through keyword matching (or checking for empty fields). Since the characteristics of BOQ data in domestic projects are relatively obvious—for example, the content of these five elements cannot be empty for the list row—the identification effect can meet the needs of practical applications. However, for international projects, the five elements of the BOQ are less obvious. For example, the code lacks clear characteristics, there is generally no item characteristic column, the quantity may not have a numerical value, and BOQs also support multiple levels. If conventional methods are still used for automatic identification of BOQ data, the identification effect cannot meet the needs of practical applications, and a large amount of manual modification work is still required. For large projects, there are usually tens of thousands of BOQ data entries. Without an automatic identification method specifically tailored to the characteristics of international project BOQs, the work efficiency of quotation personnel will inevitably be greatly affected. Summary of the Invention

[0004] This application provides a method and system for identifying BOQ data in international engineering projects, aiming to address the limitations of existing technologies in international engineering projects. For international engineering projects, the five key characteristics of BOQ are not obvious, and BOQ supports multiple levels. If conventional methods are used for automatic identification of BOQ data, the identification effect cannot meet the needs of practical applications, and a large amount of manual modification work is still required, which greatly affects the work efficiency of quotation personnel.

[0005] Firstly, a method for identifying BOQ data in international engineering projects is provided, including:

[0006] Step S1: Determine the Excel file to be identified. Insert a frozen row above the first row of each valid sheet in the Excel file to display the type of each data column. Insert two frozen columns to the left of the first column to display the type and hierarchy of each data row, respectively.

[0007] Step S2: Locate the target Sheet page, identify the data columns by keywords, and determine the position area of ​​the entire header based on the row where the matching keywords are located; the rest is the data area.

[0008] Step S3: Select the recognition mode based on the need to retain manually modified content, including incremental recognition and re-recognition;

[0009] Step S4: Starting from the first row of the data area of ​​the current Sheet page, for each row of data: If it is the incremental recognition mode, first determine whether the manual modification flag is true. If it is, skip and continue to the recognition of the next row; otherwise, execute step S5. If it is the re-recognition mode, ignore the manual modification flag and directly execute step S5.

[0010] Step S5: First, determine whether the current data row is an auxiliary data row or an invalid row. If so, skip and continue to the next row for identification. If not, in the order of list row, comment row, and chapter row, match identification rule items according to the characteristics of the data row. If a matching rule item is found, set the current data row to the corresponding data row type. If no matching rule item is found, set the current data row to an invalid row. The identification rule items are pre-set for list row, comment row, and chapter row, and each data row type has zero or more identification rule items. Each identification rule item consists of data row characteristics and feature values ​​of data columns.

[0011] Step S6: If the current data row is identified as a chapter row or a list row, then the hierarchy of the chapter row or list row is further identified based on its encoding features or text similarity rules.

[0012] Step S7: Repeat steps S4-S6 to continue recognizing the next row of data until all data rows on the current Sheet page have been recognized and the recognition results are displayed.

[0013] Step S8: Find the next valid Sheet page, repeat steps S2-S7 until all valid Sheet pages have been identified, and save the identified data.

[0014] In the above scheme, optionally, the data row features include content features and format features, wherein the content features are whether the cell has content, and the format features include font type, whether it is bold, whether it is italic, whether it is underlined, and whether it is all uppercase;

[0015] The feature values ​​of the data column are used to represent the values ​​of a specified data column on a feature item of a certain data row. Optionally, in the above scheme, the hierarchical identification rules for chapter rows in step S6 include:

[0016] Let the current chapter line be H2, find the nearest chapter line and let it be H1. If no such line is found, the level of H2 will be automatically set to 1.

[0017] If there are no list lines in the valid data lines between chapter line H1 and chapter line H2, and the codes of H1 and H2 are not empty, and the codes of H1 and H2 have the same coding characteristics, then H2 and H1 are determined to be at the same level; otherwise, H2 is the next level of H1. Here, a valid data line refers to all data lines except for auxiliary data lines and invalid lines.

[0018] When there is a list line in the valid data lines between chapter line H1 and chapter line H2, and both H1 and H2 have codes, compare the coding features of H2 and H1. If they are the same, then H2 and H1 are at the same level; if they are different, then compare the coding features of each level of parent chapter line of H2 and H1 upwards until a line with the same coding feature H1' is found, and then H2 and H1' are determined to be at the same level; if no line with the same coding feature is found up to the top level, then H2 and H1 are at the same level.

[0019] When there is a list line in the effective data lines between chapter line H1 and chapter line H2, and H1 and H2 do not all have codes, calculate the cosine similarity between H2 and H1 and their parent chapter names at each level in turn, and record the chapter line H1' corresponding to the maximum similarity. If the maximum similarity is greater than the effective threshold, then H2 and H1' are at the same level; otherwise, H2 and H1 are at the same level. The effective threshold is preset.

[0020] In the above scheme, alternatively, complex encoding features can be identified by defining chapter line encoding rules using regular expressions.

[0021] Optionally, in the above scheme, the hierarchical identification rules for the list rows in step S6 include:

[0022] Let the current list row be B2, and find the nearest valid data row forward;

[0023] When the current valid data row type is a chapter row, the chapter row is automatically set as the parent of the current list row, that is, the level of the current list row is set to 1;

[0024] When the current valid data row type is a list row, compare the code of the current list row B2 with the previous list row B1. If the code of B2 is the code of B1 plus a valid separator and letters or numbers, then B2 is the next level of B1. If the code of B1 exists, but the code of B2 is empty, then B2 is also the next level of B1. If the code of B1 is not empty, but its quantity column is empty, while the quantity column of B2 is not empty, then B2 is determined to be the next level of B1. In other cases, B2 and B1 are determined to be at the same level.

[0025] Optionally, when the sheet format is complex, a method of gradually adding recognition rule items and recognizing multiple times can be adopted:

[0026] 1) First, set identification rules for a portion of representative data rows and then identify them. Data rows that do not match the rules are identified as invalid rows.

[0027] 2) Filter out invalid rows, find the valid data rows, and append their features to the recognition rule items of the corresponding data type;

[0028] 3) Repeat steps 1)-2) until the invalid rows no longer contain valid data;

[0029] If manual modifications are made, incremental recognition mode must be used after adding rule items.

[0030] Optionally, in the above scheme, if a situation that does not conform to the logical relationship of the data occurs during the recognition process, the recognition is stopped and the erroneous data row is marked. The data recognition is then performed again after the data row recognition rules are adjusted, or the erroneous data row is manually modified.

[0031] Optionally, the above scheme also includes: the data type of each data row and the hierarchy of chapter rows and list rows can be manually modified. When manually modified, the manual modification flag of the row is automatically set to true. This flag is used for judgment during incremental recognition. When the re-recognition mode is selected, the flag of all data rows is automatically reset.

[0032] Optionally, in step S7, displaying the recognition result specifically includes: organizing the recognition result into tree-structured data, wherein each data row object corresponds to a node in the tree structure, and bidirectional positioning functionality is achieved.

[0033] Clicking on a recognized valid data row in an Excel file will automatically locate the corresponding node in the tree structure;

[0034] Clicking on a node in the tree structure will automatically locate the corresponding data row in the Excel file.

[0035] Secondly, a system suitable for identifying BOQ data in international engineering projects is provided, including:

[0036] The Excel file display module is used to display all the sheets in an Excel file and the data content of each sheet. It provides functions for modifying and adjusting the type and hierarchy of data rows and columns. It supports inserting a frozen row above the first row and a frozen column to the left of the first column of each valid sheet. It provides functions for filtering data rows by specified data row type and specified conditions. It also provides a sheet selection function and supports removing invalid sheets that do not need to be identified.

[0037] The recognition module identifies table structures, including header and data areas, using built-in keywords and matching methods. It supports selecting recognition modes based on the retention requirements of manually modified content, including incremental and re-recognition. Users can adjust and customize the keywords and matching methods for each data column. It provides data row recognition rule settings and displays each recognition rule item for different data row types, allowing users to manually set or adjust each rule item. It also supports automatic extraction of specified data row features. Based on the identified table structure content and data row recognition rule settings, it automatically identifies the data row type and determines the hierarchy of chapter and list rows based on encoding features and text similarity rules, supporting manual modification by the user. When setting, extracting, or modifying feature values ​​for rule items, it automatically checks for duplicates, blocking and prompting if duplicates are found.

[0038] The results display module organizes the identified data row objects in the form of structured data and displays them in a tree structure. It supports automatically locating the corresponding node in the tree structure when clicking on a row in the Excel file display module, and automatically locating the corresponding row in the Excel file display module when clicking on a node in the tree structure. The recognition results can also be saved in the form of structured data.

[0039] Compared with the prior art, this application has at least the following beneficial effects:

[0040] This application, based on further analysis and research of existing technical problems, recognizes that existing technologies are not suitable for international engineering projects. For international projects, the five key characteristics of the Business Qualification (BOQ) are not obvious, and BOQs support multiple levels. If conventional methods are used for automatic identification of BOQ data, the identification results cannot meet the needs of practical applications, still requiring a large amount of manual modification work, which greatly impacts the work efficiency of quotation personnel. This application uses frozen rows and columns inserted into an Excel file to assist in displaying the types and levels of data columns and rows, providing a clear data structure framework for subsequent identification. Furthermore, by identifying the table structure and accurately locating the table header area, a foundation is laid for identifying data rows. Based on different identification modes, data row types are matched by sequentially comparing identification rule items, and the data row levels are automatically identified using coding features and text similarity rules. Finally, the identification results are displayed in a tree structure, achieving efficient and accurate identification of international engineering BOQ data, effectively reducing manual modification workload, significantly improving the work efficiency of quotation personnel, and providing a reliable method for the automatic processing of international engineering BOQ data identification. This optimizes the international engineering BOQ data identification process and improves data processing efficiency and accuracy.

[0041] This application also has at least the following beneficial effects:

[0042] 1. The method of identifying the type by judging the content and format characteristics of data rows fully conforms to the data characteristics of international engineering BOQ, and the automatic extraction function of data row characteristics provided by the system greatly improves the identification efficiency.

[0043] 2. The method of identifying data row hierarchy through coding features has good scalability, allowing coding rules to be defined through regular expressions, and can meet the needs of hierarchical identification under complex coding feature conditions.

[0044] 3. The incremental recognition method can greatly improve the efficiency of data row recognition. By filtering invalid rows multiple times and combining manual judgment, valid data rows are found from the filtered invalid rows, and their features are added to the recognition rules for recognition again until all valid data rows are recognized, thus avoiding the situation where some valid data rows are missed. Attached Figure Description

[0045] Figure 1 This is a flowchart illustrating a method for identifying BOQ data in international engineering projects, provided as an embodiment of this application.

[0046] Figure 2 This is a schematic diagram illustrating a process of gradually adding recognition rule items and performing multiple recognitions when the Sheet page format is complex, as provided in one embodiment of this application.

[0047] Figure 3 This is a schematic diagram illustrating the setting of identification rules provided in one embodiment of this application. Detailed Implementation

[0048] To make the objectives, technical solutions, and advantages of this application clearer, the following detailed description is provided in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the scope of this application.

[0049] In the description of this application, unless otherwise stated, expressions such as "comprising," "including," and "having" also mean "not limited to" (certain units, components, materials, steps, etc.).

[0050] The basic principles of this application are:

[0051] Regarding data row type identification, the conventional method of simply matching data column keywords and checking for empty cells is changed. Instead, it automatically identifies data row types by matching data row features. The core principle and approach are the decomposition, description, and extraction of data row features. This involves establishing a correspondence between data row types and features to create identification rules, and then identifying the data row type through matching these rules. For data row hierarchy identification, different identification methods are used for chapter rows and list rows, determining the hierarchy through the identification and comparison of coded features or text similarity pairs.

[0052] The specific method of this application:

[0053] For identifying data row types: Based on the characteristics of international engineering BOQ data, data row features are decomposed into two categories: content features and format features. Content features refer to whether the cell contains content, while format features are further subdivided into: font type, whether it is bold, whether it is italic, whether it is underlined, and whether it is all uppercase, etc.

[0054] For data row level identification: By identifying encoded features or text similarity and comparing them layer by layer upwards, the hierarchy of list (BOQ) and heading (Heading) can be automatically identified.

[0055] In one embodiment, a method for identifying BOQ data for international engineering projects is provided, the method comprising the following steps S1-S7.

[0056] Step S1: Determine the Excel file to be identified. Insert a frozen row above the first row of each valid sheet in the Excel file to display the type of each data column. Insert two frozen columns to the left of the first column to display the type and level of each data row, respectively.

[0057] Step S2: Locate the target Sheet page, identify the data columns by keywords, and determine the position area of ​​the entire header based on the row where the matching keywords are located; the rest is the data area.

[0058] Step S3: Select the recognition mode based on the requirement to retain manually modified content, including incremental recognition and re-recognition.

[0059] In one embodiment, the data type of each data row and the hierarchy of chapter and list rows can be manually modified. When manually modified, the manual modification flag of that row is automatically set to true. This flag is used for judgment during incremental recognition. For manually modified data rows, incremental recognition mode is usually used. However, in some specific cases, it is also necessary to retain the re-recognition mode. For user experience considerations, this embodiment retains both incremental recognition mode and re-recognition mode, but defaults to incremental recognition. If the user needs, they can manually change to re-recognition mode. When re-recognition mode is selected, the flag of all data rows is automatically reset.

[0060] Step S4: Starting from the first row of the data area of ​​the current Sheet page, for each row of data: If it is the incremental recognition mode, first determine whether the manual modification flag is true. If it is, skip and continue to the recognition of the next row; otherwise, execute step S5. If it is the re-recognition mode, ignore the manual modification flag and directly execute step S5.

[0061] Step S5: First, determine whether the current data row is an auxiliary data row or an invalid row. If so, skip and continue to the next row for identification. If not, match the identification rule items according to the characteristics of the data row in the order of list row, comment row, and chapter row. If a matching rule item is found, set the current data row to the corresponding data row type. If no matching rule item is found, set the current data row to an invalid row. The identification rule items are pre-set for list row, comment row, and chapter row, and each data row type has zero or more identification rule items. Each identification rule item consists of data row features and data column feature values. The feature values ​​between rule items are not allowed to be repeated.

[0062] In this embodiment, each data row type has zero or more identification rule items. If a certain data row type (such as a comment) has zero identification rule items, it means that in the current identification process, no data row can be identified as a comment type, and the feature comparison of the comment row will be automatically skipped when each data row is identified.

[0063] In one embodiment, the data row features include content features and format features, wherein the content features are whether the cell contains content, and the format features include font type, whether it is bold, whether it is italic, whether it is underlined, and whether it is all uppercase.

[0064] The feature values ​​of the data column are used to represent the values ​​of a specified data column (such as code, name, unit, quantity, etc.) on a certain data row feature (such as whether the cell has content, font type, whether it is bold, italic, underlined, and all uppercase). The value type includes text (corresponding to the font type in the format feature) or boolean (corresponding to other data row features). If a value for a certain feature is not set, it is the default value. The default value for text type is empty, and the default value for boolean type is no.

[0065] In this embodiment, reference Figure 3 Users set identification rules based on the characteristics of a representative data row. By matching these rules, all data rows with the same characteristics are automatically assigned to the corresponding type. For example, if the name data column of a chapter row contains "EARTHWORKS" in bold Arial font with underscores, this row is considered a representative data row. Its characteristics are extracted as follows: the name data column contains content, the font is Arial, the style is bold, underscore, and all uppercase, and this is used as an identification rule for chapter rows. When traversing each data row, all data rows matching the characteristics are matched according to this rule, and the type of these data rows is automatically set to chapter row. When there are multiple rule items, they are compared one by one in the order of list, comment, and chapter. When a matching rule item is found, the comparison stops, and the identification of subsequent levels continues or the identification of the next row begins.

[0066] Step S6: If the current data row is identified as a chapter row or list row, then the hierarchy of the chapter row or list row is further identified based on its encoding characteristics or text similarity rules.

[0067] In one embodiment, in step S6, the hierarchical identification rules for chapter rows include:

[0068] Let the current chapter line be H2, find the nearest chapter line and let it be H1. If no such line is found, the level of H2 will be automatically set to 1.

[0069] If there are no list lines in the valid data lines between chapter line H1 and chapter line H2, and the codes of H1 and H2 are not empty, and the codes of H1 and H2 have the same coding characteristics, then H2 and H1 are determined to be at the same level; otherwise, H2 is the next level of H1. Here, a valid data line refers to all data lines except for auxiliary data lines and invalid lines.

[0070] When there is a list line in the valid data lines between chapter line H1 and chapter line H2, and both H1 and H2 have codes, compare the coding features of H2 and H1. If they are the same, then H2 and H1 are at the same level; if they are different, then compare the coding features of each level of parent chapter line of H2 and H1 upwards until a line with the same coding feature H1' is found, and then H2 and H1' are determined to be at the same level; if no line with the same coding feature is found up to the top level, then H2 and H1 are at the same level.

[0071] When there is a list line in the effective data lines between chapter line H1 and chapter line H2, and H1 and H2 do not all have codes, calculate the cosine similarity between H2 and H1 and their parent chapter names at each level in turn, and record the chapter line H1' corresponding to the maximum similarity. If the maximum similarity is greater than the effective threshold, then H2 and H1' are at the same level; otherwise, H2 and H1 are at the same level. The effective threshold is preset.

[0072] In one embodiment, defining chapter line encoding rules using regular expressions can identify complex encoding features.

[0073] In this embodiment, the encoding rule specifically refers to the way a certain encoding feature is expressed, which can be represented by a regular expression. For example, if the encoding of a certain chapter line is "GC 2", then the encoding format is: text + space + number, which is represented by the regular expression "^.*\d+". When identifying and comparing encoding features at the chapter line level, if the regular expression matches successfully, the same encoding feature has been found.

[0074] In one embodiment, in step S6, the hierarchical identification rules for the list rows include:

[0075] Let the current list row be B2, and find the nearest valid data row forward;

[0076] When the current valid data row type is a chapter row, the chapter row is automatically set as the parent of the current list row, that is, the level of the current list row is set to 1;

[0077] When the current valid data row type is a list row, compare the code of the current list row B2 with the previous list row B1. If the code of B2 is the code of B1 plus a valid separator and letters or numbers, then B2 is the next level of B1. If the code of B1 exists, but the code of B2 is empty, then B2 is also the next level of B1. If the code of B1 is not empty, but its quantity column is empty, while the quantity column of B2 is not empty, then B2 is determined to be the next level of B1. In other cases, B2 and B1 are determined to be at the same level.

[0078] In one embodiment, the data type of each data row and the hierarchy of chapter rows and list rows can be manually modified. When manually modified, the manual modification flag of the row is automatically set to true. This flag is used for judgment during incremental recognition. When the re-recognition mode is selected, the flag of all data rows is automatically reset.

[0079] Step S7: Repeat steps S4-S6 to continue recognizing the next row of data until all data rows on the current Sheet page have been recognized and the recognition results are displayed.

[0080] In one embodiment, step S7, displaying the recognition result specifically includes: organizing the recognition result into tree-structured data, wherein each data row object corresponds to a node in the tree structure, and bidirectional positioning functionality is achieved.

[0081] Clicking on a recognized valid data row in an Excel file will automatically locate the corresponding node in the tree structure;

[0082] Clicking on a node in the tree structure will automatically locate the corresponding data row in the Excel file.

[0083] Step S8: Find the next valid Sheet page, repeat steps S2-S7 until all valid Sheet pages have been identified, and save the identified data.

[0084] In one embodiment, reference Figure 2 When the sheet format is complex, a method of gradually adding recognition rule items and recognizing multiple times is adopted:

[0085] 1) First, set identification rules for a portion of representative data rows and then identify them. Data rows that do not match the rules are identified as invalid rows.

[0086] 2) Filter out invalid rows, find the valid data rows, and append their features to the recognition rule items of the corresponding data type;

[0087] 3) Repeat steps 1)-2) until the invalid rows no longer contain valid data;

[0088] If manual modifications are made, incremental recognition mode must be used after adding rule items.

[0089] In this embodiment, when the content format of a sheet in an Excel file to be identified is complex, it may not be possible to set all the data row identification rules in one go. A method of gradually adding identification rules and identifying multiple times can be used. Starting from the first row of the data area, select several representative data row features and set the corresponding identification rules. After completion, an identification can be performed. Data rows without identified rules are identified as invalid rows. All invalid rows can be filtered out, and valid data rows that have not had their identification rules correctly set can be found from them. Their features are added to the identification rules for the corresponding data type, and identification is performed again. This process is repeated until there are no valid data rows among the filtered invalid rows, at which point the sheet can be considered identified successfully. During this process, the types of some data rows can also be manually modified. If manual modification is performed, incremental identification must be selected after each addition of a new identification rule; otherwise, incremental identification or re-identification can be selected.

[0090] In one embodiment, if a situation arises during the identification process that does not conform to the logical relationship of the data, the identification is stopped and the erroneous data row is marked. The data identification is then performed again after the data row identification rules are adjusted, or the erroneous data row is manually modified.

[0091] In this embodiment, the situation that does not conform to the data logical relationship specifically refers to the situation where, due to incorrect rule settings or manual modification, chapters and lists are at the same level, which does not meet the requirements of the final data result.

[0092] The following describes the specific scheme of this application from another perspective.

[0093] In one embodiment, this application completes the identification of BOQ data according to the following steps:

[0094] 1. Enter recognition mode. Insert a frozen row above the first row of the Excel file to be recognized. This is used to set and display the data column types. Insert two frozen columns to the left of the first column to display the data row types and the hierarchy of lists (BOQ) and headings (Heading).

[0095] 2. Identify the table structure and data columns using keywords. The identification content and method are shown in Table 1 below. Simultaneously, based on the row containing the matching keyword, determine the location area of ​​the entire table header.

[0096] Table 1

[0097]

[0098]

[0099] 3. Set recognition rules for data rows. Define rule items for different data types (BOQ, Note, and Heading). Multiple rule items can be set for each data type, or none can be set. Each rule item consists of data row characteristics and data column characteristic values. For intuitive representation, a two-dimensional table can be used. For example, if the recognition rules for a section (Heading) are: the code and name columns have content, the font is Arial and bold, and all letters are uppercase, this can be represented in two-dimensional table form as shown in Table 2 below. Here, Arial represents the font name (text type), and √ represents "yes" (Boolean type). The values ​​of each rule item must not be repeated; otherwise, unexpected recognition results will occur.

[0100] Table 2

[0101]

[0102] 4. Data row recognition has two modes: incremental recognition and re-recognition. Incremental recognition does not clear manually modified data row types and levels, while re-recognition ignores and clears all manually modified markers on all rows. Locate any sheet page and start from the row after the last row in the header area (i.e., the first row of the data area). For each row of data, if it's in incremental recognition mode, determine if the manually modified marker is true. If it is, skip and continue recognizing the next row; otherwise, begin recognizing. If it's in re-recognition mode, ignore the marker and begin recognizing directly. Recognition method: First, determine if it's an auxiliary data row or an invalid row using keywords. If not, follow the priority principle of data row type recognition, proceeding sequentially according to the list, comments, and chapters. Based on the characteristics of the data row, match the recognition rules set in step 3. If a matching rule is found, set it to the corresponding data row type; otherwise, set it as an invalid row.

[0103] 5. If the current row type is identified as a heading or a list (BOQ), its hierarchy also needs to be identified. If it is a heading, the level of the first heading row is automatically set to 1. The levels of subsequent heading rows are automatically identified and set according to the specific data, following these rules:

[0104] a. If there are no list lines in the valid data lines between chapter line H1 and chapter line H2, and the codes of H1 and H2 are not empty, and they have the same characteristics, such as both being letters (e.g., A, B, etc.), numbers (e.g., 1, 2, etc.), or alphanumeric combinations (e.g., 1A, 2B, etc.), then H2 and H1 are determined to be at the same level (i.e., at the same level); otherwise, H2 is determined to be the next level (i.e., a child level) of H1.

[0105] b. When there are other list rows in the valid data rows between chapter row H1 and chapter row H2, and both H1 and H2 have codes: First, compare the code of H2 with the code of H1. Simultaneously, if H1 has an identified parent chapter (its level is H1's level - 1), continue comparing the code of H2 with the code of H1's parent chapter until the top-level (i.e., level 1) chapter is found. This comparison process will terminate when a row H1' with the same coding characteristics (determination method is the same as 5.a) is found. At this point, H2 and H1' are determined to be at the same level (i.e., at the same level). If no data row with the same coding characteristics is found, H2 and H1 are determined to be at the same level (i.e., at the same level).

[0106] c. When there are other list rows in the valid data rows between chapter line H1 and chapter line H2, and H1 and H2 do not all have codes, the determination is not based on the codes, but on the text similarity of the chapter names. The cosine similarity of the text is calculated, and H2 and H1 and their parent are calculated sequentially (recursively, until the top-level chapter position is found). At the same time, an effective similarity threshold is set (usually 80%). The data row H1' corresponding to the maximum value exceeding the threshold is found, and H2 is set to be at the same level as H1' (i.e., at the same level). If there is no data row exceeding the threshold, H2 and H1 are set to be at the same level.

[0107] The encoding features mentioned in d and a can be defined by regular expressions to define encoding rules, thereby achieving the purpose of recognizing complex encoding features. The encoding rule specifically refers to the way a certain encoding feature is expressed, which can be represented by regular expressions. Common encoding rules are directly built in Table 3 below, which facilitates the quick identification of the same encoding features. If an encoding rule is not built in, it can be added manually.

[0108] Table 3

[0109]

[0110] If the current row type is a list, the hierarchical identification rules are as follows: steps e and f:

[0111] e. When the current valid data row type is a chapter, the chapter row is automatically set as the parent of the current list row, that is, the level of the current list row is 1.

[0112] f. When the current valid data row type is a list, compare the code of the current list B2 with the previous list B1. If the code of B2 is the code of B1 plus a letter or number through a valid separator (such as "." or "-"), for example, if the code of B1 is A.1 and the code of B2 is A.1.a, then B2 is determined to be the next level (i.e., child level) of B1. If the code of B1 is not empty and the code of B2 is empty, then B2 is determined to be the next level (i.e., child level) of B1. If the code characteristics of B1 and B2 do not meet the above conditions, but the quantity column of B1 is empty, but the quantity column of B2 is not empty, then B2 is determined to be the next level (i.e., child level) of B1. In other cases, B2 and B1 are directly determined to be at the same level (i.e., at the same level).

[0113] 6. After identifying the hierarchy of chapters or lists, continue to identify the next line. If the results identified according to the data row identification rules and the above-mentioned hierarchy identification rules do not conform to the data logic relationship, stop the identification and mark the data row with the error. The data row identification rules need to be adjusted and the identification needs to be done again, or the type of the data row with the error needs to be manually modified.

[0114] 7. When the type or level of a data row is manually modified, the manual modification flag for that row is automatically set to true. When the data row recognition rules are adjusted and the recognition is performed again, the data rows with the manual modification flag set to true are automatically skipped, thereby achieving the purpose of incremental recognition. This continues until all rows in the current Sheet page have been recognized. The recognition results are automatically displayed and saved as a tree structure of data row objects.

[0115] 8. Use the above identification method to identify other Sheet pages in sequence until all valid Sheet pages have been identified.

[0116] In one embodiment, a system for recognizing BOQ data in international engineering projects is also provided. The system is divided into: an Excel file display module, a recognition module, and a result display module.

[0117] 1. Excel File Display Module: This module displays all sheets in an Excel file and the data content of each sheet, as well as providing functions for modifying and adjusting the type and hierarchy of data rows and columns. To meet the needs of data cell feature extraction, this module must highly reproduce the original appearance of the Excel file content, especially the cell font (font type) and style (bold / italic / underline / capitalic, etc.), which must be consistent with the original Excel file. It provides the ability to filter data rows based on specified data row types (invalid rows, list rows, comment rows, chapter rows) and specified conditions (e.g., codes or names starting / ending with / containing / not containing a specified string, similar to Excel's data filtering function). This module also includes a sheet selection function to remove invalid sheets that do not need to be identified.

[0118] 2. Recognition Module: This module identifies the table structure and the start and end positions of the header row using built-in recognition keywords and matching methods. Users can adjust and customize the recognition keywords and matching methods for each data column. It provides data row recognition rule settings, displaying each recognition rule item for different data row types (lists, comments, chapters) in a two-dimensional table format. Users can manually set or adjust the feature values ​​in each rule item, and it also provides the function of automatically extracting features of specified data rows by clicking with the mouse. Based on the recognized table structure content, the data row recognition rule settings, and the aforementioned recognition steps, it automatically identifies the type and level of data rows, while also supporting manual modification by the user. When setting, extracting, or modifying the feature values ​​of rule items, it automatically checks for duplicates. The scope of duplicate checks extends beyond a single data type; it also checks between different data types. If duplicates are found, it blocks the process and provides a prompt. It offers both incremental recognition and re-recognition modes to meet the needs of different application scenarios.

[0119] 3. Results Display Module: The identified data rows are organized in a structured data format and displayed in a tree structure. Clicking on a row in the Excel file display module automatically locates the corresponding node in the tree structure, and similarly, clicking on a node in the tree structure automatically locates the corresponding row in the Excel file display module. This facilitates quick checking of the correctness of the recognition results and avoids omissions. In addition, the recognition results can be saved as a binary stream or file in the form of structured data, so as to facilitate the integration of the recognition results with other software or systems.

[0120] In practical applications, for data rows that need to be identified on a specific sheet, the system's data row feature extraction function is used to extract the content or format features of the selected rows, forming rule items for data row identification. Once a new rule item is extracted, identification can be performed. After identification, the data filtering function provided by the system's Excel file display module is used to filter out invalid rows. Features are then extracted from the filtered data rows to form new identification rule items, and identification is performed again. This process is repeated until all valid data rows are identified. If the features of some data rows are unclear or non-standard during this process, their type and level can be determined manually. Incremental identification during subsequent identifications retains the results of manual modifications, thus speeding up the overall identification process. Furthermore, if the type cells of certain rows are marked in red during identification, it indicates a situation that does not conform to the data logic, possibly due to an error in the set row identification rule items. Adjustments are needed before incremental identification is performed again. If the identification rule adjustments are significant, requiring the clearing of all manually adjusted data row types (or levels), a re-identification mode is used.

[0121] This application has at least the following effects:

[0122] 1. The method of identifying the type by judging the content and format characteristics of data rows fully conforms to the data characteristics of international engineering BOQ, and the automatic extraction function of data row characteristics provided by the system greatly improves the identification efficiency.

[0123] 2. The method of identifying data row hierarchy through coding features has good scalability, allowing coding rules to be defined through regular expressions, and can meet the needs of hierarchical identification under complex coding feature conditions.

[0124] 3. The incremental identification method can greatly improve the efficiency of data row identification. By filtering invalid rows multiple times and combining manual judgment, until all valid data rows are identified, the situation of missing some valid data rows is avoided.

[0125] The embodiments provided in this application, in addition to being applied to the scenario described in this application—data recognition in the original BOQ form—can also be applied to data recognition when importing Excel files. In practical applications, when it is necessary to import data from an Excel file into other systems or software, the method of this application can effectively identify the data row types and levels in the Excel file, thereby achieving fast and accurate data import.

[0126] Practice has shown that for conventional international engineering BOQ documents, this invention can complete the data recognition of a sheet page in 1 to 2 minutes. For complex documents, especially those with unclear and non-standard data line characteristics, most data lines can be identified by setting feature items. Combined with manual modification and incremental recognition, it can achieve the effect of fast and accurate recognition of BOQ data.

[0127] The technical features of the above embodiments can be combined in any way. For the sake of brevity, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

Claims

1. A method for identifying BOQ data in international engineering projects, characterized in that, include: Step S1: Determine the Excel file to be identified. Insert a frozen row above the first row of each valid sheet in the Excel file to display the type of each data column. Insert two frozen columns to the left of the first column to display the type and hierarchy of each data row, respectively. Step S2: Locate the target Sheet page, identify the data columns by keywords, and determine the position area of ​​the entire header based on the row where the matching keywords are located; the rest is the data area. Step S3: Select the recognition mode based on the need to retain manually modified content, including incremental recognition and re-recognition; Step S4: Starting from the first row of the data area of ​​the current Sheet page, for each row of data: If it is the incremental recognition mode, first determine whether the manual modification flag is true. If it is, skip and continue to the recognition of the next row; otherwise, execute step S5. If it is the re-recognition mode, ignore the manual modification flag and directly execute step S5. Step S5: First, determine whether the current data row is an auxiliary data row or an invalid row. If so, skip and continue to the next row for identification. If not, in the order of list row, comment row, and chapter row, match identification rule items according to the characteristics of the data row. If a matching rule item is found, set the current data row to the corresponding data row type. If no matching rule item is found, set the current data row to an invalid row. The identification rule items are pre-set for list row, comment row, and chapter row, and each data row type has zero or more identification rule items. Each identification rule item consists of data row characteristics and feature values ​​of data columns. Step S6: If the current data row is identified as a chapter row or a list row, then the hierarchy of the chapter row or list row is further identified based on its encoding features or text similarity rules; wherein, the hierarchy identification rules for list rows include: Let the current list row be B2, and find the nearest valid data row forward; When the current valid data row type is a chapter row, the chapter row is automatically set as the parent of the current list row, and the level of the current list row is set to 1; When the current valid data row type is a list row, compare the code of the current list row B2 with the previous list row B1. If the code of B2 is the code of B1 plus a valid separator and letters or numbers, then B2 is the next level of B1. If the code of B1 exists, but the code of B2 is empty, then B2 is also the next level of B1. If the code of B1 is not empty, but its quantity column is empty, and the quantity column of B2 is not empty, then B2 is determined to be the next level of B1. In other cases, B2 and B1 are determined to be at the same level. Step S7: Repeat steps S4-S6 to continue recognizing the next row of data until all data rows on the current Sheet page have been recognized and the recognition results are displayed. Step S8: Find the next valid Sheet page, repeat steps S2-S7 until all valid Sheet pages have been identified, and save the identified data.

2. The method for identifying BOQ data in international engineering projects according to claim 1, characterized in that, The data row features include content features and format features. The content features are whether the cell contains content, and the format features include font type, whether it is bold, whether it is italic, whether it is underlined, and whether it is all uppercase. The feature values ​​of the data column are used to represent the values ​​of a specified data column in a certain data row feature item.

3. The method for identifying BOQ data in international engineering projects according to claim 1, characterized in that, In step S6, the hierarchical identification rules for chapter lines include: Let the current chapter line be H2, find the nearest chapter line and let it be H1. If no such line is found, the level of H2 will be automatically set to 1. If there are no list lines in the valid data lines between chapter line H1 and chapter line H2, and the codes of H1 and H2 are not empty, and the codes of H1 and H2 have the same coding characteristics, then H2 and H1 are determined to be at the same level; otherwise, H2 is the next level of H1; wherein, the valid data lines refer to all data lines except for auxiliary data lines and invalid lines. When there is a list line in the valid data lines between chapter line H1 and chapter line H2, and both H1 and H2 have codes, compare the coding features of H2 and H1. If they are the same, then H2 and H1 are at the same level; if they are different, then compare the coding features of each level of parent chapter line of H2 and H1 upwards until a line with the same coding feature H1' is found, and then H2 and H1' are determined to be at the same level; if no line with the same coding feature is found up to the top level, then H2 and H1 are at the same level. When there is a list line in the effective data lines between chapter line H1 and chapter line H2, and H1 and H2 do not all have codes, calculate the cosine similarity between H2 and H1 and their parent chapter names at each level in turn, and record the chapter line H1' corresponding to the maximum similarity. If the maximum similarity is greater than the effective threshold, then H2 and H1' are at the same level; otherwise, H2 and H1 are at the same level. The effective threshold is preset.

4. The method for identifying BOQ data in international engineering projects according to claim 3, characterized in that, Complex encoding features can be identified by defining chapter line encoding rules using regular expressions.

5. The method for identifying BOQ data in international engineering projects according to claim 1, characterized in that, When the sheet format is complex, a method of gradually adding recognition rule items and recognizing multiple times is adopted: 1) First, set identification rules for a portion of representative data rows and then identify them. Data rows that do not match the rules are identified as invalid rows. 2) Filter out invalid rows, find the valid data rows, and append their features to the recognition rule items of the corresponding data type; 3) Repeat steps 1)-2) until the invalid rows no longer contain valid data; If manual modifications are made, incremental recognition mode must be used after adding rule items.

6. The method for identifying BOQ data in international engineering projects according to claim 1, characterized in that, If any data does not conform to the logical relationship during the identification process, the identification will be stopped and the erroneous data row will be marked. The data identification will be performed again after the data row identification rules are adjusted, or the erroneous data row will be manually modified.

7. The method for identifying BOQ data in international engineering projects according to claim 1, characterized in that, Also includes: The data type of each data row, as well as the hierarchy of chapter and list rows, can be manually modified. When manual modification is performed, the manual modification flag for that row is automatically set to true. This flag is used for incremental recognition. When the re-recognition mode is selected, this flag for all data rows is automatically reset.

8. The method for identifying BOQ data in international engineering projects according to claim 1, characterized in that, In step S7, displaying the recognition result specifically includes: organizing the recognition result into tree-structured data, wherein each data row object corresponds to a node in the tree structure, and bidirectional positioning functionality is achieved. Clicking on a recognized valid data row in an Excel file will automatically locate the corresponding node in the tree structure; Clicking on a node in the tree structure will automatically locate the corresponding data row in the Excel file.

9. A system for identifying BOQ data in international engineering projects, used to implement the method described in any one of claims 1-8, characterized in that, include: The Excel file display module is used to display all the sheets in an Excel file and the data content of each sheet. It provides functions for modifying and adjusting the type and hierarchy of data rows and columns. It supports inserting a frozen row above the first row and a frozen column to the left of the first column of each valid sheet. It provides functions for filtering data rows by specified data row type and specified conditions. It also provides a sheet selection function and supports removing invalid sheets that do not need to be identified. The recognition module identifies table structures, including header and data areas, using built-in keywords and matching methods. It supports selecting recognition modes based on the need to retain manually modified content, including incremental and re-recognition. Users can adjust and customize the keywords and matching methods for each data column. It provides data row recognition rule settings and displays each recognition rule item for different data row types, allowing users to manually set or adjust each rule item. It also provides automatic extraction of specified data row features. Based on the identified table structure content and data row recognition rule settings, it automatically identifies the data row type and determines the hierarchy of chapter and list rows based on encoding features and text similarity rules, supporting manual modification by the user. When setting, extracting, or modifying feature values ​​for rule items, it automatically checks for duplicates, blocking and prompting if duplicates are found. The results display module organizes the identified data row objects in the form of structured data and displays them in a tree structure. It supports automatically locating the corresponding node in the tree structure when clicking on a row in the Excel file display module, and automatically locating the corresponding row in the Excel file display module when clicking on a node in the tree structure. The recognition results can also be saved in the form of structured data.

Images