- Prerequisites for XLSX Spreadsheets
- Writing XLSX Spreadsheets
- Reading XLSX Spreadsheets
- Using a Formula in XLSX Cell
- Inserting an Image to XLSX Cell
- Resizing XLSX Cells
- Setting Cell Borders
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 imagesWriting 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 diskReading 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'