KR101013810B1 - An excel-based management system for updating db tables and the method thereof - Google Patents

Abstract

PURPOSE: An excel-based management system for updating database tables is provided to update a database tables using data on excel sheet field. CONSTITUTION: An inquiry command button is inputted. A DB table inquiry unit(33) moves a selected records on DB table to the DB. The database table inquiry part records the data on a field to an excel sheet(60). An update command button is inputted. A DB table update unit(34) updates the selected DB table records with the field value recorded on the excel sheet.

Description

Excel-based management system for updating DB tables and the method

The present invention relates to an Excel-based DB table update system and method for performing a record update operation of a DB table of a database in Excel.

In particular, the present invention relates to an Excel-based DB table update system and method for recording field values of a record of a DB table of a database in a cell of an Excel sheet and updating a database with field values recorded in a cell of an Excel sheet. will be.

In addition, the present invention takes a DB table of the database and sets the field name to the name of the title cell, and writes or updates a query in the cell corresponding to each row of the DB table in one row, but the field name of the cell field value A system and method for updating an Excel-based DB table arranged in the same column as a cell name.

In general, database add, delete, update, etc. are performed by DB query. For example, a typical relational database management system (RDBMS) updates a database using a structured query language (SQL) statement.

However, DB query such as SQL has a complicated language structure and requires expert knowledge of database concept and structure to write DB query. Therefore, it is very difficult for a general user to directly create a DB query to add, delete, or update a database.

To overcome this, graphical query writing tools have been developed to make DB queries more intuitive. Tables can be displayed graphically, relationships between tables can be easily set by dragging and dropping a mouse, or tables can be displayed as tables to input the conditions of a query.

However, using such a graphical interface method also requires a basic concept of a database.

In contrast, spreadsheets such as Excel are software that processes data and can be handled more easily than databases. Excel is widely used as office software. In particular, Excel treats data as tables, providing functions that can be handled more intuitively. Therefore, many data are often managed and processed in spreadsheets such as Excel.

Due to the convenience of Excel as described above, a number of technologies for converting data in a database into Excel, or converting and storing data in Excel into a database have been proposed and commercialized. In other words, the data in the database is converted into Excel, the work is performed in Excel, and the result is converted back into the database.

One example of a technique for converting a spreadsheet such as Excel into a database is described in [Republic of Korea Patent Publication No. 2007-0080564 (August 10, 2007), "System, Method and Media for Processing Spreadsheet for Storage on Database". ] (Hereinafter referred to as prior art 1). Prior art 1 relates to a method of processing a record of a spreadsheet by receiving a processing request for a spreadsheet comprising a plurality of records for storage in a database, the method comprising: accessing a configuration file associated with the spreadsheet; Creating a new business object for each record in the spreadsheet; Populating the new business object based on the configuration file associated with the record and the spreadsheet. In particular, the configuration file includes the name of a business object associated with a record in the spreadsheet, metadata for one or more columns of the spreadsheet, and the name of a back-end business component associated with the spreadsheet.

In addition, an example of a technique for automatically storing the data conversion information in the database [Republic of Korea Patent Registration No. 10-0597589 (2005.06.29. Publication), "How to automatically save the data conversion information in the database"] (hereinafter referred to as prior art 2) is disclosed. The prior art 2 receives the conversion information (for example, frame information, format information, telemetry data parameter information, conversion formula information, data type, and unit information, etc.) for the telemetry data in the form of a table and the telemetry After creating a Comma-Separated Values File (CSV File) for data conversion information, comma is used as a separator to read the data of the created CSV file and stored in the database. have.

That is, all of the prior art are techniques for converting a spreadsheet or a comma separated extension (CSV) file such as Excel into a database. In particular, the prior art 1 is a technique for inputting the values recorded in the spreadsheet into a database using a configuration file made in XML format.

All of the above prior arts are very useful for repeated database updates. In other words, if the configuration file is created only once, the data recorded in the spreadsheet can be easily updated in the database.

However, in the case where it is not repetitive, for example, in the case of performing the necessary work for each person in charge, it is difficult to apply the above techniques because a burden must be newly created for each configuration file.

An object of the present invention is to solve the problems described above, to provide an Excel-based DB table update system and method for performing a record update operation of the DB table of the database in Excel.

