Analysis and display method based on excel data table relation

Abstract

The invention discloses an analysis and display method based on excel data table relations. The method is characterized by comprising the following steps that S100, a server side obtains and configures an obtained excel data table; s200, the server side configures a field name table and a relation table; s300, the server side adopts a Java technology to extract data of the configured excel data table; s400, the server side finds out a corresponding relation in the relation table according to every two field names in the field name table and stores the corresponding relation in a temporary data table; s500, transmitting the data of the temporary data table from the server to the browser by adopting an http interface request mode, and performing data display on the browser end by adopting the echarts secondary packaged visual atlas relationship; s600, an object needing to be queried and a screening condition are selected, the screening condition serves as an interface parameter and is sent to the server, and the server side returns data of related nodes and relations of the object needing to be queried; and S700, adopting echarts to carry out secondary packaging on the relation graph component and displaying the relation graph component.

Description

Technical Field

[0001] The invention belongs to the field of data analysis and processing and graphical interface display, and relates to an analysis and display method based on Excel data table relationships. Background Art

[0002] With the development of society, economy and the Internet, social and economic activities will generate a large amount of data. The information contained in these big data is rich and diverse, covering various connections between people, people and enterprises, and enterprises and enterprises.

[0003] Among them, the prosperity of e-commerce and the popularity of the Internet of Things have made the data generated in economic activities more abundant and diverse. The transaction data generated in the process of online shopping, payment, and transactions can reflect information such as consumer purchasing habits, market demand, and price sensitivity; while the data collected by IoT devices can reveal information such as the use of items, the efficiency of the supply chain, and traffic flow. These data reveal the economic connections and cooperative relationships between people, such as the relationship between suppliers and manufacturers, and between manufacturers and sellers in the supply chain. However, how to analyze and display the various connections between people, people and enterprises, and enterprises and enterprises has become a hot technical issue at present.

[0004] In the prior art, open source data analysis and display methods have the following main disadvantages:

[0005] 1. Single object type: The display style and UI collocation lack creativity and cannot realize functions such as expansion;

[0006] 2. There is no clear application scenario and it cannot be used directly in various projects.

[0007] Therefore, the prior art lacks a method for analyzing and displaying various connections between people, people and enterprises, and enterprises and enterprises in these data. Summary of the invention

[0008] In view of the deficiencies in the prior art, the present invention realizes data analysis and display functions according to a method of analyzing data and displaying them in the form of a relationship map, and proposes an analysis and display method based on the relationship of an Excel data table, which includes the following steps:

[0009] S100: The server side obtains an Excel data sheet as source data, and configures the obtained Excel data sheet for analysis and display; the configured Excel data sheet includes multiple field names in the first row and at least one row of data corresponding to each field name in the first row;

[0010] S200: The server configures a field name table to store the names of the fields in the configured Excel data table; and configures a relationship table to store the relationship between the field names in the configured Excel data table, wherein the relationship includes multiple types.

[0011] S300: The server side uses Java technology to extract data from the configured Excel data table: According to the acquired Excel data table, Java technology is used to identify and extract data from the configured Excel data table, and the extracted data is stored in a field name table and a relationship table respectively;

[0012] S400: The server finds corresponding relationships in the relationship table according to the field names in the field name table, and stores the relationships in a temporary data table;

[0013] S500: The data in the temporary data table is transmitted from the server to the browser using the http interface request method. The browser uses the visualized graph relationship after secondary encapsulation by echarts to display the data.

[0014] S600: Select the object to be queried and the screening condition, send the screening condition as an interface parameter to the server, and the server returns the data of the relevant nodes and relationships of the object to be queried;

[0015] S700: Use echarts to repackage and display the relationship graph component: Add the functions of highlighting adjacent nodes, expanding and collapsing nodes. The nodes represent the corresponding field names in the field name table, and the lines between the nodes represent the corresponding relationships in the relationship table. The nodes are connected through lines and data is displayed.

[0016] Preferably, in the temporary data table, the corresponding relationship between the object name and the field name belonging to the object name is retained, and the corresponding relationship between the remaining field names belonging to the object name is deduplicated.

[0017] Preferably, the http interface request method used is POST.

[0018] The beneficial effects of the present invention are:

[0019] 1. Multiple objects: You can perform data analysis on multiple objects at the same time and determine whether they contain related data.

[0020] 2. Multiple types: Multiple data types can be displayed, for example, including people, organizations, communications, transactions, cars, and houses, and the relationships can be clearly displayed in different styles.

[0021] 3. Multiple conditions: Multiple analysis conditions screening, including self-selection of objects, selection of association modes (for example, associated institutions, associated real estate, associated vehicles), transaction relationships (for example, common transaction persons, common number of people, amount range), communication relationships (for example, common contacts, common number of people, number of times range), and the ability to customize the relationship and query time range.

[0022] 4. High flexibility: Graph relationships are visualized, objects can be dragged and dropped freely, and objects can be expanded to display data layer by layer in a hierarchical manner. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] Figure 1 is a flow chart of the present invention;

[0024] Figure 2 This is a screenshot of an Excel data sheet of a bank card bill used in an embodiment of the present invention;

[0025] Figure 3 This is a graphical interface display diagram of a bank card bill Excel data sheet in another embodiment of the present invention. DETAILED DESCRIPTION

[0026] The terms used in this application are as follows:

[0027] echarts: A JavaScript-based data visualization library that provides intuitive, vivid, interactive, and customizable data visualization charts.

[0028] The present invention is further described below in conjunction with the accompanying drawings and embodiments. This embodiment uses an Excel data table of a bank card bill as source data for illustration. Figure 1 As shown, the method of the present invention comprises the following steps:

[0029] S100: The server obtains the excel data sheet of the bank card bill as source data, and configures the obtained excel data sheet for analysis and display.

[0030] Figure 2 An excel data sheet of a bank card bill used in an embodiment of the present invention is shown. For the sake of convenience, while ensuring that the method provided by the present invention is clearly described, only some field names are used in the acquired excel data sheet for analysis and display.

[0031] like Figure 2 As shown, the configured bank card bill excel data sheet contains multiple field names in the first row, such as: query card number, transaction type, debit / credit sign, currency, transaction amount, transaction balance, transaction time, transaction serial number, transaction counterparty name, transaction counterparty account number and transaction counterparty ID number, etc.

[0032] It is worth noting that this implementation is only an example, and multiple field names such as the query card number, transaction counterparty name, transaction counterparty account number and transaction counterparty ID number are all virtual information.

[0033] In addition, it includes three rows of data corresponding to the field names in the first row, for example, 6237683810003440000 corresponds to the query card number in the first row, etc.

[0034] S200: The server configures a field name table to store the field names of the configured bank card bill excel data table, such as: query card number, transaction type, debit / credit sign, currency, transaction amount, transaction balance, transaction time, transaction serial number, transaction counterparty name, transaction counterparty account number and transaction counterparty ID number, etc.

[0035] Configure a relationship table to store the relationship between two field names in the configured bank card bill excel data table. The relationship includes multiple types. For example, the transaction counterparty ID number and the query card number are two field names, and the transaction type and transaction serial number are the relationship between the two field names. The relationship type is transaction type and transaction serial number.

[0036] S300: The server uses Java technology to extract data from the configured bank card bill excel data table: Based on the acquired bank card bill excel data table, Java technology is used to identify and extract data from the configured bank card bill excel data table, and the extracted data is stored in the field name table and the relationship table respectively. That is, all field names in the configured bank card bill excel data table are stored in the field name table, and all relationships in the configured bank card bill excel data table are stored in the relationship table. Among them, using Java technology to extract data from an excel data table is a well-known technology and will not be repeated here.

[0037] S400: The server side finds the corresponding relationship in the relationship table according to the field names in the field name table, and stores it in the temporary data table. Because the corresponding partial relationship of the field names of the same object is not very useful for analyzing and displaying various associations between people, people and enterprises, and enterprises, in the temporary data table, the corresponding partial relationship of the field names of the same object is deduplicated, and the corresponding relationship between the field names of the transaction party (i.e., object name) and the field name belonging to the party name (i.e., object name) is retained. For example, in the embodiment provided by the present invention, the relationship between the field names of the inquiry card number, transaction type, loan mark, currency, transaction amount, transaction balance, transaction time, etc. belonging to the party is deduplicated, and the relationship between the party name (i.e., object name) Wang Moumou and the inquiry card number of the party is retained, and the relationship is the cardholder.

