Anyone who has used SAP knows that its report export function is very powerful. It supports a variety of export formats. The most commonly used one is to export to excel file.However, after SAP exported the excel file, it always kindly helped us to automatically open the exported report, but this kind of "kindness" brought difficulties to our office automation.We use python to do many SAP automated office scenarios, and the exported data often needs to be processed by libraries such as pandas.Therefore, we don't want the file to be opened by excel when it is called, which will bring permission problems such as file occupation.
So, if we want to turn off the default function of [SAP automatically opens the exported excel file], is there a good way?There must be, Xiao Climb provides two feasible ideas here.
Method 1: Use Windows API to close open reports
Specifically, after the command to export the file is sent to the SAP GUI, we can use the sap scripting API to get the text of the message in the lower left corner of SAP (as shown in the figure below):
Then it is judged that the data of SAP has been successfully transmitted. After a short time, SAP will automatically open the form.We can use the FindWindow, IsVisible and other methods in the windows user32 API to traverse the window in the background until the Excel file that is open and displayed is captured, and then use the SendMessage method to send a window message to Excel and close the Excel file.
PS: Many people write scripts in Excel VBA to drive SAP. It should be noted here that in the process of exporting data to excel, SAP creates a new Excel process, and finally opens the report under this process.Obviously not in the same process as the Application object in our current VBA, so we can't use the following code to easily close these Excel report files, give up this fantasy, Sao Nian:
For Each wb in WorkbooksIf wb.name=reportFileName thenwb.close FalseEnd IFNext
Method 2: Use SAP's export file to clipboard function to restore the clipboard content to an Excel file
In terms of operation, we must first use the native method of SAP: export to [local file] - [save list in clipboard].After these two operations, our report content has been transferred to the windows clipboard.At this point, we can use the python pywin32 library to show our strength.The Pywin32 library encapsulates almost all the API methods needed to help with Windows UI automation.For example, we can use the win32clipboard module to extract the clipboard content, the code example is as follows:
import win32clipboard as wdef get_text():w.OpenClipboard()copy_text=w.GetClipboardData(win32con.CF_UNICODETEXT)w.CloseClipboard()return copy_text
With this get_text method, we can observe the format of the content (long string) stored in the clipboard by SAP, and how do we extract the title field of the report and the corresponding data for each row.Then, we can use the Xlsxwriter library to instantiate a Workbook object, and then write the table title and row data into the object respectively, and finally save it, which realizes the entire data export process to Excel.
It should be emphasized that with this method, the Excel report file will obviously not be opened automatically.We can also flexibly parse the clipboard content and directly store it into a pandas DataFrame object, and then use the to_excel method of the DataFrame object to easily export Excel files, or directly use pandas to realize subsequent data processing, which is flexible and convenient.
Interested in children's shoes, you may wish to use python code to implement the ideas in method two.Believe me, after success, you will gain a full sense of accomplishment just like Xiao Climb~~
Welcome to scan the code and follow my official account to get more knowledge of crawler and data analysis!
