Form file generation method and device, electronic equipment and medium

Abstract

The invention provides a form file generation method and device, electronic equipment and a medium. The method comprises the steps that attribute information corresponding to a template field in a form file template uploaded by a generation user and a historical field mapping relation corresponding to the template field are obtained; determining a target rule conforming to the attribute information corresponding to the template field from a pre-established rule base; determining a field mapping relation conclusion in the target rule as an initial field mapping relation corresponding to the template field; determining a target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each historical field mapping relationship and a second similarity between preset field meanings of original fields in every two historical field mapping relationships; and generating a form file corresponding to the form file template according to the target field mapping relation and the original form file. According to the method, the complexity of configuring the field mapping relation can be reduced, the generation efficiency is improved, and the data error rate is reduced.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device, electronic device and medium for generating a table file. Background Art

[0002] With the development of technology, data needs to be presented in tables in various scenarios such as data analysis and reporting, budgeting and financial planning. The traditional method of generating table files is that users manually write data into table files.

[0003] However, the traditional method is time-consuming and laborious to adjust the field mapping when writing, has low generation efficiency, and has a high data error rate in the table file. Summary of the invention

[0004] In view of this, the purpose of the present application is to provide a method, device, electronic device and medium for generating a table file, which can reduce the complexity of the configuration field mapping relationship, improve the generation efficiency and reduce the data error rate.

[0005] In a first aspect, an embodiment of the present application provides a method for generating a table file, the method comprising:

[0006] Obtaining the attribute information corresponding to the template field in the table file template uploaded by the user, and the historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the mapping relationship between the template field and the original field in the original table file;

[0007] Determine the target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; determine the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes an attribute information condition and a field mapping relationship conclusion;

[0008] Determine the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships;

[0009] A table file corresponding to the table file template is generated according to the target field mapping relationship and the original table file.

[0010] In a possible implementation, the acquiring and generating of attribute information corresponding to a template field in a table file template uploaded by a user includes:

[0011] Extract the name and sample data corresponding to the template field in the table file template;

[0012] Matching the preset regular expression corresponding to each preset field type with the sample data corresponding to the template field to obtain the field type corresponding to the template field; the preset field type is obtained by dividing according to the purpose of the data in the original table file;

[0013] Inputting the name and field type corresponding to the template field into the field meaning analysis model corresponding to the field to obtain the field meaning corresponding to the template field;

[0014] The name, the field type and the field meaning corresponding to the template field are determined as the attribute information corresponding to the template field.

[0015] In a possible implementation, determining the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between preset field meanings of the original fields in each two of the historical field mapping relationships includes:

[0016] Determine a preset number of historical field mapping relationships with the highest first similarity as first recommended field mapping relationships;

[0017] Determine a second recommended field mapping relationship from two first recommended field mapping relationships whose second similarity is less than a preset similarity;

[0018] Determine the initial field mapping relationship and two first recommended field mapping relationships whose second similarity is greater than or equal to the preset similarity as the second recommended field mapping relationship;

[0019] The second recommended field mapping relationship is sent to the generating user, so that the generating user determines the target field mapping relationship from the second recommended field mapping relationship or manually inputs the target field mapping relationship corresponding to the template field.

[0020] In a possible implementation manner, sending the second recommended field mapping relationship to the generating user includes:

[0021] sorting all second recommended field mapping relationships according to the first similarity to obtain a mapping list; the greater the first similarity corresponding to the second recommended field mapping relationship, the earlier the position in the mapping list;

[0022] The mapping list is sent to the generating user.

[0023] In a possible implementation, the method further includes:

[0024] If the target field mapping relationship is not in the second recommended field mapping relationship, the target field mapping relationship is written into the first mapping relationship database and the second mapping relationship database; the first mapping relationship database is a cache database; the second mapping relationship database is a long-term storage database.

[0025] In a possible implementation, the method further includes:

[0026] Acquire the latest field mapping relationship within a most recent preset period from the second mapping relationship database according to a preset period;

[0027] The rules in the rule base are updated according to the latest field mapping relationship.

[0028] In a possible implementation manner, updating the rules in the rule base according to the latest field mapping relationship includes:

[0029] If the rule base stores a to-be-updated rule corresponding to the generating user and the template field in the latest field mapping relationship, then the original field in the field mapping relationship conclusion in the to-be-updated rule is updated to the original field in the latest field mapping relationship;

[0030] If the rule base does not store the to-be-updated rules corresponding to the generated user and the template field in the latest field mapping relationship, generate the latest rules based on the attribute information of the template field in the latest field mapping relationship and the latest field mapping relationship; and store the latest rules in the rule base.

[0031] In a second aspect, an embodiment of the present application further provides a device for generating a table file, the device comprising:

[0032] An acquisition module, used to acquire the attribute information corresponding to the template field in the table file template uploaded by the user, and the historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the mapping relationship between the template field and the original field in the original table file;

[0033] A determination module is used to determine the target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; determine the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes an attribute information condition and a field mapping relationship conclusion;

[0034] The determination module is further configured to determine the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships;

[0035] A generation module is used to generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file.

[0036] In a third aspect, an embodiment of the present application further provides an electronic device, comprising: a processor, a storage medium and a bus, wherein the storage medium stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor communicates with the storage medium through the bus, and the processor executes the machine-readable instructions to perform the steps of the method for generating a table file as described in any one of the first aspects.

[0037] In a fourth aspect, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored. When the computer program is executed by a processor, the steps of the method for generating a table file as described in any one of the first aspects are executed.

