import os
import requests
from openpyxl import Workbook
from openpyxl.drawing.image import Image
from concurrent.futures import ThreadPoolExecutor

# 圖片鏈接列表
image_urls = [
    "https://uploads/file/20230205/f85Lpcv8PXrLAdmNUDE1Hh6xqkp0NHi2gSXeqyOb.png",
    "https://uploads/file/20230205/geG4FOpthrsUX0LkmWvDH2veFtw6yj8JLDMYBaQ1.png",
    "https://uploads/file/20230205/mjVAx4jsbke6uj0e2Qz66f8KDceL1P5tanKQkNoy.png"
]
output_dir = "C:/Users/win-10/Desktop/發(fā)票圖片/"  # 指定Excel文件的輸出目錄
# 保存圖片的本地目錄
save_folder = "C:/Users/win-10/Desktop/發(fā)票圖片/downloaded_images/"
# Excel文件名
excel_filename = "images_with_links.xlsx"
# 最大下載嘗試次數(shù)
max_download_attempts = 3


def download_image(url, filename, attempts=0):
    """
    下載圖片到指定文件名

    :param url: 圖片的URL鏈接
    :param filename: 保存圖片的本地文件名
    :param attempts: 當前下載嘗試次數(shù)，默認為0
    :return: 成功保存的文件名，下載失敗返回None
    """
    try:
        response = requests.get(url, stream=True)
        if response.status_code == 200:
            with open(filename, 'wb') as f:
                for chunk in response.iter_content(1024):
                    f.write(chunk)
            return url, filename  # 返回包含URL和文件名的元組
        else:
            raise Exception(f"HTTP錯誤碼：{response.status_code}")
    except Exception as e:
        if attempts < max_download_attempts - 1:
            print(f"下載嘗試失敗：{e}，重試...")
            return download_image(url, filename, attempts + 1)
        else:
            print(f"下載失?。簕url}，{e}")
            return url, None  # 返回包含URL和None（表示下載失?。┑脑M


def create_excel_file(image_data, output_dir, excel_filename):
    """
    創(chuàng)建Excel文件并添加圖片信息

    :param image_data: 包含圖片URL和本地路徑的元組列表
    :param output_dir: 目標Excel文件的輸出目錄
    :param excel_filename: Excel文件名（不含目錄路徑）
    """
    global cm_to_px_ratio
    workbook = Workbook()
    sheet = workbook.active

    for idx, (img_url, img_path) in enumerate(image_data, start=1):
        sheet[f"A{idx}"] = img_url
        img = Image(img_path[1])  # 使用元組的第二個元素（文件名）

        # 設(shè)置圖片大小為6厘米 × 6厘米
        cm_to_px_ratio = 20  # 假設(shè)1厘米等于96像素
        img.width = 6 * cm_to_px_ratio
        img.height = 6 * cm_to_px_ratio

        # 將圖片放置在與鏈接同一行的第二列（B列）
        img.anchor = f"B{idx}"
        img.left = idx  # 或者 img.left = idx * 250  如果需要圖片間有一定的間距
        img.top = idx
        sheet.add_image(img)
        # 調(diào)整列寬以適應(yīng)內(nèi)容
        sheet.column_dimensions['A'].width = 6 * cm_to_px_ratio
        sheet.column_dimensions['B'].width = 6 * cm_to_px_ratio
        sheet.row_dimensions[idx].height = 6 * cm_to_px_ratio

    excel_full_path = os.path.join(output_dir, excel_filename)
    workbook.save(excel_full_path)
    print(f"圖片及其鏈接已保存至Excel文件：{excel_full_path}")


if __name__ == "__main__":
    if not image_urls:
        print("圖片鏈接列表為空，程序退出。")
        exit(1)

    # 創(chuàng)建保存目錄（如果不存在）
    if not os.path.exists(save_folder):
        os.makedirs(save_folder)

    with ThreadPoolExecutor(max_workers=5) as executor:
        # 使用線程池并發(fā)下載圖片
        image_futures = [
            executor.submit(download_image, url, os.path.join(save_folder, f"image{idx}.{url.split('.')[-1]}")) for
            idx, url in enumerate(image_urls, start=1)]
        # 收集下載結(jié)果
        image_data = [(url, future.result()) for idx, (url, future) in
                      enumerate(zip(image_urls, image_futures), start=1)]

    # 使用下載的圖片信息創(chuàng)建Excel文件

    create_excel_file(image_data, output_dir, excel_filename)

