Method for processing data in Excel by using SQL (Structured Query Language)

Abstract

The invention provides a method for processing Excel data by using an SQL (Structured Query Language). The method comprises the following steps: receiving an Excel file, verifying a file type, creating an Excel object and creating a formula calculator; and by traversing Sheet objects in the Excel, converting Sheet object data with the name starting from # into a format suitable for SQL processing, and storing the Sheet object data. Meanwhile, SQL statements are extracted from the Sheet object with the name of (at) SQL, and the corresponding relation between the name of the result set and the SQL statements is established. After the SQL statement is executed, a result is stored in a memory. The method comprises the following steps of: firstly, establishing an Excel file, then searching cells marked with user-defined data labels in the Excel, rendering SQL query results into the cells, deleting Sheet objects which start from '#' and have the name of '(at) SQL', and finally generating and returning a new Excel file. According to the method, multiple sheets do not need to be specially created to process the data, a large amount of time and workload of the user are saved, and the processing efficiency is improved. And moreover, a result after SQL processing is supported to be returned to the user, and the user visually checks the processing result in the Excel.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, in particular to a method for processing data in Excel using SQL, which is particularly suitable for Excel application scenarios that require complex data merging, calculation and custom sorting. BACKGROUND

[0002] In application scenarios using Excel as a template, users often use Excel formulas and functions for data processing. SQL statements naturally support data merging, calculation and sorting operations, so in scenarios such as data merging and custom sorting, SQL statements are often used to query and process data in Excel. However, existing technologies generally use Excel formulas to process data, and the operations during data processing are complex. It is necessary to manually create multiple sheets to perform data merging and custom sorting processing, which is inefficient. It is also difficult to intuitively display a series of processed data in Excel. The process of updating and viewing the results is tedious and time-consuming. SUMMARY

[0003] To solve the above technical problems, the present application provides a method and software for processing data in Excel using SQL.

[0004] The present application aims to accurately and quickly display the data processed by SQL in Excel. The specific steps are as follows:

[0005] S1. Receive the Excel file input by the user and create an Excel object; specifically including the following sub-steps: S11: Receive the Excel file input by the user; S12: Verify the Excel file and type; S13: Create an Excel file into a file stream object; S14: Create a file stream object into a buffer stream object; S15: Create a buffer stream object into an Excel object; The Excel object performs processing operations on the Excel file; S16: Create a formula calculator according to the Excel object; The formula calculator parses the formula expression in the Excel cell, completes the calculation by associating the cell data in the Excel object, and returns the result.

[0006] S2. Loop through all Sheet objects in the Excel object and put them into a Map type collection named sheetMap; specifically including the following sub-steps: S21: Loop through all Sheet objects in the Excel object; Sheet object, i.e. worksheet, is an independent table page in the Excel object, each worksheet has a different name, located at the bottom label of the Excel object; S22: Store each Sheet object in the traversal in the Map type collection named sheetMap; Specifically, the Map type collection is a key-value pair storage structure, taking the name of the Sheet object as the key and the Sheet object as the value, and storing it in the Map type collection named sheetMap.

[0007] S3. Prepare data for SQL processing and put it into the Map type collection named preparedDataMap; specifically including the following sub-steps: The name of the Sheet object of the data for SQL processing is English starting with "#", and the first row of the Sheet object is the field name, used to identify the attribute of the column data, such as "product ID", "sales", etc., and the remaining rows are field values; S31: Loop through each key-value pair in sheetMap; S32: Loop through the non-first row Row objects of the Sheet objects in sheetMap whose keys start with "#", process the Cell objects of the Row objects one by one, and get the corresponding values; S33: Construct a List type collection named dataListMap according to the row and column information of the sheet object; The values of the sheetMap, i.e. the values of the Cell objects corresponding to the columns of the first row Row object of the Sheet object, are stored in the List type collection named dataListMap as keys, and the values of the Cell objects corresponding to the columns of all other rows Row objects are stored as values, the elements of the List type collection are Map; S34: Store the name of the Sheet object and dataListMap as key-value pairs in the Map type collection named preparedDataMap; S35: If the key of the sheetMap key-value pair does not start with "#", skip the key-value pair and continue looping; The sheet objects whose names start with "#" represent the data in these sheet objects as data for SQL processing.

[0008] S4. Create a result set name and SQL correspondence relationship and put it into the Map type collection named sqlMap; specifically including the following sub-steps: S41: Loop through each key-value pair in sheetMap; S42: Process the value of the key "@SQL" in sheetMap, extract the result set name and SQL statement and store them in the Map type collection named sqlMap; if the key of sheetMap is not "@SQL", skip the key-value pair and continue looping; If the key of the sheetMap key-value pair is equal to the string "@SQL", loop through the value of the key-value pair, which is a Sheet object, loop through each Row object of the Sheet object, and loop through each element Cell object of the Row object. According to the SQL text of the Cell object, obtain the result set name and SQL statement, and store them in the Map type collection named sqlMap with the result set name as the key and the SQL statement as the value. If the name of a Sheet object is equal to the string "@SQL", it indicates that the sheet object defines the SQL statement to be executed.

[0009] S5. Execute the SQL statements in sqlMap to get the results; specifically including the following sub-steps: S51: Traverse preparedDataMap, use the key as the CTE name, and use the content of the value to generate select...unionall... statements as CTE members to create CTE partial SQL statements; S52: Create a JDBC connection for H2 database based on in-memory mode; S53: Loop through each key-value pair in sqlMap; S54: Concatenate the CTE partial SQL statements with the SQL statements to form a complete query statement and execute it through the JDBC connection to get the results, and encapsulate the results into a List type collection named resultList; The SQL statement in step S54 is the value of the key-value pair in sqlMap; the resultList is a List type collection with Map elements; S55: Store the key-value pair in sqlMap in the Map type collection named resultDataMap with the key of the key-value pair as the key and the resultList as the value; S56: Close the JDBC connection.

[0010] S6. Get the Cell object of the data tag and store it in the List type collection named prepareCells; specifically including the following sub-steps: S61: Loop through all key-value pairs in sheetMap whose key does not start with "#" or whose key is not the string "@SQL"; S62: Find each Cell object in each Row object in each value of all key-value pairs in sheetMap whose key does not start with "#" or whose key is not the string "@SQL" as a custom formatted data tag, and store the Cell object in a List type collection named prepareCells; The custom formatted data tag is in the format of {{result set name. field name}}; The sheet object in sheetMap whose key does not start with "#" or whose key is not the string "@SQL" is the target location where the SQL query result needs to be rendered.

