VBA-based test case automatic generation and test coverage analysis system

Abstract

The invention provides a VBA-based test case automatic generation and test coverage analysis system. The VBA-based test case automatic generation and test coverage analysis system comprises a word-to-excel test item generation module, a word-to-excel test item generation module and an excel-to-excel test item generation module, wherein an Excel-version test case is generated according to a word-version test outline test item; the test outline test item generation module is used for performing test coverage analysis on the Excel version test cases and generating a test content sufficiency analysis table; the test item checking module is used for checking whether the test item content of the word version test outline is complete or not; the test purpose and test method updating module is used for updating a test purpose and a test method in the word version test outline test item table, and remaining other contents unchanged; writing of word version test outline test items and Excel version test cases can be assisted, the repeated working time is saved, and the office process is simplified; the VBA script compiling environment of Excel is directly adopted, additional software does not need to be installed, and the method is easy to operate and good in economical efficiency.

Description

Technical Field

[0001] The invention belongs to the technical field of software testing, and in particular relates to a VBA-based test case automatic generation and test coverage analysis system. Background Art

[0002] In the general software testing project process, it is necessary to write a software testing outline and software test cases. Most of the content is shared by the test outline and the test case, and the content of one needs to be pasted to the corresponding position according to the format of the other. Therefore, there are the following problems with the document writing or generation technology related to software testing: there is a lot of copy and paste work; the document format is different, and the content needs to be manually adjusted after copying and pasting, cross-line, fill in blank content and test case number, etc. After the test case is improved according to the actual situation, it is necessary to change the modified part to the test outline, which not only adds a lot of work but also takes a lot of time. Summary of the invention

[0003] To achieve the above problem, the technical solution of the present invention is as follows:

[0004] A VBA-based test case automatic generation and test coverage analysis system, characterized by comprising:

[0005] Generate Word to Excel test item module: Generate Excel test cases based on the Word version test outline test items;

[0006] Generate test outline test item module: perform test coverage analysis on the Excel version of the test case and generate a test content sufficiency analysis table;

[0007] Check the test item module: Check whether the test item content of the word version of the test outline is complete;

[0008] Update the test objectives and test methods module: Update the test objectives and test methods in the test item table of the word version of the test outline, and keep the rest of the content unchanged;

[0009] The word version of the test outline test items is a file storing test relationship data in a table, and the test relationship data includes: test item name, test item identification, priority, tracking relationship, requirement description, test item description, test method, sufficiency requirement and passing criteria;

[0010] The Excel version of the test case is a table for storing test details, and the test details include: software requirement chapter number, software requirement description, test requirement identification, test sub-item name, use case sequence number, use case name, use case identification, test item chapter number, software development task book chapter number, use case description, test type, use case initialization, premise and constraints, input and operation instructions, expected results, measured results, test results, execution status, termination conditions, result evaluation criteria, preparer, preparation date, reviewer, review date, software problem report, problem description, software version, tester and test time;

[0011] The test content adequacy analysis table is a document used to track requirements. The test project content includes: serial number, software development task book, software requirement specification, test item identification, test item name and test type; the test content adequacy analysis table is a table used to track whether the test outline content covers all software requirement specifications, software development task book functions, performance and interface test items.

[0012] The test objectives correspond to the test item descriptions in the word version of the test outline and to the use case descriptions in the Excel version of the test case; the test methods in the test items in the word version of the test outline correspond to the input and operation instructions in the Excel version of the test case.

[0013] Generate test case modules, including:

[0014] Replace all carriage returns in the Word version of the test item table with the separators set in the current Excel table;

[0015] Extract all table contents in the word version test items to Excel tables;

[0016] Divide the test purpose and test method columns in the Excel table into different rows according to the set separators;

[0017] Check whether the "test purpose" and "test method" correspond one to one;

[0018] Remove all special ending characters left by used cells;

[0019] Fill in the remaining columns, where the remaining columns refer to columns whose contents are not "test purpose" and "test method";

[0020] Delete the rows corresponding to the blank cells in the test target column.

[0021] The method of replacing all carriage returns in the word version test item table with the separators set in the current Excel table includes the following steps:

[0022] First set up a special page in the Excel table for editing the separator, and read the separator into the program;

[0023] Replace all carriage returns in the Word version of the test item table with separators, save and close the document.

