JP2831866B2 - Spreadsheet software cooperation processing device - Google Patents

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION The present invention relates to a host computer and a personal computer / workstation (hereinafter referred to as P
(Referred to as C / WS), a data processing system that performs business processing, and in particular, spreadsheet software that enables online coordination processing between spreadsheet software running on PC / WS and application programs running on the host computer. The present invention relates to a cooperative processing device.

[0002] With the recent development of PC / WS, PC / W
It has become popular to perform simple tabular form processing using spreadsheet software of S. It is desired that business processing be made more efficient by linking such spreadsheet software with database processing in a host computer.

[0003]

2. Description of the Related Art Conventionally, spreadsheet software operating on a PC / WS does not directly cooperate with database processing and various business processes in a host computer, so data input to a table is collectively output from the host computer. Currently, it is performed manually while looking at a large amount of paper (print output). For this reason, there is a problem that end users in each department cannot effectively utilize such database information even though abundant database information is accumulated in the host computer.

[0004] In addition, the host-led conventional processing mode of searching, processing, and distributing abundant database information stored in the host computer in accordance with the end user's request for each department requires an increase in the number of departments and an increase in individual requirements. With an increase (such as an increase in search conditions), it cannot be handled. This is the main cause of delaying the development of application programs in the host computer and increasing the backlog.

[0005]

SUMMARY OF THE INVENTION The present invention solves the above-mentioned problems, eliminates the backlog of the host, and easily responds to the demands of the end users of the department who want to utilize the abundant database information of the host computer. The purpose is to be able to respond.

[0006]

FIG. 1 is a diagram illustrating the principle of the present invention. In FIG. 1, reference numeral 10 denotes a host computer having a CPU, a memory, and the like; 11, a communication processing unit for controlling communication with a terminal computer; 12, a prototype worksheet; Record area source sentence generating means, 14 is a generated record area source sentence, 15 is an application program executing means for executing an application program, 16 is a database, 17 is a distribution worksheet creating means for generating a distribution worksheet, 18
Is a distribution worksheet, 20 is a terminal computer such as a personal computer or a workstation (PC / WS) connected to the host computer 10 via a communication line, 21 is a transmitting means for transmitting the original worksheet 12, and 22 is a distribution worksheet 18 , Reception means 23, spreadsheet software, and display device 24 for displaying a table or the like handled by spreadsheet software 23.

In the present invention, the worksheet generated by the spreadsheet software 23 in the terminal computer 20 is transmitted to the host computer 10 by the transmission means 21. The communication processing unit 11 is an existing communication processing unit and receives the transmitted original worksheet 12.

[0008] The worksheet includes information defining the data structure and data format of a tabular data group having an array data structure, information defining display attributes when displaying the data on the display device 24, and operating the data. This is definition information including a procedure description to be performed.

The record area primitive sentence generating means 13 inputs the original worksheet 12 transmitted by the terminal computer 20,
This is a processing means for analyzing definition information (referred to as cell information) relating to individual data of a table constituting the worksheet and generating a record area source sentence 14 having an array data structure.

The application program executing means 15 is a processing means for executing an application program in which the generated record area primitives 14 are incorporated. The application program performs a process of transferring the data processing result obtained from the database 16 or the like to the record specified by the record area primitive 14.

The distribution worksheet creation means 17 receives the record output from the application program, and embeds the contents of each data item in the record into a table while referring to the definition information of the original worksheet 12 by performing a process. 18 is a processing means for creating the same.

The receiving means 22 in the terminal computer 20 comprises:
This is a processing unit that receives the distribution worksheet 18 via the communication processing unit 11. The spreadsheet software 23 can perform necessary reorganization of the table data and display on the display device 24 based on the distribution worksheet 18 received by the receiving unit 22.

[0013]

According to the present invention, the record area source sentence generation means 13 generates the record area source sentence 14 from the original worksheet 12 created by the spreadsheet software 23 operating on the terminal computer 20.
Is automatically generated so that the application program can incorporate the record area source sentence 14. Then, based on the data embedded in the record by the application program executing means 15, the distribution worksheet creating means 17 creates a distribution worksheet 18 and distributes it to the terminal computer 20.

As a result, cooperative processing between the spreadsheet software 23 of the terminal computer 20 and the application program of the host computer 10 becomes possible. Therefore, the EDP of the host
Departments can easily implement processing that distributes large amounts of data at once without significantly changing existing application programs.

On the other hand, an end user of a department extracts only data necessary for department management from a large amount of data distributed as a worksheet, and creates these data as, for example, a graph or another table. It can be reused or reworked. At this time, the end user of the department does not need to create a special application program, but only needs to use the search function and calculation function of the spreadsheet software 23 itself.

[0016]

FIG. 2 is an explanatory view of the processing outline of an embodiment according to the present invention. For example, when an original worksheet 12 as shown in FIG. 2 is created in the terminal computer 20, it is sent to the host computer 10. In this example, the table has three columns A, B, and C. In the host computer 10,
The record area primitive sentence generating means 13 converts the original worksheet 1
2, a record area primitive sentence 14 is generated. In this example, the COPY library 30 allows the COBOL program to be incorporated into the program by a COPY clause.
Has been generated as.

The application program 31 described in the COBOL language incorporates the record area source sentence 14 into the program according to the COPY phrase (COPYREC001). Then, necessary data is written into this record. In the case of a PL / 1 program, INCLUDE
Such an incorporation can be performed using a statement.

In response to the record output from the application program 31, the distribution worksheet creating means 17 creates a distribution worksheet 18. This distribution worksheet 18 contains
As shown in FIG. 2, necessary data is embedded by the application program 31. By sending this to the terminal computer 20, the spreadsheet software 23 of the terminal computer 20 can use the data of the host computer 10.

Next, embodiments of the present invention will be described in more detail with reference to specific examples. FIG. 3 is an example of a prototype worksheet in the embodiment of the present invention, FIG. 4 is an example of a record area primitive,
5 and 6 show an example of an application program according to the embodiment of the present invention, and FIG. 7 shows an output example of a distribution worksheet according to the embodiment of the present invention.

The original worksheet 12 has information such as, for example, shown in FIG. The contents are as follows. (1) Describing comment lines Write comment lines as necessary. Annotation lines are considered fixed text data. In data distribution (creation of the distribution worksheet 18), the comment line is inherited and embedded in the distribution worksheet 18 as it is. The row position / column position / text information at this time is the same as that described in the original worksheet 12.

(2) Description of heading items Heading items are described as necessary. Heading items are considered fixed text data. In data distribution (creation of the distribution worksheet 18), the heading items are inherited and embedded in the distribution worksheet 12 as they are. The row position / column position / text information at this time is the same as that described in the original worksheet 12.

(3) Description of Variable (HINMEI) A data item for which the application program specifies variable embedding information is described as a variable. Here, the variable name, "HI
“NMEI” corresponds to the data item name “article name” described in the record / data item definition part.

In this example, the variable (HIMMEI) is an array, and the parentheses in the subscript indicate the array index. The description of the variables in the table description defines the correspondence between the data items specified by the application program and the positions at which the data is embedded in the table.

(4) Description of Variable (ZAIKO) The data item for which the application program specifies variable embedding information is described as a variable. Where the variable name, "ZA
“IKO” corresponds to the data item name “previous month end stock quantity” described in the record / data item definition part.

In this example, the variable (ZAIKO) is an array, and the parentheses in the subscript indicate an array index. (5) Description of variable (NUUKO) The data item for which the application program specifies variable embedding information is described as a variable. Where the variable name, "NU
"UKO" corresponds to the data item name "number of storages" described in the record / data item definition part.

In the present description example, the variable (NUUKO) is an array, and the parentheses in the subscripts indicate the array index. (6) Description of variable (SHUKKO) The data item for which the application program specifies variable embedding information is described as a variable. Here, the variable name, "SH
“UKKO” corresponds to the data item name “number of dispatches” described in the record / data item definition part.

In this description example, the variable is a variable (SHUKKO) array, and parentheses in the subscript indicate array indices. (7) Description of arithmetic expression Describe the arithmetic expression. In this example, the number of stocks at the end of the current month is obtained by calculation. The number of stocks for this month is obtained by the following calculation.
The detailed description format of the arithmetic expression is in spreadsheet software 2.
3 depends on the support specification.

The number of stocks in the current month = the number of stocks at the end of the previous month (Bn) + the number of warehouses (Cn) −the number of warehouses (Dn) (8) Description of variables Describe the function ($ SUM; total). In this example, the function (合計 SUM) is used to calculate the total for each heading item (the number of stocks at the end of the previous month / the number of warehouses / the number of warehouses). The detailed description format of the function depends on the specifications supported by the spreadsheet software 23.

(9) Definition of Record The record specified by the application program by the WRITE instruction is defined. The record definition declares the start of the data item that follows, and also defines the "record name" specified by the application program.

(10) Definition of Data Item The attribute and data item name of the variable specified by “table description” as a data item are defined.

Here, the attribute indicates the data type attribute of the data item, and the data item name is the name of the data item specified by the language in which the application program is described. Based on the original worksheet 12 as described above, a record area original sentence 14 shown in FIG. 4 is generated. 4 shown in FIG. 4 has the following meaning.

Record Name This is the record name specified in the definition of the data item on the prototype worksheet 12. The application program specifies this record name and issues a WRITE instruction.

Data Item (Product Name) This is the data item (product name) specified in the definition of the data item of the original worksheet. Here, the “article name” is an array to which 10 characters of the Japanese attribute are given as the type attribute of the data item.

Data Item (previous month end inventory) This is the data item (previous month end inventory) specified in the definition of the data item in the prototype worksheet 12. Here, "previous month end stock quantity" is an array in which six digits of external decimal numbers are given as type attributes of data items.

Data Item (Number of Warehousing) This is a data item (number of warehousing) specified in the definition of the data item of the original worksheet 12. Here, the “storage number” is an array in which six digits of an external decimal number are given as the type attribute of the data item.

Data Item (Number of Outgoings) This is the data item (number of outgoings) specified in the definition of the data item in the original worksheet 12. Here, the “number of dispatches” is an array in which six digits of an external decimal number are given as the type attribute of the data item.

The record area primitive 14 as described above is
For example, it is incorporated into the COBOL application program 31 shown in FIGS. In the application program 31, the CO of "COPY inventory management-record"
The PY phrase is an instruction for instructing to incorporate the record area primitive 14 shown in FIG.

The "OPEN display file" is an instruction to open a display file for embedding data relating to a worksheet. Next, "O
The database 16 is opened by the "PEN database" to perform database processing such as searching for necessary data.

As shown in FIG. 6, information to be embedded in the worksheet, such as the setting of the product name, the setting of the number of stocks at the end of the previous month, the setting of the number of incoming goods, and the setting of the number of outgoing goods, are recorded in each data item in the original text of the record area. Set.

Thereafter, in order to create the distribution worksheet 18, the display record is written by the following statement. "WRITE display record FROM inventory management-record". Based on this sentence, the distribution worksheet creating means 17 writes out the incorporated record area original sentence and creates the distribution worksheet 18.

FIG. 7 shows an output example of the distribution worksheet 18. 7 shown in FIG. 7 have the following contents. Item name display The data embedded in the application program as variable text data (the value set in the data item "item name") is
Distribution worksheet 18 of WS spreadsheet software 23
Is displayed on a display or a printer device by the process of referring to.

Display of Last Month's Stock Number The value of each item of “last month's stock number” extracted from the database by the application program and embedded in the data item is PC /
Distribution worksheet 18 of WS spreadsheet software 23
Is displayed on a display or a printer device by the process of referring to.

Indication of the number of warehouses The value such as the item of “the number of warehouses” extracted from the database by the application program and embedded in the data item starts up the spreadsheet software of PC / WS and refers to the received distribution worksheet. Is displayed on a display or a printer device.

Display of Number of Outgoing Items The value of each item of “number of outgoing items” extracted from the database by the application program and embedded in the data item is obtained by referring to the distribution worksheet 18 of the spreadsheet software 23 of PC / WS. It is displayed on a display or a printer.

Display of Inventory at the End of Current Month The spreadsheet software 23 of the PC / WS is started, and the distribution worksheet 18 received by the spreadsheet software 23 is referred to. Is displayed. In other words, the operation expression described on the worksheet is evaluated and the operation is performed only after the application program gives data to the operated item,
The worksheet is PC / WS spreadsheet software 2
3.

Display of the Total Field By starting the spreadsheet software 23 of the PC / WS and referring to the distribution worksheet 18 received by the spreadsheet software 23, “total” is displayed on a display or a printer. That is, the function described on the worksheet is evaluated and the processing of the function is performed only after the application program gives the function factor as data and then the worksheet is converted to the spreadsheet software 23 of PC / WS.
Is the time point referred to by

The terminal computer 20 receives the distribution worksheet 18 as described above and processes it with the spreadsheet software 23. FIG. 8 is a flowchart illustrating a process of generating a record area source sentence according to the embodiment of the present invention.

The record area primitive sentence generating means 13 inputs various kinds of definition information defined in the original worksheet 12, and the record area primitive sentence 14 for incorporating the application program and the distribution worksheet creating means 17 execute Worksheets composed of various information necessary for processing
Generate a format definition body.

The original worksheet 12 is roughly classified into a "table description section" and a "record / data item description section". The process of generating the record area primitive 14 simply refers to the definition description of the record data item description section on the original worksheet 12 and describes a language (COB) that describes the application program.
OL, PL / I, etc.).

