Excel file content scoring method and system

Abstract

The invention relates to the field of computing software application, in particular to an Excel file content scoring method and system, and aims to solve the technical problem of how to use a computer program to quickly evaluate answers submitted by a user on a large scale and give score conditions according to preset evaluation cases. The system is composed of three parts, namely M1: a visual evaluation case setting module, M2: an evaluation case evaluation scoring module, and M3: a visual answer right and wrong checking module for an evaluated person. Through the three parts of modules, the computer system can process, visualize and simplify the evaluation of the Excel file, the evaluation result is transparent and the like, and the method has higher practicability.

Description

Technical Field

[0001] The present invention relates to the field of computing software applications, and specifically to a method and system for scoring the content of Excel files. Background Art

[0002] In recent years, China has attached great importance to the status and role of big data in promoting economic and social development, and has successively introduced a number of policies to encourage the development and innovation of the big data industry. Among them, Excel, as a spreadsheet processing software released by Microsoft, has advantages in data processing mainly reflected in its powerful data processing capabilities, flexible data presentation methods, convenient formula calculation functions, good compatibility and scalability, simple operations and low thresholds, and is favored and widely used by more and more users. Therefore, how to use a reliable software system to solve the data analysis and evaluation of Excel files is an important factor in large-scale measurement of users' data processing capabilities.

[0003] After retrieval, Patent CN118674404A discloses an Excel document operation scoring method and related devices. Its core principle is to perform syntax analysis on a target macro file recording the standard answer operation process corresponding to the target test questions to generate abstract syntax tree information. Among them, the target macro file records all actions for performing standard answer operations on the target test questions in the initial Excel document; based on the abstract syntax tree information and the correct answers, a scoring rule is generated to score the document to be scored corresponding to the target test questions. This patent uses the target macro to judge the score of the file, which is equivalent to filtering and comparing the user operation logs one by one, different from the processing method of the present invention.

[0004] Patent CN113065325A discloses an Excel document analysis method and system based on OpenXml. Its core steps include S1. Open the required type of Excel documents simultaneously; S2. Obtain the content and attributes of the cells in the specified Excel document according to the question requirements; S3. Compare the data obtained in the document according to the question requirements; S4. Statistically calculate the scores of each evaluation point, and judge whether all the scoring points of this question have been scored. If so, calculate the total score of the user. If not, continue to score the scoring points that should be scored; S5. Statistically calculate the total score and error information of the test questions and feedback them to the user. This patent needs to open the user's answer document, reference document, and original document to compare the data in the three documents, while the present invention pre-converts the places to be evaluated into test cases for storage and only selectively evaluates the answer document according to the test cases, with strong flexibility, simple operation and high efficiency.

[0005] Patent CN113065325B discloses an Excel document analysis method and system based on OpenXml. Its core steps include: S1. Open Excel documents of the required types simultaneously; S2. Obtain the content and attributes of cells in the specified Excel document according to the question requirements; S3. Compare the data obtained in the document according to the question requirements; S4. Statistically calculate the scores of each evaluation point, and determine whether all the scoring points of the question have been scored. If so, calculate the total score of the user. If not, continue to score the next scoring point; S5. Statistically calculate the total score and error information of the exam questions and feedback them to the user. This patent is similar to CN113065325A. Summary of the Invention

[0006] The purpose of the present invention is to provide an Excel file content scoring method and system, aiming to solve the problems that in current product teaching, exam and competition activities, the results of data analysis using Excel by the evaluated users rely on manual evaluation and cannot be carried out in large quantities and with high efficiency. At the same time, it solves the problems that the existing open-source technical solutions cannot perform visual setting of evaluation cases, cannot accurately mark the areas to be evaluated, cannot accurately set evaluation reference values and weights, cannot conveniently view and modify the set evaluation cases, and cannot perform non-visual comparison of the correctness of answers. Users cannot quickly compare the correctness, error values, and comparison situations of the answers of the examinees, lack the judgment logic for known types such as STRING, BLANK, and NUMERIC in Excel, and lack the effective evaluation logic for cell values, cell formulas, and chart types in Excel. The present invention is implemented as follows: Provide an Excel file content scoring method and system, and the method includes: As a preferred technical solution of the present invention, it includes the following several modules: M1. Visual setting of evaluation cases module; M2. Evaluation and scoring module for evaluation cases; M3. Visual viewing module for the correctness or wrongness of the answers of the evaluated person. The above three modules have a sequential order in use. It is necessary to set the evaluation cases first before performing the evaluation and scoring of the cases, and finally, the correctness or wrongness of the answers of the evaluated person can be viewed. After the several modules are executed once, any one of the modules can be executed subsequently. Among them, the M1 module and the M3 module use Vue + Html technology for web page display for users to perform input and output operations, and the M2 module uses Java technology for backend evaluation.