[0011] S7. Loop through the List type collection of prepareCells to render data into the Cell object; specifically including the following sub-steps: S71: Loop through all Cell objects in the List type collection of prepareCells; S72: Extract the result set name and field name from the text of the specified Cell object; The specified Cell object refers to the current Cell object being processed each time when looping through prepareCells; S73: Obtain the corresponding data to be rendered from resultDataMap according to the extracted result set name and field name, and store it in a List type collection named renderDataList whose elements are Object; S74: Obtain the row number and column number of the specified Cell object; S75: According to the row number, column number of the specified Cell object, and the number of elements in renderDataList, loop through the specified Cell object and obtain the Object element at the specified position in renderDataList; S76: Determine the type of the Object element and set the content of the specified Cell object; Determine the type of the Object element, then convert the Object element to the corresponding type of data according to the type of the Object element, and set the content of the specified Cell object to the corresponding type of data.

[0012] S8. Delete the sheet object in the Excel object whose name is "@SQL" or whose name starts with "#", generate an Excel file, and return the result; specifically including the following sub-steps: S81: Traverse each Sheet object in the Excel object, if the name of the sheet object is "@SQL" or starts with "#", delete the sheet object from the Excel object; S82: Convert the processed Excel object into a byte stream or file format, and close the Excel object; S83: Return the Excel file to the user.

[0013] Further, in step S12, the Excel file and type checking specifically includes non-empty check and magic number verification; the non-empty check specifically refers to checking whether the file passed in by the user in step S11 is empty; the magic number verification specifically refers to checking whether the magic number of the file passed in by the user in step S11 is the magic number corresponding to the Excel file.

[0014] Further, in step S3, the name of the Sheet object of the data to be executed SQL processing must start with "#" in English. And the first row of the Sheet object is the field name, and the remaining rows are field values.

[0015] Further, in step S4, the SQL text in the Sheet object with the name of the string "@SQL" is in the format "$result set name=SQL statement".

[0016] Further, in step S62, the data label format of the custom format is: {{result set name. field name}}; the field name is the field name of the SQL statement returned by the processed data.

[0017] Further, in step S5, for each key-value pair in the preparedDataMap collection, use the key as the CTE name, generate a select statement containing all field names and corresponding values for each element in the dataListMap collection, and then concatenate all select statements into CTE members, and concatenate the CTE name and CTE members into CTE partial SQL statements.

[0018] Further, in step S32, when traversing the non-first row Cell object of the Sheet object starting with "#", the Cell.getCellType() method is used to judge the type of the Cell object, for numerical type, the numerical value is directly obtained, for string type, the text is directly obtained, for formula type, the evaluate() method of the formula calculator created in step S16 is called to convert it into an actual value, and empty Cell objects need to be skipped to avoid empty keys or values when building the dataListMap collection.

[0019] Further, when screening the custom data label in step S62, the regular expression "\\{\\{(\\w+?)\\.(\\w+?)\\}\\}" is used to accurately match the format of "{{result set name.field name}}", wherein the first group captures the result set name, the second group captures the field name, and only the Cell object of the string type is matched to avoid invalid judgment on the Cell object of the numerical value, formula, etc. type, thereby improving the screening efficiency.

[0020] The method for processing data in Excel using SQL has the following advantages: by reading an Excel template file, parsing the custom format data label and SQL text, creating the data to be processed in the Excel template into a CTE statement, splicing the CTE statement with the SQL text into a complete SQL query statement and executing the SQL query statement, obtaining the result data, and rendering the result data to the data label and the specified position thereafter, the user can easily process complex operations such as data merging, custom sorting, and data calculation without manually creating multiple sheets for data merging and custom sorting, thereby saving a lot of time and effort of the user and improving the processing efficiency. In addition, the user can also intuitively display a series of data processed by SQL in Excel, support generating an Excel file after data processing, and return the result to the user, so that the user can directly see the result of data processing. BRIEF DESCRIPTION OF DRAWINGS

[0021] Figure 1 The method for processing data in Excel using SQL has the following advantages: by reading an Excel template file, parsing the custom format data label and SQL text, creating the data to be processed in the Excel template into a CTE statement, splicing the CTE statement with the SQL text into a complete SQL query statement and executing the SQL query statement, obtaining the result data, and rendering the result data to the data label and the specified position thereafter, the user can easily process complex operations such as data merging, custom sorting, and data calculation without manually creating multiple sheets for data merging and custom sorting, thereby saving a lot of time and effort of the user and improving the processing efficiency. In addition, the user can also intuitively display a series of data processed by SQL in Excel, support generating an Excel file after data processing, and return the result to the user, so that the user can directly see the result of data processing. Figure 2 The method for processing data in Excel using SQL has the following advantages: by reading an Excel template file, parsing the custom format data label and SQL text, creating the data to be processed in the Excel template into a CTE statement, splicing the CTE statement with the SQL text into a complete SQL query statement and executing the SQL query statement, obtaining the result data, and rendering the result data to the data label and the specified position thereafter, the user can easily process complex operations such as data merging, custom sorting, and data calculation without manually creating multiple sheets for data merging and custom sorting, thereby saving a lot of time and effort of the user and improving the processing efficiency. In addition, the user can also intuitively display a series of data processed by SQL in Excel, support generating an Excel file after data processing, and return the result to the user, so that the user can directly see the result of data processing. Figure 3 The method for processing data in Excel using SQL has the following advantages: by reading an Excel template file, parsing the custom format data label and SQL text, creating the data to be processed in the Excel template into a CTE statement, splicing the CTE statement with the SQL text into a complete SQL query statement and executing the SQL query statement, obtaining the result data, and rendering the result data to the data label and the specified position thereafter, the user can easily process complex operations such as data merging, custom sorting, and data calculation without manually creating multiple sheets for data merging and custom sorting, thereby saving a lot of time and effort of the user and improving the processing efficiency. In addition, the user can also intuitively display a series of data processed by SQL in Excel, support generating an Excel file after data processing, and return the result to the user, so that the user can directly see the result of data processing. Figure 4 The method for processing data in Excel using SQL has the following advantages: by reading an Excel template file, parsing the custom format data label and SQL text, creating the data to be processed in the Excel template into a CTE statement, splicing the CTE statement with the SQL text into a complete SQL query statement and executing the SQL query statement, obtaining the result data, and rendering the result data to the data label and the specified position thereafter, the user can easily process complex operations such as data merging, custom sorting, and data calculation without manually creating multiple sheets for data merging and custom sorting, thereby saving a lot of time and effort of the user and improving the processing efficiency. In addition, the user can also intuitively display a series of data processed by SQL in Excel, support generating an Excel file after data processing, and return the result to the user, so that the user can directly see the result of data processing. Figure 5 The method for processing data in Excel using SQL has the following advantages: by reading an Excel template file, parsing the custom format data label and SQL text, creating the data to be processed in the Excel template into a CTE statement, splicing the CTE statement with the SQL text into a complete SQL query statement and executing the SQL query statement, obtaining the result data, and rendering the result data to the data label and the specified position thereafter, the user can easily process complex operations such as data merging, custom sorting, and data calculation without manually creating multiple sheets for data merging and custom sorting, thereby saving a lot of time and effort of the user and improving the processing efficiency. In addition, the user can also intuitively display a series of data processed by SQL in Excel, support generating an Excel file after data processing, and return the result to the user, so that the user can directly see the result of data processing. DETAILED DESCRIPTION

