KR102490941B1 - An online report creation system using Excel tool - Google Patents

Abstract

An online report creation system using an Excel tool that supports configuring a report on the web with at least one grid displaying a processed data set of a database, and supports creation of each grid in an Excel tool, A grid editing unit that edits the grid on the report screen on the top, stores and manages information about one grid object in one Excel document, and supports editing of the grid object through editing of the Excel document by the extended Excel tool; a control editing unit that creates and edits control objects; a worksheet initialization unit that, when the Excel tool is called to edit a grid object, opens an Excel document mapped to the grid object if it exists, and creates and initializes a new Excel document if it does not exist; a data set loading unit for loading a basic data set on the worksheet of the Excel document; an editing result storage unit for storing the Excel document and the edited result on the Excel document when the Excel tool is terminated; a grid display unit displaying the contents of a grid object according to the contents of the Excel document; and a report viewer unit displaying a report composed of grid and control objects on the web and displaying the grid object through the grid display unit.
According to the system as described above, by supporting the creation of a grid included in a report on the web in an Excel tool, a user can perform processing of a dataset through an interface familiar to the user, thereby increasing work efficiency.

Description

Online report creation system using Excel tool { An online report creation system using Excel tool }

The present invention supports the creation of a report on the web to process and display a basic data set stored in a database, but the report on the web is composed of a grid that displays the processed data set and a control object that processes the processed variables of the data set. It is about an online report writing system using the Excel tool.

In addition, the present invention relates to an online report writing system using an Excel tool, which supports creation of each grid in a report in an Excel tool, imports report contents prepared in the Excel tool, and converts it into a grid on the web.

In general, among office workers' working hours, the time required for document preparation occupies a high proportion of the total work. Reduction of costs due to the time and manpower required to prepare these documents is an essential part in terms of corporate productivity. To this end, a system that manages and automates tasks in an integrated manner is being introduced.

As an example, a reporting technology for querying and analyzing a database in a web environment has been proposed [Patent Document 1]. According to the prior art, a developer designs a report using a WYSIWYG method and a drag & drop method using data of a database server, the web server registers the designed report, and the report settings Accordingly, necessary data is imported from the database server and a report is created, and the user requests and views the report on a web browser.

In addition, a technique for outputting a report in real time by accessing and integrating data distributed in several places using a web service has also been proposed [Patent Document 2]. In addition, a technique in which an administrator selects table information received from a database on the web to create a mapping table, uses it to generate reports in various formats, and selects and confirms the table information on the web has also been proposed [Patent Document 3].

In addition, a technique for creating a report in the form of Excel on the web has also been proposed [Patent Document 4]. In the prior art, an Excel-type grid must be directly created on the web, and an interface of grid work on the web is provided similarly to that of Excel.

However, since it is difficult to provide various interfaces such as Excel on the web, and interface methods are different for each service, users may be uncomfortable with the interface on the web. That is, the interface on-line is not the same as the interface of the Excel program on-line.

However, users are more familiar with the interface of the (offline) Excel tool installed and used on their computer terminals. Therefore, when a user creates a report on the web, a technology that supports data processing with an offline application such as commercial Excel is required.

Korean Patent Registration No. 10-0497811 (Announced on July 1, 2005) Korean Patent Registration No. 10-0697155 (Announced on March 21, 2007) Korean Patent Publication No. 10-2013-0126012 (published on November 20, 2013) Korean Patent Registration No. 10-1951719 (2019.02.25. Notice)

An object of the present invention is to solve the above-described problems, and supports configuring a report on the web with at least one grid displaying a processed data set of a database, but creating each grid in an Excel tool. It is to provide an online report writing system using the Excel tool.

In addition, an object of the present invention is to add an extension program to the Excel tool, to import a dataset from a database on the Excel tool, to support setting processing variables of the dataset, and to automatically convert the created Excel sheet into a grid on the web. It is about an online report writing system using the Excel tool.

In order to achieve the above object, the present invention relates to an online report creation system using an Excel tool, which is performed by an extension module installed in an Excel tool of a user terminal and a server, and edits a grid on a report screen on the web, but one a grid editing unit that stores and manages information about a grid object in one Excel document and supports editing of the grid object through editing of the Excel document by an extended Excel tool; a control editing unit that creates and edits control objects; a worksheet initialization unit that, when the Excel tool is called to edit a grid object, opens an Excel document mapped to the grid object if it exists, and creates and initializes a new Excel document if it does not exist; a data set loading unit for loading a basic data set on the worksheet of the Excel document; an editing result storage unit for storing the Excel document and the edited result on the Excel document when the Excel tool is terminated; a grid display unit displaying the contents of a grid object according to the contents of the Excel document; and a report viewer unit displaying a report composed of grids and control objects on the web and displaying the grid objects through the grid display unit.

