CN109829144B - Method and device for cross-table referencing of online table - Google Patents
Method and device for cross-table referencing of online table Download PDFInfo
- Publication number
- CN109829144B CN109829144B CN201811627725.8A CN201811627725A CN109829144B CN 109829144 B CN109829144 B CN 109829144B CN 201811627725 A CN201811627725 A CN 201811627725A CN 109829144 B CN109829144 B CN 109829144B
- Authority
- CN
- China
- Prior art keywords
- template
- target
- item
- oid
- online
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides a method and a device for cross-table referencing of an online table, wherein the method comprises the following steps: after detecting the loading instruction, loading an online form different from the local cache from the server, and updating the online form of the local cache by using the loaded online form; and displaying all the updated online forms of the local cache; determining a current table to be operated from the displayed online tables, and determining an active cell of the current table; obtaining a target cross-table function for the active cell; the target cross-table function includes parameter values for the following parameters: the OID number of the target table to be referenced, the target cell area where the data to be referenced are located and operators; calculating the target cross-table function to obtain a calculation result of data to be referenced in a target cell area in the target table; and displaying the calculation result in the active cell to finish the cross-table reference of the target table. By applying the embodiment of the invention, the cross-table quotation efficiency of the online table is improved.
Description
Technical Field
The invention relates to the field of online forms, in particular to a method and a device for cross-table referencing of an online form.
Background
The online form is a form which can be created on a network through devices such as a computer, a mobile phone or a tablet computer, and the like, and can be edited by multiple people at the same time, so that the content of the form has great variability. With the development of business items, completing an item may consist of a large number of online forms, e.g., an item has more than 100 sub-items, each sub-item may contain 300 workbooks, each workbook contains 3 online forms, and then a total of 9 ten thousand online forms need to be made to complete the item. In practical work, the tables have complex audit relations, if the audit relations are 90%, 90% of the tables can be generated by referring to the data of other tables through the tables, if the data processing is not performed through the table-crossing references, 9 ten thousand tables need to be filled in the network, and therefore, it is becoming more and more important to study an online table-crossing table-referencing method.
Existing online form cross-table referencing methods are typically implemented as follows: the method comprises the steps of firstly, making respective tables by workers, then gathering the tables together in the forms of mails or U discs and the like, and then uniformly carrying out data calculation such as summarizing, merging and the like on the tables with the checking relation by the workers so as to finish the cross-table reference of the online tables. In addition, once the staff modifies the data of a certain form, the original form must be replaced, and data calculation such as summarizing and merging needs to be performed again, so that the efficiency of the staff when working with the online form is low.
Disclosure of Invention
The invention aims to overcome the defects of the prior art and provides an online form cross-table quotation method and device so as to improve the online form cross-table quotation efficiency.
The invention is realized in the following way:
in a first aspect, the present invention provides an online form cross-table referencing method, the method comprising:
after detecting the loading instruction, loading an online form different from the local cache from the server, and updating the online form of the local cache by using the loaded online form; and displaying all the updated online forms of the local cache;
determining a current table to be operated from the displayed online tables, and determining active cells of the current table; obtaining a target cross-table function for the active cell; the target cross-table function includes parameter values for the following parameters: the OID number of the target table to be referenced, the target cell area where the data to be referenced are located and operators;
calculating the target cross-table function to obtain a calculation result of data to be referenced in a target cell area in the target table; and displaying the calculation result in the active cell to finish the cross-table reference of the target table.
Optionally, after detecting the load instruction, loading an online form other than the local cache from the server, including:
after the application program opening instruction is obtained, loading an online form different from the local cache from a server; or alternatively, the first and second heat exchangers may be,
after detecting that a refresh button on the displayed interface is clicked, an online form other than the local cache is loaded from the server.
Optionally, the online table includes an item table and a template table, each item table is generated based on one template table, and the OID number of each item table is the same as the OID number of the template table used for generating the item table.
Optionally, loading a presence form other than the local cache from the server, updating the local cache presence form with the loaded presence form, including:
for each locally cached template table, obtaining a local version number and an ID number of the template table; obtaining a source version number of a template table having the ID number from a server; judging whether the local version number is the same as the source version number; if not, pulling the template form with the ID number from the server, and updating the template form by using the pulled template form; if the template tables are the same, the template tables are not pulled from the server;
For each locally cached item table, obtaining a local version number, an ID number and an OID number of the item table; determining a corresponding template table with the OID number and obtaining a source version number of the item table with the ID number from the server; judging whether the local version number and the source version number of the item table are the same; if the item tables are not the same, the item table with the ID number is pulled from the server, the item table which is locally cached is updated by the pulled item table, and the updated item table is used as the item table to be combined; if the item tables are the same, taking the locally cached item table as an item table to be combined; and merging the item table to be merged with the determined corresponding template table, and updating the item table to be merged by using a merging result.
Optionally, determining the corresponding template table with the OID number includes:
obtaining a source version number of a template table with the OID number from a server; judging whether the local version number of the template table with the OID number is the same as the source version number; if the template tables are not the same, the template table with the OID number is pulled from the server, the template table with the OID number which is locally cached is updated by the pulled template table, and the updated template table is determined to be the corresponding template table with the OID number; if the template tables are the same, the locally cached template table with the OID number is determined to be the corresponding template table with the OID number.
Optionally, obtaining a target cross-table function for the active cell includes:
obtaining a cross-table function typed by a user in the active cell as a target cross-table function for the active cell; or alternatively, the process may be performed,
and after obtaining the function name typed by the user in the active cell and the OID number of the selected target table, popping up an event popup window to obtain the target cell area selected by the user through the event popup window, and generating a target cross-table function by using the obtained function name, the OID number of the target table and the target cell area.
Optionally, the parameter format of the target cross-table function is: "[ OID ] shaetindex ] shaetname", cell, wherein OID represents the OID number of the target form to be referenced, shaetindex represents the index number of the target form in which the data to be referenced is located, shaetname represents the name of the target form in which the data to be referenced is located, and cell represents the cell region in which the data to be referenced is located.
Optionally, the loaded online form is a compressed form,
the method further includes decompressing the loaded online form before updating the locally cached online form with the loaded online form;
the updating of the locally cached presence table with the loaded presence table includes updating the locally cached presence table with the decompressed presence table.
Optionally, calculating the target cross-table function to obtain a calculation result of the data to be referenced in the target cell area in the target table, including:
searching a target ID number of an item table generated based on a template table with the OID number;
and determining the item table with the target ID number as a target table, acquiring data to be referenced of a target area in the target table, and calculating the acquired data to be referenced according to the operator to obtain a calculation result.
In a second aspect, the present invention provides an online form cross-form referencing apparatus, the apparatus comprising:
the loading module is used for loading the online form which is different from the local cache from the server after the loading instruction is detected, and updating the online form of the local cache by using the loaded online form; and displaying all the updated online forms of the local cache;
a determining module, configured to determine a current table to be operated from the displayed online tables, and determine an active cell of the current table; obtaining a target cross-table function for the active cell; the target cross-table function includes parameter values for the following parameters: the OID number of the target table to be referenced, the target cell area where the data to be referenced are located and operators;
The calculation module is used for calculating the target cross-table function to obtain a calculation result of data to be referenced in a target cell area in the target table; and displaying the calculation result in the active cell to finish the cross-table reference of the target table.
Optionally, after detecting the load instruction, the loading module loads an online form different from the local cache from the server, specifically:
after the application program opening instruction is obtained, loading an online form different from the local cache from a server; or alternatively, the first and second heat exchangers may be,
after detecting that a refresh button on the displayed interface is clicked, an online form other than the local cache is loaded from the server.
Optionally, the online table includes an item table and a template table, each item table is generated based on one template table, and the OID number of each item table is the same as the OID number of the template table used for generating the item table.
Optionally, the loading module loads an online form different from the local cache from the server, and updates the local cache online form with the loaded online form, specifically:
for each locally cached template table, obtaining a local version number and an ID number of the template table; obtaining a source version number of a template table having the ID number from a server; judging whether the local version number is the same as the source version number; if not, pulling the template form with the ID number from the server, and updating the template form by using the pulled template form; if the template tables are the same, the template tables are not pulled from the server;
For each locally cached item table, obtaining a local version number, an ID number and an OID number of the item table; determining a corresponding template table with the OID number and obtaining a source version number of the item table with the ID number from the server; judging whether the local version number and the source version number of the item table are the same; if the item tables are not the same, the item table with the ID number is pulled from the server, the item table which is locally cached is updated by the pulled item table, and the updated item table is used as the item table to be combined; if the item tables are the same, taking the locally cached item table as an item table to be combined; and merging the item table to be merged with the determined corresponding template table, and updating the item table to be merged by using a merging result.
Optionally, the loading module determines a corresponding template table with the OID number, specifically:
obtaining a source version number of a template table with the OID number from a server; judging whether the local version number of the template table with the OID number is the same as the source version number; if the template tables are not the same, the template table with the OID number is pulled from the server, the template table with the OID number which is locally cached is updated by the pulled template table, and the updated template table is determined to be the corresponding template table with the OID number; if the template tables are the same, the locally cached template table with the OID number is determined to be the corresponding template table with the OID number.
Optionally, the determining module obtains a target cross-table function for the active cell, specifically:
obtaining a cross-table function typed by a user in the active cell as a target cross-table function for the active cell; or alternatively, the process may be performed,
and after obtaining the function name typed by the user in the active cell and the OID number of the selected target table, popping up an event popup window to obtain the target cell area selected by the user through the event popup window, and generating a target cross-table function by using the obtained function name, the OID number of the target table and the target cell area.
Optionally, the parameter format of the target cross-table function is: "[ OID ] shaetindex ] shaetname", cell, wherein OID represents the OID number of the target form to be referenced, shaetindex represents the index number of the target form in which the data to be referenced is located, shaetname represents the name of the target form in which the data to be referenced is located, and cell represents the cell region in which the data to be referenced is located.
Optionally, the loaded online form is a compressed form, and the apparatus further includes a decompression module configured to:
decompressing the loaded online form before updating the locally cached online form with the loaded online form;
The loading module updates the locally cached online form with the loaded online form, specifically updates the locally cached online form with the decompressed online form.
Optionally, the calculating module calculates the target cross-table function to obtain a calculation result of the data to be referenced in the target cell area in the target table, specifically:
searching a target ID number of an item table generated based on a template table with the OID number;
and determining the item table with the target ID number as a target table, acquiring data to be referenced of a target area in the target table, and calculating the acquired data to be referenced according to the operator to obtain a calculation result.
The invention has the following beneficial effects: by the embodiment of the invention, the calculation result of each cell in the target cell area in the target table can be obtained by calculating the target cross-table function, and the calculation result can be displayed in the active cell to finish cross-table reference of the target table, so that the efficiency of on-line table cross-table reference is improved. In addition, after the loading instruction is detected, the online form different from the local cache can be loaded from the server, so that the accuracy and the reliability of the local cache form are improved.
Drawings
In order to more clearly illustrate the embodiments of the invention or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, it being obvious that the drawings in the following description are only some embodiments of the invention, and that other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.
FIG. 1 is a schematic flow chart of an online form cross-table referencing method according to an embodiment of the present invention;
fig. 2 is a schematic structural diagram of an online form cross-table referencing device according to an embodiment of the present invention.
Detailed Description
The following description of the embodiments of the present invention will be made clearly and completely with reference to the accompanying drawings, in which it is apparent that the embodiments described are only some embodiments of the present invention, but not all embodiments. All other embodiments, which can be made by those skilled in the art based on the embodiments of the invention without making any inventive effort, are intended to be within the scope of the invention.
It should be noted that, the method for cross-table referencing of online tables provided by the present invention may be applied to electronic devices, where in specific applications, the electronic devices may be computers, personal computers, tablet computers, mobile phones, etc., which are all reasonable. In addition, the functional software for realizing the online form cross-table referencing method provided by the embodiment of the invention can be special online form cross-table referencing software or can be a plug-in the software with the online form cross-table referencing function. The invention is not limited to the running system in the electronic device, and for example, the running system can be a LINUX operating system, a Windows operating system, a UNIX operating system, or the like.
Referring to fig. 1, an embodiment of the present invention provides an online form cross-table referencing method, including the following steps:
s101, after a loading instruction is detected, loading an online form different from a local cache from a server, and updating the online form of the local cache by using the loaded online form; and displaying all the updated online forms of the local cache;
the present invention is not limited to a specific manner of detecting a load instruction, and may be, for example: after the application program opening instruction is obtained, loading an online form different from the local cache from a server; or, after detecting that a refresh button or a reload button on the displayed interface is clicked, loading an online form different from the local cache from the server; alternatively, each preset fixed duration or after reaching a preset point in time, an online form other than the local cache is loaded from the server.
The electronic device (execution subject of the present invention) may display an icon of an application program for implementing the functions of the present invention, and the user may click or double click on the icon, so that the electronic device may obtain an application program opening instruction, and thus may load an online form different from a local cache from the server. Alternatively, the electronic device may display an interface operable by the user, which may include a refresh button or a reload button, and upon detecting that the user clicks the refresh button or reload button, an online form other than the local cache may be loaded from the server. Loading from the server an online form that is different from the local cache refers to loading from the server an online form that is different from the local cache. The preset fixed time length and the preset time point can be preset according to requirements, for example, the preset time length is 1 hour, 2 hours and the like, and the preset time point can be 9:00, 14:00, 18:00 and the like.
The online forms may include project forms and template forms, each of which may be generated by copying one of the template forms in order to facilitate quick creation of the required project forms. It should be noted that, the template table and the item table of the present invention may include an OID (Object Identifier ) number and an ID (identification) number, where the OID number and the ID number of each template table are the same, the OID number of the item table is the same as the OID number of the template table used to generate the item table, the OID number and the ID number of each item table are different, the ID number of the template table is used to uniquely identify the template table, and the ID number of the item table is used to uniquely identify the item table. The relation between the item table and the template table can be quickly determined through the OID number, so that the item tables generated by the same template table can be quickly determined. The specific form of the ID number and the OID number is not limited in the present invention, and may be, for example, a character string formed by one or a combination of characters such as numerals, letters, and underlines.
The template table may be a pre-constructed basic table, and an item table may be generated based on a template table, and the specific manner of generating the item table based on the template table is not limited in the present invention, for example, the method may include the following steps:
Step one, displaying a template table list on an operation interface, copying the template table selected by a user after detecting the template table selected by the user in the template table list to obtain template data, and generating a project table by using the obtained template data; step two, after obtaining item data typed in by a user in the generated item table, adopting a preset merging rule to merge the template data existing in the item table with the item data to obtain an item table formed by merging results: the preset merging rule is that for the same cell, if the template data has a value in the cell, the value of the template data in the cell is used as the value of the cell; if the template data has no value in the cell, the value of the item data in the cell is taken as the value of the cell.
The user can type the item data in the item table, if the template data exists in a certain cell, the value of the cell cannot change along with the change of the value of the newly-typed item data, and the normalization of the item table is ensured.
Specifically, a preset merging rule is adopted to merge the template data existing in the item table with the item data to obtain an item table formed by merging results, which may include: converting the existing template data and the project data in the project table into a first JSON object and a second JSON object respectively; for each cell in the item table, if the first JSON object has a value in the cell, taking the value of the first JSON object as the value of the cell; and if the first JSON object has no value in the cell, taking the value of the second JSON object in the cell as the value of the cell.
The JSON (JavaScript Object Notation, JS object numbered musical notation) is a lightweight data exchange format, and both template data and project data can be in the form of JSON character strings and converted into JSON objects, so that data merging can be realized rapidly. The invention does not limit the mutual conversion between the JSON object and the JSON character string, and can adopt the existing conversion mode for conversion.
Or in other embodiments, the preset merge rule may be designed in other manners, which is not limited in the present invention. For example, JQuery may also be employed to merge two JSON objects.
By applying the embodiment of the invention, each item table in the item can be newly built by copying the template table, the efficiency of generating the item table is improved, each item table in the item is combined by template data and item data, and if the item data and the template data have values in a certain cell, the value of the template data in the cell is taken as the final value of the cell; the uniformity of template data in each item table generated based on the same template table is ensured, the consistency and normalization of each item table in the same item are ensured, and the purpose of normalizing the item table according to the template table is achieved.
After the project table is generated, the template data and the project data contained in the project table can be utilized to generate the version number of the project table; after the template table is constructed, the version number of the template table may be generated using the template data contained in the template table.
The template data of the template table may include fields in the table, field values for each field, patterns of cells, formulas in cells, and so forth. The format may be Excel format, csv (Comma-Separated Values) format, xml (eXtensible Markup Language ) format, or the like. The method for generating the item table by using the template data is not limited, and for example, a method for generating the table can be written in advance by adopting programming languages such as C#, ASP NET and the like or an Excel library can be directly referenced to generate the Excel table.
In addition, after the template table and the project table are constructed, the data in the tables can be respectively modified. The invention does not limit the specific modification mode, for example, the specific contents of the table such as the cross-table function, the field name, the field value and the like in the table can be modified through the SpreadJS online editor. The project table may be adapted according to project related content, e.g., filling in specific values of fields related to the project, modifying cross-table reference relationships, etc., and the template table may not contain specific data values, but only style related content, e.g., fonts, lines, etc., of cells.
The template table can generally meet the requirements of most of the projects, but in the process of actually creating the projects, the template of the projects may need to be modified according to the conditions of the projects, and since the OID number of the project table is the same as the OID number of the template table used for generating the project table, after the template table is modified, the contents in the project table with the OID number can be uniformly modified, so that all project tables of the whole project are uniformly modified without singly modifying each project table.
By applying the embodiment of the invention, the project table is obtained by copying the template table, and the template table is not directly used as the project table, so that the project table and the template table are mutually independent, and any party is modified without affecting the other party. When a few item tables required by the item and the existing template tables exist, the user can still select to use the template table to create the item, then independently modify the template data in the item table, and for each item table uniformly generated by the template table, the OID numbers of the item tables are identical to the OID numbers of the template table, so that the OID numbers of the item tables are identical, the template data in each item table can be uniformly modified along with the modification of the template data in a certain item table, the template data of each item table is uniformly modified by modifying the template data of one item table, the modification process is simplified, and the uniformity of the templates of each item table is facilitated.
In addition, when the project table needed by the project is larger in access with the existing template table, a user can establish a set of template table according to the project requirement so as to generate the project table, and the flexibility and usability of the template table are ensured.
A user can also pre-construct a template table through the SpreadJS online editor, and can type a cross-table function in the template table, through the cross-table function, the cross-table reference relation between the template table and other template tables can be pre-established, and the cross-table reference relation can be copied into an item table constructed based on the template table.
In the process of calculating the preset cross-table function in the item table, the OID number of the item table can be used for searching the template table with the OID number in the catalog for storing the template table, determining the OID numbers of other template tables preset in the template table and having cross-table reference relation with the template table, and taking the OID number of the determined other template tables as the OID number of the target item table having cross-table reference relation with the item table, so that the cross-table function calculation can be completed by utilizing the OID number of the target item table.
By applying the embodiment of the invention, each project table in the project can be newly built by copying the preset template table so as to adapt to different project requirements, and the purpose of quickly constructing the project table is achieved. The project table and the template table can be regarded as a database for storing two data tables, if the cross-table reference function is preset in the template table, each project table generated based on copying the template table also contains the cross-table reference function, and the cross-table reference function takes the OID number as one of parameters, and the OID number of the project table is the same as the OID number of the template table used for generating the project table, so that the preset cross-table reference relation can be directly copied into the project table, the project table directly obtains and uses the preset cross-table reference relation, and the target function calculation formula does not need to be re-obtained in the project table to establish the same cross-table reference relation as the template table, thereby realizing the new process of simplifying the table and being convenient for quickly establishing the required table.
Each online form may also include a version number (version), which may be referred to as a source version number when stored on the server; when cached locally, its version number may be referred to as a local version number. In order to ensure the integrity and version accuracy of the data, if the data (data) in the online form changes, the version number of the online form may change accordingly. version can be MD5 (Message-Digest Algorithm) value generated by the character string of the data field, MD5 is a widely used cryptographic hash function, a 128-bit (16-byte) hash value can be generated, and MD5 values generated by different character strings are different, so that version difference after data change can be ensured, and the integrity and consistency of data are ensured.
The MD5 value of the cached online form may be calculated by using an MD5 calculator, or a program for calculating the MD5 value may be written in advance using a programming language such as Python, PHP, or the like, and the MD5 value of the cached online form is calculated by executing the program. In addition, in other implementations, the SHA (Secure Hash Algorithm ) 1 value, SHA256 value, or SHA512 value of the cached online form may be calculated, and the calculated SHA1 value, SHA256 value, or SHA512 value may be used as the local version number of the cached online form.
To avoid impacting efficiency by continually pulling forms from the server, online forms may be cached locally to increase the efficiency of form retrieval, online forms other than the local cache are loaded from the server, the locally cached online forms are updated with the loaded online forms, including:
for each locally cached template table, obtaining a local version number and an ID number of the template table; obtaining a source version number of a template table having the ID number from a server; judging whether the local version number is the same as the source version number; if not, pulling the template form with the ID number from the server, and updating the template form by using the pulled template form; if the template tables are the same, the template tables are not pulled from the server;
for each locally cached item table, obtaining a local version number, an ID number and an OID number of the item table; determining a corresponding template table with the OID number and obtaining a source version number of the item table with the ID number from the server; judging whether the local version number and the source version number of the item table are the same; if the item tables are not the same, the item table with the ID number is pulled from the server, the item table which is locally cached is updated by the pulled item table, and the updated item table is used as the item table to be combined; if the item tables are the same, taking the locally cached item table as an item table to be combined; and merging the item table to be merged with the determined corresponding template table, and updating the item table to be merged by using a merging result.
In the first loading, since the local cache does not contain the table from the server, all template tables and item tables can be pulled from the server, and then only the tables different from the local cache need to be pulled from the server for each loading.
Two cache data structures can be adopted to respectively cache the template table and the item table, and the cache data structure for storing the template table can comprise ID, data { }, version and OID which respectively represent the ID number, data, local version number and OID number of the template table; the cache data structure for storing the item table may include ID, data { }, excel_id, project_id, version, and OID, representing the ID number, data, form number, item number, local version number, and OID number of the item table, respectively. In other embodiments, other data structures may be used to buffer the template table and the item table, and the present invention is not limited to the specific data structure form.
Specifically, the following merging principle may be adopted to merge the determined corresponding template table and the item table to be merged: if the item table to be combined and the corresponding template table have values in a certain cell, taking the value of the corresponding template table as the final value of the cell; this ensures normalization of the item tables to be merged. Assuming that the data content of the corresponding template table is A= { a:1, B: { B1:2, B2:3}, c:2}, and the data content of the item table to be merged is B= { a:2, d:3}, the result obtained by merging A and B is { a:1, B: { B1:2, B2:3}, c:2, d:3}.
In one implementation, determining the corresponding template table with the OID number may include:
obtaining a source version number of a template table with the OID number from a server; judging whether the local version number of the template table with the OID number is the same as the source version number; if the template tables are not the same, the template table with the OID number is pulled from the server, the template table with the OID number which is locally cached is updated by the pulled template table, and the updated template table is determined to be the corresponding template table with the OID number; if the template tables are the same, the locally cached template table with the OID number is determined to be the corresponding template table with the OID number.
By adopting the embodiment of the invention, the online form is cached locally, so that the online form does not need to be acquired at each time by the server, the influence on the efficiency caused by continuously pulling the item form and the template form from the server is avoided, and the efficiency of acquiring the online form subsequently is improved. And by comparing the version numbers, the consistency of the locally cached template table and the content of the template table in the server and the consistency of the locally cached item table and the content of the item table in the server are ensured, the timely update of the locally cached online table is realized, and the accuracy and the reliability of the locally cached online table are improved, so that even if a worker modifies the data of a certain table, the modified item table can be timely obtained, and the timeliness and the accuracy of cross-table reference are improved.
In addition, in order to improve the data transmission efficiency, the server may compress the online form that the electronic device requests to load, and further transmit the compressed online form to improve the transmission efficiency, for example, the server may compress the online form by adopting GZIP and/or LZ-string, so that before the electronic device updates the locally cached online form with the loaded online form, the method further includes decompressing the loaded online form; updating the locally cached presence table with the loaded presence table includes updating the locally cached presence table with the decompressed presence table.
S102, determining a current table to be operated from the displayed online tables, and determining active cells of the current table; obtaining a target cross-table function for the active cell; the target cross-table function includes parameter values for the following parameters: the OID number of the target table to be referenced, the target cell area where the data to be referenced are located and operators;
each online form may be displayed in the form of a list or a multi-level directory structure, and after detecting that a certain online form displayed is selected, the online form may be determined to be the current form to be operated, and the current form may be displayed in a new window or on the left/right/above/below the display area of each form loaded; upon detecting that one or more cells of the current table are selected, the selected cells may be determined to be active cells.
The target table may be a table to be referred to by the current table, and it should be noted that one table may include one or more tables, and the active cell may be a cell in a certain table of the current table; the target cell region may be a cell region in a form of the target table; the operator may be one or a combination of addition, subtraction, multiplication, division, root number, integration, etc.
Specifically, obtaining a target cross-table function for the active cell includes:
obtaining a cross-table function typed by a user in the active cell as a target cross-table function for the active cell; or alternatively, the process may be performed,
and after obtaining the function name typed by the user in the active cell and the OID number of the selected target table, popping up an event popup window to obtain the target cell area selected by the user through the event popup window, and generating a target cross-table function by using the obtained function name, the OID number of the target table and the target cell area.
The user can directly type in the cross-table function according to a preset function format, or in order to improve user experience and facilitate rapid generation of the target cross-table function, the user can type in a function name in a previous active cell and select an OID number of a target table to be referred to, the electronic device can obtain the function name and the OID number of the target table, further an event popup window can be popped up, the user can select a target cell area to be referred to by editing the event popup window, and after clicking determination, the electronic device can generate the target cross-table function. Or in other modes, the function name typed by the user in the active cell and the ID number of the selected target table, the electronic equipment can obtain the function name, determine the OID number of the target table through the ID number of the target table, and further generate the target cross-table function after obtaining the target cell area selected by the user.
The preset function format of the target cross-table function may be a preset parameter format, and may be preset according to requirements, for example, may be: "[ OID ] sheet name", cell ", wherein OID represents the OID number of the target form to be referenced, sheet represents the index number of the target form (sheet) in which the data to be referenced is located, i.e. in which form the data to be referenced is in the target form, and after the name of the target form is modified, the target form can still be found by this parameter; the shaetname represents the name of the target form where the data to be referred is located, the target position where the data to be referred is located can be searched through the name, and if the name is found, the target position is directly determined to be the target form with the name; if the name is not found, indicating that the name of the target form is modified, searching the target form through a shaetindex parameter, and updating a shaetname parameter in a cross-table function by using the form name of the searched target form;
the cell represents a cell area where the data to be referenced is located, and may be: a1 cell, a cell in the A1-D1 region, etc., by which the specific cell location in the target form can be located, thereby obtaining accurate data to be referenced.
The method can be used for registering an asynchronous function in advance based on the SpreadJS control as a cross-meter function, and further, the data of the unit cell where the cross-meter function is located can be asynchronously changed through a method in the SpreadJS control. The cross-table function can comprise operation structures formed by operators, each cross-table function has a unique function name, and the function with the function name can be executed by calling the function name, so that cross-table calculation is realized.
By applying the embodiment of the invention, the accurate data to be quoted can be obtained by executing the target cross-table function, the data to be quoted does not need to be manually obtained, the calculation error caused by manual input error is avoided, the error rate of the table is reduced, the data obtaining efficiency is improved, and the efficiency of workers when working by using the online table is improved.
S103, calculating the target cross-table function to obtain a calculation result of data to be referenced in a target cell area in the target table; and displaying the calculation result in the active cell to finish the cross-table reference of the target table.
After the data to be quoted is obtained, an operator in the target cross-table function can be used for calculation, and a calculation result is obtained. For example, the target cross-table function is used for summing the data to be referred in the target cell area in the target table, the calculated result is a calculated sum value, and the sum value can be displayed in the active cell; or in other embodiments, the data to be referenced in each cell in the target cell area in the target table and the calculation result can be displayed together.
In one implementation manner, calculating the target cross-table function to obtain a calculation result of data to be referenced in a target cell area in the target table includes:
searching a target ID number of an item table generated based on a template table with the OID number;
and determining the item table with the target ID number as a target table, acquiring data to be referenced of a target area in the target table, and calculating the acquired data to be referenced according to the operator to obtain a calculation result.
The target table can be a project table with a target ID number in the current project, the OID number of a template table for generating the target table can be determined through the OID number of the target table, the target ID number of the project table is searched through a template catalog by using the OID number of the template table, then the data of the target table to be referenced is searched in a local cache database according to the target ID number of the project table, the data is obtained from a target cell area defined in a target cross-table function, and further the calculation can be performed according to an operator, and the calculation result is updated to an active cell, so that cross-table data reference logic is realized.
By the embodiment of the invention, the calculation result of each cell in the target cell area in the target table can be obtained by calculating the target cross-table function, and the calculation result can be displayed in the active cell to finish cross-table reference of the target table, so that the efficiency of on-line table cross-table reference is improved. In addition, after the loading instruction is detected, a table different from the local cache can be loaded from the server, so that the accuracy and the reliability of the local cache table are improved.
Corresponding to the method embodiment, the embodiment of the invention also provides an online form cross-form quotation device.
Referring to fig. 2, fig. 2 is a schematic structural diagram of an online form cross-table referencing device according to an embodiment of the present invention, where the device includes:
a loading module 201, configured to load an online form different from the local cache from the server after detecting the loading instruction, and update the local cache online form with the loaded online form; and displaying all the updated online forms of the local cache;
a determining module 202, configured to determine a current table to be operated from the displayed online tables, and determine an active cell of the current table; obtaining a target cross-table function for the active cell; the target cross-table function includes parameter values for the following parameters: the OID number of the target table to be referenced, the target cell area where the data to be referenced are located and operators;
the calculation module 203 is configured to calculate the target cross-table function, and obtain a calculation result of data to be referenced in a target cell area in the target table; and displaying the calculation result in the active cell to finish the cross-table reference of the target table.
By the embodiment of the invention, the calculation result of each cell in the target cell area in the target table can be obtained by calculating the target cross-table function, and the calculation result can be displayed in the active cell to finish cross-table reference of the target table, so that the efficiency of on-line table cross-table reference is improved. In addition, after the loading instruction is detected, a table different from the local cache can be loaded from the server, so that the accuracy and the reliability of the local cache table are improved.
Optionally, after detecting the load instruction, the loading module 201 loads an online form different from the local cache from the server, specifically:
after the application program opening instruction is obtained, loading an online form different from the local cache from a server; or alternatively, the first and second heat exchangers may be,
after detecting that a refresh button on the displayed interface is clicked, an online form other than the local cache is loaded from the server.
Optionally, the online table includes an item table and a template table, each item table is generated based on one template table, and the OID number of each item table is the same as the OID number of the template table used for generating the item table.
Optionally, the loading module 201 loads an online form different from the local cache from the server, and updates the local cache online form with the loaded online form, specifically:
For each locally cached template table, obtaining a local version number and an ID number of the template table; obtaining a source version number of a template table having the ID number from a server; judging whether the local version number is the same as the source version number; if not, pulling the template form with the ID number from the server, and updating the template form by using the pulled template form; if the template tables are the same, the template tables are not pulled from the server;
for each locally cached item table, obtaining a local version number, an ID number and an OID number of the item table; determining a corresponding template table with the OID number and obtaining a source version number of the item table with the ID number from the server; judging whether the local version number and the source version number of the item table are the same; if the item tables are not the same, the item table with the ID number is pulled from the server, the item table which is locally cached is updated by the pulled item table, and the updated item table is used as the item table to be combined; if the item tables are the same, taking the locally cached item table as an item table to be combined; and merging the item table to be merged with the determined corresponding template table, and updating the item table to be merged by using a merging result.
Optionally, the loading module 201 determines a corresponding template table with the OID number, specifically:
obtaining a source version number of a template table with the OID number from a server; judging whether the local version number of the template table with the OID number is the same as the source version number; if the template tables are not the same, the template table with the OID number is pulled from the server, the template table with the OID number which is locally cached is updated by the pulled template table, and the updated template table is determined to be the corresponding template table with the OID number; if the template tables are the same, the locally cached template table with the OID number is determined to be the corresponding template table with the OID number.
Optionally, the determining module 202 obtains a target cross-table function for the active cell, specifically:
obtaining a cross-table function typed by a user in the active cell as a target cross-table function for the active cell; or alternatively, the process may be performed,
and after obtaining the function name typed by the user in the active cell and the OID number of the selected target table, popping up an event popup window to obtain the target cell area selected by the user through the event popup window, and generating a target cross-table function by using the obtained function name, the OID number of the target table and the target cell area.
Optionally, the parameter format of the target cross-table function is: "[ OID ] shaetindex ] shaetname", cell, wherein OID represents the OID number of the target form to be referenced, shaetindex represents the index number of the target form in which the data to be referenced is located, shaetname represents the name of the target form in which the data to be referenced is located, and cell represents the cell region in which the data to be referenced is located.
Optionally, the loaded online form is a compressed form, and the apparatus further includes a decompression module configured to:
decompressing the loaded online form before updating the locally cached online form with the loaded online form;
the loading module updates the locally cached online form with the loaded online form, specifically updates the locally cached online form with the decompressed online form.
Optionally, the calculating module calculates the target cross-table function to obtain a calculation result of the data to be referenced in the target cell area in the target table, specifically:
searching a target ID number of an item table generated based on a template table with the OID number;
and determining the item table with the target ID number as a target table, acquiring data to be referenced of a target area in the target table, and calculating the acquired data to be referenced according to the operator to obtain a calculation result.
The foregoing description of the preferred embodiments of the invention is not intended to be limiting, but rather is intended to cover all modifications, equivalents, alternatives, and improvements that fall within the spirit and scope of the invention.
Claims (6)
1. An online form cross-table referencing method, the method comprising:
after detecting the loading instruction, loading an online form different from the local cache from the server, and updating the online form of the local cache by using the loaded online form; the online table comprises item tables and template tables, each item table is generated based on one template table, and the OID number of each item table is the same as the OID number of the template table used for generating the item table;
the loading of the online form different from the local cache from the server and updating of the local cache online form by the loaded online form specifically comprises the following steps:
for each locally cached template table, obtaining a local version number and an ID number of the template table; obtaining a source version number of a template table having the ID number from a server; judging whether the local version number is the same as the source version number; if not, pulling the template form with the ID number from the server, and updating the template form by using the pulled template form; if the template tables are the same, the template tables are not pulled from the server;
For each locally cached item table, obtaining a local version number, an ID number and an OID number of the item table; determining a corresponding template table with the OID number and obtaining a source version number of the item table with the ID number from the server; judging whether the local version number and the source version number of the item table are the same; if the item tables are not the same, the item table with the ID number is pulled from the server, the item table which is locally cached is updated by the pulled item table, and the updated item table is used as the item table to be combined; if the item tables are the same, taking the locally cached item table as an item table to be combined; combining the item table to be combined with the determined corresponding template table, and updating the item table to be combined by using a combination result;
the determining the corresponding template table with the OID number comprises the following steps:
obtaining a source version number of a template table with the OID number from a server; judging whether the local version number of the template table with the OID number is the same as the source version number; if the template tables are not the same, the template table with the OID number is pulled from the server, the template table with the OID number which is locally cached is updated by the pulled template table, and the updated template table is determined to be the corresponding template table with the OID number; if the template tables are the same, determining the locally cached template table with the OID number as a corresponding template table with the OID number;
And displaying all the updated online forms of the local cache;
determining a current table to be operated from the displayed online tables, and determining active cells of the current table; obtaining a target cross-table function for the active cell; the target cross-table function includes parameter values for the following parameters: the OID number of the target table to be referenced, the target cell area where the data to be referenced are located and operators;
calculating the target cross-table function to obtain a calculation result of data to be referenced in a target cell area in the target table; the method specifically comprises the following steps:
searching a target ID number of an item table generated based on a template table with the OID number;
determining the item table with the target ID number as a target table, acquiring data to be referenced of a target area in the target table, and calculating the acquired data to be referenced according to the operator to obtain a calculation result;
and displaying the calculation result in the active cell to finish the cross-table reference of the target table.
2. The method of claim 1, wherein loading the online form, other than the local cache, from the server after detecting the load instruction comprises:
After the application program opening instruction is obtained, loading an online form different from the local cache from a server; or alternatively, the first and second heat exchangers may be,
after detecting that a refresh button on the displayed interface is clicked, an online form other than the local cache is loaded from the server.
3. The method of claim 1, wherein obtaining a target cross-table function for the active cell comprises:
obtaining a cross-table function typed by a user in the active cell as a target cross-table function for the active cell; or alternatively, the process may be performed,
and after obtaining the function name typed by the user in the active cell and the OID number of the selected target table, popping up an event popup window to obtain the target cell area selected by the user through the event popup window, and generating a target cross-table function by using the obtained function name, the OID number of the target table and the target cell area.
4. The method of claim 1, wherein the parameter format of the target cross-table function is: "[ OID ] shaetindex ] shaetname", cell, wherein OID represents the OID number of the target form to be referenced, shaetindex represents the index number of the target form in which the data to be referenced is located, shaetname represents the name of the target form in which the data to be referenced is located, and cell represents the cell region in which the data to be referenced is located.
5. The method of claim 1, wherein the loaded online form is a compressed form,
the method further includes decompressing the loaded online form before updating the locally cached online form with the loaded online form;
the updating of the locally cached presence table with the loaded presence table includes updating the locally cached presence table with the decompressed presence table.
6. An online form cross-table referencing apparatus, the apparatus comprising:
the obtaining module is used for loading the online form which is different from the local cache from the server after the loading instruction is detected, and updating the online form of the local cache by the loaded online form; the online table comprises item tables and template tables, each item table is generated based on one template table, and the OID number of each item table is the same as the OID number of the template table used for generating the item table;
the loading of the online form different from the local cache from the server and updating of the local cache online form by the loaded online form specifically comprises the following steps:
for each locally cached template table, obtaining a local version number and an ID number of the template table; obtaining a source version number of a template table having the ID number from a server; judging whether the local version number is the same as the source version number; if not, pulling the template form with the ID number from the server, and updating the template form by using the pulled template form; if the template tables are the same, the template tables are not pulled from the server;
For each locally cached item table, obtaining a local version number, an ID number and an OID number of the item table; determining a corresponding template table with the OID number and obtaining a source version number of the item table with the ID number from the server; judging whether the local version number and the source version number of the item table are the same; if the item tables are not the same, the item table with the ID number is pulled from the server, the item table which is locally cached is updated by the pulled item table, and the updated item table is used as the item table to be combined; if the item tables are the same, taking the locally cached item table as an item table to be combined; combining the item table to be combined with the determined corresponding template table, and updating the item table to be combined by using a combination result;
the determining the corresponding template table with the OID number comprises the following steps:
obtaining a source version number of a template table with the OID number from a server; judging whether the local version number of the template table with the OID number is the same as the source version number; if the template tables are not the same, the template table with the OID number is pulled from the server, the template table with the OID number which is locally cached is updated by the pulled template table, and the updated template table is determined to be the corresponding template table with the OID number; if the template tables are the same, determining the locally cached template table with the OID number as a corresponding template table with the OID number;
And displaying all the updated online forms of the local cache;
a determining module, configured to determine a current table to be operated from the displayed online tables, and determine an active cell of the current table; obtaining a target cross-table function for the active cell; the target cross-table function includes parameter values for the following parameters: the OID number of the target table to be referenced, the target cell area where the data to be referenced are located and operators;
the calculation module is used for calculating the target cross-table function to obtain a calculation result of data to be referenced in a target cell area in the target table; the method specifically comprises the following steps: searching a target ID number of an item table generated based on a template table with the OID number; determining the item table with the target ID number as a target table, acquiring data to be referenced of a target area in the target table, and calculating the acquired data to be referenced according to the operator to obtain a calculation result;
and displaying the calculation result in the active cell to finish the cross-table reference of the target table.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811627725.8A CN109829144B (en) | 2018-12-28 | 2018-12-28 | Method and device for cross-table referencing of online table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201811627725.8A CN109829144B (en) | 2018-12-28 | 2018-12-28 | Method and device for cross-table referencing of online table |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN109829144A CN109829144A (en) | 2019-05-31 |
| CN109829144B true CN109829144B (en) | 2023-06-06 |
Family
ID=66861324
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201811627725.8A Active CN109829144B (en) | 2018-12-28 | 2018-12-28 | Method and device for cross-table referencing of online table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN109829144B (en) |
Families Citing this family (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN111126015B (en) * | 2019-11-25 | 2023-12-26 | 金蝶软件（中国）有限公司 | Report form compiling method and equipment |
| CN112989773B (en) * | 2019-12-13 | 2024-02-20 | 北京庖丁科技有限公司 | Method, apparatus, device and computer readable medium for synchronizing update data |
| CN111144082B (en) * | 2019-12-23 | 2023-05-12 | 中国建设银行股份有限公司 | Multi-table iterative calculation method, device, electronic equipment and storage medium |
| CN115081412A (en) * | 2021-03-01 | 2022-09-20 | 北京字跳网络技术有限公司 | Table content processing method, device, equipment and storage medium |
| CN114997124A (en) * | 2021-03-01 | 2022-09-02 | 北京字跳网络技术有限公司 | Table content processing method, device, equipment and storage medium |
| JP2024508012A (en) * | 2021-03-01 | 2024-02-21 | 北京字跳▲網▼絡技▲術▼有限公司 | Table content processing method, device, equipment, and storage medium |
| CN113220752A (en) * | 2021-05-06 | 2021-08-06 | 北京字跳网络技术有限公司 | Display method and device and electronic equipment |
| CN113361231B (en) * | 2021-07-06 | 2024-05-28 | 北京字跳网络技术有限公司 | Information quotation method, system, device and electronic equipment |
| CN113704257B (en) * | 2021-08-06 | 2023-09-26 | 网易（杭州）网络有限公司 | Data table updating method and device |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JPH10254749A (en) * | 1997-03-14 | 1998-09-25 | Hitachi Ltd | Data update system in multi-server system |
| EP2418591A1 (en) * | 2010-08-11 | 2012-02-15 | Sap Ag | Online data migration |
| CN104978215A (en) * | 2014-10-08 | 2015-10-14 | 腾讯科技（深圳）有限公司 | File updating method, relevant equipment and system |
| CN107766314A (en) * | 2017-10-20 | 2018-03-06 | 网易（杭州）网络有限公司 | The data processing method and device of electrical form |
Family Cites Families (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101719131B (en) * | 2009-12-01 | 2013-08-07 | 金蝶软件（中国）有限公司 | Method, device and system for processing report data |
| WO2012017056A1 (en) * | 2010-08-06 | 2012-02-09 | Solyp Informatik Gmbh | Method and apparatus for automatically processing data in a cell format |
| CN103942184B (en) * | 2013-12-30 | 2018-01-05 | 远光软件股份有限公司 | Collocation method, generation method and system with addition Item consolidated statement |
| CN104391725B (en) * | 2014-12-08 | 2017-11-14 | 畅捷通信息技术股份有限公司 | Page display method and page presentation device |
-
2018
- 2018-12-28 CN CN201811627725.8A patent/CN109829144B/en active Active
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| JPH10254749A (en) * | 1997-03-14 | 1998-09-25 | Hitachi Ltd | Data update system in multi-server system |
| EP2418591A1 (en) * | 2010-08-11 | 2012-02-15 | Sap Ag | Online data migration |
| CN104978215A (en) * | 2014-10-08 | 2015-10-14 | 腾讯科技（深圳）有限公司 | File updating method, relevant equipment and system |
| CN107766314A (en) * | 2017-10-20 | 2018-03-06 | 网易（杭州）网络有限公司 | The data processing method and device of electrical form |
Non-Patent Citations (2)
| Title |
|---|
| REDLetr: Workflow and tools to support the migration of legacy clinical data capture systems to REDCap;William D. Dunn等;International Journal of Medical Informatics;第93卷;103-110 * |
| 如何用C#生成Excel文件记录数据;李斌;电脑编程技巧与维护(第07期);92-93 * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN109829144A (en) | 2019-05-31 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN109829144B (en) | Method and device for cross-table referencing of online table | |
| RU2408074C2 (en) | Method, system and apparatus for providing access to workbook models through remote function calls | |
| US8621421B2 (en) | Workflow visualization | |
| CN111061833B (en) | Data processing method and device, electronic equipment and computer readable storage medium | |
| US11093242B2 (en) | Automatically mapping data while designing process flows | |
| US11689609B2 (en) | Mechanism for webpage composition | |
| US10691882B2 (en) | Techniques for using a spreadsheet as a compute engine in an online data management system | |
| US11347492B2 (en) | Software deployment control using blockchain | |
| CN109829141B (en) | Project table generation method and device | |
| US20220284371A1 (en) | Method, device and medium for a business function page | |
| CN111190905A (en) | Database table processing method and device and electronic equipment | |
| CN114064925A (en) | Knowledge graph construction method, data query method, device, equipment and medium | |
| CN109815428B (en) | Online table caching method and device | |
| US20210382699A1 (en) | Data processing method, electronic device, and storage medium | |
| US10460015B1 (en) | Assimilation in multi model webpage composition | |
| CN109829143B (en) | Online form summarizing method and device | |
| US11861300B2 (en) | System and method for maintaining links and revisions | |
| US11734505B2 (en) | System and method for document branching | |
| KR101798145B1 (en) | Method and system for processing data at server side | |
| CN117149729A (en) | File processing method, device, electronic equipment and computer readable storage medium | |
| Elam et al. | A computer program to calculate two-stage short-run control chart factors for (X, s) charts | |
| WO2021055239A1 (en) | Method for automated code reviewer recommendation | |
| CN113742497A (en) | Data processing method and device based on knowledge graph | |
| CN117573199A (en) | Model difference comparison analysis method, device, equipment and medium | |
| CN117010738A (en) | Service data processing method and device, electronic equipment and storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20230824 Address after: DB Area, Liye Commune Store, No. 289 Guanshan Avenue, Hongshan District, Wuhan City, Hubei Province, 430070 Patentee after: Wuhan Mango Technology Co.,Ltd. Address before: 430056 room 1603, building 36, Golden Harbor Phase IV, 68 Dongfeng Avenue, Hanyang District, Wuhan City, Hubei Province Patentee before: Chen Deqin |