[0038] The embodiment of the present application provides a method, device, electronic device and medium for generating a table file, the method comprising: obtaining attribute information corresponding to the template field in the table file template uploaded by the generating user, and the historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the mapping relationship between the template field and the original field in the original table file; determining the target rule that the attribute information corresponding to the template field conforms to from a pre-established rule base; determining the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes attribute information conditions and field mapping relationship conclusions; determining the target field mapping relationship corresponding to the template field according to the first similarity between the initial field mapping relationship and each of the historical field mapping relationships, and the second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; generating a table file corresponding to the table file template according to the target field mapping relationship and the original table file. The present application can reduce the complexity of configuring field mapping relationships, improve generation efficiency, and reduce data error rate. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the embodiments will be briefly introduced below. It should be understood that the following drawings only show certain embodiments of the present application and therefore should not be regarded as limiting the scope. For ordinary technicians in this field, other related drawings can be obtained based on these drawings without paying creative work.

[0040] Figure 1 A flow chart showing a method for generating a table file provided in an embodiment of the present application is shown;

[0041] Figure 2 A schematic diagram of a process for generating a table file corresponding to a table file template provided in an embodiment of the present application is shown;

[0042] Figure 3 A schematic diagram showing the structure of a device for generating a table file provided in an embodiment of the present application is shown;

[0043] Figure 4 A schematic structural diagram of an electronic device provided in an embodiment of the present application is shown. DETAILED DESCRIPTION

[0044] To make the purpose, technical scheme and advantages of the embodiments of the present application clearer, the technical scheme in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. It should be understood that the drawings in the present application only serve the purpose of explanation and description and are not used to limit the scope of protection of the present application. In addition, it should be understood that the schematic drawings are not drawn in real proportion. The flowchart used in this application shows the operations implemented according to some embodiments of the present application. It should be understood that the operations of the flowchart can be implemented out of sequence, and the steps without logical context can be reversed in order or implemented simultaneously. In addition, those skilled in the art can add one or more other operations to the flowchart under the guidance of the content of the present application, or remove one or more operations from the flowchart.

[0045] In addition, the described embodiments are only a part of the embodiments of the present application, rather than all the embodiments. The components of the embodiments of the present application described and shown in the drawings here can be arranged and designed in various configurations. Therefore, the following detailed description of the embodiments of the present application provided in the drawings is not intended to limit the scope of the application claimed for protection, but merely represents the selected embodiments of the present application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without making creative work belong to the scope of protection of the present application.

[0046] In order to enable those skilled in the art to use the content of this application, the following implementation is provided in conjunction with the specific application scenario "data processing technology field". For those skilled in the art, the general principles defined herein can be applied to other embodiments and application scenarios without departing from the spirit and scope of this application. Although this application is mainly described around the "data processing technology field", it should be understood that this is only an exemplary embodiment.

[0047] It should be noted that the term "comprising" will be used in the embodiments of the present application to indicate the existence of the features declared thereafter, but does not exclude the addition of other features.

[0048] The following is a detailed description of a method for generating a table file provided in an embodiment of the present application.

[0049] Reference Figure 1 As shown, it is a flow chart of a method for generating a table file provided in an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:

[0050] S101. Obtaining attribute information corresponding to template fields in a table file template uploaded by a user and a historical field mapping relationship corresponding to the template fields.

[0051] In the implementation mode of the present application, the table file template includes at least one template field and corresponding sample data. The attribute information corresponding to the template field is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the historical mapping relationship between the template field and the original field in the original table file. If the first mapping relationship database contains a field mapping relationship, the field mapping relationship in the first mapping relationship database is determined as a historical field mapping relationship; if the first mapping relationship database does not contain a field mapping relationship, the field mapping relationship in the second mapping relationship database is determined as a historical field mapping relationship.

[0052] The file types of the table file template can be Excel (.xls, .xlsx) and CSV.

[0053] The first mapping relationship database is a cache database; the second mapping relationship database is a long-term storage database.

[0054] Here, the first mapping relationship database uses hash storage, user_id (generated user ID) as the key, template field and mapped_value original field as the value. Example: HSET user:1 "Amount" "Invoice Total". The structure of the second mapping relationship database is designed as: user_id (generated user ID), field_name (template field), mapped_value (original field), timestamp (adjustment time); example data storage: INSERT INTO field_mapping_adjustments (user_id, field_name, mapped_value, timestamp) VALUES (1, 'Amount', 'Invoice Total', '2024-12-04 12:34:56').

[0055] Specifically, the attribute information corresponding to the template field in the table file template uploaded by the user is obtained, including:

[0056] Step 1: Extract the names and sample data corresponding to the template fields in the table file template.

[0057] In the implementation of the present application, Apache POI is used to parse the table file template and extract the column headers (i.e., template fields) and sample data. The full name of Apache POI is "Apache Portable Document Format (PDF) and Office Open XML (OOXML) Project", which is an open source project that provides a Java API to process files in Microsoft Office formats, including but not limited to Excel, Word, PowerPoint, etc.

[0058] Example: The column headers in the table file template are "|Date|Amount|Name|" and the sample data of each column is "|2023-11-19|100.50|John Doe|", then the names corresponding to the extracted template fields are ["Date","Amount","Name"]. The sample data corresponding to the template fields are ["2023-11-19","100.50","John Doe"].