[0038] Similarly, the relationship between the transaction counterparty account number and the transaction counterparty ID number is deduplicated, and the corresponding relationship between the transaction counterparty name (i.e., the other object name) and the field name belonging to the transaction counterparty name (i.e., the other object name) is retained. For example, the relationship between the transaction counterparty name (i.e., the other object name) Gao Moumou and the transaction counterparty account number is retained, and the relationship is the cardholder.

[0039] S500: The data in the temporary data table is transmitted from the server to the browser using the HTTP post interface request method. The browser uses the visualized graph relationship after secondary encapsulation by echarts to display the data.

[0040] S600: Select the object to be queried and the screening condition, send the screening condition as an interface parameter to the server, and the server returns the data of the relevant nodes and relationships of the object to be queried;

[0041] S700: Use echarts to repackage and display the relationship graph component: Add the functions of highlighting adjacent nodes, expanding and collapsing nodes. The nodes represent the corresponding field names in the field name table, and the lines between the nodes represent the corresponding relationships in the relationship table. The nodes are connected through lines and data is displayed.

[0042] Figure 3 FIG. 1 shows a graphical interface display diagram of a bank card bill Excel data sheet in another embodiment of the present invention. Figure 3 As shown in the figure, the object Wang is the party in the transaction, and the object Gao is the other party in the transaction. The two objects are connected through their respective account numbers / card numbers as nodes and the transaction amount between the nodes as the edge of the relationship. The object Wang is associated with the object Gao, realizing the analysis and display of the relationship between people in the Excel data.

[0043] Similarly, it is possible to analyze and display the various relationships between people and companies, as well as between companies, from Excel data.

[0044] Furthermore, data analysis can be performed on multiple objects at the same time to determine whether there is a relationship between the multiple objects.

[0045] It should be understood that this embodiment is only an analysis and display of a bank card bill Excel data sheet. In fact, the method provided by the present invention can be used for analysis and display of other scenarios, as long as the source data is an Excel data sheet. For example, for Excel data sheets such as call details and SMS sending and receiving records obtained from communication operators, various associations between objects can also be analyzed and displayed.

[0046] The method provided by the present invention solves the technical problem that there is no analysis and display method based on Excel data table relationship in the prior art.

[0047] It should be understood that the present invention is not limited to the above examples. For those skilled in the art, improvements or changes can be made based on the above description. All these improvements and changes should fall within the scope of protection of the claims attached to the present invention.

Claims

1. An analysis and display method based on Excel data table relationship, characterized in that The following steps are involved: S100: The server side obtains an Excel data sheet as source data, and configures the obtained Excel data sheet for analysis and display; The configured Excel data table contains multiple field names in the first row and at least one row of data corresponding to each field name in the first row; S200: The server configures a field name table to store the names of the fields in the configured Excel data table; and configures a relationship table to store the relationship between the field names in the configured Excel data table, wherein the relationship includes multiple types. S300: The server side uses Java technology to extract data from the configured Excel data table: According to the acquired Excel data table, Java technology is used to identify and extract data from the configured Excel data table, and the extracted data is stored in a field name table and a relationship table respectively; S400: The server finds corresponding relationships in the relationship table according to the field names in the field name table, and stores the relationships in a temporary data table; S500: The data in the temporary data table is transmitted from the server to the browser using the http interface request method. The browser uses the visualized graph relationship after secondary encapsulation by echarts to display the data. S600: Select the object to be queried and the screening condition, send the screening condition as an interface parameter to the server, and the server returns the data of the relevant nodes and relationships of the object to be queried; S700: Use echarts to repackage and display the relationship graph component: Add the functions of highlighting adjacent nodes, expanding and collapsing nodes. The nodes represent the corresponding field names in the field name table, and the lines between the nodes represent the corresponding relationships in the relationship table. The nodes are connected through lines and data is displayed.

2. The analysis and display method based on the relationship of Excel data tables according to claim 1 is characterized in that: In the temporary data table, the corresponding relationship between the object name and the field name belonging to the object name is retained, and the corresponding relationship between the remaining two field names belonging to the object name is deduplicated.

3. The analysis and display method based on the relationship of Excel data tables according to claim 1 is characterized in that: The http interface request method is POST.

Images