[0024] The method of extracting all table contents in the word version test items into an Excel table includes extracting all test item table contents in the test document into the Excel table one test item per row, and corresponding the elements in the word version test item table to specific columns in the Excel table.

[0025] The test objectives and test method columns in the Excel table are divided into different rows according to the set separators, including extracting all the test objectives and test methods under each test item to different pages in the Excel table, dividing a row into multiple rows according to the set separators, and then pasting the divided test method column to the same page as the test objectives through a program, so that each row corresponds to only one test objective and test method.

[0026] The check whether the "test purpose" and "test method" correspond one to one is specifically to check whether the number of "test purposes" and "test methods" is consistent, and whether there are blank cells in the document; if the number of "test purposes" and "test methods" is inconsistent or there are blank cells, the specific erroneous test item table information is given, and the operation is stopped to the next step.

[0027] The removing of special ending characters left by all used cells specifically involves removing special ending characters such as "○" in all cells after the test item content is filled in to meet the requirements.

[0028] The deleting of the rows corresponding to the blank cells in the test purpose column specifically involves deleting the rows corresponding to the blank cells left in the "test method" column obtained according to the above method, thereby obtaining all the test cases under each test item.

[0029] The remaining columns include: software requirement chapter number, software requirement description, test requirement identification, test sub-item name, test sub-item identification number and test type.

[0030] Generate test item modules, including:

[0031] Combine all the test objectives and corresponding test methods under the same test item into the test item description and test method cell contents in the word version of the test outline test item;

[0032] Generate a separate word table for each test item;

[0033] Generate a table title for each test item, including the test outline section number and test item name;

[0034] Generate a test content adequacy analysis table to track the test items in the test outline with the software requirements specification chapters and the software development task book chapters.

[0035] Check the test item module, including: check whether there are any abnormalities in the test item content of the word version of the test outline, and the abnormalities include: blank cells and cells with only labels but no actual content.

[0036] Update the test purpose and test method modules, including updating the test purpose and test method parts to the test items in the word version of the test outline after improving the Excel version of the test case; the remaining contents include: test item name, test item identification, priority, tracking relationship, requirement description, sufficiency requirements and passing criteria.

[0037] The present invention has the following beneficial effects:

[0038] 1) After completing the test items in the Word version of the test outline, the Excel version of the test case is generated;

[0039] 2) Ability to perform test coverage analysis on Excel version test cases; ability to automatically perform integrity check on test items in the Word version test outline;

[0040] 3) Ability to update the test objectives and test methods in the Excel version of the test case to the test items in the Word version of the test outline

[0041] 4) Using the VBA script compilation environment that comes with Excel, there is no need to install additional software, which can simplify office processes; BRIEF DESCRIPTION OF THE DRAWINGS

[0042] Figure 1 A schematic block diagram of the structure of a test case automatic generation and test coverage analysis system provided by an embodiment of the present invention;

[0043] Figure 2 A flowchart of the steps for generating an Excel version of a test case based on a word version of a test outline test item provided in the first embodiment of the present invention;

[0044] Figure 3 This is the test case Excel table format of the embodiment of the present invention;

[0045] Figure 4 A flowchart of the steps for performing test coverage analysis on an Excel version of a test case provided in the second embodiment of the present invention;

[0046] Figure 5All active pages in the Excel spreadsheet generated by the embodiment of the present invention;

[0047] Figure 6 The generated interactive interface of the embodiment of the present invention. DETAILED DESCRIPTION

[0048] The present invention provides a VBA-based test case automatic generation and test coverage analysis system, which can generate an Excel version test case after completing the writing of a word version test outline test item, perform test coverage analysis after completing the writing of the Excel version test case, and can automatically perform integrity check on the word version test outline test item. When the test purpose and test method in the Excel version test case are improved, these two parts of content can be updated to the test items in the word version test outline.

[0049] The specific implementation modes of the present invention are described in detail below with reference to the accompanying drawings.

[0050] In the present invention, test-related specifications are defined, including:

[0051] Word version of the test outline test items: Test items are files that store test relationship data in a table, as shown in Table 2. The test relationship data includes: test item name, test item identifier, priority, tracking relationship, requirement description, test item description, test method, adequacy requirements, and pass criteria;

