Opening Excel from Python using the Subprocess Module

Python has a lot of great IDEs and computational notebooks for viewing data; however, sometimes there's no replacement for firing up excel and getting your hands dirty with the data directly.

One thing I've found help is using the subprocess module to open open the workbook for you once your process is done. This is especially useful if your deliverable happens to be an excel workbook (the horror!). Below is an example of a script template I use to pull data from a database, apply some transformations, output the results or some diagnostics of the results to excel, and then open that excel file directly from python.

  import os
import argparse
import subprocess
import datetime as dt
import psycopg2
from connection_config import cc

def main():
    args = parse_args()
    f = args.filename
    sql = open(f, 'r').read()

    with psycopg2.connect(**cc) as conn:
        df = pd.read_sql(sql, conn)

    """
    Apply transformations if necessary
    df = df...
    """

    ts = dt.datetime.now().strftime('%Y-%m-%d.%H.%M')
    fout = os.path.join(
        'data',
        'f_test_{}.xlsx'.format(ts)
    )
    df.to_excel(fout, index=False)
    subprocess.run(['open', fout], check=True)

def parse_args():
    parser = argparse.ArgumentParser()
    parser.add_argument('filename')
    args = parser.parse_args()
    return args

if __name__ == '__main__':
    main()

Hope you find this as helpful as I have!