[0059] Step 2: Match the preset regular expression corresponding to each preset field type with the sample data corresponding to the template field to obtain the field type corresponding to the template field; the preset field type is divided according to the purpose of the data in the original table file.

[0060] In the implementation manner of the present application, the preset field type may include various types such as date, amount, text, etc., which are divided according to the purpose of the data in the original table file. The preset regular expression corresponding to the date may be ˋ\d{4}-\d{2}-\d{2}ˋ. The preset regular expression corresponding to the amount may be ˋ^\d+(\.\d{1,2})? $ˋ. The preset field type corresponding to the successfully matched regular expression is determined as the field type corresponding to the template field.

[0061] Step 3: Input the name and field type corresponding to the template field into the field meaning analysis model corresponding to the domain to obtain the field meaning corresponding to the template field.

[0062] In the implementation manner of the present application, the field meaning analysis model is a pre-trained SpaCy model. The SpaCy model is optimized for specific fields (such as finance, law, medical care, etc.), and the field-specific vocabulary and entity recognition model are loaded to obtain a field meaning analysis model corresponding to the specific field. The field meaning analysis model can combine the contextual information of the template field to infer the field meaning and solve the problem of multiple meanings of fields with the same name; use a multilingual model or customize a multilingual training set to process fields in different languages ​​in a global environment to ensure the compatibility of cross-border business. For example, the "Date" field may mean "creation date" in an order and "payment date" in a payment. Therefore, the names and field types corresponding to all template fields are input together into the field meaning analysis model corresponding to the field to obtain the field meaning corresponding to each template field.

[0063] Example key code:

[0064] -NLP semantic analysis: SpaCy model extracts the meaning of fields.

[0065] Model input: Field name: ["Date","Amount","Name"], Field type: ["Date","Amount","Name"].

[0066] Through SpaCy semantic analysis and context inference: "Date" → time dimension (for example, transaction date, creation date); "Amount" → financial amount (such as payment amount, total amount); "Name" → user name (for example, customer name, employee name).

[0067] Model output: ["Date"->transaction date, "Amount"->total financial amount, "Name"->customer name].

[0068] Among them, NLP (Natural Language Processing) is a broad field that involves multiple disciplines such as computer science, artificial intelligence, and linguistics, with the goal of enabling computers to understand, interpret, and generate human language. SpaCy is a specific tool and library for implementing NLP tasks.

[0069] Step 4: Determine the name, field type and field meaning corresponding to the template field as the attribute information corresponding to the template field.

[0070] S102, determining from a pre-established rule base the target rule that the attribute information corresponding to the template field complies with; determining the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes an attribute information condition and a field mapping relationship conclusion.

[0071] In the implementation of the present application, the rule whose attribute information corresponding to the template field meets the attribute information condition is determined as the target rule. In the rule base, there is only one rule corresponding to each template field of each generated user, and the rules in the rule base are used to determine the original field with a mapping relationship with the template field.

[0072] Here, the rule base is a library in the business rule management system Drools. The business rule management system Drools allows developers to separate business rules from application code so that business analysts can also manage and maintain these rules. The rule base includes the rule file (drl format) corresponding to each rule.

[0073] Example of rule:

[0074] rule "Match Date Field"

[0075] when

[0076] Field(name=="Date"||semanticType=="Time")

[0077] then

[0078] FieldMapping.targetField="Transaction Date";

[0079] end

[0080] Among them, "Field(name=="Date"||semanticType=="Time")" is the attribute information condition, and "FieldMapping.targetField="Transaction Date"" is the field mapping relationship conclusion. The above example is used to indicate that when the name of the template field is Date and the field type semanticType is Time, the template field targetField is mapped to the original field Transaction Date.

[0081] Specifically, the process of triggering a rule is to pass the parsed fields as fact objects (Fact) to the rule engine Drools, and Drools generates field mapping according to the rule file.

[0082] Example: - Input template fields: ˋ["Date","Amount","Name"]ˋ, output mapping relationships: Date->Transaction Date, Amount->Transaction Amount, Name->Customer Name.

[0083] S103: Determine a target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each historical field mapping relationship and a second similarity between preset field meanings of the original fields in every two historical field mapping relationships.

[0084] In the implementation mode of the present application, the cosine similarity between the initial field mapping relationship and each historical field mapping relationship is calculated to obtain the first similarity between the initial field mapping relationship and each historical field mapping relationship. For each two historical field mapping relationships, the cosine similarity between the preset field meanings of the original fields in the two historical field mapping relationships is calculated to obtain the second similarity between the preset field meanings of the original fields in the two historical field mapping relationships.

[0085] Example 1, the initial field mapping relationship is A, and the historical field mapping relationships include B, C, and D; then according to the first similarity between A and B, the first similarity between A and C, the first similarity between A and D, the second similarity between the original field in B and the original field in C, the second similarity between the original field in B and the original field in D, and the second similarity between the original field in C and the original field in D, the target field mapping relationship corresponding to the template field is determined.

[0086] Specifically, determining the mapping relationship between the template fields and the target fields includes:

[0087] Step 1: Determine a preset number of historical field mapping relationships with the highest first similarity as first recommended field mapping relationships.

[0088] Example 2, in the scenario of Example 1, the first similarity between A and B is greater than the first similarity between A and C, and the first similarity between A and C is greater than the first similarity between A and D; the preset number is 2, then B and C are determined as the first recommended field mapping relationship.

[0089] Step 2: Determine a second recommended field mapping relationship from two first recommended field mapping relationships whose second similarity is less than a preset similarity.