[0052] The Excel version of the test case is a table that stores test details, as shown below Figure 3 As shown in the figure, the test details include: software requirement chapter number, software requirement description, test requirement identification, test sub-item name, use case sequence number, use case name, use case identification, test item chapter number, software development task book chapter number, use case description, test type, use case initialization, premise and constraint, input and operation instructions, expected results, measured results, test results, execution status, termination conditions, result evaluation criteria, preparer, preparation date, reviewer, review date, software problem report, problem description, software version, tester and test time; all active pages in the Excel table are as follows Figure 5 As shown;

[0053] The test objectives correspond to the test item descriptions in the word version of the test outline and to the use case descriptions in the Excel version of the test case; the test methods in the test items in the word version of the test outline correspond to the input and operation instructions in the Excel version of the test case.

[0054] The test content adequacy analysis table is a document used to track requirements, as shown in Table 1, and its contents include: serial number, software development task book, software requirement specification, test item identification, test item name and test type, etc. The test content adequacy analysis table is a table used to track whether the test outline content covers all software requirement specifications, software development task book functions, performance and interface test items.

[0055] Based on the above content, the interactive interface of the VBA-based test case automatic generation and test coverage analysis system is constructed as follows: Figure 6 shown.

[0056] Specifically, the VBA-based test case automatic generation and test coverage analysis system provided by the present invention includes:

[0057] P100 generates word to excel test item module: generates Excel test cases according to the word version test outline test items;

[0058] Example 1

[0059] As a specific example, a contract management system software test project, the test document contains 198 test items, run P100 to generate word to excel test item module function, specifically, please refer to Figure 2 , the steps include:

[0060] S101, replace all the carriage returns in the word version test item table with the separators set in the current Excel table;

[0061] In this example, the Excel table name is "Test Case and Report Sheet.xls", the Word version of the test item table document name is "Test Item.docx", set the separator to "★★★★★" in cell A2 of the "Set Delimiter" page in "Test Case and Report Sheet.xls", open "Test Item.docx", press "CTRL+H", enter ^p in "Find what", enter "★★★★★" in "Replace with", save the text and close it after the replacement is completed.

[0062] S102, extracting all table contents of the word version test items into an Excel table;

[0063] In this example, "Test Item.docx" contains 198 test item tables, which are initially extracted to 198 rows in "Test Case and Report Sheet.xls" and then Figure 3 The content shown corresponds to the fill.

[0064] S103, dividing the columns of test purpose and test method in the Excel table into different rows according to the set separators;

[0065] In this example, the "test requirement identification column (as a benchmark) + test purpose column" is split by the delimiter in the "Purpose" page of the "Test Case and Report Sheet.xls", the "test requirement identification column (as a benchmark) + test method column" is split by the delimiter in the "Purpose 1" page of the "Test Case and Report Sheet.xls", and the "test requirement identification column (as a benchmark) + expected result column" is split by the delimiter in the "Purpose 2" page of the "Test Case and Report Sheet.xls". After the splitting is completed, the "Purpose 1" and "Purpose 2" test method columns and expected result columns are pasted into the corresponding columns in the "Purpose". Finally, after the test purposes and test methods in the 198 test items are divided, a total of 832 test cases are obtained.

[0066] S104, check whether the “test purpose” and “test method” correspond to each other;

[0067] In this example, the normal process and the abnormal process are implemented separately:

[0068] In this example, all data in the normal process are filled in as required, that is, there is no unfilled content in "Test Item.docx", "Test Purpose" and "Test Method" correspond one to one, and no error prompt will pop up when running the program, and the software can continue to run;

[0069] In this example, the content of a cell in "Test Item.docx" in the abnormal process is not filled, and the "Test Purpose" under a test item is 4 rows and the "Test Method" is 5 rows. When the software is run directly, the software pops up a prompt "Please refer to the check results on the "Test Item Check" page to change the "Test Item.docx" document!", and displays specific error information on the "Test Item Check" page in "Test Case and Report Sheet.xls", including the number of test item descriptions, the number of test methods, the comparison results, and whether there are blank cells; in the comparison result column, if the number of test item descriptions and the number of test methods are consistent, "Consistent" is displayed without displaying the specific test item name; if the number of test item descriptions and the number of test methods are inconsistent, "The number of test item descriptions and test methods in XX test item is inconsistent" is displayed;