Another object of the present invention is to provide an Excel-based DB table update system and method for recording field values of a record of a DB table of a database in a cell of an Excel sheet and updating a database with field values recorded in a cell of an Excel sheet. To provide.

Another object of the present invention is to take a DB table of the database and set the field name to the name of the title cell, and write or update a query in the cell by mapping each record of the DB table to one row, but not including the field of the cell field value. To provide an Excel-based DB table update system and method in which names are arranged in the same column as the name of the title cell.

In order to achieve the above object, the present invention relates to an Excel-based DB table update system for performing a record update operation of a DB table of a database in Excel, the DB table selecting means for receiving a selection of the DB table of the database; An Excel sheet generator for generating an Excel sheet and inserting a command button into the Excel sheet; A DB table inquiry unit which, when an inquiry command button is input, takes a record of a selected DB table from the database and records a field value of the record in a cell of an Excel sheet; And a DB table updating unit for updating a record of the selected DB table with a field value of a record recorded in a cell of the Excel sheet when the update command button is input, wherein the DB table inquiry unit and the DB table update unit are dynamically connected. The DB table updating unit connects the dynamic link function to the command button.

In addition, the present invention provides an Excel-based DB table update system, wherein the Excel spreadsheet generation unit connects the field name of the selected DB table to the cells (hereinafter title column) of one row (hereinafter title column) of the Excel sheet, The DB table query unit records the field value of the record in rows of cells in an Excel sheet (hereinafter, referred to as a record cell), wherein a field name of a field value recorded in the record cell matches a field name of a title cell corresponding to a column of the record cell. It is characterized by.

The present invention provides an Excel-based DB table updating system, wherein the Excel sheet generating unit sets one cell of the title row as an update type title cell, and the DB table updating unit records cell corresponding to a column of the update type title cell. The update method may be different depending on the update type of the following (update type cell).

In addition, the present invention is an Excel-based DB table update system, wherein the Excel sheet generating unit generates the Excel sheet by importing an Excel template template that has a cell setting related to a command button and an update type title cell.

In another aspect of the present invention, in the Excel-based DB table update system, the Excel sheet generation unit sets the name of the title cell to a field name, thereby connecting the title cell to the field name.

In addition, the present invention provides an Excel-based DB table update system, wherein the DB table selection means shows the meta information of the DB table including the DB table name and the field name of the DB table, one selected DB table and the selected The field names selected from the DB table are input, and the Excel generator generates only the selected field names in the title cell.

In addition, the present invention is an Excel-based DB table update system, wherein the Excel spreadsheet generation unit changes the cell display method of the title cell according to the field characteristics of the field name, the field characteristics are key field, null prohibition field, general It is characterized by including a field.

The present invention also provides an Excel-based DB table update system, wherein the DB table updater creates a DB query that updates the DB table by grouping the record cells in rows, and requests the DB query from the database. .

In addition, the present invention is an Excel-based DB table update system, the DB table update unit, if receiving an error message for the DB query from the database, and displays a different color of the row (hereinafter referred to as error row) corresponding to the row unit Or write the error message to one cell of the error row.

In addition, the present invention is an Excel-based DB table update system, wherein the DB table query unit and the DB table update unit is characterized in that implemented by a DLL (Dynamic Link Library) function.

The present invention also relates to an Excel-based DB table update method for performing a record update operation of a DB table of a database in Excel, the method comprising: (a) receiving a selection of a DB table of the database; (b) generating an Excel sheet and inserting a command button into the Excel sheet; (c) when an inquiry command button is input, taking a record of a selected DB table from the database and recording a field value of the record in a cell of an Excel sheet; And (d) updating the record of the selected DB table with the field value of the record recorded in the cell of the Excel sheet when the update command button is input, wherein the steps (c) and (d) The function is implemented as a dynamic linking function, and step (b) is characterized in that connecting the dynamic linking function to the command button.

In addition, in the method of updating an Excel-based DB table, the step (b) links the field names of the selected DB table to cells (hereinafter, title cells) of one row (hereinafter, title row) of the Excel sheet. In the step (c), the field value of the record is recorded in a cell of an Excel sheet (hereinafter, referred to as a record cell) in a row unit, and the field name of the field value recorded in the record cell corresponds to the column of the record cell. It is characterized by matching the name.

