Excel spreadsheet data import and export method for realizing multi-level table header multi-type data structure

Abstract

The application discloses an excel electronic form data import and export method for realizing a multi-level table header multi-type data structure, and realizes data import and export between excel electronic form data and a relational database through parsing and coding of a JSON data structure object and a cell sheet object of excel; the method comprises parsing of a tree level data structure of a table header, parsing of a data type object of a table column, and parsing of a table cell format. In view of a complex multi-level table header structure and various data formats, a self-defined standard and normative data algorithm is used to correctly read and write table data, and when data is collected, the data is parsed and stored through various type data conversion methods; when output is performed, a standard data structure format conversion method is used to correctly output the excel electronic form. Advantages are as follows: the import and export application is wide, and is suitable for data standard conversion between various databases; excel data format is effectively parsed and output, and the use experience of excel and the ability of analyzing and integrating data are improved.

Description

Technical Field

[0001] This invention relates to the field of automated data collection, storage and output technology, and in particular to a method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types. Background Technology

[0002] The data collected in production is complex, including various formats such as timestamps, numeric types, and text. The data structures are hierarchical, with multiple combinations such as grouping and categorization. Typically, Excel spreadsheets are used to convert data between these formats and mainstream databases such as Oracle, DB2, SQL Server, Access, Sybase, and MySQL. Traditional data conversion methods involve manually converting the raw data format in Excel reports to a standard string type, importing it into the production database, and exporting a single table structure. The drawbacks of this approach are: it requires manual conversion of firsthand production data to a standard format, which is time-consuming, labor-intensive, and inefficient; the built-in calculation functions of the spreadsheet are lost; and the exported table structure is simple and lacks style. Summary of the Invention

[0003] The purpose of this invention is to provide a method for importing and exporting data in an Excel spreadsheet with multiple headers and data types, thereby solving the aforementioned problems in the prior art.

[0004] To achieve the above objectives, the technical solution adopted by the present invention is as follows:

[0005] A method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types is provided. This method achieves data import and export between Excel spreadsheet data and relational databases through the parsing and encoding of JSON data structure objects and Excel cell / sheet objects. This includes...

[0006] A. Parsing the tree-like hierarchical data structure of the table header: corresponding to the data column object structure, realizing the construction of the header sheet object;

[0007] B. Parsing of data type objects in table columns: When importing data from an Excel spreadsheet into a relational database, the data undergoes string format conversion, timestamp conversion, and numeric data conversion methods; when exporting data from a relational database to an Excel spreadsheet, the data undergoes string format conversion, date format conversion, and numeric data format conversion methods.

[0008] C. Parsing of table cell formatting: Pure JS syntax, extending the reading and writing of cell formats in .xlsx workbooks; the number format strings associated with cells use standard Sheet object properties to calculate and construct the output data object structure.

[0009] Preferably, the parsing of the tree-like hierarchical data structure of the table header includes the following process:

[0010] A1. Customize a complex standard JSON data structure for table headers, defining multi-level column1, column2, ..., columnN table column element objects, whose basic properties include field, title, width, type, numeric type, format type, and child nodes;

[0011] A2. A method of traversing and parsing the table-based JSON structured data layer by layer is adopted. The cell object is calculated through the column object, index, level, and header node object. The cell object includes the row, column, attributes, title, form label, sequence number, type, and data type.

[0012] A3. Recursively execute A2 to calculate the array collection of cell objects for the entire header node, thus constructing a complex multi-level header structure;

[0013] A4. Calculate the format of the header cells by filtering row and column data objects;

[0014] A5. Get the maximum number of columns and the maximum level of the table header, and execute the fromCharCode method in a loop to convert the ASCII code value to the corresponding character, and calculate the table header fill area.

[0015] Preferably, it is characterized by:

[0016] The string format conversion method is as follows: A custom key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}] is defined. The setRuleData function is used to match column attributes, and the column text format is converted to encoded values ​​by matching key values ​​with valid values ​​one-to-one. The import data formatting method setImportTableData is then executed to import Chinese characters into encoded data through key-value mapping.

