[Python] 纯文本查看 复制代码
import pandas as pd
import re
import win32com.client as win32
from win32com.client import constants
import os
import os.path as osp
#读取表格
path=os.getcwd()
fp=osp.join(path,'fuck_demo.xlsx')
df=pd.read_excel(fp,header=1,usecols=['序号','光缆段落名（A端-B端）'],nrows=72)#别忘了nrows
df.set_index('序号',inplace=True)#设置索引
#筛选数据
df_1=df[~df['光缆段落名（A端-B端）'].str.contains('JT01')]
df_2=df[df['光缆段落名（A端-B端）'].str.contains('JT01')]
#操作引用df_2数据
reg=re.compile(r'(?<=\（)(.*?)(?=\）)')
pf=df_2.copy()
pf['提取A-B端']=pf['光缆段落名（A端-B端）'].str.extract(reg,expand=False)
pf['光缆芯数']=pf['光缆段落名（A端-B端）'].str.extract(r'(?:.*)(-\d+)',expand=False)
pf=pf.iloc[::-1]#倒序,关键
pf['光缆芯数'] = pf['光缆芯数'].replace('-', '', regex=True)#替换
#处理数据
ls=[]
for idx,row in pf.iterrows():
strs_2=row['光缆段落名（A端-B端）']
strs_3=row['提取A-B端']
strs_4=row['光缆芯数']
if strs_3 in strs_2:
ls.append([idx,strs_3,strs_4])
lg=[]#存放当前项与前一项相等,且与后一项不等
lf=[]#存放前后两项不等的
for i in range(len(ls)):
if ls[i][1]==ls[i-1][1] and ls[i][1]!=ls[i+1][1]:
lt=ls[i-1:i+2]
l1=[]#存放数字
l2=[]#存放A-B端
l3=[]#存放光缆芯数
for j in range(len(lt)):
l1.append(lt[j][0])
l2.append(lt[j][1])
l3.append(lt[j][-1])
new_l1=[min(l1),max(l1)]#取最小最大
new_l1=map(str,new_l1)#列表元素映射为字符串
new_l2=list(set(l2))+list(set(l3))
res_1='-'.join(new_l1)#序号拼接
res_2='-'.join(new_l2)#AB端光缆芯数拼接
res_3=list((res_1,res_2))#转为列表
lg.append(res_3)
else:
l1=ls[i][0]#存放数字
l2=ls[i][1]#存放A-B端
l3=ls[i][-1]#存放光缆芯数
res_4=list((l1,l2,l3))
lf.append(res_4)
xh=[]#序号
ab=[]#ab端
xs=[]#芯数
for k in range(len(lf)):
l1=lf[k][0]#存放数字
l2=lf[k][1]#存放A-B端
l3=lf[k][-1]#存放光缆芯数
xh.append(l1)
ab.append(l2)
xs.append(l3)
#重新两两分组
xh=[xh[x:x+2] for x in range(0,len(xh),2)]
ab=[ab[x:x+2] for x in range(0,len(ab),2)]
xs=[xs[x:x+2] for x in range(0,len(xs),2)]
lm=[]
for n in range(len(xh)):
l1=list((min(xh[n]),max(xh[n])))
new_l1=map(str,l1)
l2=list(set(xs[n]))
l3=ab[n]+l2
res_1='-'.join(new_l1)
res_2='-'.join(l3)
lm.append([res_1,res_2])
finall_list=lg+lm
#去重
finall_ls=[]
for l in finall_list:
if l not in finall_ls:
finall_ls.append(l)
#根据结果列表重新创建DataFrame
df_3=pd.DataFrame(data=finall_ls,columns=['序号','光缆段落名（A端-B端）'])
df_3.set_index('序号',drop=True,inplace=True)#设置序号列为索引
#把没有JT的光缆段落和经过处理的有JT的段落进行拼接
cf=pd.concat([df_1,df_3],axis=0)
'''
重新替换索引也行，不替换也行。反正行号是在序号的基础上+2
'''
#pywin32读取excel表格
#运行excel程序
try:
excel_app = win32.gencache.EnsureDispatch('Excel.Application')
except:
try:
win32.gencache.EnsureDispatch('et.Application')
except:
win32.gencache.EnsureDispatch('ket.Application')
finally:
excel_app.Visible = True
excel_app.DisplayAlerts = False
#读取表格所需列
workbook = excel_app.Workbooks.Open(fp)
worksheet = workbook.Worksheets('清单')
worksheet.Columns(3).Insert()
worksheet.Columns(4).Insert()
worksheet.Cells(2, 3).Value = '图纸编号'#设置列名
worksheet.Cells(2, 4).Value = '段落名称'#设置列名.
#操作表格
for idx,row in cf.iterrows():
dl=row['光缆段落名（A端-B端）']
bh=idx
if '-' in str(bh):
sb=bh.split('-')
start_row=int(min(sb))+2#开始行号
end_row=int(max(sb))+2#结束行号
range1_to_merge=worksheet.Range(worksheet.Cells(start_row, 3), worksheet.Cells(end_row, 3))#要合并的单元格的“图纸编号”
range2_to_merge=worksheet.Range(worksheet.Cells(start_row, 4), worksheet.Cells(end_row, 4))#要合并单元格的“段落名称”
range3_to_color=worksheet.Range(worksheet.Cells(start_row, 5), worksheet.Cells(end_row, 5))#要设置颜色的“光缆段落名(A端-B端)”
range4_to_color=worksheet.Range(worksheet.Cells(start_row, 17), worksheet.Cells(end_row, 17))#要设置颜色的“委托单号+需求人”
range2_to_merge.WrapText = True#自动换行
range3_to_color.Interior.Color =7396243 # 浅红色填充色804040
range4_to_color.Interior.Color =7396243
#range3_to_merge.Font.Color = 25500 # 深红色文本
range1_to_merge.Value=bh#图纸编号
range2_to_merge.Value=dl#光缆段落
range1_to_merge.Merge()
range2_to_merge.Merge()
range2_to_merge.Rows.RowHeight=36
range2_to_merge.Columns.ColumnWidth = 34
else:
worksheet.Cells(idx+2,3).Value = idx
worksheet.Cells(idx+2,4).Value = dl
worksheet.Cells(idx+2,4).WrapText = True#自动换行
#结束操作保存表格
workbook.Save()
workbook.Close(SaveChanges=False)
excel_app.Quit()
发表于 2024-3-20 22:17
|
发表于 2024-3-21 08:38
