Method and system for analyzing XLSX file and converting data into JSON
By automating the parsing of XLSX files and converting them into JSON format, the problem of low efficiency and error-proneness in existing technologies is solved, achieving efficient and accurate data conversion that is suitable for various data processing scenarios.
Patent Information
- Application Number
- CN202511029257.4
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-07-25
- Publication Date
- 2025-10-31
AI Technical Summary
Existing XLSX file parsing methods are inefficient, support limited fields, struggle to handle large-scale data, and are prone to errors when converting to JSON format. They also fail to meet the requirements of speed, accuracy, and efficiency, especially in cross-platform data transmission and automated processing.
An automated approach is used, employing an XLSX reading library such as openpyxl to read XLSX files, construct JSON object keys, iterate through and generate JSON objects, summarize them into a JSON array, perform data type judgment and conversion, and output in JSON format, with error handling and formatting functions.
It improves data processing efficiency, reduces manual operations, lowers error rates, supports compatibility with different versions of Excel files, and uses JSON format for easy storage, transmission, and processing, making it suitable for data analysis, financial statement generation, and automated report generation.
Smart Images
Figure CN120874757A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data processing technology, specifically to a method and system for parsing XLSX files and converting the data into JSON. Background Technology
[0002] With the deepening development of the information society, spreadsheet files (especially XLSX format files) are widely used for data storage, analysis, and transmission in various industries. However, existing XLSX file parsing methods suffer from low efficiency, limited field support, and difficulty in handling large-scale data. Especially when converting data from XLSX to JSON format, existing methods often involve manual operations, which are cumbersome and error-prone. As the amount of data increases, manual parsing methods are not only inefficient but may also affect the integrity and accuracy of the data.
[0003] Traditional XLSX parsing methods often only read a portion of the cells, failing to quickly and accurately complete complex data conversion tasks, especially in cross-platform data transfer and automated processing. Existing technologies cannot meet the requirements of speed, accuracy, and efficiency. Therefore, developing an efficient and automated XLSX file parsing and conversion method is particularly important.
[0004] The XLSX file format, a standard format introduced by Microsoft Excel, is widely used in data storage and exchange, playing a crucial role, especially in business, finance, and scientific research. However, existing file conversion tools generally suffer from low processing efficiency, data loss, or conversion errors. This is particularly true when processing XLSX files containing large amounts of complex data, where the conversion process often consumes significant time and system resources, limiting the effective use of the files.
[0005] XLSX files contain various data types, including text, numbers, images, formulas, and complex styles. If these contents are not processed or converted correctly, data loss or formatting errors often occur. Furthermore, most existing tools lack effective solutions for handling compatibility differences between different Excel versions, ensuring data integrity, and maintaining conversion efficiency when dealing with such files. Therefore, designing an efficient XLSX file conversion solution that guarantees data integrity has become a major challenge in the current technological field. Summary of the Invention
[0006] The purpose of this invention is to provide a method and system for parsing XLSX files and converting the data into JSON, so as to solve the problems mentioned in the background art.
[0007] To achieve the above objectives, the present invention provides the following technical solution: a method for parsing XLSX files and converting data into JSON, comprising the following steps:
[0008] Read table data from an XLSX file: Use the XLSX reading library to open the target XLSX file, read all worksheets in the file and the data in each worksheet, and extract the column names and row data of the tables;
[0009] Keys for JSON objects are constructed based on column names: Using the first line of the XLSX file as the column name, a key for a JSON object is constructed for each column, with the title of each column as the key name;
[0010] Generate a JSON object by iterating through each row of data: Iterate through the table data row by row, and generate a JSON object containing all fields of each row of data according to the JSON keys corresponding to the constructed column names;
[0011] Collect JSON objects into a JSON array: Combine the JSON objects obtained from converting all rows of data into a single JSON array;
[0012] Output the converted JSON data: Output the final JSON array data in the form of an array. The output method includes storing it in a file, outputting it to the console, or using it for further data transfer.
[0013] Preferably, in the step of opening the target XLSX file using the XLSX reading library, reading all worksheets and data in each worksheet, and extracting column names and row data, the XLSX reading library used is openpyxl. The relevant functions of this library are used to accurately obtain worksheet information and table data content, and to extract column names and row data according to the preset data structure.
[0014] Preferably, in the step of constructing the key of the JSON object based on the column name, the column names in the first line of the XLSX file are preprocessed, including removing whitespace characters at both ends of the column names and unifying the case of the column names, to ensure the accuracy and consistency of the constructed JSON object key.
[0015] Preferably, in the step of traversing each row of data to generate a JSON object, the data type of the row data is judged and converted during the generation of the JSON object, and the data that conforms to a specific format is converted into the corresponding JSON data type to ensure the accuracy and rationality of the data in the generated JSON object.
[0016] Preferably, in the steps of collecting JSON objects into a JSON array and outputting the converted JSON data, when collecting the JSON objects into a JSON array, the JSON objects in the array are sorted, and the sorting rules can be set according to actual needs; when outputting JSON data, the output data is formatted to make it more readable.
[0017] A system for parsing XLSX files and converting data to JSON includes:
[0018] The file reading module is used to open the target XLSX file using the XLSX reading library, read all the worksheets in the file and the data in each worksheet, and extract the column names and row data of the tables to provide a data foundation for subsequent conversion work;
[0019] The key building module connects to the file reading module. Based on the column names in the first row of the XLSX file extracted by the file reading module, it builds a JSON object key for each column, using the title of each column as the key name.
[0020] The object generation module, connected to the key building module, iterates through each row of data in the table and generates a JSON object containing all fields of each row of data according to the JSON keys corresponding to the column names generated by the key building module. Each JSON object represents a row of data in the XLSX file.
[0021] The array composition module connects with the object generation module and aggregates all the JSON objects generated by the object generation module into a JSON array, so that the data of the entire XLSX file is presented in JSON format.
[0022] The data output module connects to the array synthesis module and outputs the data in JSON array format obtained by the array synthesis module. The output methods include storing it in a file, outputting it to the console, or using it for further data transfer.
[0023] Preferably, the file reading module has an error handling mechanism when opening the target XLSX file using the openpyxl library. When the file fails to open or an error occurs while reading data, the module captures the error and outputs the corresponding error message, while also recording the error log for subsequent troubleshooting.
[0024] Preferably, when constructing the keys of a JSON object, the key construction module preprocesses the column names in the first row of the XLSX file, including removing whitespace characters at both ends of the column names, unifying the case of the column names, and escaping column names containing special characters, to ensure the accuracy and validity of the constructed JSON object keys.
[0025] Preferably, the object generation module includes a data validation and conversion submodule during the JSON object generation process. This submodule performs data type determination on each row of data, converts data that conforms to a specific format into the corresponding JSON data type, and marks or corrects data that does not conform to the format requirements according to preset rules to ensure the accuracy and rationality of the data in the generated JSON object.
[0026] Preferably, the array synthesis module has a sorting function when summarizing JSON objects into a JSON array, and can sort the JSON objects in the array according to the sorting rules set by the user; the data output module has a formatting submodule when outputting JSON data, which formats the output JSON data to make it more readable, and supports the configuration of multiple output formats to meet the needs of different scenarios.
[0027] Compared with the prior art, the beneficial effects of the present invention are:
[0028] This invention presents a method and system for parsing XLSX files and converting data into JSON. By automating the parsing of XLSX files, it significantly improves data processing efficiency and enables the rapid processing of large amounts of data. The entire data parsing process is automated, reducing manual operation and error rates. This method is applicable to various XLSX file formats, including different versions of Excel files, demonstrating strong compatibility. The JSON output format is easy to store, transmit, and process, and facilitates subsequent modification and maintenance. This invention is applicable to various scenarios such as data analysis, financial statement generation, and automated report generation, and has broad application prospects. Attached Figure Description
[0029] Figure 1 This is a flowchart of the method of the present invention. Detailed Implementation
[0030] To make the objectives, technical solutions, and advantages of the present invention clear and complete, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are only some, not all, embodiments of the present invention, and are merely illustrative of the embodiments of the present invention. They are not intended to limit the embodiments of the present invention. All other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.
[0031] Example 1, please refer to Figure 1 This invention provides a technical solution: a method for parsing XLSX files and converting data into JSON, comprising the following steps:
[0032] Reading tabular data from an XLSX file: Open the target XLSX file using the XLSX reading library, read all worksheets and data from each worksheet, and extract column names and row data. The XLSX reading library used is openpyxl. Through the relevant functions of this library, the worksheet information and table data content are accurately obtained, and the column names and row data are extracted according to the preset data structure.
[0033] Constructing JSON object keys based on column names: Using the first line of the XLSX file as the column name, construct a JSON object key for each column, using the column header as the key name; preprocess the column names in the first line of the XLSX file, including removing leading and trailing whitespace characters and standardizing the case of column names, to ensure the accuracy and consistency of the constructed JSON object keys.
[0034] Iterate through each row of data to generate a JSON object: Iterate through the table data row by row, and generate a JSON object containing all fields of each row according to the JSON keys corresponding to the constructed column names; perform data type judgment and conversion on the row data, and convert data that conforms to a specific format into the corresponding JSON data type to ensure the accuracy and rationality of the data in the generated JSON object.
[0035] To compile JSON objects into a JSON array: All JSON objects converted from the data in each row are compiled into a single JSON array. During this process, the JSON objects in the array are sorted according to the specified sorting rules.
[0036] Output the converted JSON data: Output the final JSON array data in the form of an array. The output method includes storing it in a file, outputting it to the console, or using it for further data transfer. When outputting JSON data, the output data is formatted to make it more readable.
[0037] Example 2, based on Example 1, proposes a system for parsing XLSX files and converting data into JSON, including:
[0038] The file reading module is used to open the target XLSX file using the XLSX reading library, read all worksheets in the file and the data in each worksheet, and extract the column names and row data of the tables to provide a data foundation for subsequent conversion work; it has an error handling mechanism, which captures the exception and outputs the corresponding error message when the file fails to open or the data reading is abnormal, and records the error log for subsequent troubleshooting.
[0039] The key construction module, connected to the file reading module, constructs a JSON object key for each column based on the column names in the first row of the XLSX file extracted by the file reading module, using the title of each column as the key name. When constructing the JSON object key, the key construction module preprocesses the column names in the first row of the XLSX file, including removing leading and trailing whitespace characters from the column names, standardizing the capitalization of the column names, and escaping column names containing special characters to ensure the accuracy and validity of the constructed JSON object key.
[0040] The object generation module, connected to the key construction module, iterates through each row of data in the table and generates a JSON object containing all fields of each row based on the JSON keys corresponding to the column names generated by the key construction module. Each JSON object represents a row of data in the XLSX file. During the JSON object generation process, the object generation module includes a data validation and conversion submodule. This submodule checks the data type of each row, converts data that conforms to a specific format to the corresponding JSON data type, and marks or corrects data that does not meet the format requirements according to preset rules to ensure the accuracy and reasonableness of the data in the generated JSON object.
[0041] The array composition module, connected to the object generation module, aggregates all JSON objects generated by the object generation module into a single JSON array, presenting the entire XLSX file's data in JSON format. The array composition module also features a sorting function, allowing users to sort the JSON objects in the array according to their defined sorting rules. The data output module includes a formatting submodule to format the output JSON data, improving its readability and supporting multiple output format configurations to meet the needs of different scenarios.
[0042] The data output module connects to the array synthesis module and outputs the data in JSON array format obtained by the array synthesis module. The output methods include storing it in a file, outputting it to the console, or using it for further data transfer.
[0043] Although embodiments of the invention have been shown and described, it will be understood by those skilled in the art that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the appended claims and their equivalents.
Claims
1. A method for parsing XLSX files and converting data into JSON, characterized in that: Includes the following steps: Read table data from an XLSX file: Use the XLSX reading library to open the target XLSX file, read all worksheets in the file and the data in each worksheet, and extract the column names and row data of the tables; Keys for JSON objects are constructed based on column names: Using the first line of the XLSX file as the column name, a key for a JSON object is constructed for each column, with the title of each column as the key name; Generate a JSON object by iterating through each row of data: Iterate through the table data row by row, and generate a JSON object containing all fields of each row of data according to the JSON keys corresponding to the constructed column names; Collect JSON objects into a JSON array: Combine the JSON objects obtained from converting all rows of data into a single JSON array; Output the converted JSON data: Output the final JSON array data in the form of an array. The output method includes storing it in a file, outputting it to the console, or using it for further data transfer.
2. The method for parsing XLSX files and converting data to JSON according to claim 1, characterized in that: In the steps of opening the target XLSX file using the XLSX reading library, reading all worksheets and data in each worksheet, and extracting column names and row data, the XLSX reading library used is openpyxl. The relevant functions of this library accurately obtain worksheet information and table data content, and extract column names and row data according to the preset data structure.
3. The method for parsing XLSX files and converting data to JSON according to claim 2, characterized in that: In the step of constructing the keys of a JSON object based on column names, the column names in the first line of the XLSX file are preprocessed, including removing whitespace characters from both ends of the column names and standardizing the case of the column names, to ensure the accuracy and consistency of the constructed JSON object keys.
4. The method for parsing an XLSX file and converting the data into JSON according to claim 3, characterized in that: In the process of iterating through each row of data to generate a JSON object, the data type of each row of data is judged and converted during the generation of the JSON object. Data that conforms to a specific format is converted into the corresponding JSON data type to ensure the accuracy and rationality of the data in the generated JSON object.
5. The method for parsing an XLSX file and converting the data into JSON according to claim 4, characterized in that: In the steps of collecting JSON objects into a JSON array and outputting the converted JSON data, when collecting the JSON objects into a JSON array, the JSON objects in the array are sorted, and the sorting rules can be set according to actual needs; when outputting the JSON data, the output data is formatted to make it more readable.
6. A system for using the method of parsing XLSX files and converting data to JSON according to claim 5, characterized in that: include: The file reading module is used to open the target XLSX file using the XLSX reading library, read all the worksheets in the file and the data in each worksheet, and extract the column names and row data of the tables to provide a data foundation for subsequent conversion work; The key building module connects to the file reading module. Based on the column names in the first row of the XLSX file extracted by the file reading module, it builds a JSON object key for each column, using the title of each column as the key name. The object generation module, connected to the key building module, iterates through each row of data in the table and generates a JSON object containing all fields of each row of data according to the JSON keys corresponding to the column names generated by the key building module. Each JSON object represents a row of data in the XLSX file. The array composition module connects with the object generation module and aggregates all the JSON objects generated by the object generation module into a JSON array, so that the data of the entire XLSX file is presented in JSON format. The data output module connects to the array synthesis module and outputs the data in JSON array format obtained by the array synthesis module. The output methods include storing it in a file, outputting it to the console, or using it for further data transfer.
7. The system according to claim 6, characterized in that: The file reading module has an error handling mechanism when using the openpyxl library to open target XLSX files. When the file fails to open or an error occurs while reading data, the module captures the exception and outputs the corresponding error message, while also logging the error for subsequent troubleshooting.
8. A system according to claim 6, characterized in that: When constructing keys for JSON objects, the key construction module preprocesses the column names in the first line of the XLSX file, including removing leading and trailing whitespace characters, standardizing the case of column names, and escaping special characters in column names to ensure the accuracy and validity of the constructed JSON object keys.
9. A system according to claim 6, characterized in that: The object generation module includes a data validation and conversion submodule during the JSON object generation process. This submodule checks the data type of each row of data, converts data that conforms to a specific format into the corresponding JSON data type, and marks or corrects data that does not meet the format requirements according to preset rules to ensure the accuracy and rationality of the data in the generated JSON object.
10. A system according to claim 6, characterized in that: The array composition module has a sorting function when summarizing JSON objects into a JSON array. It can sort the JSON objects in the array according to the sorting rules set by the user. The data output module has a formatting submodule when outputting JSON data. It formats the output JSON data to make it more readable and supports configuration of multiple output formats to meet the needs of different scenarios.