[0007] As a preferred technical solution of the present invention, the module M1 includes the following steps: S-01: Upload the Excel reference answer; S-02: Display the uploaded Excel file; S-03: Set the Excel evaluation cases. The above three steps have a sequential order in operation. In the S-01 step, the reference answer formulated in advance needs to be uploaded manually by a human. After the upload is completed, the content of the uploaded file can be viewed in the form of a web page in the S-02 step. Then, the S-03 step can be executed to manually specify the evaluation cases. The above three steps have a sequential order in operation. In the S-01 step, the reference answer formulated in advance needs to be uploaded manually by a human. After the upload is completed, the content of the uploaded file can be viewed in the form of a web page in the S-02 step. Then, the S-03 step can be executed to manually specify the evaluation cases. Among them, the S-01 step is written in Vue language, interfaces with the background interface or directly connects to the OSS server on the cloud service for uploading and obtaining the file address, and only the file address needs to be submitted to the background; the S-02 step is written in Vue + Html language. By obtaining the address of the uploaded file, it is read out in the way of file stream, and the file is read through the xlsx module of Vue. In order to prevent too many rows and columns from affecting the display, the paging method is used to load in batches. When displaying, the metadata information of the cells such as row, column, address, etc. will be loaded to facilitate the next operation.

[0008] As a preferred technical solution of the present invention, the module M2 includes the following steps: S1: Prepare the evaluation cases; S2: Prepare the user's answer sheet file; S3: Evaluate the user's answer record; S4: Calculate the user's score. The above four steps have a sequential order during operation. After the S1 step and the S2 step are executed, the evaluation in the S3 step can be carried out, and then the score calculation in the S4 step can be carried out. The above four steps have a sequential order during operation. After the S1 step and the S2 step are executed, the evaluation in the S3 step can be carried out, and then the score calculation in the S4 step can be carried out.

[0009] As a preferred technical solution of the present invention, the module M3 includes the following several modules: M31: The display area of the Excel content of the user to be evaluated; M32: The display area of the correctness or incorrectness of the Excel cases of the user to be evaluated; M33: The pop-up window for displaying the correctness or incorrectness of the Excel cells of the user to be evaluated. Among the above 3 modules, there is no strict sequential order between M31 and M32 in operation. The display of the M33 module needs to appear after clicking in M31. The above 3 modules are all written in Vue + Html language. By obtaining the address of the uploaded file, it is read out in the way of file stream, and the file is read through the xlsx module of Vue. In order to prevent too many rows and columns from affecting the display, the paging method is used to load in batches. When displaying, the metadata information of the cells such as row, column, address, etc. will be loaded to facilitate the next operation.

[0010] As a preferred technical solution of the present invention, the step S1 includes the following steps: S11: Clear the results of the evaluation test cases; S12: Obtain the total weight of the Excel question evaluation test cases; S13: Query all sheets in the evaluation test cases; S14: Query all evaluation test cases; S15: Transfer the evaluation test cases to a Map structure. In the above 3 steps, there is a strict order of execution, and the operations of subsequent steps are based on the premise that the previous steps have been executed.

[0011] As a preferred technical solution of the present invention, the step S2 includes the following steps: S21: Read the files in the OSS of the person to be evaluated; S22: Read the number of sheet pages. In the above 2 steps, there is a strict order of execution, and the operations of subsequent steps are based on the premise that the previous steps have been executed. S21 reads network files using the http file stream method, and S22 reads the information of the sheets in the Excel file using the poi component.

[0012] As a preferred technical solution of the present invention, the step S3 includes the following steps: S31: Judge whether the number of sheet pages of the file to be evaluated by the user is 0. If so, end the process; otherwise, go to the next step; S32: Initialize the loop variable for traversing each sheet page; S33: Judge whether it has looped to the last sheet page. If so, end the process; otherwise, go to the next step; S34: Judge whether the Map of the evaluation test cases contains this sheet page. If so, go to the next step; otherwise, end this round of loop and return to S33; S35: Obtain the list of evaluation test cases in this sheet page; S36: Initialize the loop variable for traversing each evaluation test case in the sheet page; S37: Judge whether it has looped to the last evaluation test case. If so, return to S33; otherwise, go to the next step; S38: Write the evaluation results into the database in advance; S39: Obtain the row corresponding to the test case in the Excel file; S310: Judge whether there is such a row. If so, go to the next step; otherwise, end this round of loop and return to the S37 step; S311: Obtain the cell corresponding to the test case in this row of the Excel file; S312: Judge whether there is such a cell. If so, go to the next step; otherwise, end this round of loop and return to the S37 step; S313: Judge the evaluation type of the cell, and enter different subsequent steps according to different types; S314: Evaluate the cell value; S315: Evaluate the chart; S316: Evaluate the cell formula. In the above 16 steps, except for the last 3 steps, the others have a strict order of execution, and the operations of subsequent steps are based on the premise that the previous steps have been executed. S31 reads the information of the sheets in the Excel file using the poi component and loads the information into the memory.