[0022] To further understand the purpose, structure, features, and functions of the present application, the following embodiments are described in detail.

[0023] As Figures 1-5 shown, a method for processing data in Excel using SQL includes the following steps:

[0024] S1. Receiving the user's incoming Excel file and creating an Excel object. Specifically, the following sub-steps are included:

[0025] S11: Receiving the user's incoming Excel file; The Excel file supports file complete path, file input stream object, file object or file byte array form; the user uploads the Excel file in various ways, such as selecting a local Excel file, directly pasting the file content, etc. By receiving these different forms of file input, subsequent processing can be performed.

[0026] For example, the user selects an Excel file named "sales data.xlsx" for uploading, and receives the byte stream of this file.

[0027] S12: Verify the Excel file and type; Non-empty check: The verification of the Excel file is to verify whether the Excel file is null; Magic number verification: The type of the Excel file is verified to determine whether the magic number of the Excel file is correct; First, check if the Excel file is null, i.e. determine whether the user has successfully selected and uploaded the file. If the file is null, it means that no file has been transmitted, and the process ends with an error message.

[0028] Then, determine whether the file type is correct by checking the magic number. Magic number is a fixed sequence of specific bytes in the file header, and different file formats have different magic numbers to determine the actual type of the file. For example, the magic number of an Excel file (.xls) is "D0CF11E0A1B11AE1", and the magic number of an Excel file (.xlsx) is "504B0304 [PK]".

[0029] The non-empty check can avoid subsequent null pointer exceptions caused by "no file transmission"; the magic number verification can accurately identify the file type and prevent non-Excel files (such as.txt,.docx) from entering the processing flow, ensuring the safety and stability of file processing.

[0030] When the verification content and the type of verification are passed, proceed to step S13.

[0031] S13: Create an Excel file into a file stream object;

[0032] Convert the verified Excel file into a file stream object, which points to the beginning of the file and starts reading the file content byte by byte, facilitating subsequent sequential reading and manipulation of the file content.

[0033] S14: Create a file stream object into a buffer stream object;

[0034] In order to improve the efficiency of reading and writing, the file stream object is further converted into a buffer stream object. The buffer stream object uses an internal buffer to read or write multiple bytes at a time, reducing the number of disk I / O operations. When reading file content, the buffer stream object first reads a block of data from the file into the buffer, and then reads data from the buffer byte by byte, rather than directly from the file each time, thereby improving processing speed.

[0035] S15: Create a buffer stream object into an Excel object;

[0036] The buffer stream object is parsed by Apache POI to generate an operable Excel object.

[0037] The Excel object is the core carrier for interacting with the Excel file. After converting the stream data into a structured object model, various operations on the Excel file can be performed, such as reading the worksheet, cell data, etc.

[0038] S16: Create a formula calculator according to the Excel object;

[0039] The formula calculator is created and initialized to process formula calculations in Excel. The formula calculator parses the formula expression in the Excel cell, completes the calculation by associating the cell data in the Excel object, and returns the result.

[0040] First, introduce the ApachePOI library, which is specifically used to process Excel files and provides a rich API to manipulate various elements of Excel files, including cell formulas; Next, create a formula calculator for the sheet object according to the Excel object by calling the FormulaEvaluator class provided by ApachePOI. This is achieved by creating an instance of the sheet object and calling its getFormulaEvaluator() method; Finally, use the evaluate method of the formula calculator to pass in the cell object that needs to be calculated, and calculate the actual value of the formula in the cell. The evaluate method automatically calculates based on various functions and operations in the formula and returns the calculation result.

[0041] Step S1 is to read the user-provided Excel file into the program and create an Excel object for subsequent operations on the Excel file, such as reading the worksheet, cell data, etc.

[0042] S2. Loop through all Sheet objects in the Excel object and put them into a Map type collection named sheetMap. Specifically, the following sub-steps are included:

[0043] S21: Loop through all Sheet objects in the Excel object;

[0044] Access each Sheet object in the Excel object one by one. Sheet object, i.e. worksheet, is an independent table page in the Excel file, each worksheet has a different name, located at the bottom of the label in the Excel file.

[0045] For example, in "Sales Data.xlsx", there are four sheet objects, namely "#Sales_records", "#Employee_Information", "Segment_performance" and "@SQL", then access these four sheet objects in turn.

[0046] S22: Store each Sheet object in the traversal in a Map type collection named sheetMap;

[0047] Store each Sheet object in a Map type collection named sheetMap. Through this Map type collection, the corresponding value (Sheet object) can be quickly obtained through the key (Sheet object name).

[0048] For example, store the sheet object with the name "#Sales_records" in sheetMap, the key is "#Sales_records" and the value is the corresponding Sheet object. When accessing the sheet object with the name "#Sales_records", it can be directly obtained from sheetMap through the key "#Sales_records".

[0049] Step S2 obtains all sheet objects in the Excel file and stores them in a Map type collection named sheetMap. Map type collection is a key-value storage structure, here the name of sheet object is the key and sheet object is the value, which facilitates subsequent quick access to the corresponding sheet object through the name of sheet object.

