Electronic form conversion

Abstract

A scheme for electronic form conversion is provided. In the scheme, at least one header area and a data area of the electronic form are detected; and determining the hierarchical structure of each header area in the header areas by analyzing the unit merging and / or indentation in the header areas and / or the function relationship between the data items in the corresponding units in the data areas. The electronic form table can be converted into a relationship table based on the identification of the hierarchical structure of the header region. In this way, understanding of the header structure is assisted based on the header hierarchy, and automatic conversion from an electronic form table to a relation table can be achieved.

Description

[0001] This application is a divisional application of the invention patent application with the application date of August 8, 2019, application number 201980099111.3, and invention name “Electronic Form Conversion”. Background Art

[0002] Electronic documents often contain spreadsheets to convey densely packed multidimensional data. Spreadsheets can be edited by employing layout patterns to efficiently indicate data items in two dimensions. Many spreadsheets are designed to be interpreted by humans and have flexible structures. They typically include complex sets of headers, embedded subheaders, and various cell sizes. However, the rich combination of table structure and content presents difficulties in using spreadsheets for complex data analysis, visualization, troubleshooting, and other processing by other tools. In various scenarios, it is desirable to convert spreadsheets into a unified structure for subsequent processing by machines. Summary of the Invention

[0003] According to an implementation of the subject matter described herein, a scheme for converting an electronic form table is provided. In this scheme, one or more header areas and data areas of an electronic form table are detected. By analyzing the merging and / or indentation of cells in the header area, and / or the functional relationship between data items in corresponding cells in the data area, the hierarchical structure of each header area in the header area is determined. Based on the identification of the hierarchical structure of the header area, the electronic form table can be converted into a relational table. In this way, the header structure is understood based on the header hierarchy, and automatic conversion from an electronic form table to a relational table can be achieved.

[0004] This Summary is provided to introduce some concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used to limit the scope of the claimed subject matter. BRIEF DESCRIPTION OF THE DRAWINGS

[0005] Figure 1 A block diagram illustrating a computing device 100 in which various implementations of the present disclosure may be implemented;

[0006] Figure 2 shows a block diagram of a table analysis module according to an implementation of the present disclosure;

[0007] Figure 3 An example of the result of region detection of a spreadsheet table according to an implementation of the present disclosure is shown;

[0008] Figure 4A and Figure 4B An example of a header hierarchy structure according to an implementation of the present disclosure is shown;

[0009] Figure 5A Another example of a spreadsheet table according to an implementation of the present disclosure is shown, wherein formulas are displayed instead of result values;

[0010] Figure 5B Showing the implementation according to the present disclosure Figure 5A An example of the header hierarchy of a spreadsheet table in ;

[0011] Figure 6 A block diagram of a table analysis module according to another implementation of the present disclosure is shown;

[0012] Figure 7 shows an example user interface according to an implementation of the present disclosure;

[0013] Figures 8A to 8C shows an example user interface according to an implementation of the present disclosure;

[0014] Figure 9 A flow chart illustrating a process for table conversion according to an implementation of the present disclosure is shown.

[0015] Throughout the drawings, the same or similar reference numerals refer to the same or similar elements. DETAILED DESCRIPTION

[0016] The present disclosure will now be discussed with reference to a number of example implementations. It should be understood that these implementations are discussed only to enable those skilled in the art to better understand and implement the present disclosure, and do not imply any limitation on the scope of the subject matter.

[0017] As used herein, the term "including" and its variations should be interpreted as open-ended terms meaning "including but not limited to." The term "based on" should be understood as "based, at least in part, on." The terms "an implementation" and "implementation" should be interpreted as "at least one implementation." The term "another implementation" should be interpreted as "at least one other implementation." The terms "first," "second," etc. may refer to different or the same objects. Other explicit or implicit definitions may be included below.

[0018] Sample Environment

[0019] In various situations, it's desirable to convert a spreadsheet table into a relational table for subsequent machine processing. Currently, a key step in today's typical workflow involves manually extracting data from a spreadsheet table and converting it into a unified structure, such as a relational table. This manual extraction and conversion is tedious and time-consuming, especially when the table is complex. Due to the difficulty machines have in understanding spreadsheet tables, automated techniques for converting spreadsheet tables into relational tables are lacking. In implementations of the present disclosure, a solution for automating spreadsheet table conversion is provided.

[0020] Figure 1 1 is a block diagram of a computing device 100 in which various implementations of the present disclosure may be implemented. It should be understood that Figure 1 The computing device 100 shown is for illustration purposes only and is not intended to limit the functionality and scope of the implementation of the present disclosure in any way. Figure 1 As shown, computing device 100 comprises a general computing device 100. Components of computing device 100 may include, but are not limited to, one or more processors or processing units 110, memory 120, storage device 130, one or more communication units 140, one or more input devices 150, and one or more output devices 160.

[0021] In some implementations, the computing device 100 can be implemented as any user terminal or server terminal with computing capabilities. The server terminal can be a server provided by a service provider, a large computing device, etc. The user terminal can be, for example, any type of mobile terminal, fixed terminal, or portable terminal, including a mobile phone, a station, a unit, a device, a multimedia computer, a multimedia input board, an Internet node, a communicator, a desktop computer, a laptop computer, a notebook computer, a netbook computer, a tablet computer, a personal communication system (PCS) device, a personal navigation device, a personal digital assistant (PDA), an audio / video player, a digital camera / camcorder, a positioning device, a television receiver, a radio broadcast receiver, an e-book device, a gaming device, or any combination thereof, including accessories and peripherals of these devices, or any combination thereof. It is conceivable that the computing device 100 can support any type of interface to the user (such as "wearable" circuitry, etc.).

[0022] Processing unit 110 may be a physical or virtual processor and may implement various processes based on programs stored in memory 120. In a multi-processor system, multiple processing units execute computer-executable instructions in parallel to increase the parallel processing capabilities of computing device 100. Processing unit 110 may also be referred to as a central processing unit (CPU), a microprocessor, a controller, or a microcontroller.

[0023] The computing device 100 typically includes various computer storage media. Such media can be any media accessible to the computing device 100, including but not limited to volatile and non-volatile media, or detachable and non-detachable media. The memory 120 can be a volatile memory (e.g., registers, cache memory, random access memory (RAM)), a non-volatile memory (e.g., read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), or flash memory), or any combination thereof. The storage device 130 can be any detachable or non-detachable medium and can include a machine-readable medium such as a memory, a flash drive, a disk, or another other medium that can be used to store information and / or data and can be accessed in the computing device 100.

[0024] The computing device 100 may also include additional detachable / indetachable, volatile / non-volatile storage media. Figure 1 Although not shown, a magnetic disk drive for reading from and / or writing to a removable and non-volatile magnetic disk, and an optical disk drive for reading from and / or writing to a removable non-volatile optical disk may be provided. In such a case, each drive may be connected to the bus (not shown) via one or more data media interfaces.

[0025] The communication unit 140 communicates with another computing device via a communication medium. In addition, the functionality of the components in the computing device 100 can be implemented by a single computing cluster or multiple computers that can communicate via a communication connection. Thus, the computing device 100 can operate in a networked environment using logical connections to one or more other servers, networked personal computers (PCs), or other general-purpose network nodes.

[0026] Input device 150 may be one or more of a variety of input devices, such as a mouse, keyboard, trackball, voice input device, etc. Output device 160 may be one or more of a variety of output devices, such as a display, speakers, printer, etc. Through communication unit 140, computing device 100 may also communicate with one or more external devices (not shown), such as storage devices and display devices, and, if desired, with one or more devices that enable a user to interact with computing device 100, or any device that enables computing device 100 to communicate with one or more other computing devices (such as a network card, modem, etc.). Such communication may be performed via an input / output (I / O) interface (not shown).

[0027] In some implementations, instead of being integrated into a single device, some or all components of computing device 100 may be arranged in a cloud computing architecture. In a cloud computing architecture, components may be provided remotely and work together to implement the functionality described in this disclosure. In some implementations, cloud computing provides computing, software, data access, and storage services without requiring the end user to know the physical location or configuration of the systems or hardware providing these services. In various implementations, cloud computing provides services via a wide area network (such as the Internet) using appropriate protocols. For example, a cloud computing provider provides an application over a wide area network that can be accessed through a web browser or any other computing component. The software or components of the cloud computing architecture and the corresponding data may be stored on servers at a remote location. Computing resources in a cloud computing environment may be consolidated or distributed across locations in remote data centers. Cloud computing infrastructure can provide services through shared data centers, although they appear to users as a single access point. Therefore, cloud computing architecture can be used to provide the components and functionality described herein from a service provider at a remote location. Alternatively, they can be provided from a conventional server, installed directly on a client device, or otherwise installed.

[0028] The computing device 100 can be used to implement table conversion in the implementation of the present disclosure. Therefore, the computing device 100 is also referred to as a "table conversion device" hereinafter. The memory 120 can include one or more table analysis modules 122 having one or more program instructions. These modules can be accessed and executed by the processing unit 110 to perform the functions of the various implementations described herein.

