push_to_faq_pull_to_excel 自己亲自做的 推送excel 到faq 网站, 再从faq网站拉取新的数据到 excel 有大用 有大大用 有大大大用

import xlwings as xw

import requests

import json

import time

import psutil

import sys

import ctypes

import tkinter as tk

from tkinter import messagebox

from itertools import zip_longest

def is_excel_running():

    for proc in psutil.process_iter(['name']):

        if proc.info['name'] == 'EXCEL.EXE':

            return True

    return False

def kill_excel():

    for proc in psutil.process_iter(['name', 'pid']):

        if proc.info['name'] == 'EXCEL.EXE':

            proc.kill()

def show_message(message):

    # 创建主窗口

    root = tk.Tk()

    root.title("提示")

    # 创建一个标签来显示消息

    label = tk.Label(root, text=message, padx=20, pady=20)

    label.pack()

    # 创建一个函数，在 3 秒后关闭窗口

    def close_window():

        root.destroy()

    # 设置 3000 毫秒后调用 close_window 函数

    root.after(3000, close_window)

    # 启动 tkinter 的 mainloop

    root.mainloop()

show_message('程序处理excel开始!请等待!')

is_excel_running = is_excel_running()

if is_excel_running:

    kill_excel()

time.sleep(5)  #暂停5秒钟

path = 'C:\\SCRIPTS\\更改仲裁主管表信息.xlsx'

app = xw.App(visible=False, add_book=False)

app.display_alerts = False

app.screen_updating = False