[0090] In the implementation manner of the present application, the first recommended field mapping relationship with the closest relationship configuration time among the two first recommended field mapping relationships is determined as the second recommended field mapping relationship.

[0091] Example 3, in the scenario of Example 2, the relationship configuration time of B is earlier than the relationship configuration time of C. If the second similarity between the original field in B and the original field in C is less than the preset similarity, C is determined as the second recommended field mapping relationship.

[0092] Step three: determine the initial field mapping relationship and the two first recommended field mapping relationships whose second similarity is greater than or equal to a preset similarity as the second recommended field mapping relationship.

[0093] Example 3, in the scenario of Example 2, if the second similarity between the original field in B and the original field in C is greater than the preset similarity, it means that the field semantics between B and C are not similar, therefore, A, B and C are determined as the second recommended field mapping relationship.

[0094] Here, when generating a user recommendation field mapping relationship, the present application not only considers the attribute information of the template field, but also considers the field meaning of the historical field mapping relationship of the template field, thereby improving the recommendation accuracy and user experience.

[0095] Step 4: Send the second recommended field mapping relationship to the generating user, so that the generating user can determine the target field mapping relationship from the second recommended field mapping relationship or manually enter the target field mapping relationship corresponding to the template field.

[0096] In the implementation mode of the present application, all second recommended field mapping relationships are sorted according to the first similarity to obtain a mapping list; the greater the first similarity corresponding to the second recommended field mapping relationship, the higher the position in the mapping list; the mapping list is sent to the generating user. The generating user can select a field mapping relationship from the second recommended field mapping relationship as the target field mapping relationship, or can execute the target field mapping relationship corresponding to the configuration template field.

[0097] Furthermore, if the target field mapping relationship is not in the second recommended field mapping relationship, the target field mapping relationship is written into the first mapping relationship database and the second mapping relationship database.

[0098] In the implementation of the present application, if the target field mapping relationship is not in the second recommended field mapping relationship, it means that the target field mapping relationship is manually configured by the user when it is generated and is not recommended. Therefore, the target field mapping relationship needs to be written into the first mapping relationship database and the second mapping relationship database to improve the accuracy of the next recommendation.

[0099] Furthermore, the method further comprises: acquiring the latest field mapping relationship within a most recent preset period from the second mapping relationship database according to a preset period; and updating the rules in the rule base according to the latest field mapping relationship.

[0100] In an implementation manner of the present application, if the rule base stores rules to be updated corresponding to the template fields in the generated user and the latest field mapping relationship, the original fields in the field mapping relationship conclusion in the rules to be updated are updated to the original fields in the latest field mapping relationship; if the rule base does not store rules to be updated corresponding to the template fields in the generated user and the latest field mapping relationship, the latest rules are generated based on the attribute information of the template fields in the latest field mapping relationship and the latest field mapping relationship; and the latest rules are stored in the rule base.

[0101] Here, the rules to be updated refer to the rules corresponding to the generated user and template fields in the rule base. If the rule base does not store the rules to be updated corresponding to the generated user and the template fields in the latest field mapping relationship, the attribute information of the template fields in the latest field mapping relationship is used as the attribute information condition, and the latest field mapping relationship is determined as the field mapping relationship conclusion to obtain the latest rules.

[0102] S104: Generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file.

[0103] Reference Figure 2 As shown, it is a schematic diagram of a flow chart of generating a table file corresponding to a table file template provided in an embodiment of the present application. The exemplary steps of the embodiment of the present application are described below:

[0104] S201. Allocate all sharding tasks in the pending execution state to threads in a thread pool at a preset time interval and according to the task execution rate and current state of each thread; the sharding tasks are obtained by sharding the data in the original table file.

[0105] Step 1: Count the total number of rows in the original table file; slice the data in the original table file according to the first preset number of slice rows and the total number of rows in the original table file to obtain at least one slice task and a first range of rows corresponding to each slice task.

[0106] In the implementation manner of the present application, the first preset number of shard rows is achieved through repeated performance testing and adjustment, and can start from a relatively standard value (such as 5000 rows or 10000 rows) and gradually adjust according to performance. If the shard is too large, memory consumption will increase, processing speed may decrease, and parallel processing efficiency will be low; if the shard is too small, although the parallel processing efficiency can be improved, it will lead to increased management and scheduling overhead, and the overall system performance will decrease. The sharding method of the present application not only ensures parallel processing efficiency, but also ensures overall system performance.

[0107] Taking the total number of rows in the original table file as 10,000 as an example, sharding is performed according to the first preset sharding number of 5,000 rows. The first row number corresponding to the first sharding task ranges from 1 to 5,000 rows; the first row number corresponding to the second sharding task ranges from 5,001 to 10,000 rows.

[0108] Here, the pseudo code for counting the total number of rows in the original table file is: 1. Open the original table file: Workbookworkbook = new XSSFWorkbook(inputFile); 2. Get the first Sheet: Sheet sheet = workbook.getSheetAt(0); 3. Traverse each row of the Sheet and count the total number of rows.

[0109] Furthermore, the method further includes storing the shard task identifier, the minimum value in the first row number range, the maximum value in the first row number range, and the execution status in the shard task database. The execution status of the shard task includes three states: completed, pending, and executing.

[0110] Step 2: At a preset time interval, according to the task execution rate and current state of each thread, assign all shard tasks with an execution state of pending to be executed to threads in the thread pool.