[0029] When performing table conversion, computing device 100 may receive electronic form file 170 via input device 150. Electronic form file 170 includes at least one electronic form table 172 for conversion. Table analysis module 122 performs electronic form table-to-relational table conversion on electronic form table 172 to generate one or more relational tables 180. Relational table 180 includes multiple data records 182, each data record 182 including data items from electronic form table 172. Relational table 180 arranges the data items in a uniform structure. Output device 160 may present relational table 180 to a viewer, or transmit or store relational table 180 to another device or database. Relational table 180 may also be stored locally at computing device 100 for future use.

[0030] As used herein, a "spreadsheet table" refers to a table comprising cells or a grid in rows and columns, having any layout pattern indicating data values in a two-dimensional form. A spreadsheet table can be generated, edited, and / or presented using a spreadsheet application. As used herein, a "spreadsheet application" refers to an unmodified, commercially available application that is operable to present and process data as a spreadsheet comprising a grid of cells. Examples of spreadsheet applications include, but are not limited to Excel and Open Office Calc.

[0031] As used herein, a "relational table" has a set of records, wherein each record is referred to as a row of the table. A relational table organizes data in a unified or normalized structure, wherein each record in the same relational table has the same number of data fields. Typically, one or more data fields of a record can form an index to one or more other data fields containing data values for the record. However, some fields in a record may not store data, as indicated by a NULL value. The corresponding data fields of a relational table form a set of columns, which may have a specific name that may not be part of the data itself. Each data field of a record may have a specific meaning, so in some cases, a relational table may have a header record containing header fields for describing the corresponding data fields. The header record may be placed as the first row of a relational table. A relational table may sometimes be referred to as a database table or a column-major flat table.

[0032] As used herein, the "conversion" operation from a spreadsheet table to a relational table is to use the relational table to index each data value in the spreadsheet table. Compared to the spreadsheet table, the relational table is easier for machines to analyze and process.

[0033] It should be understood that Figure 1 The electronic form table and the relationship table shown are for illustration purposes only. In other examples, any electronic form table can be processed and any relationship table can be generated accordingly.

[0034] How it works and a sample structure for implementing table transformations

[0035] According to the implementation of the present disclosure, a scheme for converting an electronic form table is proposed. In this scheme, one or more header areas and data areas of an electronic form table are detected. By analyzing the merging and / or indentation of cells in the header area, and / or the functional relationship between data items in corresponding cells of the data area, the hierarchical structure of each header area is determined. Based on the identification of the hierarchical structure of the header area, the electronic form table can be converted into a relational table. In this way, by promoting the understanding of the header structure based on the header hierarchy level, automatic conversion from an electronic form table to a relational table can be achieved. The basic principles of the present disclosure and multiple example implementations are described below with reference to the accompanying drawings.

[0036] First reference Figure 2 , Figure 2 A block diagram of a table analysis module according to an implementation of the present disclosure is shown. For illustration purposes, reference is made to Figure 1 The table analysis module 122 in the computing device 100 is used to implement the table conversion example described herein. The table analysis module 122 includes multiple modules for implementing multiple stages in the conversion of the electronic form table.

[0037] In order to better understand the implementation of the present disclosure, some basic concepts related to electronic spreadsheets are first introduced.

[0038] A spreadsheet table includes cells (or a grid) arranged in rows and columns. Cells are the basic components of a table. In some cases, multiple cells can be merged into one cell, called a merged cell. A merged cell in a row or column can extend across multiple cells in a subsequent row or cell; therefore, the length of a merged cell can be equal to the total length of multiple cells in a subsequent row or cell. The data filled in a cell can generally be called a data item. Some cells in a spreadsheet table can be blank and not filled with any valid value or character. A data item can be a string or numeric value in any representation format. Data items and cells can be organized in various structures and / or presented in various ways supported by the editing tool of the spreadsheet table.

[0039] A spreadsheet table can generally be divided into different types of areas, including a title area for presenting the title of the spreadsheet table, a note area for presenting notes related to the table, a data area for presenting data values, and a header area for indexing or describing the data values within the data area. Each area in the table can consist of one or more cells in one or more rows or columns of the spreadsheet table. Some spreadsheet tables may not include a title area or a note area.

[0040] The header area may typically be located at the top or left side of the electronic form table. The header area located at the top side may be referred to as the top header area, while the header area located on the left side may be referred to as the left header area. It will be understood that a header area located at the bottom or right side of the electronic form table is also possible. The top header area (or bottom header area) has data items arranged in a row orientation, and therefore may sometimes be referred to as a row-oriented header area. The left header area (or right header area) has data items arranged in a column orientation, and therefore may sometimes be referred to as a column-oriented header area. The electronic form table may include more than one header area, for example, including a top header area and a left header area. It should be noted that although other types of header areas (e.g., header areas at the bottom or right side) are also possible, for ease of description, the top header area and the left header area are described herein as typical examples of header areas.

[0041] The inventors have discovered that the data items filled in the cells of the header area can be classified into different semantic categories according to their functions in the electronic form table. The data items in the header area can be used as indexes to index the data items in the rows or columns of the data area, and thus such an index can be considered as a semantic category. For example, in a electronic form table containing product sales in different countries over the past ten years, the names of the countries indicated in the table (such as China, the United States, Australia, etc.) can be the corresponding indexes for indexing sales, and the years indicated in the table (such as 2016, 2017, 2018, etc.) can also be the corresponding indexes for indexing sales together with the country names. In some cases, the data items in the header area can be used as index set names to describe the set of indexes for the data items in the rows or columns of the index data area, and thus such an index set name can be considered as a semantic category. The set of indexes can be in the same row or column of the header area and can be semantically aggregated. For example, "country" refers to the index set name of indexes such as China, the United States, Australia, etc.

[0042] Additionally, data items in the header area can be value names used to describe data items in a row or column of the data area, and thus such value names can be considered a semantic category. This type of data item cannot be used to index data items in a data area, because data items in different rows or columns indexed by different index data items can have the same value name. For example, a value name can be a measurement such as "number," "amount," or "percentage," or a unit of measurement such as "meter," "ml," or the like. In some cases, a data item in the header area can be an aggregate name used to describe data items in a row or column of the data area that are calculated from data items in at least one other row or column of the data area. An example of such an aggregate name is "Total" or "Subtotal," which describes a result summarized from multiple other data items. Other examples include "Maximum," "Minimum," "Average," "Division," and the like. It should be understood that the terminology of value names and aggregate names is provided for illustrative purposes only, and that variations thereof representing the same or similar semantics may also be included in spreadsheet tables.

[0043] We have discussed some concepts related to spreadsheet tables above. Figure 2 The table analysis module 122 in the embodiment of the present invention implements the table conversion process. As shown in the figure, the table analysis module 122 includes a region detection stage or module 210, a table header hierarchy structure identification stage or module 220 and a table conversion stage or module 230.

[0044] In some implementations, an electronic document or file containing an electronic form table 172 can be provided as input to the table analysis module 122. The table analysis module 122 can first detect the electronic form table 172 from a file, such as the electronic form file 170. For example, the electronic form table 172 in the file 170 can be detected by determining a bounding box of the electronic form table 172 in the file 170. In some implementations, the area of the electronic form table 172 in the file 170 can be explicitly or implicitly indicated by a user. Any method for automatically or manually assisting with table detection (whether existing or to be developed in the future) can be used to detect the extent of the electronic form table 172 in the electronic document.

[0045] After determining the bounding box of the electronic form table 172, the region detection module 210 is configured to detect different types of regions contained in the electronic form table 172, specifically, to detect at least one header region and a data region. The electronic form table potentially includes a top header region and / or a left header region. The region detection module 210 can detect whether each of these two types of header regions is contained in the electronic form table 172. If one or more header regions are detected, the data region of the electronic form table can be determined to include valid data items in the same rows and / or columns as the one or more header regions. Figure 3 Detection results on an example electronic form table 172 after detection by the region detection module 210 are shown. As shown, upon detection, the electronic form table 172 includes a top header region 310-1 and a left header region 310-2 (collectively or individually referred to as the header region 310 of the electronic form table 172). The electronic form table 172 also includes a data region 320 that contains data items that can be indexed by data items in the header region. Header detection in the region detection module 210 will be discussed in detail below.

[0046] The detection results of the region detection module 210, i.e., the detection of (multiple) header regions and data regions, are provided to the header hierarchy identification module 220. The header hierarchy structure identification module 220 is configured to determine the hierarchical structure of the data items in each of the detected (multiple) header regions. The hierarchical structure associated with a header region may include one or more hierarchical levels, each hierarchical level corresponding to one or more data items filled in the cells of the header region. In a complex electronic form table, the header region may be designed to have more hierarchical levels semantically. The identification of the underlying hierarchical structure of the header region can facilitate the understanding of the electronic form table and subsequent table conversion.

