Using openpyxl to Manipulate Excel Files in Python

The article introduces using the openpyxl library in Python for various Excel operations.

Opening and creating workbooks: Shows how to import Workbook, create a new workbook, and load an existing workbook, including read‑only and write‑only modes for large files.

from openpyxl import Workbook
wb = Workbook()
from openpyxl import load_workbook
wb2 = load_workbook('test.xlsx')
wb = load_workbook(filename='large_file.xlsx', read_only=True)
wb = Workbook(write_only=True)

Worksheets: Demonstrates obtaining the active sheet, creating new sheets with different insertion positions, and accessing sheets by name.

ws = wb.active
ws1 = wb.create_sheet("Mysheet")
ws2 = wb.create_sheet("Mysheet", 0)
ws3 = wb.create_sheet("Mysheet", -1)
ws3 = wb["New Title"]
print(wb.sheetnames)

Saving workbooks: Shows saving to a temporary file stream, to a regular file, and saving as a template.

from tempfile import NamedTemporaryFile
wb = Workbook()
with NamedTemporaryFile() as tmp:
    wb.save(tmp.name)
    tmp.seek(0)
    stream = tmp.read()
wb.save('balances.xlsx')
wb = load_workbook('document.xlsx')
wb.template = True
wb.save('document_template.xltx')

Cell operations: Covers reading and writing cell values, using slices for ranges, applying number formats, formulas, merging/unmerging cells, and accessing rows and columns.

c = ws['A4']
ws['A4'] = 4
c.value = 'hello, world'
cell_range = ws['A1':'C2']
ws['A1'] = datetime.datetime(2010,7,21)
ws['A1'].number_format = 'yyyy-mm-dd h:mm:ss'
ws["A1"] = "=SUM(1, 1)"
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)

Row and column manipulation: Explains inserting and deleting rows/columns, appending data, and iterating over values only.

ws.insert_rows(7)
ws.insert_cols(5)
ws.delete_rows(10, 2)
ws.delete_cols(3, 1)
ws.append(range(600))
for row in ws.values:
    for value in row:
        print(value)