[0111] In the implementation manner of the present application, the initial value of the task execution rate of each thread is set to 0. Initialize the thread pool: ExecutorService executor = Executors.newFixedThreadPool (10); query the shard task database for all shard tasks with an execution status to be executed: SELECT * FROM data_shard WHERE status = 'PENDING'; according to the task execution rate, current status, and preset load balancing strategy of each thread, all shard tasks with an execution status to be executed are encapsulated as Runnable and assigned to threads in the thread pool. In Java, Runnable is an interface that belongs to the java.lang package. The Runnable interface allows objects of classes that implement it to be executed by threads. In other words, any instance of a class that implements the Runnable interface can be used as a thread body and thus run in a separate thread.

[0112] Here, the load balancing strategies include: 1. Round robin scheduling: Use round robin to allocate shards to each thread pool or thread in turn, which is suitable for scenarios where the shard task volume is relatively balanced. 2. Weighted round robin: According to the computational workload of the task, the size of the shard, etc., different "weights" are assigned to each thread; thread pools with larger weights will be assigned more shards, while thread pools with smaller weights will process fewer shards. 3. Least connection first: If the workload of each thread pool varies greatly, the "least connection first" strategy can be used to allocate new shards to the thread pool with the least current processing volume.

[0113] In addition, when the query execution status is a shard task to be executed, a priority is set for each shard task according to the data volume, data complexity, and estimated processing time of each shard task; the shard tasks to be executed are placed in the global queue according to the priority, so that the shard tasks to be executed with high priority are executed first.

[0114] For example, a larger shard can be set with a lower priority, while a smaller shard can be set with a higher priority, so that small tasks can be processed first as much as possible to avoid some threads in the thread pool being idle waiting for the large shard to complete.

[0115] S202. When executing each sharding task, each thread reads the data of the i-th page within the first row number range of the sharding task in the original table file; stores the data within the j-th second row number range of the i-th page in the cache; writes each row of data within the j-th second row number range in the cache into the locally stored initial table file in sequence according to the target field mapping relationship; j=j+1; until all the data of the i-th page is written into the initial table file, i=i+1; until all the data within the first row number range of the sharding task is written, stop reading; the initial values ​​of i and j are 0.

[0116] In the implementation mode of the present application, the data in the first row number range of the slicing task in the original table file is paged according to the second preset slicing row number (such as 500 rows); the data on each page is divided according to the third preset slicing row number (such as 100 rows) to obtain at least one second row number range. The third preset slicing row number is less than the second preset slicing row number.

[0117] Here, the data of the i-th page within the first row number range of the shard task in the original table file is read by paging, which ensures the data writing efficiency. The memory usage is reduced by caching the data of each page multiple times before writing.

[0118] Furthermore, the method further comprises: after all data within the j-th second line number range in the cache are written into the initial table file, clearing all data within the j-th second line number range in the cache.

[0119] Furthermore, the method further comprises: after all the data of the i-th page are written into the initial table file, clearing the data in the table database. The table database is used to store the data read from the original table file.

[0120] Furthermore, the method also includes: after all data of the i-th page are written to the initial table file, the i-th page is determined as the latest execution page number of the thread for the shard task; after the shard task is interrupted and restored, data is written to the initial table file starting from the next page of the latest execution page number of the thread for the shard task.

[0121] Here, by recording the latest number of pages executed by the thread for the shard task, the problem of data rewriting caused by task interruption can be avoided.

[0122] Furthermore, the method also includes: after all data of the i-th page are written into the initial table file, determining the maximum content length of each column in the initial table file; adjusting the cell width of each column according to the maximum content length of each column, so that the content display length of each cell in each column accounts for a ratio of the total content length that is greater than a preset ratio.

[0123] In the implementation mode of the present application, the column width is dynamically adjusted according to the column content to improve the readability of the generated table. The cell width of each column is adjusted according to the maximum content length of each non-empty column so that the content display length of each cell in each column accounts for a greater proportion of the total content length than a preset proportion.

[0124] In addition, when adjusting the column width, in addition to considering the text length of the cell, you also need to consider the data format. For example, for date and number columns, you may need to consider their display length after formatting to ensure that the column width can adapt to the formatted data.

[0125] Furthermore, the method also includes: after the thread completes the execution of the slice task, determining the execution status of the slice task as completed; when the thread starts to execute the slice task, determining the execution status of the slice task as being executed.

[0126] Furthermore, after the thread writes a preset number of rows of data into the initial table file, the execution progress of the sharding task is updated (represented by the number of rows). The task execution rate of the thread can be calculated based on the execution progress.

[0127] S203: When the execution status of all sharding tasks is completed, copy the data in all initial table files to the target table files stored locally.

[0128] Furthermore, if the target table file is not encrypted or the encryption duration of the current encryption key is greater than the preset duration, an encryption key is generated; the target table file is encrypted by the encryption key; the identifier of the encryption key and the storage path of the target table file are stored in the key database accordingly; or the identifier of the encryption key corresponding to the storage path of the target table file in the key database is determined as the identifier of the newly generated encryption key.