[0013] As a preferred technical solution of the present invention, the step S314 includes the following steps: S314-01: Determine whether the cell is of the BLANK type. If so, go to S314-02; otherwise, go to S314-03; S314-02: Obtain the cell string value; S314-03: Determine whether the cell is of the NUMBERIC type. If so, go to S314-04; otherwise, go to S314-09; S314-04: Determine whether the cell is in the date format. If so, go to S314-08; otherwise, go to S314-05; S314-05: Obtain the cell value; S314-06: Determine whether it ends with ".0". If so, go to S314-07; otherwise, directly end; S314-07: Remove the ending ".0"; S314-08: Ignore this case in the cell value evaluation; 314-09: Determine whether the cell is of the BOOLEAN type; if so, go to S314-10; otherwise, go to S314-11; S314-10: Obtain the BOOLEAN value; 314-11: Determine whether the cell is of the FORMULA type. If so, go to S314-12; otherwise, directly end; S314-12: Obtain the cell formula; S314-13: Determine whether the formula is empty. If so, directly end; otherwise, go to step S314-14; S314-14: Execute the formula to obtain the value. The step 315 includes the following steps: S315-01: Query all chart evaluation cases; S315-02: Determine whether the chart evaluation cases are empty. If so, directly end; otherwise, go to S315-03; S315-03: Convert the chart evaluation cases to a Map; S315-04: Clear the chart evaluation records; S315-05: Obtain the chart data in the Excel file to be evaluated; S315-06: Determine whether the chart data in the Excel file to be evaluated is empty. If so, directly end; otherwise, go to S315-07; S315-07: Traverse the chart data in each Excel file to be evaluated; S315-08: Determine whether it has looped to the last chart data. If so, directly end; otherwise, go to S315-09; S315-09: Obtain the chart title; S315-10: Determine whether the title of the Excel chart to be evaluated is consistent with the evaluation case. If so, go to S315-11; otherwise, end this round of loop and return to S315-08; S315-11: Obtain the chart type; S315-12: Determine whether the type of the Excel chart to be evaluated is consistent with the evaluation case. If so, go to S315-13; otherwise, end this round of loop and return to S315-08; S315-13: Obtain the chart series; S315-14: Determine whether the chart series in the Excel file to be evaluated is empty. If so, end this round of loop and return to S315-08; if so, go to S315-15; S315-15: Obtain the data source of the chart series; S315-16: Determine whether it has looped to the last chart series. If so, end; otherwise, go to S315-17;S315-17: Traverse the evaluated Excel chart series; S315-18: Determine if the chart title is empty. If so, end this loop and return to S315-17. Otherwise, enter S315-19; S315-19: Determine if it is X-axis data or Y-axis data. If it is X-axis data, enter S315-27. If it is Y-axis data, enter S315-20; S315-20: Obtain the Y-axis data source of the chart series; S315-21: Determine if the chart Y-axis data source is empty. If so, end directly. Otherwise, enter S315-22; S315-22: Determine if the data source of the evaluated chart is empty or the data reference is empty. If so, enter S315-24. Otherwise, enter S315-23; S315-23: Set the value of the evaluated cell; S315-24: Determine if the data reference in the evaluated Excel file is the same as the evaluation case. Otherwise, enter S315-26. Otherwise, enter S315-25; S315-25: Set the evaluation result to correct; S315-26: Update the evaluation record data; S315-27: Obtain the X-axis data source of the chart series; S315-28: Determine if the chart X-axis data source is empty. If so, end directly. Otherwise, enter S315-29; S315-29: Determine if the data source of the evaluated chart is empty or the data reference is empty. If so, enter S315-31. Otherwise, enter S315-30; S315-30: Set the value of the evaluated cell; S315-31: Determine if the data reference in the evaluated Excel file is the same as the evaluation case. If so, enter S315-25. Otherwise, enter S315-32; S315-32: Update the evaluation record data. The step 316 includes the following steps: S316-01: Determine if the type of the evaluated cell is BLANK. If so, enter S316-09. Otherwise, enter S316-02; S316-02: Determine if the evaluated cell is of FORMULA type. If so, enter S316-03. Otherwise, enter S316-05; S316-03: Obtain the user formula; S316-04: The formula of the evaluated cell is the same as the formula of the evaluation case; S316-05: Set the evaluation result to incorrect; S316-06: Set the evaluation result to correct; S316-07: Calculate the sum of the weighted scores; S316-08: Set the value of the evaluated cell; S316-09: Set the value of the evaluated cell to empty.;

