A method for processing data in Excel using SQL

Abstract

The application provides a method for processing Excel data using SQL. By receiving an Excel file, checking the file type, creating an Excel object and creating a formula calculator. By traversing the Sheet object in Excel, converting the Sheet object data with the name starting with "#" to a format suitable for SQL processing and storing. At the same time, extract the SQL statement from the Sheet object with the name "@SQL", establish the corresponding relationship between the result set name and the SQL statement. After executing the SQL statement, store the result in the memory. Then find the cells marked with custom data tags in Excel, render the SQL query result to these cells, delete the Sheet object with the name starting with "#" and the name "@SQL", and finally generate and return a new Excel file. This method does not need to specially create multiple sheets to process data, saves a lot of time and workload for users, and improves processing efficiency. Moreover, it supports returning the result of SQL processing to the user, and the user can intuitively view the processing result in Excel.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method for processing data in Excel using SQL, which is especially suitable for Excel application scenarios that require complex data merging, calculation and custom sorting. Background Technology

[0002] In applications using Excel as a template, users often utilize Excel formulas and functions for data processing. SQL statements naturally support data merging, calculation, and sorting operations. Therefore, in scenarios involving data merging and custom sorting, SQL statements are frequently used to query and process data in Excel. However, current technology typically involves using Excel formulas for data processing, which is complex, requiring the manual creation of multiple sheets for data merging and custom sorting. This is inefficient and makes it difficult to visually display the processed data in Excel. The process of updating data and viewing results is cumbersome and time-consuming. Summary of the Invention

[0003] To address the aforementioned technical problems, this invention proposes a method and software for processing data in Excel using SQL.

[0004] This invention aims to accurately and quickly display SQL-processed data intuitively in Excel. The specific steps are as follows:

[0005] S1. Receive the Excel file passed in by the user and create an Excel object; this includes the following sub-steps:

[0006] S11: Receive the Excel file sent by the user;

[0007] S12: Verify the Excel file and its type;

[0008] S13: Create an Excel file as a file stream object;

[0009] S14: Create a buffered stream object from the file stream object;

[0010] S15: Create an Excel object from the buffered stream object;

[0011] The Excel object performs processing operations on Excel files;

[0012] S16: Create a formula calculator based on an Excel object;

[0013] The formula calculator parses formula expressions in Excel cells, performs calculations by associating them with cell data in the Excel object, and returns the results.

[0014] S2. Loop through all Sheet objects in the Excel object and put them into a Map collection named sheetMap; this includes the following sub-steps:

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

[0016] A Sheet object, or worksheet, is an independent table page in an Excel object. Each worksheet has a different name and is located at the bottom tab of the Excel object.

[0017] S22: Store each Sheet object that has been traversed into a Map type collection named sheetMap;

[0018] Specifically, a Map type collection is a key-value pair storage structure that uses the name of a Sheet object as the key and the Sheet object as the value, and stores it in a Map type collection named sheetMap.

[0019] S3. Prepare the data to be processed by the SQL query and put it into a Map type collection named preparedDataMap; this includes the following sub-steps:

[0020] The names of the Sheet objects used for SQL processing are English letters that begin with "#". The first row of the Sheet object contains the field names, which are used to identify the attributes of the data in that column, such as "Product ID" or "Sales". The remaining rows contain the field values.

[0021] S31: Loop through each key-value pair in the sheetMap;

[0022] S32: Loop through all non-first row Row objects of Sheet objects whose keys start with "#" in SheetMap, process the Cell objects of Row objects one by one, and get the corresponding values;

[0023] S33: Construct a List collection named dataListMap based on the row and column information of the sheet object;

[0024] The values ​​of sheetMap, i.e. the values ​​of the Cell objects in the corresponding columns of the first Row objects of the Sheet object, are used as keys, and the values ​​of the Cell objects in the corresponding columns of all other Row objects are used as values. These values ​​are stored in a List type collection named dataListMap, where each element of the List type collection is a Map.

[0025] S34: Store the Sheet object's name and dataListMap as key-value pairs into a Map type collection named preparedDataMap;

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

[0027] Sheet objects whose names begin with "#" indicate that the data in these sheet objects is the data to be processed by the SQL.

[0028] S4. Create the mapping between result set names and SQL statements, and put them into a Map type collection named sqlMap; this includes the following sub-steps:

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

[0030] S42: Process the values ​​in the sheetMap whose key is the string "@SQL", extract the result set name and SQL statement and store them in a Map type collection named sqlMap; if the key of the sheetMap is not "@SQL", skip the key-value pair and continue looping.