The following processes (a) to (e) as shown in FIG. 8 are performed. (a) Obtain one line of definition data from the record data item description section of the prototype worksheet 12.

(B) When the definition data ends, the processing ends. (c) Determine whether the definition data indicates a record definition. In the case of a record definition, the process proceeds to the process (d).

(D) Generate a record sentence corresponding to the record definition. (e) Perform the following data sentence generation processing. -Set level information (separate levels in case of array).

Obtain a data item name and set it. -Set the data item attribute by obtaining the variable type specification. -Obtain the data item length and set it.

The generation of the worksheet format definition is performed as follows. This worksheet format definition includes information that the distribution worksheet creation means 17 that has received the embedded information as a record from the application program refers to for convenience of its own processing. This generation processing is a first phase of generating the data item intermediate text (also serves as a generation phase of the record area source sentence 14).
And a second phase for analyzing the table description part, and a third phase for creating a final definition from the intermediate text.

In the first phase for generating the data item intermediate text, the record data item description section 4 shown in FIG.
From 0, a data item intermediate text 41 is generated by the record area source sentence generating means 13. The data item intermediate text 41 contains the variable name, data type, item length, number of repetitions,
It consists of information such as the offset in the record.

FIG. 10 shows the flow of processing for generating a worksheet format definition in the second phase of analyzing the table description part. One frame of the display description section is called a “cell”. Here, addresses are assigned to the cells. For example, the vertical direction is identified by a number (1, 2, 3,...), And the horizontal direction is identified by an alphabetic character (A, B, C,...).