[0014] As a preferred technical solution of the present invention, the step S4 includes the following steps: S41: Close the file; S42: Calculate the score to be obtained according to the score weight value; S43: Set the answer right or wrong according to the score; S44: Update the user's answer record. Among the above 4 steps, there is a strict order of execution, and the operations of subsequent steps need to be based on the premise that the previous steps have been executed. Among them, in step S41, the file closing operation needs to be executed in finally to prevent the system memory occupancy from not being released due to the inability to close the file normally when the code encounters an exception; in step S43, by obtaining the weight and comparing it with the question weight, it is judged whether all are correct, and the actual score is calculated by multiplying the ratio of the weights by the total score of the question; in step S43, by comparing the total score with the question score, if they are the same, the answer is set as correct, and if they are different, the answer is set as wrong.

[0015] The present invention has the following outstanding advantages: By setting M1: Visual setting of test case module, M2: Test case evaluation and scoring module, M3: Visual viewing of whether the answers of the person being tested are correct or wrong module, it is possible to evaluate the answers submitted by users in large quantities and quickly for Excel files and give scores, making the evaluation process of Excel files by computer systems more procedural, visual, simple, and the evaluation results transparent, etc., with stronger practicality. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] In order to more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following will briefly introduce the drawings required for use in the description of the embodiments or the prior art. Obviously, the drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0017] Figure 1 It is a schematic diagram of the modules in the embodiment of the present invention.

[0018] Figure 2 It is a schematic diagram of the operation process in the M1 module of the embodiment of the present invention.

[0019] Figure 3 It is a schematic diagram of the display of the cell values in the reference answer Excel content in the M1 module of the embodiment of the present invention.

[0020] Figure 4 It is a schematic diagram of the display of the cell formulas in the reference answer Excel content in the M1 module of the embodiment of the present invention.

[0021] Figure 5 It is a schematic diagram of the display of the cell charts in the reference answer Excel content in the M1 module of the embodiment of the present invention.

[0022] Figure 6 Schematic diagram for visualizing the evaluation cases in the reference answer Excel content in the M1 module of the embodiment of the present invention.

[0023] Figure 7 Schematic diagram for visualizing the set scoring rules in the M1 module of the embodiment of the present invention.

[0024] Figure 8 Schematic diagram of the overall process of the M2 module of the embodiment of the present invention.

[0025] Figure 9 For the embodiment of the present invention Figure 6 Schematic diagram of the sub-process of the S1 process.

[0026] Figure 10 For the embodiment of the present invention Figure 6 Schematic diagram of the sub-process of the S2 process.

[0027] Figure 11 For the embodiment of the present invention Figure 6 Schematic diagram of the sub-process of the S3 process.

[0028] Figure 12 For the embodiment of the present invention Figure 10 Schematic diagram of the sub-process of the S314 process.

[0029] Figure 13 For the embodiment of the present invention Figure 10 Schematic diagram of the sub-process of the S315 process.

[0030] Figure 14 For the embodiment of the present invention Figure 10 Schematic diagram of the sub-process of the S316 process.

[0031] Figure 15 For the embodiment of the present invention Figure 6 Schematic diagram of the S4 process.

[0032] Figure 16 Schematic diagram of the M31 module and the M32 module in the M3 module of the embodiment of the present invention, specifically for the display of the evaluation results of the cells.

[0033] Figure 17 Schematic diagram of the M31 module and the M32 module in the M3 module of the embodiment of the present invention, specifically for the display of the evaluation results of the cell formulas.

[0034] Figure 18 Schematic diagram of the M31 module in the M3 module of the embodiment of the present invention, specifically for the display of the evaluation results of the unit charts.

[0035] Figure 19Schematic diagram of the M33 module in the M3 module of the embodiment of the present invention. Detailed implementation manners

[0036] In order to make the objectives, technical solutions and advantages of the present invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0037] It can be understood that the terms "S1", "S2", etc. used in this application can be used herein to describe various steps. However, unless otherwise specified, these steps are not limited by these terms. These terms are only used to distinguish the first step from another step. For example, without departing from the scope of this application, the S1 step can be called the S2 step, and similarly, the S2 step can be called the S1 step. Embodiment

[0038] As Figure 1 shown, an Excel file content scoring method and system, characterized in that it includes the following modules: M1: a visual setting test case module, M2: a test case evaluation and scoring module, M3: a visual viewing module for whether the answers of the person being evaluated are correct or wrong. The above three modules have a sequential order in use. It is necessary to set up the test cases first before performing the test case evaluation and scoring, and finally it is possible to view whether the answers of the person being evaluated are correct or wrong. After the several modules are executed once, any one of the modules can be executed subsequently. Among them, the M1 module and the M3 module use Vue + Html technology for web page display for users to perform input and output operations, and the M2 module uses Java technology for back-end evaluation.