In addition, in the present invention, in an online report creation system using an Excel tool, the system sets processing variables on the worksheet of the Excel document, but sets the name of the variable definition cell to the name of the processing variable. The processing variable is used in a formula in a cell of a table to be displayed in a grid on the Excel document, the control editing unit maps the processing variable to a control object, and the report viewer unit maps the processing variable to a control object. It is characterized in that the input value of is set as a variable value of the corresponding processing variable.

In addition, according to the present invention, in an online report creation system using an Excel tool, the worksheet initialization unit includes a view area sheet for creating a table to be displayed on a grid, a data area sheet for storing basic data sets retrieved from a database, and processing variables. The parameter area sheet to be created is created as a worksheet of the Excel document, and the table on the view area sheet refers to the basic data set input on the data area sheet, but in the formula of the cell in the table on the view area sheet The cell of the data area sheet is referred to, and the grid display unit retrieves the basic data set from the database and stores it on the virtual sheet, and when each cell of the table of the grid object is reflected according to the formula, the referenced data area sheet It is characterized in that the content of the cell on the virtual sheet is reflected instead of the content of the cell.

In addition, in the present invention, in an online report creation system using an Excel tool, the processing variable is defined as the name of a cell on the parameter area sheet, and the processing variable is used in a formula in a cell of a table on the view area sheet. to be characterized

In addition, according to the present invention, in an online report creation system using an Excel tool, the editing result storage unit stores information on tables in the view area sheet as a hierarchical template, and the template is a data structure having a hierarchical structure. , The table structure in the view area sheet is converted into a hierarchical structure of the template, the property of each cell is converted into the property of a leaf node of the hierarchical structure of the template, and the grid display unit refers to the content of the template instead of the view area sheet. to reflect the contents of the grid.

In addition, in the present invention, in an online report creation system using an Excel tool, the editing result storage unit stores template information for a table on the view area sheet, a header row structure of the table, and a data row structure of the table. , The structure of the data row of the table is stored as attribute information including the format and formula of the first data row, and if the data row of the table is a cross row, the format of both the first data row and the second data row of the table is saved. characterized by storage.

In addition, in the present invention, in the online report creation system using an Excel tool, the grid editing unit, control editing unit, report viewer unit, and grid display unit are provided in the server, and the worksheet initialization unit, processing variable setting unit, and data set loading unit , The editing result storage unit is characterized in that provided in the expansion module.

As described above, according to the online report creation system using the Excel tool according to the present invention, by supporting the creation of the grid included in the report on the web in the Excel tool, the user can perform the processing of the dataset through a familiar interface. By doing so, the effect of increasing work efficiency is obtained.

In addition, according to the online report writing system using the Excel tool according to the present invention, by setting processing variables of the data set in the Excel tool, the user can more easily implement the interactive function of displaying the data set.

1 is a configuration diagram of the entire system for implementing the present invention.
2 is a block diagram of the configuration of an online report creation system using an Excel tool according to an embodiment of the present invention.
3 is a diagram illustrating an interface screen for editing a report on the web according to an embodiment of the present invention.
4 is a diagram illustrating a report edited on the web according to an embodiment of the present invention.
5 is a diagram illustrating an initialized Excel document according to an embodiment of the present invention.
6 illustrates a screen for defining processing variables on a parameter area sheet according to an embodiment of the present invention.
7 is a diagram illustrating a screen for loading a dataset according to an embodiment of the present invention.
8 is a diagram illustrating a screen of a data area sheet loaded with a basic data set according to an embodiment of the present invention;
9 is a diagram illustrating an editing result screen of a view area sheet of an Excel document according to an embodiment of the present invention.
10 is an exemplary diagram illustrating template information of a pivot table according to an embodiment of the present invention, and is an exemplary diagram for (a) a pivot table and (b) template information.
11 is an exemplary view of formula automatic calculation according to an embodiment of the present invention.

Hereinafter, specific details for the implementation of the present invention will be described according to the drawings.

In addition, in explaining the present invention, the same reference numerals are assigned to the same parts, and the repeated explanation thereof is omitted.

First, the configuration of the entire system for implementing the present invention will be described with reference to FIG.

