python将excel中一行表格分成多行

前几天，朋友找我帮他将一个excel处理一下。原文件中每个产品都只有一行，但其中Part Number部分可能含有多个值，也可能只有一个值，如果是多个值，就要将每个值单独分成一行。以前他都是手动分行，但此文件便有800多行，分行后估计得有三四千行。

网上找了些案例，几行代码即可搞定，但苦于自己水平有限，看不懂！！！

于是，那就从最原始的方法下手吧：读取 --> 写入。

文件格式如下：（可能包含商业信息，部分内容已打码）

 代码如下：

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 = ["PSA Number", "Project Sourcing Action Name", "Project Name", "PMT", "Part     Number", "PAF", "SCI Indicator", "Platform/Tophat", "Direct Buyer (CDSID)", "D&R Engineer (CDSID)"]    # result表格的首行内容
    for a in range(1, 10):
        wb_2.cell(row=1, column=a, value=name[a-1])    # 写入result的首行

    j = 2
    k = 0
    with open(file_path + "\\" + "result.xlsx") as f:
        for i in range(2, 817):
            # 读取原excel的每行内容
            pas_number = wb.cell(i, 1).value
            project_sour = wb.cell(i, 2).value
            project_name = wb.cell(i, 3).value
            pmt = wb.cell(i, 4).value
            part_number = wb.cell(i, 5).value
            paf = wb.cell(i, 6).value
            sci = wb.cell(i, 7).value
            platform = wb.cell(i, 8).value
            direct_buyer = wb.cell(i, 9).value
            d_and_r = wb.cell(i, 10).value

            # 将其余列的内容写入result中
            wb_2.cell(row=j, column=1, value=pas_number)
            wb_2.cell(row=j, column=2, value=project_sour)
            wb_2.cell(row=j, column=3, value=project_name)
            wb_2.cell(row=j, column=4, value=pmt)
            wb_2.cell(row=j, column=6, value=paf)
            wb_2.cell(row=j, column=7, value=sci)
            wb_2.cell(row=j, column=8, value=platform)
            wb_2.cell(row=j, column=9, value=direct_buyer)
            wb_2.cell(row=j, column=10, value=d_and_r)

            # 对part number中的内容进行识别
            if ";" in part_number:
                k = part_number.count(";")
                k = k + 1        # 数据量比“;”的个数多一个
                part_number_2 = []
                part_number_2.append(re.split(r"[;,s]s*", part_number.split("\n")[0]))
                for p in range(0, k):
                    wb_2.cell(row=j, column=5, value=str(part_number_2[0][p])) 
                    j = j + 1
            else:
                wb_2.cell(row=j, column=5, value=part_number)
                j = j + 1

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

if __name__ == "__main__":
    file_path = input("file_path:")
    raw_excel = input("excel_name:")   
    SplitLine()

运行结果：

完美！