[0047] According to an implementation of the present disclosure, the hierarchical structure of the data items in the header area is determined based on the detection of one or more of the following three features, including: the merging of cells in the header area, the indentation level of cells in the header area, or the functional relationship defined in one cell with respect to at least one other cell in the data area. When more than one header area is detected in the electronic form table 172, the hierarchical structure of each header area can be determined in parallel or sequentially by the header hierarchy identification module 220 in a similar manner. By detecting any of the three features from the header area, the hierarchical structure of the data items in each header area can be understood. In some implementations, additionally or alternatively, automatic semantic analysis of the data items in the header area can be applied in the header hierarchy structure identification module 220 to determine the header hierarchy structure. Some implementations of header hierarchy structure identification will be discussed in more detail below.

[0048] The hierarchical structure of the header area(s) of electronic form table 172 is provided to table conversion module 230, which is configured to convert electronic form table 172 into at least one relational table 180 based on the hierarchical structure(s) of the corresponding header area(s). A relational table arranges the data items in electronic form table 172 in a unified structure. Generally, a relational table may include multiple data records, each of which includes multiple data fields corresponding to the data items in the electronic form table. The corresponding data fields in the multiple data records include data items in the header area at the same hierarchical level in the hierarchical structure, or data items in a data area indexed by a data item in the header area.

[0049] Depending on the hierarchical structure, the electronic form table 172 may have one or more possible drill-down levels. The data items in the electronic form table 172 may be rearranged from the perspective of each drill-down level to form a corresponding relational table. The following will discuss hierarchical table transformation in detail.

[0050] It should be understood that Figure 2 The modules in the example may be implemented as one or more software engines, components, etc., which are configured with logic for implementing the functionality belonging to the particular module. Each module may be implemented using one or more such software engines, components, etc. The software engines, components, etc. execute on one or more processors of one or more computing systems or devices and utilize or operate on data stored in one or more storage devices, memories, etc. on one or more computing systems. In some implementations, Figure 2 The different modules in can be implemented as a single module, and Figure 2 A single module in may be separated into more than one module. In some implementations, one or more additional modules may be included in the table analysis module 122.

[0051] Header area detection

[0052] When detecting one or more header areas in the electronic form table, the area detection module 122 can determine whether there is a separator line in the horizontal direction or the vertical direction of the electronic form table 172 that separates the header area from the data area. The separator line can be considered to be the line between the last row of the top header area and the first row of the data area, or the line between the last column of the left header area and the first column of the data area. The top header area can include one or more rows of the electronic form table 172, and the left header area can include one or more columns of the electronic form table 172. If a separator line exists in the horizontal direction, the top header area is detected as including the following area: the area includes one or more rows from the first row of the electronic form table 172 to the row that defines the separator line. Similarly, if a separator line exists in the vertical direction, the left header area can be detected.

[0053] In some implementations, the region detection module 122 may detect one or more of the following characteristics of the rows and columns in the spreadsheet table 172 to determine whether a top header region or a left header region exists, and which region of the spreadsheet table 172 is considered to be the top header region or the left header region. These characteristics may include: the presence of at least one blank value detected in a cell of a row and / or column of the spreadsheet table 172; the data type of the cell of the row and / or column of the spreadsheet table, the relative position of the row and / or column in the spreadsheet table 172; the data conversion in the row and / or column of the spreadsheet table 172, which indicates a contrast between data items in adjacent cells; and / or the distribution of values in the rows and / or columns of the spreadsheet table 172. By considering one or more characteristics associated with the rows and / or columns of the spreadsheet table 172, a possible row or column for a header region may be detected at a separator line.

[0054] Characteristics associated with rows and / or columns may be referred to as row / column characteristics or line characteristics, which may be evaluated from one or more characteristics of cells in the corresponding rows and / or columns. In some implementations, characteristics of cells that facilitate header region detection may include: the data item populated in the cell, the type of the data item (by detecting whether the data item is represented as a floating point, integer, or string), and / or the representation of the numerical value populated in the cell (e.g., as a number, date, time, or other form). Alternatively or additionally, other characteristics may be derived from the cell, such as information about cell merging, cell styling, semantics of the data item, and the like. Cell styling may, for example, include font, font color, style, background color, indentation level, spacing, and / or other characteristics that define how the data item in the cell is presented.

[0055] In some implementations, the region detection module 210 may apply a machine learning model to detect one or more header regions within the spreadsheet table 172 based on row / column characteristics. Various types of machine learning models may be applied, examples of which may include gradient boosted decision tree (GBDT) models, support vector machines (SVMs), random forests, neural networks, and the like. The output of the model is the location of the top header region and / or the left header region within the spreadsheet table 172. For example, the output of the model may indicate that a line between two rows or columns is a separator between the header region and the data region. In some implementations, the row / column characteristics may be used directly as input to the machine learning model. If there are one or more blank columns or rows adjacent to the separator, such blank columns or rows may be considered to be part of the data region. In some other implementations, various other characteristics may be derived from the row / column characteristics used as input to the machine learning model, as long as such characteristics are considered to be correlated with the model's output. The machine learning model may be determined using training data. The training data may include various model inputs and corresponding ground truth outputs for a known spreadsheet table.

[0056] Header hierarchical structure identification

[0057] As described above, the header hierarchy identification module 220 determines the hierarchical structure of each detected header area in the electronic form table 172 based on one or more of unit merging, indentation levels, functional relationships, and possible data item semantic analysis. All or part of the hierarchical structure can be determined based on one of the above factors. In some implementations, the hierarchical structure can be represented as a tree structure, where each node represents a data item in the header area.

[0058] In some implementations, if one or more merged cells appear in the header area, the header hierarchy identification module 220 can determine the hierarchical structure of the header area. When a merged cell is detected in a row or column, the header hierarchy identification module 220 can also determine whether there are subsequent rows (in the case of the top header area) or subsequent columns (in the case of the left header area) in the header area. If there are subsequent rows or columns, the merged cell extends across multiple cells in the subsequent row or column, and the header hierarchy identification module 220 determines a node at a hierarchical level in the hierarchical structure (sometimes referred to as the "first" hierarchical level here for ease of description) to represent the data item in the merged cell, and determines multiple nodes at a hierarchical level in the hierarchical structure (sometimes referred to as the "second" hierarchical level here for ease of description) to represent the data items in the multiple cells respectively. The second hierarchical level is lower than the first hierarchical level. That is, the data item in the merged cell has a higher hierarchical level than the data item in the cell in the subsequent row or column above which the merged cell extends. This is the rule people follow when designing electronic form tables.

[0059] exist Figure 1 and Figure 3 In the example electronic form table 172 shown, the top header area 310-1 has two merged cells in its first row, which are filled with data items "Small size" and "Large size," respectively. Each of the two merged cells extends across two cells in the subsequent second row. Therefore, the data items in the merged cells are at a higher hierarchical level than the data items in the subsequent rows covered by the merged cells. Figure 4A An exemplary hierarchical structure 401 of the top header region 310-1 is shown. As shown, the hierarchical structure 401 includes nodes 420-1 and 420-2, which represent data items in two merged cells in the top header region 310-1. Nodes 420-1 and 420-2 have their child nodes 430-1 and 430-2, and child nodes 430-3 and 430-4, which represent data items in the cells of the header region covered by the merged cells. Nodes 420-1 and 420-2 are at the first hierarchical level in the hierarchical structure 401, which is higher than the hierarchical level of nodes 430-1 and 430-2.

[0060] In some implementations, the data items in the header area that are not related to cell merging can be determined as nodes in a hierarchical structure, and their hierarchical levels can be the same as the levels corresponding to the data items at the same row (for the top header area) and the same column (for the left header area). Figure 4A In the structure 401, the node 420-3 representing the data item "Totalhouseholds" is at the same hierarchical level as the nodes 420-1 and 420-2.

[0061] In some examples, if it is not possible to find any specific data item in the header area of the electronic form table 172 that satisfies the following conditions (the specific data item is associated with the required node at the hierarchical level determined in the hierarchical structure), a virtual node can be constructed in the hierarchical structure. In some examples, the virtual node can be a virtual root node of the hierarchical structure, or can be a virtual parent node of a set of child nodes representing a set of data items in the header area. Figure 4A As shown in the example of FIG, a virtual “root” node 410 is included in the hierarchical structure 401.

[0062] In some implementations, the indentation level in a cell can reflect their hierarchical relationship. The indentation level is typically used to determine the hierarchical structure in the header area (left header area, or possible right header area) that the column is facing. Therefore, the header hierarchy identification module 220 can determine the indentation level of the data items in the same row of the header area (e.g., left header area). In some examples, the indentation level of a cell can be measured by the distance between the left side of the cell and the beginning of the data items filled therein. The greater the measured distance, the higher the indentation level. Typically, the probability that the data items in the cells with the same indentation level are at the same hierarchical level in the entire hierarchical structure of the header area is higher, and the data items in the cells with higher indentation levels may be at a lower hierarchical level than the data items in the cells with lower indentation levels.