[0031] If the key of a sheetMap key-value pair is equal to the string "@SQL", then iterate through the value of the key-value pair, i.e., the Sheet object, then iterate through each Row object of the Sheet object, then iterate through each element Cell object of the Row object, and obtain the result set name and SQL statement based on the SQL text of the Cell object. Then, store the result set name as the key and the SQL statement as the value in a Map type collection named sqlMap.

[0032] If the name of a Sheet object is equal to the string "@SQL", it means that the sheet object contains an SQL statement to be executed.

[0033] S5. Execute the SQL statements in sqlMap and obtain the results; this includes the following sub-steps:

[0034] S51: Traverse the preparedDataMap, using the key as the CTE name and the value content to generate select...unionall... statements as CTE members, and create the CTE part of the SQL statement;

[0035] S52: Create an H2 database JDBC connection based on memory mode;

[0036] S53: Loop through each key-value pair in the sqlMap;

[0037] S54: Concatenate the CTE part of the SQL statement with the SQL statement to form a complete query statement, execute it through JDBC connection to obtain the result, and encapsulate the result into a List type collection named resultList;

[0038] The SQL statement mentioned in step S54 is the value of the key-value pair in sqlMap; the resultList is a List type collection whose elements are Maps.

[0039] S55: Store the key-value pairs in sqlMap as keys and resultList as values ​​into a Map type collection named resultDataMap;

[0040] S56: Close the JDBC connection.

[0041] S6. Retrieve the Cell objects of the data tags and store them in a List collection named prepareCells; this includes the following sub-steps:

[0042] S61: Loop through all key-value pairs in the sheetMap whose keys do not start with "#" or whose keys are not the string "@SQL";

[0043] S62: Find the Cell object in the sheetMap whose key-value pair values ​​whose keys do not start with "#" or whose keys are not the string "@SQL" and whose cell object has a custom-formatted data label, and store the Cell object in a List type collection named prepareCells;

[0044] The custom-formatted data labels are in the format {{result set name.field name}};

[0045] In a sheetMap, sheet objects whose keys do not begin with "#" or whose keys are not the string "@SQL" are the target locations where the SQL query results need to be rendered.

[0046] S7. Loop through the List collection of prepareCells and render the data into Cell objects; this includes the following sub-steps:

[0047] S71: Loop through all Cell objects in the List collection of prepareCells;

[0048] S72: Extract the result set name and field names from the specified Cell object text;

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

[0050] S73: Based on the extracted result set name and field name, retrieve the corresponding data to be rendered from resultDataMap and store it in a List type collection named renderDataList with elements of type Object;

[0051] S74: Get the row and column numbers of the specified Cell object;

[0052] S75: Based on the row number, column number, and number of elements in the renderDataList of the specified Cell object, loop through the specified Cell object and retrieve the Object element at the specified position in the renderDataList;

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

[0054] Determine the type of the Object element, then cast the Object element to the corresponding data type, and set the content of the specified Cell object to the corresponding data type.

[0055] S8. Delete sheet objects in the Excel object whose names begin with "@SQL" or "#", generate an Excel file, and return the results; specifically, it includes the following sub-steps:

[0056] S81: Iterate through each Sheet object in the Excel object. If the sheet object's name is "@SQL" or begins with "#", delete the sheet object from the Excel object.

[0057] S82: Convert the processed Excel object to a byte stream or file format, and close the Excel object;

[0058] S83: Return the Excel file to the user.

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

[0060] Furthermore, in step S3, the name of the Sheet object containing the data to be processed by the SQL must begin with the letter "#". The first line of the Sheet object contains the field names, and the remaining lines contain the field values.

[0061] Furthermore, in step S4, the SQL text format within the Sheet object named "@SQL" is "$ResultSetName=SQLStatement".

[0062] Furthermore, in step S62, the custom data label format is: {{result set name.field name}}; the field name is the field name returned by the SQL statement that processes the data.

[0063] Furthermore, in step S5, for each key-value pair in the preparedDataMap collection, the key is used as the CTE name, and a select statement containing all field names and corresponding values ​​of the element is generated for each element in the dataListMap collection. Then, all select statements are concatenated into CTE members, and the CTE name and CTE members are concatenated into CTE part SQL statements.

[0064] Furthermore, in step S32, when iterating through non-first-line Cell objects of Sheet objects starting with "#", the Cell object type must first be determined by the Cell.getCellType() method. For numeric types, the numeric value is obtained directly; for string types, the text is obtained directly; and for formula types, the evaluate() method of the formula calculator created in step S16 is called to convert it to an actual value. Empty Cell objects must be skipped to avoid null keys or null values ​​when constructing the dataListMap collection.