[0129] Specifically, if the target table file is not encrypted, an AES key is dynamically generated through the Vault API and encrypted using AES-256 (i.e., 256-bit key length). This ensures high-strength encryption protection. API example for obtaining the key: vaultClient.getSecret("encryption_keys / aes-256"); the key is dynamically generated and can be used to encrypt multiple files. Key identifier storage: Each generated key will have a unique key identifier, which is associated with the file path and stored in the MySQL database to ensure the association between the file and the key. Example key database storage: INSERT INTO file_metadata(file_name, key_identifier) ​​VALUES('file.xlsx', 'key123'). Use the JavaCryptography API to set the AES encryption tool, select the appropriate encryption mode, and initialize the key. Code example: Cipher cipher = Cipher.getInstance("AES"); cipher.init(Cipher.ENCRYPT_MODE, secretKey). File encryption block by block: read the file input stream and encrypt it block by block to prevent memory overload, suitable for processing large files. Use CipherOutputStream for encrypted output: FileInputStream fis = new FileInputStream (originalFile); CipherOutputStream cos = new CipherOutputStream (newFileOutputStream (encryptedFile), cipher); byte[] buffer = new byte

[1024] ; intbytesRead; while((bytesRead = fis.read(buffer))! = -1) {cos.write(buffer, 0, bytesRead);} cos.close(); fis.close(). The code to store the encryption key identifier and the storage path of the target table file in the key database is: INSERT INTO file_metadata(file_name, key_identifier) ​​VALUES('file.xlsx', 'key123'.

[0130] Specifically, if the encryption duration of the current encryption key of the target table file is longer than the preset duration, it is necessary to rotate the key regularly to ensure the security of the key. Use the Quartz scheduled task framework to regularly check the validity period of the key, and trigger the scheduled task when the key expires or reaches the rotation period. The scheduled task is started through Quartz and checks whether the key is expired at regular intervals (for example, monthly or quarterly). If it has expired, generate a new key: The code to generate a new AES key is: vaultClient.createSecret("encryption_keys / aes-256-new"), and returns the new key identifier. Update the key identifier in the key database. When the new key is generated, the key identifier associated with the file will need to be updated. Update these metadata to ensure that the file is associated with the new key. Each file corresponds to a key identifier, and we need to replace the old key identifier of the file with the new key identifier. For example, the update statement is as follows: UPDATE file_metadata SET key_identifier = 'new_key_identifier' WHERE file_name = 'file.xlsx'; the above SQL will update the key identifier of the file file.xlsx to the new key identifier, ensuring that the file uses the latest key.

[0131] Here, when the target table file is re-encrypted by using the encryption key, the target table file may be first decrypted by using the old encryption key and then encrypted by using the new encryption key.

[0132] In addition, old keys should not be deleted immediately, but should be kept for a certain period of time to roll back or decrypt historical files. The key version management provided by Vault can help solve this problem. Ensure the security of the key rotation process to prevent key leakage. All sensitive operations (such as key updates) should be performed in a protected environment and access control should be used to limit unnecessary permissions.

[0133] Further, after copying the data in all the initial table files to the locally stored target table files, the method further includes:

[0134] Step 1: Determine the hash value of the target table file as the summary of the target table file.

[0135] In the implementation manner of the present application, the Java Cryptography API is used to calculate the SHA-256 hash value of the file as the file digest.

[0136] Step 2: Digitally sign the summary of the target table file using the RSA private key.

[0137] In the implementation manner of the present application, an RSA private key is used to sign the file hash value to generate a digital signature.

[0138] Step 3: Store the digital signature and the name of the target table file in the digital signature database accordingly.

[0139] Example: `INSERT INTO file_metadata(file_name,digital_signature)VALUES('file.xlsx',base64EncodedSignature);`.

[0140] In the implementation manner of the present application, the RSA private key is dynamically loaded from Vault for signing operations; example: use VaultAPI to obtain the private key: `vaultClient.getSecret("keys / rsa-private")`.

[0141] Key code: MessageDigestdigest=MessageDigest.getInstance("SHA-256"); FileInputStream fis=new FileInputStream(encryptedFile); byte[]buffer=newbyte

[1024] ; int bytesRead; while((bytesRead=fis.read(buffer))!=-1){digest.update(buffer,0,bytesRead);}byte[]fileHash=digest.digest().

[0142] Furthermore, the method further comprises:

[0143] Step 1: In response to a download request initiated by a file download user, the file download user is authenticated.

[0144] In the implementation of the present application, OAuth2 or JWT is used to verify the identity of the file downloading user. The user permission record is queried to verify whether the user has the decryption permission for the target file. Example query: SELECT FROM user_permissionsWHERE user_id=? AND file_name=?.

[0145] Step 2: If the verification is successful, the key identifier of the table file corresponding to the download request is obtained from the key database.

[0146] Step 3: Decrypt the table file according to the key corresponding to the key identifier.

[0147] In the implementation manner of the present application, the corresponding encryption key is obtained from the Vault according to the key identifier, the encrypted file is decrypted using the key, and provided to the user for download.

[0148] Step 4: Send the decrypted table file to the file download user for downloading.

[0149] Further, after copying the data in all the initial table files to the locally stored target table files, the method further includes:

[0150] Step 1: Store the target table file to the preset storage address in the preset main storage bucket of Alibaba Cloud OSSSDK.

[0151] In the implementation mode of the present application, the target table file is uploaded to the designated primary storage bucket using Alibaba Cloud OSS SDK, and a storage address is designated for the target table file. The preset storage address of the target table file can be generated according to the current date or other rules.

[0152] The generated code of the preset storage address is as follows: bucket-name / 2024 / 11 / 26 / generated-file.xlsx.

[0153] In addition, the target table file is copied from the primary storage bucket to the backup storage bucket to complete the backup. The backup storage bucket and the primary storage bucket are set to different geographical regions to improve file security.

[0154] Step 2: Generate a download link with a validity period for the target table file.

[0155] In the implementation mode of the present application, a signed URL is generated for the target table file, and the file downloading user can download the file through the URL, and the link will expire within a specified time.

[0156] Step 3: Store the download link information in the link database.

[0157] In the implementation mode of the present application, in order to implement anti-hotlinking, the download link information needs to be stored in Redis, including the target table file identifier, the identifier of the generating user, the IP address of the generating user and the validity period, etc. This will help to verify during downloading.

[0158] Specifically, obtaining the key identifier of the table file corresponding to the download request from the key database includes:

[0159] Step 1: Verify the download link of the file download user according to the download link information of the table file corresponding to the download request in the link database. Verify whether the file download request is legal, including but not limited to verifying whether the file information exists, the file validity period, the link, the user ID and IP, etc.

[0160] Step 2: If the verification passes, obtain the table file corresponding to the download request from the main storage bucket of Alibaba Cloud OSSSDK, and obtain the key identifier of the table file corresponding to the download request from the key database.

[0161] The method also includes: when the target table file is written, the identification of the generating user, the identification of the target table file, the generation time, and the generation status (success or failure) are stored in the log accordingly; after the target table file is uploaded to the main storage bucket, the identification of the target table file, the file size, the upload time, and the storage path are stored in the log accordingly; after the file downloading user completes downloading the corresponding table file, the identification of the file downloading user, the IP address of the file downloading user, the download time, and the download link verification result are stored in the log accordingly.

[0162] Here, configure the visualization panel to support log queries by time range, task status, and user dimension.

[0163] The method also includes: extracting all log data from the log according to a preset log viewing time interval; sorting the log data of each user according to time; sliding the sorted log data in a time window (10 minutes) to count the number of times the user downloaded files and / or generated files in each time window; if the number of times the user downloaded files and / or generated files is greater than the preset number, an alarm is triggered.

[0164] In an implementation manner of the present application, if the number of file downloads and / or file generation times is greater than a preset number, the behavior is considered abnormal. These abnormal behaviors may be manifestations of malicious operations, abuse of services, or system failures. Once abnormal behavior is detected, the system will trigger an alarm. The alarm can be notified through instant messaging tools (such as WeChat for Enterprise, DingTalk, etc.) to ensure that relevant personnel can handle abnormal situations in a timely manner. Alarm notification: The alarm message will include information such as the user identifier that triggered the exception, the specific circumstances of the abnormal behavior (such as more than 50 downloads within 10 minutes, and the time when the abnormal behavior occurred. The system can push this information to a preset alarm platform (such as WeChat for Enterprise or DingTalk) through HTTP requests so that staff can conduct subsequent inspections and processing.

[0165] The embodiment of the present application provides a method for generating a table file, the method comprising: obtaining attribute information corresponding to the template field in the table file template uploaded by the generating user, and the historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the mapping relationship between the template field and the original field in the original table file; determining the target rule that the attribute information corresponding to the template field conforms to from a pre-established rule base; determining the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes an attribute information condition and a field mapping relationship conclusion; determining the target field mapping relationship corresponding to the template field according to the first similarity between the initial field mapping relationship and each of the historical field mapping relationships, and the second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; generating a table file corresponding to the table file template according to the target field mapping relationship and the original table file. The present application can reduce the complexity of configuring field mapping relationships, improve generation efficiency, and reduce data error rate.

[0166] Based on the same inventive concept, an embodiment of the present application also provides a device for generating a table file corresponding to the method for generating a table file. Since the principle of solving the problem by the device in the embodiment of the present application is similar to the method for generating a table file in the embodiment of the present application, the implementation of the device can refer to the implementation of the method, and the repeated parts will not be repeated.

[0167] Reference Figure 3 FIG. 1 is a schematic diagram of a device for generating a table file provided in an embodiment of the present application, wherein the device for generating a table file includes:

[0168] The acquisition module 301 is used to acquire the attribute information corresponding to the template field in the table file template uploaded by the user, and the historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to the mapping relationship between the template field and the original field in the original table file;

[0169] The determination module 302 is used to determine the target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; determine the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes an attribute information condition and a field mapping relationship conclusion;

[0170] The determination module 302 is further configured to determine the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships;

[0171] The generating module 303 is used to generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file.

[0172] The table file generation device provided in the present application can reduce the complexity of the configuration field mapping relationship, improve the generation efficiency, and reduce the data error rate.

[0173] like Figure 4 As shown, an electronic device 400 provided in an embodiment of the present application includes: a processor 401, a memory 402 and a bus, wherein the memory 402 stores machine-readable instructions executable by the processor 401. When the electronic device is running, the processor 401 communicates with the memory 402 through the bus, and the processor 401 executes the machine-readable instructions to perform the steps of the method for generating a table file as described above.

[0174] Specifically, the memory 402 and the processor 401 can be general-purpose memories and processors, which are not specifically limited here. When the processor 401 runs the computer program stored in the memory 402, the method for generating the table file can be executed.

[0175] Corresponding to the above-mentioned method for generating a table file, an embodiment of the present application further provides a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, the steps of the above-mentioned method for generating a table file are executed.

[0176] Those skilled in the art can clearly understand that, for the convenience and simplicity of description, the specific working process of the system and device described above can refer to the corresponding process in the method embodiment, and will not be repeated in this application. In the several embodiments provided in this application, it should be understood that the disclosed system, device and method can be implemented in other ways. The device embodiments described above are merely schematic. For example, the division of the modules is only a logical function division. There may be other division methods in actual implementation. For example, multiple modules or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some communication interfaces, indirect coupling or communication connection of devices or modules, which can be electrical, mechanical or other forms.

[0177] The modules described as separate components may or may not be physically separated, and the components shown as modules may or may not be physical units, that is, they may be located in one place or distributed on multiple network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0178] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0179] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a non-volatile computer-readable storage medium that is executable by a processor. Based on this understanding, the technical solution of the present application, or the part that contributes to the prior art or the part of the technical solution, can be embodied in the form of a software product, which is stored in a storage medium and includes several instructions for a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the information processing method described in each embodiment of the present application. The aforementioned storage medium includes: various media that can store program codes, such as USB flash drives, mobile hard drives, ROM, RAM, magnetic disks, or optical disks.

