A method, device, equipment and storage medium for converting Excel data into JSON data

By converting Excel data into JSON data and utilizing the structured dataset and custom elements of the JSON file format, the problem of low efficiency in Excel data processing is solved, enabling convenient data flow and logical expression.

CN117669500BActive Publication Date: 2024-11-15CHINA MOBILE M2M +1
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202211012799.7
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2022-08-23
Publication Date
2024-11-15
Estimated Expiration
2042-08-23

AI Technical Summary

Technical Problem

In existing technologies, Excel data processing is inefficient and requires significant human and material resources for customized code development to achieve data format interoperability.

Method used

By obtaining data fields in JSON file format, including structured datasets, custom elements, and category items, the row data and cell data of the Excel data to be converted are obtained and written into the structured dataset and custom elements respectively, and then saved to the target category item, thus realizing the conversion of Excel data into JSON data.

Benefits of technology

It improves the convenience and logic of Excel data processing, reduces the investment of human and material resources, and realizes the convenient flow and logical expression of Excel data on the data processing platform.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN117669500B_ABST
    Figure CN117669500B_ABST
Patent Text Reader

Abstract

The present application provides a method, apparatus, device and storage medium for converting Excel data into JSON data, the method comprising: obtaining a data field in a JSON file format, the data field comprising a structured data set, a custom element and at least one classification item; obtaining Excel data to be converted, and obtaining row data of the Excel data to be converted and cell data outside the row data; writing the row data into the structured data set, and writing the cell data into the custom element; saving the structured data set with the row data written and the custom element with the cell data written into a target classification item, the at least one classification item comprising the target classification item.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present application relates to the fields of computer data processing and data integration, and in particular to a method, device, equipment and storage medium for converting Excel data into JSON data. Background Art

[0002] As the digital transformation process continues to accelerate, many new and old systems are not compatible and data formats are not interoperable. Excel is widely used as a convenient tool for data editing, and a large amount of data entry work still relies on Excel. Since Excel cannot be used as a database application, in order to enter Excel data into the system, it is necessary to develop customized Excel code for different formats, which consumes a lot of manpower and material resources and leads to low data processing efficiency. Summary of the invention

[0003] The embodiments of the present application provide a method, apparatus, device and storage medium for converting Excel data into JSON data to solve the problem of low computing efficiency in existing Excel data processing.

[0004] In order to solve the above technical problems, this application is implemented as follows:

[0005] In a first aspect, an embodiment of the present application provides a method for converting Excel data into JSON data, the method comprising:

[0006] Obtain a data field in a JSON file format, wherein the data field includes a structured data set, a custom element, and at least one classification item;

[0007] Acquire Excel data to be converted, and acquire row data of the Excel data to be converted and cell data outside the row data;

[0008] Writing the row data into the structured data set, and writing the cell data into the custom element;

[0009] The structured data set in which the row data is written and the custom element in which the cell data is written are saved in a target classification item, wherein the at least one classification item includes the target classification item.

[0010] In a second aspect, an embodiment of the present application further provides a device for converting Excel data into JSON data, the device comprising:

[0011] A first acquisition module is used to acquire data fields in a JSON file format, wherein the data fields include a structured data set, a custom element, and at least one classification item;

[0012] A second acquisition module is used to acquire Excel data to be converted, and acquire row data of the Excel data to be converted and cell data outside the row data;

[0013] A first writing module, used for writing the row data into the structured data set, and writing the cell data into the custom element;

[0014] The second writing module saves the structured data set in which the row data is written and the custom element in which the cell data is written into a target classification item, wherein the at least one classification item includes the target classification item.

[0015] In a third aspect, an embodiment of the present application provides an electronic device, which includes a processor and a memory, wherein the memory stores programs or instructions that can be run on the processor, and when the program or instructions are executed by the processor, the steps of the method described in the first aspect are implemented.

[0016] In a fourth aspect, an embodiment of the present application provides a readable storage medium, on which a program or instruction is stored, and when the program or instruction is executed by a processor, the steps of the method described in the first aspect are implemented.

