Excel otomasyonu: openpyxl, pandas ve rapor üretimi

Neden önemli?

Anadolu Market finans ekibinde her pazartesi aynı iş yapılıyor: satış sisteminden CSV indirilir, Excel'de açılır, şehirlere göre özet tablo hazırlanır, toplam satırına formül yazılır, başlık kalın yapılır, sütunlar genişletilir, bir grafik eklenir ve dosya yöneticilere gönderilir. Bu iş her hafta 1–2 saat sürer ve her seferinde küçük bir kopyala-yapıştır hatası riski taşır. Python ile aynı rapor birkaç saniyede, her hafta aynı biçimde ve hatasız üretilir.

Şirketlerin büyük kısmında veri hâlâ Excel'de yaşar: bütçe tabloları, stok sayımları, İK puantajları, belediye tahsilat listeleri. Excel'i okuyup yazabilen bir Python geliştiricisi, "veri ekibi" ile "iş birimi" arasındaki köprü olur. Bu derste iki ana aracı öğreneceksin: hücre düzeyinde kontrol için openpyxl, tablo düzeyinde hızlı iş için pandas.

Excel dosyasının yapısı

Bir .xlsx dosyası aslında içinde XML dosyaları olan bir ZIP arşividir. Kod gözüyle bakınca üç katman vardır:

Hücre adresi (cell address) harf + sayıdır: B2 2. satır, 2. sütun demektir. Sütun harfleri 26 tabanlı ama sıfırsız bir sayma sistemidir: A=1 … Z=26, AA=27, AZ=52, BA=53. Bu yüzden "harfi sayıya çevir" işlemi klasik bir mülakat sorusudur ve Excel otomasyonunda sürekli lazım olur.

def sutun_no(harfler):
    no = 0
    for ch in harfler.upper():
        no = no * 26 + (ord(ch) - ord("A") + 1)
    return no

for h in ["A", "Z", "AA", "AZ", "BA", "XFD"]:
    print(h, "→", sutun_no(h))

Son satırdaki XFD Excel'in en son sütunudur (16.384). Bir sayfada en fazla 1.048.576 satır bulunur; daha büyük veri için Excel doğru araç değildir.

openpyxl ile rapor yazmak

Aşağıdaki kod hem bilgisayarında hem de bu sayfada çalışır: dersin sonundaki "Gerçek .xlsx dosyasıyla çalışmak" bölümünde ve alıştırmalarda openpyxl tarayıcıda gerçek dosya yazar. İlk bölüm hücre modelini saf Python ile kurar, mantığı görmen için.

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.title = "Şehir Özeti"

ws["A1"] = "Anadolu Market — Haftalık Ciro"
ws.merge_cells("A1:C1")                       # başlığı üç sütuna yay
ws["A1"].font = Font(size=14, bold=True)
ws["A1"].alignment = Alignment(horizontal="center")

ws.append(["Şehir", "Sipariş", "Ciro"])       # 2. satır: başlıklar
for satir in [["İzmir", 42, 18250.5], ["Ankara", 35, 15100.0], ["Bursa", 18, 7420.75]]:
    ws.append(satir)

ws["A6"] = "TOPLAM"
ws["B6"] = "=SUM(B3:B5)"                      # formül metin olarak yazılır
ws["C6"] = "=SUM(C3:C5)"

baslik = PatternFill("solid", fgColor="DDEBF7")
for hucre in ws[2]:                            # 2. satırın tüm hücreleri
    hucre.font = Font(bold=True)
    hucre.fill = baslik
for satir in ws.iter_rows(min_row=3, min_col=3, max_col=3):
    satir[0].number_format = "#,##0.00"        # binlik ayraç + 2 ondalık

ws.column_dimensions["A"].width = 16
ws.freeze_panes = "A3"                        # başlıklar kaydırınca sabit kalsın

grafik = BarChart()
grafik.title = "Şehre göre ciro"
veri = Reference(ws, min_col=3, min_row=2, max_row=5)
etiket = Reference(ws, min_col=1, min_row=3, max_row=5)
grafik.add_data(veri, titles_from_data=True)
grafik.set_categories(etiket)
ws.add_chart(grafik, "E2")

wb.save("haftalik_rapor.xlsx")

Okumak için load_workbook kullanılır. iter_rows(values_only=True) hücre nesneleri yerine doğrudan değerleri demet (tuple) olarak verir; veriyi işlemenin en pratik yoludur:

from openpyxl import load_workbook

wb = load_workbook("stok.xlsx", data_only=True)   # formül yerine son hesaplanan değer
ws = wb["Depo"]
print(ws.max_row, ws.max_column)
for urun, adet, fiyat in ws.iter_rows(min_row=2, values_only=True):
    if adet is None:          # boş hücre None gelir
        continue
    print(urun, adet * fiyat)

Hücreleri kod içinde modellemek