[0017] The timestamp conversion method is as follows: For data of type date or datetime, the formatDate function is used to convert it into date and datetime format, and then the format function is called to convert it into a date format that matches the dataformat; For data of type time, the formatTimes function is executed to convert it into a time string in the style HH:mm:ss.

[0018] The method for converting numerical data is as follows: If the data in the Excel cell is numerical, the display format is changed, and the data is converted to the Number type according to the precision to import the data.

[0019] Preferably, it is characterized by:

[0020] The string format conversion method is as follows: A custom key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}] is created. The setRuleData function is used to match column attributes, and the column encoding values ​​are converted to text format by matching each key-value pair with a valid value. The setExportTableData method is then executed to export the encoded data to Chinese characters through key-value mapping.

[0021] The date format conversion method is as follows: For data of type date or datetime, text formatting, and table column objects, the dateFormat property sets the standard date and time output format. Excel's custom date display number format is used to convert the date and time string into a date and time type for export, calculating the date and time display format. Date display number formats include "yyyy-MM-dd", "yyyy / MM / dd", ... "yyyy-MM-ddHH:mm:ss".

[0022] The method for converting numerical data formats is as follows: Numerical data uses Excel's custom numerical display format to achieve format conversion of numerical data. It judges the numerical string, and the order of placeholders is positive or negative, integer place, and decimal precision, and then calculates the numerical format of z. The numerical display format includes: numerical value as "0.00%", positive number as "#, ##.00", negative number as "-#, ##.00", and zero as "0.00".

[0023] z represents the numeric format string associated with the cell.

[0024] Preferably, the process of importing data from an Excel spreadsheet into a relational database is as follows:

[0025] This method uses a hierarchical traversal parsing approach for JSON-structured data to parse the header structure of an Excel spreadsheet, obtain column objects, read data from the spreadsheet and establish corresponding relationships with the column objects, add data filtering methods based on column type to check and filter data with the same name, and execute spreadsheet data conversion functions to convert different types of data in the Excel spreadsheet into the correct data type for import into a relational database.

[0026] Preferably, the process of exporting data from a relational database to an Excel spreadsheet is as follows:

[0027] S21. The customized header structure is passed parameters through the multipleHeader function, which is recursively executed to parse, analyze, and calculate the smallest unit cell structure. The parameters include the table data object, column index, row number, sub-object sequence number, cell object, and whether it is a cell.

[0028] S22. Traverse the header column, construct the cell data corresponding to the table header, merge cell objects, replace the data in the preceding column of rows with the same group number, calculate the table header area, and filter to generate a multi-level JSON array structure.

[0029] S23. Traverse the table object and execute the getCellTypeMultiu cell data conversion method. For each row of data, use the string format conversion method and / or the date format conversion method and / or the numeric data format conversion method to convert the data type.

[0030] S24. Write the Sheets objects of the table cells calculated in the above steps into the header fill area in sequence, and export the data as a blob binary stream to an Excel file.

[0031] The beneficial effects of this invention are: 1. The method of this invention adopts Vue+Element+xlsx+js-xlsx technology, using JS methods to parse and write spreadsheet data structures, supporting the output of various Excel data formats. 2. For complex multi-level header structures and multiple data formats, a custom standard data algorithm is adopted, which can correctly read and write spreadsheet data. During data collection, multiple data type conversion algorithms are used to parse and input the data into the database; the output uses a standard data structure format parsing algorithm to correctly output Excel spreadsheets. Attached Figure Description

[0032] Figure 1 This is a schematic diagram illustrating the data import and export principle in an embodiment of the present invention;

[0033] Figure 2 This is a diagram illustrating the JSON structure-sheet conversion structure in an embodiment of the present invention;

[0034] Figure 3 This is a complex tree-shaped cell attribute structure diagram in an embodiment of the present invention. Detailed Implementation

[0035] To make the objectives, technical solutions, and advantages of this invention clearer, the 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 merely illustrative and not intended to limit the invention.