[0065] Furthermore, in step S62, when filtering custom data labels, the regular expression "\\{\\{(\\w+?)\\.(\\w+?)\\}\\}" is used to precisely match the format "{{result set name.field name}}". The first group captures the result set name, and the second group captures the field name. Only string-type Cell objects are matched to avoid invalid judgments on numerical, formula, and other types of Cell objects, thereby improving filtering efficiency.

[0066] The beneficial effects of this invention's method for processing data in Excel using SQL are as follows: By reading an Excel template file, parsing custom-formatted data labels and SQL text, the data to be processed in the Excel template is created into a CTE statement, which is then concatenated with the SQL text to form a complete SQL query statement and executed to obtain the result data. This result data is then rendered to the data labels and subsequent specified positions. Users can easily handle complex operations such as data merging, custom sorting, and data calculation without having to manually create multiple sheets for data merging and custom sorting, saving users significant time and workload and improving processing efficiency. Furthermore, users can visually display a series of SQL-processed data in Excel, supporting the generation of processed Excel files and returning the results to the user, allowing users to directly see the data processing results. Attached Figure Description

[0067] Figure 1 This is a schematic diagram illustrating the overall workflow of the method for processing data in Excel using SQL according to the present invention;

[0068] Figure 2 This is a flowchart illustrating step S1 of the method for processing data in Excel using SQL according to the present invention;

[0069] Figure 3 This is a flowchart illustrating step S3 of the method for processing data in Excel using SQL according to the present invention;

[0070] Figure 4 This is a flowchart illustrating step S5 of the method for processing data in Excel using SQL according to the present invention;

[0071] Figure 5 This is a flowchart illustrating step S7 of the method for processing data in Excel using SQL according to the present invention; Detailed Implementation

[0072] To provide a further understanding of the purpose, structure, features, and functions of the present invention, detailed descriptions are provided below with reference to specific embodiments.

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

[0074] S1. Receive the Excel file passed in by the user and create an Excel object. This includes the following sub-steps:

[0075] S11: Receive the Excel file sent by the user;

[0076] The Excel file supports full file path, file input stream object, file object, or file byte array format; users can upload Excel files in various ways, such as selecting a local Excel file or directly pasting file content. These different forms of file input are received for subsequent processing.

[0077] For example, if a user selects an Excel file named "Sales Data.xlsx" to upload, the byte stream of that file will be received.

[0078] S12: Verify the Excel file and its type;

[0079] Not empty check: The Excel file verification check is to check whether the Excel file is null;

[0080] Magic number verification: This step verifies the type of the Excel file and determines whether the magic number of the Excel file is correct.

[0081] First, check if the Excel file is null, that is, determine if the user successfully selected and uploaded a file. If the file is null, it means no file was passed, so return an error message and end the process.

[0082] Then, the file type is checked to verify its correctness by examining the file's magic number. The magic number is a fixed sequence of bytes in the file header; different file formats have different magic numbers, used to determine the actual file type. For example, the magic number of an Excel file (.xls) is "D0CF11E0A1B11AE1", and the magic number of an Excel file (.xlsx) is "504B0304[PK]", etc.

[0083] The non-empty check can prevent null pointer exceptions in subsequent processes caused by "no file input"; 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 security and stability of file processing.

[0084] If both the verification content and the verification type pass, proceed to step S13.

[0085] S13: Create an Excel file as a file stream object;

[0086] The validated Excel file is converted into a file stream object, which points to the beginning of the file and begins reading the file content byte by byte, facilitating subsequent sequential reading and manipulation of the file content.

[0087] S14: Create a buffered stream object from the file stream object;

[0088] To improve read / write efficiency, file stream objects are further converted into buffered stream objects. Buffered stream objects use 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 buffered stream object first reads a block of data from the file into the buffer, and then reads data byte by byte from the buffer, instead of reading directly from the file each time, thereby improving processing speed.

[0089] S15: Create an Excel object from the buffered stream object;

[0090] The buffered stream object is parsed using Apache POI to generate an operable Excel object.

[0091] Excel objects are the core carriers for interacting with Excel files. Only after converting streaming data into a structured object model can various operations be performed on Excel files, such as reading worksheets and cell data.

[0092] S16: Create a formula calculator based on an Excel object;

[0093] Create and initialize a formula calculator to handle formula calculations in Excel. The formula calculator parses formula expressions in Excel cells, performs calculations by associating them with cell data within the Excel object, and returns the results.

[0094] First, we introduce the Apache POI library, which is specifically designed for processing Excel files and provides a rich API to manipulate various elements of Excel files, including cell formulas.

[0095] Next, by calling the FormulaEvaluator class provided by ApachePOI, a formula calculator is created from the Excel object by creating an instance of the sheet object and calling its getFormulaEvaluator() method.