In the Excel-based DB table updating method, in step (b), one cell of the title row is set as an update type title cell, and in step (d), a column of the update type title cell is set. The update method may be different depending on the update type of the corresponding record cell (hereinafter referred to as update type cell).

In the Excel-based DB table update method, in the step (b), the title cell is set as a field name, and the title cell and the field name are connected.

The present invention also provides a method for updating an Excel-based DB table, wherein, in the step (d), a DB query for updating a DB table by grouping the record cells in rows is made, and the DB query is requested to the database. It is done.

The present invention also relates to a computer-readable recording medium having recorded thereon a program for executing the Excel-based DB table updating method.

As described above, according to the Excel-based DB table updating system and method according to the present invention, by updating the DB table of the database in an Excel display method familiar to office workers, it is possible to easily process or process the data stored in the database, and finally The effect of simply updating the DB table is obtained.

In addition, according to the Excel-based DB table update system and method according to the present invention, by setting the field name to the name of the title cell and the field name of the record cell to the name of the title cell, even if various processing is performed in Excel, The effect of accurately transferring the values recorded in the cells of the sheet to the database can be obtained.

1 is a diagram showing the configuration of an entire system for implementing an Excel-based DB table update system and method according to the present invention.
2 is a block diagram of the configuration of an Excel-based DB table update system according to an embodiment of the present invention.
3 is an exemplary diagram for accessing a database according to an embodiment of the present invention.
4 is an exemplary view of a screen for selecting a DB table according to an embodiment of the present invention.
5 is an exemplary view of an Excel sheet according to an embodiment of the present invention.
6 is a screen showing an example of a screen showing meta information of a DB table according to an embodiment of the present invention.
7 is an example of a screen for editing an Excel sheet according to an embodiment of the present invention.
8 is an exemplary diagram of SQL written to update a DB table with the Excel sheet of FIG. 7 according to an embodiment of the present invention.
9 is an exemplary view of an error message screen according to an embodiment of the present invention.
10 is a flowchart illustrating a method of updating an Excel-based DB table according to an embodiment of the present invention.
Explanation of symbols on the main parts of the drawings
10: computer terminal 11: the user
20: network
30: DB table update system 31: DB table selection means
32: Excel sheet generation unit 33: DB table search unit
34: DB table update unit
40: DB server 50: Database
60: Excel sheet 61: command button
62: title row 63: title cell
64: Update Type Title Cell 65: Record Row
66: record cell 67: update type cell

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, the present invention will be described in detail with reference to the drawings.

In addition, in describing this invention, the same code | symbol is attached | subjected and the repeated description is abbreviate | omitted.

First, the configuration of the entire system for implementing the Excel-based DB table update system and method according to the present invention will be described with reference to FIG.

As shown in Fig. 1, the entire system for implementing the present invention is composed of a computer terminal 10, a DB table update system 30, a DB server 40 and a database 50. The apparatus further includes a network 20 for connecting the computer terminal 10 and the DB server 40 (or a database).

The user terminal 10 is a conventional computing terminal used by the user 11. Examples of the user terminal 10 include a PC, a PDA, a mobile, and the like, and a detailed description thereof will be omitted since the configuration and operation principle thereof are well-known techniques commonly used in the art. The DB table update system 30 is installed in the user terminal 10 and executed by the user 11 to perform an update operation of the DB table. In particular, the user terminal 10 is a terminal provided with spreadsheet software such as Excel.

The DB server 40 is a conventional DBMS (Database Management System) that performs and manages DB operations such as access, update, addition, and deletion of the database 50. The DB server 40 provides an interface through which the user terminal 10 can access the database 50 and perform a DB operation. In addition, the DB server 40 forms a DB processing transaction at the request of the user terminal 10, processes the DB job according to the request, and returns the result to the user terminal 10. Meanwhile, the DB server 40 also performs a task of authenticating whether the user 11 using the user terminal 10 is a legitimate user.

The database 50 is a DB in which various kinds of data are stored, and is managed by the DB server 40. DB server 40 is composed of DB tables, DB tables are composed of records that are a collection of data. One record can be thought of as having data for one entity. Records consist of field values, and DB tables manage meta information about tables and fields. The meta information of a field has a field name and a field attribute, and the field attribute has a key field, a data type, a consistency characteristic (for example, null null fields are not allowed). Features of the database as described above are well-known techniques, and thus detailed description thereof will be omitted.

