CN113326680A

CN113326680A - Method and device for generating table

Info

Publication number: CN113326680A
Application number: CN202010128724.XA
Authority: CN
Inventors: 窦洪亮
Original assignee: Beijing Jingdong Century Trading Co Ltd; Beijing Wodong Tianjun Information Technology Co Ltd
Current assignee: Beijing Jingdong Century Trading Co Ltd; Beijing Wodong Tianjun Information Technology Co Ltd
Priority date: 2020-02-28
Filing date: 2020-02-28
Publication date: 2021-08-31

Abstract

The invention discloses a method and a device for generating a table, and relates to the technical field of computers. One embodiment of the method comprises: generating coordinates of one or more first cells and one or more second cells in the table; associating the values of the one or more first cells with the coordinates of the one or more first cells; and generating values of the one or more second cells using the coordinates of the one or more second cells and the values of the one or more first cells. The implementation mode reduces the complexity of the front-end code and improves the convenience and the simplicity.

Description

Method and device for generating table

Technical Field

The present invention relates to the field of computer technologies, and in particular, to a method and an apparatus for generating a table.

Background

In a web page, it is often necessary to query and edit form data, which triggers formula calculations. Taking the sales plan as an example, the sales amount of a single month is modified, and the sales plan values of the quarter and the year are automatically summarized and calculated. Users want a similar experience in browsers to e-forms such as Excel.

The current general method is that after a cell is modified, a change or blu event is triggered, then in a callback function of the event, a summary value is calculated according to a set formula, and finally, corresponding cell data is updated.

In the process of implementing the invention, the inventor finds that the prior art has problems, such as complex and redundant formula calculation codes and incapability of multiplexing; a large number of formula calculation codes are mixed in the service codes, and the code readability and maintainability are poor; after the formula is adjusted, the original calculation logic needs to be modified, and especially when the parameter of one formula is the calculation result of the previous formula, a plurality of formula calculation codes need to be modified.

Disclosure of Invention

In view of this, embodiments of the present invention provide a method for generating a web table, which can solve the problem of poor extensibility and maintainability of repeated code development by using a cell coordinate mode, and can separate a formula calculation code from a service code, thereby achieving an effect similar to an Excel formula calculation.

To achieve the above object, according to an aspect of an embodiment of the present invention, there is provided a method of generating a table, including:

generating coordinates of the one or more first cells and the one or more second cells;

associating the values of the one or more first cells with the coordinates of the one or more first cells; and

generating values for the one or more second cells using the coordinates of the one or more second cells and the values for the one or more first cells.

According to an aspect of the embodiments of the present invention, there is provided a method of generating a table, wherein the first cell is a parameter cell, and the second cell is a target cell.

According to an aspect of the embodiments of the present invention, there is provided a method of generating a table, wherein the values of the one or more first cells are obtained by at least one of:

receiving user input to the one or more first cells to obtain values for the one or more first cells; or

Obtaining values for the one or more first cells from a database.

According to an aspect of the embodiments of the present invention, there is provided a method of generating a table, wherein generating values of the one or more second cells is triggered by at least one of:

obtaining values for the one or more first cells; or

Changing a value of the one or more first cells.

According to an aspect of the embodiments of the present invention, there is provided a method of generating a table, including generating a mapping set of coordinates and values of the one or more first cells; and

a set of formulas is generated based on the attributes of the table.

According to an aspect of the embodiments of the present invention, there is provided a method of generating a table, wherein generating values of the one or more second cells using coordinates of the one or more second cells and values of the one or more first cells includes:

for each of the one or more second cells:

selecting a formula corresponding to the second cell from the formula set based on the coordinates of the second cell; and

the coordinates and values of one or more first cells associated with the selected formula are used to generate the value of the second cell.

According to an aspect of the embodiments of the present invention, there is provided a method of generating a table, wherein coordinates of one or more cells in the table are generated using a data structure of the cells.

