Data visualization model construction method based on multiple excel sub-tables

Abstract

The present application relates to the technical field of low-code platform, and particularly relates to a data visualization model construction method based on multiple Excel sub-tables, comprising the following steps: S1, receiving an uploaded Excel data table and reading an Excel sub-table of the Excel data table; S2, based on the read Excel sub-table, obtaining table header fields of each Excel sub-table; S3, based on the obtained table header fields of each Excel sub-table, respectively generating a data visualization model corresponding to each Excel sub-table; S4, extracting data in data cells in each Excel sub-table as sample data according to a preset extraction requirement; analyzing whether there is a calculation formula in the sample data of each Excel sub-table, if there is a calculation formula, analyzing whether the data used by the calculation formula all belong to the Excel sub-table to which the calculation formula belongs, if not all belong to the Excel sub-table to which the calculation formula belongs, then parsing the calculation formula. With the present application, industrial enterprise users can also realize visual data management work through a low-code platform.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of low-code platform, and in particular to a data visualization model construction method based on multiple Excel sub-tables. BACKGROUND

[0002] Low code is a visual application development method that delivers applications with less code and faster speed. It automates the code that programmers do not want to develop, which is called low code. Low code is a set of digital technology tool platforms that realize rapid construction, data arrangement, connection ecology, and middle platform services based on graphical drag-and-drop and parameterized configuration, etc. Through a small amount of code or no code, scene application innovation in digital transformation is realized.

[0003] With the development of low code technology, data visualization application has become a development trend of low code development platform. In the industrial field, the informatization degree of industrial enterprises is relatively low, and enterprise personnel generally use Excel data tables for data management. In the prior art, when using low code data visualization application, a data visualization model needs to be manually created according to the existing Excel data format. However, such an operation mode has the following problems:

[0004] First, the prior art manually creates a data visualization model according to the existing Excel data format, but users in the industrial field have a shallow understanding of concepts such as data visualization model creation and Json data format, and the operation has a certain degree of difficulty.

[0005] Second, for a data source of multiple sub-tables in an Excel, many low code platforms cannot identify multiple Excel sub-tables, and users need to process them through related technical means of data visualization model.

[0006] Therefore, for general users in the industrial field, it is difficult to create an associated data visualization model in the process of configuring a data source using a low code platform, and the technical threshold required for using a low code development data visualization application is also relatively high.

[0007] In summary, how to enable industrial enterprise users without background related to data visualization model to also achieve visual data management work through a low code platform is a problem to be solved in the industrial field when using low code technology. SUMMARY

[0008] In view of the above problems of the prior art, the present application provides a data visualization model construction method based on multiple Excel sub-tables, which enables industrial enterprise users without background related to data visualization model to also achieve visual data management work through a low code platform.

[0009] To solve the above technical problems, the present application adopts the following technical solutions:

[0010] The data visualization model construction method based on multiple Excel sub-tables comprises the following steps:

[0011] S1, receiving an uploaded Excel data table and reading Excel sub-tables of the Excel data table;

[0012] S2, based on the read Excel sub-tables, obtaining the table header fields of each Excel sub-table;

[0013] S3, based on the obtained table header fields of each Excel sub-table, generating a data visualization model corresponding to each Excel sub-table respectively;

[0014] S4, according to the preset extraction requirements, extracting data in the data cells in each Excel sub-table as sample data; analyzing whether there is a calculation formula in the sample data of each Excel sub-table, if there is a calculation formula, analyzing whether the data used by the calculation formula all belong to the Excel sub-table to which the calculation formula belongs, if not all belong to the Excel sub-table to which the calculation formula belongs, parsing the calculation formula, and based on the parsing result, associating the data visualization models corresponding to each Excel sub-table to which the data used by the calculation formula belongs, to generate an associated data visualization model;

[0015] S5, based on the created data visualization model and the associated data visualization model, performing API encapsulation.

[0016] Preferably, in S1, after reading the Excel sub-tables of the Excel data table, it is further verified whether the data of each Excel sub-table is empty, if not empty, the corresponding Excel sub-table is taken as a to-be-processed Excel sub-table; in S2, the table header fields of each to-be-processed Excel sub-table are obtained.