As shown in FIG. 1, the entire system for implementing the present invention includes an Excel tool 20 and an extension module 60 installed in the user terminal 10, and a creation server that supports report creation by dataset on the web ( 30), and a database or DB server 40 for storing data sets. The user terminal 10 and the creation server 30 perform data communication through a network 80 such as the Internet.

First, the user terminal 10 is a computing terminal having a computing function, such as a personal computer (PC), a laptop computer, a tablet PC, a phablet, a PDA, and a smart phone. The user terminal 10 may have a communication function and transmit/receive data with the creation server 30 through the network 80 .

In addition, the user terminal 10 may install and run applications such as the Excel tool 20 and the extension module 60 .

Next, the Excel tool 20 is an application that performs Excel (a spreadsheet software trademark of Microsoft Corporation) and is a program system that is installed and driven in the user terminal 10 .

The Excel tool 20 provides a normal spreadsheet function and, in particular, provides a WYSIWYG or Drag & Drop interface. That is, the Excel tool 20 displays an Excel sheet on the screen and provides functions for performing normal spreadsheet operations on the Excel sheet.

In particular, the Excel tool 20 loads data or datasets onto an Excel sheet so that each cell of the Excel sheet has data. In addition, commands capable of performing operations such as calculations on the data of each cell are provided.

On the other hand, the Excel tool 20 creates an Excel document written on an Excel sheet (or worksheet). An Excel document is composed of a number of worksheets, and each worksheet is composed of objects, formats, and data written on the sheet.

Preferably, the Excel tool 20 is a spreadsheet application manufactured by Microsoft, and has a trademark of Excel.

In addition, the function of the Excel tool 20 is extended through the extension module 60. At this time, the function-extended Excel tool 20 will be referred to as an extended Excel tool.

Next, the extension module 60 is a program system installed in the computer terminal 10 and added in the form of a plug-in to the Excel tool 20, a function of loading a data set from a database, and a function of setting processing variables of a data set. provide etc.

That is, the extension module 60 is a program system that is added to the Excel tool 20 in a plug-in method or an add-in method and is executed. In particular, the extension module 60 is registered as at least one command (hereinafter referred to as a plug-in command) of the Excel tool 20, and is executed when the corresponding plug-in command is called from the Excel tool 20. As an example, when a plug-in command is registered on a menu on an Excel sheet and a corresponding command is selected from the menu, the extension module 60 is operated. As another example, when a control object is registered as an event operation and a corresponding event occurs, the extension module 60 may be operated.

In particular, the extension module 60 has an initial worksheet creation function, a processing variable setting function, a data set loading function, and the like, and commands for calling each function are registered in the Excel tool 20.

First, when the Excel tool 20 is called, the extension module 60 creates each worksheet representing the view area V, the data area D, and the parameter area P in the Excel document.

The worksheet of the view area (view area sheet) is a worksheet for designing the display form of a grid or table to be displayed on the screen on the web.

In addition, the worksheet of the data area (D) (hereafter referred to as a data area sheet) is a worksheet in which a data set loaded from a database is entered in each cell and stored. At this time, the data set loaded from the database is referred to as the basic data set.

Meanwhile, the dataset displayed on the view area sheet is a dataset processed by referring to the basic dataset. Therefore, the data set displayed on the view area sheet is referred to as a processing data set.

In addition, the worksheet of the parameter area P (hereinafter referred to as parameter area sheet) is a worksheet for setting processing variables of the data set. The processing variable is set as the name of one cell, and the variable value of the processing variable is set as the input value in the cell.

In addition, the extension module 60 provides a function of setting processing variables. At this time, since the name of the processing variable (variable name) is defined as the name of the cell, the cell value entered into the corresponding cell (variable definition cell) becomes the variable value of the corresponding processing variable. Therefore, processing variables on the Excel tool 20 are used as cell names. In other words. The variable value of the processing variable is the value entered in the variable definition field (cell) of the processing variable.

In addition, the extension module 60 supports a function of creating a query statement, loading a basic dataset from a database by the query statement, and storing the load information. In addition, the extension module 60 stores load information of the basic dataset. Basic dataset load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database.

Next, the creation server 30 is a normal server connected to the network 80 and supports creation of a report on the web, but also supports creation of a report analyzing a data set imported from the database 40 .

A report on the web is composed of a grid object that displays the processed data set and a control object that processes the processed variable of the data set. The creation server 30 provides services for creating grid objects and control objects.

First, the creation server 30 provides grid objects to be created or edited through the extended Excel tool 20, and correspondingly creates or stores an Excel document linked to each grid object. That is, in order to create or edit one grid object, the user creates or edits it in the extended Excel tool 20. In addition, the Excel document created or edited in the extended Excel tool 20 is stored, and is stored in association with the corresponding grid object. That is, the corresponding Excel document can be viewed as a file containing the information of the corresponding grid object.