[0039] As Figure 2As shown, as a preferred technical solution of the present invention, the module M1 includes the following steps: S-01: Upload the Excel reference answer; S-02: Display the uploaded Excel file; S-03: Set the Excel evaluation cases. The above three steps have a sequential order in operation. In step S-01, the reference answer formulated in advance needs to be uploaded manually by the user. After the upload is completed, the content of the uploaded file can be viewed in the form of a web page in step S-02. Then, step S-03 can be executed to manually specify the evaluation cases. The above three steps have a sequential order in operation. In step S-01, the reference answer formulated in advance needs to be uploaded manually by the user. After the upload is completed, the content of the uploaded file can be viewed in the form of a web page in step S-02. Then, step S-03 can be executed to manually specify the evaluation cases. Among them, step S-01 is written in Vue language, interfaces with the background interface or directly connects to the OSS server on the cloud service for uploading and obtaining the file address, and only the file address needs to be submitted to the background; step S-02 is written in Vue + Html language. By obtaining the address of the uploaded file, it is read out in the way of file stream, and the file is read through the xlsx module of Vue. In order to prevent too many rows and columns from affecting the display, the paging method is used to load in batches. When displaying, the metadata information of the cells such as row, column, address, etc. will be loaded to facilitate the next operation. As Figure 3 shown, it shows the cell value information of the content of the reference answer Excel. If there are set evaluation cases, they will be marked in green here; as Figure 4 shown, it shows the cell formula information of the content of the reference answer Excel. If there are set evaluation cases, they will be marked in green here; as Figure 5 shown, it shows the cell chart information of the content of the reference answer Excel. By default, the chart will be displayed as an evaluation case. If it is not needed, the button under the status bar can be clicked to close it; In step S-03, the user needs to manually click on the cells that need to be set. Multiple cells can be clicked for batch setting, and then the "Set Weight" button is clicked. As Figure 6 shown, a weight setting pop-up window will appear. Enter the weight value of the evaluation case and click the OK button to complete the operation. Similarly, select the set evaluation cases and click the "Cancel Setting" button to delete the evaluation cases; as Figure 7 shown, the set scoring rules will be displayed at the bottom of the page, marking the sheet page number, cell, row number, column number, standard value, weight, status, etc. where they are located. The evaluation cases can be enabled or disabled through the status bar, and the evaluation cases can also be modified or deleted in the operation bar.

[0040] As Figure 8As shown in the figure, as a preferred technical solution of the present invention, the module M2 includes the following steps: S1: Prepare test cases; S2: Prepare user answer sheets; S3: Evaluate user answer records; S4: Calculate user scores. The above four steps have a sequential order during operation. After steps S1 and S2 are executed, the evaluation in step S3 can be carried out, and then the score calculation in step S4 can be carried out. The above four steps have a sequential order during operation. After steps S1 and S2 are executed, the evaluation in step S3 can be carried out, and then the score calculation in step S4 can be carried out.

[0041] As a preferred technical solution of the present invention, the module M3 includes the following several modules: M31: Display area for the Excel content of the user to be evaluated; M32: Display area for the correctness of the Excel test cases of the user to be evaluated; M33: Pop-up window for displaying the correctness of the Excel cells of the user to be evaluated. Among the above 3 modules, there is no strict sequential order in the operations of M31 and M32. The display of the M33 module needs to be clicked in M31 to appear. The above 3 modules are all written in Vue + Html language. By obtaining the address of the uploaded file, it is read in the way of file stream, and the file is read through the xlsx module of Vue. In order to prevent the excessive number of rows and columns from affecting the display, the paging method is used to load in batches. When displaying, the metadata information of the cells such as row, column, address, etc. will be loaded to facilitate the next operation.

[0042] As Figure 9 As shown in the figure, as a preferred technical solution of the present invention, the step S1 includes the following steps: S11: Clear the results of the test cases; S12: Obtain the total weight of the Excel question test cases; S13: Query all sheets in the test cases; S14: Query all test cases; S15: Convert the test cases into a Map structure. Among the above 3 steps, there is a strict sequential execution order, and the operations of subsequent steps need to be based on the premise that the previous steps have been executed.

[0043] As Figure 10 As shown in the figure, as a preferred technical solution of the present invention, the step S2 includes the following steps: S21: Read the file of the person to be evaluated in OSS; S22: Read the number of sheet pages. Among the above 2 steps, there is a strict sequential execution order, and the operations of subsequent steps need to be based on the premise that the previous steps have been executed. S21 reads the network file in the way of http file stream, and S22 reads the information of the sheets in the Excel file using the poi component.

