Python: OpenXML xlsx files

Python can be used to both create and read xlsx files in the OpenXML data format. The Python openpyxl package will be used.

One way to install the openpyxl package is as follows.

D:\Python38\Scripts\pip3.exe install openpyxl

Use the path to your installed version of Python. See below to install a specific version.

In the case of openpyxl, as of 2020-04-24, there is an issue in saving xlsx files using version 3.0.2. The solution, until it is fixed (and this works for other packages with issues) is to downgrade the installation. Here is one (safe) way.

D:\Python38\Scripts\pip3.exe uninstall openpyxl D:\Python38\Scripts\pip3.exe install openpyxl==3.0.1

Another way is as follows. An upgrade or downgrade uses the same form of the command.

D:\Python38\Scripts\pip3.exe install --upgrade openpyxl==3.0.1

In many cases, one does not need to read nor create charts in Excel (for data science acquisition or data distribution). If needed, one can check the documentation for the large number of ways in which Excel charts can be created. In most cases, one will need to extract data from existing workbooks or generate workbooks.

I have found that generating workbook sheets in many different forms is useful for whoever it is that is getting the data. They can then decide which best suites them.

In the example below, the same data is provided in table order and in transposed table order.

Besides this page, more information on getting started is at the openpyxl web site at https://openpyxl.readthedocs.io/en/stable/.

Excel is based on slides of a workbook containing a collection of sheets, charts, etc.

It is often best to start with a blank sheet and add cells.

Each cell has a content, a type, a format, etc.

For historical reasons, Excel has many specific ways to do things that may not be obvious.

There are some things that are difficult or impossible to do with Excel. There are also certain specific things that cannot be done using openpyxl

For such things I use VBA (Visual Basic for Applications) code in Excel to do actions, fix-ups, etc.

Here is the table used for example purposes.

tableList1 = [
	["r0c1", "r0c2", "r0c3"],
	["r1c1", "r1c2", "r1c3"],
	["r2c1", "r2c2", "r2c3"],
	["r3c1", "r3c2", "r3c3"],
	]

An example xlsx file will be created, read, and accessed.

excelFs1 = "./data1.xlsx"

As with many packages, it is often useful to create a custom class with which to access that package.

import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font, Color, Alignment
from openpyxl.styles.borders import Border, Side

The initialization can be done as follows and which creates a new in-memory workbook.

def __init__(self):
	excel1 = Workbook()
	# may need to delete any extra sheets
	sheetLen1 = 0

Sheets are numbered starting at 1.

The make method makes a new sheet in the current workbook, adding it to the end of the existing sheets.

def make(self, sheetTitle1):
	sheetTitle1 = sheetTitle1
	sheetLen1 += 1
	print("Sheet {0:d}: \"{1:s}\" (make)".format(sheetLen1,sheetTitle1))
	if sheetLen1 != 1:
		excel1.create_sheet(sheetTitle1,sheetLen1)
	sheet1 = excel1.worksheets[sheetLen1-1]
	sheet1.title = sheetTitle1

A workbook must have at least one sheet so a new sheet is not created if "making" the first sheet, just the title changed.

The load method loads a new in-memory workbook.

def load(self, excelFs1):
	excelFs1 = excelFs1
	print("Load \"{0:s}\"".format(excelFs1))
	excel1 = load_workbook(excelFs1)

The instance variable excel1 is the workbook.

The save method saves the in-memory workbook.

def save(self, excelFs1):
	excelFs1 = excelFs1
	print("Save \"{0:s}\"".format(excelFs1))
	excel1.save(filename=excelFs1)

The add method adds a cell at the specified row and column and with the specified value.