In addition, when the creation of the Excel document is completed by the extended Excel tool 20, the creation server 30 reads the saved Excel document and creates or modifies a grid object according to the content defined in the Excel document.

At this time, the creation server 30 reads the processing variables defined in the parameter area sheet, defines them as processing variables, and sets them. In other words, declare the processing variable to be used in the control object.

In addition, the creation server 30 creates/edits the grid shape of the grid object according to the table shape of the view area sheet of the corresponding Excel document, imports the basic data set, and reflects it to the corresponding grid.

Also, the creation server 30 provides an interface to create a control object. Control objects include objects for receiving commands or data, such as buttons, combo boxes, check boxes, and input boxes.

The creation server 30 maps control objects to processing variables. When a control object is mapped to a processing variable, the input value of the corresponding control object is automatically mapped to the variable value of the corresponding processing variable. That is, when the creation server 30 detects an event in which an input value of a control object is updated (input), all objects are updated by binding the variable value of the mapped processing variable to the updated (input) input value.

Next, the database 40 is a normal database (DB) for storing datasets, and is provided with a database management system (DBMS) for managing data, and queries operations such as saving, deleting, and searching of datasets. (or query statement). In particular, the database 40 is a commercialized database and performs a data query service using a general query function for processing a dataset.

In particular, the database 40 is a database that stores big data. Also preferably, the database 40 is comprised of a relational database (RDB).

In addition, the database 40 may be implemented as a database directly connected to the creation server 30, or may be implemented as an independent DB server on the Internet.

In particular, the database 40 may be implemented as one server driven by a DBMS. That is, the database 40, as one server, provides a service of viewing/providing a requested data set according to a request of the creation server 30. Therefore, the database and DB server use the same reference number.

Next, an online report creation system using the Excel tool of the present invention will be described with reference to FIG. 2 .

As shown in FIG. 2, the online report writing system 30 using an Excel tool according to an embodiment of the present invention includes a screen editing unit 31 that provides a report screen design service on the web, and a grid editing unit that edits a grid ( 32), a control editing unit 33 that edits control objects, a report viewer unit 34 that displays reports composed of grids and control objects on the web, and a grid display unit 35 that displays the contents of grid objects according to Excel documents or templates. ), a worksheet initialization unit 61 that initializes the worksheet of an Excel document, a processing variable setting unit 62 that sets processing variables, a data set loading unit 63 that loads a data set, and stores editing results. It is composed of an editing result storage unit 64. In addition, it may be configured to further include a storage unit 38 for storing necessary data.

At this time, the screen editing unit 31, the grid editing unit 32, the control editing unit 33, the report viewer unit 34, and the grid display unit 35 are provided in the creation server 30, and the worksheet initialization unit 61, The processing variable setting unit 62, the data set loading unit 63, and the editing result storage unit 64 are provided in the extension module 60.

First, the screen editing unit 31 provides a report screen design service on the web.

A report on the web is composed of a grid object displaying a processed data set (processed data set) and a control object mapped to a processed variable to receive the value of the processed variable.

As shown in FIG. 3 , the screen editing unit 31 creates a grid object or a control object using a WYSIWYG method and a drag & drop method, and supports arranging them on the screen.

In addition, the screen editing unit 31 provides a detailed editing service for a grid object or control object through the grid editing unit 32 or the control editing unit 33 .

Next, the grid editing unit 32 edits the grid object, stores and manages information about one grid object in one Excel document, etc., and edits the grid object by editing the Excel document by the extended Excel tool 20. Edit.

That is, the grid editing unit 32 creates or stores an Excel document or the like mapped to each grid object. Excel documents (or editing result information) are composed of Excel documents, templates (grid templates), and data set load information. Template, data set load information, etc. will be referred to as additional information.

The corresponding Excel document is composed of a view area sheet, data area sheet, parameter area sheet, etc., and is an Excel file containing grid object information. A template (or grid template) is a data structure that stores the form, format, formula, etc. of a table (or grid) in a view area sheet. This is to analyze and use the contents of the view area sheet more quickly. In addition, data set load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database.

In particular, when receiving an editing request for each grid object, the grid editing unit 32 determines whether an Excel document mapped (linked) to the corresponding grid object exists. If present, the extended Excel tool 20 is executed while opening the corresponding Excel document. If it does not exist, a new Excel document is created, and the expanded Excel tool 20 is executed while opening the created Excel document. At this time, the expanded Excel tool 20 or the worksheet initialization unit 61 initializes the corresponding Excel document. In particular, an initial worksheet is created and initialized by the worksheet initialization unit 61 .

