from openpyxl import load_workbook
from openpyxl.styles import Font, Border, Side, Alignment
from playwright.sync_api import sync_playwright
from datetime import datetime
 
# 打開瀏覽器并截圖
def capture_table_screenshot( url, output_file, table_selector):
    with sync_playwright() as p:
        browser = p.chromium.launch(headless=False)
        page = browser.new_page()
        # 注意這里需要加協(xié)議
        page.goto("file://" + url)
        
        # 等待表格元素加載完成
        page.wait_for_selector(table_selector)
        page.wait_for_timeout(1000)
        
        # 對(duì)表格元素進(jìn)行截圖
        table_element = page.locator(table_selector)
        table_element.screenshot(path=output_file)
        
        browser.close()
 
# 默認(rèn)合并單元格的文本內(nèi)容是放在左上單元格的，如果不是，需要專門程序處理。
# 邊框樣式默認(rèn)為1px solid
def read_excel(file_path):
    # data_only 將 Excel 表格里的公式計(jì)算成數(shù)值讀取出來(lái)。
    wb = load_workbook( filename=file_path, data_only=True)
    ws = wb.active  # 讀取活動(dòng)工作表
    data = []
    merges = []  # 用于存儲(chǔ)合并單元格的信息
    cell_styles = []
    
    # 讀取合并單元格信息
    for merged_range in ws.merged_cells.ranges:
        start_row, start_col = merged_range.min_row, merged_range.min_col
        end_row, end_col = merged_range.max_row, merged_range.max_col
        merges.append((start_row-1, start_col-1, end_row-1, end_col-1))
 
    for row in ws.iter_rows():
        row_data = []
        row_styles = []
 
        for cell in row:
            print(f"當(dāng)前單元格的坐標(biāo)：{cell.coordinate}")
            if cell.coordinate in ws.merged_cells.ranges:
                # 跳過合并單元格中的非起始單元格
                continue            
            if cell.value is not None:
                print(f"單元格的值：{cell.value}")
                row_data.append(str(cell.value))                
            else:
                row_data.append('')  # 空單元格填充空字符串
            # 讀取單元格樣式，提供默認(rèn)值
            font = cell.font if cell.font else Font()
            border = cell.border if cell.border else Border()
            alignment = cell.alignment if cell.alignment else Alignment()
 
            print(f"單元格字體顏色：{font.color.index}")
            print(f"單元格邊框樣式：{border.top.style}")
            cell_style = {
                'font': {
                    'name': font.name if font.name else 'Arial',
                    'size': font.size if font.size else 12,
                    'bold': font.bold if font.bold else False,
                    'italic': font.italic if font.italic else False,
                    'color': font.color.rgb if font.color and font.color.rgb else '#000000'
                },
                'border': {
                    'top': '1px solid' if border.top and border.top.style else None,
                    'left': '1px solid' if border.left and border.left.style else None,
                    'right': '1px solid' if border.right and border.right.style else None,
                    'bottom': '1px solid' if border.bottom and border.bottom.style else None
                },
                'alignment': {
                    'horizontal': alignment.horizontal if alignment.horizontal else None,
                    'vertical': alignment.vertical if alignment.vertical else None
                }
            }
            row_styles.append(cell_style)
            print(f"轉(zhuǎn)換后的單元格樣式：{cell_style}")
 
        data.append(row_data)
        cell_styles.append(row_styles)      
 
    return data, merges, cell_styles
 
# 該處默認(rèn)只有同一行合并多列的情況。如果合并單元格占了兩行，需要另外的處理。
def generate_html_table(data, merges, cell_styles):
    print(f"合并單元格的信息：{merges}")
    html = "<table style='border-collapse: collapse;'>\n"
    for row_idx, row in enumerate(data):
        print("-"*20)
        print(f"當(dāng)前行的數(shù)據(jù)：{row}")
        html += "<tr>\n"
        # 設(shè)置一個(gè)跳過非首個(gè)合并單元格的標(biāo)記
        skip_next_cell = 0
        for col_idx,cell in enumerate(row):
            if skip_next_cell > 0:
                skip_next_cell -= 1
                continue
            # 行號(hào)、列號(hào)從0開始
            print(f"當(dāng)前單元格的值：{cell},行號(hào)：{row_idx},列號(hào)：{col_idx}")
            # 如果當(dāng)前單元格為1行4列，則修改cell值
            if row_idx == 1 and col_idx == 4:
                # 獲取今天的日期
                today = datetime.today()
                cell = formatted_date_no_leading_zeros = "截止 " + today.strftime("%-m 月 %-d 日")
                print(f"修改后的單元格值：{cell}")
            # 去除單元格樣式
            style = cell_styles[row_idx][col_idx]
            if style:                
                font_style = f"font-family:{style['font']['name']}; font-size:{style['font']['size']}pt; " \
                            f"font-weight:{'bold' if style['font']['bold'] else 'normal'}; " \
                            f"font-style:{'italic' if style['font']['italic'] else 'normal'};"
                border_style = f"border-top:{style['border']['top']}; " \
                            f"border-left:{style['border']['left']}; " \
                            f"border-right:{style['border']['right']}; " \
                            f"border-bottom:{style['border']['bottom']};"
                alignment_style = f"text-align:{style['alignment']['horizontal']}; " \
                                f"vertical-align:{style['alignment']['vertical']};"
            
            if (row_idx, col_idx) in [(m[0], m[1]) for m in merges]:  # 檢查當(dāng)前單元格是否是合并單元格的起始單元格
                rowspan = [m[2] - m[0] + 1 for m in merges if m[0] == row_idx and m[1] == col_idx][0]
                colspan = [m[3] - m[1] + 1 for m in merges if m[0] == row_idx and m[1] == col_idx][0]
                if style:
                    html += f"<td style='{font_style} {border_style} {alignment_style}' rowspan={rowspan} colspan={colspan}>{cell}</td>"
                else:
                    html += f"<td rowspan={rowspan} colspan={colspan}>{cell}</td>"
                skip_next_cell = colspan - 1    # 跳過合并的列
            else:
                if style:
                    html += f"<td style='{font_style} {border_style} {alignment_style}' >{cell}</td>"
                else:
                    html += f"<td>{cell}</td>"
            
        html += "</tr>\n"
    html += "</table>"
    html = "<!DOCTYPE html><html><head><meta charset='UTF-8'><title>Excel Table</title></head><body>" + html + "</body></html>"
    return html
 
def main():
    current_dir = 'reer'
    excel_file_path = current_dir + 'log/2re0207.xlsx'  # 替換為你的Excel文件路徑
    html_file_path = current_dir + 'log/output.html'
    screenshot_file_path = current_dir + 'log/table_screenshot.png'
 
    data, merges, cell_styles = read_excel(excel_file_path)
    html_table = generate_html_table(data, merges, cell_styles)
    with open(html_file_path, 'w', encoding='utf-8') as file:
        file.write(html_table)
    # 調(diào)用函數(shù)，替換以下參數(shù)
    url = html_file_path  # 網(wǎng)頁(yè)URL
    output_file = screenshot_file_path  # 輸出文件路徑
    table_selector = 'table'  # 表格的CSS選擇器，根據(jù)實(shí)際情況調(diào)整
    capture_table_screenshot(url, output_file, table_selector)
        
if __name__ == "__main__":
    main()