[0036] Example 1

[0037] like Figures 1 to 3 As shown in this embodiment, a method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types is provided. The method achieves data import and export between Excel spreadsheet data and relational databases by parsing and encoding JSON data structure objects and Excel cell sheet objects.

[0038] This invention enables batch data collection via Excel import. It provides a function conversion algorithm that conforms to relational database data structures for Excel's date and time, floating-point data, and text data structures, ensuring accurate collection of business production data. Through multi-header parsing methods and data display format conversion methods, it uses XLSXStyle to achieve conversion between Excel Worksheet data objects and the data, supporting editing of table header objects and table styles, as well as formatted table output. The following three technical principles enable the import and export of complex header table data with various data types, styles, and formats:

[0039] A. Parsing the tree-like hierarchical data structure of the table header: corresponding to the data column object structure, realizing the construction of the header sheet object;

[0040] B. Parsing of data type objects in table columns: When importing data from an Excel spreadsheet into a relational database, the data undergoes string format conversion, timestamp conversion, and numeric data conversion methods; when exporting data from a relational database to an Excel spreadsheet, the data undergoes string format conversion, date format conversion, and numeric data format conversion methods.

[0041] C. Parsing of table cell formatting: Pure JS syntax, extending the reading and writing of cell formats in .xlsx workbooks; the number format strings associated with cells use standard Sheet object properties to calculate and construct the output data object structure.

[0042] Standard Sheet object properties: v (raw value), w (formatted text), z (number format string associated with the cell), s (cell style theme).

[0043] In this embodiment, the parsing of the tree-like hierarchical data structure of the table header includes the following process.

[0044] A1. Customize a complex standard table header JSON data structure, define multi-level column1, column2, ..., columnN table column element objects, whose basic properties include field, title, width, type, numeric type, format type, and children.

[0045] A2. A method of traversing and parsing the table JSON structured data layer by layer is adopted. The cell object is calculated through the column object, index, level, and header node object. The cell object includes the row c, column t, prop attribute, title, form label, order number, type, and data type.

[0046] A3. Recursively execute A2 to calculate the array collection of cell objects for the entire header node, thus constructing a complex multi-level header structure;

[0047] A4. Implement table headers by filtering row and column data objects! Calculate MERGS cell formatting;

[0048] A5. Retrieve the maximum number of columns and the maximum level of the table header, loop through and execute the fromCharCode method to convert the ASCII code values ​​to the corresponding characters, and calculate the table header! ref fill area.

[0049] In this embodiment, during the data import process, different conversion methods need to be selected for different types of data. Specifically, these include the following three:

[0050] 1. The string format conversion method is as follows: Customize the key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}], use the setRuleData function to match column attributes, and convert the column text format to encoded values ​​by matching the key-value pairs with the valid values ​​one by one; execute the import data formatting method setImportTableData, and realize the import of Chinese characters converted to encoded data through key-value mapping;

[0051] 2. The timestamp conversion method is as follows: For data of type date and datetime, the formatDate function is used to convert it into date and datetime format, and then the format function is called to convert it into a date format that matches the dataformat; For data of type time, the formatTimes function is executed to convert it into a time string in the style HH:mm:ss.

[0052] 3. The method for converting numerical data is as follows: If the data in the Excel cell is numerical, the display format is changed, and the data is converted to the Number type according to the precision to import the data.

[0053] In this embodiment, different conversion methods need to be selected for different types of data during the data export process. Specifically, these include the following three:

[0054] 1. The string format conversion method is as follows: Customize the key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}], use the setRuleData function to match column attributes, and convert the column encoding value into text format by matching the key value with the valid value one by one; execute the export data formatting method setExportTableData, and realize the data export of encoding converted to Chinese characters through key-value mapping;

[0055] 2. The date format conversion method is as follows: For data of type date or datetime, text formatting, and table column objects, the dateFormat property sets the standard format for date and time output. Excel's custom date display number format is used to convert the date and time string into a date and time type for export, calculating the date and time display format. Date display number formats include "yyyy-MM-dd", "yyyy / MM / dd", ... "yyyy-MM-dd HH:mm:ss".

