Automate the Boring Stuff with Python

by Al Sweigart

  The pprint.pformat() function produces a string that itself is formatted as valid Python code. By outputting it to a text file named census2010.py, you’ve generated a Python program from your Python program! This may seem complicated, but the advantage is that you can now import census2010.py just like any other Python module. In the interactive shell, change the current working directory to the folder with your newly created census2010.py file (on my laptop, this is C:Python34), and then import it:

  >>> import os >>> os.chdir('C:\Python34') >>> import census2010 >>> census2010.allData['AK']['Anchorage'] {'pop': 291826, 'tracts': 55} >>> anchoragePop = census2010.allData['AK']['Anchorage']['pop'] >>> print('The 2010 population of Anchorage was ' + str(anchoragePop)) The 2010 population of Anchorage was 291826

  The readCensusExcel.py program was throwaway code: Once you have its results saved to census2010.py, you won’t need to run the program again. Whenever you need the county data, you can just run import census2010.

  Calculating this data by hand would have taken hours; this program did it in a few seconds. Using OpenPyXL, you will have no trouble extracting information that is saved to an Excel spreadsheet and performing calculations on it. You can download the complete program from http://nostarch.com/automatestuff/.

  Ideas for Similar Programs

  Many businesses and offices use Excel to store various types of data, and it’s not uncommon for spreadsheets to become large and unwieldy. Any program that parses an Excel spreadsheet has a similar structure: It loads the spreadsheet file, preps some variables or data structures, and then loops through each of the rows in the spreadsheet. Such a program could do the following:

  Compare data across multiple rows in a spreadsheet.

  Open multiple Excel files and compare data between spreadsheets.

  Check whether a spreadsheet has blank rows or invalid data in any cells and alert the user if it does.

  Read data from a spreadsheet and use it as the input for your Python programs.

  Writing Excel Documents

  OpenPyXL also provides ways of writing data, meaning that your programs can create and edit spreadsheet files. With Python, it’s simple to create spreadsheets with thousands of rows of data.

  Creating and Saving Excel Documents

  Call the openpyxl.Workbook() function to create a new, blank Workbook object. Enter the following into the interactive shell:

  >>> import openpyxl >>> wb = openpyxl.Workbook() >>> wb.get_sheet_names() ['Sheet'] >>> sheet = wb.get_active_sheet() >>> sheet.title 'Sheet' >>> sheet.title = 'Spam Bacon Eggs Sheet' >>> wb.get_sheet_names() ['Spam Bacon Eggs Sheet']

  The workbook will start off with a single sheet named Sheet. You can change the name of the sheet by storing a new string in its title attribute.

  Any time you modify the Workbook object or its sheets and cells, the spreadsheet file will not be saved until you call the save() workbook method. Enter the following into the interactive shell (with example.xlsx in the current working directory):

  >>> import openpyxl >>> wb = openpyxl.load_workbook('example.xlsx') >>> sheet = wb.get_active_sheet() >>> sheet.title = 'Spam Spam Spam' >>> wb.save('example_copy.xlsx')

  Here, we change the name of our sheet. To save our changes, we pass a filename as a string to the save() method. Passing a different filename than the original, such as 'example_copy.xlsx', saves the changes to a copy of the spreadsheet.

  Whenever you edit a spreadsheet you’ve loaded from a file, you should always save the new, edited spreadsheet to a different filename than the original. That way, you’ll still have the original spreadsheet file to work with in case a bug in your code caused the new, saved file to have incorrect or corrupt data.

  Creating and Removing Sheets

  Sheets can be added to and removed from a workbook with the create_sheet() and remove_sheet() methods. Enter the following into the interactive shell:

  >>> import openpyxl >>> wb = openpyxl.Workbook() >>> wb.get_sheet_names() ['Sheet'] >>> wb.create_sheet() >>> wb.get_sheet_names() ['Sheet', 'Sheet1'] >>> wb.create_sheet(index=0, title='First Sheet') >>> wb.get_sheet_names() ['First Sheet', 'Sheet', 'Sheet1'] >>> wb.create_sheet(index=2, title='Middle Sheet') >>> wb.get_sheet_names() ['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1']

  The create_sheet() method returns a new Worksheet object named SheetX, which by default is set to be the last sheet in the workbook. Optionally, the index and name of the new sheet can be specified with the index and title keyword arguments.

  Continue the previous example by entering the following:

  >>> wb.get_sheet_names() ['First Sheet', 'Sheet', 'Middle Sheet', 'Sheet1'] >>> wb.remove_sheet(wb.get_sheet_by_name('Middle Sheet')) >>> wb.remove_sheet(wb.get_sheet_by_name('Sheet1')) >>> wb.get_sheet_names() ['First Sheet', 'Sheet']

  The remove_sheet() method takes a Worksheet object, not a string of the sheet name, as its argument. If you know only the name of a sheet you want to remove, call get_sheet_by_name() and pass its return value into remove_sheet().

  Remember to call the save() method to save the changes after adding sheets to or removing sheets from the workbook.

  Writing Values to Cells

  Writing values to cells is much like writing values to keys in a dictionary. Enter this into the interactive shell:

  >>> import openpyxl >>> wb = openpyxl.Workbook() >>> sheet = wb.get_sheet_by_name('Sheet') >>> sheet['A1'] = 'Hello world!' >>> sheet['A1'].value 'Hello world!'

  If you have the cell’s coordinate as a string, you can use it just like a dictionary key on the Worksheet object to specify which cell to write to.

  Project: Updating a Spreadsheet

  In this project, you’ll write a program to update cells in a spreadsheet of produce sales. Your program will look through the spreadsheet, find specific kinds of produce, and update their prices. Download this spreadsheet from http://nostarch.com/automatestuff/. Figure 12-3 shows what the spreadsheet looks like.

  Figure 12-3. A spreadsheet of produce sales

  Each row represents an individual sale. The columns are the type of produce sold (A), the cost per pound of that produce (B), the number of pounds sold (C), and the total revenue from the sale (D). The TOTAL column is set to the Excel formula =ROUND(B3*C3, 2), which multiplies the cost per pound by the number of pounds sold and rounds the result to the nearest cent. With this formula, the cells in the TOTAL column will automatically update themselves if there is a change in column B or C.

  Now imagine that the prices of garlic, celery, and lemons were entered incorrectly, leaving you with the boring task of going through thousands of rows in this spreadsheet to update the cost per pound for any garlic, celery, and lemon rows. You can’t do a simple find-and-replace for the price because there might be other items with the same price that you don’t want to mistakenly “correct.” For thousands of rows, this would take hours to do by hand. But you can write a program that can accomplish this in seconds.

  Your program does the following:

  Loops over all the rows.

  If the row is for garlic, celery, or lemons, changes the price.

  This means your code will need to do the following:

  Open the spreadsheet file.

  For each row, check whether the value in column A is Celery, Garlic, or Lemon.

  If it is, update the price in column B.

  Save the spreadsheet to a new file (so that you don’t lose the old spreadsheet, just in case).

  Step 1: Set Up a Data Structure with the Update Information

  The prices that you need to update are as follows:

  Celery

  1.19

  Garlic

  3.07

  Lemon

  1.27

  You could write code like this:

  if produceName == 'Celery': cellObj = 1.19 if produceName == 'Garlic': cellObj = 3.07 if produceName == 'Lemon': cellObj = 1.27

  Having the produce and updated price