[0063] Specifically, when determining the hierarchical structure of the left header region of the electronic form table 172, the header hierarchy identification module 220 detects that the indentation level of a cell in the header region is lower than the indentation level of one or more other cells in the header region. In this case, the header hierarchy identification module 220 determines that a node at one hierarchical level in the hierarchical structure (sometimes referred to herein as the "third" hierarchical level for ease of description) represents a data item with a lower indentation level, and determines that a node at another hierarchical level in the hierarchical structure (sometimes referred to herein as the "fourth" hierarchical level for ease of description) represents a data item with a higher indentation level.

[0064] In some implementations, the header hierarchy identification module 220 detects that multiple cells in the same column of the left header area have the same indentation level, and can then determine, based at least on such detection, corresponding nodes in the hierarchical structure at the same hierarchy level (which is sometimes referred to as the "fifth" hierarchy level for ease of description only) to represent the data items in these multiple cells.

[0065] exist Figure 1 and Figure 3 In the example electronic form table 172 shown, the left header area 310-2 includes data items arranged using different indentation levels. More specifically, the cell filled with the data item "Married-couple families" has a lower indentation level than the indentation levels of the following four cells related to age. In addition, the indentation levels of the following four cells are determined to be the same. Therefore, according to the indentation level, Figure 4B An exemplary hierarchical structure 402 defining the left header area 310 - 2 is shown, in which a node 440 representing a data item “Married-couple families” having a lower indentation level is at a higher hierarchical level than nodes 450 - 1 to 450 - 4 representing data items having higher indentation levels in the subsequent four cells.

[0066] It should be understood that although a comparative example of two different indentation levels is shown, in other implementations, a column-oriented header region, such as the left header region of a spreadsheet table, may include cells with two or more different indentation levels. The hierarchical structure of the header region may be determined accordingly.

[0067] Alternatively or in addition, the header hierarchy identification module 220 can detect one or more functional relationships between data items in different units of the data area and determine the hierarchical structure of the header area based on the detected functional relationships. Many electronic form editing tools provide users with the option of inserting various formulas to calculate data items from one or more other data items in the data area. A formula can represent a functional relationship between data items, which indicates that an operation is to be performed on one or more data items to calculate a result data item. Some examples of functional relationships can include summing up, dividing, maximizing, minimizing, conditional functions, etc.

[0068] The functional relationship between the data items in the data region can reflect a hierarchy of the corresponding data items in the header region. Therefore, when a functional relationship defined in a first unit with respect to at least one second unit in the data region is detected, the header hierarchy identification module 220 can determine one or more nodes at a hierarchy level (sometimes referred to herein as the "sixth" hierarchy level for ease of description only) in the hierarchy structure to represent the corresponding data items in the unit of the header region, and determine at least one node at a hierarchy level (sometimes referred to herein as the "seventh" hierarchy level for ease of description only) in the hierarchy structure to represent the corresponding data items in the unit of the header region. The data item in the header region corresponding to the data item in the data region is a data item that indexes or describes the data item in the data region, and the two data items can be in the same column (in the case of the top header region) or in the same row (in the case of the left header region).

[0069] Depending on the type of functional relationship, the sixth and seventh hierarchical levels can be at the same level or at different levels. In the case of different levels, the sixth hierarchical level can be higher or lower than the seventh hierarchical level. In some examples, if the functional relationship indicates a sum operation, the sixth hierarchical level can be higher than the seventh hierarchical level. If the functional relationship indicates an operation of averaging, maximizing, or minimizing multiple data items, or an operation of dividing data items, the sixth hierarchical level can be equal to the seventh hierarchical level. The mapping between the difference or equality of two hierarchical levels and different types of functional relationships can be preconfigured.

[0070] To illustrate the determination of hierarchical structure based on functional relationships, Figure 5A 172, wherein the left header area 510 and the data area 520 of the table are detected. Figure 5A As highlighted in FIG, a summation formula is inserted in the cell indexed by column B and row 2 (represented by "B2," and the positions of other cells can be similarly represented), indicating that the data value in cell B2 is the sum of the data values in four other cells B3, B4, B5, and B8 in the data area 520. A similar summation formula is inserted in cell C2 in the same row as cell B2. In this way, the header hierarchy structure identification module 220 can determine that the data item "All Sources" in the header area 510 corresponding to the data items in cells B2 and C2 is at a higher hierarchical level, while the data items "Stationary Sources," "Area-wide Sources," "On-road Mobile," and "Other Mobiles" in the header area 510 corresponding to the data items in cells B3, B4, B5, and B8 are at a lower hierarchical level, and their nodes are connected to the node representing the data item "All Sources." Similarly, summation formulas are detected in cells B5 and C5 in the same row, and summation formulas are detected in cells B8 and C8 in another row, indicating that each data value in these cells is the sum of the data values in the other cells.

[0071] Figure 5B A hierarchical structure 530 is shown, which is identified from the left header region 510 based on the functional relationship detected in the data region of the example electronic form table 172. Node 540 representing the data item of cell A2 in the header region 510 is at a higher hierarchical level, while nodes 550-1 to 550-4 representing the data items of cells A3, A4, A5, and A8 in the header region 510, as child nodes of node 540, are at a relatively lower hierarchical level. Similarly, node 550-3 has two child nodes 560-1 and 560-2 at a lower hierarchical level representing the data items of cells A6 and A7, and node 550-4 has three child nodes 560-3, 560-4, and 560-5 at a lower hierarchical level representing the data items of cells A9 to A11.

[0072] In some implementations, the header hierarchy identification module 220 can perform semantic analysis on the data items contained in the header area to support the detection of the header hierarchy. Semantic analysis can help determine whether multiple data items in the header area have parallel semantic meanings or belong to higher semantic roles. For example, if the sequence of data items {Apple, Banana, Orange, Pear, China, US} is included in the header area, then through semantic analysis, the header hierarchy identification module 220 can determine that the data item set {Apple, Banana, Orange, Pear} is at the same hierarchical level, which can be lower than the hierarchical level of the virtual node corresponding to "fruit" (if such data items are not included in the header area). It can also be determined that the data item set {China, US} is at the same hierarchical level, which can be lower than the virtual hierarchical level corresponding to "country" (if such data items are not included in the header area).

[0073] The above implementation describes determining the hierarchical level of the header area in the hierarchical structure based on various factors including unit merging, indentation level and / or functional relationship. The hierarchical results of the determination process based on different factors can be aggregated to form a complete hierarchical structure of the header area.

[0074] In some implementations, a machine learning-based approach can be applied to predict the correct hierarchical structure from the results. For example, if there are inconsistencies in the hierarchical structure results determined from these factors, a machine learning model can be trained and applied. In addition, the machine learning model can also help process certain spreadsheet tables where cell merges, indents, and / or functional relationships are not found in the header area or are insufficient to detect the header hierarchy. For example, if cells are not actually merged, but are visually designed to have a "merged" effect and extend across multiple other cells, the machine learning model can be trained to facilitate predicting the corresponding hierarchical levels associated with these cells. In some implementations, when such inconsistencies are discovered, user intervention or confirmation may be required to invite the user to adjust or confirm the hierarchical structure based on human understanding of the header area of the spreadsheet table.

[0075] In some implementations, in addition to the hierarchical structure, the header hierarchy identification module 220 can also be configured to determine corresponding semantic categories for data items in the header area of the electronic form table 172, and accordingly mark the nodes representing the hierarchical structure of the data items as having corresponding semantic categories. The semantic categories of the data items in the header area can be those semantic categories described above, including: an index for a data item indexing a row or column of the data area, an index set name for describing a set of indexes for indexing data items in a row or column of the data area, a value name for describing a data item in a row or column of the data area, and an aggregate name for describing the following data items in a row or column of the data area, which data items are calculated from data items in at least one other row or column of the data area. The semantic categories used for node markings in the header area can also be used for table conversion in the table conversion module 230.

[0076] In order to determine the semantic category of the data item, the header hierarchy identification module 220 can also identify one or more node sets linked in a tree structure according to a parent-child relationship, and construct multiple predetermined semantic patterns for each node in the node set. Each semantic pattern indicates a different combination of semantic categories of each node in the parent-child relationship. For example, for a node set with a parent node and two child nodes, the semantic pattern can be determined as {index set name, index, index}, {index, index, aggregation name}, {index, value name, value name}, where the first semantic category in the above list is for the parent node, and the remaining semantic categories are for the child nodes.

[0077] The header hierarchy identification module 220 can then determine the corresponding semantic matching level between the tree structure and multiple predetermined semantic patterns. The semantic matching level can be determined by measuring whether each semantic category in the semantic pattern matches the corresponding node in the tree structure, and then summing the number of matching semantic categories found in the semantic pattern. In some cases, the semantic matching level can be determined by measuring the overall matching probability of the semantic pattern that matches each node in the tree structure. If the semantic matching level indicates that all or most of the semantic categories in a pattern match the nodes in the tree structure, or the total matching probability exceeds a threshold, the header hierarchy identification module 220 determines that the semantic categories of the set of nodes in the tree structure are the corresponding semantic categories indicated in the pattern. In some implementations, a pre-trained machine learning model can be applied to determine the semantic matching level between the tree structure and the semantic pattern. The use of the semantic category of the node will be described in the table conversion process below.