import pandas as pd
import requests
from docx import Document
from docx.shared import Inches
import os
import uuid
from datetime import datetime
from pdf2image import convert_from_path
import tempfile


def read_excel_to_dict(file_path, sheet_name=0):
    """
    讀取Excel文件并返回字典列表
    """
    try:
        df = pd.read_excel(file_path, sheet_name=sheet_name)

        # 打印列名用于調(diào)試
        print("Excel 列名:", list(df.columns))

        # 填充 NaN 值為空字符串，防止后續(xù)出錯
        df = df.fillna('')

        return df.to_dict('records')
    except FileNotFoundError:
        raise FileNotFoundError(f"文件 {file_path} 不存在")
    except Exception as e:
        raise RuntimeError(f"讀取Excel文件失敗: {str(e)}")


def filter_data_by_month(data, target_order_ids=None):
    """
    篩選下單時間為 2024年11月 和 12月 的數(shù)據(jù)，每組取35個有效條目（不重）
    要求：發(fā)票和驗收單鏈接不為空或無效標記
    """
    from collections import defaultdict

    # 每月存儲訂單且使用集合記錄已處理的訂單號
    monthly_orders = defaultdict(list)
    seen_order_ids = set()  # 全局記錄已經(jīng)處理過的訂單號（防止跨月重復）
    result = []
    if target_order_ids:
        # ?? 用戶主動指定了訂單號，匹配這些訂單
        target_order_ids = set(target_order_ids)  # 去重 & 提高查找效率

        for item in data:
            order_id = item.get('訂單號', '').strip()

            if not order_id or order_id not in target_order_ids or order_id in seen_order_ids:
                continue

            # 復用原有字段有效性判斷邏輯
            invoice_link = str(item.get('發(fā)票', '')).strip()
            receipt_link = str(item.get('驗收單', '')).strip()

            invalid_values = {'nan', '#n/a', '#na', 'n/a', 'na', '', 'none', '<null>'}
            if invoice_link.lower() in invalid_values or receipt_link.lower() in invalid_values:
                continue

            try:
                # 嘗試解析時間字段構(gòu)造訂單月份
                order_time_str = str(item['下單時間']).strip()
                order_time = datetime.strptime(order_time_str, "%Y-%m-%d %H:%M:%S")
                month = order_time.month
                item['訂單月份'] = f"2024年{month}月"
            except Exception:
                item['訂單月份'] = "未知月份"

            seen_order_ids.add(order_id)
            result.append(item)
    else:
        for item in data:
            order_time_str = str(item.get('下單時間', '')).strip()
            invoice_link = str(item.get('發(fā)票', '')).strip()
            receipt_link = str(item.get('驗收單', '')).strip()

            if not order_time_str:
                continue

            try:
                # 解析時間字符串并獲取年月
                order_time = datetime.strptime(order_time_str, "%Y-%m-%d %H:%M:%S")
                year, month = order_time.year, order_time.month

                # 只保留 2024年11月 和 12月，并且訂單號不能重復
                if year == 2024 and month in [11, 12]:
                    order_id = item.get('訂單號', '')

                    # 更全面判斷無效值（包括 nan、#N/A 等）
                    invalid_values = {'nan', '#n/a', '#na', 'n/a', 'na', '', 'none', '<null>'}
                    if (
                            invoice_link.lower() in invalid_values or
                            receipt_link.lower() in invalid_values or
                            order_id in seen_order_ids
                    ):
                        continue

                    # 構(gòu)造“訂單月份”字段供后續(xù)使用
                    item['訂單月份'] = f"2024年{month}月"
                    monthly_orders[f"2024年{month}月"].append(item)
                    seen_order_ids.add(order_id)  # 標記為已處理

            except ValueError:
                continue
        for month, items in monthly_orders.items():
            count = len(items)
            print(f"【{month}】共找到 {count} 條不重復的有效數(shù)據(jù)")
            result.extend(items[:35])  # 每月最多取前35條，確保不重復

    return result


