python --version

D:\yearbook_project

main.py

pip install requests pandas beautifulsoup4 openpyxl

base_dir = r"F:\年鑒"

base_dir = r"D:\yearbook_project\data"

main.py

python main.py

D:\yearbook_project\data

import os
import re
import requests
import pandas as pd
from bs4 import BeautifulSoup
from collections import defaultdict

# ================= 配置 =================
base_dir = r"D:\【your_folder】"
years = [2023, 2024]
base_urls = {
    2023: "https://oss.henan.gov.cn/sbgt-wztipt/attachment/hntjj/hntj/lib/tjnj/2023nj/zk/lefte.htm",
    2024: "https://oss.henan.gov.cn/sbgt-wztipt/attachment/hntjj/hntj/lib/tjnj/2024nj/zk/indexce.htm"
}

# ================= 工具函數(shù) =================
def get_excel_links(url, year):
    """解析頁(yè)面，返回所有 Excel 文件的完整 URL 列表"""
    try:
        resp = requests.get(url, timeout=20)
        resp.raise_for_status()
        resp.encoding = 'gb2312'
    except Exception as e:
        print(f"[ERROR] 獲取網(wǎng)頁(yè)失敗: {url} ({e})")
        return []

    soup = BeautifulSoup(resp.text, "html.parser")
    links = []
    for a in soup.find_all('a', href=True):
        href = a['href']
        if href.lower().endswith(('.xls', '.xlsx')):
            if href.startswith("http"):
                links.append(href)
            else:
                base = f"https://oss.henan.gov.cn/sbgt-wztipt/attachment/hntjj/hntj/lib/tjnj/{year}nj/zk/"
                links.append(base + href.lstrip('./'))
    return links

def download_file(url, save_path):
    """下載文件，如果已存在則跳過(guò)"""
    if os.path.exists(save_path) and os.path.getsize(save_path) > 0:
        print(f"[SKIP] 已存在: {save_path}")
        return
    try:
        resp = requests.get(url, timeout=30)
        resp.raise_for_status()
        with open(save_path, 'wb') as f:
            f.write(resp.content)
        print(f"[OK] 下載成功: {save_path}")
    except Exception as e:
        print(f"[ERROR] 下載失敗: {save_path} ({e})")

def clean_and_merge_by_category(year_folder, output_dir):
    excel_files = [f for f in os.listdir(year_folder) if f.lower().endswith(('.xls', '.xlsx'))]
    if not excel_files:
        print(f"[INFO] 沒(méi)有 Excel 文件可整合: {year_folder}")
        return

    category_dict = defaultdict(list)

    # 這里是“按文件名前兩位分組”
    for f in excel_files:
        match = re.match(r'(\d{2})(\d{2})', f)
        if not match:
            print(f"[WARN] 文件名不符合規(guī)則，跳過(guò): {f}")
            continue
        main_cat, sub_cat = match.groups()
        category_dict[main_cat].append((f, sub_cat))

    os.makedirs(output_dir, exist_ok=True)

    for main_cat, files in category_dict.items():
        sheets_to_write = []

        for fname, sub_cat in files:
            path = os.path.join(year_folder, fname)
            try:
                with pd.ExcelFile(path) as xl:
                    for sheet_name in xl.sheet_names:
                        df = xl.parse(sheet_name, header=None)
                        if df.empty:
                            continue
                        sheet_name_clean = re.sub(r'[\\/*?:[\]]', '', sub_cat)[:31]
                        sheets_to_write.append((df, sheet_name_clean))
            except Exception as e:
                print(f"[ERROR] 處理 {path} 失敗 ({e})")

        if not sheets_to_write:
            sheets_to_write.append((pd.DataFrame({"提示": ["此大類(lèi)下無(wú)有效數(shù)據(jù)"]}), "占位"))

        output_file = os.path.join(output_dir, f"{main_cat}_年鑒.xlsx")

        if os.path.exists(output_file):
            os.remove(output_file)

        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            for df, sheet_name_clean in sheets_to_write:
                df.to_excel(writer, sheet_name=sheet_name_clean, index=False, header=False)

        print(f"[OK] {main_cat}_年鑒.xlsx 已生成")

# ================= 主流程 =================
for year in years:
    print(f"\n[INFO] {year} 年抓取 Excel 鏈接...")
    url = base_urls[year]
    links = get_excel_links(url, year)
    print(f"[INFO] {year} 年發(fā)現(xiàn) {len(links)} 個(gè) Excel 文件")

    year_folder = os.path.join(base_dir, str(year), "excel")
    os.makedirs(year_folder, exist_ok=True)

    for link in links:
        fname = os.path.basename(link)
        save_path = os.path.join(year_folder, fname)
        download_file(link, save_path)

    output_dir = os.path.join(base_dir, str(year))
    clean_and_merge_by_category(year_folder, output_dir)

print("\n[INFO] 所有年份下載與整合完成！")

pip install requests pandas beautifulsoup4 openpyxl

FileNotFoundError