KR20180004632A - Method and computer program for integrating spread sheet with database - Google Patents

Abstract

Disclosed are a method for integrating a spread sheet into a database (DB), and a computer program and a system thereof. The method according to the present invention comprises the steps of: providing a user interface screen including a sheet area for displaying content of a spread sheet, an integrated control area for displaying control information required to integrate the spread sheet into a DB, and a DB area for displaying table information on the DB; extracting at least one sheet column to be mapped to the DB from an area selected in the sheet area; displaying information on the extracted sheet column in the integrated control area; requesting a server to generate a table including a column corresponding to the extracted sheet column in the DB; and displaying information on the generated table in the DB area.

Description

Method and computer program for integrating a spreadsheet into a database

The present invention relates to a method of integrating the contents of a spreadsheet into a database and to a computer program and spreadsheet integration system.

Experts with database and query language skills are needed to carry out the work of creating and collecting data from a spreadsheet, such as Excel, into a database.

There are many products that implement a system that makes it easier to build a database with existing Excel files, but it is still difficult for general users to use.

SUMMARY OF THE INVENTION The present invention is directed to providing a method and computer program and system for providing a convenient and intuitive user interface for incorporating the contents of a spreadsheet into a database.

A method for integrating spreadsheets into a database according to an embodiment of the present invention includes a sheet area displaying contents of a spreadsheet, an integrated control area for displaying control information necessary for integrating a spreadsheet into a database, Providing a user interface screen including a DB area for displaying the user interface screen; Extracting at least one sheet column to be mapped from a selected region in the sheet region to a database; Displaying information on the extracted sheet column in the integrated control area; Requesting a server to create a table in the database that includes a column corresponding to the extracted sheet column; And displaying information on the generated table in the DB area.

In the step of extracting the sheet column to be mapped, the selected region may be analyzed to determine the region where the merged column last exists as the sheet column region to extract the sheet columns to be mapped.

A step of determining whether the column mode is a row-based mode or a column-based mode, extracting sheet columns to be mapped based on a row or a column based on the determined column mode, The column area can be determined.

In the step of extracting the sheet column to be mapped, an area according to a user's input may be determined as a sheet column area to extract the sheet columns to be mapped.

The method includes determining a data type by referring to data of a spreadsheet corresponding to the extracted sheet column before requesting generation of the table, and displaying column summary information including the determined data type on the DB area Step;

A method for integrating a spreadsheet into a database according to another embodiment of the present invention includes a sheet area displaying the contents of the spreadsheet, an integrated control area for displaying control information required to integrate the spreadsheet into the database, Providing a user interface screen including a DB area for displaying the user interface screen; Extracting at least one sheet column to be mapped from a selected region in the sheet region to a database and displaying the extracted sheet column in the integrated control region; Displaying a column mapping table to display information on a source column and a target column for data movement in the integrated control area; Adding at least one column of the work table displayed in the DB area to a target column of the column mapping table; Adding at least one of the sheet columns to be mapped to a source column of the column mapping table according to a user's input; And requesting the server to add data of the selected area in the sheet area to the work table with reference to the column mapping table.

The method includes converting the column mapping table into a mapping information file and storing the mapping information file; Binding the mapping information file with a template folder; Detecting whether a data file is added to the template folder; And if the data file is added to the template folder, storing the data of the data file in the database with reference to the bound mapping information file.

According to the present invention, a user can easily create and manage a data table by linking and integrating a spreadsheet and a database through an intuitive user interface displayed on a single screen even without expertise in a database or a query language.

According to the present invention, a user can easily perform a work of migrating data to a database in a repetitive manner by a WYSIWYG (What You See Is What You Get) method.