[0017] Preferably, in S2, the obtaining of the table header fields of each to-be-processed Excel sub-table comprises:

[0018] S21, obtaining the number X of non-empty data columns in an Excel sub-table;

[0019] S22, according to the preset requirements, verifying the table header of the Excel sub-table, and extracting the table header field that passes the verification.

[0020] Preferably, S22 comprises:

[0021] S221, extracting the data field of the first row that has not been subjected to table header verification, and analyzing the number Y of fields in the row;

[0022] S222, judge whether Y is equal to X, if not, return to S221, if yes, go to S223;

[0023] S223, analyze the number n of the row in the corresponding Excel sub-table, and extract the data fields of the n+1th to the n+mth rows of the Excel sub-table;

[0024] S224, judge whether the data types of the corresponding cells in each row of the data fields of the n-th to the n+m-th rows are the same, if not, return to S221, if yes, take the data field of the n-th row as the table header field of the Excel sub-table.

[0025] Preferably, the value of m is greater than or equal to 2.

[0026] Preferably, in S4, if all the data in an Excel sub-table does not belong to the used data in the calculation formula of any other Excel sub-table, the data visualization model corresponding to the Excel sub-table is packaged separately.

[0027] Preferably, in S4, if there is a calculation formula in the separately packaged data visualization model corresponding to the Excel sub-table, the calculation formula is analyzed, and then the data visualization model corresponding to the Excel sub-table is packaged.

[0028] Preferably, in S5, after completing the API packaging, the API link for calling the Excel table upload and the data visualization model is also published.

[0029] Compared with the prior art, the present application has the following beneficial effects:

[0030] 1. Using the method, the industrial enterprise user only needs to upload the Excel table, and then the background will automatically create a data visualization model according to the table header of the Excel sub-table, and will also generate a related data visualization model through the sample data in the multiple sub-tables. Excel formula, and API packaging. In this way, when the user performs data management, the user can directly import the data filled in the Excel into the low-code platform, and the platform will automatically perform corresponding data filling work according to the data visualization model and the related data visualization model (i.e. the data visualization model association relationship). Industrial users without data visualization model background can also perform visual data management work.

[0031] In summary, through the method, industrial enterprise users without data visualization model background can also realize visual data management work through the low-code platform.

[0032] 2、The application can not only generate corresponding data visualization model based on a single Excel table, but also automatically identify and process the special case by establishing a corresponding associated data visualization model when there are multiple Excel sub-tables in the Excel table and the data used in the calculation formula in the Excel sub-table belongs to more than one Excel sub-table, thereby ensuring the effectiveness and accuracy of data management when used by users.

[0033] 3、Since the data in the data visualization model needs to strictly correspond to the Excel sub-table, in order to ensure the effectiveness of the data visualization model, the application will verify the table header of the Excel sub-table according to the preset requirements. Specifically, by judging the number of fields, it can be understood whether the number of fields of the tested data row is the same as the table header, and by testing the contents of the corresponding cells, it can be understood whether the data rows below the tested data row are filled according to the data type requirements of the data row. Moreover, since the testing method starts from the first row which has not been tested for the table header, it can be ensured that the table header of the Excel sub-table can be accurately identified.

[0034] 4、The value of m is greater than or equal to 2, which can further ensure the accuracy of identification of the table header of the Excel sub-table.

[0035] 5、If all the data in an Excel sub-table does not belong to the data used in the calculation formula of any other Excel sub-table, the application will separately encapsulate the data visualization model corresponding to the Excel sub-table, and when there is a calculation formula in the Excel sub-table, the application will first analyze the calculation formula and then encapsulate the data visualization model corresponding to the Excel sub-table. In this way, it is convenient for subsequent direct use of the corresponding data visualization model, so that users can perform data visualization management operations without performing operations other than uploading the Excel table.

[0036] 6、After completing the API encapsulation, the API link for Excel table uploading and data visualization model calling will be published. When users perform subsequent operations, they only need to find the corresponding connection (such as a button or a data entry bar with a connection) on the platform to perform the operation, which is simple and has strong guidance. BRIEF DESCRIPTION OF DRAWINGS