[0050] S3. Prepare data for SQL processing and put it into a Map type collection named preparedDataMap. Specifically, the following sub-steps are included:

[0051] S31: Loop through each key-value pair in sheetMap.

[0052] Get the set of all sheetMap keys through the keySet method, loop through the key-value pairs, and process each Sheet object in sheetMap key-value pair by pair, that is, each sheet object.

[0053] S32: Loop through all the Row objects of the Sheet object whose key starts with "# " in sheetMap, process the Cell objects of Row objects one by one, and get the corresponding value;

[0054] Further, the name of the Sheet object of the data to be executed SQL processing is English starting with "#". And the first line of the Sheet object is the field name, and the subsequent lines are field values.

[0055] If the key of the sheetMap key-value pair starts with "#", loop through the value of the key-value pair, that is, the Sheet object, from the second row (index 1) to the last row (get by getLastRowNum method) Loop through the Row objects, loop through each Cell object of the Row object, first determine the Cell object type through the Cell.getCellType() method, and according to the type of the Cell object, get the value of the Cell object corresponding to the type. Cell object, that is, the cell object in Excel, is the most basic object element in Excel. In particular, if the type of the Cell object is formula type, use the formula calculator to calculate the formula to the actual value.

[0056] For the Sheet object whose name starts with "#", it means that the data in this sheet object is the data to be executed SQL processing. Traverse the Row objects from the second row to the last row of this Sheet object (the first row is the field name row), and then process each Cell object in each Row object. According to the type of the Cell object, get its corresponding type value. If the Cell type is formula type, use the formula calculator to calculate its actual value.

[0057] Assuming that the first line of the sheet object with the name "#Sales_records" is the field name, then from the second line, traverse each line. There is a formula "=A2*B2" in a certain cell, and the formula calculator calculates the actual value of the formula (such as 100*5=500).

[0058] S33: According to the row and column information of the sheet object, construct a list type collection named dataListMap;

[0059] The values of the Cell objects corresponding to the columns of the first Row object (field name row) of the Sheet object are stored in a List type collection named dataListMap, with the values of the Cell objects corresponding to the columns of the other Row objects as values. The elements of the List type collection are Maps.

[0060] For example, the values of the Cell objects corresponding to the columns of the first Row object of "#Sales_records" are "product_id", "sales_quantity", and "unit_price"; the values of the Cell objects corresponding to the columns of the second Row object are "P001", 100, and 50; the values of the Cell objects corresponding to the columns of the third Row object are "P002", 200, and 30; and the values of the Cell objects corresponding to the columns of the fourth Row object are "P003", 300, and 20.

[0061] The generated dataListMap collection is: {"product_id":"P001","sales_quantity":100,"unit_price":50}, {"product_id":"P002","sales_quantity":200,"unit_price":30}, {"product_id":"P003","sales_quantity":300,"unit_price":20} ]。

[0062] The dataListMap collection is constructed to provide a structured data format for subsequent generation of CTE statements, ensuring that SQL can identify and process the data.

[0063] S34: Store the name of the Sheet object and dataListMap as key-value pairs in a Map collection named preparedDataMap;

[0064] Add the above key-value pairs to the Map type collection of preparedDataMap, with the name of the Sheet object as the key and dataListMap as the value. When traversing preparedDataMap later, the corresponding data row collection can be quickly located through the Sheet object name.

[0065] For example, add the following key-value pairs to preparedDataMap: {"#Sales_records":[ {"product_id":"P001","sales_quantity":100,"unit_price":50}, {"product_id":"P002","sales_quantity":200,"unit_price":30}, {"product_id":"P003","sales_quantity":300,"unit_price":20} ]}.

[0066] The preparedDataMap contains all the data to be processed by SQL. When generating CTE statements later, the corresponding data list can be quickly obtained directly by the name of the Sheet object, without having to repeatedly traverse the Excel object, and it is also convenient to trace the source of the data.

[0067] S35: If the key of a sheetMap key-value pair does not start with "#", skip the key-value pair and continue looping.

[0068] If the name of a sheet object does not begin with "#", it means that the data in that sheet object will not be processed by SQL. Skip that sheet object and continue processing the next sheet object.

[0069] For example, if the names of the sheet objects "Segment_performance" and "@SQL" do not begin with "#", then the sheet object will be skipped and no data will be processed on it.

[0070] Step S3 essentially filters out sheet objects whose names begin with "#", as these sheet objects contain data to be processed by the SQL query. For each matching sheet object, starting from the second row, each row's cells are traversed, the cell values ​​are retrieved, and they are converted into a data format suitable for SQL processing (i.e., a List of key-value pairs). During this process, if a cell contains a formula, it is calculated into its actual value using a formula calculator. Finally, this data is stored in a preparedDataMap collection. The purpose of this step is to extract the data to be processed by the SQL query and convert it into a format suitable for SQL operations.

[0071] S4. Create the mapping between result set names and SQL statements, and store them in a Map type collection named sqlMap. This includes the following sub-steps:

[0072] S41: Loop through each key-value pair in the sheetMap;

[0073] Get the set of all sheetMap keys by the keySet method, and loop through the key-value pairs.

[0074] For example, traverse the key-value pairs of the four sheet objects "#Sales_records", "#Employee_Information", "Segment_performance" and "@SQL" again.

[0075] S42: Process the value of the key "@SQL" in sheetMap, extract the result set name and SQL statement and store them in the Map type collection named sqlMap; if the key of sheetMap is not "@SQL", skip the key-value pair and continue the loop;

[0076] If the key of the sheetMap key-value pair is equal to the string "@SQL", loop through the value of the key-value pair, that is, the Sheet object, loop through each Row object of the Sheet object, and loop through each element Cell object of the Row object. According to the SQL text of the Cell object, obtain the result set name and SQL statement through a regular expression, and store them in the Map type collection named sqlMap with the result set name as the key and the SQL statement as the value. Among them, the SQL text format is: $result set name = SQL statement.

[0077] If the name of a Sheet object is equal to the string "@SQL", it indicates that this sheet object defines the SQL statement to be executed. Traverse each Cell object in this sheet object and extract the result set name and SQL statement through a regular expression.

[0078] For example, in the "@SQL" sheet object, the content of a certain cell is "$product_summary = SELECT product_id, SUM(sales_quantity * unit_price) AS total_sales FROM Sales_records GROUP BY product_id", After extracting by regular expression, the result set name is product_summary, and the corresponding SQL statement is SELECT product_id, SUM(sales_quantity * unit_price) AS total_sales FROM Sales_records GROUP BY product_id, and they are stored in sqlMap.

