Method, device and equipment for intelligent integration of feishu spreadsheet and system data table

Abstract

The present disclosure provides a method, device and equipment for intelligent integration of Feishu spreadsheet and system data table, the method comprising: in response to a trigger for reading the spreadsheet, loading pre-configured reading task parameters, the reading task parameters comprising a Feishu access token, a sheet name of the spreadsheet and a parameter configuration table preset for the reading task, the parameter configuration table being used to record a first mapping relationship between column headers of the spreadsheet and fields of the system data table; based on the Feishu access token and the sheet name of the spreadsheet, obtaining spreadsheet data and extracting column header fields from the spreadsheet data; through at least one of annotation matching, synonym matching and parameter configuration table matching, constructing a second mapping relationship between the column header fields and the fields of the system data table; and based on the second mapping relationship, writing the spreadsheet data into the system data table. Through the method of the present disclosure, automatic integration between the spreadsheet and the system data table can be achieved.

Description

Technical Field

[0001] This disclosure relates to the field of data processing technology, and in particular to a method, apparatus and device for intelligent integration of Lark spreadsheet and system data table. Background Technology

[0002] As enterprises accelerate their digital transformation, Lark Spreadsheets, with their superior collaboration, real-time sharing, and online editing capabilities, are increasingly becoming the mainstream tool for data aggregation and analysis, gradually replacing traditional local files and significantly improving the efficiency of team collaboration and information flow. More and more companies are incorporating them into the core of their daily operations and advertising data management, further driving the deep application of spreadsheets across departments and systems.

[0003] To improve data utilization efficiency, enterprises often integrate Lark Spreadsheets (hereinafter referred to as spreadsheets) into their own business systems to achieve data synchronization and functional integration. Typical practices include: using Lark API interfaces to read spreadsheet content; building corresponding data table structures and entity classes in the system; manually implementing a one-to-one mapping between spreadsheet column headers and system fields through code; completing data entry, data validation, and update logic; and implementing the spreadsheet's data write-back function, which also requires coding the one-to-one field mapping relationship. Although this method can achieve bidirectional communication, because the mapping configuration relies entirely on manual hard coding, the mapping logic needs to be redeveloped for each new spreadsheet integrated, which not only significantly increases development and testing costs but also severely limits the system's agility in responding to business changes.

[0004] Therefore, how to achieve automatic integration of spreadsheets with business systems has become a key technical problem that urgently needs to be solved by those skilled in the art. Summary of the Invention

[0005] In view of this, this disclosure proposes a method, apparatus and device for intelligent integration of Lark spreadsheets and system data tables, which can realize the automatic integration of spreadsheets and business systems.

[0006] According to a first aspect of this disclosure, a method for intelligent integration of Lark spreadsheets and system data tables is provided, comprising:

[0007] In response to the triggering of reading a spreadsheet, pre-configured reading task parameters are loaded. The reading task parameters include Lark access token, the sheet name of the spreadsheet, and index information of a parameter configuration table preset for the reading task. The parameter configuration table is used to record a first mapping relationship between a specified column header of the spreadsheet and a specified field of the system data table. The index information is used to query the specified first mapping relationship in the parameter configuration table.

[0008] Based on the Lark access token and the sheet name of the spreadsheet, obtain the spreadsheet data and extract the column header fields from the spreadsheet data;

[0009] A second mapping relationship between the column header field and the field of the system data table is constructed by at least one of the following matching operations: annotation matching, synonym matching, and parameter configuration table matching.

[0010] Based on the second mapping relationship, the spreadsheet data is written into the system data table.

[0011] In one possible implementation, determining the second mapping relationship between the column header field and the field of the system data table through at least one matching operation among annotation matching, synonym matching, and parameter configuration table matching includes:

[0012] First, the first batch of matching relationships between the column header fields and the fields of the system data table is determined through the annotation matching;

[0013] Determine whether the first batch of matching relationships covers all the column header fields. If not, determine the second batch of matching relationships between the unmatched column header fields and the fields of the system data table through synonym matching.

[0014] Determine whether the second batch of matching relationships covers all the column header fields. If not, determine the third batch of matching relationships between all unmatched column header fields and the fields of the system data table by matching the parameter configuration table.

[0015] Based on the first batch of matching relationships, the second batch of matching relationships, and the third batch of matching relationships, a second mapping relationship is determined between the column header field and the fields of the system data table.

