Online openpyxl Compiler

openpyxl reads and writes Excel .xlsx files from Python. Because it can open an existing workbook, change it and save it again, it is the usual choice for filling in a template, cleaning up an export or pulling numbers out of a spreadsheet someone sent you. If you only need to create new files, XlsxWriter is an alternative. This page is an online openpyxl compiler: the code runs in your browser, so you can try it without installing anything. Run the example first, then paste any snippet below into a new cell to try it.

What the example does

Workbook() creates a workbook with one sheet, and wb.active is that sheet. ws["A1"] = 42 sets a single cell. ws.append([1, 2, 3]) adds a row below the last row in use, which here is row 2. Assigning a datetime to A2 overwrites the 1 that append() put there and stores it as an Excel date. wb.save("sample.xlsx") writes the file. The last lines open the file again with load_workbook() and print each row, so you can see exactly what was saved.

Read a workbook

iter_rows() walks the sheet row by row. min_row=2 skips a header, and values_only=True gives plain values instead of cell objects:

from openpyxl import load_workbook

ws = load_workbook("sample.xlsx").active
for a, b, c in ws.iter_rows(min_row=2, values_only=True):
    print(a, b, c)

To work on your own spreadsheet, upload it with Add files in the sidebar's Data tab and open it by name. wb.sheetnames lists its sheets and wb["Sheet name"] picks one.

Formulas

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["Quarter", "Sales"])
for row in [("Q1", 120), ("Q2", 135), ("Q3", 150)]:
    ws.append(row)
ws["B5"] = "=SUM(B2:B4)"
wb.save("totals.xlsx")

openpyxl stores the formula but never calculates it. Reading B5 back returns the text =SUM(B2:B4). load_workbook(path, data_only=True) returns the result instead, but only for a file that Excel or another spreadsheet app has saved, since they store the calculated value. For a file openpyxl wrote, it returns None.

Format cells

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

wb = Workbook()
ws = wb.active
ws.append(["Item", "Price"])
ws.append(["Coffee", 3.5])
for cell in ws[1]:
    cell.font = Font(bold=True)
    cell.fill = PatternFill("solid", fgColor="DDEBF7")
ws["B2"].number_format = "$#,##0.00"
ws.column_dimensions["A"].width = 20
wb.save("styled.xlsx")

Add a chart

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
for row in [("Quarter", "Sales"), ("Q1", 120), ("Q2", 135), ("Q3", 150)]:
    ws.append(row)

chart = BarChart()
chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=4), titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=4))
ws.add_chart(chart, "D2")
wb.save("chart.xlsx")

Getting the file

Files your code saves, like sample.xlsx, appear in the sidebar's Data tab. Running code and inspecting the result is free. Downloading the files requires a Premium plan.

Good to know

openpyxl does not read every part of a workbook. Shapes and text boxes in a file you open are dropped when you save it, so save edits under a new name until you have checked the result.
It reads .xlsx and .xlsm, not the older .xls format.
pandas uses openpyxl to read .xlsx files, so pd.read_excel("sample.xlsx") works here too.

Online openpyxl Compiler