[0079] The purpose of step S4 is to extract the SQL statement from a sheet object (equal to the string "@SQL") that defines a special SQL statement, which defines the SQL statement to be executed. The result set name and the corresponding SQL statement are extracted from the cells of the sheet object by regular expression and stored in the sqlMap collection. This step is to obtain the subsequent SQL statements to be executed and associate them with the result set name, in preparation for subsequent execution of the SQL statement.

[0080] S5. Execute the sql statements in sqlMap to get the results. Specifically, it includes the following sub-steps:

[0081] S51: Traverse preparedDataMap, use key as CTE name, use the content of value to generate select...unionall... statement as CTE member, splice CTE name and CTE member into CTE part SQL statement; Traverse the keySet() of preparedDataMap, for each key corresponding dataListMap, generate select statement according to the element order of List type collection (i.e. the row order in the original Excel), and splice it into CTE member through unionall;

[0082] Suppose preparedDataMap is {"#Sales_records": [ {"product_id":"P001","sales_quantity":100,"unit_price":50}, {"product_id":"P002","sales_quantity":200,"unit_price":30}, {"product_id":"P003","sales_quantity":300,"unit_price":20} ]}, splice into CTE part statement: WITH Sales_records AS ( SELECT "P001" AS product_id, 100 AS sales_quantity, 50 AS unit_price UNION ALL SELECT "P002" AS product_id, 200 AS sales_quantity, 30 AS unit_price UNION ALL SELECT "P003" AS product_id, 300 AS sales_quantity, 20 AS unit_price ) CTE (Common Table Expression) is a temporary result set used in SQL queries. After converting Excel data into a CTE, there is no need to create a physical table in the database. The CTE can be directly combined with user-defined SQL statements for execution, reducing the redundancy of creating or deleting database tables and improving SQL readability and execution efficiency.

[0083] S52: Create a JDBC connection for the H2 database in memory mode.

[0084] In the project configuration, import the JDBC driver dependency of H2 database to ensure that the connection utility class of H2 can be called. The connection URL of H2 database needs to specify the "memory mode" and the database name. Load the H2 driver and create a JDBC connection with the database to enable the execution of SQL statements.

[0085] This step uses H2 in-memory database to execute SQL based on memory throughout, without disk storage. Data only exists in memory, which ensures fast execution and avoids disk IO consumption. CTE temporary tables do not fall to disk, and data is automatically cleared after closing the JDBC connection, without any disk residue. There is no need to deploy a database service, and the single machine can run, which is suitable for lightweight scenarios such as personal office and small and micro enterprises. It also avoids the risk of sensitive data such as insurance customer information and financial reimbursement data being leaked due to disk storage. JDBC connection is a standard interface for executing SQL statements, ensuring that Excel data can be processed through SQL.

[0086] S53: Loop through each key-value pair in sqlMap.

[0087] Iterate through the keySet() of sqlMap, and process each SQL statement key-value pair in the order of key (result set name) insertion.

[0088] S54: Concatenate the CTE part of the SQL statement with the SQL statement to form a complete query statement and execute it through the JDBC connection to get the result. Encapsulate the result into a List type collection named resultList.

[0089] The CTE part SQL statement is spliced with the SQL statement (i.e. the value of the key-value pair in sqlMap) to form a complete SQL query statement, and the returned result ResultSet object is obtained by executing the complete SQL query statement through JDBC connection. The data in the returned result ResultSet object is encapsulated into a List type collection with the element Map named resultList.

[0090] For example, after the statement "WITH Sales_records AS ( SELECT "P001" AS product_id, 100 AS sales_quantity, 50 AS unit_priceUNION ALL SELECT "P002" AS product_id, 200 AS sales_quantity, 30 AS unit_priceUNION ALL SELECT "P003" AS product_id, 300 AS sales_quantity, 20 AS unit_price ) SELECT product_id, SUM(sales_quantity * unit_price) AS total_salesFROM Sales_records GROUP BY product_id" is executed, the following three rows of data are obtained in the ResultSet object: product_id is "P001", total_sales is 5000 (100*50); product_id is "P002", total_sales is 6000 (200*30); product_id is "P003", total_sales is 6000 (300*20). Then the three rows of data are encapsulated into three Maps and added to the resultList, and the resultList is as follows: [ {"product_id":"P001","total_sales":5000}, {"product_id":"P002","total_sales":6000}, {"product_id":"P003","total_sales":6000} ]。

[0091] The stitching of CTE and SQL ensures that the SQL statement can be executed based on the Excel data, and the ResultSet is encapsulated as resultList to facilitate subsequent quick extraction and rendering of data through the result set name and field name, avoiding the risk of resource leakage caused by direct operation of ResultSet.

[0092] S55: Store the key-value pair in the sqlMap as the key of the Map type collection named resultDataMap, and the resultList as the value;

[0093] resultDataMap is a Map type collection used to store the results of SQL queries. Its role is to associate the results of each SQL query with the corresponding result set name, so that subsequent query results can be quickly obtained according to the result set name, providing convenience for subsequent operations such as data rendering.

[0094] Suppose the key of a key-value pair in sqlMap is product_summary, and the corresponding SQL query is executed to obtain the following resultList: [ {"product_id":"P001","total_sales":5000}, {"product_id":"P002","total_sales":6000}, {"product_id":"P003","total_sales":6000} ] At this time, the resultDataMap will store the following key-value pairs: { "product_summary":[ {"product_id":"P001","total_sales":5000}, {"product_id":"P002","total_sales":6000}, {"product_id":"P003","total_sales":6000} ] } resultDataMap collects all SQL execution results, and subsequent data rendering is directly located through the result set name; This step associates the execution result of the current SQL with the result set name and stores it, such as storing "product_summary" as the key and resultList as the value in resultDataMap. resultDataMap centrally manages all SQL execution results, and when rendering data subsequently, the data is directly located through the result set name.

[0095] S56: Close the JDBC connection;

[0096] Close the connection with the database, release resources, and avoid connection leakage.

[0097] Step S5 is to generate the CTE part of the SQL statement from the previously prepared data (located in preparedDataMap), then splice it with the SQL statement in sqlMap to form a complete SQL query statement and execute it. After executing the SQL statement, the query result is encapsulated into a List type collection and stored in resultDataMap. Finally, the database connection is closed. This step is the core of the entire process, which processes data by executing SQL statements and obtains the processed results.

