KR102056709B1 - Method and computer program for extracting table record from spread sheet - Google Patents

Abstract

A method of automatically extracting table records from the contents of a spreadsheet is disclosed. The extracting method according to the present invention comprises the steps of: detecting a rectangular table area comprising a plurality of cells in which data is described in a spreadsheet, but detecting at least one table area present at different positions and not overlapping; Determining a table direction with respect to the detected table area; Searching the table area in the table direction to determine a column area including a column name of the table and a record area including table data, and calculating column levels by analyzing the structure of the column area; Generating mapping information by comparing the structure of the column level and the record area; And extracting column names and records from the table area by using the generated mapping information and generating a table including the extracted column names and records.

Description

How to extract table records from a spreadsheet and a computer program for executing them {Method and computer program for extracting table record from spread sheet}

The present invention relates to a method of automatically extracting a table record from the contents of a spreadsheet and a computer program for executing the method.

In order to move data created and collected by using a spreadsheet such as Excel to a database, it had to be exported to a CSV file and integrated into the database based on the same field.

In particular, it was difficult to directly export a CSV file without any modification when several tables were complicated and irregular in one spreadsheet.

Therefore, there is a need to develop a technology for automatically extracting table records from a spreadsheet without the troublesome process of modifying the data of the spreadsheet or exporting the CSV file and integrating it into a database.

The present invention provides a table record extraction method for automatically recognizing a table area to be integrated by analyzing the contents of a spreadsheet, extracting column names and data records from the recognized table area, and creating a table, and a convenient and intuitive user interface therefor. It is to provide.

In a method of extracting a table record from a spreadsheet according to an embodiment of the present invention, a computer detects a rectangular table area including a plurality of cells in which data is written in the spreadsheet, but exists at different positions and does not overlap each other. Detecting at least one table area; Determining a table direction with respect to the detected table area; Searching the table area in the table direction to determine a column area including a column name of a table and a record area including table data, and calculating a column level by analyzing a structure of the column area; Generating mapping information by comparing the column level with the structure of the record area; And extracting column names and records from the table area using the generated mapping information and generating a table including the extracted column names and records.

The calculating of the column level may include initializing a column level value; Searching the table area in the table direction and increasing the column level while the column area is continued; And storing the current column level value when the column area no longer lasts.

In the generating of the mapping information, when the record area has a structure corresponding to the column level of the column area, mapping information indicating to map one-to-one mapping between the column name extracted from the column area and the record extracted from the record area is provided. Generating; In the case where the column level of the column area is two or more columns and the corresponding record area is a single row structure, instructing to map the records extracted from the record area after generating column names by combining data of the multi-row column. Generating mapping information.

According to the present invention, a table can be generated by automatically extracting a record table by simply selecting a spreadsheet to be merged.

In addition, according to the present invention, since it automatically determines and extracts only the data corresponding to the table data from the complex spreadsheet including various types of data and the table, the integration into the database can be performed conveniently.

1 is a flowchart illustrating a method of extracting a table record from a spreadsheet according to an embodiment of the present invention.
2 is a diagram illustrating a method of extracting a table area according to an embodiment of the present invention.
3 is a view for explaining a method of determining a table direction in a plurality of table areas having different structures and directions according to an embodiment of the present invention.
4 is a flowchart illustrating a method of estimating a column level of a column area according to an embodiment of the present invention.
5 and 6 are diagrams for describing various embodiments of extracting a column level of a column region from a spreadsheet and generating mapping information accordingly.
FIG. 7 is a diagram for explaining a table extracted from the table area of FIG. 6.
8 illustrates an example of a user interface for performing a method of extracting table records from a spreadsheet.

Hereinafter, with reference to the preferred embodiments of the present invention and the accompanying drawings will be described in detail, the same reference numerals in the drawings will be described on the assumption that the same components.

When any one element in the description or claims of the invention "includes" another element, unless otherwise stated, it is not limited to consisting only of that element, and other elements are not interpreted. It should be understood that it may include more.

Further, in the detailed description of the invention or in the claims, the elements designated as "~ means", "~ part", "~ module", and "~ block" mean a unit that processes at least one function or operation, Each of these may be implemented by software or hardware, or a combination thereof.

The method of extracting table records from a spreadsheet according to the present invention may be implemented in the form of a computer program running on a computing device. That is, the following invention may be described in the general context of computer-executable instructions, such as program modules, being executed by a computer. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types.

The invention that follows may operate in a variety of general purpose or special purpose computing system environments. Personal computers, server computers, portable or laptop devices, multiple processors, programmable consumer electronics, network PCs, and the like can be used to implement the present invention, but are not limited to these. The described embodiments can also be practiced in distributed computing environments where tasks are performed by remote processing devices that are linked through a communications network.

1 is a flowchart illustrating a method of extracting table data from a spreadsheet according to an embodiment of the present invention.

In step S10, a rectangular table area including a plurality of cells in which data is described in the spreadsheet is detected.

The table area is a rectangular area surrounded by a line in this spreadsheet, which consists of a column area and a record area. The column region is arranged on the top or left side of the rectangle and is a collection of cell (s) with column names. The record area is located at the bottom or the right of the column area, and is an area in which data of the same format is generally included for each column in a cell connected in one direction. Each table area is a rectangular, non-overlapping area that exists at different locations in the spreadsheet. The table area detection module searches the entire spreadsheet to detect one or more areas that satisfy the above conditions.

 The input spreadsheet may include not only one or more table areas but also various information such as a document title and a graph. In step S10, only the rectangular table area (s) that can be converted into table data is detected from the contents of the spreadsheet, and a user interface for receiving confirmation of the user is provided for the table area automatically detected by the table area detecting means. Can be.

2 is a diagram illustrating a method of extracting a table area according to an embodiment of the present invention.

Referring to FIG. 2, a rectangular area composed of cell (s) in which a data is described in a spreadsheet and surrounded by a line with borders B2: B2 (21), F2: F2 (24), B3: C4 (22), and E3: F4 (25), C5: C5 (23) and E5: E5 (26) can be detected. These rectangular areas exist at different locations and are separated by non-overlapping areas. Of these, 1x1 sized areas 21, 24, 23, and 26, which cannot include column and record areas, will be excluded from the table area.

According to an embodiment of the present invention, the table area detection module may be implemented with the following algorithm to detect a table area by searching a spreadsheet.

First, an example of an algorithm for searching for an upper left cell of a rectangular table area by starting to search an area.

var obj = ws.UsedRange // valid cell area on the spreadsheet to be searched;

for (int I = obj.Row; I <= obj.EndRow; i ++) // loop through the entire sheet row
{
for (int j = obj.Col; j <= obj.EndCol; j ++) // loop through the entire sheet column
{
var cell = ws. Cells [I, j]; // determine the cell to search
if (this.IsLeftTopBorder (cell)) // Check if left top cell
// (judged by the shape of the ruled line)
{
RangeBound bound = null;
try
{
bound = new RangeBound (ws, cell);
this.Add (bound); // register to collection
}
catch (Exception ex)
{
PGlobal.MSG.Error (ex);
}
}
}
}

The following is an example of an algorithm for determining the top right corner of a rectangular table area by continuing to search the area.

void UpdateRT () // Confirm Top Right Vertex
{
if (this.LeftTopR == null) return;

var cell = this.LeftTopR;
Cell found = null;
while (cell! = null)
{
if (cell.NoTopLine ()) break;
if (cell.HasRightLine ())
{
found = cell;
}
cell = cell.GetRight ();
}

this.RightTop = found;
}

The following is an example of an algorithm for estimating the lower right corner of a rectangular table area by continuing to search the area.

void UpdateRBCandidate () // estimate bottom right corner
{
if (this.RightTop == null) return;

var cell = this.RightTop.EndCellR;
Cell found = null;
while (cell! = null)
{
if (cell.NoRightLine ()) break;
if (cell.HasBottomLine ())
{
found = cell;
// whether it collides with a larger area,
var check = cell.GetRight ();
if (check! = null && check.HasBottomLine ())
{
break;
}
}
cell = cell.GetDown ();
}
this.RightBottom = found;
}

The following is an example of an algorithm for estimating the lower left corner of a rectangular table area by continuing to search the area.

void UpdateLBCandidate ()
{
if (this.LeftTopR == null) return;

var cell = this.LeftTopR;
Cell found = null;
while (cell! = null)
{
if (cell.NoLeftLine ()) break;
if (cell.HasBottomLine ())
{
found = cell;
var check = cell.GetLeft ();
if (check! = null && check.HasBottomLine ())
{
break;
}
}
cell = cell.GetDown ();
}
this.LeftBottom = found;
}