def download_image(url, save_path):
    """下載圖片并保存到指定路徑"""
    try:
        response = requests.get(url, timeout=10)
        if response.status_code == 200:
            with open(save_path, 'wb') as f:
                f.write(response.content)
            return True
        else:
            print(f"無法下載圖片：{url}")
            return False
    except Exception as e:
        print(f"下載圖片出錯: {e}")
        return False


def write_data_to_word(data, output_file):
    """
    將數(shù)據(jù)寫入Word文檔，并插入相關(guān)圖片（支持多張發(fā)票/驗收單圖片）
    """
    document = Document()

    if not data:
        print("?? 沒有數(shù)據(jù)可寫入文檔！")
        return

    for index, item in enumerate(data):
        print(f"正在處理第訂單號為 {item.get('訂單號')}的數(shù)據(jù)")
        # 從下單時間提取月份
        order_time_raw = item.get('下單時間')
        try:
            if isinstance(order_time_raw, pd.Timestamp):
                order_time_str = order_time_raw.strftime("%Y-%m-%d %H:%M:%S")
            else:
                order_time_str = str(order_time_raw).strip()
            order_time = datetime.strptime(order_time_str, "%Y-%m-%d %H:%M:%S")
            month = order_time.month
            month_label = f"2024年{month}月"
        except ValueError:
            month_label = "未知月份"

        # 添加主標題
        document.add_heading(f"{index + 1}）{month_label}訂單", level=1)

        # 添加子標題（訂單編號）
        order_id = item.get('訂單號', '未知編號')
        document.add_heading(f"{index + 1}.{order_id}驗收單及發(fā)票", level=3)

        # 添加驗收單描述
        # document.add_paragraph("根據(jù)數(shù)據(jù)連接下載的高清“驗收單”圖片")

        # 下載并插入驗收單圖片（支持多個鏈接）
        receipt_links = item.get('驗收單', '')
        if receipt_links.strip():
            receipt_link_list = [link.strip() for link in receipt_links.split(',') if link.strip()]
            for i, url in enumerate(receipt_link_list):
                yanshou_img_path = f"yanshou_temp_{uuid.uuid4().hex}.jpg"
                if download_image(url, yanshou_img_path):
                    try:
                        document.add_picture(yanshou_img_path, width=Inches(5))
                    except Exception as e:
                        print(f"插入驗收單圖片失敗: {e}")
                    finally:
                        if os.path.exists(yanshou_img_path):
                            os.remove(yanshou_img_path)

        # 添加發(fā)票描述
        # document.add_paragraph("     根據(jù)數(shù)據(jù)連接下載的高清“發(fā)票”圖片")

        # 下載并插入發(fā)票圖片（支持多個鏈接）
        invoice_links = item.get('發(fā)票', '')
        if invoice_links.strip():
            invoice_link_list = [link.strip() for link in invoice_links.split(',') if link.strip()]
            for i, url in enumerate(invoice_link_list):
                fapiao_img_path = f"fapiao_temp_{uuid.uuid4().hex}.jpg"
                if download_image(url, fapiao_img_path):
                    try:
                        document.add_picture(fapiao_img_path, width=Inches(5))
                    except Exception as e:
                        print(f"插入發(fā)票圖片失敗: {e}")
                    finally:
                        if os.path.exists(fapiao_img_path):
                            os.remove(fapiao_img_path)

    # 保存文檔
    document.save(output_file)
    print(f"文檔已保存為 {output_file}")


# 示例調(diào)用
if __name__ == "__main__":
    excel_file = r'F:\訂單250430.xlsx'
    output_word_file = r'F:\訂單匯總.docx'

    print("開始讀取Excel文件...")
    raw_data = read_excel_to_dict(excel_file)
    print(f"讀取到 {len(raw_data)} 條原始數(shù)據(jù)")

    print("開始按月份篩選數(shù)據(jù)...")
    target_order_ids = ['b064536e31', '96937b09bc', 'e2dd08d858', '4cf8df81ae']
    filtered_data = filter_data_by_month(raw_data, target_order_ids)
    print(f"篩選后共 {len(filtered_data)} 條有效數(shù)據(jù)")

    if not filtered_data:
        print("?? 沒有符合要求的數(shù)據(jù)，請檢查字段名稱和數(shù)據(jù)內(nèi)容！")
    else:
        print("開始寫入Word文檔...")
        write_data_to_word(filtered_data, output_word_file)