System and method for generating zero-deployment label template based on office software extension

Abstract

According to the zero-deployment label templated generation system and method based on office software extension, the structured template file and the column binding rule are independently stored in the cloud server, so that a user can multiplex the same template across computers and data sources, and the template multiplexing time can be shortened to be within 1 minute as soon as possible. According to the column binding rule based on the column name instead of the column sequence, the template can automatically adapt to the data source of any column sequence, the data adaptation success rate is greatly increased, and manual intervention is not needed. Furthermore, the user does not need to install an independent client, the data of the cloud server is directly called through the loading item, and cooperation among teams is particularly facilitated. In addition, by means of seamless joint of style deserialization and the original API of the spreadsheet, high template style reduction precision can be achieved, and meanwhile the template rendering speed is greatly increased. And moreover, the maintenance cost of the template can be effectively reduced.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of electronic spreadsheet data processing and office software extension, and particularly relates to a system and method for zero-deployment label template generation based on office software extension. BACKGROUND

[0002] Electronic spreadsheet software (such as Microsoft Excel, WPS table) is widely used in data processing, report generation and label making fields. In the prior art, the main ways for users to make labels or format documents include:

[0003] 1. Traditional template reuse: by manually copying styles or using fixed template files (such as.xltx), the styles and data are stored in a coupled manner. The traditional template file (such as Excel template) binds the styles and data in a single file, lacks an independent storage mechanism for style information, and cannot dynamically match different data source structures, so manual adjustment of cell position or column order is required when used across data sources.

[0004] 2. Script automation (such as VBA macro): batch operations are achieved by writing scripts, but this method depends on programming ability and has high maintenance cost. In addition, existing automation tools (such as mail merge) require the data column order to be strictly consistent with the template preset, and the column binding rule is based on column index rather than semantics (such as column name), resulting in high data mapping vulnerability. If the data source column order changes, it needs to be reconfigured.

[0005] 3. Third-party label software: uses an independently installed dedicated tool (such as Bartender, LabelMatrix), which has high deployment cost, requires additional learning, and cannot be deeply integrated with electronic spreadsheets. Moreover, the template file is stored locally, making version management difficult for team collaboration. SUMMARY

[0006] To overcome the shortcomings of the prior art, the present application aims to provide a system and method for zero-deployment label template generation based on office software extension, which realizes label batch generation with zero client deployment through style and data decoupled storage, independent mapping of column name and column binding rule, and in-host software embedded rendering engine.

[0007] To achieve the above purpose, the present application adopts the following technical solutions:

[0008] The method for zero-deployment label template generation based on office software extension includes the following steps:

[0009] S1, the host software extension module is integrated in the electronic spreadsheet software, the user designs the template of the label by using the rendering engine submodule in the host software extension module, the template is provided with the title content of the label and the cell where the title content is located, the name of each field and the cell where the name is located, and the cell where the data of each field is located; after the user designs the template of the label, the style extractor in the host software extension module generates a structured template file from the template of the label;

[0010] S2, the column binder in the host software extension module is used to bind the cell where the data of each field in the template is located with the column name of each column in the data source;

[0011] The data source pre-stores the name of each field in the label and the corresponding data, wherein the column name of each column is recorded in the first row, each row after the first row corresponds to the data of different labels respectively, and the data of each column in the same row corresponds to the data of each field in the same label respectively;

[0012] S3, a label generation event is triggered, the rendering engine submodule fills the data of each column in each row after the first row in the data source into the cell corresponding to the data of each field in the template according to the column binding rule generated by the column binder, and generates corresponding labels respectively, so that the batch generation of the labels is completed;

[0013] S4, the host software extension module uploads the structured template file and the corresponding column binding rule to the cloud server for storage;

[0014] S5, when the template of the label generated in step S1 needs to be reused, the host software extension module in the current computer is started, and then the corresponding structured template file and column binding rule are loaded from the cloud server, the local rendering engine submodule of the host software extension module in the current computer restores the template of the label by analyzing the loaded structured template file, the column binder of the host software extension module in the current computer is triggered by clicking the template area, after the target data source to be used this time is selected by the column binder, the column binder maps each column in the target data source to the cell corresponding to the data of each field in the template according to the loaded column binding rule; after the user triggers the label generation event, the local rendering engine submodule fills the data of each column in each row after the first row in the target data source into the corresponding cell of the template to generate corresponding labels, so that the batch generation of the labels is completed.

[0015] Further, in step S2, after the user designs the template of the label in the template design interface provided by the rendering engine submodule and clicks the template area in the template design interface to trigger the column binder, the column binder first pops up a data source selection interface, and the user selects the target data source on the data source selection interface; after the user selects the target data source, the column binder extracts the correspondence between each column and the column name of each column in the target data source and returns to the template design interface, and when the user clicks the cell where the data of a certain field is located, the column binder shows the user the correspondence between each column and the column name of each column in the target data source, and the user selects the target correspondence; after the user selects the target correspondence for the cell where the data of each field in the template is located, the column binder forms the column binding rule between the cell where the data of each field in the template is located and the column name of each column in the data source according to the correspondence.

