What you can read this article

結果.jpg

Use PySimpleGUI to get files and folders dialogs to get files, You can execute the process using the acquired file and display the log on the screen.

Overview

There are some articles and books that use Python as a de-VBA, and articles that use OpenPyXl to manipulate Excel files There are, but most of the files are started by CLI, and I don't see many articles that operate with GUI. This time, I will explain how to use PySimple GUI to do something equivalent to VBA GUI. For the basic operation of PySimpleGUI, please refer to If you use Tkinter, try using PySimpleGUI.

For example, if you want to read multiple files in Excel and combine them into one file, you may need the following operations and display.

Select multiple files to load
Display the selected file list
Option selection
Run
Log output of execution results

The code is as follows. This time, the function of Excel operation is not listed. It is only the explanation of the GUI part.

#Original file https://pysimplegui.trinket.io/demo-programs#/examples-for-reddit-posts/visual-basic-mockup

import PySimpleGUI as sg
from os.path import basename

frame1 = [[sg.Radio('Organize each sheet into one file', 1, key='-MULTI-SHEET-', default=True)],
          [sg.Radio('Put together in one file and one sheet', 1, key='-ONE-SHEET-')]]

col1 = [[sg.Button('Run')],
        [sg.Button('End')]]


layout = [[sg.Text('File selection', size=(15, 1), justification='right'),
          sg.InputText('File list', enable_events=True,),
          sg.FilesBrowse('Add file', key='-FILES-', file_types=(('Excel file', '*.xlsx'),))],
          [sg.Button('Copy log'), sg.Button('Clear log')],
          [sg.Output(size=(100, 5), key='-MULTILINE-')],
          [sg.Button('Clear the input list')],
          [sg.Listbox([], size=(100, 10), enable_events=True, key='-LIST-')],
          [sg.Frame('Processing content', frame1), sg.Column(col1)]]

window = sg.Window('Excel combination', layout)

new_files = []
new_file_names = []

while True:             # Event Loop
    event, values = window.read()
    if event in (None, 'End'):
        break

    if event == 'Run':
        print('Execute processing')
        print('File to be processed:', new_files)

        #Processing changes depending on the value of the radio button
        if values['-MULTI-SHEET-']:
            print('Combine multiple sheets into one file')
        elif values['-ONE-SHEET-']:
            print('Combine multiple sheets into one sheet')

        #Pop-up
        sg.popup('Processing ended normally')
    elif event == 'Clear log':
        print('Clear log')
        window.FindElement('-MULTILINE-').Update('')
    elif event == 'Copy log':
        window.FindElement('-MULTILINE-').Widget.clipboard_append(window.find_element('-MULTILINE-').Get())
        sg.popup('I copied the log')
    elif event == 'Clear the input list':
        print('Clear the input list')

        new_files.clear()
        new_file_names.clear()
        window['-LIST-'].update('')
    elif values['-FILES-'] != '':
        print('FilesBrowse')

        # TODO:It is necessary to process whether the files are the same for actual operation.
        new_files.extend(values['-FILES-'].split(';'))
        new_file_names.extend([basename(file_path) for file_path in new_files])

        print('Add file')
        window['-LIST-'].update(new_file_names)  #Display in the list box

window.close()

Below is an introduction to each function.

Select multiple files to read

There are the following three buttons (methods) to specify a file with PySimpleGUI.

FolderBrowse() --Read folder
FileBrowse() --Read one file
FilesBrowse() --Read one multiple file

Operationally, when you add it to the layout, a button is displayed, when you click it, a dialog is displayed, and the file selected in the dialog is displayed. You can get the selected file with an absolute path.

Below is an example of the code. In addition, this code has added functions by referring to the official Visual Basic Mockup. I will.

import PySimpleGUI as sg

sg.InputText('File list',enable_events=True,), sg.FilesBrowse('Add file', key='-FILES-', file_types=(("Excel file", "*.xlsx"),))],

If you get multiple files with FilesBrowse, the value is" Absolute path of file 1; Absolute path of file; ". You can get the file by splitting it with values ['-FILES-']. Split (';'))

About drag & d drop

The drag and drop function is not included in tkinter that comes with Python as standard. It seems that you can do it by installing the extension yourself.

How can I create a Drag and Drop interface?

PySimpleGUI is a wrapper for tkinter, so there is no drag and drop function. However, if you look at the Python 3.9 documentation, you will find the following page, so in the next version of 3.9 Drag and drop will be possible with tkinter that comes with Python, and drag and drop functions may be added in PySimple GUI.

tkinter.dnd --- Drag and drop support

Updated on February 6, 2020: I was officially informed. It seems that tkinter's drag and drop only supports in-app drag and drop. It was said that PySimpleGUIQt supports drag and drop of Explorer.

I think the tkinter drag and drop support is only within the application. I don't see how you can drag and drop a file from Windows explorer for example, which is the most likely source of a drag and drop.

It's supported in PySimpleGUIQt for the Input and Multiline elements.
— PySimpleGUI (@PySimpleGUI) February 15, 2020

# Display the selected file list To list the selected files, this time we will use a list box to display them. In PySimpleGUI, it is displayed using `Listbox ()`.

[sg.Listbox([], size=(100, 10), enable_events=True, key='-LIST-')],

To make the entered contents into a list box, use the ʻupdate ()method of thewindow` class to update.

window['-LIST-'].update(new_file_names)  #Display in the list box

Option selection

Radio buttons are used to select options.

frame1 = [[sg.Radio('Organize each sheet into one file',1, key='-MULTI-SHEET-', default=True)],
          [sg.Radio('Put together in one file and one sheet', 1, key='-ONE-SHEET-')]]

Which value of the radio button is selected at the time of execution is determined by whether the value set in key is True.

if values['-MULTI-SHEET-']:
    print('Combine multiple sheets into one file')
elif values['-ONE-SHEET-']:
    print('Combine multiple sheets into one sheet')

Log output of execution results

Use the ʻOutput ()element to display the execution on the screen. If this is placed, the contents described byprint ()` will be output.

[sg.Output(size=(100,5), key='-MULTILINE-'),],

Copy to clipboard

Clipboard related uses the following methods of tkinter

widget.clipboard_get() --Get the value stored in the clipboard.
widget.clipboard_clear() --Delete the contents of the clipboard.
widget.clipboard_append() --Add a value to the clipboard.

This time, use clipboard_append to copy the log value output to ʻOutput ()`.

window.FindElement('-MULTILINE-').Widget.clipboard_append( window.FindElement('-MULTILINE-').Get())

Summary

I introduced how to make a GUI equivalent to VBA with PySimpleGUI. Open the dialog and select the file. I think that the GUI that can process the selected file is useful.

Create a UI that replaces VBA with PySimpleGUI (file dialog, list, log output)