[0078] Table orientation detection

[0079] In some implementations, during the table conversion process, it is also desirable to determine the orientation of the data in the electronic form table 172. Typically, the orientation of the data in the table can include column-major orientation, row-major orientation, or a cross-row and column orientation. The table analysis module 122 can also include a module that implements such table orientation detection. Figure 6 An example of the table analysis module 122 in such an implementation is shown. As shown, the table analysis module 122 further includes an orientation determination module 610 for determining the orientation of the data arrangement in the electronic form table 172. The orientation determination module 610 may require the detection results of the region detection module 210 and the electronic form table 172 as input.

[0080] In some implementations, the orientation determination module 610 can detect whether the presentation format of the data items is significantly consistent in the row direction and / or column direction of the data region. If the data items in the data region have identical presentation formats in the row direction, the column direction, or both directions, the orientation determination module 610 can determine whether the electronic form table 172 has a row-first orientation, a column-first orientation, or a cross orientation in the rows and columns. Typically, the cross orientation can have both the top header region and the left header region.

[0081] If the representation format of the data items does not indicate significant consistency, or as an alternative, the orientation determination module 610 can utilize one or more other rules. For example, the orientation determination module 610 can detect whether there is consistency in the data type in the row direction and / or column direction of the data area, whether there are too many blank cells in the row direction and / or column direction of at least one header area (e.g., the number of blank cells exceeds a threshold number), and / or whether the length of the data in the row direction or column direction of the data area is long enough (e.g., above a predetermined length threshold). Depending on one or more results of the detection / determination, the orientation determination module 610 can determine the direction in which the data in the electronic form table 172 is arranged. For example, Figure 1 and Figure 2 The example spreadsheet table 172 shown has a cross-wise orientation in rows and columns.

[0082] In some implementations, the orientation determination module 610 may also apply a machine learning model to predict whether the input electronic form table 172 has a row-major orientation, a column-major orientation, or a cross-major orientation. The machine learning model may be trained to explore the aforementioned rules and / or feature information related to any other aspects of the header and data areas to determine the orientation of the electronic form table 172. Various machine learning models for classification may be employed in this regard.

[0083] In some implementations, in addition to being used in the table conversion phase, the resulting orientation of the data arrangement from the orientation determination module 610 may be used to guide the determination of the header hierarchy levels by the header hierarchy identification module 220. For example, if it is determined that the electronic form table 172 has a column-major orientation, the hierarchy of the column-oriented header area (such as the left header area, if any) may not be identified because it may be simple or even not exist in a table with such a column-major orientation. The header hierarchy identification module 220 may only need to determine the hierarchy of the row-oriented header area (e.g., the top header area). Similarly, if it is determined that the electronic form table 172 has a row-major orientation, the hierarchy of the row-oriented header area (if any) may not be specifically identified, and only the hierarchy of the column-oriented header area may need to be identified.

[0084] Table Conversion

[0085] The table conversion module 230 is configured to convert the electronic form table 172 into at least one relational table 180 based on the hierarchical structure of the corresponding header area.

[0086] In some implementations, if the orientation of the data arrangement in the electronic form table 172 indicates a cross orientation, and the electronic form table includes a row-oriented header region and a column-oriented header region (such as a top header region and a left header region), the table conversion module 230 may perform an unpivoting operation on the electronic form table 172. If the hierarchical structure of the top header region and / or the left header region includes more than one hierarchical level, there may be more than one drill-down level for performing the unpivoting operation. All possible drill-down levels may be an enumerated number of combinations of hierarchical levels in the two hierarchical structures. Specifically, the table conversion module 230 may select a first hierarchical level from the hierarchical structure of the column-oriented header region and a second hierarchical level from the hierarchical structure of the row-oriented header region, and then perform an unpivoting operation on the electronic form table 172 with respect to the first hierarchical level and the second hierarchical level (referred to as a drill-down level pair) to obtain a relationship table. The relationship table may include data items in the hierarchical level in the header region and its higher hierarchical levels, as well as data items in the data region indexed or described by these data items.

[0087] In some implementations, if there are missing data items in the target drill-down level, there are some optional methods to handle such a situation. Specifically, in the conversion table for drill-down level k, if there is not a group of data items in one or more columns or rows at level k, the corresponding data items may not be shown in the converted relational table, including the data items at the higher level that exist. As a second option, the data items in the converted relational table can be displayed as blanks. Alternatively, the data items corresponding to the higher level table headers that exist can be copied and filled into the cells of the conversion table as the data items for the current drill-down level. In one implementation, the user can specify, for example, by user input: which of the above options can be applied when generating and presenting the converted table.

[0088] If the spreadsheet table 172 has a column-major orientation and there is more than one hierarchical level in the hierarchical structure (which means that the table is not flat), all data items corresponding to one or more hierarchical levels in the hierarchical structure (from the root node to the leaf nodes) can be concatenated into a new flat header for the table. Such a table can be output as a relational table. If the spreadsheet table 172 has a row-major orientation, the spreadsheet table 172 can first be transposed into a new spreadsheet table with a column-major orientation, and then the new spreadsheet table can be converted into a relational table in a manner similar to that of a spreadsheet table with a column-major orientation.

[0089] In some implementations, in a table conversion, if a header area includes a data item corresponding to an aggregate name, the spreadsheet table 172 may be converted to generate a relational table including a plurality of data records to record one of the data items in a row or column of the data area, and to record at least one data item in the header area for indexing the data item in the data area.

[0090] In some implementations, in a table conversion, if a header area of the electronic form table 172 includes one or more data items corresponding to an index set name, a value name, and / or an aggregate name, a header record may be created in the relational table, which includes data fields indicating one or more data items corresponding to the index set name, the value name, and / or the aggregate name, so as to describe subsequent records in the same data fields.

[0091] In some implementations, if a data field in a plurality of data records in a relational table contains a data item populated in a cell in a header area of a spreadsheet table, the data item is copied from the cell. If a data field contains a data item populated in a cell in a data area, the data item in the cell in the data area can be referenced in the relational table such that the data item in the data field changes as the data item in the cell in the data area changes.

[0092] Application and user interaction of table transformations

[0093] The functionality of the table analysis module 122 described herein may be implemented as a standalone application or as an added component in other applications.In some implementations, a specific user interface may be presented to the user to indicate the results of one or more stages during the table conversion process.

[0094] In one implementation, after determining one or more relationship tables 180, the relationship tables 180 and possibly the original electronic form table 172 can be presented to the user upon specific request or automatically. For each relationship table 180, the data item in the header area corresponding to the hierarchical level can be highlighted in the electronic form table 172, and the corresponding data field in the relationship table 180 can also be highlighted. For example, Figure 7 As shown, if the relationship table 180 is structured according to the following hierarchical levels, i.e., a hierarchical level having nodes related to age in the left header area and nodes related to family sizes in the top header area, the corresponding columns and rows are highlighted in different ways in the spreadsheet table 172, and the corresponding data fields are also highlighted. In addition, the data items in the data area of the spreadsheet table 172 and the corresponding data fields of the relationship table 180 can also be highlighted as shown in FIG. Figure 7 The data in the two tables are highlighted. This allows users to easily identify the corresponding relationship between the data in the two tables.

[0095] In some implementations, user interaction is allowed to guide or control the table conversion process. In one implementation, when determining the header hierarchy, the original hierarchical structure of the header area automatically determined by the header hierarchy identification module 220 can be presented to the user. The presentation can be triggered each time the hierarchy is determined for the user, or it can be triggered when the header hierarchy identification module 220 finds some inconsistency between the results determined from different relevant factors, as described above. The user is allowed to adjust the presented hierarchy. When a modification to the original hierarchical structure is received from the user, the original hierarchical structure is modified based on the modification to generate a hierarchical structure, and the original hierarchical structure is adjusted accordingly. In this way, it is flexible for the user to control table understanding and facilitate correct structure identification.

[0096] Figure 8A An example user interface 801 is shown presenting an original hierarchical structure determined for a header region of an electronic form table. In the original hierarchical structure, nodes 820, 822, and 824 are determined to be child nodes at a lower hierarchical level of a root node 810, and nodes 830 and 832 are determined to be respective child nodes of nodes 820 and 824. The user can indicate, through specific user input, that node 822 should be a child node at a lower level of node 820. Upon receiving the user input, the hierarchical structure can be modified accordingly in the user interface 801, such as Figure 8B shown.

[0097] In one implementation, a user is allowed to define how the electronic form table is converted. For example, a hierarchical structure of one or more table header areas may be presented to the user, and the user may flexibly select a desired level or one or more nodes within the hierarchy from which the electronic form table is to be drilled down. Upon receiving the user selection, the table conversion module 230 may determine the hierarchical level or node selected by the user and convert the electronic form table with respect to the one or more columns and / or rows corresponding to the selection.