[0037] In order to make the purpose, technical scheme and advantages of the application clearer, the application will be further described in detail below in combination with the drawings, in which:

[0038] Figure 1 is a flowchart in the embodiment. DETAILED DESCRIPTION

[0039] The application will be further described in detail through specific embodiments.

[0040] Example:

[0041] like Figure 1 As shown in the figure, this embodiment discloses a method for constructing a data visualization model based on multiple Excel sub-tables, including the following steps:

[0042] S1. Receive the uploaded Excel data table and read the Excel sub-tables of the Excel data table; in specific implementation, after reading the Excel sub-tables of the Excel data table, it also checks whether the data in each Excel sub-table is empty. If it is not empty, the corresponding Excel sub-table is used as the Excel sub-table to be processed.

[0043] S2. Based on the Excel sub-tables to be processed, obtain the header fields of each Excel sub-table.

[0044] The steps to obtain the header fields of each Excel sub-table to be processed include:

[0045] S21. Get the number of non-empty data columns X in an Excel sub-table;

[0046] S22. According to the preset requirements, validate the header of the Excel sub-table and extract the header fields that pass the validation.

[0047] S22 includes:

[0048] S221. Extract the data fields of the first row that has not yet undergone header validation, and analyze the number of fields Y in that row;

[0049] S222. Determine if Y is equal to X. If not, return to S221. If yes, go to S223.

[0050] S223. Analyze the row number n of the corresponding Excel sub-table and extract the data fields from row n+1 to row n+m of the Excel sub-table; m is greater than or equal to 2, and the value of m in this embodiment is 2.

[0051] S224. Determine whether the data types of the corresponding cells in each row from row n to row n+m are all the same. If not, return S221. If so, use the data field of row n as the header field of this Excel sub-table.

[0052] Since the data in the data visualization model needs to strictly correspond to the Excel sub-table, in order to ensure the effectiveness of the data visualization model, the application will check the table header of the Excel sub-table according to the preset requirements. Specifically, by judging the number of fields, it can be known whether the number of fields of the data line being tested is the same as the table header, and by testing the contents of the corresponding cells, it can be known whether the data lines below the data line being tested are filled in accordance with the data type requirements of the data line. Moreover, since the testing starts from the first row which has not been tested for the table header, it can be ensured that the table header of the Excel sub-table can be accurately identified.

[0053] S3、Based on the table header fields of each Excel sub-table obtained, the data visualization model corresponding to each Excel sub-table is generated respectively;

[0054] S4, according to the preset extraction requirements, the data in the data cells in each Excel sub-table is extracted as sample data. The specific extraction requirements can be designed by the person skilled in the art according to the industry adaptability. Specifically, it only needs to ensure that all non-empty data columns of the Excel sub-table are extracted. Analyze whether there is a calculation formula in the sample data of each Excel sub-table. If there is a calculation formula, analyze whether the data used by the calculation formula all belong to the Excel sub-table to which the calculation formula belongs. If not, parse the calculation formula, and based on the parsing result, associate the data visualization model corresponding to each Excel sub-table to which the data used by the calculation formula belongs, and generate an associated data visualization model;

[0055] If all the data in an Excel sub-table does not belong to the data used in the calculation formula of any other Excel sub-table, the data visualization model corresponding to the Excel sub-table is packaged separately. If there is a calculation formula in the Excel sub-table corresponding to the separately packaged data visualization model, the calculation formula is parsed, and then the data visualization model corresponding to the Excel sub-table is packaged. In this way, it is convenient for subsequent direct use of the corresponding data visualization model, so that the user can perform data visualization management operations without performing operations other than uploading the Excel table.

[0056] S5, based on the created data visualization model and the associated data visualization model, API packaging is performed. After completing the API packaging, the API link for uploading the Excel table and calling the data visualization model is also published. When the user performs subsequent operations, the user only needs to find the corresponding connection (such as a button or a data entry bar with a connection) on the platform to perform the operation. The operation is simple and has strong guidance.