[0070] In the column of whether there are blank cells, if there are no blank cells in the test item table, nothing is displayed. If there are blank cells in the test item table, "XX test item has blank cells" is displayed.

[0071] S105, remove the special end character left by S102 to S103;

[0072] In this example, a new .xls file will be added to "Test Cases and Report Sheets.xls" in the above manner, and the test cases initially obtained through S101 to S104 are written in this newly added .xls file. At this time, all non-empty cells in the .xls file have a special terminator at the end, similar to "○", which needs to be removed, otherwise it will affect subsequent operations; here, all non-empty cells are traversed through the function, and the special terminator is removed by the format clearing function. Through this step, the special terminator is removed and the rest of the content is retained.

[0073] S106, filling the remaining columns;

[0074] In this example, the contents of the test purpose and test method columns S101 to S105 are complete, and the software requirement chapter number, software requirement description, test requirement identifier, test sub-item name, test item chapter number, and software development task book chapter number are still 198 rows, which need to be filled in according to the number of rows filled in the test purpose and test method columns, and filled down according to the first line of each test item; in addition, the test type is distinguished according to the first two characters in the read test requirement identifier: functional test, performance test, interface test, margin test, boundary test, human-computer interaction interface test, safety test, installability test, etc., and filled down in sequence.

[0075] S107, deleting the rows corresponding to the blank cells in the test target column;

[0076] In this example, all contents have been filled in through S101 to S106, and only the test purpose column and the test method column have a blank cell at the beginning of each test item. Through S107, all the rows corresponding to these blank cells are deleted to obtain the final generated test cases. In this example, 852 test cases are finally obtained.

[0077] P200 generates test outline test item module: performs test coverage analysis on the Excel version of the test case and generates a test content sufficiency analysis table;

[0078] Example 2

[0079] As a specific example, with the test case generated in Example 1, run P200 to generate the test outline test item module function. For details, please refer to Figure 4 , the steps include:

[0080] S201, merge all test objectives and corresponding test methods under the same test item into the test item description and test method cell contents in the test item of the word version test outline;

[0081] In this example, after 832 test cases are generated by Example 1, the test cases are adaptively changed according to actual conditions, and the final number of test cases is 852. These 852 test cases need to be combined with test item descriptions and test methods according to 198 test items.

[0082] S202, generate a word version table for each test item;

[0083] In this example, after completing step 121, all contents will be filled in according to the test item table content shown in Table 2, and a total of 198 test item tables will be generated. The test item name, test item identification, priority, tracking relationship, requirement description, test item description, and test method in the table are exactly the same as the "Test Case" page in "Test Case and Report Sheet.xls".

[0084] S203, generating a table title for each test item, including the test outline chapter number and the test item name;

[0085] In this example, the test item table generated by completing steps 121 to 122 has one more row than the format of Table 2. This row is located above the table shown in Table 2. The content of this row includes the test outline chapter number and the test item name. The function provided by VBA to convert cell content into paragraphs is used to convert this row into the test item table title.

[0086] S204, generate a test content adequacy analysis table to track the test items in the test outline with the software requirement specification chapter and the software development task book chapter.

[0087] In this example, the test content adequacy analysis table is shown in Table 1; by extracting the software requirement chapter number, software requirement description, test requirement identifier, test sub-item name, software development task book chapter number, and test type columns in the "Test Case" page of "Test Case and Report Sheet.xls", a test content adequacy analysis table for 198 test items is generated according to the format of Table 1.

[0088] Table 1 Test content adequacy analysis table

[0089]

[0090] P300 Check test item module: Check whether the test item content of the word version test outline is complete; including: check whether there are any abnormalities in the test item content of the word version test outline, and the abnormalities include: blank cells, cells with only labels but no actual content.

[0091] Example 3

[0092] As a specific embodiment, the test item document generated by Example 2 is used to run P300 to check the test item module function, including: checking whether there are blank cells or cells with only labels but no actual content in the test item content of the word version test outline, and displaying the inspection results.

[0093] In this example, the inspection results are displayed on the "Test Item Inspection" page of "Trial Example and Report.xls", including: the test item name in "Test Item.docx", the test item name in the test case, the comparison results, the number of test item descriptions, the number of test methods, the comparison results, and whether there are blank cells.