In addition, when the creation of the Excel document is completed by the expanded Excel tool 20, the grid editing unit 32 refers to the stored Excel document (or editing result information) and updates the grid object according to the content. At this time, preferably, the content of the grid object is displayed on the screen through the grid display unit 35 according to the content of the Excel document.

Next, the control editing unit 33 provides an interface to edit the control object.

Control objects are objects used to express web reports, such as buttons, combo boxes, shapes, and text boxes. In particular, control objects include objects for receiving commands or data, such as buttons, combo boxes, check boxes, and input boxes. At this time, the input command or data will be referred to as an input value of the control object.

The control editing unit 33 reads processing variable information from an Excel document of the grid object and declares processing variables (that is, defines and sets processing variables). That is, in the Excel document of the grid object, processing variables are declared in the parameter area sheet. The control editor 33 reads the processing variables stored in the Excel document of each grid object and declares the processing variables to be used in the control object.

Also, the control editing unit 33 provides a service of mapping control objects to processing variables. That is, the user can map a specific processing variable to a specific control object. Preferably, by defining the control object as the name of the process variable, the process variable and the control object are mapped.

When a control object is mapped to a processing variable, the input value of the corresponding control object is automatically mapped to the variable value of the corresponding processing variable.

In addition, when the update (input) of the input value of the control object is detected by the report viewer view 34 (update event), the variable value of the mapped processing variable is bound to the updated (input) input value. All objects are updated.

Next, the report viewer unit 34 outputs the edited report on the screen.

That is, the report viewer unit 34 outputs the contents of the control object and the grid object on the screen, and when an input event (update event) of the input value occurs in the control object, the variable value of the processing variable mapped to the control object It is updated to update all objects on the screen.

Specifically, the report viewer unit 34 outputs the contents of each grid object, and reads and outputs the contents defined in the Excel document of the corresponding grid object. At this time, the report viewer unit 34 edits the shape of the grid according to the contents of the Excel document or the like (template) through the grid display unit 35, reflects the processed data set on the corresponding grid, and outputs it.

In addition, the report viewer view 34 detects an event in which an input value of a control object is updated (input). And when an event is detected, all objects are updated by binding the variable value of the mapped processing variable to the updated (input) input value.

Preferably, report viewer view 34 includes a viewer that displays a grid. The grid viewer is composed of DOM (Document Object Model)-based components based on web format (HTML 5.0 or higher) or Canvas. Therefore, it supports extremely fast rendering speed.

4 illustrates displaying a report on the web.

Next, the worksheet initialization unit 61 initializes the worksheet of the Excel document.

As shown in FIG. 5, the worksheet initialization unit 61 creates each worksheet representing a view area (V), a data area (D), and a parameter area (P) in an Excel document.

The worksheet initialization unit 61 is executed when the Excel tool 20 is called.

The worksheet of the view area (hereinafter referred to as the view area sheet) is a worksheet for designing the display form of a grid or table to be displayed on the screen on the web. The user designs a table or the like on the view area sheet. The designed table is used in the form of a grid object. In particular, the user designs a table or the like using the interface of the Excel tool 20 .

In addition, each area or cell in the table on the worksheet in the view area refers to a cell in the data area sheet or a cell in the processing variable. That is, the user creates a view area sheet by utilizing an Excel function on the Excel tool 20 . At this time, a numerical value is directly input into each cell or area of the table, or another cell referred to is input. That is, the cell/region reference method is directly entered as the location of the cell (region) or as the cell name. Processing variables are entered as cell names of processing variables.

In addition, the data set displayed in the view area is a processed data set obtained by processing the basic data set.

In addition, the worksheet of the data area (D) (hereafter referred to as a data area sheet) is a worksheet in which a data set loaded from a database is entered in each cell and stored. At this time, the data set loaded from the database is referred to as the basic data set.

In addition, the worksheet of the parameter area P (hereinafter referred to as parameter area sheet) is a worksheet for setting processing variables of the data set. The processing variable is set as the name of one cell, and the variable value of the processing variable is set as the input value in the cell.

The worksheet initialization unit 61 creates a table defining processing variables on a parameter area sheet. The process variable table consists of a number of records each representing a process variable.