The DB table update system 30 (or update system) is a software tool installed in the user terminal 10 to update the DB tables. The update system 30 accesses the DB server 40 via the network 20 to query the database 50. The user 11 then uses the update system 30 to select a DB table to be updated. The update system 30 inquires the selected DB table in the database 50 and records the inquired data in an Excel sheet through Excel installed in the user terminal 10.

The user 11 can change the contents of the DB table recorded in the Excel sheet, or perform operations such as inserting and deleting on Excel. When the user 11 completes the change of the DB table on the Excel sheet and requests a database update, the update system 30 makes a DB query for updating the DB table with the contents of the changed Excel sheet and makes a request to the DB server 40. . The DB server 40 updates the DB table by performing the requested DB query.

Next, the configuration of the Excel-based DB table update system according to an embodiment of the present invention will be described with reference to FIG.

As shown in FIG. 2, the update system 30 includes a DB table selecting means 31, an Excel sheet generating unit 32, a DB table querying unit 33, and a DB table updating unit 34. .

The DB table selecting means 31 connects to the database 50, retrieves and displays meta information of the DB table, and receives a selection of the DB table. As seen above, the database 50 is managed by the DB server 40, but the description of the DB server 40 will be omitted below, and only the operation for the database 50 will be described. This is for convenience of explanation.

First, the database 50 performs user authentication with respect to a connection request of the DB table selecting means 31. User authentication is the act of checking whether you have the proper authority to access a database.

As shown in FIG. 3, the DB table selecting means 31 outputs a user authentication popup window on the screen, and receives a user name and password from the user 11. The DB table selecting means 31 transmits the input user authentication information to the database 50 to request user authentication.

If authentication is successful, the DB table selecting means 31 inquires the list of DB tables and meta information from the database 50. As shown in Fig. 4, the DB table selecting means 31 shows the inquired DB table on the screen. 4 shows a list of DB tables on the left side and field meta information of the DB table selected on the left side on the right side.

The field meta information on the right side consists of a list of fields and meta information for each field. In addition, the DB table selecting means 31 provides a selection control so that the user 11 can select each field. For example, as shown in FIG. 4, the DB table selecting means 31 provides a check box on the screen in front of each field, and recognizes that the check box is selected when it is checked and not checked when it is released.

For example, in FIG. 4, the table selected on the left side is a "performance analysis table" table, and on the right side, fields such as "reference date", "division", "agent name", etc. of the "performance analysis table" table are selected. It is becoming.

When the user 11 finally confirms, one DB table selected in the left screen and the fields in the right screen are selected. That is, the DB table selecting means 31 shows the meta information of the DB table including the DB table name and the field name of the DB table, and receives one selected DB table and field names selected from the selected DB table.

The Excel sheet generating unit 32 generates an Excel sheet 60 in which a command button is inserted, and displays a field name of the selected DB table in one row (hereinafter, title column) in the Excel sheet 60 (hereinafter, title). Cells).

As shown in FIG. 5, the Excel sheet generator 32 generates an Excel sheet 60 corresponding to the selected DB table and displays it on the screen. The Excel sheet generator 32 executes Excel if it is not executed, generates a new Excel document in Excel, and generates one Excel sheet in the Excel document.

The Excel sheet generating unit 32 inserts the command button 61 into the Excel sheet as shown in FIG. 5. The command button 61 includes an inquiry button, a command button, a close button, and the like. In addition, a button for searching meta information of a DB table may be included.

The Excel sheet generator 32 connects the dynamic link function to the command button. As will be described below, the DB table inquiry section 33 and the DB table update section 34 are implemented with a dynamic link function. The Excel sheet generating unit 32 connects the dynamic link function to the command button, so that when the command button is pressed, the DB table inquiry unit 33 and the DB table update unit 34 are performed.

In addition, the Excel sheet generating unit 32 links the field names of the DB table selected in the Excel sheet 60 to cells (hereinafter, title cells) in one row (hereinafter, title row). The title row 62 is defined as any row in the Excel sheet 60, and each of the cells in the title row 62 is designated as the title cell 63. The title cell 63 is linked to the field of the selected DB table. In particular, only the fields previously selected by the DB table selecting means 31 are linked to the title cell 63.