[0056] 3. The method for converting numerical data formats is as follows: Numerical data uses Excel's custom numerical display format to achieve format conversion of numerical data. It judges the numerical string and determines the order of placeholders as positive or negative, integer place, and decimal precision, and then calculates the numerical format of z. The numerical display format includes: numerical value as "0.00%", positive number as "#, ##.00", negative number as "-#, ##.00", and zero as "0.00".

[0057] z represents the numeric format string associated with the cell.

[0058] Example 2

[0059] In this embodiment, see Appendix Figure 1 The present invention relates to a data import and export process. It utilizes a JSON-to-Sheet method to convert between JSON database structures and Excel data structures, enabling data conversion between Excel spreadsheets and mainstream production business databases such as Oracle and MySQL. This method assists in production data collection, data processing, and data application management.

[0060] See Figure 3 It is a JSON tree-structured data exchange format for simple and complex multi-level table headers, and a JavaScript object array structure that is parsed and generated by the machine, making it easy to read and write. It is the basic data object for importing and exporting data in Excel spreadsheets.

[0061] header=[{columns1…},{columns2…,children[{columns21…}]},{children:[{children:[{children:[]}];

[0062] This invention constructs and generates table data column objects through a header parsing method. The table column attributes are the standard structure for data processing. The transmission and acquisition of table data are bidirectional, and different parsing and conversion algorithms are used in the data flow process.

[0063] The following examples illustrate Excel data import and export methods, as well as corresponding conversion and calculation techniques.

[0064] I. Importing data from an Excel spreadsheet into a relational database

[0065] This method uses a hierarchical traversal parsing approach for JSON-structured data to parse the header structure of an Excel spreadsheet, obtain column objects, read data from the spreadsheet and establish corresponding relationships with the column objects, add data filtering methods based on column type to check and filter data with the same name, and execute spreadsheet data conversion functions to convert different types of data in the Excel spreadsheet into the correct data type for import into a relational database.

[0066] The data types for table columns include three types: string, number, and date / time (String, Number, datetime, time, date). There are three corresponding conversion algorithms. The Excel spreadsheet data is read using `filesread`, and the conversion algorithms are used for parsing. The `setRuleData` and `setImportRows` functions retrieve the set of key-value pairs for conversion and perform the conversion calculations.

[0067] (1) String format conversion method: For special business applications, it is necessary to convert machine-generated data into encoded storage. First, customize the key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}]. Use the setRuleData function to match column attributes, and convert the column text format into encoded values ​​by matching the key and the valid value one by one. Execute the import data formatting method setImportTableData(row). The row data is converted from Chinese characters to encoded data and imported into the database through key-value mapping.

[0068] (2) Timestamp conversion method: The date and time displayed in Excel cells are standard formatted times, but the actual imported data is a floating-point timestamp (timeStmap). Data imported in date and time format needs to be converted to date and time strings for storage.

[0069] For data of type date or datetime, the formatDate function is called to convert it into a date and datetime format, the 5-digit time format is converted into a timestamp, and then the format function is called to convert it into a date format that matches the dataformat.

[0070] Data of type 'time' is converted to a 'HH:mm:ss' time string by executing the function 'formatTimes(date, formatvalue)', where 'date' is a floating-point number and 'formatvalue' is the formatting style.

[0071] (3) Numeric type: Excel cells contain numeric data. The display format is converted to Number type according to precision.

[0072] II. Exporting data from a relational database to an Excel spreadsheet

[0073] The principle of data export is the same as that of import. Exporting data also uses the table header parsing method to obtain table column objects. There are three steps to exporting database data into an Excel report.

[0074] 1. Parse the table header format: The customized header structure is passed parameters through the multipleHeader function. The above function is executed recursively to parse, analyze and calculate the smallest unit cell structure. The parameters include the table data object tablabel, column index index, row number t, sub-object sequence number orderid, cell object tablehead, and whether it is a cell.

[0075] tableitem={"c":index,"t":t,"prop":prop,"title":title,"orderid1":ordered"type":type,"dataType":datetype,"width":width,"mergs":{"s":{"c":2,"r":1},"e":{"c":5,"r":1}}

[0076] Where c is the row number, t is the column number, prop is the attribute value, title is the title, type is the node type, datetype is the data type, width is the column width, Mergs is the merged cell object, and s and e are the start and end coordinates of the merged cell, respectively.

[0077] 2. Traverse the header column, construct the cell data corresponding to the table header, merge cell objects, replace the data in the column before the "c" group with the same index, calculate the table header area, and filter to generate a multi-level JSON array structure.

[0078]

[0079] Among them, Font is the font color and size, alignment is the text centering horizontally or vertically, fill is the fill color, and border is the border line size, color, and style.

[0080] 3. Export Data Type Conversion: Iterate through the table object, execute the getCellTypeMultiu cell data conversion method, and convert the data type of each row using string format conversion method and / or date formatting method and / or numeric data format conversion method respectively:

[0081] (1) String format conversion method: When database encoding needs to be converted to display text for export, first customize the key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}]. Use the setRuleData function to match column attributes, and convert the column encoding values ​​into text format by matching the key values ​​with the valid values ​​one by one. Execute setExportTableData(tabledata) to format the exported data. tabledata is the table row data. Through key-value mapping, the encoding is converted to Chinese characters for output.

[0082] (2) Data format conversion method: Number data uses Excel's custom display number format to achieve data formatting of numerical type. The custom format of the numerical data is "0.00%", positive number '#, ##.00', negative number '-#, ##.00', zero '0.00'. The numerical string is judged, and the order of the placeholders is positive or negative number, integer place, decimal precision. The numerical format of z is calculated.

[0083] (3) Date format conversion method: The type is date, datetime type, z text format, table column objects, the property dateFormat is the standard format for date and time output, select "yyyy-MM-dd", "yyyy / MM / dd", ... "yyyy-MM-dd HH:mm:ss", through Excel's custom display number format, realize the conversion of date and time string to date and time type for export, and calculate the date and time display format of z.

[0084] 4. Write the Sheets objects calculated in the above steps into the header fill area in sequence! ref, and export the data as a blob binary stream to an Excel file.

[0085] By adopting the above-disclosed technical solution of this invention, the following beneficial effects are obtained:

[0086] This invention provides a method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types. The method employs Vue + Element + xlsx + js-xlsx technology, using JavaScript to parse and write the spreadsheet data structure, supporting output in various Excel data formats. For complex multi-level header structures and multiple data formats, a custom-defined standard data algorithm is adopted to correctly read and write spreadsheet data. During data acquisition, various data type conversion algorithms are used to parse and import the data into the database. Output uses a standard data structure format parsing algorithm to correctly output the Excel spreadsheet.

[0087] The above description is only a preferred embodiment of the present invention. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types: Data import and export between Excel spreadsheet data and relational databases is achieved through the parsing and encoding of JSON data structure objects and Excel cell / sheet objects; These include, A. Parsing the tree-like hierarchical data structure of the table header: corresponding to the data column object structure, realizing the construction of the header sheet object; B. Parsing of data type objects in table columns: When importing data from an Excel spreadsheet into a relational database, the data undergoes string format conversion, timestamp conversion, and numeric data conversion methods; when exporting data from a relational database to an Excel spreadsheet, the data undergoes string format conversion, date format conversion, and numeric data format conversion methods. C. Parsing Table Cell Formatting: Pure JavaScript syntax, extending the reading and writing of cell formats from .xlsx workbooks; the numeric format strings associated with cells use standard Sheet object properties, calculating and constructing the output data object structure; the parsing of the tree-like hierarchical data structure of the table header includes the following process... A1. Customize a complex standard JSON data structure for table headers, defining multi-level column1, column2, ..., columnN table column element objects, whose basic properties include field, title, width, type, numeric type, format type, and child nodes; A2. A method of traversing and parsing the table-based JSON structured data layer by layer is adopted. The cell object is calculated through the column object, index, level, and header node object. The cell object includes the row, column, attributes, title, form label, sequence number, type, and data type. A3. Recursively execute A2 to calculate the array collection of cell objects for the entire header node, thus constructing a complex multi-level header structure; A4. Calculate the format of the header cells by filtering row and column data objects; A5. Get the maximum number of columns and the maximum level of the table header, and execute the fromCharCode method in a loop to convert the ASCII code value to the corresponding character, and calculate the table header fill area.

2. The method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types as described in claim 1, characterized in that: The string format conversion method is as follows: A custom key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}] is defined. The setRuleData function is used to match column attributes, and the column text format is converted to encoded values ​​through a one-to-one correspondence between key-value pairs and valid values. The import data formatting method setImportTableData is then executed to import data containing Chinese characters converted to their encoded form through key-value mapping. The timestamp conversion method is as follows: For data of type date or datetime, the formatDate function is used to convert it into date and datetime format, and then the format function is called to convert it into a date format that matches the dataformat; For data of type time, the formatTimes function is executed to convert it into a time string in the style HH:mm:ss. The method for converting numerical data is as follows: If the data in the Excel cell is numerical, the display format is changed, and the data is converted to the Number type according to the precision to import the data.