[0044] As Figure 11As shown, as a preferred technical solution of the present invention, the step S3 includes the following steps: S31: Determine whether the number of sheet pages of the file to be evaluated by the user is 0. If so, end the process; otherwise, proceed to the next step; S32: Initialize the loop variable for traversing each sheet page; S33: Determine whether the loop reaches the last sheet page. If so, end the process; otherwise, proceed to the next step; S34: Determine whether the evaluation case Map contains this sheet page. If so, proceed to the next step; otherwise, end this round of loop and return to S33; S35: Obtain the list of evaluation cases in this sheet page; S36: Initialize the loop variable for traversing each evaluation case in the sheet page; S37: Determine whether the loop reaches the last evaluation case. If so, return to S33; otherwise, proceed to the next step; S38: Write the evaluation result into the database in advance; S39: Obtain the row corresponding to the test case in the Excel file; S310: Determine whether there is such a row. If so, proceed to the next step; otherwise, end this round of loop and return to step S37; S311: Obtain the cell corresponding to the test case in this row of the Excel file; S312: Determine whether there is such a cell. If so, proceed to the next step; otherwise, end this round of loop and return to step S37; S313: Determine the evaluation type of the cell and enter different subsequent steps according to different types; S314: Evaluate the cell value; S315: Evaluate the chart; S316: Evaluate the cell formula. Among the above 16 steps, except for the last 3 steps, the others have a strict execution order. The operations of subsequent steps are based on the premise that the previous steps have been executed. S31 uses the poi component to read the information of the sheet in the Excel file and load the information into the memory.

[0045] As Figure 12As shown, as a preferred technical solution of the present invention, the step S314 includes the following steps: S314-01: Determine whether the cell is of the BLANK type. If so, go to S314-02; otherwise, go to S314-03; S314-02: Obtain the cell string value; S314-03: Determine whether the cell is of the NUMBERIC type. If so, go to S314-04; otherwise, go to S314-09; S314-04: Determine whether the cell is in the date format. If so, go to S314-08; otherwise, go to S314-05; S314-05: Obtain the cell value; S314-06: Determine whether it ends with ".0". If so, go to S314-07; otherwise, directly end; S314-07: Remove the ending ".0"; S314-08: Ignore this situation for cell value evaluation; 314-09: Determine whether the cell is of the BOOLEAN type; if so, go to S314-10; otherwise, go to S314-11; S314-10: Obtain the BOOLEAN value; 314-11: Determine whether the cell is of the FORMULA type. If so, go to S314-12; otherwise, directly end; S314-12: Obtain the cell formula; S314-13: Determine whether the formula is empty. If so, directly end; otherwise, go to step S314-14; S314-14: Execute the formula to obtain the value. As Figure 13As shown, step 315 includes the following steps: S315-01: Query all chart evaluation cases; S315-02: Determine whether the chart evaluation cases are empty. If so, directly end. Otherwise, enter S315-03; S315-03: Convert the chart evaluation cases into a Map; S315-04: Clear the chart evaluation records; S315-05: Obtain the chart data in the Excel file to be evaluated; S315-06: Determine whether the chart data in the Excel file to be evaluated is empty. If so, directly end. Otherwise, enter S315-07; S315-07: Traverse the chart data in each Excel file to be evaluated; S315-08: Determine whether it has looped to the last chart data. If so, directly end. Otherwise, enter S315-09; S315-09: Obtain the chart title; S315-10: Determine whether the title of the Excel chart to be evaluated is the same as the evaluation case. If so, enter S315-11. Otherwise, end this round of loop and return to S315-08; S315-11: Obtain the chart type; S315-12: Determine whether the type of the Excel chart to be evaluated is the same as the evaluation case. If so, enter S315-13. Otherwise, end this round of loop and return to S315-08; S315-13: Obtain the chart series; S315-14: Determine whether the chart series in the Excel file to be evaluated is empty. If so, end this round of loop and return to S315-08. If so, enter S315-15; S315-15: Obtain the data source of the chart series; S315-16: Determine whether it has looped to the last chart series. If so, end. Otherwise, enter S315-17; S315-17: Traverse the chart series in the Excel file to be evaluated; S315-18: Determine whether the chart title is empty. If so, end this round of loop and return to S315-17. Otherwise, enter S315-19; S315-19: Determine whether it is the X-axis data or the Y-axis data. If it is the X-axis data, enter S315-27. If it is the Y-axis data, enter S315-20; S315-20: Obtain the Y-axis data source of the chart series; S315-21: Determine whether the Y-axis data source of the chart is empty. If so, directly end. Otherwise, enter S315-22; S315-22: Determine whether the data source of the chart to be evaluated is empty or the data reference is empty. If so, enter S315-24. Otherwise, enter S315-23; S315-23: Set the value of the cell to be evaluated; S315-24: Determine whether the data reference in the Excel file to be evaluated is the same as the evaluation case. Otherwise, enter S315-26. Otherwise, enter S315-25; S315-25: Set the evaluation result to correct; S315-26: Update the evaluation record data; S315-27: Obtain the X-axis data source of the chart series; S315-28: Determine whether the X-axis data source of the chart is empty. If so, directly end. Otherwise, enter S315-29;S315-29: Determine whether the data source of the chart to be evaluated is empty or the data reference is empty. If so, go to S315-31; otherwise, go to S315-30. S315-30: Set the value of the cell to be evaluated. S315-31: Determine whether the data reference in the Excel file to be evaluated is the same as the evaluation case. If so, go to S315-25; otherwise, go to S315-32. S315-32: Update the evaluation record data. For example; Figure 14 As shown, step 316 includes the following steps: S316-01: Determine whether the type of the cell to be evaluated is BLANK. If so, go to S316-09; otherwise, go to S316-02. S316-02: Determine whether the cell to be evaluated is of FORMULA type. If so, go to S316-03; otherwise, go to S316-05. S316-03: Obtain the user formula. S316-04: The formula of the cell to be evaluated is the same as the formula of the evaluation case. S316-05: Set the evaluation result to incorrect. S316-06: Set the evaluation result to correct. S316-07: Calculate the sum of the weighted scores obtained. S316-08: Set the value of the cell to be evaluated. S316-09: Set the value of the cell to be evaluated to be empty.