Each record consists of fields such as variable name (variable name), variable definition, initial value, and description. The variable name field is a cell to input the name (or variable name) of the processing variable, and the variable definition field is a cell to set its own cell name with the name (or variable name field value) of the processing variable. In addition, the initial value field and the description field are cells for inputting the initial value or description of each processing variable.

In particular, since the cell name of the variable definition field is set as the variable name (processing variable name), the value of the cell entered in the variable definition field becomes the variable value of the corresponding processing variable.

That is, the user can use the name of the processing variable in the form of a cell name on the Excel tool 20. For example, if the name of a processing variable is written in a cell formula on the Excel tool 20, the processing variable in the corresponding cell formula is replaced with a value set in the variable definition field (cell) of the processing variable. Therefore, the name of the processing variable on the Excel tool 20 can be used as a processing variable.

In addition, processing variables can be used as variables in the binding syntax of query statements. At this time, the variable value of the processing variable is a value entered in the variable definition field (cell) of the corresponding processing variable.

On the other hand, the worksheet initialization unit 61 refetches the basic data set by utilizing the data set load information when there is already stored data set load information, and updates the contents of the data area sheet.

Next, the processing variable setting unit 62 sets processing parameters on the parameter area sheet.

As shown in FIG. 6, when a variable name (variable name) is input in the variable name field of a specific record, the processing variable setting unit 62 converts the cell name of the variable definition field of the record to the input variable name (variable name). ) is set.

On the other hand, since the name (variable name) of the processing variable is defined as the name of the cell, the cell value input to the corresponding cell becomes the variable value of the corresponding processing variable. That is, the cell name is used as a variable.

That is, each processing variable is managed as a Name object in Excel, and the value of the corresponding Name is replaced with the value entered by the user in the corresponding cell. Name in Excel defines a unique name for cell addresses in Excel, and you can use that name. Then, the corresponding name corresponds to the corresponding cell (cell or cell address corresponding to the name), and the corresponding name is finally replaced with the cell value and calculated.

For example, define the name of cell A1 as "address", enter the value "90 Hyehwa-dong, Jongno-gu, Seoul" into cell A1, and use the formula "=address" in cell B1. At this time, "address" in the formula is a name, and the name is replaced with the value of the corresponding cell (corresponding cell) ("90 Hyehwa-dong, Jongno-gu, Seoul"). Therefore, in cell B1, the value of “90 Hyehwa-dong, Jongno-gu, Seoul” is finally calculated.

Next, the dataset loading unit 63 provides a service of preparing a query statement, loading a basic dataset from the database by the query statement, and storing the load information.

As shown in FIG. 7 , the dataset loading unit 63 provides a function or interface for inputting connection information such as a key for connecting to the database 40 . The user accesses the database 40 by inputting connection information such as a key.

In addition, the dataset loading unit 63 provides a function or interface for creating a query statement referring to a basic dataset in the database 40 . In particular, it provides a function of writing a query statement directly or through a drag-and-drop GUI (Graphic User Interface).

As shown in FIG. 8 , the data set loading unit 63 loads a basic data set from a database using a query statement and outputs it to a data area sheet. At this time, the output position may be determined in advance or set by the user. At this time, preferably, the field name of the data set is output on the first row of the corresponding data area sheet, and the data set is output from the next row.

In addition, the dataset loader 63 stores load information of the basic dataset. Basic dataset load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database. Preferably, output location information indicating a location address to output the basic dataset may be further included.

DB connection information manages database connection information in the system, and includes the DB server address and key values for accessing the information. Database connection information is extracted internally with the corresponding key.

A query statement is a normal SQL statement to be executed in the database.

In addition, the output location information includes an address value for a location to output a query execution result. As an example, it is displayed as "D1$A1", which means to output from cell A1 of the D1 worksheet.

Next, the editing result storage unit 64 stores editing results performed on the worksheet when the Excel tool 20 is terminated. In particular, the editing results are Excel documents, templates, and data set load information.

An Excel document is a document worked in the Excel tool 20, and is saved as a document itself.

As described above, an Excel document is composed of a view area sheet, a data area sheet, and a parameter area sheet. In the data area sheet, the basic data set is entered. At this time, the user edits the table for the grid in the view area sheet, and edits the analyzed data to be displayed in the table with reference to the basic data set.

As shown in Fig. 9, in a table in a view area sheet, a cell refers to data (or cells) in a data area sheet. Cell C5 of the view area sheet contains a formula using the function sumif. At this time, some of the variables of the function sumif refer to cells or regions of the data area sheet D1.

Viewing the data area sheet, users can clearly see the structure of the underlying dataset pulled from the database. Therefore, the user can easily design a table in the view area sheet by using the data in the data area sheet.