[0098] Figure 8C An example user interface 802 is shown presenting a hierarchical structure of header areas for user selection. In this example, Figure 4A The user is allowed to select an entire hierarchy level or specifically select one or more (but not all) nodes at a hierarchy level to indicate his / her intention for table conversion. Figure 8C As shown, the user selects three nodes (not all four nodes) at the last hierarchical level. Therefore, the spreadsheet table 172 (i.e., Figure 1 and Figure 3 The example table shown in FIG) can be converted with respect to the rows in the header area corresponding to the three selected nodes. The data item corresponding to the index "Five or more" in the spreadsheet table will not be included in the relationship table.

[0099] Exemplary Process

[0100] Figure 9 Flowchart of table conversion process 900 according to some implementations of the present disclosure is shown. Process 900 can be implemented by computing device 100, for example, by table analysis module 122 in computing device 110. Process 900 can also be implemented by any other device or device cluster similar to computing device 100. For the purpose of description, reference is made to Figure 1 Process 900 is described.

[0101] At block 910, computing device 100 detects at least one header region and a data region of a spreadsheet table, the spreadsheet table including data items populated in cells arranged in rows and columns, the at least one header region including cells populated with data items for indexing or describing the data items in the cells of the data region. At block 920, computing device 100 determines a hierarchical structure of the data items in the at least one header region by detecting at least one of the following: merging of cells in the at least one header region, indentation levels of cells in the at least one header region, or a functional relationship defined in one cell with respect to at least one other cell in the data region. At block 930, computing device 100 converts the spreadsheet table into at least one relational table based on the determined hierarchical structure, the at least one relational table having the data items of the spreadsheet table arranged in a unified structure.

[0102] In some implementations, determining the hierarchical structure of at least one header region further includes determining the hierarchical structure based on semantic analysis of data items in the header region.

[0103] In some implementations, detecting at least one header area of the spreadsheet table includes: detecting at least one of the following features in the spreadsheet table: the presence of at least one blank value in a row or column of the spreadsheet table, a data type in a cell in a row or column of the spreadsheet table, a relative position of a row or column of the spreadsheet table, a data conversion in a row or column of the spreadsheet table, or a distribution of values in a row or column of the spreadsheet table; and determining at least one header area in the spreadsheet table based on the at least one detected feature.

[0104] In some implementations, method 900 further includes: determining corresponding semantic categories for data items in at least one header area, the corresponding semantic categories being selected from a group comprising: indices for indexing data items in rows or columns of the data area, index set names, sets for describing indexes for indexing data items in rows or columns of the data area, value names for describing data items in rows or columns of the data area, aggregate names for describing data items in rows or columns of the data area, the data items being calculated from data items in at least one other row or column of the data area; and wherein converting the spreadsheet table includes: also converting the spreadsheet table based on the determined semantic categories of the data items.

[0105] In some implementations, the hierarchical structure is represented as a tree structure, the tree structure including at least one set of nodes linked together in a parent-child relationship, each of the nodes representing a data item in at least one header area; and wherein determining the corresponding semantic category includes: for a given set of at least one set of nodes, determining a corresponding semantic matching level between the tree structure and a plurality of predetermined semantic patterns, each of the plurality of predetermined semantic patterns indicating a different combination of semantic categories for the corresponding nodes in the parent-child relationship; in response to one of the determined semantic matching levels satisfying a predetermined condition, determining the semantic category indicated by the corresponding semantic pattern of the data item represented by the given set of nodes.

[0106] In some implementations, determining the hierarchical structure includes: for each header region in at least one header region, in response to detecting the occurrence of a cell merge in the header region, detecting whether a first merged cell in a row or column of the header region extends across multiple second cells in a subsequent row or subsequent column of the header region; and at least partially based on detecting that the first merged cell extends across multiple second cells, determining a first node at a first hierarchical level in the hierarchical structure to represent a data item in the first merged cell, and determining multiple second nodes at a second hierarchical level in the hierarchical structure to represent data items in the multiple second cells, the second hierarchical level being lower than the first hierarchical level.

[0107] In some implementations, determining the hierarchical structure includes: in response to determining that one of the at least one header region is a column-oriented header region, performing at least one of the following: determining a third node at a third hierarchical level in the hierarchical structure to represent a data item in the third cell, and determining a fourth node at a fourth hierarchical level in the hierarchical structure to represent a data item in the fourth cell, based at least in part on detecting that the indentation level of the third cell in the header region is lower than the indentation level of the fourth cell in the header region; and / or determining a corresponding node at a fifth hierarchical level in the hierarchical structure to represent the data items in the multiple fifth cells, based at least in part on detecting that the multiple fifth cells in the column of the header region have the same indentation level.

[0108] In some implementations, determining the hierarchical structure includes: for each header area of at least one header area, determining a sixth node at a sixth hierarchical level in the hierarchical structure to represent a data item in the sixth cell in the header area based at least in part on detecting a functional relationship defined in a first cell in the data area with respect to at least one second cell, and determining at least one seventh node at a seventh hierarchical level in the hierarchical structure to represent a data item in a seventh cell in the header area, the difference between the sixth hierarchical level and the seventh hierarchical level being based on a type of functional relationship, wherein the data item in the sixth cell indexes or describes the data item in the first cell in the data area, and the data item in the seventh cell indexes or describes the data item in at least one second cell in the data area.

[0109] In some implementations, determining the hierarchical structure includes: for each header area in at least one header area, determining an original hierarchical structure of the header area based on detection; presenting the original hierarchical structure to a user; in response to receiving a modification to the original hierarchical structure from the user, modifying the original hierarchical structure based on the modification to generate a hierarchical structure; and in response to receiving confirmation of the original hierarchical structure from the user, determining the original hierarchical structure as a hierarchical structure.

[0110] In some implementations, the method includes: determining the orientation of data arrangement in the electronic form table based on at least one of the following: detecting consistency in the representation format of data items in the row direction and / or column direction of the data area, detecting consistency in the data type in the row direction and / or column direction of the data area, the number of blank cells in at least one header area in the row direction and / or column direction of at least one header area exceeds a threshold number, or determining that the length of data in the row direction or column direction of the data area is higher than a predetermined length threshold, and wherein the orientation of the data arrangement is one of the following: column-first orientation, row-first orientation, or a cross orientation of rows and columns, and wherein converting the electronic form table further includes: converting the electronic form table based on the orientation of the data arrangement.

[0111] In some implementations, converting the electronic form table based on the orientation of the data arrangement also includes: in response to the orientation of the data arrangement being a cross orientation of rows and columns and at least one header area including a row-oriented header area and a column-oriented header area, selecting a first hierarchical level from the hierarchical structure of the column-oriented header area and selecting a second hierarchical level from the hierarchical structure of the row-oriented header area; and performing column-to-row conversion on the electronic form table with respect to the first hierarchical level and the second hierarchical level to obtain a relational table.

[0112] In some implementations, converting a spreadsheet table includes: in response to a header area in at least one header area including a data item corresponding to the following aggregate name, converting the spreadsheet table to generate a relational table including multiple data records, the aggregate name being used to describe the following data items in a row or column of the data area, the data item being calculated from data items in at least one other row or column of the data area, each data record in the multiple data records including one of the following data items: the data item in the row or column of the data area, and at least one data item in the header area for indexing the data item in the data area.

[0113] In some implementations, converting the spreadsheet table includes: presenting a hierarchical structure to a user; in response to receiving a selection by the user of at least one portion of a hierarchical level of the hierarchical structure, determining at least one column or row in at least one header area corresponding to the at least one portion of the hierarchical level; and converting the spreadsheet table with respect to the determined at least one column or row to construct a relational table.

[0114] In some implementations, converting a spreadsheet table includes: in response to at least one header area including a data item corresponding to at least one of an index set name, a value name, or an aggregate name, constructing a header record of a relational table, the header record of the relational table including a data field indicating at least one of an index set name, a value name, or an aggregate name, wherein the index set name describes a set of indexes used to index data items in a row or column of the data area, the value name is used to describe the data items in the row or column of the data area, and the aggregate name is used to describe the following data items in the row or column of the data area, the data items are calculated from data items in at least one other row or column of the data area.

[0115] In some implementations, the plurality of data records include a first data field and a second data field, the first data field containing a data item copied from a cell in a corresponding row or column of at least one header area, the second data field containing a data item that references a data item in a cell of the data area, the data item in the second data field changing as the data item in the cell of the data area changes.

[0116] Exemplary Implementation

[0117] Some example implementations of the present disclosure are listed below.

[0118] In a first aspect, the present disclosure provides a computer-implemented method. The method includes: detecting at least one header region and a data region of a spreadsheet, the spreadsheet including data items populated in cells arranged in rows and columns, the at least one header region including cells populated with data items for indexing or describing the data items in the cells of the data region; determining a hierarchical structure of the data items in the at least one header region by detecting at least one of the following: cell merging in the at least one header region, an indentation level of a cell in the at least one header region, or a functional relationship defined in a cell with respect to at least one other cell in the data region; and converting the spreadsheet into at least one relational table based on the determined hierarchical structure, the at least one relational table having the data items of the spreadsheet arranged in a unified structure.

