- 是否因为客户准备报价而占用大量时间
- 是否总是在调Excel列宽行高搞得精疲力尽
- 是否为产品在报价表上需要分类感到焦头烂额
- 是否想有一个助手把这些机械的工作做得又快又好
下面这段python代码可以帮你解决以上所有问题，只用1秒钟。
先看下效果：
https://www.bilibili.com/video/BV1Be411x7j7/
-
准备好基础数据表
0.png -
客户信息照旧填写，把客户的需要的型号以及数量填好；0-2.png
运行程序。
完成！
0-3.png
接下来我们来看看如何实现。
需要准备的基础文件主要是产品信息统计表，python程序会根据客户的新需求在统计表中自动寻找匹配。此处我们将所有的信息都汇总在 ’DATA.xlsx‘ 下的'Pool 产品明细'中，并将客户需求填写在'AUTO PI DOCUMENT.xlsx'的'Sheet1'中，程序根据此表在 ’DATA.xlsx‘ 下的'Pool 产品明细'中做出匹配，然后生成新的'PI’ 作为发票草本。
准备好基础文件后，我们正式进入编程
我们会用到openpyxl库来处理Excel，及Font, Alignment,Border，Side方法来调整字体，格式，以及绘制表格。
载入库函数
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
- 载入需要使用的文档，
# 打开要求文档
wbData = openpyxl.load_workbook('AUTO PI DOCUMENT.xlsx')
wsData = wbData['Sheet1']
wbSource = openpyxl.load_workbook('DATA.xlsx', data_only=True)
wsSource = wbSource['Pool 产品明细']
这里注意在载入'Data.xlsx'的时候，因为表中存在大量公式进行计算，所以需要读取单元格内的值，而不要提取公式，所以后面加上了data_only=True
新建一个工作簿，在新工作簿中创建名为‘PI’的工作表
# 新建文档
wb = openpyxl.Workbook()
# Sheet新建与命名
wsPI = wb.active
wsPI.title = 'PI'
- 定义一下将会需要的字体与单元格边框格式
# 定义字体及边框格式
fontTitle = Font(name='Arial', bold=True, size=14)
fontObj1 = Font(name='Arial', size=9)
fontObj2 = Font(name='Arial', bold=True, size=8)
border_set = Border(left=Side(style='thin'),
right=Side(style='thin'),
top=Side(style='thin'),
bottom=Side(style='thin'))
- 接下来需要制作几个内置函数来方便程序编写
由于期望将客户需要的产品根据系列不同来分开，所以需要提取系列的编号，提取产品的型号，以及匹配信息并返回相应值的三个函数
- 提取系列的编号函数
- 由于例子中的产品主要是开关插座产品，编号'C301', ' VP405'，规则是第一位或前两位字母表示系列，后面3位表示型号，即便后面再加上颜色参数，总位数也不会超过8位。这里我首先需要判断产品型号是否小于8位，且第一位是字母，第三四位是数字。然后继续判断，如果第二位是数字，则系列编号只有1位，如果第二位也是字母，那么就返回前两位作为系列编号
# 判断是不是开关，并提取系列
def isswich (CODE):
if len(CODE) < 8 and CODE[0].isalpha() and CODE[2:4].isdecimal():
if CODE[1].isdecimal():
return CODE[0]
else:
return CODE[0:2]
else:
return False
- 提取产品型号函数
- 如果输入的产品是开关插座，那么将系列型号去除，接下来三位是编号，返回编号
# 提取产品型号
def code(CODE):
if isswich(CODE):
return CODE[len(isswich(CODE)): len(isswich(CODE))+3]
3.匹配产品信息函数
- 需要根据产品的型号’item'，匹配出需要的信息‘info'
# 匹配产品信息
def match(item,info):
title_col = 0
for title in range(1, wsSource.max_column+1):
if wsSource.cell(row=1, column=title).value == info:
title_col = title
if isswich(item):
# 在database里寻找相应的系列
for row in range(1, wsSource.max_row+1):
if wsSource.cell(row=row, column=4).value == isswich(item) and \
wsSource.cell(row=row, column=5).value == int(code(item)):
return wsSource.cell(row=row, column=title_col).value
- 然后因为在PI中根据系列进行分类，那就先将所有系列抽取，放在一个list中
# 找出所有 range
AllRange = set()
for i in range(16, wsData.max_row+1):
# 只有当item字符小于8个，首位是字母，第3,4位为数字，被认作是开关插座
if isswich(wsData['C' + str(i)].value):
rangeObj = str(wsData['C' + str(i)].value)[0:2]
if rangeObj[1].isdecimal():
rangeObj = rangeObj[0]
AllRange.add(rangeObj)
AllRange = list(AllRange)
- 接下来把所有产品分门别类的放在各个系列中，组成一个产品字典。
# 创建 产品字典
AllItem = {}
for Range in AllRange:
for itemRow in range(16, wsData.max_row+1):
if isswich(wsData['C'+str(itemRow)].value) == Range:
ITEM = wsData.cell(row=itemRow, column=3).value
qty = wsData.cell(row=itemRow, column=4).value
AllItem.setdefault(Range, {})
AllItem[Range].setdefault(ITEM, qty)
- 准备工作都做好了，接下来开始正式编辑PI
- 设置列宽
# 编辑文档
# 列宽设置
wsPI.column_dimensions['A'].width = 3
wsPI.column_dimensions['B'].width = 9
wsPI.column_dimensions['C'].width = 35
wsPI.column_dimensions['D'].width = 10
wsPI.column_dimensions['E'].width = 12
wsPI.column_dimensions['F'].width = 9.5
wsPI.column_dimensions['G'].width = 6.5
- 填写公司名称等表头并调整字体格式
# 第一行 公司名称
wsPI['A1'].value = wsData['B3'].value
wsPI['A1'].font = fontTitle
wsPI.merge_cells('A1:G1')
wsPI['A1'].alignment = Alignment(horizontal='center')
# 第二行 地址
wsPI['A2'].value = wsData['B4'].value
wsPI['A2'].font = fontObj1
wsPI.merge_cells('A2:G2')
wsPI['A2'].alignment = Alignment(horizontal='center')
# 第三行 电话
wsPI['A3'].value = wsData['B5'].value
wsPI['A3'].font = fontObj1
wsPI.merge_cells('A3:G3')
wsPI['A3'].alignment = Alignment(horizontal='center')
# 第四行 表名
wsPI['A4'].value = wsData['B6'].value
wsPI['A4'].font = fontTitle
wsPI.merge_cells('A4:G4')
wsPI['A4'].alignment = Alignment(horizontal='center')
- 第二部分填写订单相关信息并调整字体格式
- 填写表头
# 第二部分，客户及合同信息
# 2.1 填写订单相关信息
wsPI['A5'].value = 'To: ' + str(wsData['B7'].value)
wsPI['A6'].value = 'SHIPMENT: ' + str(wsData['B8'].value)
wsPI['A7'].value = 'PAYMENT: ' + str(wsData['B9'].value)
wsPI['A8'].value = 'AMOUNT AND QUANTITY: ' + str(wsData['B10'].value)
wsPI['E5'].value = 'INVOICE NO.: ' + str(wsData['B1'].value)
wsPI['E6'].value = 'DATE: ' + str(wsData['B11'].value)
wsPI['E7'].value = 'PORT OF DESTINATION: ' + str(wsData['B12'].value)
wsPI['E8'].value = 'PORT OF LOADING: ' + str(wsData['B13'].value)
# 2.2 合并单元格
wsPI.merge_cells('A5:D5')
wsPI.merge_cells('A6:D6')
wsPI.merge_cells('A7:D7')
wsPI.merge_cells('A8:D8')
wsPI.merge_cells('E5:G5')
wsPI.merge_cells('E6:G6')
wsPI.merge_cells('E7:G7')
wsPI.merge_cells('E8:G8')
for i in range(5, 9):
wsPI["A"+str(i)].font = fontObj1
wsPI["E" + str(i)].font = fontObj1
- 完成表格主体
# 填写产品相关信息
# 标题
TitleList = ['No.', 'ITEM', 'DESCRIPTION', 'QUANTITY', 'UNIT PRICE',
'TOTAL', 'VOLUME', 'CTN', 'PCS/CTN', 'CBM/CTN']
for i in range(1, 11):
wsPI.cell(row=9, column=i).value = str(TitleList[i - 1])
for i in range(1, 11):
wsPI.cell(row=9, column=i).font = fontObj2
wsPI.cell(row=9, column=i).alignment = Alignment(horizontal='center')
# 内容
Line = 10
for R in AllRange:
wsPI.cell(row=Line, column=1).value = R+' Range'
Line += 1
ItemList = list(AllItem[R])
for eachItem in ItemList:
wsPI.cell(row=Line, column=2).value = eachItem
wsPI.cell(row=Line, column=4).value = AllItem[R][eachItem]
wsPI.cell(row=Line, column=3).value = match(wsPI.cell(row=Line, column=2).value, 'DESCRIPTION')
wsPI.cell(row=Line, column=5).value = match(wsPI.cell(row=Line, column=2).value, 'PRICE')
wsPI.cell(row=Line, column=9).value = match(wsPI.cell(row=Line, column=2).value, 'QTY/CTN')
wsPI.cell(row=Line, column=10).value = match(wsPI.cell(row=Line, column=2).value, 'VOLUME')
wsPI.cell(row=Line, column=6).value = '=round(D{}*E{},2)'.format(Line, Line)
wsPI.cell(row=Line, column=8).value = '=D{}/I{}'.format(Line, Line)
wsPI.cell(row=Line, column=7).value = '=round(H{}*J{},3)'.format(Line, Line)
wsPI.cell(row=Line, column=4).number_format = '#,##0" PCS"'
wsPI.cell(row=Line, column=5).number_format = '$#,##0.00'
wsPI.cell(row=Line, column=6).number_format = '$#,##0.00'
wsPI.cell(row=Line, column=7).number_format = '#,##0.000'
wsPI.cell(row=Line, column=8).number_format = '#" CTN"'
wsPI.cell(row=Line, column=9).number_format = '#" PCS"'
wsPI.cell(row=Line, column=10).number_format = '#,##0.000'
Line += 1
- 完成表格汇总行
MaxRow = wsPI.max_row
wsPI.cell(row=MaxRow+1, column=3).value = 'TOTAL'
wsPI.cell(row=MaxRow+1, column=4).value = '=sum(D10:D{})'.format(MaxRow)
wsPI.cell(row=MaxRow+1, column=5).value = 'FOB WENZHOU'
wsPI.cell(row=MaxRow+1, column=6).value = '=sum(F10:F{})'.format(MaxRow)
wsPI.cell(row=MaxRow+1, column=7).value = '=sum(G10:G{})'.format(MaxRow)
wsPI.cell(row=MaxRow+1, column=8).value = '=sum(H10:H{})'.format(MaxRow)
wsPI.cell(row=wsPI.max_row, column=3).font = fontObj2
wsPI.cell(row=wsPI.max_row, column=3).alignment = Alignment(horizontal='center')
wsPI.cell(row=wsPI.max_row, column=4).number_format = '#,##0" PCS"'
wsPI.cell(row=wsPI.max_row, column=5).font = fontObj2
wsPI.cell(row=wsPI.max_row, column=5).alignment = Alignment(horizontal='center')
wsPI.cell(row=wsPI.max_row, column=6).number_format = '$#,##0.00'
wsPI.cell(row=wsPI.max_row, column=7).number_format = '#,##0.000'
wsPI.cell(row=wsPI.max_row, column=8).number_format = '#" CTN"'
- 绘制表格，调整部分需要加粗字体
for row in range(10, wsPI.max_row+1):
for column in range(1,8):
wsPI.cell(row=row,column=column).border = border_set
wsPI.cell(row=row, column=column).font = fontObj1
for row in range(9,wsPI.max_row+1):
wsPI.cell(row=row, column=1).font = fontObj2
for column in range(1,8):
wsPI.cell(row=9, column=column).border = border_set
- 结尾 合同双方
# 结尾
lastRow = wsPI.max_row
wsPI.cell(row=lastRow+2, column=1).value = 'ACCEPTED:'
wsPI.cell(row=lastRow+3, column=1).value = wsData['B7'].value
wsPI.cell(row=lastRow+2, column=4).value = 'OFFERED:'
wsPI.cell(row=lastRow+3, column=4).value = wsData['B3'].value
for k in range(lastRow+2, lastRow+4):
wsPI["A"+str(k)].font = fontObj2
wsPI["D" + str(k)].font = fontObj2
- 保存文件名（合同编号及卖方信息）
# 文件保存
wb.save("{}-{}.xlsx".format(wsData["B2"].value, wsData["B1"].value))
- 以上便是合同编写简易的小程序
- （代码中会有冗余，和不合理不严谨的逻辑，还望高手指正）