[0017] In the embodiment of the present application, by using the JSON format SCHEMA to describe the definition of Excel format data, the definition of the Excel format data model is completed, so that the Excel data can be transferred to the data processing platform in JSON format, which provides convenience for the business processing and value calculation of Excel data. At the same time, the Excel format data is converted into JSON format data, and the structure of the Excel data is retained, making the converted Excel data more convenient and more logical during data processing, avoiding the problem of the prior art that the data is only listed in a table when converting data. BRIEF DESCRIPTION OF THE DRAWINGS

[0018] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the drawings required for use in the description of the embodiments of the present application will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0019] Figure 1 This is a flowchart of a method for converting Excel data into JSON data provided by an embodiment of the present application;

[0020] Figure 2a yes Figure 1 The Excel data to be converted obtained in ;

[0021] Figure 2b yes Figure 1 Convert Excel data to JSON data format;

[0022] Figure 3 This is a structural diagram of a device for converting Excel data into JSON data provided in an embodiment of the present application;

[0023] Figure 4 This is a structural diagram of an electronic device for converting Excel data into JSON data provided in an embodiment of the present application. DETAILED DESCRIPTION

[0024] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0025] This application embodiment provides a method for converting Excel data into JSON data. Figure 1 , Figure 1 It is a flow chart of an embodiment of the present application, such as Figure 1 As shown, the following steps are included:

[0026] Step 101: Obtain a data field in a JSON file format, where the data field includes a structured data set, a custom element, and at least one classification item;

[0027] In this embodiment, the Excel data is converted by using the JSON file format, which is based on the fact that the JSON file format is independent of language and platform and can support many different programming languages. The above data fields are data stored in the JSON file, and each data field of the JSON file belongs to a data type.

[0028] Step 102: Acquire Excel data to be converted, and acquire row data of the Excel data to be converted and cell data outside the row data;

[0029] In this step, the acquired Excel data to be converted may be cell data, row data, etc. in an Excel list, and the above cell data may be data stored in cells other than row data in the Excel list.

[0030] Step 103: writing the row data into the structured data set, and writing the cell data into the custom element;

[0031] In this embodiment, the above-mentioned structured data set can be a collection of multiple structured data stored in the JSON file format, and the data composition is structured and can be a data composition of rows or columns; the setting of custom elements is based on the particularity of Excel data, and there is a situation where certain Excel cell data needs to be obtained, so the custom element is defined as a field type in the JSON file format.

[0032] Step 104: Save the structured data set in which the row data is written and the custom element in which the cell data is written into a target classification item, wherein the at least one classification item includes the target classification item.

[0033] In an embodiment of the present application, a target classification item is a field type stored in a JSON file format that includes a structured data set and a custom element. The JSON file includes multiple target classification items, and the target classification item is composed of a structured data set and a custom element.

[0034] In an embodiment of the present application, the acquired Excel data to be converted is written into a data field in a JSON file format, the row data is written into a structured data set, the cell data is written into a custom element, and then the structured data set and the custom element are written into a target classification item, thereby completing the conversion of Excel format data into JSON format data, so that the key core data of Excel can be expressed in JSON format, which is convenient for data processing in the field of data integration.

[0035] It should be noted that the “writing” mentioned in the embodiments of the present application may mean adding a database to a database, or overwriting or appending to existing files or data.

[0036] Optionally, step 104 further includes:

[0037] Based on the list name in the Excel data, determining a target classification item corresponding to the list name;

[0038] Each of the at least one classification item corresponds to a list in the Excel data.

[0039] In an embodiment of the present application, before the structured data set in which the row data is written and the custom element in which the cell data is written are saved to the target category item, the target category item corresponding to the list name is determined by the list name in the acquired Excel data, and the data field in the acquired JSON file format contains at least one category item, and the target category item may be one of the at least one category item, and the target category item may correspond to one of the at least one category item, which facilitates the conversion of Excel format data into JSON format data for value operation.

[0040] It should be noted that the target classification item may correspond to one of at least one classification item, that is, any row data corresponds to related data in a structured data set, and a cell data in Excel data corresponds to related custom elements.

[0041] Optionally, the row data includes a first row data and a second row data, the structured data set includes a structured data attribute set and structured data, and the first row data is used to characterize the attributes of the second row data;

[0042] Optionally, step 103 further includes:

[0043] The first row of data is written into the structured data attribute set, and the second row of data is written into the structured data.