[0119] In some implementations, determining the hierarchical structure of at least one header region further includes determining the hierarchical structure based on semantic analysis of data items in the header region.

[0120] In some implementations, detecting at least one header area of the spreadsheet table includes: detecting at least one of the following features in the spreadsheet table: the presence of at least one blank value in a row or column of the spreadsheet table, a data type in a cell in a row or column of the spreadsheet table, a relative position of a row or column of the spreadsheet table, a data conversion in a row or column of the spreadsheet table, or a distribution of values in a row or column of the spreadsheet table; and determining at least one header area in the spreadsheet table based on the at least one detected feature.

[0121] In some implementations, the method further includes: determining corresponding semantic categories for data items in at least one header area, the corresponding semantic categories being selected from a group including: indices for indexing data items in rows or columns of the data area, index set names, sets for describing indexes for indexing data items in rows or columns of the data area, value names for describing data items in rows or columns of the data area, aggregate names for describing data items in rows or columns of the data area, the data items being calculated from data items in at least one other row or column of the data area; and wherein converting the spreadsheet table includes: also converting the spreadsheet table based on the determined semantic categories of the data items.

[0122] In some implementations, the hierarchical structure is represented as a tree structure, the tree structure including at least one set of nodes linked together in a parent-child relationship, each of the nodes representing a data item in at least one header area; and determining the corresponding semantic category includes: for a given set of at least one set of nodes, determining the corresponding semantic matching level between the tree structure and a plurality of predetermined semantic patterns, each of the plurality of predetermined semantic patterns indicating a different combination of semantic categories for the corresponding nodes in the parent-child relationship; in response to one of the determined semantic matching levels satisfying a predetermined condition, determining the semantic category indicated by the corresponding semantic pattern of the data item represented by the given set of nodes.

[0123] In some implementations, determining the hierarchical structure includes: for each header region in at least one header region, in response to detecting the occurrence of a cell merge in the header region, detecting whether a first merged cell in a row or column of the header region extends across multiple second cells in a subsequent row or subsequent column of the header region; and at least partially based on detecting that the first merged cell extends across multiple second cells, determining a first node at a first hierarchical level in the hierarchical structure to represent a data item in the first merged cell, and determining multiple second nodes at a second hierarchical level in the hierarchical structure to represent data items in the multiple second cells, the second hierarchical level being lower than the first hierarchical level.

[0124] In some implementations, determining the hierarchical structure includes: in response to determining that one of the at least one header region is a column-oriented header region, performing at least one of the following: determining a third node at a third hierarchical level in the hierarchical structure to represent a data item in the third cell, and determining a fourth node at a fourth hierarchical level in the hierarchical structure to represent a data item in the fourth cell, based at least in part on detecting that the indentation level of the third cell in the header region is lower than the indentation level of the fourth cell in the header region; and / or determining a corresponding node at a fifth hierarchical level in the hierarchical structure to represent the data items in the multiple fifth cells, based at least in part on detecting that the multiple fifth cells in the column of the header region have the same indentation level.

[0125] In some implementations, determining the hierarchical structure includes: for each header area of at least one header area, determining a sixth node at a sixth hierarchical level in the hierarchical structure to represent a data item in the sixth cell in the header area based at least in part on detecting a functional relationship defined in a first cell in the data area with respect to at least one second cell, and determining at least one seventh node at a seventh hierarchical level in the hierarchical structure to represent a data item in a seventh cell in the header area, the difference between the sixth hierarchical level and the seventh hierarchical level being based on a type of functional relationship, wherein the data item in the sixth cell indexes or describes the data item in the first cell in the data area, and the data item in the seventh cell indexes or describes the data item in at least one second cell in the data area.

[0126] In some implementations, determining the hierarchical structure includes: for each header area in at least one header area, determining an original hierarchical structure of the header area based on detection; presenting the original hierarchical structure to a user; in response to receiving a modification to the original hierarchical structure from the user, modifying the original hierarchical structure based on the modification to generate a hierarchical structure; and in response to receiving confirmation of the original hierarchical structure from the user, determining the original hierarchical structure as a hierarchical structure.

[0127] In some implementations, the method further includes: determining the orientation of data arrangement in the electronic form table based on at least one of the following: detecting consistency in the representation format of data items in the row direction and / or column direction of the data area, detecting consistency in the data type in the row direction and / or column direction of the data area, the number of blank cells in at least one header area in the row direction and / or column direction of at least one header area exceeds a threshold number, or determining that the length of data in the row direction or column direction of the data area is higher than a predetermined length threshold, and wherein the orientation of the data arrangement is one of the following: column-first orientation, row-first orientation, or a cross orientation of rows and columns, and wherein converting the electronic form table further includes: converting the electronic form table based on the orientation of the data arrangement.

[0128] In some implementations, converting the electronic form table based on the orientation of the data arrangement also includes: in response to the orientation of the data arrangement being a cross orientation of rows and columns and at least one header area including a row-oriented header area and a column-oriented header area, selecting a first hierarchical level from the hierarchical structure of the column-oriented header area and selecting a second hierarchical level from the hierarchical structure of the row-oriented header area; and performing column-to-row conversion on the electronic form table with respect to the first hierarchical level and the second hierarchical level to obtain a relational table.

[0129] In some implementations, converting a spreadsheet table includes: in response to a header area in at least one header area including a data item corresponding to the following aggregate name, converting the spreadsheet table to generate a relational table including multiple data records, the aggregate name being used to describe the following data items in a row or column of the data area, the data item being calculated from data items in at least one other row or column of the data area, each data record in the multiple data records including one of the following data items: the data item in the row or column of the data area, and at least one data item in the header area for indexing the data item in the data area.

[0130] In some implementations, converting the spreadsheet table includes: presenting a hierarchical structure to a user; in response to receiving a selection by the user of at least one portion of a hierarchical level of the hierarchical structure, determining at least one column or row in at least one header area corresponding to the at least one portion of the hierarchical level; and converting the spreadsheet table with respect to the determined at least one column or row to construct a relational table.

[0131] In some implementations, converting a spreadsheet table includes: in response to at least one header area including a data item corresponding to at least one of an index set name, a value name, or an aggregate name, constructing a header record of a relational table, the header record of the relational table including a data field indicating at least one of an index set name, a value name, or an aggregate name, wherein the index set name describes a set of indexes used to index data items in a row or column of the data area, the value name is used to describe the data items in the row or column of the data area, and the aggregate name is used to describe the following data items in the row or column of the data area, the data items are calculated from data items in at least one other row or column of the data area.

[0132] In some implementations, the plurality of data records include a first data field and a second data field, the first data field containing a data item copied from a cell in a corresponding row or column of at least one header area, the second data field containing a data item that references a data item in a cell of the data area, the data item in the second data field changing as the data item in the cell of the data area changes.

[0133] In a second aspect, the present disclosure provides an electronic device, comprising: a processing unit; and a memory coupled to the processing unit and having instructions stored therein, wherein the instructions, when executed by the processing unit, cause the electronic device to perform any one of the steps according to the above method.

[0134] In a third aspect, the present disclosure provides a computer program product tangibly stored on a computer storage medium, the computer program product comprising machine executable instructions which, when executed by a device, cause the device to perform the method according to the first aspect. The computer storage medium may be a non-transitory computer storage medium.

[0135] In a fourth aspect, the present disclosure provides a non-transitory computer storage medium having machine-executable instructions stored thereon, which, when executed by a device, cause the device to perform the method according to the first aspect.

[0136] The functions described above herein may be performed, at least in part, by one or more hardware logic components. Examples of illustrative types of hardware logic components that may be used include, but are not limited to, field programmable gate arrays (FPGAs), application specific integrated circuits (ASICs), application specific standard products (ASSPs), systems on chips (SOCs), complex programmable logic devices (CPLDs), and the like.

[0137] The program code for executing the method of the present disclosure can be written in any combination of one or more programming languages. The program code can be provided to a processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing device so that when the program code is executed by the processor or controller, the functions / operations specified in the flow chart and / or block diagram are implemented. The program code can be executed in whole or in part on the machine, partially on the machine as a stand-alone software package, partially on a remote machine, or entirely on a remote machine or server.

[0138] In the context of the present disclosure, a machine-readable medium may be any tangible medium that may contain or store a program for use by or in conjunction with an instruction execution system, apparatus, or device. A machine-readable medium may be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium may include, but is not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any suitable combination of the foregoing. More specific examples of machine-readable storage media would include an electrical connection having one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0139] In addition, although each operation is described in a particular order, this should not be understood as requiring the specific order shown or to perform these operations in sequence, or to perform all shown operations to achieve the desired result. In some cases, multitasking and parallel processing may be advantageous. Similarly, although several specific implementation details have been included in the above discussion, these details should not be interpreted as limiting the scope of this disclosure, but should be interpreted as describing features that may be specific to a particular implementation. Certain features described in the context of a separate implementation may also be combined in a single implementation. On the contrary, the various features described in a single implementation may also be implemented individually or in any suitable subcombination in multiple implementations.