[0096] Finally, the `evaluate` method of the formula calculator is used, passing in the cell object to be calculated, to calculate the actual value of the formula in that cell. The `evaluate` method automatically performs calculations based on the various functions and operations in the formula and returns the result.

[0097] Step S1 involves reading the user-provided Excel file into the program and creating an Excel object for subsequent operations on the Excel file, such as reading worksheets and cell data.

[0098] S2. Loop through all Sheet objects in the Excel object and put them into a Map collection named sheetMap. This includes the following sub-steps:

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

[0100] Access each Sheet object in the Excel file one by one. A Sheet object, or worksheet, is an independent table page in an Excel file. Each worksheet has a different name and is located at the bottom tab of the Excel file.

[0101] For example, if there are four sheet objects in "Sales Data.xlsx", namely "#Sales_records", "#Employee_Information", "Segment_performance", and "@SQL", then these four sheet objects will be accessed in sequence.

[0102] S22: Store each Sheet object that has been traversed into a Map type collection named sheetMap;

[0103] Each Sheet object is stored in a Map collection named sheetMap. This Map collection allows for quick retrieval of the corresponding value (Sheet object) using the key (Sheet object name).

[0104] For example, a sheet object named "#Sales_records" can be stored in a sheetMap with the key "#Sales_records" and the value being the corresponding Sheet object. When accessing a sheet object named "#Sales_records", it can be retrieved directly from the sheetMap using the key "#Sales_records".

[0105] Step S2 retrieves all sheet objects from the Excel file and stores them in a Map type collection named sheetMap. A Map type collection is a key-value pair storage structure; here, the sheet object's name is the key, and the sheet object itself is the value, facilitating quick access to the corresponding sheet object by its name later.

[0106] S3. Prepare the data to be processed by the SQL query and place it into a Map type collection named preparedDataMap. This includes the following sub-steps:

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

[0108] The keySet method is used to obtain a collection of all sheetMap keys. The key-value pairs are then iterated through one by one, and each key-value pair of each Sheet object in the sheetMap is processed.

[0109] S32: Loop through all non-first row Row objects of Sheet objects whose keys start with "#" in SheetMap, process the Cell objects of Row objects one by one, and get the corresponding values;

[0110] Furthermore, the names of the Sheet objects containing the data to be processed by the SQL query begin with the letter "#". The first line of each Sheet object contains the field names, followed by the field values.

[0111] If the keys of the sheetMap key-value pairs begin with "#", then the values ​​of the key-value pairs are iterated through, i.e., the Sheet objects. Starting from the second row (index 1) and going to the last row (obtained via the getLastRowNum method), the Row objects are iterated through. Each element of the Row object, the Cell object, is then iterated through. The Cell.getCellType() method is used to determine the Cell object's type, and the corresponding value is retrieved based on that type. The Cell object, or cell object in Excel, is the most basic object element in Excel. Specifically, if the Cell object's type is a formula type, the formula calculator is used to calculate the actual value.

[0112] For Sheet objects whose names begin with "#", it indicates that the data in this sheet object is the data to be processed by the SQL. Iterate through 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 the Cell objects in each Row object one by one. Based on the Cell object's type, retrieve its corresponding value. If the Cell type is a formula type, use the formula calculator to calculate its actual value.

[0113] Assuming the first row of a sheet object named "#Sales_records" contains field names, the calculator will iterate through each row starting from the second row. If a cell contains the formula "=A2*B2", the calculator will calculate the actual value of that formula (e.g., 100*5=500).

[0114] S33: Construct a List collection named dataListMap based on the row and column information of the sheet object;

[0115] The values ​​of the Cell objects in the corresponding columns of the first Row object (the row of field names) of the Sheet object are used as keys, and the values ​​of the Cell objects in the corresponding columns of the other Row objects are used as values. These values ​​are stored in a List type collection named dataListMap, where each element of the List type collection is a Map.

[0116] For example, the values ​​of the Cell objects in the first row of "#Sales_records" are "product_id", "sales_quantity", and "unit_price"; the values ​​of the Cell objects in the second row are "P001", 100, and 50; the values ​​of the Cell objects in the third row are "P002", 200, and 30; and the values ​​of the Cell objects in the fourth row are "P003", 300, and 20.

[0117] The generated dataListMap collection is: [

[0118] {"product_id":"P001","sales_quantity":100,"unit_price":50},

[0119] {"product_id":"P002","sales_quantity":200,"unit_price":30},

[0120] {"product_id":"P003","sales_quantity":300,"unit_price":20}

[0121] ].

[0122] Construct a dataListMap collection to provide a structured data format for subsequent CTE statement generation, ensuring that SQL can recognize and process the data.

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

