openpyxl: Otomatiskan Tugas Excel dengan Python

openpyxl adalah pustaka Python yang efisien yang memungkinkan Anda membaca dan menulis file Excel dalam format berbasis XML modern (.xlsx, .xlsm) yang diperkenalkan di Excel 2007, dengan dukungan penuh untuk fitur lanjutan mulai dari Excel 2010. Pustaka ini awalnya dikembangkan sebagai port Python dari PHPExcel dan terus dipelihara oleh komunitas pengembang yang aktif. Pustaka ini unggul dalam otomasi data, pelaporan, dan alur kerja pemformatan, sehingga sangat berguna bagi pengguna yang perlu bekerja dengan file Excel tetapi tidak menginstal Excel di sistem mereka.

Banyak organisasi menggunakan openpyxl untuk mengotomatiskan laporan keuangan, membuat bagan dari ekspor basis data, serta menerapkan rumus dan pemformatan pada laporan pemasaran. Pustaka ini menawarkan kontrol terperinci atas file Excel sepenuhnya melalui kode Python.

Apa itu openpyxl?

openpyxl dirancang khusus untuk menyediakan dukungan native bagi format Excel 2010+. Pustaka ini bekerja dengan beberapa format file:

•  .xlsx - Buku kerja Excel

•  .xlsm - Buku kerja Excel dengan makro

•  .xltx - Templat Excel

•  .xltm - Templat Excel dengan makro

Salah satu keunggulan utama openpyxl adalah tidak memerlukan instalasi Excel di komputer Anda. Ini menjadikannya ideal untuk lingkungan server, sistem pelaporan terotomatisasi, dan pipeline pemrosesan data. Beberapa pustaka Python lainnya, termasuk pandas, menggunakan openpyxl sebagai antarmuka Excel mereka, yang menunjukkan keandalan dan fungsionalitasnya.

Menginstal dan Mengimpor openpyxl

Menginstal openpyxl sangat mudah menggunakan pip:

pip install openpyxl

Untuk keamanan yang lebih baik saat bekerja dengan file dari sumber yang tidak tepercaya, Anda juga dapat menginstal paket opsional defusedxml:

pip install defusedxml

Untuk menggunakan openpyxl dalam skrip Python Anda, impor kelas-kelas yang diperlukan:

from openpyxl import Workbook, load_workbook

Konsep dan Terminologi Utama

Untuk menggunakan openpyxl secara efektif, Anda perlu memahami struktur dasar Excel:

• Workbook: File Excel itu sendiri, berisi satu atau lebih worksheet
• Worksheet: Tab/lembar individual di dalam sebuah workbook
• Cell: Titik data individual di sebuah worksheet, diidentifikasi oleh huruf kolom dan nomor baris (mis., "A1")
• Row: Deret sel horizontal, diidentifikasi dengan angka (1, 2, 3...)
• Column: Deret sel vertikal, diidentifikasi dengan huruf (A, B, C...)

Di openpyxl, Anda dapat mereferensikan sel menggunakan:

• Referensi gaya Excel: sheet[“A1”]

• Pengindeksan baris-kolom: sheet.cell(row=1, column=1) (Catatan: openpyxl menggunakan pengindeksan berbasis 1, bukan berbasis 0)

Membaca File Excel dengan openpyxl

Berikut contoh praktis yang menunjukkan cara membaca data dari file Excel:

Memuat dan menjelajah workbook

from openpyxl import load_workbook

# Load the workbook - use read_only=True for large files
wb = load_workbook('sample.xlsx', read_only=False, data_only=False)
# data_only=True reads values instead of formulas

Parameter opsional mengontrol bagaimana workbook dimuat:

• read_only=True meningkatkan performa untuk file besar namun membatasi kemampuan pengeditan

• data_only=True mengembalikan nilai hasil perhitungan alih-alih rumus

Iterasi data

Untuk memproses banyak sel secara efisien, gunakan metode iterasi:

# Iterate through rows
for row in sheet.iter_rows(min_row=1, max_row=5, values_only=True):
    print(row)  # Returns a tuple of values

# Iterate through columns
for column in sheet.iter_cols(min_col=1, max_col=3, values_only=True):
    print(column)  # Returns a tuple of values

# Access a range of cells
cell_range = sheet["A1:C5"]
for row in cell_range:
    for cell in row:
        print(cell.value)

Konversi ke struktur data Python

Anda dapat dengan mudah mengubah data Excel menjadi struktur data Python:

# Convert worksheet data to a list of dictionaries
def sheet_to_dict(sheet):
    data = []
    headers = [cell.value for cell in sheet[1]]  # First row as headers
    
    for row in sheet.iter_rows(min_row=2, values_only=True):
        row_data = {}
        for key, value in zip(headers, row):
            row_data[key] = value
        data.append(row_data)
    
    return data

# Example usage
data_dict = sheet_to_dict(sheet)
print(data_dict)

Menulis File Excel dengan openpyxl

Membuat dan memodifikasi file Excel sama mudahnya dengan openpyxl.