[0044] In an embodiment of the present application, the list name of the Excel data can be the name of different tables represented in the lower left corner of the Excel software, at least one classification item is written by the list name in the acquired Excel data to be converted, and at least one classification item can be one or more list names in the acquired Excel data to be converted; the row data of the Excel data is written into the structured data set; when it is necessary to access certain cell data in the Excel data, the cell data is defined as a field under the list name, representing the value of a certain Excel cell, and the cell data is written into a custom element, so the custom element is defined as a field under at least one classification item, representing the value of a certain cell in the Excel data in JSON file format.

[0045] It should be noted that the row data can be any row of data in Excel data, and can be one or more rows of data consisting of cells with any number of columns; the first row of data written into the structured data attribute set can be the attributes representing the second row of data, and the structured data attribute set can be a row of Excel that represents a set of special attribute fields (such as name, age), which belongs to the fields under the structured data set.

[0046] See also Figure 2a , an example is given below:

[0047] The acquired Excel data to be converted is defined as four key fields associated with the numeric fields of the JSON format file. Sheet1 is the list data in the Excel data, where Sheet1 is written as the Sheet name in the JSON format file. In the table shown, "Zhang San 18 Male 13800000001E001" is a second row of data in the row data. In this example, the data domain is A1 to E4, which is written into the structured data set in the JSON format file. "Name Age Gender Telephone" is the first row of data. In this example, the data domain is A1 to E1, which is written into the structured data attribute set in the JSON format file. "Total number of males and females" and "3 2 1" are cell data outside the first and second rows of data. In this example, multiple custom data points B7, C7, D7, etc. are taken and written into the custom elements in the JSON file format.

[0048] Optionally, the JSON file format includes keyword fields and data types.

[0049] It should be noted that the core of JSON format is to store multiple sets of KEY-VALUE key-value pairs. The keyword field part of JSON can be six data types: String, Number, Boolean, Array, Object, and null. KEY represents the keyword field, and VALUE represents the data type.

[0050] Optionally, step 103 further includes:

[0051] Writing the first row of data into the structured data attribute set, and using the structured data attribute set as the first keyword field of the structured data set;

[0052] The second row of data is written into the structured data, and the structured data is used as the first data type of the structured data set.

[0053] In an embodiment of the present application, each row of data in a structured data set can be understood as each row of structured data, and a collection of multiple rows of structured data is a structured data set; the first keyword field can be the name of the structured data attribute set itself as the keyword field, or other keyword names can be defined as the first keyword field; the first data type can be the data type part that stores the structured data, that is, the first data type of the structured data attribute set can be an object type, or it can be an Array type, a String type, etc.

[0054] Optionally, step 103 further includes:

[0055] Writing the attribute name of the cell data into the custom element as the second keyword field of the custom element;

[0056] The data corresponding to the attribute name in the cell data is written into the custom element as the second data type of the custom element.

[0057] In an embodiment of the present application, by traversing all value cells in the Excel data, the second keyword field can be the name of the custom element itself defined as the keyword field, or other keyword names can be defined as the second keyword field; the second data type can be the data type part of the value cell, that is, the second data type of the custom element can be a String type, or an Array type, a Number type, or other data types.

[0058] Optionally, step 104 further includes:

[0059] saving the structured data set in which the row data is written and the custom element in which the cell data is written into the target classification item as a third data type of the target classification item;

[0060] The list name corresponding to the target classification item in the Excel data is saved in the target classification item as the third keyword field of the target classification item.

[0061] See also Figure 2b In the embodiment of the present application, the data format of Excel is described by the JSON file format, and the data field is defined by the keyword field and the data type; at least one classification item itself is defined as the KEY, and its VALUE part is of Object type, and the content of the VALUE part is the description of the Excel data under the current list name; data is defined as the KEY of the structured data set, and its VALUE part is of Array type, each element of the Array is of Object type, and each element is each structured data; each element of the structured data set is of Object type, and the specific structure of the Object type is composed of fields representing special attributes contained in the structured data attribute set, and each special attribute field name in the structured data attribute set is defined as the KEY, and its VALUE part is of String type; a custom element is defined as a field under at least one classification item, representing the value of a cell in the Excel data, and the custom element name itself is defined as the KEY, and its VALUE part is of String type.