openpyxl'in yaptığı iş özünde "adres → değer" eşlemesidir. Bunu bir sözlükle taklit edersen hem mantığı kavrar hem de raporu test edilebilir hale getirirsin: önce sözlüğü üret ve test et, en son tek bir fonksiyonla Excel'e dök.

def harf(n):
    s = ""
    while n > 0:
        n, kalan = divmod(n - 1, 26)
        s = chr(65 + kalan) + s
    return s

satirlar = [["Şehir", "Ciro"], ["İzmir", 18250.5], ["Ankara", 15100.0]]
sayfa = {}
for r, satir in enumerate(satirlar, start=1):
    for c, deger in enumerate(satir, start=1):
        sayfa[f"{harf(c)}{r}"] = deger
son = len(satirlar)
sayfa[f"A{son + 1}"] = "TOPLAM"
sayfa[f"B{son + 1}"] = f"=SUM(B2:B{son})"
for adres, deger in sayfa.items():
    print(adres, deger)

Formüller: İngilizce ad, virgül ayraç

Türkçe Excel'de =TOPLA(B2;B10) görürsün ama dosyanın içinde formüller her zaman İngilizce ad ve virgül ayraç ile saklanır. Excel açılırken arayüz diline çevirir. Bu yüzden openpyxl ile Türkçe formül yazarsan Excel #AD? (#NAME?) hatası gösterir.

TR_EN = {"TOPLA": "SUM", "ORTALAMA": "AVERAGE", "EĞERSAY": "COUNTIF"}

def formul(islev, sutun, ilk, son):
    ad = TR_EN.get(islev.upper(), islev.upper())
    return f"={ad}({sutun}{ilk}:{sutun}{son})"

print(formul("TOPLA", "C", 2, 41))
print(formul("average", "D", 2, 41))

pandas ile Excel: tablo düzeyinde iş

Hücre biçimiyle değil, veriyle ilgileniyorsan pandas çok daha kısadır. pandas'ı ileri seviyede derinlemesine göreceksin; burada Excel'e özgü kısımlar yeterli.

import pandas as pd

df = pd.read_excel("satislar.xlsx", sheet_name="Ekim", usecols="A:D", dtype={"musteri_no": str})
tum = pd.read_excel("satislar.xlsx", sheet_name=None)   # {sayfa_adı: DataFrame} sözlüğü
birlesik = pd.concat(tum.values(), keys=tum.keys(), names=["ay", None]).reset_index(level=0)

ozet = df.pivot_table(index="sehir", columns="kategori", values="tutar", aggfunc="sum", fill_value=0)

with pd.ExcelWriter("rapor.xlsx", engine="openpyxl") as yazici:
    df.to_excel(yazici, sheet_name="Ham Veri", index=False)
    ozet.to_excel(yazici, sheet_name="Özet")
    yazici.sheets["Özet"].column_dimensions["A"].width = 18   # openpyxl sayfasına erişim

Rapor üretiminin "hesap" kısmı dosya biçiminden bağımsızdır. Aynı pivot mantığını CSV ile görelim; Excel'e yazmayı dersin sonundaki görevlerde yapacaksın:

import io
import pandas as pd

csv_metni = """sehir,ay,tutar
İzmir,Eylül,1200
İzmir,Ekim,1500
Ankara,Ekim,900
Ankara,Ekim,300
Bursa,Eylül,450
"""
df = pd.read_csv(io.StringIO(csv_metni))
ozet = df.pivot_table(index="sehir", columns="ay", values="tutar", aggfunc="sum", fill_value=0)
print(ozet)
print(ozet.reset_index().to_csv(index=False))

openpyxl mi, xlsxwriter mı, CSV mi?

Kural: sistemden sisteme veri taşıyorsan CSV (ya da Parquet); insanların okuyacağı biçimli bir rapor üretiyorsan XLSX. Şirket şablonunu doldurmak gerekiyorsa openpyxl, sıfırdan büyük ve grafikli rapor için xlsxwriter (pip install xlsxwriter, pd.ExcelWriter(..., engine="xlsxwriter")).

Türkçe sayı ve tarih biçimleri

Excel bir sayıyı içeride her zaman sayı olarak tutar; 1.234,56 yalnızca görünüştür (number format). Sorun, birileri Türkçe Excel'den CSV aldığında ya da hücreye sayıyı metin olarak yapıştırdığında başlar: "1.234,56" metni float() ile çevrilemez. Doğru sıra önemlidir: önce binlik noktalarını sil, sonra ondalık virgülünü noktaya çevir.

def tr_coz(metin):
    temiz = metin.strip().replace("TL", "").strip()
    return float(temiz.replace(".", "").replace(",", "."))

def tr_yaz(sayi):
    s = f"{sayi:,.2f}"                       # 1,234,567.89
    return s.replace(",", "_").replace(".", ",").replace("_", ".")

