Use a Python script to read and write on Excel File in Python

This post demonstrates how to utilize the xlwings Python package to execute a Python script to read and write Excel in Python.

Running a Python script to read and write Excel file in Python

Unlike the previous post, I will demonstrate how to read and write an Excel file in Python without relying on a button in Excel.

By bypassing the use of an Excel button, we can verify the functionality in Python to ensure it operates correctly. Once confirmed, we can then integrate this process by clicking a button within Excel.

This approach is preferred because testing Python functionalities directly from Excel right from the beginning can be quite cumbersome.

Python code

The Python code below handles three inputs: whether the file is new or existing, the file name (whether it's new or existing), and whether the Excel instance should be visible or not.

import os import xlwings as xw import sys   # Check if a file exists and exit with a warning message if it does.     def exit_file_exists(base_name, directory):       filename = os.path.join(directory, base_name)     if os.path.exists(filename):         print(f"""File '{filename}' already exists.                Exiting the program.""")         sys.exit(0)  # Use 0 for a normal termination     return filename   #=================================================== # Input #=================================================== # new or existing file b_excel_new_file = True #False   # new or existing filename file_name_w_ext = "TestBook9.xlsm"    # Excel instance visible or not b_excel_visible = True #False #===================================================   # Excel instance app = xw.App(visible=b_excel_visible)     # create file  if b_excel_new_file == True:          # Get the directory of the running Python file     current_directory = os.path.dirname(os.path.abspath(__file__))     # Check if the file exists     filename = exit_file_exists(file_name_w_ext, current_directory)          # New workbook     wb = xw.Book()     wb.save(filename)     print(f"File '{filename}' is newly created.")      else:       filename = os.path.join(os.path.dirname(os.path.abspath(__file__)),                              file_name_w_ext)     wb = xw.Book(filename)     # select first sheet sht1 = wb.sheets[0]   # 1) one cell sht1.range('A1').value = 'Hello World'   # 2) a range of cells sht1.range('A3:D4').value = 11   # 3) formula sht1.range('F3').formula = '=SUM(A3:D3)'   #4) clear cells sht1.range('A3:B4').clear()   # exit instance only when xw.App(visible=False) if b_excel_visible == False:     wb.save()  # save      app.kill() # shut down excel process   Colored by Color Scripter

cs

A result is simple as folows.

After creating a new file named TestBook9.xlsm, attempting to create another new file with the same name, TestBook9.xlsm, will result in an error message. The program will then exit to ensure the existing TestBook9.xlsm file is not overwritten.

The folder and file names were too long, so they have been abbreviated to "aaa," "bbb," and "ccc" for convenience.

runfile('aaa.py', wdir='bbb') File 'ccc\TestBook9.xlsm' is newly created.   runfile('aaa.py', wdir='bbb') File 'ccc\TestBook9.xlsm' already exists.       Exiting the program.   Colored by Color Scripter

cs