Is there a way to consistently visually navigate excel in Python?

I am looking to automate a reporting process for my job, which involves taking a screenshot of multiple excel sheets within the same workbook. Right now, I am using pyAutoGui, which works O.K.

The issue I am running into is consistency — while it works on my computer, I need this automation to be both scalable and for other people to run it on their machines and have it function the same. This takes any sort of “click” function out of the question, because I will not know what screen size the user is running (e.g., monitor, laptop, etc.). I do know that it will always be run on a windows machine, however.

I am relatively new to python, so I apologize if this is somewhat unclear, and will provide any clarification needed.

In fact, I have been navigating the windows desktop and excel interface entirely using hotkeys — the one click in the program is a click on the full-screened “find and replace” menu to make sure it is in the foregroun for when I send keys for the search:

# open excel sheet on first time

    if (wbIndex == 0):

        pyg.press("win")

        time.sleep(1)

        pyg.write("[EXCEL NAME]", 0.01)

        time.sleep(1)

        pyg.press("enter")

        time.sleep(5)

        try:

            pyg.getWindowsWithTitle("[EXCEL WINDOW TITLE]")[0].maximize

        except:

            print("EXCEL SHEET NOT FOUND - Ending Program")

            quit()

        time.sleep(2)

    #open excel navigator to navigate to correct sheet
    pyg.hotkey("ctrl", "f")

    time.sleep(2)

    try:

        pyg.getWindowsWithTitle("Find and Replace")[0].maximize()

    except Exception as e:

        print("Could NOT find Search window -- exiting program.")

        time.sleep(15)

        quit()

    time.sleep(1)
    #click to bring search window to the foreground
    pyg.click(1400,600)

    time.sleep(0.5)
    #tab to the search menu for the find and replace meny
    pyg.press("tab")

    time.sleep(0.25)

    pyg.press("tab")

    time.sleep(0.25)
    #sheet title that I want
    pyg.write(clientName)

    time.sleep(0.5)
    #if it is first search, navigate to make sure it can search within workbook, not sheet
    if wbIndex == 0:

        pyg.press("right")

        time.sleep(0.25)

        pyg.press("tab")

        time.sleep(0.25)

        pyg.press("tab")

        time.sleep(0.25)

        pyg.press("down")

        time.sleep(0.25)

        pyg.press("down")

        time.sleep(0.5)

        pyg.press("enter")

        time.sleep(0.5)
    #navigate down to "next" button and press enter to navigate to the correct sheet
    pyg.press("enter")

    time.sleep(2)

    pyg.press("esc")

    time.sleep(1)
    ```
    That process gets me to the first page I need, at which point I screenshot. Then, I navigate to the next page I need, which will always be the following sheet.
    ```

    pyg.hotkey("ctrl", "pagedown")

    time.sleep(1)

    pyg.hotkey("ctrl", "g")

    time.sleep(0.25)
    #scroll up to first cell with cell navigation
    pyg.write("A1")

    time.sleep(0.1)

    pyg.press("enter")

    time.sleep(1)

And then I would screenshot again.

Again, this process works on my computer, but when testing on other computers, it is extremely inconsistent, and I am curious if there is a more robust way to go about this process that could reduce errors. My most common errors are that it cannot find and maximize the find and replace window, and that it is improperly navigating the find and replace window.

I have tried navigating via “alt + w” and then “k” for the navigation tab, however, I then run into the problem that I have no way to automatically close the navigation side bar, which interferes with the screenshot I want to capture automatically.

Example of performing some of the functions you appear to need with Xlwings.
Win32 will use similar Excel commands but might be more useful for Windows operations external to Excel.

You can use Xlwings with visible= either True or False. If it’s True then you can observe the workbook/sheet and changes made to it in real time and if needed pause the execution to view changes.

You can use the Excel Find/Replace function, with simple syntax specifying what to look for and what to replace with. Or you can set all/some of the options available.
Note the code uses the Excel enumerations which are sourced from the Xlwings constants. Or you can replace these with the actual enumerated value if preferred;
e.g. LookAt.xlPart could be replaced by the value 2.

You can save a range of cells as an image using a few options. I’ve shown just ‘copy to clipboard’ and using the ‘CopyPicture’ option from Excel here.
Excels CopyPicture has a few options that can be set, again the code uses the Excel enumerations or enumerated value.
You can specify a range of cells or used_range which is all the cells that have a value and save with differing formats, PNG, JPG etc.

You can save the sheet at the completion with same or new filename if desired. Or leave this line out if the changes are to be discarded.

import xlwings as xw
from xlwings.constants import LookAt, SearchOrder, PictureAppearance, CopyPictureFormat
from PIL import ImageGrab

xlReplaceFormula = 0  # Replace Formula (0)
xlReplaceFormula2 = 1  # Replace Formula 2 (1)

filepath = 'foo.xlsx'
sheetname = 'Sheet1'

searchfor = 'text1'
replacewith = 'text2'

with xw.App(visible=True) as app:
    wb = xw.Book(filepath)  # Load the Workbook 
    ws = wb.sheets[sheetname]  # Select the Sheet to use

    ### Find/Replace function
    # ws.used_range.api.Replace(text1, text2)  # Simple syntax
    ### Set all options
    ws.used_range.api.Replace(What=text1,
                              Replacement=text2,
                              LookAt=LookAt.xlPart,
                              SearchOrder=SearchOrder.xlByRows,
                              MatchCase=False,
                              SearchFormat=False,
                              ReplaceFormat=False,
                              FormulaVersion=xlReplaceFormula2
                              )

    ### Copy a data range to save as an image
    ### Using copy
    # ws.range('A1:H20').copy(destination=None)  # Copy a range
    ws.range(ws.used_range).copy(destination=None)  # Copy the used range
    img = ImageGrab.grabclipboard()
    img.save('test_jpg.jpeg', 'JPEG')

    ### Using CopyPicture
    # ws.range('A1:H20').api.CopyPicture(Appearance=PictureAppearance.xlScreen, Format=CopyPictureFormat.xlPicture)
    ws.range(ws.used_range).api.CopyPicture(Appearance=1, Format=-4147)  # Using enumerated values
    ws.api.Paste()
    image = ws.pictures[0]  # Should be one only in the list 
    image.api.Copy()
    img_png = ImageGrab.grabclipboard()
    img_png.save('test_png.png')

    ### Stop the execution to observe the Sheet at any time if desired
    input("Hit any key when ready!")

    ### Save the changes to the Sheet if required
    wb.save('foo_out.xlsx')

Filed under: Kiến thức lập trình - @ 05:58

Thẻ: pythonexcelautomationpyautogui

Thiết kế website giá rẻ

Danh mục

Is there a way to consistently visually navigate excel in Python?