One embodiment of the above invention has the following advantages or benefits: due to the adoption of the technical means of the cell coordinates, the technical problem of complex and redundant codes is solved, and the technical effect that the form of the Web page has the user experience similar to an Excel spreadsheet is achieved.

Further effects of the above-mentioned non-conventional alternatives will be described below in connection with the embodiments.

Drawings

The drawings are included to provide a better understanding of the invention and are not to be construed as unduly limiting the invention. Wherein:

FIG. 1 is a schematic diagram of a main flow of a method of generating a table according to an embodiment of the invention;

FIG. 2 is a schematic diagram of a main flow of a method of calculating a target cell value according to an embodiment of the present invention;

FIG. 3 is a schematic diagram of the main modules of an apparatus for generating a table according to an embodiment of the present invention;

FIG. 4 is an exemplary system architecture diagram in which embodiments of the present invention may be employed;

fig. 5 is a schematic block diagram of a computer system suitable for use in implementing a terminal device or server of an embodiment of the invention.

Detailed Description

Exemplary embodiments of the present invention are described below with reference to the accompanying drawings, in which various details of embodiments of the invention are included to assist understanding, and which are to be considered as merely exemplary. Accordingly, those of ordinary skill in the art will recognize that various changes and modifications of the embodiments described herein can be made without departing from the scope and spirit of the invention. Also, descriptions of well-known functions and constructions are omitted in the following description for clarity and conciseness.

Fig. 1 is a schematic diagram of a main flow of a method of generating a table according to an embodiment of the present invention, and as shown in fig. 1, the method of generating a table mainly includes steps S101, S102, and S103.

Step S101: coordinates of one or more first cells and one or more second cells in the table are generated.

First, a web form is generated using front-end technology. Any form component technology available may be used, such as acts, vue, angular, etc. MV frameworks, which all have well-established form components. And converting the table back-end data into the configuration item of the table component, and finishing the rendering of the table component.

Similar to Excel spreadsheets, the web form is considered to be a planar coordinate system defined by row and column coordinates. In the back-end data model, each cell is an object and contains row and column coordinates, and each cell is uniquely defined by the row and column coordinates. When rendering a cell component, the cell component saves the coordinate values.

Department of department	One month	February of February	March	Summary of the invention
					Sell one part	1000	2000	3000
Second part of sales	2000	1500	1000

TABLE 1

The sales department performance summary table in table 1 will be described as an example. The table includes row attributes, january, february, march, summary, and column attributes, sales one, sales two. The data cells in the table are sales data for one part of the sales: january 1000, february 2000, march 3000; sales data of sales section two: january 2000, february 1500, march 1000.

The back-end data model may implement the table using any suitable data structure. In one embodiment, the table is identified by an array:

table (1)

Where m1 represents January, m2 represents February, m3 represents March, and sum represents Sum.

The coordinates of each data cell are determined from the data structure. In the embodiment in which the cells are expressed by arrays, the array index [0] is a row coordinate and the attribute names [ m1], [ m2], [ m3] are column coordinates for data of one part of sales. For example, the coordinates {0, m1} uniquely define selling a monthly sales data cell, in this example, the value of the {0, m1} cell is 1000 (identified in the table by shading). The value of a cell refers to a numerical value corresponding to the coordinates of the cell, for example, if a cell at the coordinates {0, m1} is written with a numerical value of 1000, the value of the cell {0, m1} is 1000; as another example, if the cell at the coordinates {0, m2} read from a store such as a database has a corresponding value of 2000, then the cell {0, m2} has a value of 2000. For the data of the second part of sales, the array subscript [1] is a row coordinate, and the attribute names [ m1], [ m2], [ m3] are column coordinates. Accordingly, a sales summary cell may be represented as {0, sum }; the sales two summary cells may be represented as {1, sum }. It should be noted that the above examples of the cells are only examples, and do not limit the embodiments.

