提取不同表格数据自动生成三种表格(xlrd,openpyxl)

提取儿童版样式：

非儿童版样式：

生成标识卡表格：

卷标：

箱标：

代码:

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)