3. The method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types as described in claim 1, characterized in that: The string format conversion method is as follows: A custom key-value matching data set [{key1,value1},{key2,value2},…{keyn,valuen}] is created. The setRuleData function is used to match column attributes, and the column encoding values ​​are converted to text format by matching each key-value pair with a valid value. The setExportTableData method is then executed to export the encoded data to Chinese characters through key-value mapping. The date format conversion method is as follows: For data of type date or datetime, text formatting, and table column objects, the dateFormat property sets the standard date and time output format. This uses Excel's custom date display number format to convert the date and time string into a date and time type for export, calculating the date and time display format. Date display number formats include "yyyy-MM-dd", "yyyy / MM / dd", ... "yyyy-MM-dd HH:mm:ss". The method for converting numerical data formats is as follows: Numerical data uses Excel's custom numerical display format to achieve format conversion of numerical data. It judges the numerical string, and the order of placeholders is positive or negative, integer place, and decimal precision, and then calculates the numerical format of z. The numerical display format includes: numerical value as "0.00%", positive number as "#, ##.00", negative number as "-#, ##.00", and zero as "0.00". z represents the numeric format string associated with the cell.

4. The method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types as described in claim 1, characterized in that: The process of importing data from an Excel spreadsheet into a relational database is as follows: This method uses a hierarchical traversal parsing approach for JSON-structured data to parse the header structure of an Excel spreadsheet, obtain column objects, read data from the spreadsheet and establish corresponding relationships with the column objects, add data filtering methods based on column type to check and filter data with the same name, and execute spreadsheet data conversion functions to convert different types of data in the Excel spreadsheet into the correct data type for import into a relational database.

5. The method for importing and exporting Excel spreadsheet data with multi-level headers and multiple data types as described in claim 1, characterized in that: The process of exporting data from a relational database to an Excel spreadsheet is as follows: S21. The customized header structure is passed parameters through the multipleHeader function, which is recursively executed to parse, analyze, and calculate the smallest unit cell structure. The parameters include the table data object, column index, row number, sub-object sequence number, cell object, and whether it is a cell. S22. Traverse the header column, construct the cell data corresponding to the table header, merge cell objects, replace the data in the preceding column of rows with the same group number, calculate the table header area, and filter to generate a multi-level JSON array structure. S23. Traverse the table object and execute the getCell TypeMultiu cell data conversion method. For each row of data, use the string format conversion method and / or the date format conversion method and / or the numeric data format conversion method to convert the data type. S24. Write the Sheets objects of the table cells calculated in the above steps into the header fill area in sequence, and export the data as a blob binary stream to an Excel file.

Images