For example, since the selected DB table is a "Performance Analysis Statement" table, what is displayed in the Excel sheet 60 is for the "Performance Analysis Statement" table, and in the title row 62, each of the "Performance Analysis Statement". The fields of the table, "reference date", "division", "agent name", and the like are recorded. The cells in which these field names are recorded are the title cells 63.

Preferably, the Excel generating unit 32 sets the name of the title cell 63 as a field name, thereby connecting the title cell 63 and the field name. By setting the name (cell name) of the title cell 63 as the field name, it is possible to confirm which field the title cell 63 is associated with even if the cell value of the title cell 63 is changed.

In addition, the Excel sheet generating unit 32 sets one cell of the title row 62 as the update type title cell 64. Update type indicates the type of work that updates records in the DB table. Update types include Update, Delete, and Insert. Change means updating the contents of a record, deleting means deleting the corresponding record in the DB table, and inserting means inserting the corresponding record in the DB table.

The Excel sheet generator 32 sets only one of a change, a deletion, and an insertion into a cell of a column corresponding to the update type title cell 64. That is, the cell format of the column is set to " validation check ", and the cell value that can fit in the cell is set to three types of change, deletion, and insertion. If the update type is further included in addition to the above three, the limit value of the validation check is set for each type of update type.

In addition, the Excel sheet generation unit 32 changes the cell display method of the title cell 63 according to the field characteristics. The field properties include key fields, null inhibit fields, and general fields. Field characteristics are determined by the field attributes of each field of the DB table. For example, whether it is a key field, whether the field must have a field value, or whether it is a general field. The cell display method of the title cell 62 is changed according to the attribute of the field as described above. The display method is a display format such as a text color or a background color, and a font size.

Preferably, the Excel sheet generating unit 32 generates the Excel sheet 60 by importing the Excel template with the cell settings related to the command button 61 and the update type title cell 64. That is, an Excel sheet template having cell settings for the command button 61 and the update type title cell 64 has already been created and stored, and the Excel sheet generating unit 32 loads the Excel sheet template and opens the Excel sheet. Make. By using the template as described above, it is possible to generate the Excel sheet 60 in a short time since it is not necessary to work such as generating a command button.

On the other hand, as shown in Figure 6, when the button for querying the meta information of the DB table into the Excel sheet 60, the meta information of the DB table is shown through a pop-up window. FIG. 6A is an example of a popup window showing access information and meta information of a selected DB table, and FIG. 6B is an example of a popup window showing meta information about a field of a selected DB table.

When the inquiry command button 61 is input, the DB table inquiry unit 33 fetches a record of the selected DB table from the database 50, and stores the field values of the record in units of rows (hereinafter, referred to as record cells). The field name of the field value recorded in the record cell matches the field name of the title cell corresponding to the column of the record cell.

The DB table inquiry unit 33 records the field values in the rows so that one row corresponds to one record. At this time, as shown in Fig. 7, the row to be recorded is called a record row 65, and the cell on which a field value is recorded will be referred to as a record cell 66.

The field of the field value recorded in the record cell 66 is the same as the field linked to the title cell corresponding to the column of the record cell 66. That is, the field of "new product 1" which is the field value of the ninth record of FIG. 7 corresponds to "product name" which is a field connected to the title cell 63 of this column. That is, the field name of the field value "new product 1" matches the "product name" which is the name of the title cell 63.

On the other hand, each record row 65 has an update type cell 67 corresponding to an update type title cell 64. Since the update type cell 67 belongs to a column to which the title cell 64 belongs, the update type cell 67 has a cell display format in which any one of change, deletion, and insertion can be input.

The user 11 sets the update type of the corresponding record by setting the cell value of the update type cell 67 to one of change, deletion, and insertion on the Excel sheet. That is, if the cell value of the update type cell 67 is selected to be deleted, the record row 65 including the update type cell is deleted from the DB table.

When the user 11 wants to (insert) change a specific record, the corresponding field value of the field to be inserted is changed in the record row 65 of the record. The cell value of the update type cell 76 of the record row 65 is selected as " Insert. &Quot; For example, in Fig. 7, the " product name " of the ninth record is " new product 1 ", and the update type cell 67 is designated as " Insert. &Quot;

