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

A result is simple as follows.

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.

Originally posted on SH Fintech Modeling.