前几天,朋友找我帮他将一个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()
运行结果:
完美!