[0062] In the embodiment of the present application, the conversion of Excel format data into JSON format data is completed, and the expression of JSON format data is constructed for the Excel format data. Since Excel has multiple file types and the Excel file format is closed source, it is not possible to be fully compatible with its format. This embodiment allows Excel data to flow to the data processing platform in the JSON file format, which provides convenience for business processing, value operation, etc. of Excel data; and retains the structure of Excel data, ensuring that the converted Excel data is more convenient and more logical during data processing, avoiding the problem of data being merely listed in a table.

[0063] See also Figure 3 , Figure 3 This is a device for converting Excel data into JSON data provided by an embodiment of the present application. Figure 3 As shown, the device 200 for converting Excel data into JSON data includes:

[0064] A first acquisition module 201 is used to acquire data fields in a JSON file format, where the data fields include a structured data set, a custom element, and at least one classification item;

[0065] The second acquisition module 202 is used to acquire the Excel data to be converted, and acquire the row data of the Excel data to be converted and the cell data outside the row data;

[0066] A first writing module 203, used for writing the row data into the structured data set, and writing the cell data into the custom element;

[0067] The second writing module 204 saves the structured data set in which the row data is written and the custom element in which the cell data is written into a target classification item, wherein the at least one classification item includes the target classification item.

[0068] Optionally, the row data includes a first row data and a second row data, the structured data set includes a structured data attribute set and structured data, and the first row data is used to characterize the attributes of the second row data.

[0069] The first writing module 203 includes a first writing unit, and the first writing unit is used to write the first row of data into the structured data attribute set, and write the second row of data into the structured data.

[0070] Optionally, the JSON file format includes a keyword field and a data type, and the first writing unit is further used to:

[0071] Writing the first row of data into the structured data attribute set, and using the structured data attribute set as the first keyword field of the structured data set;

[0072] The second row of data is written into the structured data, and the structured data is used as the first data type of the structured data set.

[0073] Optionally, the second writing module 204 is further configured to:

[0074] Writing the attribute name of the cell data into the custom element as the second keyword field of the custom element;

[0075] The data corresponding to the attribute name in the cell data is written into the custom element as the second data type of the custom element.

[0076] Optionally, the second writing module 204 is further configured to:

[0077] saving the structured data set in which the row data is written and the custom element in which the cell data is written into the target classification item as a third data type of the target classification item;

[0078] The list name corresponding to the target classification item in the Excel data is saved in the target classification item as the third keyword field of the target classification item.

[0079] In the embodiment of the present application, the first acquisition module acquires the data field in the JSON file format, the second acquisition module acquires the Excel data to be converted, the first writing module 203 writes the row data acquired by the second acquisition module 202 into the first acquisition module 201, and writes the cell data into the custom element, and the second writing module 204 writes the structured data set and the custom element into the target classification item, completing the conversion from the Excel data format to the JSON data format, achieving the same technical effect as the above-mentioned method embodiment for converting Excel data into JSON data, and to avoid repetition, it will not be repeated here.

[0080] See also Figure 4 The embodiment of the present application also provides an electronic device 300, including a processor 301, a memory 302, and a computer program stored in the memory 302 and executable on the processor. When the computer program is executed by the processor 301, each process of the above-mentioned method embodiment for converting Excel data into JSON data is implemented, and the same technical effect can be achieved. To avoid repetition, it will not be repeated here.

[0081] The embodiment of the present application also provides a computer-readable storage medium, on which a computer program is stored, and when the computer program is executed by a processor, each process of the method embodiment for converting Excel data into JSON data is implemented, and the same technical effect can be achieved. To avoid repetition, it is not repeated here. The computer-readable storage medium is, for example, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0082] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.

[0083] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, a magnetic disk, or an optical disk), and includes a number of instructions for a terminal (which can be a mobile phone, a computer, a server, an air conditioner, or a network device, etc.) to execute the methods described in each embodiment of the present application.

[0084] The embodiments of the present application are described above in conjunction with the accompanying drawings, but the present application is not limited to the above-mentioned specific implementation methods. The above-mentioned specific implementation methods are merely illustrative and not restrictive. Under the guidance of the present application, ordinary technicians in this field can also make many forms without departing from the purpose of the present application and the scope of protection of the claims, all of which are within the protection of the present application.

Claims