def add(self, rpos1, cpos1, value1, bg1="FFFFFF", fg1="000000", halign1="general", valign1="bottom"):
	cell1 = sheet1.cell(row=rpos1,column=cpos1)
	cell1.value = value1
	cell1.fill = openpyxl.styles.PatternFill(fill_type="solid",start_color=bg1,end_color=bg1)
	border1 = Border(
		left=Side(style="thin",color=fg1),
		right=Side(style="thin",color=fg1),
		top=Side(style="thin",color=fg1),
		bottom=Side(style="thin",color=fg1)
		)
	cell1.border = border1
	cell1.font = Font(name="Helvetica",size=16,color=fg1,bold=True)
	cell1.alignment = Alignment(horizontal=halign1,vertical=valign1)

Cell rows start at 1. Cell columns start at A. Row and column positions start at 1.

The ref method returns the user-friendly cell reference for the provided row and column.

def ref(self, rpos1, cpos1):
	ref1 = get_column_letter(cpos1+1) + str(rpos1+1)
	return ref1

Here is a code module in Lua [module rsExcel in rsExcel.py].

# module rsExcel.py
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font, Color, Alignment
from openpyxl.styles.borders import Border, Side
class excel():
	def __init__(self):
		excel1 = Workbook()
		# may need to delete any extra sheets
		sheetLen1 = 0
	def make(self, sheetTitle1):
		sheetTitle1 = sheetTitle1
		sheetLen1 += 1
		print("Sheet {0:d}: \"{1:s}\" (make)".format(sheetLen1,sheetTitle1))
		if sheetLen1 != 1:
			excel1.create_sheet(sheetTitle1,sheetLen1)
		sheet1 = excel1.worksheets[sheetLen1-1]
		sheet1.title = sheetTitle1
	def load(self, excelFs1):
		excelFs1 = excelFs1
		print("Load \"{0:s}\"".format(excelFs1))
		excel1 = load_workbook(excelFs1)
	def save(self, excelFs1):
		excelFs1 = excelFs1
		print("Save \"{0:s}\"".format(excelFs1))
		excel1.save(filename=excelFs1)
	def add(self, rpos1, cpos1, value1, bg1="FFFFFF", fg1="000000", halign1="general", valign1="bottom"):
		cell1 = sheet1.cell(row=rpos1,column=cpos1)
		cell1.value = value1
		cell1.fill = openpyxl.styles.PatternFill(fill_type="solid",start_color=bg1,end_color=bg1)
		border1 = Border(
			left=Side(style="thin",color=fg1),
			right=Side(style="thin",color=fg1),
			top=Side(style="thin",color=fg1),
			bottom=Side(style="thin",color=fg1)
			)
		cell1.border = border1
		cell1.font = Font(name="Helvetica",size=16,color=fg1,bold=True)
		cell1.alignment = Alignment(horizontal=halign1,vertical=valign1)
	def ref(self, rpos1, cpos1):
		ref1 = get_column_letter(cpos1+1) + str(rpos1+1)
		return ref1
print("openpyxl version: {0:s}".format(openpyxl.__version__))
print("(module rsExcel imported)")

The complete program that uses the rsExcel module and excel class is now covered.

The program creates an xlsx file with two sheets using the data table and the data table transposed.

The program then reads in the created xlsx file and goes through and outputs the (used) cells in each sheet. Note that Excel keeps track of which cells and ranges are used, so this is built into openpyxl.

The sheets can be created as follows.

print("Create \"{0:s}\"".format(excelFs1))
excel1 = rsExcel.excel()
sheetAdd1("Table",tableList1,0)
sheetAdd1("TableT",tableList1,1)
excel1.save(excelFs1)

Since the creation of the sheets are similar, the procedure sheetAdd1 is used to create the sheet using the global variable excel1. Note that a global variable can be created at any place in the program and does not need a static scope instance (i.e., somewhere above the code using excel1).

To show cells shaded by colors, the following color lists are defined.

colorList1 = [
	["FFCCCC", "660000"],
	["CCFFCC", "006600"],
	["CCCCFF", "000066"],
	["CCFFFF", "006666"],
	["FFCCFF", "660066"],
	["FFFFCC", "666600"],
	]