[0057] Using this method, industrial users only need to upload an Excel spreadsheet. The backend will then automatically create a data visualization model based on the headers of the Excel sub-spreadsheets. It will also generate a related data visualization model using sample data from multiple sub-spreadsheets and preset Excel formulas, and encapsulate this information into an API. Users can then directly import the populated data from the Excel spreadsheet into the low-code platform, which will automatically populate the data based on the data visualization model and related data visualization models (i.e., the relationships between the data visualization models). Industrial users do not need prior knowledge of data visualization models to perform data visualization management. Furthermore, this invention can not only generate corresponding data visualization models based on a single Excel spreadsheet, but also automatically identify and handle special cases where the Excel spreadsheet contains multiple sub-spreadsheets, and the data used in the calculation formulas in those sub-spreadsheets belongs to more than one sub-spreadsheet. This ensures the effectiveness and accuracy of data management for users.

[0058] To facilitate understanding of this method, an example of an automotive parts manufacturing company will be used for illustration.

[0059] After the company's staff uploads an Excel spreadsheet, the platform retrieves the number of sub-spreadsheets and performs data validation to check if any of them are empty. If a sub-spreadsheet is not empty, its status is marked as `state=pending` to indicate it is pending processing, and an empty data visualization model is created. In this specific example, assume the uploaded Excel spreadsheet is a production plan, including the production plans for XX Automotive Parts Workshop 1 and XX Automotive Parts Workshop 2, as well as one empty Excel spreadsheet. Upon receiving the production plan, the platform places the sub-spreadsheets into a pending processing queue based on their order and names.

[0060] Afterwards, the platform performs header data validation based on the obtained Excel sub-table; it obtains the Excel sub-table 1 to be processed and performs non-empty data column validation; for example, for the production plan of the first workshop of XX Auto Parts, data column X=8, the created empty data visualization model automatically creates 8 attribute bits.

[0061] Once the attribute bits are created, perform header validation; determine if the number of fields Y in row Ln is equal to the number of data columns X. If Ln.value.length != X, then Ln is not a header; if Ln.value.length = X, then get the value of n at this time. Assuming n=5, it means that the corresponding Excel sub-table is row L5.

[0062] Next, retrieve the data types of cells in row L5 and row L6 and perform format validation; L5.format() = L6.format();

[0063] Then, retrieve the data types of cells in row L6 and row L7 and perform format validation; L6.format() = L7.format();

[0064] In this example, it is assumed that three consecutive rows of data have the same format, and L5 is the data header. If the above conditions are not met, the header check will be performed again starting from row L6.

[0065] A data visualization model is automatically generated based on the retrieved table header fields; then

[0066] model1{

[0067] Oder: 1;

[0068] Tbname: "XX Automotive Parts Workshop 1 Production Plan"

[0069] Data:{

[0070] Att1: "Order Number"

[0071] Att2: "Delivery Date"

[0072] Att3: "Parts Code"

[0073] Att4: "Part Name"

[0074] Att5: "Quantity"

[0075] Att6: "unit"

[0076] Att7: "Installation method"

[0077] Att8: "Shipping Method"

[0078] }

[0079] Deal{

[0080] }

[0081] };

[0082] Construct model 2 using the same method: Production plan for the second workshop of XX Automotive Parts;

[0083] Based on the sample data, the system automatically analyzes the data relationships between sub-tables; it verifies whether there are calculation formulas in the data cells of each Excel sub-table. When the calculation formula does not penetrate the sub-table, it parses the cell formula and encapsulates it into a single data visualization model, model1 and model2.

[0084] When a calculation formula involves calculations that penetrate sub-tables, the cell formula is parsed, and the data visualization models of the two separate Excel sub-tables are linked by the formula.

[0085] Next, add a sub-table for the daily production plan of XX auto parts; construct model3: sub-table for the daily production plan of XX auto parts; sub-table3 is related to sub-tables1 and2, obtain the method and quantity P in each cell, and create P attribute bits for Deal;