[0180] The above are only specific implementations of the present application, but the protection scope of the present application is not limited thereto. Any technician familiar with the 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 method for generating a table file, characterized in that: The method comprises: Obtaining attribute information corresponding to a template field in a table file template uploaded by a user, and a historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to a mapping relationship between the template field and the original field in the original table file; Determine the target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; determine the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes an attribute information condition and a field mapping relationship conclusion; Determine the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; A table file corresponding to the table file template is generated according to the target field mapping relationship and the original table file.

2. The method for generating a table file according to claim 1, characterized in that: The obtaining and generating of attribute information corresponding to the template fields in the table file template uploaded by the user includes: Extract the name and sample data corresponding to the template field in the table file template; Matching the preset regular expression corresponding to each preset field type with the sample data corresponding to the template field to obtain the field type corresponding to the template field; the preset field type is obtained by dividing according to the purpose of the data in the original table file; Inputting the name and field type corresponding to the template field into the field meaning analysis model corresponding to the field to obtain the field meaning corresponding to the template field; The name, the field type and the field meaning corresponding to the template field are determined as the attribute information corresponding to the template field.

3. The method for generating a table file according to claim 1 or 2, characterized in that: The determining, according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between preset field meanings of the original fields in each two of the historical field mapping relationships, a target field mapping relationship corresponding to the template field comprises: Determine a preset number of historical field mapping relationships with the highest first similarity as first recommended field mapping relationships; Determine a second recommended field mapping relationship from two first recommended field mapping relationships whose second similarity is less than a preset similarity; Determine the initial field mapping relationship and two first recommended field mapping relationships whose second similarity is greater than or equal to the preset similarity as the second recommended field mapping relationship; The second recommended field mapping relationship is sent to the generating user, so that the generating user determines the target field mapping relationship from the second recommended field mapping relationship or manually inputs the target field mapping relationship corresponding to the template field.