[0016] In one possible implementation, when writing the spreadsheet data into the system data table based on the second mapping relationship, the following is included:

[0017] Determine whether the target table corresponding to the spreadsheet exists in the system data table;

[0018] If the target table is determined to exist, the spreadsheet data is written to the target table based on the second mapping relationship and the pre-configured data writing strategy.

[0019] In one possible implementation, when writing the spreadsheet data to the target table based on the second mapping relationship and a pre-configured data writing strategy, the following steps are included:

[0020] Perform preprocessing operations on the spreadsheet data to obtain preprocessed spreadsheet data;

[0021] Based on the second mapping relationship and the pre-configured data writing strategy, the pre-processed spreadsheet data is written to the target table.

[0022] In one possible implementation, if it is determined that the target table does not exist, the target table is first created based on the second mapping relationship;

[0023] After the target table is created, the spreadsheet data is written to the target table based on the second mapping relationship and the pre-configured data writing strategy.

[0024] In one possible implementation, the method further includes:

[0025] In response to the triggering of the write-back of the spreadsheet, the pre-configured write-back task parameters are loaded, wherein the write-back task parameters include the Lark access token, the sheet name of the spreadsheet, and the SQL identifier preset for the write-back task.

[0026] Based on the SQL identifier, the target SQL statement is read from the pre-configured SQL configuration table, wherein the SQL configuration table records the mapping relationship between multiple preset SQL statements and their corresponding SQL identifiers;

[0027] Execute the target SQL statement to retrieve query data from the system data table;

[0028] Based on the second mapping relationship, the Lark access token, and the sheet name of the spreadsheet, the query data is written back to the spreadsheet.

[0029] In one possible implementation, the SQL configuration table is configured and adjusted based on a preset SQL configuration interface.

[0030] According to a second aspect of this disclosure, a smart integration device for Lark spreadsheets and system data sheets is provided, comprising:

[0031] The task parameter acquisition module is used to respond to the trigger of reading a spreadsheet and load pre-configured reading task parameters. The reading task parameters include Lark access token, the sheet name of the spreadsheet, and index information of a parameter configuration table preset for the reading task. The parameter configuration table is used to record the first mapping relationship between a specified column header of the spreadsheet and a specified field of the system data table. The index information is used to query the specified first mapping relationship in the parameter configuration table.

[0032] The data reading module is used to obtain spreadsheet data based on the Lark access token and the sheet name of the spreadsheet, and extract column header fields from the spreadsheet data;

[0033] An automatic matching module is used to construct a second mapping relationship between the column header field and the fields of the system data table through at least one of the following matching operations: annotation matching, synonym matching, and parameter configuration table matching;

[0034] The data writing module is used to write the spreadsheet data into the system data table based on the second mapping relationship.

[0035] According to a third aspect of this disclosure, a smart integrated device for Lark spreadsheets and system data sheets is provided, comprising: a processor; a memory for storing processor-executable instructions; wherein the processor is configured to perform the method described in the first aspect of this disclosure.

[0036] This disclosure provides a method, apparatus, and device for intelligent integration of Lark spreadsheets and system data tables. The method includes: responding to a trigger to read the spreadsheet, loading pre-configured read task parameters, wherein the read task parameters include a Lark access token, the sheet name of the spreadsheet, and a parameter configuration table preset for the read task, the parameter configuration table being used to record a first mapping relationship between the column headers of the spreadsheet and the fields of the system data table; obtaining spreadsheet data based on the Lark access token and the sheet name of the spreadsheet, and extracting the column header fields from the spreadsheet data; constructing a second mapping relationship between the column header fields and the fields of the system data table through at least one matching operation among annotation matching, synonym matching, and parameter configuration table matching; and writing the spreadsheet data into the system data table based on the second mapping relationship. This disclosure significantly reduces the complexity of manual configuration through a multi-strategy integrated intelligent mapping mechanism, realizing a "zero-code" or "low-code" integration mapping process, and effectively improving the automation and flexibility of data integration and sharing between spreadsheets and system data tables.

[0037] Other features and aspects of this disclosure will become clear from the following detailed description of exemplary embodiments with reference to the accompanying drawings. Attached Figure Description