In the second phase, these individual cells are analyzed one by one, for example, the cells in the vertical direction are analyzed in one row, and when the analysis of all the rows is completed, all the cells in the next row are analyzed. Analyze and analyze the entire table.

As shown in processing (h) of FIG. 10, it is determined whether or not each cell is a variable cell.
As shown in (i), the correspondence between variables and data items is established. Then, the cell information is translated by processing (j) to create a cell intermediate text.

FIG. 11 is an explanatory diagram of the worksheet format definition generated in this manner. The cell intermediate text 42 and the data item intermediate text 41 are shown in FIG.
The relationship is as shown in FIG. Cell intermediate text 42
In the above, the other cell information is mainly a display / print attribute of a cell (ruled line, numeric item edit attribute, etc.).

As a final phase, a more physical text (definition table) is created from a logical intermediate text as shown in FIG. 11 in order to speed up the process of generating a distribution worksheet. The description is omitted because it does not relate to the gist of the invention.

FIG. 12 is an explanatory diagram of a distribution worksheet creation process according to the embodiment of the present invention. As shown in FIG. 12, the distribution worksheet creation unit 17 receives a record in which the embedded information is embedded as in the example of the inventory record from the application program 31. Then, the fixed text cell and the function / operation cell are restored by referring to the worksheet format definition body 50 described above. By embedding the embedding information from the application program 31 in the variable cell,
Complete a table such as the distribution worksheet 18 shown in FIG.