1 illustrates a network configuration of a spreadsheet integration system according to an embodiment of the present invention.
FIG. 2 illustrates a screen of a spreadsheet integrator according to an embodiment of the present invention.
3 is a flowchart illustrating a method of integrating a spreadsheet into a database according to an embodiment of the present invention.
FIG. 4 is a view for explaining a method of extracting a sheet column to be mapped by selecting a part of a spreadsheet according to an embodiment of the present invention.
5 is a view for explaining how a column extracting area is manually set according to an embodiment of the present invention.
FIG. 6 is a view for explaining how a table is automatically generated according to an embodiment of the present invention.
FIG. 7 is a diagram for explaining how a data type of a table column is changed according to an embodiment of the present invention.
8 is a view for explaining a detailed type of a column according to an embodiment of the present invention.
9 is a flowchart illustrating a method of integrating a spreadsheet according to another embodiment of the present invention into a database.
10 is a diagram for explaining how column mapping is set to add data of a spreadsheet to a table.
11 is a view for explaining a template folder according to an embodiment of the present invention.
12 is a diagram for explaining how column mapping is set to modify data of a table using data of a spreadsheet.
13 is a diagram for explaining a method of integrating data of a selectable data type according to an embodiment of the present invention.
14 to 17 are views for explaining a method of integrating data of a inquiry type data type according to an embodiment of the present invention.
18 shows an example of a user interface for using a reserved word as a source column according to an embodiment of the present invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT Hereinafter, the present invention will be described in detail with reference to preferred embodiments of the present invention and the accompanying drawings, wherein like reference numerals refer to like elements.

It is to be understood that when an element is referred to as being "comprising" another element in the description of the invention or in the claims, it is not to be construed as being limited to only that element, And the like.

Also, in the description of the invention or the claims, the components named as "means", "parts", "modules", "blocks" refer to units that process at least one function or operation, Each of which may be implemented by software or hardware, or a combination thereof.

The methods, computer programs and systems for integrating spreadsheets into databases, disclosed below, provide a completely new and advanced user interface as compared to known user interfaces. Various embodiments having one or more sensors for interacting with a user may be considered in combination with or independently of known input tools.

1 illustrates a network configuration of a spreadsheet integration system according to an embodiment of the present invention.

The spreadsheet integration system according to the present invention includes a spreadsheet integration server 10 and one or more user terminals 11 that communicate with each other over a network. The spreadsheet integration server 10 is a computing device that performs functions of a database management system (DBMS) and a spreadsheet integration server for driving a database (DB) 14, and may be implemented in one physical device, It is also possible to implement the database server and the spreadsheet integration server in separate devices.

The user terminal 11 is provided with a spreadsheet integrator, which is a client program that provides a user interface for integrating the spreadsheet into the database 14, and the spreadsheet integration server 10, in cooperation with the spreadsheet integrator, And then integrates the contents of the spreadsheet into the database 14. The spreadsheet integration server 10 calls the DBMS to perform an integration operation according to a user's operation so that creation, modification, deletion, entry, change, deletion, etc. of the table are actually performed on the database 14 .

A spreadsheet integrator that provides a user interface screen to the user terminal 11 may be implemented as a web screen. In this embodiment, the spreadsheet integration server 10 will operate as a web server and communicate with the user terminal 11. [

FIG. 2 illustrates a screen of a spreadsheet integrator according to an embodiment of the present invention, and FIG. 3 is a flowchart illustrating a method of integrating a spreadsheet according to an embodiment of the present invention into a database.

When the spreadsheet integrator is executed, a user interface screen is provided (S32).

The user interface provided by the spreadsheet integrator allows the user to check the contents of the spreadsheet and the contents of the database, extract the desired part from the spreadsheet, and automatically perform such integrated tasks as creating the corresponding table or inputting data A spreadsheet area 21 for displaying the contents of the spreadsheet, a database area 23 for displaying table information of the database, an integrated control area for displaying control information necessary for integrating the spreadsheet into a database 22). These areas are preferably arranged on one screen, but it may be implemented in a plurality of tabs or windows in consideration of the environment of the user terminal according to the embodiment.

Referring to FIG. 2, the spread sheet region 21 is a portion located on the left side of the screen, and the contents of the spreadsheet file are displayed. In the example of FIG. 2, an Excel file is used as a spreadsheet file. However, the present invention is not limited to this, and other types of spreadsheet files may also be supported. In the spreadsheet area 21, a spreadsheet file can be called up, and the spreadsheet file includes one or more tables, each table being composed of a column name and data.