In addition, data set load information consists of DB connection information for connecting to the database and query statements (SQL, etc.) to be executed in the database.

Also, a template (or grid template) is a data structure that stores the form, format, formula, etc. of a table (or grid) of a view area sheet in an Excel document. This is to interpret and use the contents of the view area sheet of the Excel document more quickly. Preferably, the template information is stored in a standard script data format such as JSON (JavaScript Object Notation).

In particular, each cell of the table on the view area sheet references a cell of the underlying dataset on the data area sheet as a formula. Therefore, the cell formula information in the template includes information referring to cells (or basic data sets or data of basic data sets) on the data area sheet.

Meanwhile, as another embodiment, it may be implemented by directly interpreting an Excel document without using a template. In this case, it is not created according to the template.

In particular, the editing result storage unit 64 extracts grid structure information from the table on the view area sheet of the Excel document and stores it as template information.

That is, grid structure information such as the structure of each table and the properties (format, formula, etc.) of each table or cell is extracted from the table on the view area sheet and stored as a template. Preferably, the template is written in a format having a hierarchical structure such as JSON. The table structure is converted into a hierarchical structure of the template, and the attributes of each cell of the table are converted into the attributes of the leaf nodes of the hierarchical template structure. The properties of each cell consist of cell values, formats, and formulas.

10 is a diagram illustrating grid structure information of a template for a table. That is, FIG. 10A illustrates a pivot table of a view area sheet of an Excel document, and FIG. 10B illustrates template information (grid structure information) of the pivot table of FIG. 10A. In particular, template information is stored in JSON (JavaScript Object Notation) format. That is, the pivot table is decomposed into a hierarchical structure, and is composed of elements of each hierarchy and attribute values of the elements.

In addition, if the object is a table, only the structure information of the header row of the corresponding table and the first row of data is stored as a template. If the format of data rows in a table is cross-row, all formats (attributes) of two consecutive data rows are saved.

Next, the grid display unit 35 generates a grid by analyzing an Excel document or the like. That is, a grid structure is created and output according to the template, the basic data set is imported according to the loading information of the basic data set of the template, and the processed data set is reflected on the grid and output.

First, the grid display unit 35 creates a grid shape according to the grid structure of the template. As described above, since the template file is written in a standard script data format such as JSON, it is analyzed using the parser of the script data format.

That is, in the case of a pivot table object, the pivot table is restored by interpreting the object's JSON data, and in the case of a general table object, the object's JSON data is interpreted to restore the header row of the table and the first row of data (or the first and second rows). row) is restored. Preferably, the grid herein may be data loaded onto memory.

In particular, if there is a user request in the server, it is interpreted in real time. On the other hand, template files are lighter than Excel documents and can be loaded at high speed.

Also, the grid display unit 35 loads basic data from the database according to the load information. Preferably, the latest query result of the database is reflected in real time.

The grid display unit 35 refers to the load information, accesses the database using the DB connection information, and generates and requests a query statement using the query statement information. In other words, it accesses the database after being authenticated with the key value of the DB connection information. In addition, a query statement is created by reflecting processing variables using a binding statement. At this time, the variable value of the processing variable is set as the input value of the control object mapped to the corresponding processing variable.

Preferably, the grid display unit 35 loads the basic data set retrieved from the database (DB) into memory. At this time, the basic dataset is loaded in the form of a virtual sheet having rows and columns. In other words, it is loaded so that the data of the underlying dataset can be referenced based on the rows and columns of the worksheet.

In addition, the grid display unit 35 reflects and inserts the basic data set into the data area of the grid.

In particular, if the form of the dataset and the corresponding grid is a table, as many rows as the number of records (data rows) of the dataset are dynamically created in the corresponding grid. After identifying the binding location, dynamically create as many rows as the number of records.

In addition, when the cell of the table is a formula, the grid display unit 35 interprets the formula and inserts the calculated value. In particular, if the cell formula refers to a cell of the data area sheet, the cell value is calculated by referring to the updated basic data set (or the basic data set loaded in memory).

In particular, since the basic data set (updated basic data set) imported from the database is stored in the form of a virtual sheet, formulas referring to cells of the data area sheet described in the Excel document can be used as they are. At this time, the name of the data area sheet is replaced with a virtual sheet (virtual sheet loaded in memory) and interpreted.

That is, the grid display unit 35 automatically generates formulas according to the number of records.

As shown in FIG. 11, after identifying the binding position in the template, as many rows as the number of records are dynamically created. At this time, if a formula exists in the data area by determining the header and data area, the formula is automatically created along with the row creation. .