[0086] Model3{

[0087] Oder: 3;

[0088] Tbname: "Daily Production Plan for XX Automotive Parts"

[0089] Data:{

[0090] Att1: "workshop"

[0091] Att2:"Date"

[0092] Att3: "Planned Production Quantity"

[0093] }

[0094] Deal{

[0095] Att1.fun:””

[0096] Att2.fun:””

[0097] Att3.fun: "SUMPRODUCT((Condition 1)*(Condition 2)*(Condition 3)*……*Summation range)"

[0098] }

[0099] };

[0100] In model3, the calculation method for Att3 is SUMPRODUCT((condition 1)*(condition 2)*(condition 3)*……*summation region). If the calculation method has a penetrating table, then the model association relationship is bound; and the associated data visualization model MODEL (model1, model2, model3) is generated.

[0101] Then, based on the created data visualization model and related data visualization model, it is packaged into an API to provide services.

[0102] Finally, it needs to be explained that the above examples are only used to illustrate the technical solutions of the present application but not to limit the technical solutions, and those of ordinary skill in the art should understand that the technical solutions of the present application are modified or equivalently replaced without departing from the purpose and scope of the technical solutions, which should be covered in the scope of claims of the present application.

Claims

1. A method for constructing a data visualization model based on multiple Excel sub-tables, characterized in that, The method comprises the following steps: S1, receiving an uploaded Excel data table and reading an Excel sub-table of the Excel data table; S2, obtaining table header fields of each Excel sub-table based on the read Excel sub-table; S3, generating a data visualization model corresponding to each Excel sub-table respectively based on the obtained table header fields of each Excel sub-table; S4, extracting data in data cells in each Excel sub-table as sample data according to a preset extraction requirement; analyzing whether there is a calculation formula in the sample data of each Excel sub-table, if there is a calculation formula, analyzing whether the data used by the calculation formula all belong to the Excel sub-table to which the calculation formula belongs, if not, parsing the calculation formula, and based on the parsing result, associating the data visualization models corresponding to each Excel sub-table to which the data used by the calculation formula belongs to generate an associated data visualization model; S5, performing API encapsulation based on the created data visualization model and the associated data visualization model; In S4, if all data in an Excel sub-table does not belong to the data used in the calculation formula of any other Excel sub-table, the data visualization model corresponding to the Excel sub-table is encapsulated separately; If there is a calculation formula in the Excel sub-table corresponding to the separately encapsulated data visualization model, the calculation formula is parsed, and then the data visualization model corresponding to the Excel sub-table is encapsulated.

2. The method of claim 1, wherein the method is based on a multi-Excel sub-table data visualization model construction method. In S1, after reading the Excel sub-table of the Excel data table, it is further verified whether the data of each Excel sub-table is empty, if not, the corresponding Excel sub-table is taken as a to-be-processed Excel sub-table; in S2, the table header fields of each to-be-processed Excel sub-table are obtained.

3. The method of claim 2, wherein the method is based on a multi-Excel sub-table data visualization model construction method. In S2, the table header fields of each to-be-processed Excel sub-table are obtained, which comprises: S21, obtaining the number X of non-empty data columns in an Excel sub-table; S22, according to a preset requirement, verifying the table header of the Excel sub-table, and extracting the table header field that passes the verification.

4. The method of claim 3, wherein the method is based on a multi-Excel sub-table data visualization model construction method. S22 comprises: S221, extracting the data field of the first row that has not been subjected to table header verification, and analyzing the number Y of fields in the row; S222, determining whether Y is equal to X, if not, returning to S221, if yes, going to S223; S223, analyzing the number n of rows in which the row is located in the corresponding Excel sub-table, and extracting the data fields of the n+1th to n+mth rows of the Excel sub-table; S224, determining whether the data types of the cells corresponding to each row in the data fields of the n-th to n+m-th rows are the same, if not, returning to S221, if yes, taking the data field of the n-th row as the table header field of the Excel sub-table.

5. The method of claim 4, wherein the method is based on a multi-Excel sub-table data visualization model construction method. The value of m is greater than or equal to 2.

6. The method of claim 1, wherein the method is based on a multi-Excel-subtable-based data visualization model construction method. In S5, after completing the API encapsulation, an API link for Excel table uploading and data visualization model calling is further published.

Images