An automatic management system for power engineering design data based on VBA
Through the VBA-based power engineering design data automation management system, the power engineering design data is automatically collected and managed, and the problem of inefficient traditional manual operation is solved, efficient and accurate material information management and alternative material recommendations are achieved, and engineering costs are reduced .
Patent Information
- Application Number
- CN202411266849.3
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2024-09-11
- Publication Date
- 2024-11-15
- Estimated Expiration
- 2044-09-11
AI Technical Summary
In power engineering design, traditional data management methods rely on manual operations, are inefficient and prone to errors, and lack effective methods to determine alternative materials, making it difficult to efficiently select and manage materials.
Design a VBA-based power engineering design data automation management system, through data collection unit, data reading unit, database, comparison and update unit, alternative analysis unit, image recognition technology, file analysis technology, string matching algorithm and Database query statements automatically collect and manage material information in drawings, determine alternative materials and add labels.
Implement automated data collection and management, improve data entry efficiency and accuracy, reduce manual operation errors, ensure the completeness and accuracy of material information, provide recommendations for alternative materials, and reduce engineering costs and manpower. cost.
Smart Images

Figure CN118797962B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data management, and in particular to a VBA-based automatic management system for electric power engineering design data. Background Art
[0002] In the field of power engineering design, as the complexity of projects continues to increase, efficient management of design data has become critical. Traditional power engineering design data management methods often rely on manual collection, organization and analysis, which is not only inefficient but also prone to errors and omissions.
[0003] On the one hand, a large number of drawings are generated during the design of power engineering projects, which contain a wealth of key data such as material names and specification information. At present, the collection and management of data in these drawings mainly rely on manual operations, which consumes a lot of time and manpower costs, and it is difficult to ensure the accuracy and timeliness of the data. On the other hand, during the implementation of power engineering projects, the selection and management of materials directly affect the cost and progress of the project. Different materials may have similar properties and specifications and can be substituted for each other in some cases. However, there is currently a lack of an effective method to determine alternative materials. When a certain material is not available or the quantity is insufficient, it is difficult to quickly find a suitable alternative.
[0004] In order to solve the above problems, there is an urgent need for a VBA-based power engineering design data automation management system that can automatically collect data in the drawings, efficiently and accurately manage material detail information, and determine alternative materials through analysis, providing strong support for power engineering design and implementation. Summary of the invention
[0005] The purpose of the present invention is to provide a VBA-based automatic management system for electric power engineering design data, which solves the technical problems raised in the background technology.
[0006] The purpose of the present invention can be achieved through the following technical solutions:
[0007] A VBA-based power engineering design data automation management system, comprising:
[0008] A data collection unit, used to collect all drawings in the power engineering design data;
[0009] A data reading unit, used to read the material name and specification information in the drawing file; in this embodiment, it extracts the text information in the drawing through image recognition technology or file parsing technology, and then analyzes the text to extract the material name and specification information;
[0010] A database is used to store all material details about the power engineering design data; wherein the database stores all material details in a table format; the fields of the table include attribute parameters corresponding to the material name, specification, quantity, and unit in the material details;
[0011] The comparison and update unit is used to compare the extracted material name and specification information with the material detail information already in the database, and update the material detail information in the database based on the comparison result.
[0012] As a further solution of the present invention: wherein, the comparison method is to perform the comparison through a string matching algorithm or a database query statement.
[0013] As a further solution of the present invention: the comparison and update unit is as follows:
[0014] SS1, take a material in the material details in the database as an example:
[0015] Obtain the number of identical characters between the extracted material name and the material details in the database, and count the total number of characters between the extracted material name and the material details;
[0016] At the same time, the number of identical parameters of the extracted specification information and the specification information in the material detail information already in the database is obtained, and the total number of parameters of the extracted specification information and the specification information in the material detail information is counted;
[0017] SS2, by:
[0018] Name similarity = (number of identical characters × 2) / total number of characters
[0019] Specification similarity = (number of identical parameters × 2) / total number of parameters
[0020] Calculate the name similarity and specification similarity of the extracted material name and specification information with the material details information in the database respectively;
[0021] SS3, by:
[0022] Material similarity = (name similarity × α1 + specification similarity × α2) / 2
[0023] Calculate the material similarity between the extracted material name and specification information and the material details information already in the database;
[0024] Among them, α1 and α2 are the corresponding preset weight values;
[0025] SS4, then compare the material similarity with the preset similarity threshold:
[0026] If the material similarity is greater than or equal to the similarity threshold, it is determined that the extracted material name and specification information are the same as the material detailed information already in the database;
[0027] If the material similarity is less than the similarity threshold, it is determined that the extracted material name and specification information are different from the material details already in the database;
[0028] SS5. When the extracted material name and specification information are different from the material details already in the database, it is determined to be a new material, and a new entry is added to the database, and the attribute parameters corresponding to its material name, specification, quantity, and unit are recorded;
[0029] When the extracted material name and specification information are the same as the material details already in the database, it is determined to be an existing material and its quantity information is updated accordingly.
[0030] As a further solution of the present invention: also include:
[0031] The alternative analysis unit is used to analyze the alternative materials for each material in the database; based on the analysis results, the alternative materials are determined and labels are added to the alternative materials; wherein, the alternative materials refer to two or more materials that can replace each other, and their labels are "alternative" or "non-alternative".
[0032] As a further solution of the present invention: wherein, the replaceable materials can also be determined by relevant user input.
[0033] As a further solution of the present invention: the alternative material analysis method is as follows:
[0034] SY1. Select two materials in the database;
[0035] Get the corresponding performance parameters and unit prices of the two materials at the same time;
[0036] Then, the number of identical performance parameters is counted, and then all performance parameters are counted and recorded as the total number of performance parameters;
[0037] Then, the material unit prices of the two materials are extracted, and the absolute value of the difference between the material unit prices of the two materials and the average value of the material unit prices of the two materials are calculated, and they are marked as the unit price difference and the average unit price respectively;
[0038] SY2, by:
[0039] Performance similarity = (number of identical performance parameters × 2) / total number of performance parameters
[0040] Unit price similarity = 1-(unit price difference / average unit price)
[0041] Calculate the performance similarity and unit price similarity of the two materials;
[0042] SY3. Pass:
[0043] Substitutability = (performance similarity × β1 + unit price similarity × β2) / 2
[0044] Calculate the degree of interchangeability between two materials;
[0045] Among them, β1 and β2 are the corresponding preset weight values;
[0046] SY4. Compare the degree of interchangeability of the two materials with the threshold value of interchangeability:
[0047] If the degree of interchangeability is greater than or equal to the threshold value of the degree of interchangeability, the two materials are determined to be interchangeable materials;
[0048] If the degree of interchangeability is less than the threshold value of the degree of interchangeability, it is determined that the two materials are not interchangeable materials;
[0049] SY5. Add corresponding identification for alternative materials in the database.
[0050] As a further solution of the present invention: in the table, a field is used to record the alternative identification of the material.
[0051] As a further solution of the present invention: wherein, when generating a configuration plan, the alternative materials are used, when a user selects a material but the material is not available or the quantity is insufficient, the alternative materials can be recommended based on the identification of the alternative materials.
[0052] Beneficial effects of the present invention:
[0053] In the present invention, the data collection unit can automatically collect all drawings in the power engineering design data without manual search and sorting one by one, which greatly saves time and labor costs; the data reading unit uses image recognition technology or file parsing technology to extract material names and specification information in the drawings, quickly and accurately converts key data in the drawings into a processable form, and improves the efficiency of data entry; the comparison and update unit can timely compare the newly extracted material information with the existing information in the database through an automated comparison and update process, and automatically update the database according to the comparison results, ensuring that the material detail information in the database is always kept up to date, reducing the workload of manual verification and update;
[0054] The present invention can accurately judge whether the extracted material information is the same as the existing information in the database through comparison by using a string matching algorithm or a database query statement and an accurate similarity calculation method, thereby avoiding possible errors in manual judgment. The similarity calculation of the material name and specification information takes into account the number of characters and the number of parameters, more comprehensively measures the similarity of the material information, and improves the accuracy of data comparison. When it is determined to be a new material, a new entry is automatically added to the database, and detailed attribute parameters are recorded, thereby ensuring the integrity and accuracy of the material information in the database.
[0055] In the present invention, the alternative analysis unit can perform alternative material analysis on each material in the database, determine the alternative materials and add identifications, thereby providing more options for generating configuration schemes; the analysis method of the alternative materials comprehensively considers the performance similarity and the unit price similarity, calculates the degree of alternativeness through preset weight values, and can accurately judge the substitutability between materials; the alternative identification of the material is recorded in a field in the table, which is convenient for quickly querying and recommending alternative materials when generating configuration schemes, thereby improving the flexibility and rationality of material configuration.
[0056] The present invention can recommend alternative materials based on the identification of alternative materials when the material selected by the user is unavailable or insufficient, thereby avoiding the impact of material shortages on project progress and improving the reliability and stability of the project; the analysis and identification of alternative materials can help users comprehensively consider performance and price factors when selecting materials, select alternative materials with higher cost performance, and reduce project costs.
[0057] The present invention has an automated management system that reduces manual operation errors and repetitive work, improves work efficiency, and indirectly reduces labor costs and management costs; the database stores material detail information in a tabular form with a clear structure and is easy to maintain and query; independent alternative analysis units and alternative material identifiers make database maintenance more convenient, and the information on alternative materials can be updated at any time according to actual conditions; users can also determine alternative materials by input, which increases the flexibility and scalability of the system and meets the needs of different users. BRIEF DESCRIPTION OF THE DRAWINGS
[0058] The present invention will be further described below in conjunction with the accompanying drawings.
[0059] Figure 1 The present invention discloses a system block diagram of an automatic management system for electric power engineering design data based on VBA.
[0060] Figure 2 It is a flow chart of a comparison and update unit in a VBA-based automatic management system for electric power engineering design data of the present invention.
[0061] Figure 3 The present invention is a flowchart of an alternative analysis unit in a VBA-based automatic management system for electric power engineering design data. DETAILED DESCRIPTION
[0062] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0063] Embodiment 1
[0064] See also Figure 1 and Figure 3 As shown, the present invention is a VBA-based power engineering design data automation management system, comprising:
[0065] A data collection unit, used to collect all drawings in the power engineering design data;
[0066] A data reading unit, used to read the material name and specification information in the drawing file; in this embodiment, it extracts the text information in the drawing through image recognition technology or file parsing technology, and then analyzes the text to extract the material name and specification information;
[0067] Database for storing all material details about power engineering design data;
[0068] In this embodiment, the database stores all material detail information in a table format; the fields of the table include attribute parameters corresponding to the material name, specification, quantity, and unit in the material detail information;
[0069] A comparison and update unit, used to compare the extracted material name and specification information with the material detailed information already in the database, and update the material detailed information in the database according to the comparison result;
[0070] The specific method is as follows:
[0071] In this embodiment, the comparison is performed through a string matching algorithm or a database query statement, and according to the comparison result:
[0072] SS1. Count the number of identical characters and parameters in the name and specification
[0073] Take a material in the material details information already in the database as an example:
[0074] Obtain the number of identical characters between the extracted material name and the material details in the database, and count the total number of characters between the extracted material name and the material details;
[0075] At the same time, the number of identical parameters of the extracted specification information and the specification information in the material detail information already in the database is obtained, and the total number of parameters of the extracted specification information and the specification information in the material detail information is counted;
[0076] SS2, calculate name similarity and specification similarity
[0077] pass:
[0078] Name similarity = (number of identical characters × 2) / total number of characters
[0079] Specification similarity = (number of identical parameters × 2) / total number of parameters
[0080] Calculate the name similarity and specification similarity of the extracted material name and specification information with the material details information in the database respectively;
[0081] SS3, Calculate material similarity
[0082] pass:
[0083] Material similarity = (name similarity × α1 + specification similarity × α2) / 2
[0084] Calculate the material similarity between the extracted material name and specification information and the material details information already in the database;
[0085] Among them, α1 and α2 are the corresponding preset weight values;
[0086] SS4, compare material similarity with threshold for judgment
[0087] Compare material similarity to a preset similarity threshold:
[0088] If the material similarity is greater than or equal to the similarity threshold, it is determined that the extracted material name and specification information are the same as the material detailed information already in the database;
[0089] If the material similarity is less than the similarity threshold, it is determined that the extracted material name and specification information are different from the material details already in the database;
[0090] SS5. Process material information based on the determination results
[0091] When the extracted material name and specification information are different from the material details already in the database, it is determined to be a new material, and a new entry is added to the database, and the attribute parameters corresponding to its material name, specification, quantity, and unit are recorded;
[0092] When the extracted material name and specification information are the same as the material details already in the database, it is determined to be an existing material and its quantity information is updated accordingly;
[0093] For example: if the material name in the drawing is "cable XYZ" and the material name in the database is "cable ABC", the name similarity is 2 / 5;
[0094] Assume that the material specification in the drawing is "10mm², copper core", and the material specification in the database is "10mm², aluminum core", and there is only one parameter that is different, then the specification similarity is 1 / 2;
[0095] Therefore, the similarity of the materials is 0.45. If the similarity threshold is set to 0.5, it is considered that these are two different materials and a new entry needs to be added to the database;
[0096] This embodiment extracts the material name and specification information in the drawing through image recognition or file parsing technology, and compares and updates it with the material details in the database, ensuring that the material information in the database accurately reflects the needs in the actual project and improves the accuracy of material management; it can automatically determine new materials and existing materials, add new entries for new materials, and update quantity information for existing materials, thereby improving the efficiency of material management and reducing the errors and tediousness of manual operations.
[0097] Embodiment 2
[0098] As the second embodiment of the present invention, when the present application is implemented, compared with the first embodiment, the technical solution of the present embodiment is different from that of the first embodiment only in that the present embodiment further includes:
[0099] An alternative analysis unit, used for analyzing alternative materials for each material in the database;
[0100] The specific method is as follows:
[0101] SY1. Statistical performance parameters and calculation of unit price related values
[0102] In the database, select two materials;
[0103] Get the corresponding performance parameters and unit prices of the two materials at the same time;
[0104] Then, the number of identical performance parameters is counted, and then all performance parameters are counted and recorded as the total number of performance parameters;
[0105] Then, the material unit prices of the two materials are extracted, and the absolute value of the difference between the material unit prices of the two materials and the average value of the material unit prices of the two materials are calculated, and they are marked as the unit price difference and the average unit price respectively;
[0106] SY2. Calculate performance similarity and unit price similarity
[0107] pass:
[0108] Performance similarity = (number of identical performance parameters × 2) / total number of performance parameters
[0109] Unit price similarity = 1-(unit price difference / average unit price)
[0110] Calculate the performance similarity and unit price similarity of the two materials;
[0111] SY3. Calculate the degree of substitutability
[0112] pass:
[0113] Substitutability = (performance similarity × β1 + unit price similarity × β2) / 2
[0114] Calculate the degree of interchangeability between two materials;
[0115] Among them, β1 and β2 are the corresponding preset weight values;
[0116] SY4. Compare the degree of alternative use with the threshold value for determination
[0117] The degree of interchangeability of the two materials is compared to the threshold value of interchangeability:
[0118] If the degree of interchangeability is greater than or equal to the threshold value of the degree of interchangeability, the two materials are determined to be interchangeable materials;
[0119] If the degree of interchangeability is less than the threshold value of the degree of interchangeability, it is determined that the two materials are not interchangeable materials;
[0120] Wherein, the replaceable materials refer to two or more materials that can replace each other. In this embodiment, the replaceable materials can also be preset by relevant personnel, or the information of the replaceable materials can be obtained by consulting relevant standards, specifications or empirical data, or the replaceable materials can be determined by user input or historical records in a database;
[0121] SY5. Add alternative logos
[0122] In the database, add corresponding identification for alternative materials;
[0123] In the table, a field is used to record the alternative identification of the material, which is "alternative" or "non-alternative";
[0124] Among them, the alternative materials are used to recommend alternative materials according to the identification of the alternative materials when the user selects a material but the material is not available or the quantity is insufficient when generating a configuration plan;
[0125] For example: Assuming that the performance parameters of two materials are 9 and 10 respectively, and they have 8 identical performance parameters, and the total number of performance parameters is 20, then the performance similarity is 0.8;
[0126] Assuming that the unit prices of the two materials are 100 yuan and 120 yuan respectively, and the average unit price is 110 yuan, then the unit price similarity is 1-|120-100| / 110=0.91;
[0127] Therefore, the degree of material interchangeability is 0.855. If the threshold of the degree of interchangeability is set to 0.8, it is considered that the two materials can replace each other and can be recommended when generating configuration plans;
[0128] This embodiment analyzes the materials for alternative materials and provides users with recommendations for alternative materials when the materials are unavailable or insufficient, thereby increasing the flexibility of material selection and helping to improve the feasibility and efficiency of the project. By considering the performance similarity and unit price similarity of the materials to determine the alternative materials, more appropriately priced materials can be selected while meeting the project requirements, thereby reducing the project cost.
[0129] Embodiment 3
[0130] As the third embodiment of the present invention, when the present application is implemented, compared with the first and second embodiments, the technical solution of this embodiment is to combine the solutions of the first and second embodiments;
[0131] Embodiment 1 mainly collects and extracts information from materials in power engineering design data and compares and updates it with the database to ensure the accuracy of material detail information in the database; Embodiment 2 is based on the accurate material detail database established in Embodiment 1, and performs alternative material analysis on each material in the database to provide recommendations for alternative materials for generating configuration plans; Embodiment 1 provides an accurate material data basis for Embodiment 2, and Embodiment 2 further expands the function of the database and improves the flexibility and cost-effectiveness of materials in actual engineering applications.
[0132] The above formulas are all dimensionless and numerical calculations. The formula is a formula for the most recent real situation obtained by collecting a large amount of data and performing software simulation. The preset parameters and thresholds in the formula are set by technicians in this field according to actual conditions.
[0133] The above is only a specific implementation of the present application, but the protection scope of the present application is not limited thereto. Any person skilled in the art who is familiar with the present technical field can easily think of changes or substitutions within the technical scope disclosed in the present application, which should be included in the protection scope of the present application. Therefore, the protection scope of the present application should be based on the protection scope of the claims.
Claims
1. A VBA-based power engineering design data automation management system, characterized in that: include: A data reading unit, used to read the material name and specification information in the drawing file; It uses image recognition technology or file parsing technology to extract text information from drawings, then analyzes the text and extracts material names and specification information; A database is used to store all material details about the power engineering design data; wherein the database stores all material details in a table format; the fields of the table include attribute parameters corresponding to the material name, specification, quantity, and unit in the material details; A comparison and update unit is used to compare the extracted material name and specification information with the material details information already in the database, and update the material details information in the database according to the comparison result, by calculating the name similarity, specification similarity and material similarity, and then comparing with the corresponding preset thresholds to determine whether the materials are the same, and perform corresponding database update operations, adding new entries for different materials, and updating quantity information for the same materials; A data collection unit, used to collect all drawings in the power engineering design data; An alternative analysis unit, used to analyze the alternative materials for each material in the database, by comparing the performance parameters and unit prices of two materials, calculating the performance similarity and unit price similarity, and obtaining the degree of alternativeness, and then comparing with the corresponding preset threshold value to determine whether the two materials are alternative, and adding a mark to the alternative material; wherein, the alternative material refers to two or more materials that can replace each other, and the mark is "alternative" or "non-alternative"; Alternative material analysis methods are as follows: SY1. Select two materials in the database; At the same time, obtain the corresponding performance parameters and unit prices of the two materials; Then, the number of identical performance parameters is counted, and then all performance parameters are counted and recorded as the total number of performance parameters; Then, the material unit prices of the two materials are extracted, and the absolute value of the difference between the material unit prices of the two materials and the average value of the material unit prices of the two materials are calculated, and they are marked as the unit price difference and the average unit price respectively; SY2, by: Performance similarity = (number of identical performance parameters × 2) / total number of performance parameters Unit price similarity = 1-(unit price difference / average unit price) Calculate the performance similarity and unit price similarity of the two materials; SY3. Pass: Substitutability = (performance similarity × β1 + unit price similarity × β2) / 2 Calculate the degree of interchangeability between two materials; Among them, β1 and β2 are the corresponding preset weight values; SY4. Compare the degree of interchangeability of the two materials with the threshold value of interchangeability: If the degree of interchangeability is greater than or equal to the threshold value of the degree of interchangeability, the two materials are determined to be interchangeable materials; If the degree of interchangeability is less than the threshold value of the degree of interchangeability, it is determined that the two materials are not interchangeable materials; SY5. Add corresponding identification to the alternative materials in the database 。 2. The VBA-based power engineering design data automation management system according to claim 1, characterized in that: in, The comparison method is to use a string matching algorithm or a database query statement to perform the comparison.
3. The VBA-based power engineering design data automation management system according to claim 2 is characterized in that: The comparison and update unit is as follows: SS1. For any material already in the database: Obtain the number of identical characters between the extracted material name and the material details in the database, and count the total number of characters between the extracted material name and the material details; At the same time, the number of identical parameters of the extracted specification information and the specification information in the material detail information already in the database is obtained, and the total number of parameters of the extracted specification information and the specification information in the material detail information is counted; SS2, by: Name similarity = (number of identical characters × 2) / total number of characters Specification similarity = (number of identical parameters × 2) / total number of parameters Calculate the name similarity and specification similarity of the extracted material name and specification information with the material details information in the database respectively; SS3, by: Material similarity = (name similarity × α1 + specification similarity × α2) / 2 Calculate the material similarity between the extracted material name and specification information and the material details information already in the database; Among them, α1 and α2 are the corresponding preset weight values; SS4, then compare the material similarity with the preset similarity threshold: If the material similarity is greater than or equal to the similarity threshold, it is determined that the extracted material name and specification information are the same as the material detailed information already in the database; If the material similarity is less than the similarity threshold, it is determined that the extracted material name and specification information are different from the material details already in the database; SS5. When the extracted material name and specification information are different from the material details already in the database, it is determined to be a new material, and a new entry is added to the database, and the attribute parameters corresponding to its material name, specification, quantity, and unit are recorded; When the extracted material name and specification information are the same as the material details already in the database, it is determined to be an existing material and its quantity information is updated accordingly.
4. The VBA-based power engineering design data automation management system according to claim 1, characterized in that: in, Alternative Materials The alternative materials are also determined by relevant user input.
5. The VBA-based automatic management system for electric power engineering design data according to claim 1, characterized in that: in, In the form, a field is used to record the alternative identification of the material.
6. The VBA-based power engineering design data automation management system according to claim 1, characterized in that: in, Alternative materials are used to recommend alternative materials based on the identifiers of alternative materials when a user selects a material but the material is not available or the quantity is insufficient when generating a configuration plan.