1. A method for converting Excel data into JSON data, characterized in that: include: Obtain a data field in a JSON file format, wherein the data field includes a structured data set, a custom element, and at least one classification item; Acquire Excel data to be converted, and acquire row data of the Excel data to be converted and cell data outside the row data; Writing the row data into the structured data set, and writing the cell data into the custom element; Saving the structured data set in which the row data is written and the custom element in which the cell data is written into a target classification item, wherein the at least one classification item includes the target classification item; The step of writing the cell data into the custom element comprises: Writing the attribute name of the cell data into the custom element as the second keyword field of the custom element; The data corresponding to the attribute name in the cell data is written into the custom element as the second data type of the custom element.

2. The method according to claim 1, characterized in that: Before saving the structured data set having the row data written therein and the custom element having the cell data written therein to the target classification item, the method further comprises: Based on the list name in the Excel data, determining a target classification item corresponding to the list name; Each of the at least one classification item corresponds to a list in the Excel data.

3. The method according to claim 1, characterized in that The row data includes a first row of data and a second row of data, the structured data set includes a structured data attribute set and structured data, and the first row of data is used to characterize the attributes of the second row of data; The step of writing the row data into the structured data set includes: The first row of data is written into the structured data attribute set, and the second row of data is written into the structured data.

4. The method according to claim 3, characterized in that The JSON file format includes a keyword field and a data type, and writing the first row of data into the structured data attribute set and writing the second row of data into the structured data includes: Writing the first row of data into the structured data attribute set, and using the structured data attribute set as the first keyword field of the structured data set; The second row of data is written into the structured data, and the structured data is used as the first data type of the structured data set.

5. The method according to claim 3, characterized in that: The step of saving the structured data set having the row data written therein and the custom element having the cell data written therein to the target classification item includes: saving the structured data set in which the row data is written and the custom element in which the cell data is written into the target classification item as a third data type of the target classification item; The list name corresponding to the target classification item in the Excel data is saved in the target classification item as the third keyword field of the target classification item.

6. A device for converting Excel data into JSON data, characterized in that: include: A first acquisition module is used to acquire data fields in a JSON file format, wherein the data fields include a structured data set, a custom element, and at least one classification item; A second acquisition module is used to acquire Excel data to be converted, and acquire row data of the Excel data to be converted and cell data outside the row data; A first writing module, used for writing the row data into the structured data set, and writing the cell data into the custom element; A second writing module saves the structured data set in which the row data is written and the custom element in which the cell data is written into a target classification item, wherein the at least one classification item includes the target classification item; The first writing module is further used for: Writing the attribute name of the cell data into the custom element as the second keyword field of the custom element; The data corresponding to the attribute name in the cell data is written into the custom element as the second data type of the custom element.

7. The device according to claim 6, characterized in that The second writing module is also used for: Based on the list name in the Excel data, determining a target classification item corresponding to the list name; Each of the at least one classification item corresponds to a list in the Excel data.

8. The device according to claim 6, characterized in that The row data includes a first row of data and a second row of data, the structured data set includes a structured data attribute set and structured data, and the first row of data is used to characterize the attributes of the second row of data; The first writing module comprises: The first writing unit is used to write the first row of data into the structured data attribute set, and write the second row of data into the structured data.

9. The device according to claim 8, characterized in that The JSON file format includes a keyword field and a data type, and the first writing unit is further used for: Writing the first row of data into the structured data attribute set, and using the structured data attribute set as the first keyword field of the structured data set; The second row of data is written into the structured data, and the structured data is used as the first data type of the structured data set.

10. The device according to claim 8, characterized in that The second writing module is further used for: saving the structured data set in which the row data is written and the custom element in which the cell data is written into the target classification item as a third data type of the target classification item; The list name corresponding to the target classification item in the Excel data is saved in the target classification item as the third keyword field of the target classification item.

11. An electronic device, characterized in that: The invention comprises a processor and a memory, wherein the memory stores a program or instruction that can be run on the processor, and when the program or instruction is executed by the processor, the method steps of converting Excel data into JSON data according to any one of claims 1 to 5 are implemented.

12. A computer-readable storage medium, characterized in that: The readable storage medium stores a program or instruction, and when the program or instruction is executed by the processor, the method steps of converting Excel data into JSON data according to any one of claims 1 to 5 are implemented.