# -*- coding: utf-8 -*-

import requests
from bs4 import BeautifulSoup
from csv import writer
import pandas as pd

# Web sayfasından verileri çekme
header = {"User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36"}        
atk = requests.get("https://ankarateknokent.com/firmalar-portfolio/", headers=header)   

req1 = BeautifulSoup(atk.text, "html.parser")
sonuc = req1.body

firmalar = sonuc.find_all(class_="gallery-item-caption-over")

# Verileri CSV dosyasına yazma
try:
    with open('firmalar.csv', 'w', newline='', encoding='utf-8') as csv_file:
        csv_writer = writer(csv_file)
        csv_writer.writerow(['firma_ismi', 'firma_tel', 'firma_web'])  # Başlıkları ekleyin
    
        for firma in firmalar:
            firma_ismi = firma.find(class_="fg-item-title").string
            firma_tel = firma.find(class_="fg-item-content").find("p").string
            firma_web = firma.find("a")["href"]
    
            csv_writer.writerow([firma_ismi, firma_tel, firma_web])
except Exception as e:
    print(e)

# CSV dosyasını okuyarak DataFrame oluşturma
columns = ['firma_ismi', 'firma_tel', 'firma_web']
dffirmalar = pd.read_csv("firmalar.csv", names=columns, header=0, sep=",", encoding="utf-8")

# Excel dosyasına yazma
with pd.ExcelWriter("ATK_FirmaListesi.xlsx", engine='openpyxl') as writer:
    dffirmalar.to_excel(writer, sheet_name="ATK_Firmalar", index=False)
    worksheet = writer.sheets["ATK_Firmalar"]
    
    # Dinamik olarak sütun genişliğini ayarlama
    for i, col in enumerate(dffirmalar.columns):
        column_len = max(dffirmalar[col].astype(str).str.len().max(), len(col) + 2)
        worksheet.column_dimensions[chr(65 + i)].width = column_len
    
    # Auto filter ve freeze panes ekleme
    worksheet.auto_filter.ref = worksheet.dimensions
    worksheet.freeze_panes = worksheet['A2']