[0124] Using the name of the Sheet object as the key and dataListMap as the value, the above key-value pairs are added to the Map type collection of preparedDataMap. When iterating through preparedDataMap later, the corresponding data row collection can be quickly located by the name of the Sheet object.

[0125] For example, add the following key-value pairs to a preparedDataMap:

[0126] {"#Sales_records":[

[0127] {"product_id":"P001","sales_quantity":100,"unit_price":50},

[0128] {"product_id":"P002","sales_quantity":200,"unit_price":30},

[0129] {"product_id":"P003","sales_quantity":300,"unit_price":20}

[0130] ]}.

[0131] The preparedDataMap contains all the data to be processed by SQL. When generating CTE statements later, the corresponding data list can be quickly retrieved 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.

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

[0133] 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.

[0134] 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.

[0135] 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.

[0136] 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:

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

[0138] Get a collection of all sheetMap keys using the keySet method, and then loop through the key-value pairs.

[0139] For example, iterate through the key-value pairs corresponding to the four sheet objects: "#Sales_records", "#Employee_Information", "Segment_performance", and "@SQL".

[0140] S42: Process the values ​​in the sheetMap whose key is the string "@SQL", extract the result set name and SQL statement and store them in a Map type collection named sqlMap; if the key of the sheetMap is not "@SQL", skip the key-value pair and continue looping.

[0141] If the key of a key-value pair in the sheetMap equals the string "@SQL", then iterate through the values ​​of the key-value pairs, i.e., the Sheet objects, then iterate through each Row object within the Sheet object, and then iterate through each Cell object within the Row object. Based on the SQL text of the Cell object, use regular expressions to obtain the result set name and the SQL statement. Store the result set name as the key and the SQL statement as the value in a Map collection named sqlMap. The SQL text format is: $result set name = SQL statement.

[0142] If the name of a sheet object is equal to the string "@SQL", it indicates that the sheet object defines an SQL statement to be executed. Iterate through each cell in this sheet object and extract the result set name and SQL statement using regular expressions.

[0143] 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 FROMSales_records GROUP BY product_id".

[0144] After extraction using regular expressions, the result set is named product_summary, and the corresponding SQL statement is SELECT product_id, SUM(sales_quantity * unit_price) AS total_sales FROMSales_records GROUP BY product_id, and these results are stored in sqlMap.

[0145] Step S4 aims to extract SQL statements from a sheet object (equal to the string "@SQL") that specifically defines the SQL statements to be executed. Regular expressions are used to extract the result set names and corresponding SQL statements from the cells of this sheet object and store them in a sqlMap collection. This step is to obtain the SQL statements to be executed later and associate them with the result set names, preparing for subsequent SQL statement execution.

[0146] S5. Execute the SQL statements in sqlMap and obtain the results. This includes the following sub-steps:

[0147] S51: Traverse the preparedDataMap, using the key as the CTE name and the value content to generate select...unionall... statements as CTE members, and concatenate the CTE name and CTE member into a CTE part of the SQL statement;

[0148] Iterate through the keySet() method of preparedDataMap, and for each key's corresponding dataListMap, generate a select statement according to the element order of the List type collection (i.e., the row order in the original Excel), and concatenate them into CTE members using unionall;

[0149] If preparedDataMap is {"#Sales_records":[

[0150] {"product_id":"P001","sales_quantity":100,"unit_price":50},

[0151] {"product_id":"P002","sales_quantity":200,"unit_price":30},

[0152] {"product_id":"P003","sales_quantity":300,"unit_price":20}

[0153] ]}, concatenated into the CTE portion of the statement:

[0154] WITH Sales_records AS (

[0155] SELECT "P001" AS product_id, 100 AS sales_quantity, 50 AS unit_price UNION ALL

[0156] SELECT "P002" AS product_id, 200 AS sales_quantity, 30 AS unit_price UNION ALL

[0157] SELECT "P003" AS product_id, 300 AS sales_quantity, 20 AS unit_price )

[0159] A Common Table Expression (CTE) 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. It can be directly concatenated with user-defined SQL statements for execution, reducing redundant operations of database table creation or deletion, while improving SQL readability and execution efficiency.

[0160] S52: Create an H2 database JDBC connection based on memory mode;

[0161] In the project configuration, include the JDBC driver dependency for the H2 database to ensure that the H2 connection utility class can be called. The connection URL for the H2 database needs to specify the "memory mode" and the database name, load the H2 driver, and create a JDBC connection to the database so that SQL statements can be executed.

