禅道自动化脚本python，selenium ，openpyxl

主要目的就是同步我本地的excel和禅道中任务和bug的数据，手动同步起来太麻烦了

代码：

from selenium import webdriver
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.common.by import By
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.common.keys import Keys
import csv
import threading
import time
from lxml import etree
from queue import Queue
import re,sys,os
import random

from openpyxl import load_workbook,Workbook
from openpyxl.styles import PatternFill, Border, Side, Color
from openpyxl.styles import Alignment
import json
import datetime

def jsonStandFormat(jsondata):
    return json.dumps(jsondata, sort_keys=True, indent=4, separators=(',', ':'), ensure_ascii=False)


class ChandaoSpider(object):
    def __init__(self, searchkw = ''):
        self.url = 'https://zt.xxxx.com' if 1 else 'http://ztt.xxxx.com:100'
        chrome_driver = r"D:\software\chrome-win64\chromedriver.exe" 
        path =  Service(chrome_driver)
        options = webdriver.ChromeOptions()
        #options.add_argument("headless")  # 启用无头模式
        self.driver = webdriver.Chrome(executable_path=chrome_driver, options=options)
        self.qtitle = Queue()
        self.qurl = Queue()
        self.searchname = '禅道'
        self.tasks = []
        self.bugs = []
        self.task_results = []
        self.bug_results = []
        
    def test_xpath(self, xxpath):
        print('debug xpath: %s' % xxpath)
        xs = xxpath.split('//')
        lenxs = len(xs)
        i = 1
        while lenxs - i > 0:
            xstmp = xs[:lenxs-i]
            xxpathtmp = '//'.join(xstmp)
            if xxpathtmp == '':
                continue
            try:
                element = self.driver.find_element(By.XPATH, xxpathtmp)
            except:
                print('[%s]%s' % (i, xxpathtmp))
            i = i + 1
        
    def clickitem(self, xxpath, value = ''):
        try:
            element = self.driver.find_element(By.XPATH, xxpath)
        except:
            self.test_xpath(xxpath)
            return False
            
        self.driver.execute_script("arguments[0].style.border='2px solid red';", element)
        time.sleep(random.randint(1, 2))
        
        self.driver.execute_script("arguments[0].style.border='1px solid green';", element)
        self.driver.execute_script("arguments[0].click();", element)
        time.sleep(random.randint(1, 2))
        
        if value:
            element.send_keys(value)
            time.sleep(random.randint(1, 2))
        

    # 获取每一页数据, 开趴.
    def get_project_list(self):
        print("[%s]获取最新任务..." % self.searchname)
        #print("[%s]进入首页..." % self.searchname)
        self.driver.get('%s/my.html' % self.url)
        time.sleep(random.randint(1, 2))
        
        #print("[%s]点击项目..." % self.searchname)
        #self.clickitem('//div[@id="menu"]//nav[@id="menuNav"]//ul//li[3]//a')
        self.driver.get('%s/project-browse-0-all-0-order_asc-16-2000-1.html' % self.url)
        time.sleep(random.randint(1, 2))
        
        #print("[%s]切换至iframe..." % self.searchname)
        iframe = self.driver.find_element_by_id("appIframe-project")
        self.driver.switch_to.frame(iframe)
        
        response = self.driver.page_source
        html = etree.HTML(response)
        ids = html.xpath('//form[@id="projectForm"]//table//tbody//tr//td[1]/text()')
        projects = html.xpath('//form[@id="projectForm"]//table//tbody//tr//td[2]')
        hrefs = html.xpath('//form[@id="projectForm"]//table//tbody//tr//td[2]//a')
        zhuangtais = html.xpath('//form[@id="projectForm"]//table//tbody//tr//td[3]/span/text()')
        for i, project in enumerate(projects):
            id = ids[i]
            url = hrefs[i].get('href')
            title = project.get("title")
            zhuangtai = zhuangtais[i].strip()
            if zhuangtai in ['已关闭']:
                continue
            #print(id, title, zhuangtai)
            self.get_tasks_list(id, title)
        #sys.exit(0)
            
    def get_tasks_list(self, id, title):
        myurl = '%s/execution-task-%s.html#app=project' % (self.url, int(id)+1)
        #print('[%s]处理任务: %s,%s,%s' % (self.searchname, id, title, myurl))
        self.driver.get(myurl)
        time.sleep(random.randint(1, 2))

        #print("[%s]切换至iframe..." % self.searchname)
        iframe = self.driver.find_element_by_id("appIframe-project")
        self.driver.switch_to.frame(iframe)
        time.sleep(random.randint(1, 2))

        
        #print("[%s]切换到每页2000..." % self.searchname)
        response = self.driver.page_source
        html = etree.HTML(response)
        empty_str = html.xpath('//div[@id="mainContent"]//div[@class="main-col"]//div[@class="table-empty-tip"]')
        if empty_str:
            #print(empty_str)
            return False
        
        try:
            self.clickitem('//div[@id="mainContent"]//div[@class="main-col"]//div[@class="table-footer fixed-footer"]//ul[@class="pager"]//li[2]//div[@class="btn-group pager-size-menu dropup"]//button')
        except:
            self.clickitem('//div[@id="mainContent"]//div[@class="main-col"]//div[@class="table-footer"]//ul[@class="pager"]//li[2]//div[@class="btn-group pager-size-menu dropup"]//button')
        self.driver.execute_script("window.scrollTo(document.body.scrollWidth, 0);")
        self.clickitem('//ul[@class="pager"]//li[2]//ul[@class="dropdown-menu"]//li[last()]//a')
        
        #print("[%s]获取数据..." % self.searchname)
        response = self.driver.page_source
        html = etree.HTML(response)
        
        hrefs = html.xpath('//table[@id="taskList"]//tbody//tr//td[1]//a')
        titles = html.xpath('//table[@id="taskList"]//tbody//tr//td[2]')
        zhuangtais = html.xpath('//table[@id="taskList"]//tbody//tr//td[5]//span/text()')
        shijians = html.xpath('//table[@id="taskList"]//tbody//tr//td[7]')
        names = html.xpath('//table[@id="taskList"]//tbody//tr//td[4]//a//span/text()')
        
        for i,shijian in enumerate(shijians):
            shijians[i] = re.sub('<.*?>', '', etree.tostring(shijian).decode('utf-8'))
        
        hrefs = [i.get('href') for i in hrefs]
        titles = [i.get('title').replace(',', '，') for i in titles]
        shijians = ['2025-%s' % i.split(' ')[0] if i else '' for i in shijians]
        ids  = [i.split('.')[0].split('-')[2] for i in hrefs]
        names = [i.strip() for i in names]
        
        ids.reverse()
        hrefs.reverse()
        titles.reverse()
        zhuangtais.reverse()
        shijians.reverse()
        names.reverse()
        
        exists_task_ids = [int(i['任务编号']) if i['任务编号'] !='' else 0 for i in self.task_results]
        for k,id in enumerate(ids):
            if not names[k] in ['马明', '宋文', '徐小波', '王伟', '李伟', '董卓', '孔子']:
                continue
            #self.tasks.append(ids[k])
            if int(ids[k]) in exists_task_ids:
                continue
            print('[append]%s,%s,%s,%s,%s,%s' % (title, ids[k], titles[k], names[k], zhuangtais[k], shijians[k]))
        if ids:
            pass#print()

    # 获取每一页数据, 开趴.
    def get_bugs_list(self):
        print("[%s]获取最新bug..." % self.searchname)
        #print("[%s]进入首页..." % self.searchname)
        self.driver.get('%s/my.html' % self.url)
        time.sleep(random.randint(1, 2))
        
        #print("[%s]点击测试..." % self.searchname)
        self.clickitem('//div[@id="menu"]//nav[@id="menuNav"]//ul//li[5]//a')
        
        #print("[%s]切换至iframe..." % self.searchname)
        iframe = self.driver.find_element_by_id("appIframe-qa")
        self.driver.switch_to.frame(iframe)
        
        #print("[%s]点击bug..." % self.searchname)
        #self.driver.switch_to.default_content() # 切换回主文档
        self.clickitem('//nav[@id="navbar"]//ul//li[3]//a')
        
        #print("[%s]切换到v3项目..." % self.searchname)
        self.clickitem('//div[@id="heading"]//div[@id="swapper"]//button[@id="currentItem"]')
        self.clickitem('//div[@id="other"]//li//a[@title="六度V3"]')
        
        #print("[%s]切换到每页2000..." % self.searchname)
        try:
            self.clickitem('//div[@id="mainContent"]//div[@class="main-col"]//div[@class="table-footer fixed-footer"]//ul[@class="pager"]//li[2]//div[@class="btn-group pager-size-menu dropup"]//button')
        except:
            self.clickitem('//div[@id="mainContent"]//div[@class="main-col"]//div[@class="table-footer"]//ul[@class="pager"]//li[2]//div[@class="btn-group pager-size-menu dropup"]//button')
        self.driver.execute_script("window.scrollTo(document.body.scrollWidth, 0);")
        self.clickitem('//ul[@class="pager"]//li[2]//ul[@class="dropdown-menu"]//li[last()]//a')
        
        #print("[%s]获取数据..." % self.searchname)
        response = self.driver.page_source
        html = etree.HTML(response)
        
        hrefs = html.xpath('//table[@id="bugList"]//tbody//tr//td[1]//a')
        titles = html.xpath('//table[@id="bugList"]//tbody//tr//td[2]')
        zhuangtais = html.xpath('//table[@id="bugList"]//tbody//tr//td[5]//span/text()')
        shijians = html.xpath('//table[@id="bugList"]//tbody//tr//td[7]/text()')
        querens = html.xpath('//table[@id="bugList"]//tbody//tr//td[8]//span/text()')
        names = html.xpath('//table[@id="bugList"]//tbody//tr//td[9]//a//span/text()')
        
        hrefs = [i.get('href') for i in hrefs]
        titles = [i.get('title').replace(',', '，') for i in titles]
        shijians = ['2025-%s' % i.split(' ')[0] for i in shijians]
        ids  = [i.split('.')[0].split('-')[2] for i in hrefs]
        names = [i.strip() for i in names]
        
        '''
        print(ids)
        print(hrefs)
        print(titles)
        print(zhuangtais)
        print(shijians)
        print(querens)
        print(names)
        
        print(len(ids))
        print(len(hrefs))
        print(len(titles))
        print(len(zhuangtais))
        print(len(shijians))
        print(len(querens))
        print(len(names))
        '''
        
        ids.reverse()
        hrefs.reverse()
        titles.reverse()
        zhuangtais.reverse()
        shijians.reverse()
        querens.reverse()
        names.reverse()
        
        exists_bug_ids = [int(i['bug编号']) if i['bug编号'] !='' else 0 for i in self.bug_results]
        for k,id in enumerate(ids):
            if not names[k] in ['马明', '宋文', '徐小波', '王伟', '李伟', '董卓', '孔子']:
                continue
            #self.bugs.append(ids[k])
            if int(ids[k]) in exists_bug_ids:
                continue
            print('[append]20250321版本,%s,%s,%s,%s,%s,%s' % (ids[k], titles[k], names[k], querens[k], shijians[k], shijians[k]))
        if ids:
            pass#print()
            
    def get_task_list_status(self, bugs = ''):
        print("[%s]获取现有任务状态..." % self.searchname)
        #print("[%s]进入首页..." % self.searchname)
        self.driver.get('%s/my.html' % self.url)
        time.sleep(random.randint(1, 2))
        
        bugs = '''6795''' if bugs == '' else bugs
        bugids = [i.strip() for i in bugs.split('\n') if i.strip()]
        for bugid in bugids:
            self.get_task_detail(bugid)
        #self.driver.quit()
        if bugids:
            pass#print()

    # 获取详情页
    def get_task_detail(self, bugid):
        #print("[%s]进入bug详情页..." % self.searchname)
        self.driver.get('%s/task-view-%s.html' % (self.url, bugid))
        time.sleep(random.randint(1, 2))
        
        iframe = self.driver.find_element_by_id("appIframe-execution")
        self.driver.switch_to.frame(iframe)
        
        #print("[%s]获取数据..." % self.searchname)
        response = self.driver.page_source
        html = etree.HTML(response)
        
        mybugid = html.xpath('//div[@id="mainMenu"]//div[@class="page-title"]//span[1]/text()')
        mybugid = mybugid[0].strip()
        
        mytitle = html.xpath('//div[@id="mainMenu"]//div[@class="page-title"]//span[2]/text()')
        mytitle = mytitle[0].strip()
        
        fuzeren = html.xpath('//div[@id="legendBasic"]//table//tbody//tr[3]//td/text()')
        fuzeren = fuzeren[0].strip().split(' ')[0]

        taskstatus = html.xpath('//div[@id="legendBasic"]//table//tbody//tr[5]//td[1]//span/text()')
        taskstatus = taskstatus[0].strip()
        gongshi = html.xpath('//div[@id="legendEffort"]//table//tbody//tr[3]//td[1]/text()')
        gongshi = gongshi[0].strip().replace('工时', '')
        yujikaishi = html.xpath('//div[@id="legendEffort"]//table//tbody//tr[4]//td[1]/text()')
        yujikaishi = yujikaishi[0].strip() if len(yujikaishi) > 0 else ''
        shijijikaishi = html.xpath('//div[@id="legendEffort"]//table//tbody//tr[5]//td[1]/text()')
        shijijikaishi = shijijikaishi[0].strip().split(' ')[0]
        jiezhi = html.xpath('//div[@id="legendEffort"]//table//tbody//tr[6]//td[1]/text()')
        jiezhi = jiezhi[0].strip()
        
        '''
        print(mybugid, mytitle, fuzeren, taskstatus)
        print(gongshi, yujikaishi, shijijikaishi, jiezhi)
        '''
        
        task_detail = [i for i in self.task_results if i['任务编号']!='' and int(i['任务编号']) == int(mybugid)]
        if task_detail:
            task_detail = task_detail[0]
            if mytitle!=task_detail['标题'] or fuzeren!=task_detail['指派给'] or taskstatus!=task_detail['状态']:
                print('[update]%s,%s,%s,%s,%s,%s' % (mybugid, mytitle, fuzeren, taskstatus, gongshi, jiezhi))
        else:
            print('[add]%s,%s,%s,%s,%s,%s' % (mybugid, mytitle, fuzeren, taskstatus, gongshi, jiezhi))
        
    # 获取每一页数据, 开趴.
    def get_bugs_list_status(self, bugs = ''):
        print("[%s]获取现有bug状态..." % self.searchname)
        #print("[%s]进入首页..." % self.searchname)
        self.driver.get('%s/my.html' % self.url)
        time.sleep(random.randint(1, 2))
        
        bugs = '''56345''' if bugs == '' else bugs
        bugids = [i.strip() for i in bugs.split('\n') if i.strip()]
        for bugid in bugids:
            self.get_bug_detail(bugid)
        #self.driver.quit()
        if bugids:
            pass#print()

    # 获取详情页
    def get_bug_detail(self, bugid):
        #print("[%s]进入bug详情页..." % self.searchname)
        self.driver.get('%s/bug-view-%s.html' % (self.url, bugid))
        time.sleep(random.randint(1, 2))
        
        iframe = self.driver.find_element_by_id("appIframe-qa")
        self.driver.switch_to.frame(iframe)
        
        #print("[%s]获取数据..." % self.searchname)
        response = self.driver.page_source
        html = etree.HTML(response)
        bugstatus = html.xpath('//div[@id="legendBasicInfo"]//table//tbody//tr[9]//td//span/text()')
        bugstatus = bugstatus[0].strip()
        
        querenstatus = html.xpath('//div[@id="legendBasicInfo"]//table//tbody//tr[12]//td/text()')
        querenstatus = querenstatus[0].strip()
        
        fuzerentext = html.xpath('//div[@id="legendBasicInfo"]//table//tbody//tr[13]//td/text()')
        fuzeren = fuzerentext[0].strip().split(' ')[0]
        zhipaishijian = fuzerentext[0].strip().split(' ')[2]
        
        banben = html.xpath('//div[@id="legendLife"]//table//tbody//tr[2]//td/text()')
        banben = banben[0].strip()
        
        '''
        self.driver.execute_script("window.scrollTo(document.body.scrollWidth, 0);")
        time.sleep(random.randint(1, 2))
        
        self.driver.find_element(By.XPATH, '//div[@id="mainContent"]//div[@class="side-col"]//ul[@class="nav-tabs"]//li[2]//a').click()
        time.sleep(random.randint(1, 2))
        
        xiangmu = html.xpath('//div[@id="legendExecStoryTask"]//table//tbody//tr[1]//td//a/text()')
        xiangmu = xiangmu[0].strip()
        '''
        
        bug_detail = [i for i in self.bug_results if i['bug编号']!= '' and int(i['bug编号']) == int(bugid)]
        if bug_detail:
            bug_detail = bug_detail[0]
            if bugstatus!=bug_detail['状态'] and querenstatus!=bug_detail['状态']:
                print('[update]%s,%s,%s,%s,%s,%s' % (bugid, fuzeren, bugstatus, querenstatus, zhipaishijian, banben))
        else:
            print('[add]%s,%s,%s,%s,%s,%s' % (bugid, fuzeren, bugstatus, querenstatus, zhipaishijian, banben))
        
    def login(self):
        print("[%s]进入登录页..." % self.searchname)
        self.driver.get('%s/user-login.html' % self.url)
        time.sleep(random.randint(1, 2))
        
        print("[%s]输入用户名和密码..." % self.searchname)
        self.clickitem('//input[@id="account"]', 'xxx')
        self.clickitem('//input[@name="password"]', 'yyyyyyy')
        self.clickitem('//button[@id="submit"]')
        
        '''
        #print("[%s]进入首页..." % self.searchname)
        self.driver.get('%s/my.html' % self.url)
        time.sleep(random.randint(1, 2))
        '''
        
        print("[%s]点击关闭提示..." % self.searchname)
        self.clickitem('//div[@class="version-detail"]//button[@class="close"]')

    def run(self):
        self.get_project_list()
        self.get_bugs_list()
        
        tasks = '\n'.join([str(i['任务编号']) for i in self.task_results if i['状态']!='已完成'])
        self.get_task_list_status(tasks)
        
        bugs = '\n'.join([str(i['bug编号']) for i in self.bug_results if i['状态']!='已关闭'])
        self.get_bugs_list_status(bugs)
        
        print()
        #self.driver.quit()
        
    def load_excel(self):
        print()
        print("[%s]获取excel数据..." % self.searchname)
        filename = 'D:/python组工作进度.xlsx'
        wb = load_workbook(filename=filename)
        
        #从excel读取原数据
        sheet = wb['任务']
        max_rows = sheet.max_row
        task_results = []
        titles = []
        valuess = []
        for rownum in range(1, max_rows+1):
            row_values = sheet[rownum]  # 第一行的所有值（注意：行号从1开始）
            values = []
            for field in row_values:
                if rownum == 1:
                    titles.append(field.value)
                else:
                    values.append(field.value if field.value else '')
                    
            if check_values(values):
                valuess.append(values)
                
        for values in valuess:
            task_results.append(dict(zip(titles, values)))
            
        for i in task_results:
            i['截止时间'] = str(i['截止时间']).split(' ')[0]
            i['最后更新时间'] = str(i['最后更新时间']).split(' ')[0]
        
        #从excel读取原数据
        sheet = wb['bug']
        max_rows = sheet.max_row
        bug_results = []
        titles = []
        valuess = []
        for rownum in range(1, max_rows+1):
            row_values = sheet[rownum]  # 第一行的所有值（注意：行号从1开始）
            values = []
            for field in row_values:
                if rownum == 1:
                    titles.append(field.value)
                else:
                    values.append(field.value if field.value else '')
                    
            if check_values(values):
                valuess.append(values)
                
        for values in valuess:
            bug_results.append(dict(zip(titles, values)))
            
        for i in bug_results:
            i['指派时间'] = str(i['指派时间']).split(' ')[0]
            i['最后更新时间'] = str(i['最后更新时间']).split(' ')[0]
            

        self.task_results = task_results
        self.bug_results = bug_results
        