[0038] The accompanying drawings, which are included in and form part of this specification, illustrate exemplary embodiments, features, and aspects of this disclosure together with the specification and serve to explain the principles of this disclosure.

[0039] Figure 1 A flowchart illustrating a method for intelligent integration of Lark spreadsheets and system data sheets according to an embodiment of the present disclosure;

[0040] Figure 2 An example flowchart illustrates a method for intelligent integration of Lark spreadsheets and system data sheets according to an embodiment of the present disclosure;

[0041] Figure 3 A schematic block diagram of a smart integration device for Lark spreadsheets and system data sheets according to an embodiment of the present disclosure is shown.

[0042] Figure 4 A schematic block diagram of a smart integrated device for Lark spreadsheets and system data sheets according to an embodiment of the present disclosure is shown. Detailed Implementation

[0043] Various exemplary embodiments, features, and aspects of this disclosure will now be described in detail with reference to the accompanying drawings. The same reference numerals in the drawings denote elements that have the same or similar functions. Although various aspects of the embodiments are shown in the drawings, they are not necessarily drawn to scale unless specifically indicated otherwise.

[0044] The term “exemplary” as used herein means “serving as an example, embodiment, or illustration.” Any embodiment illustrated herein as “exemplary” is not necessarily to be construed as superior to or better than other embodiments.

[0045] Furthermore, to better illustrate this disclosure, numerous specific details are set forth in the following detailed description. Those skilled in the art will understand that this disclosure can be practiced without certain specific details. In some instances, methods, means, components, and circuits well known to those skilled in the art have not been described in detail in order to highlight the main points of this disclosure.

[0046] <Method Implementation>

[0047] Figure 1 This diagram illustrates a flowchart of a smart integration method between Lark spreadsheets and system spreadsheets according to an embodiment of the present disclosure. The method comprises... Figure 2 The implementation of the backend task executor in the process. Figure 2 This diagram illustrates an example flowchart of a smart integration method for Lark spreadsheets and system spreadsheets according to an embodiment of the present disclosure. The example method is... Figure 2 The system implementation consists of a front-end configuration page, Quartz task scheduler, back-end task executor, database, and Lark API. The following section combines... Figure 1 and Figure 2 The method disclosed herein will be described in detail. For example... Figure 1 As shown, the method includes steps S1100-S1400.

[0048] S1100, in response to the trigger of reading the spreadsheet, loads the pre-configured read task parameters for this read task. It should first be noted that the system executing the method disclosed herein provides, for example... Figure 2 The front-end configuration interface shown allows users to configure and save the reading task parameters for this reading task before executing the method disclosed herein. In this way, when step S1100 is executed, the pre-configured reading task parameters can be loaded in response to the triggering of reading the spreadsheet.

[0049] In one possible implementation, the read task parameters may include a Lark access token, the sheet name of the spreadsheet to be read in this read task, and index information of a pre-defined parameter configuration table for the read task. The parameter configuration table records the initial mapping relationship between specified column headers of the spreadsheet and specified fields of the system data table. By using the index information provided in the read task parameters, the specified initial mapping relationship in the parameter configuration table can be located, thus providing a basis for subsequent parameter configuration table matching.

[0050] In one possible implementation, users can also customize the first mapping relationship in the parameter configuration table based on the actual needs of the current reading task through the front-end configuration interface. Each first mapping relationship in the parameter configuration table is recorded in the form of "Key1!Value1", where Key1 represents the specified column header in the spreadsheet, and Value1 represents the specified field in the system data table that it maps to. Key1 can also be used as index information to query this first mapping relationship. This parameter configuration table records the display mapping relationship between specified column headers in the spreadsheet and specified fields in the system data table, facilitating user understanding and configuration. For example, if the "Name" column header in the spreadsheet needs to be mapped to the "name" field in the system data table, then the first mapping relationship "Name!name" is configured in the parameter configuration table; similarly, the first mapping relationship "Age!age" can be configured in the parameter configuration table to map the "Age" column header in the spreadsheet to the "age" field in the system data table. Ultimately, the parameter configuration table can contain multiple such custom mapping relationships.