[0046] For example Figure 15 As shown, as a preferred technical solution of the present invention, step S4 includes the following steps: S41: Close the file. S42: Calculate the score that should be obtained according to the weighted score value. S43: Set whether the answer is correct or wrong according to the score. S44: Update the user's answer record. Among the above 4 steps, there is a strict order of execution. The operations of subsequent steps need to be based on the premise that the previous steps have been executed. For example Figure 16 As shown, it shows the display area of the evaluation content of the cell value of the person to be evaluated in module M31 and the display area of the correctness or wrongness of the evaluation case of the cell value in module M32; for example Figure 17 As shown, it shows the evaluation content of the cell formula of the person to be evaluated in module M31 and the display area of the correctness or wrongness of the evaluation case of the cell formula in module M32; for example Figure 18 As shown, it shows the display area of the evaluation content of the chart of the cell of the person to be evaluated in module M31; for example Figure 19 As shown, it shows module M33, and when clicking on the red or green evaluation area in module M31, a pop-up window shows the details of the evaluation of the content of a certain cell of the person to be evaluated.

[0047] The technical features of the above-described embodiments can be combined arbitrarily. For the sake of brevity of description, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, it should be considered as the scope recorded in this specification.

[0048] The above-described embodiments merely represent several implementation manners of the present application. The description thereof is relatively specific and detailed, but it should not be construed as a limitation to the patent scope of the present application. It should be noted that for those of ordinary skill in the art, without departing from the concept of the present application, several modifications and improvements can still be made, and these all fall within the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the appended claims.

Claims

1. A method and system for scoring Excel file content, which is characterized in that: It includes the following modules: M1: module for visually setting evaluation cases, M2: module for evaluating and scoring evaluation cases, and M3: module for visually checking the correctness of the answers of the evaluated persons; Module M1 includes the following steps: S-01: Upload Excel reference answers; S-02: Display uploaded Excel files; S-03: Set Excel assessment use cases; Module M2 includes the following steps: S1: prepare evaluation cases; S2: prepare user answer files; S3: evaluate user answer records; S4: calculate user scores; Module M3 includes the following modules: M31: Excel content display area for the user being evaluated; M32: Right or wrong display area for Excel use cases for the user being evaluated; M33: Pop-up window for displaying the right or wrong of Excel cells for the user being evaluated.

2. A method and system for scoring Excel file content according to claim 1, characterized in that: The step S1 includes the following steps: S11: clearing the evaluation case results; S12 obtaining the total weight of the Excel question evaluation case; S13: querying all sheets in the evaluation case; S14: querying all evaluation cases; S15: transferring the evaluation case to a Map structure.

3. The Excel file content scoring method and system according to claim 1, characterized in that: The step S2 includes the following steps: S21: reading the file in the OSS of the person being evaluated; S22: reading the number of sheet pages.

4. The Excel file content scoring method and system according to claim 1, characterized in that: The step S3 includes the following steps: S31: determine whether the number of sheet pages of the user's evaluated file is 0, if yes, end the process, otherwise go to the next step; S32: traverse each sheet page loop variable initialization; S33: determine whether the loop reaches the last sheet page, if yes, end the process, otherwise go to the next step; S34: determine whether the evaluation case Map contains this sheet page, if yes, go to the next step, otherwise end this round of loop and return to S33; S35: obtain the list of evaluation cases in this sheet page; S36: traverse each evaluation case loop variable initialization in the sheet page; S37: determine whether the loop reaches the last evaluation case, if yes, Then return to S33, otherwise go to the next step; S38: write the evaluation results into the database in advance; S39: get the row corresponding to the test case in the Excel file; S310: determine whether the row exists, if yes, go to the next step, otherwise end this loop and return to step S37; S311: get the cell corresponding to the test case in the row in the Excel file; S312: determine whether the cell exists, if yes, go to the next step, otherwise end this loop and return to step S37; S313: determine the cell evaluation type, and go to different subsequent steps according to the type; S314: cell value evaluation; S315: chart evaluation; S316: cell formula evaluation.