Similar to Excel spreadsheets, the cells in table 1 include two types of cells. The first cell indicates a cell for which a value can be directly obtained without calculation, and may be referred to as a parameter cell, for example. The second cell indicates a cell that requires a trigger calculation to obtain a value, and may be referred to as a target cell, for example. The parameter cell value is the parameter used in calculating the target cell value. In the example of Table 1, the parameter cells include the cells of Table 1 that have been filled with data, and the target cell includes two cells that have not generated a summarized column of values.

In one embodiment, a consistent cell object may be constructed, whether the first cell (parameter cell) or the second cell (target cell). For example: { x:0, y:1, value:10000, editable: true }. Where x represents the abscissa, y represents the column coordinate, value represents the value of the cell, editable indicates whether the cell is editable (used when rendering the cell). The mapping of cell coordinates and values can be embodied in the cell object.

In step S101, the coordinates of each first cell and the coordinates of each second cell are generated by the backend data structure.

Step S102: associating the values of the one or more first cells with the coordinates of the one or more first cells.

Continuing with the example of Table 1 above, the first cells are stored in a backend data structure, and the coordinates of each first cell are associated with its value, generating a mapping set. The coordinates and values of each cell are stored, for example, in arrays and array element values. In the example of table 1, the values of the first cell are as follows: cell {0, m1}, cell {0, m2}, cell { 2000, cell {0, m3}, cell 3000; cell {1, m1}, cell {1, m2}, cell { 1500, cell {1, m3}, and cell { 1000. In one embodiment, the coordinates of the parameter cells may be placed into a parameter array to represent a set of parameter cells, such as definitions ═ m1(x1, y1), m2(x2, y2), m3(x3, y 3). It should be noted that the above array format is only an example and does not limit the embodiments of the present invention.

It should be noted that the value of the first cell (parameter cell) may be entered by the user from the front-end page or read from the back-end database.

Step S103: generating values for the one or more second cells using the coordinates of the one or more second cells and the values for the one or more first cells.

The second cell is a target cell that needs to be calculated to obtain a value, i.e., the value of the second cell is obtained by calculating the parameter. The calculation method is generally expressed by a formula. In one embodiment, a formula object may be generated for each formula. Taking the summary calculation as an example, the formula is sum m1+ m2+ m3(m1 is the number of january, m2 is the number of february, and m3 is the array of march). There are 4 cells participating in the computation in total. The structure of the formula object is as follows:

formula object (1)

Wherein 'p } + { p } + { p }' represents a calculation formula; { p } is a parameter placeholder; params [ [ x0, y0], [ x1, y1], [ x2, y2] ] represents a parameter cell coordinate array, and the sequence corresponds to the parameters of the formula one by one; target [ x3, y3] represents the coordinate of the target cell, and after the triggering calculation, the value of the cell is updated to the formula calculation value.

A web table may correspond to multiple formulas. For example, in addition to aggregation, the sales schedule may also need to use the formula "gross rate of sale ═ gross rate of sale"/"gross rate of sale ═ gross rate of sale"/"gross amount of sale", etc. In this case, these formula objects need to be combined to form an object list. The structure of the object list is, for example, as follows:

formula object list (1)

Equation 1 in the above example may be substituted with values representing a + B + C for three parameters A, B, C; equation 2 may be substituted with the values of the four parameters A, B, C, D, representing (A + B)/C-D.

The opportunities for triggering formula calculations fall into two categories: the first type is that formula calculation is triggered immediately after data loading, and is suitable for a scene without formula calculation at the rear end; the second type is to trigger formula calculation after modifying the value of the parameter cell, thereby updating the table data. The calculation formula can be created in the front-end code, or can be returned to the front end together with the table data after the back end is constructed, and formula analysis is carried out at the front end to obtain the value of the target cell. The specific steps are shown in fig. 2.

It should be noted that the above formula objects and formula object list forms are only examples and do not limit the embodiments of the present invention.

Fig. 2 is a schematic diagram of a main flow of a method of calculating a target cell value according to an embodiment of the present invention, and as shown in fig. 2, the method of calculating a target cell value mainly includes steps S201, S202, S203, S204, and S205.

Step S201: and traversing the calculation formula list to obtain a calculation formula object.

In one embodiment, a formula list is traversed and a formula object corresponding to a target cell is selected from a plurality of formulas in the formula list. The selected formula object needs to satisfy the coordinates of the cell containing the parameter in its params array.

Formula object list (1)

Again using the above example as an example, for the target cell target [ x3, y3], the coordinates [ x4, y4] of the target cell are obtained in step S101; the entire formula list is then traversed, filtering out the coordinates [ x4, y4] as the formula for the calculation factor. In this example, the formula:'{ p } + { p })/{ p } - { p }' may be found.

Step S202: and traversing the parameter cell set of the formula object to obtain a parameter value set.

In this step, after the cell data is returned, the front end caches the cell data, and locates the cell according to the row coordinate and the column coordinate [ xi, yj ] of the cell, thereby obtaining the value of the cell. The values of all the parameter cells are placed sequentially in a data structure. In one embodiment, for example, it may be placed in an array, naming the parameter value array as, for example, a valueList. The method for obtaining the value of the cell can be specifically realized according to different table components. Continuing with the example of Table 1, using the row sequence number [0] as the row coordinate and the column attribute value [ m1] as the column coordinate, the cell corresponding value 1000 can be obtained from the coordinates [0, m1 ].

Step S203: replace the placeholder { p } for the formula.

In step S201, the object of the calculation formula is obtained, and in step S202, the parameter value array valueList is obtained, and the parameter values are consistent with the order of the parameters in the formula. The parameter value array may be traversed in step S203 to replace the parameter placeholders in params in the formula object one by one. Continuing with the above example, the formula { p } + { p })/{ p }, with parameter value arrays of [1000,2000,3000], is obtained in steps S201 and S202, and after replacement, the formula becomes the expression (1000+ 2000)/3000.

Step S204: and analyzing the expression and calculating.

And according to the expression obtained in the last step, carrying out evaluation calculation on the expression. Analytical evaluation of expressions can be implemented using any third party class library that can be used, such as mathjs, etc.

Step S205: the target cell data is updated.

Continuing with the above example, from the formula calculation, the coordinates of the target cell [ x4, y4] are taken using the coordinates specified by the target attribute, and the corresponding cell is found from the coordinates in the web table. The cell may have an existing value or no value at present, and the value calculated by the formula in the previous step is updated to the target cell.

Taking table 1 as an example, after updating the value of the target cell, table 1 is filled completely as shown in table 2.

Department of department	One month	February of February	March	Summary of the invention
					Sell one part	1000	2000	3000	6000
Second part of sales	2000	1500	1000	4500

TABLE 2

It should be noted that the above forms of formula objects, formula object lists, coordinates, arrays, etc. are merely examples, and do not limit the embodiments of the present invention.

Fig. 3 is a schematic diagram of main blocks of an apparatus for generating a table according to an embodiment of the present invention.

Module 301 inputs module: for obtaining the value of the parameter cell and the value of the write target cell. For example, when the value in the parameter cell is input into the web page by the user, the input module obtains the input of the user from the web page; and the input module is used for reading the values in the parameter cells from the background database and writing the values in the parameter cells into the parameter cells under the condition that the values in the parameter cells are read from the background database. In addition, after calculating the value of the generated target cell according to the formula, the input module is used for writing the generated value into the target cell.

The module 302: and the cell module is used for realizing the operation related to the cell. Including obtaining the coordinates of the parameter cell and the target cell in the table, associating the value of the parameter cell with its coordinates, and associating the value of the target cell with its coordinates. Also included are actions that trigger formula calculations, such as receiving that the value of a parameter cell is changed, receiving that an originally blank parameter cell has data written to, and so on.

Module 303: and the formula calculation module is used for generating the value of the target cell by utilizing the value of the parameter cell, the coordinate of the target cell and the background formula. But also to generate formula objects, generate formula lists, select corresponding formula objects for the corresponding target cells, and any operations related to formula calculation.

Fig. 4 illustrates an exemplary system architecture 400 of a method or apparatus for generating a table to which embodiments of the invention may be applied.

As shown in fig. 4, the system architecture 400 may include

terminal devices

401, 402, 403, a network 404, and a server 405. The network 404 serves as a medium for providing communication links between the

terminal devices

401, 402, 403 and the server 405. Network 404 may include various types of connections, such as wire, wireless communication links, or fiber optic cables, to name a few.

A user may use

terminal devices

401, 402, 403 to interact with a server 405 over a network 404 to receive or send messages or the like. The

terminal devices

401, 402, 403 may have installed thereon various communication client applications, such as shopping-like applications, web browser applications, search-like applications, instant messaging tools, mailbox clients, social platform software, etc. (by way of example only).

The

terminal devices

401, 402, 403 may be various electronic devices having a display screen and supporting web browsing, including but not limited to smart phones, tablet computers, laptop portable computers, desktop computers, and the like.

The server 405 may be a server that provides various services, such as a background management server that provides support for websites browsed by users using the

terminal devices

401, 402, 403. The backend management server may analyze and otherwise process data such as a received update table request, and feed back a processing result (e.g., a value of a target cell — by way of example only) to the terminal device.

It should be noted that the method for generating the table provided by the embodiment of the present invention is generally executed by the server 405, and accordingly, the apparatus for generating the table is generally disposed in the server 405.

It should be understood that the number of terminal devices, networks, and servers in fig. 4 is merely illustrative. There may be any number of terminal devices, networks, and servers, as desired for implementation.

Referring now to FIG. 5, shown is a block diagram of a computer system 500 suitable for use with a terminal device implementing an embodiment of the present invention. The terminal device shown in fig. 5 is only an example, and should not bring any limitation to the functions and the scope of use of the embodiments of the present invention.

As shown in fig. 5, the computer system 500 includes a Central Processing Unit (CPU)501 that can perform various appropriate actions and processes according to a program stored in a Read Only Memory (ROM)502 or a program loaded from a storage section 508 into a Random Access Memory (RAM) 503. In the RAM 503, various programs and data necessary for the operation of the system 500 are also stored. The CPU 501, ROM 502, and RAM 503 are connected to each other via a bus 504. An input/output (I/O) interface 505 is also connected to bus 504.

The following components are connected to the I/O interface 505: an input portion 506 including a keyboard, a mouse, and the like; an output portion 507 including a display such as a Cathode Ray Tube (CRT), a Liquid Crystal Display (LCD), and the like, and a speaker; a storage portion 508 including a hard disk and the like; and a communication section 509 including a network interface card such as a LAN card, a modem, or the like. The communication section 509 performs communication processing via a network such as the internet. The driver 510 is also connected to the I/O interface 505 as necessary. A removable medium 511 such as a magnetic disk, an optical disk, a magneto-optical disk, a semiconductor memory, or the like is mounted on the drive 510 as necessary, so that a computer program read out therefrom is mounted into the storage section 508 as necessary.

In particular, according to the embodiments of the present disclosure, the processes described above with reference to the flowcharts may be implemented as computer software programs. For example, embodiments of the present disclosure include a computer program product comprising a computer program embodied on a computer readable medium, the computer program comprising program code for performing the method illustrated in the flow chart. In such an embodiment, the computer program may be downloaded and installed from a network through the communication section 509, and/or installed from the removable medium 511. The computer program performs the above-described functions defined in the system of the present invention when executed by the Central Processing Unit (CPU) 501.

It should be noted that the computer readable medium shown in the present invention can be a computer readable signal medium or a computer readable storage medium or any combination of the two. A computer readable storage medium may be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination of the foregoing. More specific examples of the computer readable storage medium may include, but are not limited to: an electrical connection having one or more wires, a portable computer diskette, a hard disk, a Random Access Memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing. In the present invention, a computer readable storage medium may be any tangible medium that can contain, or store a program for use by or in connection with an instruction execution system, apparatus, or device. In the present invention, however, a computer readable signal medium may include a propagated data signal with computer readable program code embodied therein, for example, in baseband or as part of a carrier wave. Such a propagated data signal may take many forms, including, but not limited to, electro-magnetic, optical, or any suitable combination thereof. A computer readable signal medium may also be any computer readable medium that is not a computer readable storage medium and that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a computer readable medium may be transmitted using any appropriate medium, including but not limited to: wireless, wire, fiber optic cable, RF, etc., or any suitable combination of the foregoing.

The flowchart and block diagrams in the figures illustrate the architecture, functionality, and operation of possible implementations of systems, methods and computer program products according to various embodiments of the present invention. In this regard, each block in the flowchart or block diagrams may represent a module, segment, or portion of code, which comprises one or more executable instructions for implementing the specified logical function(s). It should also be noted that, in some alternative implementations, the functions noted in the block may occur out of the order noted in the figures. For example, two blocks shown in succession may, in fact, be executed substantially concurrently, or the blocks may sometimes be executed in the reverse order, depending upon the functionality involved. It will also be noted that each block of the block diagrams or flowchart illustration, and combinations of blocks in the block diagrams or flowchart illustration, can be implemented by special purpose hardware-based systems which perform the specified functions or acts, or combinations of special purpose hardware and computer instructions.

The modules described in the embodiments of the present invention may be implemented by software or hardware. The described modules may also be provided in a processor, which may be described as: a processor comprises an input module, a unit heel module and a formula calculation module. Where the names of these modules do not in some cases constitute a limitation of the module itself, for example, the input unit may also be described as a "module inputting a value to a cell".

As another aspect, the present invention also provides a computer-readable medium that may be contained in the apparatus described in the above embodiments; or may be separate and not incorporated into the device. The computer readable medium carries one or more programs which, when executed by a device, cause the device to comprise:

generating coordinates of one or more first cells and one or more second cells in the table;

According to the technical scheme of the embodiment of the invention, the problems of poor expandability and maintainability of repeated code development can be solved by using a cell coordinate mode, the formula calculation code can be separated from the service code, and the effect similar to Excel spreadsheet formula calculation can be achieved.

The above-described embodiments should not be construed as limiting the scope of the invention. Those skilled in the art will appreciate that various modifications, combinations, sub-combinations, and substitutions can occur, depending on design requirements and other factors. Any modification, equivalent replacement, and improvement made within the spirit and principle of the present invention should be included in the protection scope of the present invention.

Claims

1. A method of generating a table, comprising:

2. The method of claim 1, wherein the first cell is a parameter cell and the second cell is a target cell.

3. The method of claim 1, wherein the value of the one or more first cells is obtained by at least one of:

Obtaining values for the one or more first cells from a database.

4. The method of claim 1, wherein generating the value of the one or more second cells is triggered by at least one of:

obtaining values for the one or more first cells in the table; or

Changing the value of the one or more first cells in the table.

5. The method of claim 1, further comprising:

generating a mapped set of coordinates and values for the one or more first cells; and

a set of formulas is generated based on the attributes of the table.

6. The method of claim 5, wherein generating the values for the one or more second cells using the coordinates for the one or more second cells and the values for the one or more first cells comprises:

for each of the one or more second cells:

7. The method of claim 1, wherein the coordinates of the one or more cells are generated using a data structure of the cells.

8. An apparatus for generating a table, comprising:

a cell module to generate coordinates of one or more first cells and one or more second cells; and associating the values of the one or more first cells with the coordinates of the one or more first cells;

a formula calculation module: generating values for the one or more second cells using the coordinates of the one or more second cells and the values for the one or more first cells.

9. An electronic device that generates a form, comprising:

one or more processors;

a storage device for storing one or more programs,

the one or more programs, when executed by the one or more processors, implement the method of any of claims 1-7.

10. A computer-readable medium, on which a computer program is stored, which, when being executed by a processor, carries out the method according to any one of claims 1-7.