import os
from win32com import client

# 打开工作薄   *** wps用ket.Application;Microsoft excel用Excel.Application ***
excel = client.Dispatch("ket.Application")
excel.Visible = False
# 打开word   *** wps用kwps.Application;Microsoft word用Word.Application ***
doc_app = client.Dispatch("kwps.Application")
doc_app.Visible = False


# 获取粘贴的位置，并修改标题
def get_location(targetword, location_No1, location_No11,title1,title11):
    doc = doc_app.Documents.Open(targetword)
    ps = doc.Paragraphs
    tarlen = ps.__len__()
    location = 0
    s = doc_app.Selection
    for i in range(1, tarlen + 1):
        r = ps(i).Range
        start = r.Start
        if r.ListFormat.ListString == location_No1:
            s.SetRange(start, start)
            for d in range(4):
                s.Delete()
            s.TypeText(title1)
        if r.ListFormat.ListString == location_No11:
            s.SetRange(start, start)
            for dd in range(4):
                s.Delete()
            s.TypeText(title11)
            location = i
            break
    doc.Save()
    doc.Close()
    return location


# 粘贴复制的excel 表格
def insert_word(targetword, sheet, title_location):
    # 打开word
    doc = doc_app.Documents.Open(targetword)
    ps = doc.Paragraphs
    ps(title_location).Range.InsertAfter(f"{sheet.Name}\n\n")
    doc.Save()
    sheet.Range(sheet.Cells(1, 1),
                sheet.Cells(sheet.UsedRange.Rows.__len__(),
                            sheet.UsedRange.Columns.__len__())).Copy()
    ps(title_location + 2).Range.PasteExcelTable(False, False, True)
    doc.Save()
    doc.Close()

# 获取需要复制表格的所有文件
def get_input_files(input_path):
    full_path = []
    for current_path, dirs, files in os.walk(input_path):
        for file_name in files:
            full_path.append(os.path.join(root, current_path, file_name))
    return full_path


if __name__ == '__main__':
    root = os.getcwd()
    target_file = r'C:\Users\meiya\PycharmProjects\TransferWebTestCase\简单版需求文档.docx'
    input_path = r'excel_files'
    files = get_input_files(input_path)
    count = 0
    location_start = '1.2'
    head = location_start.split('.')[0]
    tail = location_start.split('.')[1]
    for excel_file in files:
        file_name = os.path.basename(excel_file).split('.')[0]
        title1 = file_name.split('-')[0]
        title11 = file_name.split('-')[1]
        wb = excel.Workbooks.Open(excel_file)
        tail = str(int(tail) + count)
        location_No1 = head+'.'+tail
        location_No11 = location_No1+'.1'
        location = get_location(target_file, location_No1, location_No11,title1,title11)
        for x in range(len(wb.Worksheets),0,-1):
            sheet = wb.Worksheets[x]
            insert_word(target_file, sheet, location)
        wb.Close()
        count += 1