Intro

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