4. The method for generating a table file according to claim 3, characterized in that: The sending the second recommended field mapping relationship to the generating user includes: sorting all second recommended field mapping relationships according to the first similarity to obtain a mapping list; the greater the first similarity corresponding to the second recommended field mapping relationship, the earlier the position in the mapping list; The mapping list is sent to the generating user.

5. The method for generating a table file according to claim 3, characterized in that: The method further comprises: If the target field mapping relationship is not in the second recommended field mapping relationship, the target field mapping relationship is written into the first mapping relationship database and the second mapping relationship database; the first mapping relationship database is a cache database; the second mapping relationship database is a long-term storage database.

6. The method for generating a table file according to claim 5, characterized in that: The method further comprises: Acquire the latest field mapping relationship within a most recent preset period from the second mapping relationship database according to a preset period; The rules in the rule base are updated according to the latest field mapping relationship.

7. The method for generating a table file according to claim 6, characterized in that: The updating of the rules in the rule base according to the latest field mapping relationship includes: If the rule base stores a to-be-updated rule corresponding to the generating user and the template field in the latest field mapping relationship, then the original field in the field mapping relationship conclusion in the to-be-updated rule is updated to the original field in the latest field mapping relationship; If the rule base does not store the to-be-updated rules corresponding to the generated user and the template field in the latest field mapping relationship, generate the latest rules based on the attribute information of the template field in the latest field mapping relationship and the latest field mapping relationship; and store the latest rules in the rule base.

8. A device for generating a table file, characterized in that: The device comprises: An acquisition module, used to acquire attribute information corresponding to a template field in a table file template uploaded by a user, and a historical field mapping relationship corresponding to the template field; the attribute information is determined based on the purpose and field of the data stored in the template field; the historical field mapping relationship refers to a mapping relationship between the template field and the original field in the original table file; A determination module is used to determine the target rule that the attribute information corresponding to the template field complies with from a pre-established rule base; determine the field mapping relationship conclusion in the target rule as the initial field mapping relationship corresponding to the template field; each rule in the rule base includes an attribute information condition and a field mapping relationship conclusion; The determination module is further configured to determine the target field mapping relationship corresponding to the template field according to a first similarity between the initial field mapping relationship and each of the historical field mapping relationships and a second similarity between the preset field meanings of the original fields in each two of the historical field mapping relationships; A generation module is used to generate a table file corresponding to the table file template according to the target field mapping relationship and the original table file.

9. An electronic device, characterized in that: include: A processor, a storage medium and a bus, wherein the storage medium stores machine-readable instructions executable by the processor, and when the electronic device is running, the processor and the storage medium communicate via the bus, and the processor executes the machine-readable instructions to perform the steps of the method for generating a table file as described in any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the steps of the method for generating a table file according to any one of claims 1 to 7 are executed.

Images