[0162] This step utilizes the H2 in-memory database, executing SQL entirely in memory without disk storage. Data exists solely in memory, resulting in high execution speed and avoiding disk I / O overhead. The CTE temporary table is not persisted to disk; data is automatically cleared after closing the JDBC connection, leaving no disk residue. Furthermore, no database service deployment is required; it can run on a single machine, making it suitable for lightweight scenarios such as personal offices and small businesses. It also avoids the risk of leakage of sensitive data such as insurance customer information and financial reimbursement data due to disk storage. JDBC connection is the standard interface for executing SQL statements, ensuring that Excel data can be processed via SQL.

[0163] S53: Loop through each key-value pair in the sqlMap;

[0164] Iterate through the keySet() method of sqlMap, processing each SQL statement key-value pair one by one according to the insertion order of the key (result set name).

[0165] S54: Concatenate the CTE part of the SQL statement with the SQL statement to form a complete query statement, execute it through JDBC connection to obtain the result, and encapsulate the result into a List type collection named resultList;

[0166] The CTE part of the SQL statement is concatenated with the SQL statement (i.e. the values ​​of the key-value pairs in sqlMap) to form a complete SQL query statement, and then executed through a JDBC connection to obtain a ResultSet object. The data in the ResultSet object is then encapsulated into a List type collection named resultList with Map elements.

[0167] For example, "WITH Sales_records AS (

[0168] SELECT "P001" AS product_id, 100 AS sales_quantity, 50 AS unit_priceUNION ALL

[0169] SELECT "P002" AS product_id, 200 AS sales_quantity, 30 AS unit_priceUNION ALL

[0170] SELECT "P003" AS product_id, 300 AS sales_quantity, 20 AS unit_price )

[0172] The statement `SELECT product_id, SUM(sales_quantity * unit_price) AS total_sales FROM Sales_records GROUP BY product_id` returns a ResultSet object containing three rows of data: product_id "P001", total_sales 5000 (100×50); product_id "P002", total_sales 6000 (200×30); product_id "P003", total_sales 6000 (300×20). These three rows of data are then encapsulated into three Maps and added to a resultList, as shown in the example: [

[0174] {"product_id":"P001","total_sales":5000},

[0175] {"product_id":"P002","total_sales":6000},

[0176] {"product_id":"P003","total_sales":6000}

[0177] ].

[0178] By concatenating CTE and SQL, the SQL statement can be executed based on Excel data. The ResultSet is encapsulated as a resultList, which makes it easy to quickly extract and render data by the result set name and field name, avoiding the risk of resource leakage caused by directly manipulating the ResultSet.

[0179] S55: Store the key-value pairs in sqlMap as keys and resultList as values ​​into a Map type collection named resultDataMap;

[0180] resultDataMap is a Map type collection used to store SQL query results. Its purpose is to associate the result of each SQL query with the corresponding result set name, so that the corresponding query result data can be quickly retrieved according to the result set name, which facilitates subsequent operations such as data rendering.

[0181] Assuming the key of the one-key value pair in sqlMap is product_summary, executing the corresponding SQL query will return the following resultList: [

[0183] {"product_id":"P001","total_sales":5000},

[0184] {"product_id":"P002","total_sales":6000},

[0185] {"product_id":"P003","total_sales":6000} ]

[0187] At this point, resultDataMap will store the following key-value pairs:

[0188] {

[0189] "product_summary":[

[0190] {"product_id":"P001","total_sales":5000},

[0191] {"product_id":"P002","total_sales":6000},

[0192] {"product_id":"P003","total_sales":6000} ]

[0194] }

[0195] The resultDataMap centralizes all SQL execution results, allowing data to be located directly by the result set name during subsequent data rendering.

[0196] This step associates the execution result of the current SQL with the result set name for storage. For example, "product_summary" is used as the key and resultList is used as the value to store it in resultDataMap. resultDataMap centrally manages all SQL execution results, and the data can be located directly by the result set name when rendering data later.

[0197] S56: Close the JDBC connection;

[0198] Close the connection to the database, release resources, and prevent connection leaks.

[0199] Step S5 essentially involves generating the CTE portion of the SQL statement from the previously prepared data (located in preparedDataMap), then concatenating it with the SQL statement in sqlMap to form a complete SQL query statement and executing it. After executing the SQL statement, the query results are 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, processing the data by executing SQL statements and obtaining the processed results.

[0200] S6. Retrieve the Cell objects of the data tags and store them in a List collection named prepareCells. This includes the following sub-steps:

[0201] S61: Loop through all key-value pairs in the sheetMap whose keys do not start with "#" or whose keys are not the string "@SQL";

[0202] The `keySet` method retrieves a collection of all sheetMap keys, and then iterates through the key-value pairs. If a key-value pair in a sheetMap starts with "#" or has the name "@SQL", that key-value pair is skipped, and the iteration continues.

