Python Excel

Python Excel Writer

openpyxl can create a workbook, write text, numbers, and formulas, then save it. The same workbook object can add or remove sheets, set fonts, change row height and column width, and merge cells. You do not need Excel installed to produce the file.

Create a workbook and save it

import openpyxl

wb = openpyxl.Workbook()
print(wb.sheetnames)       # ['Sheet']
sheet = wb.active
print(sheet.title)         # Sheet
sheet.title = "MyNewTitle"
wb.save("NewExcelFile.xlsx")

A new workbook starts with one sheet named Sheet. After save, NewExcelFile.xlsx is in the current working directory and its only tab is MyNewTitle.

Open an existing file and save a copy

Any change is discarded unless you call save. Saving under a new name leaves the original file alone, which is the safer habit while a script is still being tested.

import openpyxl

wb = openpyxl.load_workbook("filetest.xlsx")
sheet = wb.active
sheet.title = "Working on Save as"
wb.save("example_filetest.xlsx")

Add and remove sheets

import openpyxl

wb = openpyxl.Workbook()
wb.create_sheet()                          # Sheet1 at the end
wb.create_sheet(index=0, title="1st Sheet")
wb.create_sheet(index=2, title="2nd Sheet")
print(wb.sheetnames)

del wb["1st Sheet"]
del wb["Sheet1"]
print(wb.sheetnames)
wb.save("sheets.xlsx")

create_sheet() with no arguments appends a sheet. index chooses the position. The first sheet is index 0. title sets the tab name. Removing a sheet is covered step by step in Delete a sheet.

Write a value into a cell

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = "Writing new Value!"
print(sheet["F6"].value)
wb.save("cells.xlsx")

More ways to address a cell, including a loop, are on the write to a cell page.

Fonts

In current openpyxl you assign a Font directly to the cell. The older Style class has been removed.

import openpyxl
from openpyxl.styles import Font

wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = "Bold Arial"
sheet["F6"].font = Font(name="Arial", bold=True)

sheet["D7"] = "32 pt Italic"
sheet["D7"].font = Font(size=32, italic=True)
wb.save("Appliedstyle.xlsx")

Font accepts name, size, bold, italic, and color. Color is a hex string such as "FF0000" for red.

Formulas

Write the formula as text, starting with =.

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = 500
sheet["F7"] = 800
sheet["D3"] = "=SUM(F6:F7)"
wb.save("Applyingformula.xlsx")

Excel shows 1300 in D3 when the file is opened. openpyxl stores the formula. It does not calculate it.

Row height and column width

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet["F6"] = "Tall row"
sheet["D7"] = "Wide column"
sheet.row_dimensions[6].height = 65
sheet.column_dimensions["D"].width = 25
wb.save("Heightandwidth.xlsx")

The default row height is 15 points in current Excel (older files often used 12.75). One point is 1/72 inch. Row height can run from 0 to 409. Column width can run from 0 to 255. A height or width of 0 hides that row or column.

Merge and unmerge cells

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active
sheet.merge_cells("B2:D3")
sheet["B2"] = "Cells merged together."
sheet.merge_cells("F6:F7")
wb.save("Mergingcells.xlsx")

The argument is the rectangle to merge: the first address is the top-left cell, the second is the bottom-right. Put the visible value in the top-left cell of that rectangle. The other cells in the merge should stay empty.

wb = openpyxl.load_workbook("Mergingcells.xlsx")
sheet = wb.active
sheet.unmerge_cells("B2:D3")
sheet.unmerge_cells("F6:F7")
wb.save("unmerged.xlsx")