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

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.

  1. Select multiple files to load
  2. Display the selected file list
  3. Option selection
  4. Run
  5. 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.

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.

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.

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.

# 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

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.

Recommended Posts

Create a UI that replaces VBA with PySimpleGUI (file dialog, list, log output)
Create a cylinder with open3d + STL file output
Create a file uploader with Django
Make it possible to output a log to a file with go echo
Create a large text file with shellscript
Create a VM with a YAML file (KVM)
Create a GUI executable file created with tkinter
Create a game UI from scratch with pygame2!
Create a PDF file with a random page size
Create a page that loads infinitely with python
Create a Photoshop format file (.psd) with python
Open a file dialog with a python GUI (tkinter.filedialog)
Create an exe file that works in a Windows environment without Python with PyInstaller
Create a chatbot that supports free input with Word2Vec
Create a Todo app with Django ③ Create a task list page
[Python] Create a Tkinter program distribution file with cx_Freeze
Create a 2d CAD file ".dxf" with python [ezdxf]
Create a web application that recognizes numbers with a neural network
Create a PythonBox that outputs with Random after PEPPER Input
[Python] Create a file & folder path specification screen with tkinter
Create a list in Python with all followers on twitter
Let's create a script that registers with Ideone.com in Python.
Create a life game that is manually updated with tkinter
Tornado-Let's create a Web API that easily returns JSON with JSON
Create a web API that can deliver images with Django