[0094] In this example, the normal process and the abnormal process are implemented separately:

[0095] In this example, the content of "Test Item.docx" is complete in the normal process. After running this program, the comparison results all show "consistent", and whether there are blank cells is listed as blank.

[0096] In this example, in the abnormal process, "Test Item.docx" sets some cells to empty, deletes some test item tables, and then changes some test item names to abnormal values. For example, the original test item name is "Add New Contract", which is changed to "Add New Contract 1" here. After running this program, the prompt "Inconsistent number of test items" is popped up first, and the inspection results are displayed in the "Test Item Check" page of "Test Case and Report Sheet.xls", including: the test item name in "Test Item.docx" and the test item name in the test case.

[0097] Go back to "Test Items.docx" to view the missing test item table. After adding the test item table at the corresponding position in "Test Items.docx", run the program again. A prompt will pop up saying "Please check the results on the "Test Item Check" page to make changes to the "Test Items.docx" document." Specific error information will be displayed on the "Test Item Check" page in "Test Cases and Reports.xls", including the number of test item descriptions, the number of test methods, the comparison results, and whether there are blank cells.

[0098] In the comparison result column, for test items whose number of test item descriptions and test methods are inconsistent, "The number of test item descriptions and test methods in XX test item are inconsistent" is displayed; for test items whose number of test item descriptions and test methods are consistent, "Consistent" is displayed;

[0099] In the column of whether there are blank cells, if there are blank cells, "XX test item has blank cells" is displayed; if there are no blank cells in the test item, the column is blank.

[0100] P400 Update test objectives and test methods module: Update the test objectives and test methods in the test item table of the word version test outline, and keep the rest of the content unchanged. This includes updating the test objectives and test methods to the test items in the word version test outline after the Excel version test cases are improved; the rest of the content includes: test item name, test item identification, priority, tracking relationship, requirement description, sufficiency requirements and passing criteria.

[0101] Example 4

[0102] As a specific embodiment, the test case Excel table and test item document generated by Embodiment 1 and Embodiment 2 are checked by Embodiment 3 and then run P400 to update the test purpose and test method module functions.

[0103] In this example, in order to achieve a more intuitive effect, the test item descriptions and test method contents in some test items of the test item document generated in Example 2 are directly deleted, and then the processed document is copied and pasted into "test item.docx". After the program runs, the deleted test item descriptions and test methods in "test item.docx" are checked, and they have been filled with the corresponding contents of the improved Excel version of the test case, and the test item name, test item identifier, priority, tracking relationship, requirement description and other contents in the test item of the test outline are kept unchanged.

[0104] As a specific embodiment, the test item document format is shown in Table 2, and the test case Excel table format is shown in Figure 3 As shown, all active pages in the Excel spreadsheet are as follows Figure 5 The interactive interface is as shown. Figure 6 shown.

[0105] As a specific embodiment, the flow chart is as follows Figure 1 As shown, in the interactive interface, "Generate ☆ word to Excel_test item" implements the function of generating word to excel test item module in Example 1; "Generate ☆ test outline test item" implements the function of generating test outline test item module in Example 2; "Check ☆ test item document" implements the function of checking test item module in Example 3; "Update word test content and test method" implements the function of updating test purpose and test method module in Example 4. All operations in the following examples, except for the necessary manual operation required to execute the abnormal process, are automatically executed by clicking "Execute" after selecting the corresponding macro, without manual intervention.

[0106] Table 2 Test item document format

[0107]

[0108] Through the present invention, an Excel version test case can be generated after the test items of the word version test outline are written, and a test coverage analysis can be performed after the test items of the Excel version test case are written, and the integrity check can be automatically performed on the test items of the word version test outline. After the test purpose and the test method in the Excel version test case are improved, these two parts of content can be updated to the test items of the word version test outline, and the remaining content is kept unchanged, thus saving a lot of time for repeated work. The VBA script compilation environment that Excel comes with is directly adopted, and no additional software needs to be installed, which can simplify office processes, and VBA is relatively simple and easy to use, and has good economy.

[0109] The above disclosures are only several specific embodiments of the present invention; however, the present invention is not limited thereto, and any changes that can be conceived by those skilled in the art should fall within the protection scope of the present invention.

Claims

