A method for realizing Excel pivot table picking based on RPA

Abstract

The application discloses a kind of based on RPA realization Excel data pivot table pickup method, including RPA robot, including the following steps: S1, application RPA robot reads the data pivot table in original Excel;S2, data pivot table is set event listening;S3, return to original Excel and click data pivot table relevant area, obtain the entire data pivot table of this sheet, then determine the data pivot table object selected currently;S4, according to data pivot table object, the encoding information of data pivot table is loaded into the program of RPA robot;S5, using RPA robot, the pivot table setting rule corresponding to encoding information is applied to current Excel object.The present application can obtain existing data pivot table configuration information, and then be applied to other arbitrary Excel by RPA robot, improve the automation execution efficiency of RPA.

Description

Technical Field

[0001] This invention relates to the field of RPA technology, and more specifically to a method for picking data from an Excel pivot table based on RPA. Background Technology

[0002] Robotic Process Automation (RPA) is business process automation software based on software robots. This technology can simulate human behavior, automating existing manual tasks or processes, significantly improving work efficiency. Excel pivot tables are interactive tables capable of calculations such as summation and counting. Excel is highly flexible and powerful, allowing for dynamic layout changes to analyze data in different ways and rearranging row numbers, column headers, and page fields. However, existing RPA tools can only perform simple write and delete operations when processing Excel data. For complex data processing, it's often necessary to separate from RPA and use pivot tables in Excel to process the data, followed by further RPA processing. This necessitates human intervention in data handling within the RPA process, severely impacting automation efficiency. Summary of the Invention

[0003] The purpose of this invention is to provide a method for retrieving Excel pivot tables based on RPA. This invention can obtain existing pivot table configuration information and then apply it to any other Excel file via an RPA robot, improving the automation efficiency of RPA.

[0004] To solve the above-mentioned technical problems, the present invention provides the following technical solution: A method for picking Excel pivot tables based on RPA, including an RPA robot, comprising the following steps:

[0005] S1. Use an RPA robot to read the pivot table in the original Excel file;

[0006] S2. Set up event listeners for the pivot table;

[0007] S3. Return to the original Excel file and click on the relevant area of ​​the pivot table to get all the pivot tables on that sheet, and then select the currently selected pivot table object.

[0008] S4. Load the pivot table's encoding information into the RPA robot's program based on the pivot table object;

[0009] S5. Use an RPA robot to apply the pivot table setting rules corresponding to the coded information to the current Excel object.

[0010] The above-described method for retrieving Excel pivot tables based on RPA, in step S1, the reading of the pivot table further includes monitoring whether there is an open Excel or WPS process locally, as follows:

[0011] a) Obtain the COM object using the MarshalEx.GetActiveObject method;

[0012] b) Use Process.GetProcessesByName to check if a corresponding Excel or WPS process exists;

[0013] c) If both conditions are met, return the process instance; otherwise, indicate that no Excel file is open.

[0014] In the aforementioned method for picking Excel pivot tables based on RPA, step S2 includes setting event listeners for workbook activation, sheet activation, sheet selection change, and workbook before closing. Workbook activation is used to obtain the currently active work set; sheet activation is used to obtain the active sheet in the current work set; sheet selection change is triggered when the user changes the selected area of ​​the current sheet to obtain the area selected by the user; and workbook before closing is used to release the corresponding memory resources of the program when the user closes the Excel window.

[0015] In the aforementioned method for retrieving Excel pivot tables based on RPA, step S3 involves obtaining all pivot tables of the currently active sheet object using the PivotTables method.

[0016] In the aforementioned method for picking Excel pivot tables based on RPA, step S3 involves comparing the selected pivot table object with the area clicked by the user's mouse using the Intersect method.

[0017] Compared to existing technologies, this invention uses an RPA robot to read pivot tables from the original Excel file, sets event listeners for these pivot tables, returns to the original Excel file, clicks on the relevant area of ​​the pivot table, retrieves all pivot tables on that sheet, identifies the currently selected pivot table object, loads the pivot table's encoding information into the RPA robot's program, and finally uses the RPA robot to apply the pivot table settings corresponding to the encoding information to the current Excel object. This invention can automatically obtain existing pivot table configuration information, facilitating the application of pivot tables to any other Excel file, achieving seamless integration of RPA operations on Excel. All necessary operations can be completed in a single process without interruption. Furthermore, this invention can monitor locally running Excel processes or WPS, ensuring compatibility with both Microsoft Excel and WPS without version issues. Attached Figure Description

[0018] Figure 1 This is a flowchart illustrating the present invention;

[0019] Figure 2 This is a diagram of a pivot table;

[0020] Figure 3 This is a schematic diagram of an RPA robot obtaining a pivot table;

[0021] Figure 4 This is a diagram illustrating the acquisition of coded information in a pivot table. Detailed Implementation

[0022] The present invention will be further described below with reference to the embodiments and accompanying drawings, but this should not be construed as limiting the present invention.

[0023] Example: A method for picking Excel pivot tables based on RPA, including an RPA robot, which consists of an instruction set, a process designer, a result debugger, a process manager, a global variable manager, and a toolbar.