When the update command button 61 is input, the DB table updater 34 updates the record of the selected DB table with the field value of the record recorded in the cell of the Excel sheet 60. The update command button is one of the command buttons 61 and is a button for commanding record update. When the update command button 61 is clicked by the user 11, a function connected to the button is executed.

The DB table inquiry unit 33 and the DB table update unit 34 are implemented as a dynamic link function, for example, a DLL (Dynamic Link Library) function, and a command button 61 inserted into the Excel sheet 60. ) Is connected to the dynamic link function. Therefore, when an event occurs, such as the command button 61 being clicked, the dynamic link function connected to the event of the command button 61 is executed.

On the other hand, the DB table updater 34 records the DB table record with the field values recorded in the record cell 66 of the Excel sheet 60 according to the cell value (change, insert, delete) of the update type cell 67. Update (change, insert, delete). In addition, the DB table updater 34 creates a DB query for updating the DB table by grouping the record cells 66 in rows, and requests the DB query from the database 50.

The DB table updating unit 34 creates a condition for updating each record row 65 in the form of &quot; <field name> = &lt; field value> &quot; for each record cell 66 of the record row 65. FIG. At this time, the <field name> is determined by the name of the title cell 63 corresponding to the column of the record cell 66, and the <field value> is determined by the cell value of the record cell 66.

For example, as shown in FIG. 8, the first, second, and ninth records of FIG. 7 are written in SQL statements for updating.

When the DB table updater 34 receives an error message for the DB query from the database 50, the DB table updater 34 displays a different color of a row corresponding to the row unit (hereinafter, referred to as an error row) or displays the error message as the error row. In one cell.

The DB table updater 34 does not specifically check the consistency of the DB query. However, the DB table updater 34 transmits the DB query to the database 50, and if the DB query is incorrect, outputs and shows an error message received from the database 50 or displays the color of the corresponding row differently. Indicates that has occurred. Alternatively, the cell of the error message is specified and the content of the error message is recorded. 9 is an example of an error message.

Next, an Excel-based DB table update method according to an embodiment of the present invention will be described with reference to FIG. 8.

As shown in FIG. 8, the method for updating an Excel-based DB table may include: (a) accessing a database 50 to obtain and display meta information of a DB table, and receiving a selection of a DB table (S10); (b) Create an Excel sheet 60 in which the command button 61 is inserted, and insert a field name of the selected DB table into one row (hereinafter, title row and 62) in the Excel sheet 60 (hereafter, title). Connecting to cells 63 (S20); (c) When an inquiry command button is input, the record of the selected DB table is taken from the database, and the field value of the record is recorded in the cell of the Excel sheet (hereinafter, record cell) 66 in units of rows, and the record cell ( Step S30, in which the field name of the field value recorded in 66) matches the field name of the title cell 63 corresponding to the column of the record cell 66; And (d) updating the record of the selected DB table with the field value of the record recorded in the cell of the Excel sheet 60 when the update command button is input (S40).

In particular, in the step (b), one cell of the title row 62 is set as the update type title cell 64, and the name of the title cell 63 is set as the field name to match the title cell 63. Connect the fields.

In addition, in the step (d), the update method is changed according to the update type of the record cell (hereinafter, the update type cell 64) corresponding to the column of the update type title cell 64. In addition, a DB query for updating the DB table by grouping the record cells 66 in rows is made, and the DB query is requested to the database 50. In addition, when an error message for the DB query is received from the database 50, the color of a row corresponding to the row unit (hereinafter referred to as an error row) is displayed differently, or the error message is recorded in one cell of the error row. do.

For the omitted parts of the description of the Excel-based DB table update method, refer to the description of the Excel-based DB table update system described above.

As mentioned above, although the invention made by this inventor was demonstrated concretely according to the said Example, this invention is not limited to the said Example, Of course, a various change is possible in the range which does not deviate from the summary.

The present invention is applicable to the development of an Excel-based DB table update system that performs a record update operation of a DB table of a database in Excel. In particular, the present invention is applicable to the development of an Excel-based DB table update system for recording field values of a record of a DB table of a database in a cell of an Excel sheet and updating a database with field values recorded in a cell of an Excel sheet. It is possible.