The formula is created by automatically increasing/decreasing based on the address of the row created by interpreting the absolute and relative addresses of Excel. An absolute address represents a fixed, unchanging cell address. In the relative address, the address value changes according to the relative position of the position of the reference cell and the position of the corresponding cell.

Also, as shown in FIG. 11 , the grid display unit 35 supports cross rows and styles (forms). That is, it supports the case where the formats of the cross rows are different. That is, after identifying the binding position in the template, the style (format) of the data area is reflected in the newly created row. When the size of a row in the data area is 2, the style (format) of the cross row is supported and the style (format) can be reflected alternately.

In addition, the grid display unit 35 supports formulas in Excel on the grid. The expression is defined in the template. Formulas can be newly created according to data binding rules. Data binding means that if a formula exists at the last address of this location when a processed data set is output in the form of a table, the corresponding formula is automatically generated for each row.

In the above, the invention made by the present inventors has been specifically described according to the above embodiments, but the present invention is not limited to the above embodiments, and various changes can be made without departing from the gist of the present invention.

10: user terminal 20: excel tool
30: writing server 31: screen editing unit
32: grid editing unit 33: control editing unit
34: report viewer unit 35: grid display unit
38: storage unit
60: extension module 61: worksheet initialization unit
62: processing variable setting unit 63: data set loading unit
64: editing result storage unit
80: network

Claims (7)

In the online report creation system using an Excel tool, including a creation server and a user terminal,
The creation server,
A grid editing unit that edits a grid on a report screen on the web, stores and manages information about one grid object in one Excel document, and supports editing of the grid object through editing of the Excel document by an extended Excel tool;
a control editing unit that creates and edits control objects;
a grid display unit displaying the contents of the grid object; and,
A report viewer unit displaying a report composed of grid and control objects on the web, displaying the grid object through the grid display unit,
The extension module installed in the Excel tool installed in the user terminal,
a worksheet initialization unit that opens an Excel document mapped to the grid object when the Excel tool is called to edit the grid object, and creates and initializes a new Excel document if it does not exist;
a data set loading unit for loading a basic data set on the worksheet of the Excel document;
When the Excel tool is terminated, an editing result storage unit for storing the Excel document and a result edited on the Excel document,
The online report creation system using an Excel tool, characterized in that the grid display unit displays the contents of the grid object according to the contents of the Excel document stored by the editing result storage unit.
According to claim 1,
The extension module further includes a processing variable setting unit that sets processing variables on the worksheet of the Excel document and sets the name of the variable definition cell to the name of the processing variable,
The processing variable is used in a formula in a cell of a table to be displayed in a grid on the Excel document,
The control editing unit maps the processing variable to a control object,
The report viewer unit online report creation system using an Excel tool, characterized in that for setting the input value of the control object mapped to the processing variable as a variable value of the corresponding processing variable.
According to claim 2,
The worksheet initialization unit creates a view area sheet for creating a table to be displayed on the grid, a data area sheet for storing basic data sets retrieved from a database, and a parameter area sheet for creating processing variables as worksheets of the Excel document;
A table on the view area sheet refers to an underlying data set entered on the data area sheet, and a formula of a cell in the table on the view area sheet refers to a cell of the data area sheet;
The grid display unit fetches the basic data set from the database and stores it on the virtual sheet, and when each cell of the table of the grid object is reflected according to a formula, instead of the content of the cell of the data area sheet to be referred to, the corresponding data area on the virtual sheet is reflected. An online report writing system using an Excel tool, characterized in that it is reflected as the contents of a cell.
According to claim 3,
The processing variable is defined as a name of a cell on the parameter area sheet, and the processing variable is used in a formula in a cell of a table on the view area sheet.
According to claim 3,
The editing result storage unit stores information about tables in the view area sheet as a hierarchical template,
The template is a data structure having a hierarchical structure, converting a table structure in the view area sheet into a hierarchical structure of the template, and converting a property of each cell into a property of a leaf node of the hierarchical structure of the template;
The online report creation system using an Excel tool, characterized in that the grid display unit reflects the contents of the grid by referring to the contents of the template instead of the view area sheet.
According to claim 5,
The editing result storage unit stores template information for the header row structure of the table and the data row structure of the table with respect to the table on the view area sheet, and converts the data row structure of the table to the format and formula of the first data row. An online report creation system using an Excel tool, characterized in that it is stored as the attribute information containing, and if the data row of the table is a cross row, both the format of the first data row and the second data row of the table are saved.
delete

Images