提取儿童版样式:
非儿童版样式:
生成标识卡表格:
卷标:
箱标:
代码:
import openpyxl
import os
import xlrd
import datetime
from openpyxl.styles import *
#卷标根据尺码,大于1000分卷;箱标与标识卡根据款号大于5300分卷
files = []
file_path = r'\\192.168.0.198\车间打印写码\原始数据\斐乐-绿雨' # excel所在文件夹
#设置边框{'medium' 中粗 'thin' 细 'thick' 粗 'dashed' 虚线 'dotted' 点线}
def format_border_cell(ws,row_index,col_index):
ws.cell(row_index,col_index).border = Border(top = Side(border_style='thin', color='FF000000'),
right = Side(border_style='thin', color='FF000000'),
bottom = Side(border_style='thin', color='FF000000'),
left = Side(border_style='thin', color='FF000000'))
#画边框
def draw_border(ws,row,col):
for i in range(1,row+1):
for j in range(1,col+1):
format_border_cell(ws,i,j)
#尺寸填充数组
def size_tc(str1,num,a):
if str1 == "XS":
a[1] = num
elif str1 == "S":
a[2] =num
elif str1 == "M":
a[3] =num
elif str1 == "L":
a[4] =num
elif str1 == "XL":
a[5] =num
elif str1 == "2XL":
a[6] =num
elif str1 == "3XL":
a[7] =num
elif str1 == "4XL":
a[8] =num
elif str1 == "5XL":
a[9] =num
def size_tc1(str1,num,a):
if str1 == "105":
a[1] = num
elif str1 == "110":
a[2] =num
elif str1 == "120":
a[3] =num
elif str1 == "130":
a[4] =num
elif str1 == "140":
a[5] =num
elif str1 == "150":
a[6] =num
elif str1 == "160":
a[7] =num
elif str1 == "165":
a[8] =num
elif str1 == "170":
a[9] =num
elif str1 == "175":
a[10] =num
elif str1 == "180":
a[11] =num
elif str1 == "185":
a[12] =num
for dirpath, dirnames, filenames in os.walk(file_path):
for file in filenames:
if file.endswith('.xlsx') or file.endswith('.xls'):
files.append(dirpath + '\\' + file)
break
def table1(file):
s1 = file.split('\\')
s3 = s1[s1.__len__() - 1]
s2 = s3[:s3.index(".xls")]
save_path = file_path + '\\' + s2
if not os.path.exists(save_path):
os.mkdir(save_path)
wb1 = xlrd.open_workbook(file)
table = wb1.sheets()[1]
row_max = table.nrows
################卷标##############################
WT1 = openpyxl.Workbook()
WE1 = WT1.active
WE1.column_dimensions['A'].width = 15
WE1.append(['款号/颜色', '尺码', '数量'])
juanbiao = []
for i in range(3, row_max):
if table.cell(i, 0).value == "":
break
b1000 = int(table.cell(i, 13).value) // 1000 + 1
for j in range(0, b1000):
juanbiao_xiao = []
if j != b1000 - 1:
juanbiao_xiao.append(table.cell(i, 0).value)
juanbiao_xiao.append(table.cell(i, 11).value)
juanbiao_xiao.append(1000)
juanbiao.append(juanbiao_xiao)
else:
juanbiao_xiao.append(table.cell(i, 0).value)
juanbiao_xiao.append(table.cell(i, 11).value)
juanbiao_xiao.append(int(table.cell(i, 13).value) % 1000)
juanbiao.append(juanbiao_xiao)
for k in juanbiao:
WE1.append(k)
draw_border(WE1, juanbiao.__len__() + 1, 3)
WT1.save(save_path + '/' + '卷标-' + s2 + '.xlsx')
################箱标##############################
WT2 = openpyxl.Workbook()
WE2 = WT2.active
name_str = ""
kuanhao = ""
for ch in table.cell(1, 1).value:
if u'\u4e00' <= ch <= u'\u9fff':
name_str += ch
else:
kuanhao += ch
WE2.append(['货号', 'XS', 'S', 'M', 'L', 'XL', '2XL', '3XL', '4XL', '5XL', '合计', '件数'])
WE2.column_dimensions['A'].width = 15
xiangbiao = []
for i in range(3, row_max):
if table.cell(i, 0).value == "":
break
if xiangbiao.__len__() == 0: # 第一次情况
xiangbiao_xiao = []
for j in range(13):
xiangbiao_xiao.append("")
xiangbiao_xiao[0] = table.cell(i, 1).value
size_tc(table.cell(i, 11).value, int(table.cell(i, 13).value), xiangbiao_xiao)
xiangbiao_xiao[12] = name_str
xiangbiao_xiao[11] = 1
xiangbiao.append(xiangbiao_xiao)
elif table.cell(i, 1).value != xiangbiao[xiangbiao.__len__() - 1][0]: # 当货号不相同的情况
xiangbiao_xiao = []
for j in range(13):
xiangbiao_xiao.append("")
xiangbiao_xiao[0] = table.cell(i, 1).value
size_tc(table.cell(i, 11).value, int(table.cell(i, 13).value), xiangbiao_xiao)
xiangbiao_xiao[12] = name_str
xiangbiao_xiao[11] = 1
xiangbiao.append(xiangbiao_xiao)
else: # 当相同货号的情况
sum = 0
for k in range(1, 10):
if xiangbiao[xiangbiao.__len__() - 1][k] != "":
sum += xiangbiao[xiangbiao.__len__() - 1][k]
if int(table.cell(i, 13).value) + sum < 5300:
size_tc(table.cell(i, 11).value, int(table.cell(i, 13).value), xiangbiao[xiangbiao.__len__() - 1])
else: # 当货号数量大于5300时
xiangbiao_xiao = []
for j in range(13):
xiangbiao_xiao.append("")
huohao = xiangbiao[xiangbiao.__len__() - 1][0] # 货号
jianshu = xiangbiao[xiangbiao.__len__() - 1][11] + 1 # 件数
xiangbiao_xiao[11] = jianshu
for m in range(xiangbiao.__len__() - 1, -1, -1):
if huohao != xiangbiao[m][0]:
break
xiangbiao[m][11] = jianshu
xiangbiao_xiao[0] = table.cell(i, 1).value
size_tc(table.cell(i, 11).value, int(table.cell(i, 13).value), xiangbiao_xiao)
xiangbiao_xiao[12] = name_str
xiangbiao.append(xiangbiao_xiao)
for k in xiangbiao:
sum1 = 0
for j in range(1, 10):
if k[j] != "":
sum1 += k[j]
k[10] = sum1
WE2.append(k)
draw_border(WE2, xiangbiao.__len__() + 1, 11)
WT2.save(save_path + '/' + '箱标-' + s2 + '.xlsx')
################标识卡##############################
WT3 = openpyxl.Workbook()
WE3 = WT3.active
WE3.append(['客户', '生产单号', '材料名称', '物料编码', '数量', '件数', '合同号', '备注', '生产日期'])
biaoshika = []
for temp in xiangbiao:
biaoshika_xiao = []
biaoshika_xiao.append(name_str)
biaoshika_xiao.append(kuanhao)
biaoshika_xiao.append("合格证")
biaoshika_xiao.append(temp[0])
biaoshika_xiao.append(temp[10])
biaoshika_xiao.append(temp[11])
biaoshika_xiao.append("")
biaoshika_xiao.append("02")
biaoshika_xiao.append("")
biaoshika.append(biaoshika_xiao)
for k in biaoshika:
WE3.append(k)
WT3.save(save_path + '/' + '标识卡-' + s2 + '.xlsx')
def table(file):
s1 = file.split('\\')
s3 = s1[s1.__len__() - 1]
s2 = s3[:s3.index(".xls")]
save_path = file_path + '\\' + s2
if not os.path.exists(save_path):
os.mkdir(save_path)
wb1 = xlrd.open_workbook(file)
table = wb1.sheets()[1]
row_max = table.nrows
################卷标##############################
WT1 = openpyxl.Workbook()
WE1 = WT1.active
WE1.column_dimensions['A'].width = 15
WE1.append(['款号/颜色', '尺码', '数量'])
juanbiao = []
for i in range(3, row_max):
if table.cell(i, 0).value == "":
break
b1000 = int(table.cell(i, 15).value) // 1000 + 1 #数量
for j in range(0, b1000):
juanbiao_xiao = []
if j != b1000 - 1:
juanbiao_xiao.append(table.cell(i, 0).value)
juanbiao_xiao.append(table.cell(i, 12).value) #尺寸
juanbiao_xiao.append(1000)
juanbiao.append(juanbiao_xiao)
else:
juanbiao_xiao.append(table.cell(i, 0).value)
juanbiao_xiao.append(table.cell(i, 12).value)
juanbiao_xiao.append(int(table.cell(i, 15).value) % 1000)
juanbiao.append(juanbiao_xiao)
for k in juanbiao:
WE1.append(k)
draw_border(WE1, juanbiao.__len__() + 1, 3)
WT1.save(save_path + '/' + '卷标-' + s2 + '.xlsx')
################箱标##############################
WT2 = openpyxl.Workbook()
WE2 = WT2.active
str5 = table.cell(1,1).value.split(' ')
name_str = ""
kuanhao = ""
for ch in str5[0]:
if u'\u4e00' <= ch <= u'\u9fff':
name_str += ch
else:
kuanhao += ch
WE2.append(['货号', '105', '110', '120', '130', '140', '150', '160', '165', '170', '175', '180', '185', '合计', '件数'])
WE2.column_dimensions['A'].width = 15
xiangbiao = []
for i in range(3, row_max):
if table.cell(i, 0).value == "":
break
if xiangbiao.__len__() == 0: # 第一次情况
xiangbiao_xiao = []
for j in range(16):
xiangbiao_xiao.append("")
xiangbiao_xiao[0] = table.cell(i, 2).value
size_tc1(table.cell(i, 12).value, int(table.cell(i, 15).value), xiangbiao_xiao)
xiangbiao_xiao[15] = name_str
xiangbiao_xiao[14] = 1
xiangbiao.append(xiangbiao_xiao)
elif table.cell(i, 2).value != xiangbiao[xiangbiao.__len__() - 1][0]: # 当货号不相同的情况
xiangbiao_xiao = []
for j in range(16):
xiangbiao_xiao.append("")
xiangbiao_xiao[0] = table.cell(i, 2).value
size_tc1(table.cell(i, 12).value, int(table.cell(i, 15).value), xiangbiao_xiao)
xiangbiao_xiao[15] = name_str
xiangbiao_xiao[14] = 1
xiangbiao.append(xiangbiao_xiao)
else: # 当相同货号的情况
sum = 0
for k in range(1, 13):
if xiangbiao[xiangbiao.__len__() - 1][k] != "":
sum += xiangbiao[xiangbiao.__len__() - 1][k]
if int(table.cell(i, 15).value) + sum < 5300:
size_tc1(table.cell(i, 12).value, int(table.cell(i, 15).value), xiangbiao[xiangbiao.__len__() - 1])
else: # 当货号数量大于5300时
xiangbiao_xiao = []
for j in range(16):
xiangbiao_xiao.append("")
huohao = xiangbiao[xiangbiao.__len__() - 1][0] # 货号
jianshu = xiangbiao[xiangbiao.__len__() - 1][14] + 1 # 件数
xiangbiao_xiao[14] = jianshu
for m in range(xiangbiao.__len__() - 1, -1, -1):
if huohao != xiangbiao[m][0]:
break
xiangbiao[m][14] = jianshu
xiangbiao_xiao[0] = table.cell(i, 2).value
size_tc1(table.cell(i, 12).value, int(table.cell(i, 15).value), xiangbiao_xiao)
xiangbiao_xiao[15] = name_str
xiangbiao.append(xiangbiao_xiao)
for k in xiangbiao:
sum1 = 0
for j in range(1, 13):
if k[j] != "":
sum1 += k[j]
k[13] = sum1
WE2.append(k)
draw_border(WE2, xiangbiao.__len__() + 1, 13)
WT2.save(save_path + '/' + '箱标-' + s2 + '.xlsx')
################标识卡##############################
WT3 = openpyxl.Workbook()
WE3 = WT3.active
WE3.append(['客户', '生产单号', '材料名称', '物料编码', '数量', '件数', '合同号', '备注', '生产日期'])
biaoshika = []
for temp in xiangbiao:
biaoshika_xiao = []
biaoshika_xiao.append(name_str)
biaoshika_xiao.append(kuanhao)
biaoshika_xiao.append("合格证")
biaoshika_xiao.append(temp[0])
biaoshika_xiao.append(temp[13])
biaoshika_xiao.append(temp[14])
biaoshika_xiao.append("")
biaoshika_xiao.append("02")
biaoshika_xiao.append("")
biaoshika.append(biaoshika_xiao)
for k in biaoshika:
WE3.append(k)
WT3.save(save_path + '/' + '标识卡-' + s2 + '.xlsx')
for file in files:
#table1(file)
print(file)
is_contain = "童" in file
if is_contain:
table(file)
else:
table1(file)