[0024] Instruction set: Used to store and manage RPA instructions. Users can select the instructions they need and add them to the process designer by double-clicking or dragging and dropping for free design.

[0025] Process Designer: Provides users with a visual RPA process editing platform.

[0026] Results debugger: Used to display program error messages, log information, and other debugging data.

[0027] Process Manager: Manages RPA processes and process files such as Python scripts.

[0028] Global Variable Manager: Manages global variables.

[0029] Toolbar: Includes workflow arrangement, debugging functions, and intelligent recording functions.

[0030] This involves using an RPA robot to retrieve data from an Excel pivot table. The RPA robot is configured with relevant RPA commands, pivot table parameters, cell formatting parameters, and custom sorting parameters. Figure 1 As shown, it includes the following steps:

[0031] S1. Use an RPA robot to read the pivot tables in the original Excel file; the original Excel file can be a previously used file with pivot tables, or a newly created Excel file can be used to create pivot tables with the corresponding functions, such as... Figure 2 As shown; create a new application in the RPA robot, open the "New PivotTable" command in Excel's advanced commands, click "Point in Excel", and a "Get PivotTable" pop-up window will appear. Select the created pivot table in Excel to retrieve the pivot table data, as shown. Figure 3 As shown. Reading the pivot table also includes monitoring whether an Excel or WPS process is running locally, with the following steps:

[0032] a) Obtain the COM object using the MarshalEx.GetActiveObject method;

[0033] b) Use Process.GetProcessesByName to check if a corresponding Excel or WPS process exists;

[0034] c) If both conditions are met, return the process instance; otherwise, indicate that no Excel file is open.

[0035] S2. Set event listeners for the pivot table; the event listener settings include WorkbookActivate, SheetActivate, SheetSelectionChange, and WorkbookBeforeClose;

[0036] The workbook activation is used to obtain the currently active work set;

[0037] The sheet page activation is used to obtain the active sheet page in the current work set;

[0038] The sheet change selection is triggered when the user obtains a change in the currently selected area of ​​the sheet, and the user selects the area.

[0039] The "Workbook Before Closing" setting is used to release the program's memory resources when the user closes the Excel window.

[0040] S3. By listening to a series of events in step 2, when the user returns to the original Excel file and clicks on the relevant area of ​​the pivot table, the PivotTables method of the currently active sheet object can be used to obtain all the pivot tables of that sheet. Then, the Intersect method is used to compare with the area clicked by the user's mouse to determine which pivot table object is currently selected, and then the result is returned.

[0041] S4. Load the pivot table's encoding information into the RPA robot's program based on the pivot table object, such as... Figure 4 As shown. The coding information of a pivot table is the pivot table's settings. These settings can be used to generate corresponding commands and output, or they can be modified by the user.

[0042] S5. Use an RPA robot to apply the pivot table settings rules corresponding to the coded information to the current Excel object. When running the "Create PivotTable" command in the RPA robot, the existing pivot table can be easily transferred to other Excel files for use.

[0043] In summary, this invention can automatically acquire existing pivot table configuration information, facilitating the application of pivot tables to any other Excel file, achieving seamless integration of RPA operations on Excel. All necessary operations can be completed in a single process without interruption. Furthermore, this invention can monitor locally running Excel or WPS processes, ensuring compatibility with both Microsoft Excel and WPS without version issues.

Claims

1. A method for picking data from an Excel pivot table based on RPA, comprising an RPA robot, characterized in that: Includes the following steps: S1. Use an RPA robot to read the pivot table in the original Excel file; S2. Set up event listeners for the pivot table; S3. Return to the original Excel file and click on the relevant area of ​​the pivot table to get all the pivot tables on the sheet, and then select the currently selected pivot table object. S4. Load the pivot table's encoding information into the RPA robot's program based on the pivot table object; S5. Use an RPA robot to apply the pivot table setting rules corresponding to the coded information to the current Excel object; In step S1, reading the pivot table also includes monitoring whether there is an open Excel or WPS process locally, as follows: a) Obtain the COM object using the MarshalEx.GetActiveObject method; b) Use Process.GetProcessesByName to check if a corresponding Excel or WPS process exists; c) If both conditions are met, return the process instance; otherwise, indicate that no Excel file is open. In step S3, the Intersect method is used to compare the selected pivot table object with the area clicked by the user's mouse to determine the currently selected pivot table object.

2. The method for extracting data from an Excel pivot table based on RPA according to claim 1, characterized in that: In step S2, the event listener settings include workbook activation, sheet activation, sheet selection change, and workbook before closing; workbook activation is used to obtain the currently active work set; sheet activation is used to obtain the active sheet in the current work set; sheet selection change is triggered when the user changes the selected area of ​​the current sheet to obtain the area selected by the user; and workbook before closing is used to release the corresponding memory resources of the program when the user closes the Excel window.

3. The method for extracting data from an Excel pivot table based on RPA according to claim 1, characterized in that: In step S3, all pivot tables of the sheet are obtained through the PivotTables method of the currently active sheet object.

Images