data hardcoded like this is a bit inelegant. If you needed to update the spreadsheet again with different prices or different produce, you would have to change a lot of the code. Every time you change code, you risk introducing bugs.

  A more flexible solution is to store the corrected price information in a dictionary and write your code to use this data structure. In a new file editor window, enter the following code:

  #! python3 # updateProduce.py - Corrects costs in produce sales spreadsheet. import openpyxl wb = openpyxl.load_workbook('produceSales.xlsx') sheet = wb.get_sheet_by_name('Sheet') # The produce types and their updated prices PRICE_UPDATES = {'Garlic': 3.07, 'Celery': 1.19, 'Lemon': 1.27} # TODO: Loop through the rows and update the prices.

  Save this as updateProduce.py. If you need to update the spreadsheet again, you’ll need to update only the PRICE_UPDATES dictionary, not any other code.

  Step 2: Check All Rows and Update Incorrect Prices

  The next part of the program will loop through all the rows in the spreadsheet. Add the following code to the bottom of updateProduce.py:

  #! python3 # updateProduce.py - Corrects costs in produce sales spreadsheet. --snip-- # Loop through the rows and update the prices. ➊ for rowNum in range(2, sheet.get_highest_row()): # skip the first row ➋ produceName = sheet.cell(row=rowNum, column=1).value ➌ if produceName in PRICE_UPDATES: sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName] ➍ wb.save('updatedProduceSales.xlsx')

  We loop through the rows starting at row 2, since row 1 is just the header ➊. The cell in column 1 (that is, column A) will be stored in the variable produceName ➋. If produceName exists as a key in the PRICE_UPDATES dictionary ➌, then you know this is a row that must have its price corrected. The correct price will be in PRICE_UPDATES[produceName].

  Notice how clean using PRICE_UPDATES makes the code. Only one if statement, rather than code like if produceName == 'Garlic':, is necessary for every type of produce to update. And since the code uses the PRICE_UPDATES dictionary instead of hardcoding the produce names and updated costs into the for loop, you modify only the PRICE_UPDATES dictionary and not the code if the produce sales spreadsheet needs additional changes.

  After going through the entire spreadsheet and making changes, the code saves the Workbook object to updatedProduceSales.xlsx ➍. It doesn’t overwrite the old spreadsheet just in case there’s a bug in your program and the updated spreadsheet is wrong. After checking that the updated spreadsheet looks right, you can delete the old spreadsheet.

  You can download the complete source code for this program from http://nostarch.com/automatestuff/.

  Ideas for Similar Programs

  Since many office workers use Excel spreadsheets all the time, a program that can automatically edit and write Excel files could be really useful. Such a program could do the following:

  Read data from one spreadsheet and write it to parts of other spreadsheets.

  Read data from websites, text files, or the clipboard and write it to a spreadsheet.

  Automatically “clean up” data in spreadsheets. For example, it could use regular expressions to read multiple formats of phone numbers and edit them to a single, standard format.

  Setting the Font Style of Cells

  Styling certain cells, rows, or columns can help you emphasize important areas in your spreadsheet. In the produce spreadsheet, for example, your program could apply bold text to the potato, garlic, and parsnip rows. Or perhaps you want to italicize every row with a cost per pound greater than $5. Styling parts of a large spreadsheet by hand would be tedious, but your programs can do it instantly.

  To customize font styles in cells, important, import the Font() and Style() functions from the openpyxl.styles module.

  from openpyxl.styles import Font, Style

  This allows you to type Font() instead of openpyxl.styles.Font(). (See Importing Modules to review this style of import statement.)

  Here’s an example that creates a new workbook and sets cell A1 to have a 24-point, italicized font. Enter the following into the interactive shell:

  >>> import openpyxl >>> from openpyxl.styles import Font, Style >>> wb = openpyxl.Workbook() >>> sheet = wb.get_sheet_by_name('Sheet') ➊ >>> italic24Font = Font(size=24, italic=True) ➋ >>> styleObj = Style(font=italic24Font) ➌ >>> sheet['A1'].style = styleObj >>> sheet['A1'] = 'Hello world!' >>> wb.save('styled.xlsx')

  OpenPyXL represents the collection of style settings for a cell with a Style object, which is stored in the Cell object’s style attribute. A cell’s style can be set by assigning the Style object to the style attribute.

  In this example, Font(size=24, italic=True) returns a Font object, which is stored in italic24Font ➊. The keyword arguments to Font(), size and italic, configure the Font object’s style attributes. This Font object is then passed into the Style(font=italic24Font) call, which returns the value you stored in styleObj ➋. And when styleObj is assigned to the cell’s style attribute ➌, all that font styling information gets applied to cell A1.

  Font Objects

  The style attributes in Font objects affect how the text in cells is displayed. To set font style attributes, you pass keyword arguments to Font(). Table 12-2 shows the possible keyword arguments for the Font() function.

  Table 12-2. Keyword Arguments for Font style Attributes

  Keyword argument

  Data type

  Description

  name

  String

  The font name, such as 'Calibri' or 'Times New Roman'

  size

  Integer

  The point size

  bold

  Boolean

  True, for bold font

  italic

  Boolean

  True, for italic font

  You can call Font() to create a Font object and store that Font object in a variable. You then pass that to Style(), store the resulting Style object in a variable, and assign that variable to a Cell object’s style attribute. For example, this code creates various font styles:

  >>> import openpyxl >>> from openpyxl.styles import Font, Style >>> wb = openpyxl.Workbook() >>> sheet = wb.get_sheet_by_name('Sheet') >>> fontObj1 = Font(name='Times New Roman', bold=True) >>> styleObj1 = Style(font=fontObj1) >>> sheet['A1'].style/styleObj >>> sheet['A1'] = 'Bold Times New Roman' >>> fontObj2 = Font(size=24, italic=True) >>> styleObj2 = Style(font=fontObj2) >>> sheet['B3'].style/styleObj >>> sheet['B3'] = '24 pt Italic' >>> wb.save('styles.xlsx')

  Here, we store a Font object in fontObj1 and use it to create a Style object, which we store in styleObj1, and then set the A1 Cell object’s style attribute to styleObj. We repeat the process with another Font object and Style object to set the style of a second cell. After you run this code, the styles of the A1 and B3 cells in the spreadsheet will be set to custom font styles, as shown in Figure 12-4.

  Figure 12-4. A spreadsheet with custom font styles

  For cell A1, we set the font name to 'Times New Roman' and set bold to true, so our text appears in bold Times New Roman. We didn’t specify a size, so the openpyxl default, 11, is used. In cell B3, our text is italic, with a size of 24; we didn’t specify a font name, so the openpyxl default, Calibri, is used.

  Formulas

  Formulas, which begin with an equal sign, can configure cells to contain values calculated from other cells. In this section, you’ll use the openpyxl module to programmatically add formulas to cells, just like any normal value. For example:

  >>> sheet['B9'] = '=SUM(B1:B8)'

  This will store =SUM(B1:B8) as the value in cell B9. This sets the B9 cell to a formula that calculates the sum of values in cells B1 to B8. You can see this in action in Figure 12-5.

  Figure 12-5. Cell B9 contains the formula =SUM(B1:B8), which adds the cells B1 to B8.

  A formula is set just like any other text value in a cell. Enter the following into the interactive shell:

  >>> import openpyxl >>> wb = openpyxl.Workbook() >>> sheet = wb.get_active_sheet() >>> sheet['A1'] = 200 >>> sh
eet['A2'] = 300 >>> sheet['A3'] = '=SUM(A1:A2)' >>> wb.save('writeFormula.xlsx')

  The cells in A1 and A2 are set to 200 and 300, respectively. The value in cell A3 is set to a formula that sums the values in A1 and A2. When the spreadsheet is opened in Excel, A3 will display its value as 500.

  You can also read the formula in a cell just as you would any value. However, if you want to see the result of the calculation for the formula instead of the literal formula, you must pass True for the data_only keyword argument to load_workbook(). This means a Workbook object can show either the formulas or the result of the formulas but not both. (But you can have multiple Workbook objects loaded for the same spreadsheet file.) Enter the following into the interactive shell to see the difference between loading a workbook with and without the data_only keyword argument: