Working with Spreadsheets in Python

Overview

The two most common types of spreadsheets are CSV and XLSX files. CSV files are good for simple data and good for text utilities like grep. Microsoft Excel format (XLSX) files are more flexible and allow formatting, images, charts, and formulas. You can use Libre Office and other alternatives if you don't have Microsoft Excel. You can also use Python! These examples will show you how to do some basic operations with both types of spreadsheets.

Writing a CSV Spreadsheet

CSV files are very simple. There is only one "sheet" and data is separated by a delimiter (typically a comma) with newlines separating rows.

import csv
spreadsheet = csv.writer(open('users.csv', 'wb'), delimiter=',')
spreadsheet.writerow(["id", "username", "password"])
spreadsheet.writerow(["1", "admin", "admin"])
spreadsheet.writerow(["2", "ceo", "password1"])

Reading a CSV Spreadsheet

import csv
filename = 'data.csv'
spreadsheet = csv.reader(open(filename, 'rb'), delimiter=',')
for row in spreadsheet:
    print(row)  # List of columns
    # Access individual columns with index like row[0]

Prerequisites for XLSX Spreadsheets

While CSV support is part of the Python standard library, Excel format requires a third-party package. I found openpyxl to be the easiest to use.

pip install openpyxl
pip install pillow  # If you want to use images

Writing XLSX Spreadsheets

Excel files are more complicated than CSV files because they can have images, charts, store formulas that perform calculations, and have multiple sheets. The openpyxl package has support for a number of different graph and chart types.

from openpyxl import Workbook
wb = Workbook()

ws = wb.active  # Use default/active sheet
# Or create a new named sheet and set it to active using index
# ws = wb.create_sheet(title="User Information")
# wb.active = 1  # Default sheet was 0, this new sheet is 1

# Direct cell modification
ws['A1'] = "id"
ws['B1'] = "username"
ws['C1'] = "password"

# Add new row at bottom
ws.append(["1337", "NanoDano", "password1"])

# Can use Python datetime objects
import datetime
ws['D1'] = datetime.datetime.now()

# Change sheet tab color
ws.sheet_properties.tabColor = "660000"

wb.save("users.xlsx")  # Write to disk

Reading XLSX Spreadsheets

from openpyxl import load_workbook

wb = load_workbook(filename='users.xlsx')

# List sheets available
sheets = wb.get_sheet_names()
print(sheets)

# Load active sheet or named sheet
sheet = wb.active
# sheet = wb['User Information']

# Read a specific cell
print(sheet['A1'].value)

Using a Formula in XLSX Cell

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

# Add some numbers
ws.append([27])
ws.append([13])
ws.append([35])

# Sum A1 to A3 and put it in B1
ws['B1'] = "=SUM(A1:A3)"

wb.save("formula.xlsx")

Inserting an Image to XLSX Cell

from openpyxl import Workbook
from openpyxl.drawing.image import Image
# pip install pillow

wb = Workbook()
ws = wb.active

img = Image('image.png')
ws.add_image(img, 'A1')

wb.save('image_example.xlsx')

Resizing XLSX Cells

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws['A1'] = "255.255.255.255"
ws.column_dimensions['A'].width = 15  # In characters, not pixels
ws.row_dimensions[1].height = 400  # In pixels

wb.save('cell_resize_example.xlsx')

Setting Cell Borders

from openpyxl import Workbook
from openpyxl.styles import Border, Side

wb = Workbook()
ws = wb.active

ws['A1'] = "255.255.255.255"

thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))
ws['A1'].border = thin_border

wb.save('cell_border_example.xlsx')
# Available border variables
# Make sure to import what you need if you use the constants
from openpyxl.styles.borders import BORDER_NONE, *
BORDER_NONE = None
BORDER_DASHDOT = 'dashDot'
BORDER_DASHDOTDOT = 'dashDotDot'
BORDER_DASHED = 'dashed'
BORDER_DOTTED = 'dotted'
BORDER_DOUBLE = 'double'
BORDER_HAIR = 'hair'
BORDER_MEDIUM = 'medium'
BORDER_MEDIUMDASHDOT = 'mediumDashDot'
BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot'
BORDER_MEDIUMDASHED = 'mediumDashed'
BORDER_SLANTDASHDOT = 'slantDashDot'
BORDER_THICK = 'thick'
BORDER_THIN = 'thin'

References