The following is an example of an algorithm that terminates the detection of the table area by determining each vertex of the rectangular table area.

void UpdateLBRBByAuto ()
{
if (this.LeftBottom == null this.RightBottom == null) return;

if (this.LeftBottom.EndRow <this.RightBottom.EndRow)
{
this.RightBottom = this.SheetR.Cells [this.LeftBottom.Row, RightBottom.Column];
}
if (this.LeftBottom.EndRow> this.RightBottom.EndRow)
{
this.LeftBottom = this.SheetR.Cells [this.RightBottom.Row, RightBottom.Column];
}

this.UpdateRangePositioin (new RangePosition (LT, RB))
;
this.RangeFindType = RangeFindType.Auto;
}

In step S11, the table direction is determined for the table area detected as described above. In the table area, if the column area is at the top and the record data corresponding to each column is written in the cell (s) below the column name, it is a vertical table. The column data is at the left and the record data corresponding to each column is the column name. If written in the right cell (s), it can be determined as a horizontal table.

3 is a view for explaining a method of determining a table direction in a plurality of table areas having different structures and directions according to an embodiment of the present invention.

Referring to Fig. 3, the table area detecting means makes the first table area 31 having [B4, E4, E10, B10] the rectangular vertices, and the [B12, G12, G15, B15] having the rectangular vertices. The second table area 32 will be detected.

In the case of the first table area 31, since the cells in which column names consisting of character strings are written are arranged at the top of the table, record data of the same type are arranged in a row in a vertical direction between 5 rows and 10 rows. Can be judged. In the case of the second table area 32, the cells in which column names consisting of character strings are written are arranged on the left side of the table, and data of the same type are listed in a row in the horizontal direction between columns C and G. Can be.

In step S12, the table area is searched in the direction of the table determined as described above, the column area and the record area are divided, the structure of the column area is analyzed, and the column level is calculated.

The column area is a label area including names of one or more columns included in the table, and the record area is an area in which record data of each column is written. The column level of the column area means how many rows or columns the column area is composed of and can be calculated by searching the number of rows or columns including the column name.

In the case of a vertical table, the first row of the table area is generally the column area including the column name, and the column level is 1. However, there are some cases where a multi-column column has two or more column names, and the column level is determined by the number of rows including the column name.

In the case of a horizontal table, the first column of the table area is a column area including the column name, and the column level is 1 at this time. However, there may be a case of a multirow column having two or more column names, and the column level is determined by the number of columns including the column name.

In the example of FIG. 3, the column area of the first table area 31 is composed of four cells B4 to E4 and the record area is an area composed of 24 cells of B5 to E10. The column area of the second table area 32 is composed of four cells B12 to B15, and the record area is composed of 20 cells C12 to G15.

Since the column area of the first table area 31 is composed of one row, it is a single row column area having a column level of 1, and the column area of the second table area 32 is composed of one column, so a single row column having a column level of 1. It will be judged as an area.

In step S13, mapping information is generated by comparing the calculated column level with the structure of the record area.

Mapping information is information for mapping the column name of each column and the value of each record. When the record area has a structure corresponding to the column level of the column area, mapping information is generated to instruct one-to-one mapping of the column name extracted from the column area and the record extracted from the record area.

On the other hand, if the column area of the column area is two or more columns and the corresponding record area is a single row structure, the mapping instructs to combine the data of the multi-row column to generate column names and then map the records extracted from the record area. Generate information.

In the example of FIG. 3, since the first table area 31 includes a single row column area having a vertical shape and a column level of 1, and the record area also has a structure corresponding to the column level, the column set (number, age, Information indicating that records (B5 to E5, B6 to E6, B7 to E7, B8 to E8, B9 to E9, and B10 to E10) arranged under the column area corresponding to gender and school) are mapped. Will be generated as mapping information.

The second table area 32 includes a single-row column area having a horizontal type and a column level of 1, and the record area has a structure corresponding to the column level, so that it corresponds to a column set (name, key, weight, etc.). Information indicating that the records (C12 to C15, C12 to C15, D12 to D15, E12 to E15, F12 to F15, and G12 to G15) arranged in a column form on the right side of the column area are respectively generated as mapping information. will be.