This list has a light background and a dark foreground/text color. The foreground/text color is not used here. Note: Excel, like Word and PowerPoint, and for historical reasons, sometimes support only limited colors or have "tricks" that are needed to get a desired color.

The above "trick" is that a gradient fill is needed to get the desired color as the original Excel (like Word) only permitted a small number of indexed colors.

def sheetAdd1(name1, table1, transpose1=0):
	global excel1
	excel1.make(name1)
	colorLen1 = len(colorList1)
	clen1 = len(tableList1[0])
	for rpos1,rowList1 in enumerate(tableList1):
		for cpos1,value1 in enumerate(rowList1):
			if transpose1 == 1:
				rpos2 = cpos1
				cpos2 = rpos1
			else:
				rpos2 = rpos1
				cpos2 = cpos1
			colorPos1 = ((rpos1*clen1)+cpos1) % colorLen1
			bg1,fg1 = colorList1[colorPos1]
			ref2 = excel1.ref(cpos2,rpos2)
			print("\t{0:s} ({1:d},{2:d}) is \"{3:s}\"".format(ref2,rpos2,cpos2,value1))
			excel1.add(rpos2+1,cpos2+1,value1,bg1,fg1)

excel1.load(excelFs1)
for sheetPos1,sheet1 in enumerate(excel1.excel1.worksheets):
	type1 = type(sheet1)
	print("{0:s}".format(str(type1)))
	print("Sheet {0:d}: \"{1:s}\"".format(sheetPos1+1,sheet1.title))
	for rpos1,rowList1 in enumerate(sheet1.iter_rows()):
		for cpos1,cell1 in enumerate(rowList1):
			ref1 = excel1.ref(cpos1,rpos1)
			value1 = cell1.value
			print("\t{0:s} ({1:d},{2:d}) is \"{3:s}\"".format(ref1,rpos1,cpos1,value1))

