Python and Excel: Getting Started

Why do handle Excel data manually when you can automate it?

This is a user guide for using Python to rapidly read through Excel files and extract the data you need. I will be using Python 3.5.3 on a Windows 10 machine.

I’ve only been messing around in Python for a few months. I’m sure I am missing some Python best practices. This worked for me.

Setup IDE

I use both Nuclide and PyCharm. Both have Autocomplete, Code Autoformatting, Code Outline features. I like the Nuclide UI for coding. PyCharm has the advantage of allowing you to run and debug code. But you can also run your code with a terminal. Check them out below.

Python

Download PyCharm: Python IDE for Professional Developers by JetBrains

For some reason, Nuclide deletes past quotes when you hit ctrl-backspace or ctrl-delete. You can fix this by placing the following in your keymap.

'.editor':
 'ctrl-backspace': 'editor:delete-to-previous-word-boundary'
 'ctrl-delete': 'editor:delete-to-next-word-boundary'

Setup Packages

Packages are pieces of code you can import to your current Python script. I use pip to install packages. The Big 3 tools I’m using are below.

Python Data Analysis Library - pandas: Python Data Analysis Library

Creating Excel files with Python and XlsxWriter - XlsxWriter Documentation

Using PyInstaller - PyInstaller 3.3.1 documentation

pip install pandas
# Read Excel
pip install xlrd
# Write Excel
pip install XlsxWriter
pip install pyinstaller

Note: You may want to create a virtual environment (pip install virtualenv). That way when you create an executable with pyinstaller, it only grabs the necessary packages you need and not every package installed.

# Create virtual environments
# Each environment has its own pip packages
cd my_project_folder
virtualenv my_project
# Start and stop virtualenv
my_project\Scripts\activate.bat
my_project\Scripts\deactivate.bat
# Create a batch process in Task Scheduler
my_project\Scripts\activate.bat && python my_project\code.py
# Save pip package configuration
pip freeze > local_packages.txt
# Install pip packages
pip install -r local_packages.txt
# Delete all pip packages
pip freeze | xargs pip uninstall -y

Setup Notifications

Good logging practice in Python

I used the following guide for logging. This code snippet allows you to log information into a debug.log file.

import logging
def getLogger(): # Setup logging
 logging.basicConfig(level=logging.INFO)
 logger = logging.getLogger(__name__)
 handler = logging.FileHandler('debug.log', mode='w')
 formatter = logging.Formatter(
 '%(asctime)s - %(name)s - %(levelname)s: %(message)s')
 handler.setFormatter(formatter)
 logger.addHandler(handler)
 return logger
LOGGER = getLogger()
LOGGER.info('Opening {}'.format(filename))

You may also wish to have Windows popup dialog. The following will display a simple popup.

from ctypes import windll
try:
 windll.user32.MessageBoxW(0, 'Reports created', 'Success', 0)
except FileNotFoundError:
 logger.error('Error', exc_info=True)
 windll.user32.MessageBoxW(0, 'Cannot find input files', 'Error', 0)
except PermissionError:
 logger.error('Error', exc_info=True)
 windll.user32.MessageBoxW(0, 'File in use. Please close', 'Error', 0)
except Exception:
 logger.error('Error', exc_info=True)
 windll.user32.MessageBoxW(0, 'Check log', 'Error', 0)