try:

    workbook = app.books.open(path)  # 替换为你的 Excel 文件路径

    sheet = workbook.sheets['更改仲裁主管']

    data_A = sheet.range('A2').expand('down').value

    data_A_list = [str(item).split('.')[0] if isinstance(item, float) else str(item) for item in data_A]

    data_B = sheet.range('B2').expand('down').value

    data_B_list = [str(item).split('.')[0] if isinstance(item, float) else str(item) for item in data_B]

    data_C_list = sheet.range('C2').expand('down').value

    data_D_list = sheet.range('D2').expand('down').value

    data_E_list = sheet.range('E2').expand('down').value

    data_F_list = sheet.range('F2').expand('down').value

    data_G_list = sheet.range('G2').expand('down').value

    data_H_list = sheet.range('H2').expand('down').value

    data_I_list = sheet.range('I2').expand('down').value

    data_J_list = sheet.range('J2').expand('down').value

    # print(type(data_A_list))

    # print(data_A_list)

    workbook.close()  # 如果你想在 Excel 中保留文件，请设置 save=False

    app.quit()  # 退出 Excel 应用

    time.sleep(5)  #暂停5秒钟

    combined_list = [

        {'field_person_gh': field_person_gh, 'field_manager_gh': field_manager_gh, 'field_change_status': field_change_status,

         'field_person_name': field_person_name, 'field_manager_name': field_manager_name,'field_operation_time':str(field_operation_time),

         'field_person_email': field_person_email, 'field_manager_email': field_manager_email, 'field_person_status': field_person_status,

         'field_manager_status': field_manager_status}

        for field_person_gh, field_manager_gh, field_change_status,field_change_status,field_person_name,

            field_manager_name,field_operation_time,field_person_email,field_manager_email,field_person_status,

            field_manager_status

        in zip_longest(data_A_list, data_B_list, data_C_list, data_C_list, data_D_list, data_E_list, data_F_list, data_G_list, data_H_list, data_I_list, data_J_list)

    ]

    # 创建一个字典用于存储每个 field_person_gh 的最新记录

    last_combined_list = {}

    # 遍历 A 列表

    for item in combined_list:

        gh = item['field_person_gh']

        last_combined_list[gh] = item  # 更新字典中的值，始终保留最新的记录

    # 将字典中的值转换回列表 此时就不会含有相同的 field_person_gh

    combined_list = list(last_combined_list.values())

    # print(combined_list)

    url = "http://aaa.bbbb-ict.com/push_2_20_to_faq_pull_to_excel"

    response = requests.post(url, json=combined_list)

    # 打印响应状态码

    print(f'Status Code: {response.status_code}')

    # 打印响应内容

    # print('Response Body:')

    # print(response.text)

    if(response.text == '[]'):

        print("not ok")

        sys.exit(0)

    # print("AAAAA")

    # print(response.text)

    json_data = json.loads(response.text)

    # print(type(json_data))

    # print("BBBBB")

    # print(json_data)

    # print("BBBBB")

    # 移除 combined_list 列表中在 json_data 列表中存在的 field_person_gh

    new_combined_list = [item for item in combined_list if item['field_person_gh'] not in {b['field_person_gh'] for b in json_data}]

    # print("CCCCC")

    # print(new_combined_list)

    # print("DDDDD")

    new_combined_list = new_combined_list + json_data

    combined_list = new_combined_list

    # data_A_set = set(data_A_list)  # 其实就是excel中的个人帐号列表

    # json_data_new = [item for item in json_data if item["field_person_gh"] not in data_A_set]

    # json_data = json_data_new

    # print(json_data_new)

    # 准备存放 B 列表中的 `field_person_gh` 和 `field_manager_gh`

    # old_person_manager_pairs = {(item['field_person_gh'], item['field_manager_gh']) for item in combined_list}

    #

    # json_data_new = []

    # # 迭代 json_data 列表，检查每个元素的 `field_person_gh` 和 `field_manager_gh` 是否在 B 列表中

    # for item in json_data:

    #     current_pair = (item['field_person_gh'], item['field_manager_gh'])

    #     if current_pair not in old_person_manager_pairs:

    #         json_data_new.append(item)

    # json_data = json_data_new

    # 将 JSON 数据转换为列表

    data = [

        [

            item["field_person_gh"],

            item["field_manager_gh"],

            item["field_change_status"],

            item["field_person_name"],

            item["field_manager_name"],

            item["field_operation_time"],

            item["field_person_email"],

            item["field_manager_email"],

            item["field_person_status"],

            item["field_manager_status"]

        ]

        for item in combined_list

    ]

    app = xw.App(visible=False, add_book=False)

    app.display_alerts = False

    app.screen_updating = False

    # 打开 Excel 文件

    workbook = app.books.open(path)  # 替换为你的 Excel 文件路径

    sheet = workbook.sheets['更改仲裁主管']

    #这是把A2,B2...J2以下的数据全部清除

    sheet.range(f"A2:A{sheet.cells.last_cell.row}").value = None

    sheet.range(f"B2:B{sheet.cells.last_cell.row}").value = None

    sheet.range(f"C2:C{sheet.cells.last_cell.row}").value = None

    sheet.range(f"D2:D{sheet.cells.last_cell.row}").value = None

    sheet.range(f"E2:E{sheet.cells.last_cell.row}").value = None

    sheet.range(f"F2:F{sheet.cells.last_cell.row}").value = None

    sheet.range(f"G2:G{sheet.cells.last_cell.row}").value = None

    sheet.range(f"H2:H{sheet.cells.last_cell.row}").value = None

    sheet.range(f"I2:I{sheet.cells.last_cell.row}").value = None

    sheet.range(f"J2:J{sheet.cells.last_cell.row}").value = None

    # print(data)

    # 将新的数据追加到工作表

    sheet.range(f'A2').value = data  # 从 2 行开始写入新数据

    workbook.save(path)

    workbook.close()  # 如果你想在 Excel 中保留文件，请设置 save=False

    app.quit()  # 退出 Excel 应用

except Exception as err:

    kill_excel()

    show_message('程序运行中不能打开此excel,可能需要重新运行程序!')

    time.sleep(3)  # 暂停3秒钟

show_message('程序处理excel结束!')