python读取所有sheet内容到另一个文件中

 实现效果：

将原excel中的步骤、预期效果列按回车拆成多行数据，其余字段值填充其他数据

实现结果：

# This is a sample Python script.

# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.


# def print_hi(name):
#     # Use a breakpoint in the code line below to debug your script.
#     print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.
#
#
# # Press the green button in the gutter to run the script.
# if __name__ == '__main__':
#     print_hi('PyCharm')
#
# # See PyCharm help at https://www.jetbrains.com/help/pycharm/

import openpyxl, re


def SplitLine():
    book = openpyxl.load_workbook(file_path + "\\" + raw_excel)    # 读取原excel
    wb = book.active
    book_2 = openpyxl.load_workbook(file_path + "\\" + "result.xlsx")  # 读取新建的result
    wb_2 = book_2.active

    name = ["TCID", "Test Summary", "Test Priority", "Step", "Data", "Expected Result", "Test Repository Path",
            "Pre-condition", "TestCaseType", "Automation", "LinkType", "LinkIssue"]
    # name = ["所属模块（*）", "用例标题（*）", "前置条件", "步骤", "预期结果", "优先级"]  # result表格的首行内容
    for a in range(1, len(name) + 1):
        wb_2.cell(row=1, column=a, value=name[a - 1])  # 写入result的首行

    j = 2 #新建excel的行数
    k = 0 #回车数量
    b = 2

    #遍历所有的sheet
    for sheet_name in book.sheetnames:
        print(book.sheetnames)
        sheet = book[sheet_name]
        a = sheet.cell(2,2).value
        print(a)
        print(sheet)


        print(sheet.max_row)
        with open(file_path + "\\" + "result.xlsx",encoding='utf-8') as f:
            for i in range(2, sheet.max_row+1):

                # 读取原excel的每行内容
                Test_Repository_Path = sheet.cell(i, 1).value


                try:
                    Test_Repository_Path = "/对外系统用例/"+sheet_name+"/"+Test_Repository_Path
                except:
                    print("继续执行")
                # print(wb.cell(1, 1).value)
                print(Test_Repository_Path)
                Test_Summary = sheet.cell(i, 2).value
                Pre_condition = sheet.cell(i, 3).value
                Data = sheet.cell(i, 4).value
                print(Data)
                Expected_Result = sheet.cell(i, 5).value


                # 将内容写入result中
                wb_2.cell(row=j, column=7, value=Test_Repository_Path)
                wb_2.cell(row=j, column=2, value=Test_Summary)
                wb_2.cell(row=j, column=3, value="Medium")
                wb_2.cell(row=j, column=8, value=Pre_condition)
                wb_2.cell(row=j, column=5, value=Data)
                wb_2.cell(row=j, column=6, value=Expected_Result)
                wb_2.cell(row=j, column=9, value="功能用例")
                wb_2.cell(row=j, column=10, value="否")
                wb_2.cell(row=j, column=1, value=b-1)
                wb_2.cell(row=j, column=4, value=1)


                try:
                    # 对Data中的内容进行识别
                    if '\n' in Data:
                        k = Data.count("\n")
                        k = k + 1  # 数据量比“;”的个数多一个
                        Data_2 = []
                        Expected_Result_2 = []
                        #循环所有的回车数量，分隔到每一行中
                        for p in range(0, k):
                            Data_2.append(re.split(r"[\n]\s*", Data.split("\n")[p]))
                            Expected_Result_2.append(re.split(r"[\n]\s*", Expected_Result.split("\n")[p]))

                            # print(Data_2)
                            # print(Data_2[p][0])
                            # # print(Data_2[0][p])
                            wb_2.cell(row=j, column=5, value=str(Data_2[p][0]))
                            wb_2.cell(row=j, column=6, value=str(Expected_Result_2[p][0]))
                            wb_2.cell(row=j, column=4, value=p+1)
                            wb_2.cell(row=j, column=1, value=b - 1)

                            # print(wb_2.cell(row=j, column=4).value)
                            j = j + 1
                    else:
                        wb_2.cell(row=j, column=5, value=Data)
                        wb_2.cell(row=j, column=6, value=Expected_Result)
                        wb_2.cell(row=j, column=1, value=b - 1)

                        j = j + 1

                except:
                    print("继续执行")
                b = b + 1



    book_2.save(file_path + "\\" + "result.xlsx")  # 保存excel



if __name__ == "__main__":
    file_path =r'C:\Users\12133\Desktop'
    raw_excel ='对外系统用例.xlsx'
    # file_path = input("file_path:")
    # raw_excel = input("excel_name:")
    SplitLine()