print(tr_coz("1.234,56"), tr_coz("  -12,5 TL"))
print(tr_yaz(1234567.891), tr_yaz(0.5))

pandas'ta aynı iş parametreyle yapılır: pd.read_csv(yol, sep=";", decimal=",", thousands=".", encoding="utf-8-sig"). Türkçe Excel CSV'yi noktalı virgülle ayırır, çünkü virgül ondalık işaretidir. utf-8-sig ise Excel'in dosya başına koyduğu BOM işaretini temizler; bunu json-csv dersinde de görmüştün.

Tarihler ise Excel'de seri numarası olarak saklanır: 1899-12-30'dan bu yana geçen gün sayısı. Bazen tarih sütunu 45000 gibi sayılarla gelir; bunu şöyle çevirirsin:

from datetime import date, timedelta, datetime

def excel_tarih(seri):
    return date(1899, 12, 30) + timedelta(days=int(seri))

print(excel_tarih(45000), excel_tarih(46323))
print(datetime.strptime("28.10.2026", "%d.%m.%Y").date())
print(date(2026, 10, 28).strftime("%d.%m.%Y"))

Büyük dosyalar

openpyxl normal modda tüm dosyayı belleğe alır; 200 bin satırlık bir dosyada yüzlerce MB bellek kullanabilir. İki özel mod vardır:

• load_workbook(yol, read_only=True): satırları akış halinde okur; yalnızca iter_rows ile gezinirsin, hücre yazamazsın. İş bitince wb.close().
• Workbook(write_only=True): yalnızca ws.append() ile sona satır eklersin; çok hızlı ve az bellekli yazım.

pandas read_excel parça parça (chunk) okumayı desteklemez. Düzenli gelen büyük Excel dosyalarını bir kez CSV ya da Parquet'e çevirmek, sonraki işleri çok hızlandırır.

Rapor otomasyonunun adımları

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

wb = Workbook()
ws = wb.active
ws.title = "Satış"
ws.append(["Ürün", "Adet"])
ws.append(["Çay", 3])
ws["A1"].font = Font(bold=True)
ws["C2"] = "=B2*2"
wb.save("ornek.xlsx")

okunan = load_workbook("ornek.xlsx")
print(okunan.sheetnames, okunan["Satış"]["C2"].value)
print(load_workbook("ornek.xlsx", data_only=True)["Satış"]["C2"].value)

# Power BI → Veri al → Python betiği
import pandas as pd
satis = pd.read_csv(r"C:\veri\kesit_bi\Satislar.csv")
satis = satis[satis["Durum"] != "İptal"]          # yalnız DataFrame'ler tablo olur
ozet = satis.groupby("Kanal", as_index=False)["Tutar"].sum()

# Python görseli (rapor sayfasında): Power BI "dataset" DataFrame'ini verir
import matplotlib.pyplot as plt
dataset.groupby("Kategori")["Tutar"].sum().sort_values().plot.barh()
plt.show()

=PY(
df = xl("Satislar[#All]", headers=True)
df.groupby("Kanal")["Tutar"].sum()
)

import veri

satis = veri.oku("kesit_bi", "Satislar")
urun = veri.oku("kesit_bi", "Urunler")
musteri = veri.oku("kesit_bi", "Musteriler")
print("olgu:", satis.shape, "| ürün:", urun.shape, "| müşteri:", musteri.shape)

# boyut anahtarları tekil mi? olgudaki her anahtarın boyutta karşılığı var mı?
print(urun["UrunNo"].is_unique, musteri["MusteriNo"].is_unique)
print(satis["UrunNo"].isin(urun["UrunNo"]).all(), satis["MusteriNo"].isin(musteri["MusteriNo"]).all())

tablo = satis.merge(urun[["UrunNo", "Kategori"]], on="UrunNo", validate="many_to_one")
print(tablo.groupby("Kategori")["Tutar"].sum().round(0).sort_values(ascending=False).head(3))

kalem = veri.oku("anadolu_market", "order_items")
siparis = veri.oku("anadolu_market", "orders")
urunler = veri.oku("anadolu_market", "products")
duz = (kalem.merge(siparis, on="order_id")
            .merge(urunler[["product_id", "name", "category"]], on="product_id"))
print(duz.shape)

dim_urun = duz[["product_id", "name", "category"]].drop_duplicates().sort_values("product_id")
olgu = duz[["item_id", "order_id", "order_date", "product_id", "quantity", "unit_price", "discount"]].copy()
olgu["TarihKey"] = olgu["order_date"].str.replace("-", "").astype(int)   # 20240131 biçimi
olgu["tutar"] = (olgu["quantity"] * olgu["unit_price"] * (1 - olgu["discount"])).round(2)
print(dim_urun.shape, olgu.shape)
print(olgu[["TarihKey", "tutar"]].head(3))