The database area 23 is a part located on the right side of the screen, and it is possible to inquire and edit the column list and data of the table 31 that is created or generated in the database.

The integrated control area 22 is used for selecting an execution mode, selecting a column mode, setting a sheet column extraction area, inputting a new table name, displaying a sheet column to be mapped, column matching for mapping a column of a spreadsheet to a column of a table This is the area where information and menus for spreadsheet integration work are gathered.

The execution mode 24 of the spreadsheet integrator according to an embodiment of the present invention can be selected in the integrated control area 22 and can be executed in a table creation mode, a data addition mode, or a data modification mode.

In table creation mode, to create a table using the contents of a spreadsheet, first extract the desired sheet column from the spreadsheet. Specifically, a sheet column to be mapped to the table of the database is extracted from the selected area in the spreadsheet area 21 and displayed on the screen (S34). The extracted sheet columns are output to the sheet column column 28 to be mapped in the integrated control area 22.

When extracting a sheet column, you can set the column mode (25) based on the row or column. In the row reference mode, a sheet column to be mapped is extracted in an area including a row up to a predetermined row, and in a column reference mode, a sheet column to be mapped in an area including up to a predetermined column is extracted do.

The spreadsheet opened in the spreadsheet area 21 of FIG. 2 extracts the column in the row-based mode because the spreadsheet includes the table in which the column name is written based on the row. In the case of the table in which the column name is written in the left column of the table, It should be set up to extract the column.

As a method of extracting a sheet column, there are an automatic extraction method and a manual extraction method.

According to the automatic extraction method, as the user selects an area on the spreadsheet, the sheet column (s) to be mapped from the set of cells included in the selected area is automatically extracted. The extracted columns are shown in the sheet column column 28 to be mapped in the integrated control area 22. To select a region, the user may move the mouse cursor or perform a drag operation on the spreadsheet.

4 is a view for explaining a state in which a part of a spreadsheet is selected and a sheet column to be mapped is extracted by an automatic extraction method according to an embodiment of the present invention.

As shown in FIG. 4, when the user selects an area from the cells C1 and R2 to the cells C7 and R15, there is a problem in which part the data written is recognized as a column to be mapped to the table. According to an embodiment of the present invention, a region to be merged last in a selected region is recognized as a column region to be mapped.

For example, when it is judged from the top row in the selected area 40 of FIG. 4, the cell in which the "transaction sales", "agent", "cash", "card", "total" And there are no merged cells from the lower row. Therefore, three rows from the R2 to R4 rows are determined as the mapping column extraction area, and the lower row is determined as the general data area.

As a result, the column to be mapped is extracted in an area including three rows from the cells C1 and R2 to the cells C7 and R4. Referring to the merging state of the cell, the sales person in charge, the sales person, Receipts, procedure sales, cash, unpublished, procedure sales, cash, subtotal, procedure sales, card, procedure sales, sum, procedure sales. The extracted sheet columns are displayed in the sheet column column 28 to be mapped in the integrated control area 22 and the sheet columns displayed in the sheet column column 28 to be mapped when the selected area is changed are also dynamically Is changed.

At this time, the names of the extracted columns may be reduced to include only the contents of the last cell as in the example of FIG. In other words, the sales person in charge of the procedure, the sales person in charge of the treatment, the cash, the receipt, the treatment sales, the cash, the unrepaid, the treatment sales, the cash, the subtotal, the treatment sales card, , Unpublished, subtotal, card, sum, and substitute amount are displayed in the sheet column to be mapped.

In a similar manner, when operating in the column reference mode, it is determined to extract a sheet column to be mapped in a portion from a user-selected region to a column, and up to the last column in which the merged cell is located is recognized as a sheet column extraction region .

