Method and system for automatically converting Excel formula to SQL statement based on grammar mapping

Abstract

The invention discloses an automatic conversion method and system from an Excel formula to an SQL statement based on grammar mapping, and relates to the technical field of data processing. The method is characterized by comprising the steps of 1, classifying the type of excel formulas, and presetting a mapping library of the excel formulas and sql statements; 2, generating a database table through the excel template, and obtaining a corresponding relation between excel cells and database table fields; 3, obtaining an excel formula, and analyzing the formula to construct an abstract syntax tree; and 4, generating the sql statement based on the grammar analysis data: extracting a core function of an excel formula in the abstract syntax tree, mapping a corresponding sql function, extracting a reference character field, mapping the reference character field into a field name of an established library table, extracting a literal quantity of the reference character field, mapping the reference character field into corresponding data, confirming an output structure and a condition structure of the sql statement and performing other nesting processing to generate the sql statement.

Description

Technical Field

[0001] The invention discloses an automatic conversion method and system for Excel formulas to SQL statements based on syntax mapping, and relates to the technical field of data processing. Background Art

[0002] Grassroots staff are accustomed to using Excel as a data processing tool, employing formulas for data analysis and report generation. However, when the amount of data is excessive, Excel spreadsheet formula calculations can become sluggish, even causing CPU usage to spike and shutting down the Excel program. Furthermore, relying solely on Excel for filling out and disseminating data wastes significant manpower and resources, and makes it difficult for superiors to review the status of subordinates' data entry. Databases are suitable for storing massive amounts of data and enabling rapid querying of data information using SQL statements. Therefore, it is necessary to convert Excel templates into database tables and Excel formulas into SQL statements to generate visual data analysis for review at all levels. However, existing tools only support simple function conversions and cannot handle complex logic. Manually converting Excel formulas into SQL statements is inefficient and prone to errors. Summary of the Invention

[0003] In response to the problems of the prior art, the present invention provides a method and system for automatically converting Excel formulas to SQL statements based on syntax mapping, which automatically converts Excel formulas into structured query language SQL, and is suitable for data analysis and massive report generation scenarios.

[0004] The specific scheme proposed by the present invention is:

[0005] The present invention provides an automatic conversion method from Excel formulas to SQL statements based on syntax mapping, comprising:

[0006] Step 1: Classify the types of Excel formulas and preset the mapping library between Excel formulas and SQL statements;

[0007] Step 2: Generate a database table from the Excel template and obtain the correspondence between the Excel cells and the database table fields;

[0008] Step 3: Get the Excel formula, parse the formula and build an abstract syntax tree;

[0009] Step 4: Generate SQL statements based on grammatical parsing data:

[0010] Extract the core functions of the Excel formula in the abstract syntax tree and map the corresponding SQL functions.

[0011] Extract the reference character segment and map it to the field name of the established database table.

[0012] Extract the quoted character segment literal and map it to the corresponding data.

[0013] Confirm the SQL statement output structure, conditional structure and other nested processing to generate SQL statements.

[0014] Furthermore, in step 1 of the method for automatically converting Excel formulas to SQL statements based on syntax mapping, the types of Excel formulas are classified into direct mapping, conditional mapping and special mapping, and the corresponding SQL statement templates are mapped according to the core functions in the Excel formulas, and a mapping library is preset according to the required SQL statement format.

[0015] Furthermore, step 2 of the method for automatically converting Excel formulas to SQL statements based on syntax mapping includes:

[0016] Get the correspondence between the database table name table_name, Excel column name, and database field name. Generate database table fields based on the cell data in the last row of the Excel header. The remaining header relationships are stored in JSON format.

[0017] Furthermore, step 3 of the method for automatically converting Excel formulas to SQL statements based on syntax mapping includes:

[0018] The Excel formula is lexically scanned, non-grammatical characters are filtered and deleted, the Excel formula lexical data is obtained, the grammatical format is checked, and the conversion program is terminated if the grammatical format is incorrect. The correct lexical data is parsed and split into atomic elements to construct an abstract syntax tree.

[0019] The present invention also provides an automatic conversion system from Excel formula to SQL statement based on syntax mapping, which includes a mapping management module, a table management module, a syntax parsing module and a generation module.

[0020] The mapping management module classifies the types of Excel formulas and presets the mapping library between Excel formulas and SQL statements;

[0021] The table management module generates a database table from the Excel template and obtains the correspondence between the Excel cells and the database table fields;

[0022] The syntax parsing module obtains the Excel formula, parses the formula and constructs an abstract syntax tree;

[0023] The generation module generates SQL statements based on grammatical parsing data:

[0024] Extract the core functions of the Excel formula in the abstract syntax tree and map the corresponding SQL functions.

[0025] Extract the reference character segment and map it to the field name of the established database table.

[0026] Extract the quoted character segment literal and map it to the corresponding data.

[0027] Confirm the SQL statement output structure, conditional structure and other nested processing to generate SQL statements.

[0028] Furthermore, the mapping management module of the automatic conversion system of Excel formulas to SQL statements based on syntax mapping classifies the types of Excel formulas into direct mapping, conditional mapping and special mapping, and corresponds the core functions in the Excel formula to the corresponding SQL statement template, and presets the mapping library according to the required SQL statement format.