[0051] In this embodiment, the user can first customize the parameter configuration table according to the specific requirements of this reading task. The defined parameter configuration table will be stored in the database. Figure 2 The database table in the system includes the parameter configuration table. Then, when configuring the parameters for this read task, the index information of the required parameter configuration table is passed in. In this way, when performing parameter configuration table matching in the future, the system can query the database based on the index information to find the corresponding first mapping relationship from the custom parameter configuration table. Based on the first mapping relationship, the specified column header in the spreadsheet is mapped and bound to the specified field in the system data table to generate the second mapping relationship between the two.

[0052] For example, if a parameter configuration table containing "Name!name" and "Age!age" has been configured, and the index information "Name" is passed in the reading task parameters, the system will retrieve the mapping relationship "Name!name" based on "Name" during the matching process, thereby binding the "Name" column header of the spreadsheet with the "name" field of the system data table to form a second mapping relationship.

[0053] This embodiment allows users to flexibly customize mapping rules according to actual needs, and dynamically reference predefined mapping relationships through an indexing mechanism, avoiding hard-coded field binding logic and significantly improving the system's versatility, flexibility and maintainability in the data integration process.

[0054] In one possible implementation, the user can also configure the timing conditions for this read task through the front-end configuration interface. These timing conditions must include at least one of the following: user ID, specified time node for the scheduled task, and a status value of "Enabled". Thus, after the user completes the configuration of the read task parameters, the Quartz task scheduler in the system (see...) will... Figure 2 The system will monitor in real time whether the current status meets the timed driving conditions for this read task. If the timed driving conditions for this read task are met, the system will trigger the automatic reading of the spreadsheet. At this time, the system will respond to the trigger of reading the spreadsheet, load the pre-configured read task parameters, and automatically inject these read task parameters into the specified class and method (based on Java reflection mechanism) to execute the corresponding data reading or write-back logic.

[0055] After loading the reading task parameters for this reading task, step S1200 can be executed to obtain spreadsheet data based on the Lark access token and the spreadsheet sheet name, and extract the column header fields from the spreadsheet data.

[0056] S1300, through at least one of the following matching operations—annotation matching, synonym matching, and parameter configuration table matching—constructs a second mapping relationship between column header fields and fields of the system data table. Specifically, this may include the following steps:

[0057] First, we use annotation matching to determine the initial matching relationships between the column header fields and the fields of the system data table.

[0058] First, it should be noted that each field in the system data tables has accompanying comments to provide detailed explanations of the business meaning of each field. These comments can include at least one of the following: the field's Chinese name, business description / definition, enumeration value description, and data rules or constraints.

[0059] In one possible implementation, the annotation matching process includes the following steps: traversing the column header fields; for each traversed column header field, matching the current column header field with the Chinese names of each field in the system data table one by one; if the Chinese name of a field is exactly the same as the current column header field, then that field is used as the matching field for the current column header field, and a matching relationship is established between that field and the current column header field; after the traversal is complete, all the matching relationships determined during the annotation matching process are used as the first batch of matching relationships between the column header fields of the spreadsheet and the fields of the system data table.

[0060] Second, determine whether the first batch of matching relationships covers all column header fields. If not, determine the second batch of matching relationships between the unmatched column header fields and the fields of the system data table through synonym matching.

[0061] In one possible implementation, the synonym matching process includes the following steps: traversing the unmatched column header fields from the first step; for the current column header field encountered during the traversal, based on a pre-configured thesaurus, sequentially performing synonym matching between the current column header field and the Chinese names of each field in the system data table; if the Chinese name of a field is a synonym of the current column header field, that field is used as the matching field for the current column header field, and a matching relationship is established between that field and the current column header field; after the traversal ends, all matching relationships determined during the synonym matching process are used as the second batch of matching relationships between the column header fields of the spreadsheet and the fields of the system data table.

[0062] Third, determine whether the second batch of matching relationships covers all column header fields. If not, determine the third batch of matching relationships between all unmatched column header fields and fields in the system data table by matching through the parameter configuration table.

[0063] In one possible implementation, the parameter configuration table matching process includes the following steps: traversing the unmatched column header fields from the second step; for each traversed column header field, determining its corresponding index information; using this index information to find the first mapping relationship corresponding to the current column header field in the parameter configuration table; and using this first mapping relationship to determine the matching field of the current column header field in the system data table; after the traversal ends, all matching relationships determined during the parameter configuration table matching process are used as the third batch of matching relationships between the column header fields of the spreadsheet and the fields of the system data table. By using a predefined parameter configuration table, column header matching that cannot be achieved during comment matching and synonym matching can be realized. Thus, the identification and mapping of all column headers in the spreadsheet can be completed.