In step S14, column names and records are extracted from the table area using the generated mapping information, and a table including the extracted column names and records is generated.

With the spreadsheet of FIG. 3 as input, two tables will be created for the first table area 31 and the second table area 21. For example, the following table may be generated for the second table area 32.

name key weight Other AAA 120 30 null BBB 130 34 null CCC 134 34 null DDD 132 32 null EEE 125 40 null

The following is an example of an algorithm for extracting column names from the table area.

var info = this.DetectInfo; // Temporarily save the searched table direction and column level
var rp = this.RangePositionR;
if (info.TableDirections == TableDirections.Downward)
{
var off = info.ColumnLevel-1;
for (int I = rp.Col; I <= rp.EndCol; i ++)
{
var cell = this.SheetR.Cells [rp.Row + off, i];
try
{
var column = this.GetVericalMergedText (rp.Row, rp.Row + off, i);
this.AddColumn (dt, column, i); // register column name to collection
if (cell.StartCellR.Colspan> 1) i + = cell.StartCellR.Colspan-1;
}
catch (Exception ex)
{
PGlobal.MSG.Error (ex);
}
}
}

The following is an example of an algorithm for extracting records from a record area.

void UpdateRecords (DataTable dt)
{
var info = this.DetectInfo;
var rp = this.RangePositionR;
var off = info.ColumnLevel-1;
if (info.TableDirections == TableDirections.Downward)
{
for (int i = rp.Row + off + 1; i <= rp.EndRow; i ++)
{
this.AddRecord (dt, i, info.TableDirections);
}
}
if (info.TableDirections == TableDirections.RightHanded)
{
for (int i = rp.Col + off + 1; i <= rp.EndCol; i ++)
{
this.AddRecord (dt, i, info.TableDirections);
}
}
}

4 is a flowchart illustrating a method of estimating a column level of a column area according to an embodiment of the present invention.

The column level represents the structure of the column region, and the column level value is 1 when the column region is composed of one row or one column, and the column level value is n when the column region is composed of two or more n rows or n columns.

Referring to FIG. 4, first, the column level value is initialized to 1 in step S40.

If the table direction is vertical in step S41, the flow advances to step S43 to search the table area from top to bottom. If the table direction is horizontal, the flow advances to step S42 to search the table area from left to right.

In step S44, it is determined whether the column area continues in the current search direction. If there are more column areas, the level value is increased by 1 (S45) and the search continues. If the column area does not last longer, the current level value is stored at the column level and ends (S46).

The persistence of the column area may be determined according to whether there is a cell value to be used as the column name in a subsequent adjacent cell or whether a data cell adjacent to the column cell is searched. In the former case, it may be determined that the column region is continued, and in the latter case, the column region may not be maintained.

Also, if the table direction is vertical and the current cell is horizontally integrated, increase the level value and continue to search the column area. If the table direction is horizontal and the current cell is vertically integrated, increase the level value and then open the column area. You can continue to explore.

5 and 6 are diagrams for describing various embodiments of extracting a column level of a column region from a spreadsheet and generating mapping information accordingly.

Referring to FIG. 5, three table areas 51, 52, and 53 are detected in the spreadsheet. Through exploration of each table area, the first table area 51 is a vertical table having a single row column area, the second table area 52 is a horizontal table having a single row column area, and the third table area. Reference numeral 52 is a vertical table having a multi-row column area composed of two rows, and it will be detected that the record area has a single row record that does not match the multi-row column structure.

Since the record area has a structure corresponding to the column level of the column area, the first and second table areas 51 and 52 have mapping information indicating that the column names extracted from the column area and the records extracted from the record area are mapped one-to-one. Will be generated.

Since there are three data cells in the record area corresponding to the column names in the first table area 51, three data can be combined to generate corresponding record data. That is, (null / null / null, null) will be generated as the first record corresponding to the (勞 務 部, 所屬 課 長) columns.

Meanwhile, the third table area 53 is a multi-row column having a column level of 2 in the column area. The corresponding record area has a single row structure. The column area is generated by combining data of the multi-row column to generate a column name. Mapping information will be generated to instruct to map the extracted records.

In detail, the table extracted and generated in the third table area 53 may be as follows. The column name of the multi-row part in the column area may be generated by combining data of the multi-row column, for example, 摘要 / 年 休, 摘要 / 缺勤, 摘要 / 遲 刻, 摘要 / 早退, 摘要 / 出 張. .

日 曜 日 出 社 時刻 退 社 時刻 實 勤務 時間 年 休 要 / 年 休 缺勤 要 / 缺勤 遲 刻 要 / 遲 刻 早退 要 / 早退 摘要 / 出 張 21 月  時 分  時 分  時 分 0 0 0 null null 22 火  時 分  時 分  時 分 0 0 0 null null 23 Water  時 分  時 分  時 分 One null 0 null 0

6 illustrates an example of data included in a spreadsheet, in which the table area detected includes a multi-row record area 62 having a structure that matches the multi-row column area 61 composed of two rows. That is, since the structure of the column area and the record area match, mapping information indicating that the column name extracted from the column area 61 and the record extracted from the record area 62 are mapped one-to-one will be generated.

A table as shown in FIG. 7 may be generated using mapping information obtained as a result of analyzing the table area of FIG. 6.

8 illustrates an example of a user interface for performing a method of extracting table records from a spreadsheet.

Referring to FIG. 8, a user interface is generated by extracting a table record from an area in which a user can select a spreadsheet file to be worked on, an area for showing a selected spreadsheet and spreadsheet search process and results, and a spreadsheet It can contain an area for displaying a table.

In this example, the table area detection module detected three table areas from the spreadsheet selected in FIG. 8, but only one table area 70 was selected as an extraction target of the table record by the user's operation. As shown in the drawing, the table 71 generated as a result of the analysis of the table area 70 includes some column names generated by combining data written in multiple rows of the column area.

Meanwhile, the method of extracting a table record from a spreadsheet according to the present invention may be implemented in the form of software, which is a set of computer readable instructions, and may be recorded on a recording medium.

In this case, the recording medium may include all types of media that can be read by a computer, and examples thereof include DVD-ROM, CD-ROM, hard disk, USB memory, and flash memory.

On the other hand, the expression recorded on the recording medium includes the case of being recorded on this type of recording medium, as well as the case provided through the communication line in the form of an intangible carrier wave (Carrier Wave).

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

It will be apparent to those skilled in the art that various modifications or variations can be made to the embodiments described above from the description of the invention. In addition, even if not explicitly shown or described, those skilled in the art to which the present invention pertains various forms of modification including the technical idea according to the present invention from the description of the present invention. Is obvious and still belongs to the scope of the present invention. The above embodiments described with reference to the accompanying drawings are described for the purpose of illustrating the present invention, and the scope of the present invention is not limited to these embodiments.

Claims (4)

A method of extracting table records from a spreadsheet to integrate data created or collected using a spreadsheet into a database,
Computer,
One or more table areas that detect a rectangular table area that contains a plurality of cells in which the data is described in the spreadsheet, but which exist at different locations and do not overlap-where the table area is a rectangle surrounded by lines within such a spreadsheet. Detecting an area comprising a column area and a record area;
Table direction for the detected table area-Here, in the definition of the table direction, if the column area in the table area is at the top and the record data corresponding to each column is written in the cell under the column name, the table is vertical. A definition, if the column area is on the left side and record data corresponding to each column is written in the cell on the right side of the column name;
Search the table area in the table direction to determine the column area including the column name of the table and the record area including the table data, and analyze the structure of the column area to determine the column level-here, the column level of the vertical table. Is determined by the number of rows including column names, and the column level of the horizontal table is determined by the number of columns including column names;
Generating mapping information by comparing the column level with the structure of the record area, wherein the mapping information is information for mapping the column name of each column and the value of each record;
Extracting column names and records from the table area by using the generated mapping information and generating a table including the extracted column names and records;
When the record area has a structure corresponding to the column level of the column area, the mapping information is generated to instruct to map 1: 1 the column name extracted from the column area and the record extracted from the record area.
When the column level of the column area is a multi-row column of 2 or more, mapping information for generating a column name by combining data of the multi-row column and then mapping the combined column name and a record extracted from the corresponding record area. Creates a,
And integrating the database into a vertical table form or a horizontal table form in correspondence with the mapping information. The method of claim 1,
The step of calculating the column level,
Initializing the column level values;
Searching the table area in the table direction and increasing the column level while the column area is continued;
Storing the current column level value if the column area no longer persists. delete A computer program recorded on a recording medium for carrying out the method of extracting a table record from the spreadsheet of claim 1.

Images