[0203] S62: Find the Cell object in the sheetMap whose key-value pair values ​​whose keys do not start with "#" or whose keys are not the string "@SQL" and whose cell object has a custom-formatted data label, and store the Cell object in a List type collection named prepareCells;

[0204] For sheetMap key-value pairs where the key does not begin with "#" or is not the string "@SQL", the corresponding value, i.e., the sheet object, is the target location for rendering the SQL query result. Therefore, the process iterates through each Row object of the sheetMap key-value pair, and then iterates through each Cell object within that Row. If the Cell object is a string, the text string of the Cell object is retrieved. The regular expression "\\{\\{(\\w+?)\\.(\\w+?)\\}\\}" is used to determine if the text string is a custom-formatted data label (format: {{result set name.field name}}). If it is, the Cell object is stored in a List collection named prepareCells. The field name is the field name returned by the SQL statement used to process the data.

[0205] For example, in a sheet object named "Segment_performance", there is a cell whose content is "{{product_summary.product_id}}". This cell is matched by a regular expression and found to be a custom data label. This cell object is then added to the prepareCells list.

[0206] Step S6 essentially involves locating cells in the Excel sheet object that are marked with special data labels (custom format) and storing these cells in the `prepareCells` list. These cells are where the SQL query results will be rendered later. The purpose of this step is to determine where in Excel the SQL query results will be placed.

[0207] S7. Loop through the List collection of prepareCells and render the data into Cell objects. This includes the following sub-steps:

[0208] S71: Loop through all Cell objects in the List collection of prepareCells;

[0209] The `prepareCells` list is iterated through one by one, processing each Cell object one by one. `prepareCells` stores all the Cell objects in Excel that need to be filled with SQL results. The data filling is completed later by iterating through the set of custom data label cells.

[0210] S72: Extract the result set name and field names from the specified Cell object text;

[0211] Get the text string of the specified Cell object, and extract the result set name and field names using regular expressions.

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

[0213] This step determines which part of the data to retrieve from the resultDataMap and the specific field values ​​to be rendered; it parses out the data source and target fields, providing precise location for subsequent data retrieval and rendering.

[0214] For example, for "{{product_summary.product_id}}", extract the result set name "product_summary" and the field name "product_id".

[0215] S73: Based on the extracted result set name and field name, retrieve the corresponding data to be rendered from resultDataMap and store it in a List type collection named renderDataList with elements of type Object;

[0216] The data to be rendered is obtained from the resultDataMap based on the result set name and field names, and stored in a List collection named renderDataList. The elements of the List are of type Object.

[0217] The resultDataMap stores a List type collection. Data is extracted according to the order of elements in the resultDataMap collection, that is, the order in which the SQL execution results are returned. The retrieved data is stored in a List type collection named renderDataList, and the element type of the List is Object.

[0218] This step retrieves the query result data corresponding to the result set name from the resultDataMap and extracts the specific values ​​based on the field names, forming a list containing all relevant data. This list will be used for subsequent data rendering operations to ensure that each target cell receives the correct data value.

[0219] For example, in step S72, the result set name "product_summary" and the field name "product_id" are extracted. Then, in this step, the list of values ​​corresponding to "product_summary" is obtained from resultDataMap, 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 label being processed is "{{product_summary.product_id}}" and it is the first matching item, then the "product_id" field value of the first row is extracted from this list, and renderDataList is ["P001"]. If the custom data label is "{{product_summary.total_sales}}" and the first two items need to be obtained, then the "total_sales" field value of the first two rows is extracted, and renderDataList is [5000,6000].

[0220] S74: Get the row and column numbers of the specified Cell object;

[0221] The Excel processing library is called to determine the specific location of a given Cell object in an Excel spreadsheet, namely the row number and column number, so that the data can be accurately rendered to the corresponding location.

[0222] S75: Based on the row number, column number, and number of elements in the renderDataList of the specified Cell object, loop through the specified Cell object and retrieve the Object element at the specified position in the renderDataList;

[0223] Based on the row number, column number, and number of elements in the renderDataList of the specified Cell object, the corresponding elements are retrieved from the renderDataList and written sequentially into the specified Cell object. The specific implementation is as follows:

[0224] From step S74, obtain the row number of the current specified Cell object, denoted as startRow, and the column number, denoted as fixedCol; obtain the number of elements in the renderDataList collection generated in step S73, denoted as N.

[0225] For the element at index i in renderDataList (where i can be 0 ≤ i < N), locate the target Cell object according to the following rules:

[0226] The target row number, targetRow = startRow + i, starts from the row number of the specified Cell object and increments with the data index to ensure that the data is arranged vertically in a continuous manner.

[0227] The target column number targetCol = fixedCol is fixed to the column number of the specified Cell object to avoid horizontal data offset.