[0098] S6. Get the Cell object of the data tag and store it in the List type collection named prepareCells. The specific steps include:

[0099] S61: Loop through all key-value pairs in sheetMap whose keys do not start with "#" or whose keys are not the string "@SQL"; Get the set of all sheetMap keys through the keySet method and loop through the key-value pairs. If the key of the sheetMap key-value pair starts with "#" or the name is "@SQL", skip this key-value pair and continue looping.

[0100] S62: Find each Cell object of each Row object in the value of each key-value pair in sheetMap whose keys do not start with "#" or whose keys are not the string "@SQL" as the Cell object of the custom format data tag, and store the Cell object in the List type collection named prepareCells;

[0101] For the key of the sheetMap key-value pair, if it does not start with "#" or the key is not a string "@SQL", the corresponding value, i.e. the sheet object, is the target position where the SQL query result needs to be rendered. Then, the values of the sheetMap key-value pair are iterated, and each Row object is iterated. Each element Cell object of the Row object is iterated. If the type of the Cell object is a string type, the text string of the Cell object is obtained. It is judged by a regular expression "\\{\\{(\\w+?)\\.(\\w+?)\\}\\}" whether the text string is a custom format data tag (the format is: {{result set name. field name}). If yes, the Cell object is stored in a List type collection named prepareCells. The field name is the field name of the returned result of the SQL statement for processing data.

[0102] For example, in the sheet object named "Segment_performance", the content of a Cell is "{{product_summary.product_id}}". It is matched by a regular expression that it is a custom data tag, and the Cell object is added to the prepareCells list.

[0103] Step S6 is to find the cells marked with special data tags (custom formats) in the sheet object of Excel, and store these cells in the prepareCells list. These cells are the positions where the SQL query result needs to be rendered subsequently. The purpose of this step is to determine where the SQL query result is placed in Excel.

[0104] S7. The List type collection of prepareCells is iterated, and data is rendered into the Cell object. Specifically, the following sub-steps are included:

[0105] S71: All Cell objects in the List type collection of prepareCells are iterated. The prepareCells list is iterated, and each Cell object is processed. The prepareCells stores all Cell objects in Excel that need to be filled with SQL results. Through the iteration of the custom data tag cell collection, subsequent data filling is completed.

[0106] S72: The result set name and the field name are extracted from the text of the specified Cell object.

[0107] The text string of the specified Cell object is obtained, and the result set name and the field name are extracted by a regular expression.

[0108] The specified Cell object refers to the current Cell object processed each time when iterating through prepareCells.

[0109] This step determines which part of the data to be obtained from resultDataMap and the specific field values to be rendered; the data source and target field are parsed, providing accurate positioning for subsequent data acquisition and rendering.

[0110] For example, for "{{product_summary.product_id}}", the result set name "product_summary" and the field name "product_id" are extracted.

[0111] S73: According to the extracted result set name and field name, the corresponding data to be rendered is obtained from resultDataMap and stored in the element named renderDataList, which is an Object List type collection;

[0112] According to the result set name and field name, the data to be rendered is obtained from resultDataMap and stored in the List type collection named renderDataList. The element type of List is Object type.

[0113] The value of resultDataMap is stored in a List type collection. According to the element order of the collection in resultDataMap, that is, the return order of the SQL execution result, the corresponding data is extracted and stored in a List type collection named renderDataList. The element type of List is Object type.

[0114] This step obtains the query result data corresponding to the result set name from resultDataMap, and extracts the specific value according to the field name to form a list containing all related data. This list will be used for subsequent data rendering operations to ensure that each target cell can obtain the correct data value.

[0115] For example, the result set name "product_summary" and the field name "product_id" are extracted in step S72, and then the value List corresponding to "product_summary" is obtained from resultDataMap in this step, assuming it is [{"product_id":"P001","total_sales":5000},{"product_id":"P002","total_sales":6000},{"product_id":"P003","total_sales":6000}]. If the custom data tag being processed is "{{product_summary.product_id}}" and it is the first matching item, the "product_id" field value of the first row is extracted from the List, and renderDataList is ["P001"]. If the custom data tag is "{{product_summary.total_sales}}" and the first two items are to be obtained, the "total_sales" field values of the first two rows are extracted, and renderDataList is [5000, 6000].

[0116] S74: Obtain the row number and column number of the specified Cell object;

[0117] The Excel processing library is called to determine the specific position of the specified Cell object in the Excel table, i.e., the row number and the column number, so that the data can be accurately rendered to the corresponding position subsequently.

[0118] S75: According to the row number, column number of the specified Cell object, and the number of elements in renderDataList, loop through the specified Cell object and obtain the Object element at the specified position of renderDataList;

[0119] According to the row number, column number of the specified Cell object, and the number of elements in renderDataList, and obtaining the corresponding elements from renderDataList, they are written into the specified Cell object in turn, which is implemented as follows: The row number of the current specified Cell object is obtained from step S74, denoted as startRow, and the column number is denoted as fixedCol. The number of elements in the renderDataList set generated in step S73 is obtained, denoted as N.

[0120] For the element with index i (i takes values in the range 0≤i<N) in renderDataList, the target Cell object is located according to the following rules: targetRow = startRow + i, with the row index of the Cell object as the starting reference, and the data index is incremented to ensure that the data is arranged vertically continuously; targetCol = fixedCol, fixed as the column index of the Cell object to avoid horizontal offset of the data; For example, when startRow = 3, fixedCol = 2, and N = 2, the element with index i = 0 corresponds to the target coordinates (3, 2), and the element with index i = 1 corresponds to the target coordinates (4, 2).

[0121] Further, when locating the target Cell object, if the target row targetRow does not exist, the Sheet.createRow(targetRow) method of the Excel processing library Apache POI is called to dynamically create a blank row; if the target row already exists but the target column targetCol does not exist, the Row.createCell(targetCol) method is called to dynamically create a blank column, ensuring that each target Cell object is in a valid writable state.

[0122] Finally, the renderDataList is traversed, and the Object element corresponding to renderDataList.get(i) is bound to the target Cell object at coordinates (startRow + i, fixedCol) in order, i.e., the element with index i = 0 matches the Cell at (3, 2), and the element with index i = 1 matches the Cell object at (4, 2).

[0123] This step dynamically calculates the target Cell coordinates, and if the number of rows is insufficient, a new row is created, and if the number of columns is insufficient, a new column is created, so the user does not need to plan the number of rows of the Excel template in advance.

[0124] S76: Determine the type of the Object element and set the content of the specified Cell object;

[0125] If it is an integer type, the Object element is forcibly converted to an integer data, and the content of the specified Cell object is set to the integer data.

[0126] If it is a double type, the Object element is forcibly converted to a double data, and the content of the specified Cell object is set to the double data.

[0127] If it is a single precision type, the Object element is forcibly converted to a single precision data, and the content of the specified Cell object is set to the single precision data.

[0128] If the type is BigDecimal, the Object element is cast to double data, and the content of the specified Cell object is set to double data.

[0129] If the type is date, the Object element is cast to date data, and the content of the specified Cell object is set to date data.

[0130] Otherwise, the Object element is converted to string data, and the content of the specified Cell object is set to string data.

[0131] Excel cells have specific storage and display methods for different types of data. For example, date type data needs to be displayed in a specific date format, and numerical type data may involve decimal places and other format issues. By casting, it is ensured that the data is written to the cell in the correct type, so that it is displayed and stored correctly in Excel. For example, after converting the data to an integer type, it is written to the cell, so that the data in the cell is identified as an integer in Excel, and operations such as alignment, calculation, etc. are performed according to the integer format; Excel displays different types of data differently, and type conversion avoids display abnormalities caused by mismatched data types, improving the practicality of the results. This step completes the adaptation of the data type to the Excel cell format, writes the result data (stored in resultDataMap) to the target Cell object, and realizes data visualization rendering.

[0132] The purpose of step S7 is to associate the result data obtained by executing the SQL statement previously (stored in resultDataMap) with the pre-set custom data label in Excel, to realize accurate mapping and format adaptation of the result data to the Excel cell. According to the different types of data, the corresponding conversion is performed to ensure that the data can be correctly displayed in the cell, so as to actually fill the result data of SQL into the cells of Excel, and complete the visualization of data.

[0133] S8. Delete the sheet object with the name "@SQL" or starting with "#" in the Excel object, generate the Excel file, and return the result. Specifically, the following sub-steps are included:

[0134] S81: Traverse each Sheet object in the Excel object, and if the name of the sheet object is "@SQL" or starts with "#", delete the sheet object from the Excel object; Traverse all Sheet objects in the order of the index of the Sheet object in the Excel object, and determine whether the name starts with "#" or the name is "@SQL". If the conditions are met, delete it through the removeSheetAt(index) method. After deletion, the index of the subsequent Sheet object is automatically updated, and the remaining Sheet objects need to be traversed again.

[0135] Sheet objects with names starting with "@SQL" or "#" are "temporary work areas" for processing data. Sheet objects with names starting with "#" are used to store raw data to be processed by SQL, containing a large amount of unprocessed basic data, which is redundant information for users. Sheet objects with names "@SQL" are used to define SQL statements, which are technical implementation details and do not need to be concerned by ordinary users. After deleting these Sheet objects, the final returned Excel file only retains the result display Sheet object that users need, avoiding user interference from irrelevant information and directly presenting the processing result.

[0136] S82: Convert the processed Excel object into a byte stream or file form, and close the Excel object; The processed Excel object is generated as an Excel file, which supports byte stream, file complete path, file object, and file output stream object forms. The processed Excel object is converted into a byte stream, or different forms of Excel files such as file complete path, file object, and file output stream object are generated according to user needs. Close the Excel object to release resources.

[0137] S83: Return the Excel file to the user; The generated Excel file (byte stream or other forms) is returned to the user, who saves it to the local or other storage location.

[0138] For example, after the user clicks the download button on the webpage, the generated Excel file byte stream is returned as an HTTP response to the user's browser, prompting the user to save the file as "processed sales data.xlsx". The file is returned to the user.

[0139] Step S8 deletes unnecessary Sheet objects, converts the processed Excel object into a byte stream or file form, and returns it to the user. The user can save the returned file as "processed sales data.xlsx" and view the data processed by SQL, such as displaying "technical department" at the custom data label.

[0140] The present application has been described by the above-mentioned related embodiments, however, the above-mentioned embodiments are only examples for implementing the present application. It must be pointed out that the disclosed embodiments do not limit the scope of the present application. On the contrary, changes and modifications made without departing from the spirit and scope of the present application are within the scope of the patent protection of the present application.

Claims

1. A method for processing data in Excel using SQL, characterized in that, Comprise the following steps: S1. Receive the user incoming Excel file, create into Excel object; Specifically includes the following sub-steps: S11: Receive the user incoming Excel file; S12: Check the Excel file and type; S13: Create an Excel file into a file stream object; S14: Create a file stream object into a buffer stream object; S15: Create a buffer stream object into an Excel object; S16: According to the Excel object, create formula calculator; The formula calculator parses the formula expression in the Excel cell, completes the calculation by associating the cell data in the Excel object and returns the result; S2. Loop through all Sheet objects in the Excel object, put into the Map type collection named sheetMap; Specifically includes the following sub-steps: S21: Loop through all Sheet objects in the Excel object; Sheet object, namely worksheet, is an independent table page in Excel object, each worksheet has different name, located at the bottom label of Excel object; S22: Store each Sheet object in the sheetMap Map type collection; Specifically, the Map type collection is a key-value storage structure, taking the name of Sheet object as the key and the Sheet object as the value, and storing it in the Map type collection named sheetMap; S3. Prepare data for SQL processing, put into the Map type collection named preparedDataMap; Specifically includes the following sub-steps: The name of Sheet object for SQL processing data starts with "#” in English, and the first line of Sheet object is field name, and the remaining lines are field values; S31: Loop through each key-value pair in sheetMap; S32: Loop through all non-first-line Row objects of Sheet objects whose keys start with "#" in sheetMap, process Cell objects of Row objects one by one, and get the corresponding values; S33: According to the row and column information of sheet object, construct the List type collection named dataListMap; Take the value of sheetMap, namely the value of Cell object corresponding to the first row Row object of Sheet object, as the key, and the value of Cell object corresponding to the column of all other rows Row object as the value, and store it in the List type collection named dataListMap, the element of List type collection is Map; S34: Take the name of Sheet object and dataListMap as key-value pair, and store it in the Map type collection named preparedDataMap; S35: If the key of sheetMap key-value pair does not start with "#”, skip the key-value pair and continue to loop through; The sheet object whose name starts with "#" represents the data in these sheet objects as data for SQL processing. S4. Create a result set name and SQL correspondence, and put it into a Map type collection named sqlMap; specifically including the following sub-steps: S41: Loop through each key-value pair in sheetMap; S42: Process the value of the key "@SQL" in sheetMap, extract the result set name and SQL statement and store them in the Map type collection named sqlMap; if the key of sheetMap is not "@SQL", skip the key-value pair and continue looping; If the key of the sheetMap key-value pair is equal to the string "@SQL", loop through the values of the key-value pair, i.e. the Sheet object, loop through each Row object of the Sheet object, and loop through each element Cell object of the Row object, according to the SQL text of the Cell object, obtain the result set name and SQL statement, and store them in the Map type collection named sqlMap with the result set name as the key and the SQL statement as the value; S5. Execute the sql statements in sqlMap to get the results; specifically including the following sub-steps: S51: Traverse preparedDataMap, use the key as the CTE name, and use the content of the value to generate select...unionall... statements as CTE members to create CTE part SQL statements; S52: Create a H2 database JDBC connection based on the in-memory mode; S53: Loop through each key-value pair in sqlMap; S54: Concatenate the CTE part SQL statements with the SQL statements to form a complete query statement and execute it through the JDBC connection to get the results, and encapsulate the results into a List type collection named resultList; The SQL statement in step S54 is the value of the key-value pair in sqlMap; the resultList is a List type collection with Map elements; S55: Store the key-value pair in sqlMap in the Map type collection named resultDataMap with the key as the key and the resultList as the value; S56: Close the JDBC connection; S6. Get the Cell object of the data tag and store it in the List type collection named prepareCells; specifically including the following sub-steps: S61: Loop through all key-value pairs in sheetMap whose keys do not start with "#" or are not the string "@SQL"; S62: Find each Cell object of each Row object in the values of all key-value pairs in sheetMap whose keys do not start with "#" or are not the string "@SQL" as the Cell object of the custom format data tag, and store the Cell object in the List type collection named prepareCells; The custom format data tag has the format {{result set name. field name}}; S7. Loop through the List type collection of prepareCells, render data into the Cell object; Specifically, including the following sub-steps: S71: Loop through all Cell objects in the List type collection of prepareCells; S72: Extract the result set name and field name from the specified Cell object text; The specified Cell object refers to the current Cell object processed each time when looping through prepareCells; S73: Obtain the corresponding data to be rendered from resultDataMap according to the extracted result set name and field name, and store it in the element of the Object type collection named renderDataList; S74: Obtain the row number and column number of the specified Cell object; Determine the specific position of the specified Cell object in the Excel table, i.e. the row number and column number; S75: According to the row number, column number of the specified Cell object and the element quantity of renderDataList, loop through the specified Cell object and obtain the Object element at the specified position of renderDataList; S76: Determine the type of the Object element and set the content of the specified Cell object; Determine the type of the Object element, then convert the Object element to the corresponding type of data according to the type of the Object element, and set the content of the specified Cell object to the corresponding type of data; S8. Delete the sheet object with the name "@SQL" or starting with "#" in the Excel object, generate an Excel file, and return the result; Specifically, including the following sub-steps: S81: Traverse each Sheet object in the Excel object, if the name of the sheet object is "@SQL" or starts with "#", delete the sheet object from the Excel object; S82: Convert the processed Excel object into a byte stream or file form, and close the Excel object; S83: Return the Excel file to the user.

2. The method of claim 1, wherein, In step S42, according to the SQL text of the Cell object, specifically through regular expression to extract the result set name and corresponding SQL statement from the Cell object, and store them in the Map type collection named sqlMap with the result set name as the key and the corresponding SQL statement as the value, establish the corresponding relationship between them; Wherein, the SQL text format is: $result set name = SQL statement.

3. The method of claim 1, wherein, In step S5, the CTE part SQL statement is created, including: for each key-value pair in the preparedDataMap set, taking the key as the CTE name, generating a select statement containing all field names and corresponding values of each element in the dataListMap set, and then splicing all select statements into a CTE member, and splicing the CTE name and the CTE member into the CTE part SQL statement.

4. The method of claim 1, wherein, In step S32, if the Cell object is of the formula type, the formula calculator created in step S16 is called to process the Cell object of the formula type, and the Cell object of the formula type is calculated to obtain the actual value corresponding to the formula.

5. The method of claim 1, wherein, In step S6, only for the Sheet objects in the sheetMap whose keys do not start with "#" or whose keys are not the string "@SQL", all Row objects and Cell objects of the Sheet objects are traversed, if the Cell object is of the string type, the text string thereof is obtained, and whether the text string matches the format "{{result set name.field name}}" is matched through a regular expression, if the match is true, the Cell object is stored in the prepareCells set.

6. The method of claim 1, wherein, In step S76, the specific process of judging the Object element type and setting the content of the specified Cell object is as follows: If the Object element is of the integer type, the content of the specified Cell object is set to the integer data obtained by forcibly converting the Object element; If the Object element is of the double type, the content of the specified Cell object is set to the double data obtained by forcibly converting the Object element; If the Object element is of the single type, the content of the specified Cell object is set to the single data obtained by forcibly converting the Object element; If the Object element is of the BigDecimal type, the content of the specified Cell object is set to the double data obtained by forcibly converting the Object element; If the Object element is of the date type, the content of the specified Cell object is set to the date data obtained by forcibly converting the Object element; If the Object element is not of the above types, the content of the specified Cell object is set to the string data obtained by converting the Object element.

7. The method of claim 1, wherein, In step S12, the Excel file and type are checked, including non-empty checking and magic number verification; the non-empty checking specifically refers to checking whether the file input by the user in step S11 is empty; the magic number verification specifically refers to checking whether the magic number of the file input by the user in step S11 is the magic number corresponding to the Excel format.

8. The method of claim 1, wherein, In step S8, the final generation of the new Excel file is to delete the sheet objects in the Excel object whose names start with "#” or whose names are "@SQL”, and only keep the file of the sheet objects for displaying data and results; the final generated Excel file supports the form of byte stream, file complete path, file object or file output stream object, and is returned to the user.

Images