5. The Excel file content scoring method and system according to claim 4, characterized in that: The step S314 comprises the following steps: S314-01: determine whether the cell is of BLANK type, if yes, go to S314-02, otherwise go to S314-03; S314-02: obtain the cell string value; S314-03: determine whether the cell is of NUMBERIC type, if yes, go to S314-04, otherwise go to S314-09; S314-04: determine whether the cell is in date format, if yes, go to S314-08, otherwise go to S314-05; S314-05: obtain the cell value; S314-06: determine whether it ends with ".0", if yes, go to S314-07, Otherwise, end directly; S314-07: remove the trailing ".0"; S314-08: ignore this situation in the cell value evaluation; 314-09: determine whether the cell is of BOOLEAN type; if so, go to S314-10, otherwise go to S314-11; S314-10: get the BOOLEAN value; 314-11: determine whether the cell is of FORMULA type, if so, go to S314-12, otherwise end directly; S314-12: get the cell formula; S314-13: determine whether the formula is empty, if so, end directly, otherwise go to step S314-14; S314-14: execute the formula to get the value.

6. The Excel file content scoring method and system according to claim 4, characterized in that: The step 315 includes the following steps: S315-01: query all chart evaluation use cases; S315-02: determine whether the chart evaluation use case is empty, if so, end directly, otherwise enter S315-03; S315-03: convert the chart evaluation use case into a Map; S315-04: Clear the chart evaluation record; S315-05: Get the chart data in the evaluated Excel file; S315-06: Determine whether the chart data in the evaluated Excel file is empty, if so, end directly, otherwise enter S315-07; S315-07: Traverse the chart data in each evaluated Excel file; S315-08: Determine whether the loop reaches the last chart data, if so, end directly, otherwise enter S315-09; S315- 09: Get the chart title; S315-10: Determine whether the title of the Excel chart being evaluated is consistent with the evaluation case, if yes, enter S315-11, otherwise end this cycle and return to S315-08; S315-11: Get the chart type; S315-12: Determine whether the type of the Excel chart being evaluated is consistent with the evaluation case, if yes, enter S315-13, otherwise end this cycle and return to S315-08; S315-13: Get the chart series; S315-14: Determine whether the Excel chart series being evaluated is empty, if so, end this loop and return to S315-08, if so, enter S315-15; S315-15: Get the data source of the chart series; S315-16: Determine whether the loop has reached the last chart series, if so, end, otherwise enter S315-17; S315-17: Traverse the Excel chart series being evaluated; S315-18: Determine whether the chart title is empty, if so, end this loop and return to S315-17, otherwise enter S315-19; S315-19: Determine whether it is X-axis data or Y-axis data, if it is X-axis data, enter S315- 27, if it is Y-axis data, go to S315-20; S315-20: Get the Y-axis data source of the chart series; S315-21: Determine whether the chart Y-axis data source is empty, if so, end directly, otherwise go to S315-22; S315-22: Determine whether the data source of the evaluated chart is empty or the data reference is empty, if so, go to S315-24, otherwise go to S315-23; S315-23: Set the evaluated cell value; S315-24: Determine whether the data reference in the evaluated Excel file is the same as the evaluation case, otherwise go to S315-26, otherwise go to S315-25; S315-25: Set the evaluation result to correct; S315-26: Update the evaluation record data; S315-27: Get the X-axis data source of the chart series; S315-28: Determine whether the chart X-axis data source is empty, if so, end directly, otherwise enter S315-29; S315-29: Determine whether the data source of the evaluated chart is empty or the data reference is empty, if so, enter S315-31, otherwise enter S315-30; S315-30: Set the evaluated cell value; S315-31: Determine whether the data reference in the evaluated Excel file is the same as the evaluation use case, if so, enter S315-25, otherwise enter S315-32; S315-32: Update the evaluation record data.

7. The Excel file content scoring method and system according to claim 4, characterized in that: The step 316 includes the following steps: S316-01: determine if the type of the evaluated cell is BLANK, if so, enter S316-09, otherwise enter S316-02; S316-02: determine if the evaluated cell is FORMULA type, if so, enter S316-03, otherwise enter S316-05; S316-03: obtain user formula; S316-04: the evaluated cell formula is the same as the evaluation case formula; S316-05: set the evaluation result to incorrect; S316-06: set the evaluation result to correct; S316-07: calculate the sum of the scored weights; S316-08: set the evaluated cell value; S316-09: set the evaluated cell value to empty.

8. The Excel file content scoring method and system according to claim 1, characterized in that: According to the Excel file content scoring method and system described in claim 3, it is characterized in that the step S4 includes the following steps: S41: closing the file; S42: calculating the score based on the score weight value; S43: setting the correctness of the answer based on the score; S44: updating the user's answer record.

Images