Claims (16)

In Excel-based DB table update system that performs a record update operation of the DB table of the database in Excel,
DB table selecting means for receiving a selection of the DB table of the database;
An Excel sheet generator for generating an Excel sheet and inserting a command button into the Excel sheet;
A DB table inquiry unit which, when an inquiry command button is input, takes a record of a selected DB table from the database and records a field value of the record in a cell of an Excel sheet; And
When the update command button is input, includes a DB table updating unit for updating the record of the selected DB table with the field value of the record recorded in the cell of the Excel sheet,
The DB table query unit and the DB table update unit are implemented as a dynamic link function, the DB table generator connects the dynamic link function to the command button,
The Excel spreadsheet generation unit connects the field names of the selected DB table to cells (hereinafter title cells) of one row (hereinafter title row) of the Excel sheet,
The DB table query unit records the field value of the record in rows of cells in an Excel sheet (hereinafter, referred to as a record cell), wherein a field name of a field value recorded in the record cell matches a field name of a title cell corresponding to a column of the record cell. Excel-based DB table update system, characterized in that.
delete The method of claim 1,
The Excel sheet generator sets one cell of the title row as an update type title cell,
And the DB table updating unit changes an update method according to an update type of a record cell (hereinafter, referred to as update type cell) corresponding to a column of the update type title cell.
The method of claim 3,
Wherein the Excel sheet generating unit Excel-based DB table update system, characterized in that for generating an Excel sheet by importing an Excel template template has been set for the command button and the update type title cell.
The method of claim 1,
The Excel sheet generating unit sets the name of the title cell to the field name and connects the title cell to the field name.
The method of claim 1,
The DB table selecting means shows meta information of a DB table including a DB table name and a field name of the DB table, receives one selected DB table and field names selected from the selected DB table,
The Excel sheet generation unit Excel-based DB table update system, characterized in that for connecting only the selected field name to the title cell.
The method of claim 1,
The Excel sheet generating unit changes the cell display method of the title cell according to the field characteristics of the field name, and the field characteristics include a key field, a null prohibition field, and a general field. .
The method of claim 1,
The DB table updating unit creates a DB query that updates the DB table by binding the record cells in rows, and requests the DB query from the database.
The method of claim 8,
When the DB table updater receives an error message for the DB query from the database, the DB table updater displays a different color of a row corresponding to the row unit (hereinafter, referred to as an error row) or displays the error message in one cell of the error row. Excel-based DB table update system, characterized in that for recording.
The method of claim 1,
The DB table querying unit and the DB table updating unit are implemented as a DLL (Dynamic Link Library) function.
In the DB table update method of the Excel-based DB table update system for performing a record update operation of the DB table of the database in Excel,
(a) receiving a selection of a DB table of the database;
(b) generating an Excel sheet and inserting a command button into the Excel sheet;
(c) when an inquiry command button is input, taking a record of a selected DB table from the database and recording a field value of the record in a cell of an Excel sheet; And
(d) if an update command button is input, updating a record of the selected DB table with a field value of a record recorded in a cell of the Excel sheet,
The functions of steps (c) and (d) are implemented as a dynamic linking function, and in step (b), the dynamic linking function is connected to the command button,
In step (b), the field names of the selected DB table are linked to the cells (hereinafter, title cells) of one row (hereinafter, title row) of the Excel sheet.
In the step (c), the field value of the record is recorded in a cell of an Excel sheet (hereinafter, referred to as a record cell) in a row unit, and the field name of the field value of the field value recorded in the record cell corresponds to the column of the record cell. Excel-based DB table update method characterized in that the matching.
delete The method of claim 11,
In step (b), one cell of the title row is set as an update type title cell, and in step (d), an update type of a record cell (hereinafter referred to as update type cell) corresponding to a column of the update type title cell is set. Excel-based DB table update method, characterized in that the update method according to.
The method of claim 11,
In the step (b), Excel-based DB table update method, characterized in that by setting the name of the title cell to the field name to connect the title cell and the field name.
The method of claim 11,
In step (d), create a DB query for updating a DB table by binding the record cells in rows, and requesting the DB query from the database.
A computer-readable recording medium having recorded thereon a program for executing the method of updating an Excel-based DB table according to any one of claims 11 and 13.

Images