The processing procedure of the transmitting means 21 and the receiving means 22 in the terminal computer 20 shown in FIG. 1 can be realized by using a well-known communication technique, so that detailed description will be omitted.

Next, an application example of utilizing the present invention to a department work will be described with reference to FIGS. FIG. 13 shows an example of department utilization of data distribution (with calculations, functions, and macros) using a worksheet having a plurality of tables. In this example,
A trial calculation table 62 is created from the raw data of the inventory table 60 and the raw data of the unit price table 61. As the raw data of the stock table 60 and the raw data of the unit price table 61, data distributed from the host computer can be used.

The trial calculation table 62 is created by the following formulas and functions of the spreadsheet software 23. Obtain Retail Price For the retail price, data is copied from the unit price table 61. An example of an operation expression (function expression) in the spreadsheet software 23 is shown below. This arithmetic expression depends on the specification supported by the spreadsheet software 23. The same applies to the following. "@MAT (@SEARCH (G5, A14: A20),
2, A14: C20) "Note: Cell address differs for each product name.

Determining the purchase price The purchase price is obtained by copying data from the unit price table 61. The formula for copying is the same as that for the retail price (the only difference is the address of the target cell).

Obtaining the Number of Outgoings The number of outgoings is obtained by copying data from the inventory table 60. The formula for copying is the same as that for the retail price (the only difference is the address of the target cell).