[0016] Further, in step S5, the column names of each column in the target data source to be used this time are hashed and matched with the column names bound to the cells corresponding to the data of each field in the template in the column binding rule, so as to match the cells mapped by each column in the target data source to be used this time.

[0017] Further, in step S1, the process of the style extractor generating the structured template file is that all cells in the template area selected by the user are traversed, and the format information of each cell is structured through the corresponding API in the spreadsheet software, and then the structured template file is obtained by serialization; in step S5, when the local rendering engine submodule parses the structured template file, the structured template file is deserialized, the template style data obtained by deserialization is traversed, and the template of the label is restored by calling the corresponding API in the host software.

[0018] The application also provides a system for realizing the zero-deployment label templatization generation method.

[0019] The host software extension module is integrated in the spreadsheet software as a load item, and includes a style extractor, a column binder, a rendering engine submodule and a local rendering engine submodule;

[0020] The rendering engine submodule is used to provide a template design interface for the user, and the user designs the template of the label on the template design interface;

[0021] The column binder is used to bind the cell where the data of each field in the template is located and the column name of each column in the data source to form a column binding rule when a new template is created, and is used to map each column in the target data source to the cell corresponding to the data of each field in the template according to the existing column binding rule when an existing template is reused;

[0022] The style extractor is used to generate a structured template file from a template of a designed label;

[0023] The local rendering engine submodule is used to parse the structured template file from the cloud server and restore the template of the label, and is used to dynamically generate a new label according to a mapping result of the column binder when reusing an existing template;

[0024] The cloud server is used to store the structured template file and the column binding rule generated by the host software extension module, support multiple users to collaboratively access the stored structured template file and column binding rule, and support version management of the structured template file and the column binding rule.

[0025] The present application has the following advantages:

[0026] 1. The present application stores the structured template file and the column binding rule in the cloud server independently, so that a user can reuse the same template across computers and across data sources, and the template reuse time can be shortened to within 1 minute.

[0027] 2. The column binding rule based on the column name instead of the column sequence enables the template to automatically adapt to a data source with any column sequence, and the data adaptation success rate can be improved to 100%, without manual intervention.

[0028] 3. In the present application, the host software extension module is integrated in the host software through a load item, and the cloud server data is directly called through the load item, so that the user does not need to deploy and install an independent client, which is particularly beneficial for collaboration between teams.

[0029] 4. The present application uses seamless docking of style deserialization and native API of the electronic spreadsheet to achieve 100% template style restoration accuracy, and the template rendering speed can be realized to be ≤1 second per thousand rows of data.

[0030] 5. The present application stores the template of the label and the column binding rule independently of the data file, supports the scenario of one template and multiple data sources, and does not need to maintain the template for each data file, thereby effectively reducing the maintenance cost of the template. DETAILED DESCRIPTION

[0031] Figure 1 is an example diagram of the label template in embodiment 1 of the present application;

[0032] Figure 2 is an example diagram of different labels using the same template in embodiment 1 of the present application;

[0033] Figure 3 is an example diagram of a data source in embodiment 1 of the present application;

[0034] Figure 4Figure 1 is an example of a data source selection interface popped up by the column binder in Example 1 of the present application;

[0035] Figure 5 Figure 2 is an example of selecting a corresponding relationship in a template design interface in Example 1 of the present application;

[0036] Figure 6 Figure 3 is an example of a label template after column binding is completed in Example 1 of the present application;

[0037] Figure 7 Figure 4 is an example of batch generating labels in Example 1 of the present application. DETAILED DESCRIPTION

[0038] The present application will be further described below in conjunction with the accompanying drawings. It should be noted that the present embodiment is based on the technical solution of the present application, and gives a detailed implementation and specific operation process, but the protection scope of the present application is not limited to the present embodiment.

[0039] Example 1

[0040] The present embodiment provides a method for label template generation based on office software extension without deployment, which specifically includes the following steps:

[0041] S1, the host software extension module is integrated in the electronic spreadsheet software, and the user designs a template of the label by using the rendering engine submodule in the host software extension module. The template sets the title content of the label and the cell where it is located, the name of each field and the cell where it is located, and the cell where the data of each field is located, as shown in Figure 1 After the user designs the template of the label, the style extractor in the host software extension module generates a structured template file for the template of the label.