[0228] For example, when startRow=3, fixedCol=2, and N=2, the element with index i=0 corresponds to the target coordinate (3,2), and the element with index i=1 corresponds to the target coordinate (4,2).

[0229] Furthermore, when locating a 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 and writable state.

[0230] Finally, iterate through renderDataList and bind the Object element corresponding to renderDataList.get(i) to the target Cell object at coordinate (startRow+i, fixedCol) one by one according to the index order. That is, 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).

[0231] This step dynamically calculates the target cell coordinates. If there are not enough rows, a new row is created; if there are not enough columns, a new column is created. Users do not need to plan the number of rows in the Excel template in advance.

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

[0233] If it is an integer type, the Object element will be cast to an integer and the content of the specified Cell object will be set to an integer.

[0234] If it is a double type, the Object element will be cast to double data and the content of the specified Cell object will be set to double data.

[0235] If it is a single-precision type, the Object element will be cast to single-precision data, and the content of the specified Cell object will be set to single-precision data.

[0236] If it is of type BigDecimal, the Object element will be cast to double-precision data and the content of the specified Cell object will be set to double-precision data.

[0237] If it is a date type, the Object element will be cast to date data, and the content of the specified Cell object will be set to date data.

[0238] Otherwise, convert the Object element to string data and set the content of the specified Cell object to string data.

[0239] Excel cells have specific storage and display methods for different types of data. For example, date data needs to be displayed in a specific date format, and numeric data may involve formatting issues such as decimal places. By forcibly converting the data type, we ensure that the data is written to the cell in the correct format, thus ensuring correct display and storage in Excel. For example, converting data to an integer type before writing it to a cell ensures that the data in that cell is recognized as an integer in Excel, and alignment, calculations, and other operations are performed according to the integer format. Excel displays different types of data differently; type conversion avoids display anomalies caused by data type mismatches, improving the usability of the results. This step completes the adaptation of data types to Excel cell formats, writes the result data (stored in resultDataMap) to the target Cell object, and realizes data visualization rendering.

[0240] The purpose of step S7 is to associate the result data obtained from the previously executed SQL statement (stored in resultDataMap) with the preset custom data labels in Excel, achieving accurate mapping and format adaptation of the result data to Excel cells. Depending on the data type, appropriate conversions are performed to ensure the data is correctly displayed in the cells, thus actually filling the Excel cells with the SQL result data and completing the data visualization.

[0241] S8. Delete sheet objects in the Excel object whose names begin with "@SQL" or "#", generate an Excel file, and return the results. This includes the following sub-steps:

[0242] S81: Iterate through each Sheet object in the Excel object. If the sheet object's name is "@SQL" or begins with "#", delete the sheet object from the Excel object.

[0243] Traverse all Sheet objects in the Excel object according to their index order, and check if the name starts with "#" or is "@SQL". If the condition is met, delete it using the removeSheetAt(index) method. After deletion, the index of subsequent Sheet objects will be automatically updated, and the remaining Sheet objects will need to be traversed again.

[0244] Sheet objects whose names begin with "@SQL" or "#" are temporary workspaces for data processing. Sheet objects whose names begin with "#" are used to store the raw data to be processed by SQL, containing a large amount of unprocessed basic data, which is redundant information for users. Sheet objects whose names begin with "@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 sheet objects that the user needs to display the results, avoiding interference from irrelevant information and presenting the processing results intuitively.

[0245] S82: Convert the processed Excel object to a byte stream or file format, and close the Excel object;

[0246] This process generates an Excel file from the processed Excel object. Excel files support byte streams, full file paths, file objects, and file output stream objects. It can also convert the processed Excel object to a byte stream, or generate Excel files in different formats such as full file paths, file objects, or file output stream objects according to user needs. Finally, it closes the Excel object to release resources.

[0247] S83: Return the Excel file to the user;

[0248] The generated Excel file (byte stream or other format) is returned to the user, who can then save it locally or to another storage location.

[0249] For example, when a user clicks the download button on a webpage, the generated Excel file byte stream is returned to the user's browser as an HTTP response, and the browser prompts the user to save this file as "Processed Sales Data.xlsx". The file is then returned to the user.

[0250] Step S8 deletes unnecessary Sheet objects, converts the processed Excel object into a byte stream or file format, and returns it to the user. The user can save the returned file as "Processed Sales Data.xlsx" to view the data processed by SQL, such as "Technical Department" displayed in the custom data labels.

[0251] The present invention has been described by the above-described embodiments; however, these embodiments are merely examples for implementing the present invention. It must be noted that the disclosed embodiments do not limit the scope of the present invention. Conversely, any modifications and refinements made without departing from the spirit and scope of the present invention are within the scope of patent protection of the present invention.

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