1. A VBA-based test case automatic generation and test coverage analysis system, characterized in that: include: Generate Word to Excel test item module: Generate Excel test cases based on the Word version test outline test items; Generate test outline test item module: perform test coverage analysis on the Excel version of the test case and generate a test content sufficiency analysis table; Check the test item module: Check whether the test item content of the word version of the test outline is complete; Update the test objectives and test methods module: Update the test objectives and test methods in the test item table of the word version of the test outline, and keep the rest of the content unchanged; The word version of the test outline test item is a file storing test relationship data in a table, and the test relationship data includes: test item name, test item description, and test method; The Excel version of the test case is a table that stores test details, and the test details include: software requirement chapter number, software requirement description, test requirement identifier, test sub-item name, use case name, software development task book chapter number, use case description, test type input and operation instructions; The test content adequacy analysis table is a document used to track requirements, and the test item content includes: software requirement specifications; The test objectives correspond to the test item descriptions in the word version of the test outline and to the use case descriptions in the Excel version of the test case; the test methods in the test items in the word version of the test outline correspond to the input and operation instructions in the Excel version of the test case.

2. The VBA-based test case automatic generation and test coverage analysis system according to claim 1, characterized in that: The test case generation module includes: Replace all carriage returns in the Word version of the test item table with the separators set in the current Excel table; Extract all table contents in the word version test items to Excel tables; Divide the test purpose and test method columns in the Excel table into different rows according to the set separators; Check whether the "test purpose" and "test method" correspond to each other; Remove all special ending characters left by used cells; Fill in the remaining columns, where the remaining columns are columns other than "test purpose" and "test method"; Delete the rows corresponding to the blank cells in the test target column.

3. The VBA-based test case automatic generation and test coverage analysis system according to claim 2, characterized in that: The method of replacing all carriage returns in the word version test item table with the separators set in the current Excel table includes the following steps: First set up a special page in the Excel table for editing the separator, and read the separator into the program; Replace all carriage returns in the Word version of the test item table with separators, save and close the document.

4. The VBA-based test case automatic generation and test coverage analysis system according to claim 2, characterized in that: The method of extracting all table contents in the word version test items into an Excel table includes extracting all test item table contents in the test document into the Excel table one test item per row, and corresponding the elements in the word version test item table to specific columns in the Excel table.

5. The VBA-based test case automatic generation and test coverage analysis system according to claim 2, characterized in that: The test objectives and test method columns in the Excel table are divided into different rows according to the set separators, including extracting all the test objectives and test methods under each test item to different pages in the Excel table, dividing a row into multiple rows according to the set separators, and then pasting the divided test method column to the same page as the test objectives through a program, so that each row corresponds to only one test objective and test method.

6. The VBA-based test case automatic generation and test coverage analysis system according to claim 2, characterized in that: The check whether the "test purpose" and "test method" correspond one to one is specifically to check whether the number of "test purpose" and "test method" is consistent, and whether there are blank cells in the document; if the number of "test purpose" and "test method" is inconsistent or there are blank cells, the specific erroneous test item table information is given, and the operation is stopped to the next step.

7. The VBA-based test case automatic generation and test coverage analysis system according to claim 2, characterized in that: The remaining columns include: software requirement chapter number, software requirement description, test requirement identification, test sub-item name, test sub-item identification number and test type.

8. The VBA-based test case automatic generation and test coverage analysis system according to claim 1, characterized in that: Generate test item modules, including: Combine all the test objectives and corresponding test methods under the same test item into the test item description and test method cell contents in the word version of the test outline test item; Generate a separate word table for each test item; Generate a table title for each test item, including the test outline section number and test item name; Generate a test content adequacy analysis table to track the test items in the test outline with the software requirements specification chapters and the software development task book chapters.

9. The VBA-based test case automatic generation and test coverage analysis system according to claim 1, characterized in that: The module for checking test items includes: checking whether there are abnormalities in the test item content of the word version test outline, and the abnormalities include: blank cells and cells with only labels but no actual content.

10. The VBA-based test case automatic generation and test coverage analysis system according to claim 1, characterized in that: The updating of the test purpose and test method module includes updating the test purpose and test method part to the test item of the word version test outline after the Excel version test case is improved; The remaining contents include: test item name, test item identification, priority, traceability relationship, requirement description, sufficiency requirements and passing criteria.

Images