[0042] It should be noted that the template of the label sets the content that is fixed and unchanged in the label, which is generally the title content and the name of the field and the cell where it is located, the cell where the data of each field is located, etc. The data of each field in the label can be changed, as shown in Figure 2 The templates of the three labels shown in Figure 2 are the same, including the title content "material identification card", the name of each field "part number", "part name", "shelf number", "other", and the cell where the data of each field is located, which are the same, and only the specific data of each field is different. It should be noted that Figure 2 in the example, for the field "two-dimensional code", only the data of the field is set, and the name of the field is not displayed separately, i.e. the name of the "two-dimensional code" field does not have a corresponding cell.

[0043] S2, binding the cells where the data of each field in the template is located with the column names of each column in the data source by using the column binder in the host software extension module.

[0044] The data source pre-stores the name of each field in the label and the corresponding data, wherein the columns of the first row record the column names of each column, and each row after the first row corresponds to the data of a different label, and the data of each column in the same row corresponds to the data of each field in the same label. As shown in the data source, Figure 3 The columns of the first row record the column names of each column, which correspond to the names of each field in the label, and the columns of each row starting from the second row record the specific data of each field in the same label.

[0045] Specifically, after the user designs the template of the label in the template design interface provided by the rendering engine submodule, the user clicks the template area in the template design interface to trigger the column binder. The column binder first pops up a data source selection interface, as shown in the data source selection interface, Figure 4 The user selects the target data source (the data source of the present embodiment is a worksheet) on the data source selection interface. After the user selects the target data source, the column binder extracts the correspondence between each column and the column name of each column in the target data source and returns to the template design interface. When the user clicks the cell where the data of each field is located, the column binder displays the correspondence between each column and the column name of each column in the target data source to the user in the form of a drop-down menu (or other display forms), as shown in the data source selection interface, Figure 5 The user selects the target correspondence. After the user selects the target correspondence for the cell where the data of each field in the template is located, the column binder forms the column binding rule between the cell where the data of each field in the template is located and the column name of each column in the data source according to the target correspondence, as shown in the template design interface. Figure 6

[0046] S3, triggering a label generation event, and the rendering engine submodule fills the data of each column of each row after the first row in the data source into the cell corresponding to the data of each field in the template according to the column binding rule generated by the column binder, and generates a corresponding label, thereby completing the batch generation of labels, as shown in the template design interface. Figure 7

[0047] S4, the host software extension module uploads the structured template file and the corresponding column binding rule to the cloud server for storage;

[0048] In the present embodiment, the host software extension module uses WebSocket real-time synchronization to realize data interaction between the cloud server and the host software.

[0049] ​​S5, when the template of the label generated in step S1 needs to be reused (in the original computer or across computers), starting the host software extension module in the current computer, then loading the corresponding structured template file and column binding rule from the cloud server, the local rendering engine submodule of the host software extension module in the current computer restores the template of the label by parsing the loaded structured template file, and triggers the column binder of the host software extension module in the current computer by clicking the template area, and after selecting the target data source to be used this time through the column binder, the column binder maps each column in the target data source to the cell corresponding to the data of each field of the template according to the loaded column binding rule; after the user triggers the label generation event, the local rendering engine submodule fills the data of each column of each row after the first row in the target data source into the corresponding cell of the template to generate the corresponding label, thereby completing the batch generation of the label.

[0050] In this embodiment, in step S1, the process of the style extractor generating the structured template file is: traversing all cells of the template area selected by the user, and structuring the information of each cell through the corresponding API in the spreadsheet software, and then serializing to obtain the structured template file; the information of the cell includes font information (font size, font, color, etc.), alignment, background color, border, row, column, column width, and row height, etc. Through the above process, when the template of the label is reused across computers, the template of the label can be restored by 100% by parsing the corresponding structured template file. Correspondingly, in step S5, when the local rendering engine submodule parses the structured template file, the structured template file is deserialized, and the template style data obtained by deserialization is traversed, and the template of the label is restored by calling the corresponding API in the host software.

[0051] In this embodiment, in step S5, the column binder hashes the column names of each column in the target data source to be used this time and the column names bound to the cells corresponding to the data of each field of the template in the column binding rule, to match the cells mapped by each column in the target data source to be used this time. Hash matching can ensure mapping accuracy.

[0052] Embodiment 2

[0053] The embodiment provides a system for realizing the zero-deployment label templatization generation of the method in embodiment 1, comprising:

[0054] The host software extension module is integrated into the spreadsheet software (such as Excel / WPS) as a load item, and comprises a style extractor, a column binder, a rendering engine submodule and a local rendering engine submodule.

[0055] The rendering engine submodule is configured to provide a template design interface for a user to design a template of the label on the template design interface;

[0056] The column binder is configured to bind cells in which data of each field in the template is located to column names of each column in the data source to form a column binding rule when a new template is created, and to map each column in the target data source to a cell corresponding to data of each field in the template according to the existing column binding rule when an existing template is reused;

[0057] The style extractor is configured to generate a structured template file from the designed template of the label;