[0064] In one possible implementation, after performing annotation matching and synonym matching, the unmatched column header fields can be pushed to the user. In this way, the user can customize the mapping fields of the unmatched fields in the system data table and record the mapping relationship in the parameter configuration table. Thus, performing the above operation can complete the matching mapping of all column header fields in the spreadsheet.

[0065] Fourth, based on the first, second, and third batches of matching relationships, a second mapping relationship is determined between the column header fields and the fields of the system data table. Specifically, the union of the first, second, and third batches of matching relationships constitutes the second mapping relationship between the column header fields and the fields of the system data table. That is, the second mapping relationship records all the system data table fields that match all the column header fields in the spreadsheet.

[0066] After obtaining the second mapping relationship, step S1400 can be executed to write the spreadsheet data into the system data table based on the second mapping relationship. Specifically, this may include the following steps: determining whether a target table corresponding to the spreadsheet exists in the system data table. If a target table exists, the spreadsheet data is written into the target table based on the second mapping relationship and a pre-configured data writing strategy. The data writing strategy may include at least one of a deduplication / replacement strategy, a full replacement strategy, and an append insertion strategy. The deduplication / replacement strategy deletes duplicate data by specifying a field and then inserts new data, commonly used for incremental updates; the full replacement strategy clears the existing data in the target table before writing, suitable for daily full refresh scenarios; the append insertion strategy retains the original data in the target table and only appends new data, while also supporting deduplication based on specified fields to prevent duplicate writing. This data writing strategy can also be configured in the read task parameters to allow for flexible adjustment of the data writing strategy.

[0067] In one possible implementation, when writing spreadsheet data to a target table based on a second mapping relationship and a pre-configured data writing strategy, the following steps may be included: performing preprocessing operations on the spreadsheet data to obtain preprocessed spreadsheet data; and writing the preprocessed spreadsheet data to the target table based on the second mapping relationship and the pre-configured data writing strategy. The preprocessing operations may include: special character escaping (to prevent SQL concatenation syntax errors), padding null values ​​with default values ​​or empty strings (to improve data integrity), ensuring data type consistency of numeric fields in the spreadsheet when writing to the target table (to prevent type drift), and at least one of the following SQL injection protection mechanisms. SQL injection protection mechanisms can be implemented by performing regular expression validation on input fields and removing special characters, using parameterized statements to prevent malicious concatenation, or performing whitelist validation, to ensure the security and stability of the data writing process.

[0068] In one possible implementation, if it is determined that the target table does not exist, the target table is first created based on the second mapping relationship; after the target table is created, the spreadsheet data is written to the target table based on the second mapping relationship and the pre-configured data writing strategy.

[0069] In one possible implementation, when creating the target table, it's necessary to first determine if a preset automatic table creation strategy is met. If the strategy is satisfied, the target table is then created. This automatic table creation strategy is only activated when the spreadsheet's column headers are in English or when complete mapping rules for the spreadsheet's column headers are provided. If the column headers are in Chinese and there are no complete mapping rules, the target table's fields will be in Chinese, which is detrimental to subsequent maintenance. Therefore, if the activation conditions for this automatic table creation strategy are not met, a corresponding page will be pushed, forcing the user to configure the mapping rules, and then the target table will be built based on the user-configured mapping rules.

[0070] In one possible implementation, the method may further include the following steps:

[0071] First, in response to the spreadsheet write-back trigger, the pre-configured write-back task parameters are loaded. It's important to note that before executing this step, the user can configure and save the write-back task parameters through the aforementioned front-end configuration interface. This allows the system to respond to the spreadsheet write-back trigger and load the pre-configured parameters. These parameters can include the Lark access token, the spreadsheet sheet name, and a pre-defined SQL identifier for the write-back task (i.e., the unique identifier Key2 corresponding to the target SQL statement in the SQL configuration table).