Here is the Python code [#15]

import rsExcel
tableList1 = [
	["r0c1", "r0c2", "r0c3"],
	["r1c1", "r1c2", "r1c3"],
	["r2c1", "r2c2", "r2c3"],
	["r3c1", "r3c2", "r3c3"],
	]
colorList1 = [
	["FFCCCC", "660000"],
	["CCFFCC", "006600"],
	["CCCCFF", "000066"],
	["CCFFFF", "006666"],
	["FFCCFF", "660066"],
	["FFFFCC", "666600"],
	]
def sheetAdd1(name1, table1, transpose1=0):
	global excel1
	excel1.make(name1)
	colorLen1 = len(colorList1)
	clen1 = len(tableList1[0])
	for rpos1,rowList1 in enumerate(tableList1):
		for cpos1,value1 in enumerate(rowList1):
			if transpose1 == 1:
				rpos2 = cpos1
				cpos2 = rpos1
			else:
				rpos2 = rpos1
				cpos2 = cpos1
			colorPos1 = ((rpos1*clen1)+cpos1) % colorLen1
			bg1,fg1 = colorList1[colorPos1]
			ref2 = excel1.ref(cpos2,rpos2)
			print("\t{0:s} ({1:d},{2:d}) is \"{3:s}\"".format(ref2,rpos2,cpos2,value1))
			excel1.add(rpos2+1,cpos2+1,value1,bg1,fg1)
excelFs1 = "./data1.xlsx"
print("Create \"{0:s}\"".format(excelFs1))
excel1 = rsExcel.excel()
sheetAdd1("Table",tableList1,0)
sheetAdd1("TableT",tableList1,1)
excel1.save(excelFs1)
print("Read \"{0:s}\"".format(excelFs1))
excel1.load(excelFs1)
for sheetPos1,sheet1 in enumerate(excel1.excel1.worksheets):
	type1 = type(sheet1)
	print("{0:s}".format(str(type1)))
	print("Sheet {0:d}: \"{1:s}\"".format(sheetPos1+1,sheet1.title))
	for rpos1,rowList1 in enumerate(sheet1.iter_rows()):
		for cpos1,cell1 in enumerate(rowList1):
			ref1 = excel1.ref(cpos1,rpos1)
			value1 = cell1.value
			print("\t{0:s} ({1:d},{2:d}) is \"{3:s}\"".format(ref1,rpos1,cpos1,value1))

Here is the output of the Python code.

openpyxl version: 3.0.1
(module rsExcel imported)
Create "./data1.xlsx"
Sheet 1: "Table" (make)
	A1 (0,0) is "r0c1"
	A2 (0,1) is "r0c2"
	A3 (0,2) is "r0c3"
	B1 (1,0) is "r1c1"
	B2 (1,1) is "r1c2"
	B3 (1,2) is "r1c3"
	C1 (2,0) is "r2c1"
	C2 (2,1) is "r2c2"
	C3 (2,2) is "r2c3"
	D1 (3,0) is "r3c1"
	D2 (3,1) is "r3c2"
	D3 (3,2) is "r3c3"
Sheet 2: "TableT" (make)
	A1 (0,0) is "r0c1"
	B1 (1,0) is "r0c2"
	C1 (2,0) is "r0c3"
	A2 (0,1) is "r1c1"
	B2 (1,1) is "r1c2"
	C2 (2,1) is "r1c3"
	A3 (0,2) is "r2c1"
	B3 (1,2) is "r2c2"
	C3 (2,2) is "r2c3"
	A4 (0,3) is "r3c1"
	B4 (1,3) is "r3c2"
	C4 (2,3) is "r3c3"
Save "./data1.xlsx"
Read "./data1.xlsx"
Load "./data1.xlsx"
<class 'openpyxl.worksheet.worksheet.Worksheet'>
Sheet 1: "Table"
	A1 (0,0) is "r0c1"
	A2 (0,1) is "r0c2"
	A3 (0,2) is "r0c3"
	B1 (1,0) is "r1c1"
	B2 (1,1) is "r1c2"
	B3 (1,2) is "r1c3"
	C1 (2,0) is "r2c1"
	C2 (2,1) is "r2c2"
	C3 (2,2) is "r2c3"
	D1 (3,0) is "r3c1"
	D2 (3,1) is "r3c2"
	D3 (3,2) is "r3c3"
<class 'openpyxl.worksheet.worksheet.Worksheet'>
Sheet 2: "TableT"
	A1 (0,0) is "r0c1"
	A2 (0,1) is "r1c1"
	A3 (0,2) is "r2c1"
	A4 (0,3) is "r3c1"
	B1 (1,0) is "r0c2"
	B2 (1,1) is "r1c2"
	B3 (1,2) is "r2c2"
	B4 (1,3) is "r3c2"
	C1 (2,0) is "r0c3"
	C2 (2,1) is "r1c3"
	C3 (2,2) is "r2c3"
	C4 (2,3) is "r3c3"

Here is one sheet called "Table". Generated table

Here is the other sheet called "TableT" (for transposed). Generated table transposed

The above assumed all sheets were worksheets. The type of a sheet can be used to determine the type of each sheet in a workbook.

type(sheet1)

For cell text alignment, the following can be useful. Alignments can be one of "left", "center", "right".

from openpyxl.styles import Alignment # ... cell1.alignment = Alignment(horizontal="center",vertical="center")

Sometimes one wants to create a worksheet that can be easily changed by the intended user.

In such cases, formulas can be assigned to cells such that Excel calculates the values of those cells from other cells.

Note that Excel (and other programs) will lock the file currently loaded into the application.

If you try to generate a xlsx file while Excel has it locked, a run-time error results.

Note that csv files are stored and read as text. When it is important that certain data be numeric fields, explicit conversion is necessary.

Whenever converting csv to xlsx, or when creating and/or working with xlsx files, it may be important to do some explicit conversions of cells rather than using the default "General" format.