[0140] Although the subject matter has been described in language specific to structural features and / or methodological acts, it should be understood that the subject matter specified in the appended claims is not necessarily limited to the specific features or acts described above. Rather, the specific features and acts described above are merely example forms of implementing the claims.

Claims

1. A method comprising: detecting at least one header area and a data area of an electronic form table, the electronic form table including data items populated in cells arranged in rows and columns, the at least one header area including cells populated with data items for indexing or describing data items in the cells of the data area; determining an original hierarchical structure based on the data items in the at least one header area; causing the original hierarchical structure to be presented on a user interface; receiving, via the user interface, user modifications to the original hierarchical structure; In response to receiving the user modification, modifying the original hierarchical structure based on the modification to generate a hierarchical structure; as well as Based on the hierarchical structure, the spreadsheet table is converted into at least one relational table having the data items of the spreadsheet table arranged in a unified structure.

2. The method according to claim 1, wherein for each of the at least one header area, determining the original hierarchical structure comprises: In response to detecting the occurrence of cell merging in the header region, detecting whether a first merged cell in a row or column of the header region extends over a plurality of second cells in a subsequent row or subsequent column of the header region; as well as At least in part based on detecting that the first merged unit extends across the plurality of second units, determining a first node at a first hierarchical level in the original hierarchical structure to represent the data items in the first merged unit, and determining a plurality of second nodes at a second hierarchical level in the original hierarchical structure to represent the data items in the plurality of second units, the second hierarchical level being lower than the first hierarchical level.

3. The method according to claim 1 , wherein determining the original hierarchical structure comprises: In response to determining that one of the at least one header region is a column-oriented header region, at least in part based on detecting that the indentation level of a first cell in the header region is lower than the indentation level of a second cell in the header region, a first node at a first hierarchical level in the original hierarchical structure is determined to represent the data item in the first cell, and a second node at a second hierarchical level in the original hierarchical structure is determined to represent the data item in the second cell. 4 . The method according to claim 1 , wherein determining the original hierarchical structure comprises determining the original hierarchical structure based on semantic analysis of the data items in the at least one header area.

5. The method according to claim 1, further comprising: Receiving, via the user interface, a user selection of a hierarchical level or at least one node in the hierarchical level of the hierarchical structure for conversion, wherein conversion of the spreadsheet form into at least one relational table having the data items of the spreadsheet form associated with the selected hierarchical level or at least one node in the hierarchical level arranged in a unified structure is based on the user selection.

6. The method of claim 5, wherein converting the spreadsheet form comprises: determining at least one column or row corresponding to the selected hierarchy level or at least one node in the hierarchy level in the at least one header region; as well as The spreadsheet is transformed with respect to the determined at least one column or row to construct the at least one relational table.

7. The method according to claim 1, wherein detecting the at least one header area of the electronic form table comprises: At least one of the following features is detected in the electronic form: the presence of at least one blank value in a row or column of said electronic spreadsheet, the data type of the row or column cells of the spreadsheet table, The relative position of the rows or columns of the electronic spreadsheet, conversion of data in rows or columns of the spreadsheet, or The distribution of values in the rows or columns of the electronic spreadsheet; and The at least one header area in the electronic form table is determined based on the at least one detected feature.

8. The method according to claim 1, further comprising: determining an orientation of data arrangement in the electronic form table, wherein the orientation of data arrangement is one of the following: a column-first orientation, a row-first orientation, or an orientation of rows and columns interleaved; and The converting of the spreadsheet further comprises converting the spreadsheet based on the orientation of the data arrangement.

9. The method of claim 8, wherein converting the electronic form table based on the orientation of the data arrangement comprises: In response to the orientation of the data arrangement being a cross orientation of rows and columns and the at least one header area including a row-oriented header area and a column-oriented header area, selecting a first hierarchical level from the hierarchical structure of the column-oriented header area, and selecting a second hierarchical level from the hierarchical structure of the row-oriented header area; as well as The electronic form table is subjected to column-to-row rotation with respect to the first hierarchical level and the second hierarchical level to obtain a relational table.

10. The method of claim 8, wherein determining the orientation of the data arrangement in the electronic form table is based on at least one of the following: Detecting consistency in the representation format of data items in the row direction or column direction of the data region, Detecting consistency of data types in the row direction or the column direction of the data region, The number of blank cells in the row direction or the column direction of the at least one header region exceeds a threshold number, or It is determined that the length of the data in the row direction or the column direction of the data region is greater than a predetermined length threshold.

11. A system comprising: one or more processors; as well as a memory storing instructions that, when executed by the one or more processors, cause the one or more processors to perform operations comprising: detecting at least one header area and a data area of an electronic form table, wherein the electronic form table includes data items filled in cells arranged in rows and columns, and the at least one header area includes cells filled with data items for indexing or describing the data items in the cells of the data area; determining an original hierarchical structure based on the data items in the at least one header area; causing the original hierarchical structure to be presented on a user interface; receiving, via the user interface, user modifications to the original hierarchical structure; In response to receiving the user modification, modifying the original hierarchical structure based on the modification to generate a hierarchical structure; and Based on the hierarchical structure, the spreadsheet table is converted into at least one relational table having the data items of the spreadsheet table arranged in a unified structure.

12. The system according to claim 11, wherein for each of the at least one header area, determining the original hierarchical structure comprises: In response to detecting the occurrence of cell merging in the header region, detecting whether a first merged cell in a row or column of the header region extends over a plurality of second cells in a subsequent row or subsequent column of the header region; as well as At least in part based on detecting that the first merged unit extends across the plurality of second units, determining a first node at a first hierarchical level in the original hierarchical structure to represent the data items in the first merged unit, and determining a plurality of second nodes at a second hierarchical level in the original hierarchical structure to represent the data items in the plurality of second units, the second hierarchical level being lower than the first hierarchical level.

13. The system of claim 11, wherein determining the original hierarchical structure comprises: In response to determining that one of the at least one header region is a column-oriented header region, at least in part based on detecting that the indentation level of a first cell in the header region is lower than the indentation level of a second cell in the header region, a first node at a first hierarchical level in the original hierarchical structure is determined to represent the data item in the first cell, and a second node at a second hierarchical level in the original hierarchical structure is determined to represent the data item in the second cell. 14 . The system according to claim 11 , wherein determining the original hierarchical structure comprises determining the original hierarchical structure based on semantic analysis of the data items in the at least one header area.

15. The system of claim 11, wherein the operations further comprise: Receiving, via the user interface, a user selection of a hierarchical level or at least one node in the hierarchical level of the hierarchical structure for conversion, wherein conversion of the spreadsheet form into at least one relational table having the data items of the spreadsheet form associated with the selected hierarchical level or at least one node in the hierarchical level arranged in a unified structure is based on the user selection.

16. The system of claim 15, wherein converting the spreadsheet form comprises: determining at least one column or row corresponding to the selected hierarchy level or at least one node in the hierarchy level in the at least one header region; as well as The spreadsheet is transformed with respect to the determined at least one column or row to construct the at least one relational table.

17. The system of claim 11, wherein detecting the at least one header area of the electronic form table comprises: At least one of the following features is detected in the electronic form: the presence of at least one blank value in a row or column of said electronic spreadsheet, the data type of the row or column cells of the spreadsheet table, The relative position of the rows or columns of the electronic spreadsheet, conversion of data in rows or columns of the spreadsheet, or The distribution of values in the rows or columns of the electronic spreadsheet; and The at least one header area in the electronic form table is determined based on the at least one detected feature.

18. The system of claim 11, wherein the operations further comprise: determining an orientation of data arrangement in the electronic form table, wherein the orientation of data arrangement is one of the following: a column-first orientation, a row-first orientation, or an orientation of rows and columns interleaved; and The converting of the spreadsheet further comprises converting the spreadsheet based on the orientation of the data arrangement.

19. The system of claim 18, wherein converting the electronic form table based on the orientation of data arrangement comprises: In response to the orientation of the data arrangement being a cross orientation of rows and columns and the at least one header area including a row-oriented header area and a column-oriented header area, selecting a first hierarchical level from the hierarchical structure of the column-oriented header area, and selecting a second hierarchical level from the hierarchical structure of the row-oriented header area; as well as The electronic form table is subjected to column-to-row rotation with respect to the first hierarchical level and the second hierarchical level to obtain a relational table.

20. A non-transitory storage medium comprising instructions that, when executed by one or more hardware processors of a machine, cause the machine to perform operations comprising: detecting at least one header area and a data area of an electronic form table, the electronic form table including data items populated in cells arranged in rows and columns, the at least one header area including cells populated with data items for indexing or describing data items in the cells of the data area; determining an original hierarchical structure based on the data items in the at least one header area; causing the original hierarchical structure to be presented on a user interface; receiving, via the user interface, user modifications to the original hierarchical structure; In response to receiving the user modification, modifying the original hierarchical structure based on the modification to generate a hierarchical structure; as well as Based on the hierarchical structure, the spreadsheet table is converted into at least one relational table having the data items of the spreadsheet table arranged in a unified structure.

Images