import requests
import json
import os
import time
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.utils import get_column_letter

requests.packages.urllib3.disable_warnings()

API_URL = "https://push2.eastmoney.com/api/qt/clist/get"
EXCEL_SAVE_PATH = "作业2/股票数据.xlsx"
FIELD_MAPPING = {
    "f12": "股票代码", "f14": "股票名称", "f2": "最新报价", "f3": "涨跌幅",
    "f4": "涨跌额", "f7": "成交量", "f8": "成交额", "f15": "最高",
    "f16": "最低", "f17": "今开", "f18": "昨收", "f23": "振幅"
}
EXCEL_HEADERS = ["序号", "股票名称", "股票代码", "最新报价", "涨跌幅", "涨跌额", 
                 "振幅", "最高", "最低", "今开", "昨收", "成交量", "成交额"]

MY_COOKIE = "st_inirUrl=https%3A%2F%2Fwww.eastmoney.com%2F; st_asi=delete; qgqp_b_id=c82b4c52776ca917068c06efed2b0d9c; st_nvi=_6T45tlila7AZH7MkVcagf80c; rskey=DXk79VzBIWmFwUnRDUWpLK3BMeGYvRVp6QT093dgIL; gvi=91G3yXm99SxaenjvbyorK3987; st_pvi=82904229884786; st_psi=20251029152250326-113200301321-2418745882; st_sp=2025-10-29%2014%3A07%3A23; st_si=19338798169197; nid_create_time=1761718043978; gvi_create_time=1761718043978; st_sn=11; fullscreengg2=1; fullscreengg=1; nid=0e6db4cc9634e9e54ad55022890b8f8b; isoutside=0"

def format_stock_data(raw_data):
    formatted = {}
    for api_field, chinese_name in FIELD_MAPPING.items():
        value = raw_data.get(api_field, 0)
        
        if chinese_name == "涨跌幅":
            formatted[chinese_name] = f"{value:.2f}%" if value != 0 else "0.00%"
        elif chinese_name == "涨跌额":
            formatted[chinese_name] = round(value, 2)
        elif chinese_name == "振幅":
            formatted[chinese_name] = f"{value:.2f}%" if value != 0 else "0.00%"
        elif chinese_name == "成交量":
            formatted[chinese_name] = f"{value/10000:.2f}万" if value >= 10000 else f"{value}手"
        elif chinese_name == "成交额":
            formatted[chinese_name] = f"{value/100000000:.2f}亿" if value >= 100000000 else f"{value}元"
        elif chinese_name in ["最新报价", "最高", "最低", "今开", "昨收"]:
            formatted[chinese_name] = round(value, 2)
        else:
            formatted[chinese_name] = value
    return formatted

def create_excel():
    wb = Workbook()
    ws = wb.active
    ws.title = "东方财富股票数据"
    
    for col_idx, header in enumerate(EXCEL_HEADERS, 1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = PatternFill(start_color="2E86AB", end_color="2E86AB", fill_type="solid")
        cell.alignment = Alignment(horizontal="center", vertical="center")
    
    column_widths = [8, 15, 12, 12, 12, 12, 12, 12, 12, 12, 12, 15, 15]
    for col_idx, width in enumerate(column_widths, 1):
        ws.column_dimensions[get_column_letter(col_idx)].width = width
    
    return wb, ws


def write_to_excel(ws, data, row_idx):
    ws.cell(row=row_idx, column=1, value=row_idx - 1)
    ws.cell(row=row_idx, column=2, value=data["股票名称"])
    ws.cell(row=row_idx, column=3, value=data["股票代码"])
    ws.cell(row=row_idx, column=4, value=data["最新报价"])
    ws.cell(row=row_idx, column=5, value=data["涨跌幅"])
    ws.cell(row=row_idx, column=6, value=data["涨跌额"])
    ws.cell(row=row_idx, column=7, value=data["振幅"])
    ws.cell(row=row_idx, column=8, value=data["最高"])
    ws.cell(row=row_idx, column=9, value=data["最低"])
    ws.cell(row=row_idx, column=10, value=data["今开"])
    ws.cell(row=row_idx, column=11, value=data["昨收"])
    ws.cell(row=row_idx, column=12, value=data["成交量"])
    ws.cell(row=row_idx, column=13, value=data["成交额"])
    
    for col_idx in range(1, len(EXCEL_HEADERS) + 1):
        ws.cell(row=row_idx, column=col_idx).alignment = Alignment(horizontal="center", vertical="center")

def crawl_stock_data(page_num=1, page_size=20):
    api_params = {
        "np": 1,
        "fltt": 1,
        "invt": 2,
        "fs": "m:0+t:6+f:!2,m:0+t:80+f:!2,m:1+t:2+f:!2,m:1+t:23+f:!2,m:0+t:81+s:262144+f:!2",
        "fields": "f12,f13,f14,f1,f2,f4,f3,f152,f5,f6,f7,f15,f18,f16,f17,f10,f8,f9,f23",
        "fid": "f3",
        "pn": page_num,
        "pz": page_size,
        "po": 1,
        "dect": 1,
        "ut": "fa5fd1943c7b386f172d6893dbfba10b",
        "wbp2u": "|0|0|0|web",
        "_": int(time.time() * 1000)
    }

    basic_headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
        "Accept": "text/javascript, application/javascript, application/ecmascript, application/x-ecmascript, */*; q=0.01",
        "Referer": "https://quote.eastmoney.com/",
        "Cookie": MY_COOKIE
    }

    try:
        response = requests.get(
            url=API_URL,
            params=api_params,
            headers=basic_headers,
            timeout=10,
            verify=False
        )
        response.raise_for_status()

        stock_json = response.json()

        stock_list = stock_json.get("data", {}).get("diff", [])
        if not stock_list:
            print(f"第{page_num}页无股票数据")
            return []

        formatted_stocks = [format_stock_data(stock) for stock in stock_list]
        print(f"第{page_num}页爬取成功，共{len(formatted_stocks)}条数据")
        return formatted_stocks

    except Exception as e:
        print(f"第{page_num}页爬取失败：{str(e)[:80]}")
        return []

def main(total_pages=3):
    os.makedirs(os.path.dirname(EXCEL_SAVE_PATH), exist_ok=True)
    wb, ws = create_excel()
    current_row = 2

    for page in range(1, total_pages + 1):
        stock_data_list = crawl_stock_data(page_num=page, page_size=20)
        if not stock_data_list:
            break
        for stock in stock_data_list:
            write_to_excel(ws, stock, current_row)
            current_row += 1

    wb.save(EXCEL_SAVE_PATH)
    total_data_count = current_row - 2
    print(f"\n爬取结束！共{total_data_count}条股票数据已保存至：{EXCEL_SAVE_PATH}")


if __name__ == "__main__":
    main(total_pages=3)