[0029] Furthermore, the table management module of the automatic conversion system of Excel formulas to SQL statements based on syntax mapping obtains the correspondence between the database table name table_name, the Excel column name and the database field name, generates the database table field according to the cell data in the last row of the Excel header, and stores the remaining header relationships in json format.

[0030] Furthermore, the syntax parsing module of the automatic conversion system of Excel formulas to SQL statements based on syntax mapping performs lexical scanning on the Excel formula, filters and deletes non-grammatical characters, obtains lexical data of the Excel formula, checks the syntax format, terminates the conversion program if the syntax format is incorrect, parses the correct lexical data, splits it into atomic elements, and constructs an abstract syntax tree.

[0031] The benefits of the present invention are:

[0032] The present invention aims at the automatic conversion of Excel formulas to SQL statements, with fast conversion speed and support for batch formula processing. It also has a high conversion rate for complex formulas through semantic analysis and test verification. It has high scalability, and the mapping library supports user-defined rules and can support different SQL languages ​​according to user settings. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 It is a schematic flow chart of the method of the present invention. DETAILED DESCRIPTION

[0034] The present invention will be further described below with reference to the accompanying drawings and specific embodiments so that those skilled in the art can better understand the present invention and implement it. However, the embodiments are not intended to limit the present invention.

[0035] Example 1

[0036] The present invention provides an automatic conversion method from Excel formulas to SQL statements based on syntax mapping, comprising:

[0037] Step 1: Classify the types of Excel formulas and preset the mapping library between Excel formulas and SQL statements. The types of Excel formulas are classified into direct mapping, conditional mapping, and special mapping.

[0038] Direct mapping, for example, the Excel formula =SUM(A1:A100) is mapped to the SQL statement SELECT SUM(column) FROM table;

[0039] Conditional mapping, for example, the Excel formula =SUMIFS(C2:C100,A2:A100,">2023-01-01",B2:B100,"=Sales") is mapped to the SQL statement SELECT SUM(tC)FROM data_table tWHERE tA>'2023-01-01'AND tB='Sales';

[0040] Special mapping, such as the Excel formula =VLOOKUP(A2,D:E,2,FALSE), maps to the SQL statement SELECT t2.column2 FROM main_table t1 LEFT JOIN lookup_table t2 ON t1.key=t2.key WHERE t2.column1=t1.A2;

[0041] And according to the corresponding SQL statement template corresponding to the core function in the Excel formula, the mapping library is preset according to the required SQL statement format.

[0042] Step 2: Generate a database table from the Excel template and obtain the corresponding relationship between the Excel cells and the database table fields. This may include:

[0043] Get the correspondence between the database table name table_name, Excel column name, and database field name, such as A->Unified Social Credit Code. Generate database table fields based on the cell data in the last row of the Excel header, and store the remaining header relationships in JSON format.

[0044] Step 3: Get the Excel formula, parse the formula and build an abstract syntax tree.

[0045] These may include:

[0046] The Excel formula is lexically scanned, non-grammatical characters are filtered and deleted, the Excel formula lexical data is obtained, the grammatical format is checked, and the conversion program is terminated if the grammatical format is incorrect. The correct lexical data is parsed and split into atomic elements to construct an abstract syntax tree.

[0047] For example, =SUMIFS(C2:C100,A2:A100,">2023-01-01",B2:B100,"=Sales")

[0048] FunctionCall(SUMIFS)

[0049] |--FunctionCallArgument(sum_range)

[0050] ||--Range(C2:C100)

[0051] |--FunctionCallArgument(criteria_range1)

[0052] ||--Range(A2:A100)

[0053] |--FunctionCallArgument(criteria1)

[0054] ||--GreaterThan(">2023-01-01")

[0055] |--FunctionCallArgument(criteria_range2)

[0056] ||--Range(B2:B100)

[0057] |--FunctionCallArgument(criteria2)

[0058] |--Equals("=Sales")

[0059] Step 4: Generate SQL statements based on grammatical parsing data:

[0060] Extract the core functions of the Excel formula in the abstract syntax tree and map the corresponding SQL functions.

[0061] Extract the reference character segment and map it to the field name of the established database table.

[0062] Extract the quoted character segment literal and map it to the corresponding data.

[0063] Confirm the SQL statement output structure, conditional structure and other nested processing to generate SQL statements.

[0064] Example 2

[0065] The present invention also provides an automatic conversion system from Excel formula to SQL statement based on syntax mapping, which includes a mapping management module, a table management module, a syntax parsing module and a generation module.

[0066] The mapping management module classifies the types of Excel formulas and presets the mapping library between Excel formulas and SQL statements;

[0067] The table management module generates a database table from the Excel template and obtains the correspondence between the Excel cells and the database table fields;

[0068] The syntax parsing module obtains the Excel formula, parses the formula and constructs an abstract syntax tree;

[0069] The generation module generates SQL statements based on grammatical parsing data:

[0070] Extract the core functions of the Excel formula in the abstract syntax tree and map the corresponding SQL functions.

[0071] Extract the reference character segment and map it to the field name of the established database table.

[0072] Extract the quoted character segment literal and map it to the corresponding data.

[0073] Confirm the SQL statement output structure, conditional structure and other nested processing to generate SQL statements.

[0074] The information interaction, execution process and other contents between the modules in the above system are based on the same concept as the embodiment of the method of the present invention. For specific contents, please refer to the description in the embodiment of the method of the present invention and will not be repeated here.

[0075] Similarly, the system of the present invention automatically converts Excel formulas to SQL statements, with fast conversion speed and support for batch formula processing; it also has a high conversion rate for complex formulas through semantic analysis and test verification; it has high scalability, and the mapping library supports user-defined rules and can support different SQL languages ​​according to user settings.

[0076] It should be noted that not all steps and modules in the above-mentioned processes and system structures are required, and certain steps or modules can be omitted according to actual needs. The execution order of each step is not fixed and can be adjusted as needed. The system structure described in the above-mentioned embodiments can be a physical structure or a logical structure, that is, some modules may be implemented by the same physical entity, or some modules may be implemented by multiple physical entities, or may be implemented by certain components in multiple independent devices.

[0077] The above embodiments are merely preferred embodiments for the purpose of fully illustrating the present invention, and the scope of protection of the present invention is not limited thereto. Equivalent substitutions or modifications made by those skilled in the art based on the present invention are within the scope of protection of the present invention. The scope of protection of the present invention shall be subject to the claims.

Claims

1. An automatic conversion method from Excel formula to SQL statement based on syntax mapping, characterized by include: Step 1: Classify the types of Excel formulas and preset the mapping library between Excel formulas and SQL statements; Step 2: Generate a database table from the Excel template and obtain the correspondence between the Excel cells and the database table fields; Step 3: Get the Excel formula, parse the formula and build an abstract syntax tree; Step 4: Generate SQL statements based on grammatical parsing data: Extract the core functions of the Excel formula in the abstract syntax tree and map the corresponding SQL functions. Extract the reference character segment and map it to the field name of the established database table. Extract the quoted character segment literal and map it to the corresponding data. Confirm the SQL statement output structure, conditional structure and other nested processing to generate SQL statements.

2. The method for automatically converting Excel formulas to SQL statements based on syntax mapping according to claim 1 is characterized in that in step 1, the types of Excel formulas are classified into direct mapping, conditional mapping, and special mapping, and a mapping library is preset according to the corresponding SQL statement template based on the core functions in the Excel formula and the required SQL statement format.

3. The method for automatically converting Excel formulas to SQL statements based on syntax mapping according to claim 1, characterized in that Step 2 includes: Get the correspondence between the database table name table_name, Excel column name, and database field name. Generate database table fields based on the cell data in the last row of the Excel header. The remaining header relationships are stored in JSON format.

4. The method for automatically converting Excel formulas to SQL statements based on syntax mapping according to claim 1, characterized in that Step 3 includes: The Excel formula is lexically scanned, non-grammatical characters are filtered and deleted, the Excel formula lexical data is obtained, the grammatical format is checked, and the conversion program is terminated if the grammatical format is incorrect. The correct lexical data is parsed and split into atomic elements to construct an abstract syntax tree.

5. An automatic conversion system for Excel formulas to SQL statements based on syntax mapping, characterized by Including mapping management module, table management module, syntax parsing module and generation module, The mapping management module classifies the types of Excel formulas and presets the mapping library between Excel formulas and SQL statements; The table management module generates a database table from the Excel template and obtains the correspondence between the Excel cells and the database table fields; The syntax parsing module obtains the Excel formula, parses the formula and constructs an abstract syntax tree; The generation module generates SQL statements based on grammatical parsing data: Extract the core functions of the Excel formula in the abstract syntax tree and map them to the corresponding SQL functions. Extract the reference character segment and map it to the field name of the established database table. Extract the quoted character segment literal and map it to the corresponding data. Confirm the SQL statement output structure, conditional structure and other nested processing to generate SQL statements.

6. The automatic conversion system of Excel formula to SQL statement based on syntax mapping according to claim 5 is characterized in that the mapping The management module classifies the types of Excel formulas into direct mapping, conditional mapping and special mapping, and corresponds to the corresponding SQL statement template according to the core functions in the Excel formula, and presets the mapping library according to the required SQL statement format.

7. The automatic conversion system of Excel formula to SQL statement based on syntax mapping according to claim 5 is characterized by: The table management module obtains the correspondence between the database table name table_name, the Excel column name and the database field name, generates the database table fields according to the cell data in the last row of the Excel header, and stores the remaining header relationships in JSON format.

8. The method for automatically converting Excel formulas to SQL statements based on syntax mapping according to claim 1, characterized in that The syntax parsing module performs lexical scanning on the Excel formula, filters and deletes non-grammatical characters, obtains the Excel formula lexical data, checks the syntax format, terminates the conversion program if the syntax format is incorrect, parses the correct lexical data, splits it into atomic elements, and constructs an abstract syntax tree.

Images