Buat workbook dan tulis data dasar

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()
sheet = wb.active

# Rename the sheet
sheet.title = "Data"

# Write values to cells
sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["C1"] = "City"

# Add data
data = [
    ["Alice", 25, "New York"],
    ["Bob", 30, "San Francisco"],
    ["Charlie", 35, "Chicago"]
]

for row_idx, row_data in enumerate(data, start=2):
    for col_idx, cell_value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=cell_value)

# Save the workbook
wb.save("new_workbook.xlsx")

Memodifikasi file Excel yang ada

from openpyxl import load_workbook

# Load an existing workbook
wb = load_workbook("existing_file.xlsx")
sheet = wb.active

# Modify cell values
sheet["D1"] = "Updated Data"
sheet.cell(row=5, column=2).value = 42

# Save to a new file (to preserve the original)
wb.save("modified_file.xlsx")

Menambahkan data secara dinamis

Metode .append() memudahkan penambahan baris baru:

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
sheet.append(["Date", "Product", "Quantity", "Price"])

# Add multiple rows of data
sales_data = [
    ["2025-04-01", "Laptop", 5, 1200],
    ["2025-04-01", "Mouse", 10, 25],
    ["2025-04-02", "Monitor", 3, 350]
]

for row in sales_data:
    sheet.append(row)

wb.save("sales_report.xlsx")

Pemformatan dan Styling di Excel

openpyxl menawarkan banyak opsi untuk pemformatan dan styling sel.

Menambahkan font, border, dan perataan

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle

wb = Workbook()
sheet = wb.active

# Create a header style
header_style = NamedStyle(name="header_style")
header_style.font = Font(bold=True, size=12, color="FFFFFF")
header_style.fill = PatternFill(fill_type="solid", start_color="366092")
header_style.alignment = Alignment(horizontal="center", vertical="center")
header_style.border = Border(
    bottom=Side(border_style="medium", color="000000"),
    left=Side(border_style="thin"),
    right=Side(border_style="thin"),
    top=Side(border_style="thin")
)

# Add the style to the workbook
wb.add_named_style(header_style)

# Apply the style to header row
headers = ["ID", "Product", "Category", "Price"]
for col_idx, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_idx, value=header)
    cell.style = "header_style"

# Basic styling without named styles
sheet["A2"].font = Font(bold=True, italic=True)
sheet["A3"].alignment = Alignment(horizontal="center")
sheet["A4"].border = Border(bottom=Side(border_style="thin"))

wb.save("styled_workbook.xlsx")

Pemformatan bersyarat

Pemformatan bersyarat memungkinkan Anda menerapkan gaya berdasarkan nilai sel:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import Rule, ColorScaleRule

wb = Workbook()
sheet = wb.active

# Add sample data
for row in range(1, 11):
    for col in range(1, 5):
        sheet.cell(row=row, column=col, value=(row-1)*4 + col)

# Add color scale (green to red gradient)
color_scale = ColorScaleRule(
    start_type="min", start_color="63BE7B",  # Green
    mid_type="percentile", mid_value=50, mid_color="FFEB84",  # Yellow
    end_type="max", end_color="F8696B"  # Red
)
sheet.conditional_formatting.add("A1:D10", color_scale)

# Add a rule for values less than 5
red_text = Font(color="FF0000")
red_fill = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
dxf = DifferentialStyle(font=red_text, fill=red_fill)
rule = Rule(type="cellIs", operator="lessThan", formula=["5"], dxf=dxf)
sheet.conditional_formatting.add("A1:D10", rule)

wb.save("conditional_format.xlsx")

Fitur Lanjutan

openpyxl mendukung banyak fitur lanjutan Excel yang membantu tugas spreadsheet yang kompleks.

Menambahkan rumus

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add data
for row in range(1, 6):
    sheet.cell(row=row, column=1, value=row)

# Add formulas
sheet["B1"] = "=A1*2"
sheet["B2"] = "=A2*2"
sheet["C1"] = "=SUM(A1:A5)"
sheet["C2"] = "=AVERAGE(A1:A5)"
sheet["C3"] = "=COUNTIF(A1:A5,\">2\")"

wb.save("formulas.xlsx")

Catatan: Saat menggunakan load_workbook(), rumus tidak dihitung secara otomatis oleh openpyxl. Untuk melihat nilai hasil perhitungan, gunakan load_workbook(“file.xlsx”, data_only=True).  

Menyisipkan bagan

openpyxl dapat membuat berbagai jenis bagan:

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

wb = Workbook()
sheet = wb.active

# Add data
months = ["Jan", "Feb", "Mar", "Apr", "May"]
values = [30, 45, 37, 50, 62]

for i, (month, value) in enumerate(zip(months, values), start=1):
    sheet.cell(row=i, column=1, value=month)
    sheet.cell(row=i, column=2, value=value)

# Create a bar chart
bar_chart = BarChart()
bar_chart.title = "Monthly Sales"
bar_chart.x_axis.title = "Month"
bar_chart.y_axis.title = "Sales"