Obtaining Sales The sales are obtained by a calculation formula. An example of an operation formula (calculation formula) in the spreadsheet software 23 is shown below. "Sales = Retail Price * Number of Outgoings = + H5 * J5" Note: Cell address varies by product name.

Determining the Sales Composition Ratio The sales composition ratio is obtained by a function and a calculation formula. An example of an operation formula (calculation formula) in the spreadsheet software 23 is shown below. “Sales composition ratio = Sales of each product / Total sales = R
OUND (K5 / K10 # * 100, -2) "Note: The cell address differs for each product name.

Obtaining Gross Profit Gross profit is calculated by a calculation formula. An example of an operation formula (calculation formula) in the spreadsheet software 23 is shown below. "Gross profit = (Retail price-Purchase price) * Number of warehouses = + H5-
I5 * J5 "Note: Cell address differs for each product name.

FIG. 14 shows an example of the attendance table of all employees.
This is an application example of data distribution using a worksheet having a large amount of data. Individual departments extract only necessary data and create aggregated data for that department. That is, the time at which all employees go to work and the time at which they leave work are stored in the host computer 10.
Is distributed in the form shown in FIG. Each department can extract data on employees belonging to that department. As a extracting method, for example, a data search / extraction function of spreadsheet software described in the example shown in FIG. 12 can be used.

[0071]

As described above, according to the present invention,
There are already a large number of data distribution tasks using forms,
The present invention is applied to a user / business that is recounting distribution data by using spreadsheet software of PC / WS, and has an effect that manual input of tally information can be reduced. At this time, an existing application program can be used as an application program for data distribution. At this time, it is necessary to change the parameter level of the display file, such as changing the file management entry including the destination type, specifying the object name, and registering mode, but the business process itself and the main body of the database process can be used as it is. . Specifically, the following effects are obtained.

By linking the host data (distribution data) with the calculation / function / macro functions of the spreadsheet software, the database information in the host computer can be utilized by the department without creating a new program.

A manual input operation while looking at the paper can be eliminated. Here, the manual input includes a re-input operation which is an input operation for storing the information in the database of the host computer once.

The complicated operation of searching for necessary information from a large amount of data printed on paper can be eliminated, and the efficiency of searching for necessary information can be improved. In general, the information distributed from the host computer is often collective comprehensive data including other department information unnecessary for a specific department.

It is possible to accumulate host database information for each department and to utilize the accumulated rich information in the department. Store the data necessary for the department, and
It can be reused when a new usage request is generated, such as monthly / yearly total usage.

[Brief description of the drawings]

FIG. 1 is a diagram illustrating the principle of the present invention.

FIG. 2 is an explanatory diagram of a processing outline of an embodiment according to the present invention.

FIG. 3 is a diagram showing an example of a prototype worksheet according to the embodiment of the present invention.

FIG. 4 is a diagram illustrating an example of a record area source sentence according to the embodiment of the present invention.

FIG. 5 is a diagram showing an example of an application program according to the embodiment of the present invention.

FIG. 6 is a diagram showing an example of an application program according to the embodiment of the present invention.

FIG. 7 is a diagram illustrating an output example of a distribution worksheet according to the present invention.

FIG. 8 is a diagram showing a flow of processing for generating a record area primitive sentence according to the embodiment of the present invention.

FIG. 9 is a diagram showing an example of data item intermediate text generation in the embodiment of the present invention.

FIG. 10 is a diagram illustrating a processing flow of generating a worksheet format definition body according to the embodiment of the present invention.

FIG. 11 is an explanatory diagram of a worksheet format definition body in the embodiment of the present invention.

FIG. 12 is an explanatory diagram of a distribution worksheet creation process according to the embodiment of the present invention.

FIG. 13 is an explanatory diagram of an application example of the present invention.

FIG. 14 is an explanatory diagram of an application example of the present invention.

[Explanation of symbols]

 DESCRIPTION OF SYMBOLS 10 Host computer 11 Communication processing unit 12 Original worksheet 13 Record area primitive sentence generation means 14 Record area primitive sentence 15 Application program execution means 16 Database 17 Distribution worksheet creation means 18 Distribution worksheet 20 Terminal computer 21 Transmission means 22 Receiving means 23 Spreadsheet software 24 Display device

──────────────────────────────────────────────────続 き Continued on the front page (58) Field surveyed (Int. Cl. ⁶ , DB name) G06F 19/00 G06F 13/00

Claims (1)

(57) [Claims] 1. A host computer on which an application program runs
Spreadsheet software coordination processing comprising (10) and a terminal computer (20) connected to the host computer via an online line and running spreadsheet software (23) that performs spreadsheet processing based on worksheet information A terminal for transmitting a worksheet generated by spreadsheet software to the host computer; and a receiving unit for receiving a distribution worksheet in which data is stored by the host computer. 22), wherein the host computer inputs the worksheet transmitted by the terminal computer, analyzes definition information relating to individual data of a table constituting the worksheet, and records a record area primitive comprising an array data structure. A record area source sentence generating means (13) for generating a sentence, and the generated record area source sentence are incorporated and data processing is performed on the record. The application program execution means (15) for executing the application program for transferring the result, and receiving the record output by the application program, display the contents of each data item in the record while referring to the definition information of the worksheet. And a distribution worksheet creating means (17) for creating a distribution worksheet by embedding the program into the spreadsheet. The spreadsheet software operating on the terminal computer and the application program operating on the host computer are linked. Spreadsheet software cooperative processing device.