[0072] Second, based on the SQL identifier, the target SQL statement is read from a pre-configured SQL configuration table. Specifically, the database contains a pre-configured SQL configuration table ( Figure 2 The database table includes the SQL configuration table, which records the mapping relationship between multiple preset SQL statements and their unique identifier Key2. In this step, the system can query the database based on the Key2 value passed in the write-back task parameters to find the corresponding target SQL statement from the SQL configuration table.

[0073] In one possible implementation, the SQL statements recorded in the SQL configuration table can support dynamic parameters (such as time ranges, status conditions, etc.). When the target SQL statement required by the user supports dynamic parameters, in addition to passing the Key2 value of the target SQL statement when configuring the write-back task parameters, it is also necessary to pass the dynamic parameters in the target SQL statement at the same time, thereby realizing the adjustment of the dynamic parameters of the target SQL statement. For example, if the target SQL statement contains the dynamic parameter startDate, then the specific value of startDate needs to be specified in the write-back task parameters, such as "2023-10-01".

[0074] In one possible implementation, the SQL configuration table is configured and adjusted based on a pre-defined SQL configuration interface. Specifically, users can access the SQL configuration interface through the aforementioned front-end configuration interface. After entering the SQL configuration interface, users can add required SQL records, delete unnecessary SQL records, or adjust existing SQL statements in the SQL configuration table to adapt to data query and write-back requirements in different business scenarios.

[0075] Third, execute the target SQL statement to retrieve the query data from the system data table.

[0076] Fourth, based on the second mapping relationship, Lark access token, and the sheet name of the spreadsheet, the query data is written back to the spreadsheet. Specifically, according to the established second mapping relationship, the fields in the query results are mapped to the column headers of the spreadsheet, and the data is written to the corresponding sheet of the specified spreadsheet through the Lark API, completing the data write-back operation.

[0077] In one possible implementation, the user can also configure the timing conditions for this write-back task through the front-end configuration interface. These timing conditions may include at least one of the following: user ID, time interval, and status value. After the user completes the configuration of the parameters for this write-back task, the Quartz task scheduler in the system will monitor in real time whether the current status meets the timing conditions for the write-back task. If the conditions are met, the spreadsheet write-back operation will be automatically triggered.

[0078] In one possible implementation, to enhance system robustness and maintainability, an automatic adaptation awareness and anomaly alerting mechanism is introduced. Specifically, when the column header structure of a spreadsheet changes (such as adding, deleting, or renaming columns), causing the system to fail to recognize certain column headers during the matching process, the system automatically records detailed information about the unmatched column headers (including task identifier, spreadsheet name, sheet name, and field details, etc.) and sends real-time alerts to the task manager or system administrator via email or push notifications according to preset strategies, indicating potential risks of field configuration failures. Simultaneously, the system supports flexible configuration of task execution strategies; users can choose to interrupt task execution when abnormal fields occur or ignore the anomaly and continue processing. All anomaly information is archived in the system log by task, facilitating subsequent troubleshooting, behavior tracing, and system optimization. This effectively prevents potential problems such as data misalignment, missing data, or task execution failures caused by column header changes, comprehensively improving the stability and maintainability of system operation.

[0079] This disclosure provides an intelligent integration method for Lark spreadsheets and system data tables, comprising: responding to a trigger to read the spreadsheet, loading pre-configured read task parameters, wherein the read task parameters include a Lark access token, the sheet name of the spreadsheet, and a parameter configuration table preset for the read task, the parameter configuration table being used to record a first mapping relationship between the column headers of the spreadsheet and the fields of the system data table; obtaining spreadsheet data based on the Lark access token and the sheet name of the spreadsheet, and extracting the column header fields from the spreadsheet data; constructing a second mapping relationship between the column header fields and the fields of the system data table through at least one matching operation among annotation matching, synonym matching, and parameter configuration table matching; and writing the spreadsheet data into the system data table based on the second mapping relationship. This disclosure, through a multi-strategy integrated intelligent mapping mechanism, significantly reduces the complexity of manual configuration, achieves a "zero-code" or "low-code" integration mapping process, and effectively improves the automation and flexibility of data integration and sharing between spreadsheets and system data tables.

[0080] This disclosure, through the aforementioned automatic field mapping mechanism and parameterized configuration capabilities, achieves dynamic intelligent matching and data integration between Lark spreadsheet column headers and system database fields, overcoming the problems of low development efficiency and slow response caused by reliance on manual hard coding as described in the background technology. The system provides a graphical configuration interface and flexible task scheduling support, enabling integration work to be directly operated by system administrators or business personnel. It achieves "zero-code" or "low-code" autonomous integration and automated processing of Lark spreadsheet data, significantly reducing repetitive development and testing costs and improving the system's agility in responding to business changes and overall availability.

[0081] It should be noted that the intelligent integration solution provided in this disclosure is not limited to Lark Spreadsheet. The core ideas of the solution, including intelligent field mapping based on multiple strategies (comments, synonyms, configuration tables), parameterized task configuration, and a unified task scheduling and anomaly monitoring mechanism, are also applicable to other online spreadsheet platforms with open API capabilities, such as Tencent Docs, DingTalk Online Spreadsheet, and WPS Collaborative Spreadsheet. By simply adapting to the API interface specifications of the target platform, this system can achieve core functions such as unified task scheduling, automatic field mapping, data synchronization, and write-back, ensuring good portability and scalability of the overall architecture across different platforms.

[0082] <Device Embodiment>

[0083] Figure 3 A schematic block diagram of a smart integration device for Lark spreadsheets and system spreadsheets according to an embodiment of the present disclosure is shown. Figure 3 As shown, the device 100 includes:

[0084] The task parameter acquisition module 110 is used to respond to the trigger of reading the spreadsheet and load the pre-configured reading task parameters. The reading task parameters include the Lark access token, the sheet name of the spreadsheet, and the index information of the parameter configuration table preset for the reading task. The parameter configuration table is used to record the first mapping relationship between the specified column header of the spreadsheet and the specified field of the system data table. The index information is used to query the specified first mapping relationship in the parameter configuration table.

[0085] The data reading module 120 is used to obtain spreadsheet data based on Lark access token and spreadsheet sheet name, and extract column header fields from the spreadsheet data;

[0086] The automatic matching module 130 is used to construct a second mapping relationship between column header fields and fields of the system data table through at least one of the following matching operations: annotation matching, synonym matching, and parameter configuration table matching.

[0087] The data writing module 140 is used to write spreadsheet data into the system data table based on the second mapping relationship.

[0088] <Equipment Example>

[0089] Figure 4 A schematic block diagram of a smart integrated device for Lark spreadsheets and system spreadsheets according to an embodiment of this disclosure is shown. Figure 4As shown, the intelligent integration device 200 for Lark Spreadsheet and system data sheets includes a processor 210 and a memory 220 for storing executable instructions of the processor 210. The processor 210 is configured to implement any of the aforementioned intelligent integration methods for Lark Spreadsheet and system data sheets when executing the executable instructions.

[0090] It should be noted here that the number of processors 210 can be one or more. Furthermore, the intelligent integration device 200 for Lark spreadsheets and system data sheets in this embodiment may also include an input device 230 and an output device 240. The processors 210, memory 220, input device 230, and output device 240 can be connected via a bus or other means, which are not specifically limited here.

[0091] The memory 220, as a computer-readable storage medium, can be used to store software programs, computer-executable programs, and various modules, such as the program or module corresponding to the intelligent integration method of Lark Spreadsheet and System Data Sheet in this embodiment of the present disclosure. The processor 210 executes various functional applications and data processing of the intelligent integration device 200 of Lark Spreadsheet and System Data Sheet by running the software program or module stored in the memory 220.

[0092] Input device 230 can be used to receive input digital numbers or signals. These signals may include key signals related to user settings and function control of the device / terminal / server. Output device 240 may include a display device such as a screen.

[0093] The various embodiments of this disclosure have been described above. These descriptions are exemplary and not exhaustive, and are not limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of the described embodiments. The terminology used herein is chosen to best explain the principles, practical applications, or technical improvements to the technology in the market, or to enable others skilled in the art to understand the embodiments disclosed herein.

Claims

1. A method for intelligent integration of WeBook spreadsheets with system data tables, characterized in that, Comprise: In response to the trigger of reading the spreadsheet, load the pre-configured read task parameters, wherein the read task parameters include the Feishu access token, the sheet name of the spreadsheet, and the index information of the parameter configuration table preset for the read task, the parameter configuration table is used to record the first mapping relationship between the specified column header of the spreadsheet and the specified field of the system data table, and the index information is used to query the specified first mapping relationship in the parameter configuration table; Based on the Feishu access token and the sheet name of the spreadsheet, the spreadsheet data is obtained, and the column header field is extracted from the spreadsheet data; By at least one of annotation matching, synonym matching and parameter configuration table matching, a second mapping relationship between the column header field and the field of the system data table is constructed. Specifically, first, the first batch of matching relationships between the column header field and the field of the system data table is determined through the annotation matching; whether the first batch of matching relationships covers all the column header fields is judged, and in the case that all the column header fields are not covered, the second batch of matching relationships between the unmatched column header fields and the field of the system data table is determined through synonym matching; whether the second batch of matching relationships covers all the column header fields is judged, and in the case that all the column header fields are not covered, the third batch of matching relationships between all the unmatched column header fields and the field of the system data table is determined through the parameter configuration table matching; based on the first batch of matching relationships, the second batch of matching relationships and the third batch of matching relationships, the second mapping relationship between the column header field and the field of the system data table is determined; Based on the second mapping relationship, the spreadsheet data is written into the system data table.

2. The method of claim 1, wherein, When the spreadsheet data is written into the system data table based on the second mapping relationship, comprising: Determine whether the target table corresponding to the spreadsheet exists in the system data table; In the case that the target table exists, based on the second mapping relationship and the pre-configured data writing strategy, the spreadsheet data is written into the target table.

3. The method of claim 2, wherein, When the spreadsheet data is written into the target table based on the second mapping relationship and the pre-configured data writing strategy, comprising: Perform a preprocessing operation on the spreadsheet data to obtain preprocessed spreadsheet data; Based on the second mapping relationship and the pre-configured data writing strategy, the preprocessed spreadsheet data is written into the target table.

4. The method of claim 2, wherein, In the case that the target table does not exist, first create the target table based on the second mapping relationship; After completing the creation of the target table, the spreadsheet data is written into the target table based on the second mapping relationship and the pre-configured data writing strategy.

5. The method of claim 1, wherein, Also include: In response to the trigger of writing back the spreadsheet, load the pre-configured write-back task parameters, wherein the write-back task parameters include the Feishu access token, the sheet name of the spreadsheet, and the SQL identifier preset for the write-back task; Based on the SQL identifier, the target SQL statement is read from the pre-configured SQL configuration table, wherein the SQL configuration table records the mapping relationship between multiple preset SQL statements and their corresponding SQL identifiers; Execute the target SQL statement to retrieve query data from the system data table; Based on the second mapping relationship, the Lark access token, and the sheet name of the spreadsheet, the query data is written back to the spreadsheet.

6. The method of claim 5, wherein, The SQL configuration table is configured and adjusted based on a preset SQL configuration interface.

7. A smart integration device for Lark spreadsheets and system data tables, characterized in that, include: The task parameter acquisition module is used to respond to the trigger of reading a spreadsheet and load pre-configured reading task parameters. The reading task parameters include Lark access token, the sheet name of the spreadsheet, and index information of a parameter configuration table preset for the reading task. The parameter configuration table is used to record the first mapping relationship between a specified column header of the spreadsheet and a specified field of the system data table. The index information is used to query the specified first mapping relationship in the parameter configuration table. The data reading module is used to obtain spreadsheet data based on the Lark access token and the sheet name of the spreadsheet, and extract column header fields from the spreadsheet data; An automatic matching module is used to construct a second mapping relationship between the column header fields and the fields of the system data table through at least one of the following matching operations: annotation matching, synonym matching, and parameter configuration table matching. Specifically, firstly, annotation matching is used to determine the first batch of matching relationships between the column header fields and the fields of the system data table; then, it is determined whether the first batch of matching relationships covers all the column header fields. If not, synonym matching is used to determine the second batch of matching relationships between the unmatched column header fields and the fields of the system data table; then, it is determined whether the second batch of matching relationships covers all the column header fields. If not, parameter configuration table matching is used to determine the third batch of matching relationships between all the unmatched column header fields and the fields of the system data table; finally, based on the first batch of matching relationships, the second batch of matching relationships, and the third batch of matching relationships, the second mapping relationship between the column header fields and the fields of the system data table is determined. The data writing module is used to write the spreadsheet data into the system data table based on the second mapping relationship.

8. An intelligent integration device for WeBook spreadsheets and system data tables, characterized by, include: processor; Memory used to store processor-executable instructions; The processor is configured to implement the method of any one of claims 1 to 6 when executing the executable instructions.

Images