If there are no merged cells, the columns to be mapped will be extracted from the first row of the selected spreadsheet area in the row reference mode and from the first column of the selected spreadsheet area in the column reference mode.

5 is a view for explaining how a column extracting area is manually set according to an embodiment of the present invention.

According to the manual extraction method, the user can select where to use the column mode menu 25 to set the sheet column extraction area. First, set whether to extract the column based on the row or column based on the table type. In the example of FIG. 4, since the column name is written in the upper part of the table, it is selected to select "row-based" and to recognize the column up to the third row and extract it (26). If "column criteria" is selected, the selection list of FIG. 5 will include row head, first column, second column, third column, and so on.

After extracting the sheet column to be mapped, if the user inputs a desired table name into the new table name column 27 and presses the integrated execution button 29, the spreadsheet integrator displays the state of the new table including the extracted column And displays it in the database area 23 as well.

FIG. 6 is a view for explaining how a table is automatically generated according to an embodiment of the present invention.

Referring to FIG. 6, a new table name 61 entered by the user is added to the table list, and column summary information is displayed in a column list tab for "immediate table".

The display name of the column list is displayed by default in the sheet column name displayed in the spreadsheet and can be changed. The display name is a label that is easy for the user to identify. When the column is created in the database, the system name in the format allowed by the DBMS can be used. Move the mouse over the column label in the column list to see the system name of the corresponding column. For example, the system name of a column whose display name is "contact person" is "col_399".

The spreadsheet integrator analyzes the data portion of the selected area of the spreadsheet as the data type of each table column and shows the data type automatically determined. For example, the data under the column "Person" in the spreadsheet contains text data, so the text type is automatically extracted, and the data in the "receipt" column contains numbers, so the number type is automatically extracted.

FIG. 7 is a view for explaining a method of changing a data type of a table column according to an embodiment of the present invention, and FIG. 8 is a view for explaining a detailed type of a column according to an embodiment of the present invention.

As shown in FIG. 7, the data type may be Text, Number, Selection, Date, Lookup, Formatted, or Compute. Selection is an optional data type in which one of the user-defined options can be selected as shown in FIG. Lookup is a query type.

The detail type is the data type divided by the more detailed type. For example, the detail type of a Number type can be specified as Integer, Decimal, or Currency.

Mandatory indicates whether data must exist in the corresponding column when a record is entered into the table.

The user can check the column information in this column list and change the display name, data type, detail type, and necessity if necessary.

When the user presses the save button 62 to store the current column list, a query sentence for generating a table including the corresponding columns in the database is generated and transmitted to the server, thereby requesting the server to create the table (S36). The server will receive the necessary information from the spreadsheet integrator and will generate a corresponding table and return the results. Information on the generated table is displayed in the database area 23 (S38). After creation of the table, the user can change the setting of the table as desired in the column list tab.

FIG. 9 is a flow chart for explaining a method of integrating spreadsheets into a database according to another embodiment of the present invention, and FIG. 10 is a diagram for explaining how a column is mapped to add data of a spreadsheet to a table .

A user interface screen composed of a spreadsheet area 21, an integrated control area 22, and a database area 23 is provided similarly to the generation of the table (S90).