def check_values(values):
    for i in values:
        if i != '':
            return True
    return False


if __name__ == '__main__':
    zhuce = ChandaoSpider()
    zhuce.login()
    while True:
        zhuce.load_excel()
        zhuce.run()

　　

效果：

[禅道]进入登录页...
[禅道]输入用户名和密码...
[禅道]点击关闭提示...

[禅道]获取excel数据...
[禅道]获取最新任务...
[禅道]获取最新bug...
[append]20250620版本,123450,Android端正式：小秘书内的文件，转发给用户，安卓端接收不到,王伟,未确认,2025-05-23,2025-05-23
[禅道]获取现有任务状态...
[update]7298,消息修改,王伟,已完成,0,2025-05-28
[update]7299,群公告以及待办相关逻辑的处理,王伟,已完成,0,2025-05-30
[update]7300,im客户端需求适配,王伟,已完成,0,2025-06-09
[禅道]获取现有bug状态...


[禅道]获取excel数据...
[禅道]获取最新任务...
[禅道]获取最新bug...
[禅道]获取现有任务状态...
[update]7298,消息修改,王伟,已完成,0,2025-05-28
[update]7299,群公告以及待办相关逻辑的处理,王伟,已完成,0,2025-05-30
[update]7300,im客户端需求适配,王伟,已完成,0,2025-06-09
[禅道]获取现有bug状态...


[禅道]获取excel数据...
[禅道]获取最新任务...
[禅道]获取最新bug...
[禅道]获取现有任务状态...
[禅道]获取现有bug状态...