# Define the data range
data = Reference(sheet, min_col=2, min_row=1, max_row=5)
categories = Reference(sheet, min_col=1, min_row=1, max_row=5)

# Add the data to the chart
bar_chart.add_data(data)
bar_chart.set_categories(categories)

# Add the chart to the worksheet
sheet.add_chart(bar_chart, "D1")

# Create a line chart on the same data
line_chart = LineChart()
line_chart.title = "Monthly Sales Trend"
line_chart.add_data(data)
line_chart.set_categories(categories)
sheet.add_chart(line_chart, "D15")

wb.save("charts.xlsx")

Menyisipkan Gambar

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
sheet = wb.active

# Add an image
img = Image("logo.png")

# Resize the image (optional)
img.width = 150
img.height = 75

# Add the image to cell A1
sheet.add_image(img, "A1")

wb.save("with_image.xlsx")

Catatan: Anda memerlukan pustaka Pillow terinstal untuk bekerja dengan gambar: pip install pillow

Manajemen Sheet, Row, dan Column

openpyxl menyediakan fungsi untuk mengelola struktur file Excel Anda:

Membuat dan menghapus sheet

from openpyxl import Workbook

wb = Workbook()

# Create new sheets
wb.create_sheet("Data")
wb.create_sheet("Summary", 0)  # Add at the beginning

# Remove a sheet
wb.remove(wb["Sheet"])  # Remove the default sheet

print(wb.sheetnames)  # ['Summary', 'Data']

wb.save("multiple_sheets.xlsx")

Mengganti nama sheet

wb = Workbook()
sheet = wb.active
sheet.title = "Sales Report"
wb.save("renamed_sheet.xlsx")

Menyalin worksheet

from openpyxl import Workbook

wb = Workbook()
source = wb.active
source.title = "Original"

# Add some data to copy
source["A1"] = "Test Data"

# Create a copy
wb.copy_worksheet(source)

# The copied sheet will have "Copy of Original" name
wb.save("copied_sheet.xlsx")

Menyisipkan dan menghapus baris serta kolom

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add some data
for i in range(1, 6):
    for j in range(1, 4):
        sheet.cell(row=i, column=j, value=f"R{i}C{j}")

# Insert a row at position 2
sheet.insert_rows(2)

# Insert multiple rows
sheet.insert_rows(5, 3)  # Insert 3 rows at position 5

# Insert a column at position 2
sheet.insert_cols(2)

# Delete rows
sheet.delete_rows(7, 2)  # Delete 2 rows starting at row 7

# Delete columns
sheet.delete_cols(3)  # Delete column C

wb.save("modified_structure.xlsx")

Membekukan panel dan menambahkan filter

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active

# Add headers
headers = ["ID", "Name", "Department", "Salary"]
for col_idx, header in enumerate(headers, start=1):
    sheet.cell(row=1, column=col_idx, value=header)

# Add data
data = [
    [1, "John Smith", "HR", 55000],
    [2, "Jane Doe", "IT", 65000],
    [3, "Bob Johnson", "Finance", 60000]
]
for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        sheet.cell(row=row_idx, column=col_idx, value=value)

# Freeze the header row
sheet.freeze_panes = "A2"

# Add autofilter
sheet.auto_filter.ref = "A1:D4"

wb.save("freeze_and_filter.xlsx")

Keterbatasan dan Masukan Komunitas

Meskipun openpyxl kaya fitur, pengguna mencatat beberapa keterbatasan:

• Dukungan terbatas untuk named range
• Contoh dokumentasi bisa minim untuk fitur lanjutan
• Performa dapat lambat untuk file yang sangat besar (meskipun mode read-only membantu)

Terlepas dari keterbatasan ini, openpyxl memiliki dukungan komunitas yang kuat. Isu dapat dilaporkan di pelacak GitLab, dan dukungan profesional terkadang tersedia. Untuk informasi dan bantuan lebih lanjut, Anda dapat merujuk ke:

• Dokumentasi openpyxl
• Kode sumber
• Pelacak isu
• Dukungan profesional

Kesimpulan

openpyxl adalah pustaka yang mapan untuk otomasi Excel berbasis Python yang menawarkan dukungan lengkap untuk membaca, menulis, dan memformat file Excel. Pustaka ini memungkinkan kontrol terperinci atas keluaran spreadsheet dalam alur kerja otomatis, mulai dari memanipulasi sel individual hingga membuat bagan dan menerapkan pemformatan kompleks.

Bagi pengguna yang perlu membuat atau menganalisis file Excel secara terprogram, khususnya saat membangun laporan atau memproses data tabel, openpyxl menyediakan seperangkat alat yang luas. Kemampuannya untuk bekerja tanpa memerlukan instalasi Excel menjadikannya sangat berharga untuk lingkungan server dan pipeline data otomatis.

Baik Anda membuat model keuangan, menganalisis data, atau menghasilkan laporan, openpyxl memberi Anda alat untuk menangani file Excel secara efisien melalui kode Python.