The user selects data to be moved in the spreadsheet area 21 and thus extracts the sheet columns to be mapped from the selected area in the spreadsheet area 21. [ The method of extracting a sheet column is similar to that of creating a table. The extracted sheet columns are displayed in the sheet column column 28 to be mapped in the integrated control area 22 (S92).

When the execution mode 24 is the "data addition" mode, data migration can be performed. In the "data addition" mode, as shown in FIG. 10, data of a spreadsheet can be added to a previously generated table using the column mapping table 30 of the integrated control area 22. [

In the column mapping table 30, a source 90 of data and a moving target 95 of data are displayed. The source is the sheet column of the spreadsheet region 21 and the target is the column of the database table. When the mode is set or changed in the "data addition" mode, some or all of the columns of the work table displayed in the current database area 23 are displayed on the target 95 of the column mapping table 30 (S94). These columns are the destinations of data movement.

At least one of the sheet columns to be mapped is added to the source column 90 of the column mapping table according to a user's input (S96).

The source designation of the data can be intuitively performed by moving the column of the sheet column column 28 to be mapped to the column mapping table 30 by dragging and dropping. In the example of FIG. 10, it can be seen that the "Person" column and the "Receipt" column have moved to the source of the column mapping table.

After setting both the desired source and target in the column mapping table 30 and pressing the unified execution button 29, the data included in the area selected in the spreadsheet is added to the table of the database. The spreadsheet integrator generates a query sentence (s) for adding the data of the selected region to the work table by referring to the column mapping table 30 and sends the query sentence (s) to the server to request the server to add data (S98).

The addition of data will be performed by mapping the source column to the target column as set in the column mapping table 30. [ That is, the values corresponding to the columns corresponding to the source are stored in the corresponding column of the table as one record.

The data added to the table can be directly checked through the data tab of the database area 23, and the user can modify the data as desired through the same interface.

12 is a diagram for explaining how column mapping is set to modify data of a table using data of a spreadsheet.

If the execution mode 24 is set to "data modification" as shown in FIG. 12, data previously stored in the database can be modified with the spreadsheet data. As in the "data addition" mode, the column mapping table 30 of the integrated control area 22 can be used to set how to map between the columns of the spreadsheet area and the table columns when modifying data in the "data modification" mode.

However, the column mapping table 30 in the "data modification" mode has columns 121 and 122 as well as columns 122 and 123 for designating a source of data and a movement target of data. The key field designation field 121 is for designating a column to be a key for inquiring a record to be modified, and the rest of the columns other than the key field are data fields, which are substantially revised. That is, a key field is used to inquire a record to be modified, and as a result, the same key field data as that of the spreadsheet is modified to be the same as that of the spreadsheet.

The modification target record is inquired using one or more key fields as a reference for data modification and all fields that the user checks as a key field in the column mapping table 30 as a key field combination. That is, one or more key fields designated by the user are part of the filter for querying the data for modification in the database, and entered into the SQL where clause for data modification.

In the example of FIG. 12, the user specifies the "lake" column 124 as the key field and the remaining columns as the data field. When the user selects the integrated execution button 29, a record of the "occupant information" table having the same "lake" value as the "lake" value of the area selected in the spreadsheet is displayed, Request the server to modify the data of the field "division", "name of the tenant", "contact", "contact person" into data recorded in the corresponding record of the spreadsheet. At this time, the spreadsheet integrator will use the screen data to create SQL for data modification and send it to the server. If more than one key field is specified, the same table record will be subject to modification.

According to an embodiment of the present invention, a user interface capable of automatically storing the data addition work using the column mapping table 30 in a language such as XML (eXtensible Markup Language) can be further provided. In this case, when data is added to the spreadsheet at a later time, data movement will be automatically performed when the stored XML is executed.

11 is a view for explaining a template folder according to an embodiment of the present invention.

According to one embodiment of the present invention, a template folder is used to rapidly integrate spreadsheet file data into a database in a large amount.

The mapping information included in the column mapping table 30 defining the column of the spreadsheet as the source 90 and the column of the database table as the target 95 may be converted into a mapping information file and stored. One mapping information file is set to be bound to one template folder, and data files stored in the template folder are automatically stored in the corresponding table as defined in the corresponding mapping information file.

11 shows a specific example of the template management area 110 for managing template folders. Since the template folder has a function of interlocking with the column mapping table 30, it may be convenient to arrange the template management area 110 on the lower right side of the user interface screen of FIG. 2 so as to be located side by side with the column mapping table 30.

When the user inputs the name of a new template folder through the new folder column 101, the spreadsheet integrator creates a template folder. Template folders may be created in the user terminal or spreadsheet integration server according to an embodiment. Referring to Fig. 11, it can be seen that two template folders, "Park Seung-cheol" and "Blue Corner"

When the user sets the mapping information through the column mapping table 30 and then stores the template by pressing the template storage 104, the spreadsheet integrator converts the mapping information of the column mapping table 30 into an XML file, And the template folder 102 currently selected in the template folder. The contents of the generated XML file can be confirmed by pressing the ShowXML button 106 and can be edited by clicking the template editing 107. [

A data file 103 of a predefined standard can be stored in the template folder. A spreadsheet file such as Excel will be stored. As a method for storing the data file in the template folder, there are a method of copying the file, a method of dragging and dropping the file, a method of uploading the file stored in the remote device using the upload button 105, A method of searching for and copying a file by providing a "browse" menu, and a method of transferring a file of a remote device using a file transfer protocol (FTP) can be used.

The spreadsheet integrator checks in real time or periodically whether a data file is added to the template folder. If the addition of the data file is detected, the data of the data file will be stored in the target table according to the mapping information of the XML file bound to the template folder . The data of the stored database can be directly checked through the data tab of the database area 23. [ If you need to add or query data, the spreadsheet integrator will generate the corresponding query statement and send it to the server so that the database work is done.

By creating templates like this, you can automate repetitive and massive synchronization of spreadsheet data with the database.

If the target column of the data integration is the selectable data type, the data of the sheet column to be mapped according to the input of the user in the column mapping table 30 is classified according to the category, and the category in which each category is associated with each option of the target column When requesting addition of data to the server, it is requested to store the corresponding category information in the target column instead of the data of the sheet column to be mapped.

13 is a diagram for explaining a method of integrating data of a selectable data type according to an embodiment of the present invention.

A table column with an optional data type is a column in which one of the user-defined options is selected and stored as data. When defining a column of an optional data type, the data type is designated as Selection and the above options are entered as the detailed type.

Category matching is required to move a particular column of a spreadsheet into a table column of the selectable data type. That is, instead of storing the data of the spreadsheet in the table as it is, the data of the spreadsheet is classified into categories, and information indicating each category is stored in the table. Each category information corresponds to the option specified in the detailed type of the selected column.

The column mapping table 30 includes a source column 133 and a target column 134 under the target column column 132 in addition to the source column column 131 and the target column column 132 in order to integrate the selectable data. Respectively. Further, in the source column column 131, a data item of the source column (a population of data to be category-classified) is displayed in addition to the name of the source column.

In each of the lower source items 133, data of some of the population of data subject to category classification is input, and in each of the lower target items 134, data defined by the detailed type of the table column specified in the target column 132 Option (category information) is displayed. The user can move the desired data item from the source column column 131 to the lower source item 133 so that the source data item (s) to be classified and the category information are matched.

The concrete integration process of the optional data is as follows.

First, when a table column having a selectable data type is added to the target column field 132 of the column mapping table 30, the options specified by the detailed type of the field are stored in the lower target item 134 of the target column field 132 Respectively. If the "selection 2" column of the current work table is a selection type and "User", "Guest", or "Admin7" is specified as the detailed type, the column mapping table 30 will become as shown in FIG.

When a column of the spreadsheet area to be matched with the table column of the selectable data type is brought to the source column column 131, all the data of the column is displayed in the source column column 131. If the same data is duplicated in the corresponding column of the spreadsheet area, it will only be displayed once.

Referring to FIG. 13, the "owner" column is designated as the source column 131 of the spreadsheet area to be matched with the "selection 2" column, It can be seen that all the source data of the column "Person in charge" in the column column 131 are displayed as "Park Min-sik manager", "Hyein", "Hanjoon", "Baekdam", "Daein", "Store".

These source data are classified as specified in the target column column 132. The user only needs to move each data item in the source column field 131 to the source item 133 corresponding to the desired category. FIG. 13 is a diagram showing a state in which the user is classified as " Admin7 "and the store is classified as " Admin7 " .

When the integration execution button 29 is pressed in this state, the data written in the column "Person in charge" in the spreadsheet area is classified according to the definition of the column mapping table 30, changed into corresponding category information, and stored in the table. In other words, if the value of the column "Person in charge" of the spreadsheet is "Hyein", "Minsik manager" or "Baekdam", "User" is stored in the column "Selection 2" "Guest" is stored in the "Select 2" column of the table when the value of the "Person" column of the spreadsheet is "Dyne" or " "Is stored.

As described above, the present invention provides a user interface capable of automatically classifying data in a spreadsheet area upon data integration, changing the category information associated with the classification scheme, and storing the converted category information in a table.

If the target column of data integration is a query type, one of the columns of the referenced table of the target column is matched with the source column of the spreadsheet according to user input in the column mapping table 30, , It is requested to store the key value of the referenced column in the target column instead of the data of the sheet column to be mapped.

14 to 17 are views for explaining a method of integrating data of a inquiry type data type according to an embodiment of the present invention.

A table column having a query type data type is for storing relational data referring to another entity, for example, another table. When defining a column of a query type data type, the data type is designated as a lookup. The name is entered. In the example of Fig. 15, the "charge" column 151 of the integration target table is defined as referring to another table called "contact information"

The column mapping table 30 further includes a target column 144 below the target column column 142 as well as the source column column 141 and the target column column 142 for consolidation of the inquiry type data. In the lower target item 144, the name of the column referred to by the column of the source column column 141 is input. The user has a sheet column column 28 for mapping each column so that the connection of [source column of the spreadsheet area - target column of the table - referenced column referenced by the target column] is defined in the column mapping table 30 It is only necessary to fetch it from the database area 23.

The concrete integration process of inquiry type data is as follows.

When a column of the inquiry type data type is added to the target column column 142 of the column mapping table 30, the column mapping table 30 stores a target column name Item 144 is further displayed.

When the data in the spreadsheet area specified in the source column field 141 is incorporated into the column of the inquiry type data type specified in the target column field 142, the referenced table defined in the column of the inquiry type data type is referred to The referenced column specified in the target item 144 is referenced, and the key value of the record having the same data as the spreadsheet is stored in the target column.

14, the "owner" column is moved from the sheet column column 28 to be mapped to the source column column 141, and the "responsible" column, which is an inquiry type column of the integration target table, is moved to the target column column 142 Quot; name "is input to the target item 144, and the " name "

15, the "charge" column 151, which is an inquiry type column of the integration target table, is defined to refer to the "contact person information" table. Thus, when the user presses the integration execution button 29, "Column is not directly stored in the " responsible" column of the integration target table, but the integration is performed by referring to the "name"

As a result of the consolidation, the " responsible "column of the consolidation target table stores the key value Id of the" representative information "table record with the" name "

FIG. 16 shows data stored in the "contact person information" table, and FIG. 17 illustrates data added to the integration target table as a result of the integration. It can be seen that the key value of the corresponding record in the table of "personnel information" which is the reference table is stored in the " responsible "column 171 of the integration target table. If the data in the column of the "person in charge" column of the spread sheet is the "manager of the manager", the record 163 having Id 161 is compared with the "name" column 162 of the referenced table " Quot; contact person information "and the key value" 257 "of the inquired record 163 will be stored in the target column" charge "

According to an embodiment of the present invention, a reserved word can be designated as a source column of the integration. That is, it is possible to store, in the database, reserved word data indicating the sheet name, the current year, the current month, the current date, the current time, the current day, etc., as well as the data written in each cell of the spreadsheet.

18 shows an example of a user interface for using a reserved word as a source column according to an embodiment of the present invention.

18, in addition to the sheet column column 28 to be mapped, a reserved word column 181 for specifying reserved words as source data may be further included in the integrated control area 22. [ The user can move a reserved word among the reserved words to the source column column 90 of the column mapping table 30 and execute data integration to store the reserved word in the target column of the target column column 95. In the example of FIG. 18, the name of the spreadsheet displayed in the spreadsheet area 21 is set to be stored in the "sheet name" column of the integration target table.

On the other hand, a virtual column processed using a column included in a selected area of a spreadsheet can be also designated as a source column. The virtual column may include a calculation column that uses an existing column by using a calculation result, a format column in which an existing column is inserted in a predetermined format, and a format column. The user edits the desired virtual column in the integrated control area 22 and moves the completed virtual column to the source column column 90 of the column mapping table 30, ≪ / RTI >

In the present specification, the direction of data integration is described as moving data from a spreadsheet to a database, but depending on the embodiment, the source may be a table in the database and the target may be a spreadsheet, or the source may be a table in the database, .

Meanwhile, the method of integrating spreadsheets according to the present invention into a database may be implemented in the form of software, which is a set of instructions readable by a computer, and may be recorded on a recording medium.

In this case, the recording medium may include any type of medium readable by a computer, such as DVD-ROM, CD-ROM, hard disk, USB memory, and flash memory.

On the other hand, the expression to be recorded on the recording medium includes not only the case of being recorded on the recording medium of this type but also the case of being provided through a communication line in the form of an intangible carrier wave (Carrier Wave).

The technical idea of the present invention has been described through several embodiments.

It will be apparent to those skilled in the art that various changes and modifications may be made to the embodiments described above from the description of the present invention. Further, although not explicitly shown or described, those skilled in the art can make various modifications including the technical idea of the present invention from the description of the present invention Which is still within the scope of the present invention. The above-described embodiments described with reference to the accompanying drawings are for the purpose of illustrating the present invention, and the scope of the present invention is not limited to these embodiments.

10: Spreadsheet integration server
11: User terminal
14: Database
21: Spreadsheet area
22: Integrated control area
23: Database area
24: Execution mode
25: Column mode
27: table name
28: What is a sheet column to map?
29: Integrated Execute button
30: Column mapping table

Claims (6)

Computer,
Providing a user interface screen including a sheet area displaying the contents of the spreadsheet, an integrated control area displaying control information required to integrate the spreadsheet into a database, and a DB area displaying table information of the database;
Extracting at least one sheet column to be mapped from a selected region in the sheet region to a database;
Displaying information on the extracted sheet column in the integrated control area;
Requesting a server to create a table in the database that includes a column corresponding to the extracted sheet column;
And displaying information on the generated table in the DB area. ≪ Desc / Clms Page number 20 > Computer,
Providing a user interface screen including a sheet area displaying the contents of the spreadsheet, an integrated control area displaying control information required to integrate the spreadsheet into a database, and a DB area displaying table information of the database;
Extracting at least one sheet column to be mapped from a selected region in the sheet region to a database and displaying the extracted sheet column in the integrated control region;
Displaying a column mapping table to display information on a source column and a target column for data movement in the integrated control area;
Adding at least one column of the work table displayed in the DB area to a target column of the column mapping table;
Adding at least one of the sheet columns to be mapped to a source column of the column mapping table according to a user's input;
And requesting the server to add data of the selected region in the sheet region to the work table with reference to the column mapping table. 3. The method of claim 2,
If the added target column is a selectable data type,
Further comprising classifying data of the sheet column to be mapped according to a user's input in the column mapping table and associating each category with each option of the target column,
Wherein the step of requesting the server to add data requests storing the corresponding category information in the target column instead of the data of the sheet column to be mapped. 3. The method of claim 2,
If the added target column is a query type data type,
Further comprising associating a referenced column of a table referenced by the target column with a sheet column to be mapped according to a user input in the column mapping table,
Wherein the step of requesting the server to add data requests to store the key value of the referenced column in the target column instead of the data of the sheet column to be mapped. 3. The method of claim 2,
In the data modification mode, a table to be modified is inquired in the work table using a key field specified in the column mapping table, and the corrected record is modified with data of an area selected in the sheet area with reference to the column mapping table Further comprising the steps of: < Desc / Clms Page number 13 > 3. The method of claim 2,
Converting the column mapping table into a mapping information file and storing the mapping information file;
Binding the mapping information file with a template folder;
Detecting whether a data file is added to the template folder;
Further comprising: when the data file is added to the template folder, storing the data of the data file in the database with reference to the bound mapping information file.

Images