[0058] The local rendering engine submodule is configured to parse the structured template file from the cloud server and restore the template of the label, and to dynamically generate a new label by using the target data source according to the mapping result of the column binder when an existing template is reused.

[0059] The cloud server is configured to store the structured template file (JSON / YAML / XML format) and the column binding rule generated by the host software extension module, support multiple users to collaboratively access the stored structured template file and the column binding rule, and support version management of the structured template file and the column binding rule.

[0060] For those skilled in the art, various corresponding changes and modifications can be made according to the above technical solutions and concepts, and all these changes and modifications should be included in the protection scope of the claims of the present application.

Claims

1. A method for zero-deployment tag templating generation based on office software extensions, characterized in that, The method comprises the following steps: S1, the host software extension module is integrated in the electronic spreadsheet software, the user designs the template of the label by using the rendering engine submodule in the host software extension module, the template sets the title content of the label and the cell where the label is located, the name of each field and the cell where the field is located, and the cell where the data of each field is located; after the user designs the template of the label, the style extractor in the host software extension module generates a structured template file from the template of the label; S2, the column binder in the host software extension module binds the cell where the data of each field in the template is located with the column name of each column in the data source; The data source pre-stores the name of each field in the label and the corresponding data, wherein the columns of the first row record the column names of the columns, and each row after the first row corresponds to the data of different labels respectively, and the data of each column in the same row corresponds to the data of each field in the same label respectively; S3, the label generation event is triggered, the rendering engine submodule fills the data of each column of each row after the first row in the data source into the cell corresponding to the data of each field in the template according to the column binding rule generated by the column binder, and generates corresponding labels respectively, thereby completing the batch generation of the labels; S4, the host software extension module uploads the structured template file and the corresponding column binding rule to the cloud server for storage; S5, when the template of the label generated in step S1 needs to be reused, the host software extension module in the current computer is started, and then the corresponding structured template file and column binding rule are loaded from the cloud server, the local rendering engine submodule of the host software extension module in the current computer restores the template of the label by analyzing the loaded structured template file, the column binder of the host software extension module in the current computer is triggered by clicking the template area, and after the target data source for this use is selected by the column binder, the column binder maps each column in the target data source to the cell corresponding to the data of each field in the template according to the loaded column binding rule; After the user triggers the label generation event, the local rendering engine submodule fills the data of each column of each row after the first row in the target data source into the corresponding cell of the template to generate corresponding labels, thereby completing the batch generation of the labels.

2. The method of claim 1, wherein, In step S2, after the user designs the template of the label in the template design interface provided by the rendering engine submodule, the column binder is triggered by clicking the template area in the template design interface; the column binder first pops up a data source selection interface, the user selects the target data source on the data source selection interface; after the user selects the target data source, the column binder extracts the correspondence between each column in the target data source and the column name of each column and returns to the template design interface, the column binder shows the user the correspondence between each column in the target data source and the column name of each column when the user clicks the cell where the data of each field is located, and the user selects the target correspondence; After the user selects the target correspondence for each cell where the data of each field in the template is located, the column binder forms the column binding rule between each cell where the data of each field in the template is located and the column name of each column in the data source.

3. The method of claim 1, wherein, In step S5, the column names of each column in the target data source to be used this time are hashed matched with the column names bound to the cells corresponding to the data of each field of the template in the column binding rule, to match the cells mapped by each column in the target data source to be used this time.

4. The method of claim 1, wherein, In step S1, the process of the style extractor generating the structured template file is: traversing all cells of the template area selected by the user, and structuring the format information of each cell through the corresponding API in the spreadsheet software, and then serializing to obtain the structured template file; in step S5, when the local rendering engine submodule parses the structured template file, the structured template file is deserialized, and the template style data obtained by deserialization is traversed, and the template of the label is restored by calling the corresponding API in the host software.

5. A system for zero-deployment tag templating generation implementing the method of any of claims 1-4, wherein, Comprise: The host software extension module is integrated in the spreadsheet software as a load item, and comprises a style extractor, a column binder, a rendering engine submodule and a local rendering engine submodule; The rendering engine submodule is used to provide a template design interface for the user, and the user designs the template of the label on the template design interface; The column binder is used to bind the cells where the data of each field in the template is located to the column names of each column in the data source to form a column binding rule when a new template is created, and is used to map each column in the target data source to the cells corresponding to the data of each field in the template according to the existing column binding rule when the existing template is reused; The style extractor is used to generate a structured template file for the designed template of the label; The local rendering engine submodule is used to parse the structured template file from the cloud server and restore the template of the label, and is used to dynamically generate a new label using the target data source according to the mapping result of the column binder when the existing template is reused; The cloud server is used to store the structured template file and the column binding rule generated by the host software extension module, supports multiple users to access the stored structured template file and column binding rule, and supports version management of the structured template file and the column binding rule.

Images