陶瓷工艺坯料配方自动生成工艺表格

import openpyxl
import time
from openpyxl.styles import Font, Alignment
from openpyxl.styles import PatternFill, Border, Side


# 设计坯体配方投料表格
rs = ['一级土', '二级土', '黑泥', '煅烧土', '长石', '滑石', '石英', '石灰', '白云石', '总量']
p1 = float(input('请输入"一级土"百分比：'))
p2 = float(input('请输入"二级土"百分比：'))
p3 = float(input('请输入"黑泥"百分比：'))
p4 = float(input('请输入"煅烧土"百分比：'))
p5 = float(input('请输入"长石"百分比：'))
p6 = float(input('请输入"滑石"百分比：'))
p7 = float(input('请输入"石英"百分比：'))
p8 = float(input('请输入"石灰"百分比：'))
p9 = float(input('请输入"白云石"百分比：'))
p = p1 + p2 + p3 + p4 + p5 + p6 + p7 + p8 + p9

q1 = format(p1, '.0%')      # 将浮点数转换成百分比
q2 = format(p2, '.0%')
q3 = format(p3, '.0%')
q4 = format(p4, '.0%')
q5 = format(p5, '.0%')
q6 = format(p6, '.0%')
q7 = format(p7, '.0%')
q8 = format(p8, '.0%')
q9 = format(p9, '.0%')
q = format(p, '.0%')
num = int(input('请输入装机总重量：'))

m1 = float(p1)*int(num)
m2 = float(p2)*int(num)
m3 = float(p3)*int(num)
m4 = float(p4)*int(num)
m5 = float(p5)*int(num)
m6 = float(p6)*int(num)
m7 = float(p7)*int(num)
m8 = float(p8)*int(num)
m9 = float(p9)*int(num)
m = m1 + m2 + m3 + m4 + m5 + m6 + m7 + m8 + m9

list0 = list(range(1, len(rs) + 1))
list1 = [q1, q2, q3, q4, q5, q6, q7, q8, q9, q]
list2 = [m1, m2, m3, m4, m5, m6, m7, m8, m9, m]

'''
A11 = '总量'
B11 = format(p, '.0%')
C11 = int(sum(list2))
'''
rows = list(zip(list0, rs, list1, list2))
'''
for j in range(len(rows)-1):

    rows[j+1].insert(0, str(j+1))    # 工作表内第一列插入序号
'''
wb = openpyxl.load_workbook('body_formula.xlsx')      # 打开现有工作簿
ws = wb['formula1']           # 打开现有工作表
# wb = openpyxl.Workbook()     # 新建工作簿
# ws = wb.create_sheet('formula1')     # 新建工作表并命名为‘formula1’
# ws.title = 'body_formula'        # 为工作表命名
'''
ws['A1'] = '序号'
ws['B1'] = '原料名称'
ws['C1'] = '百分比'
ws['D1'] = '数量'
'''
ws.append(['序号', '原料名称', '百分比', '数量'])

for row in rows:

    ws.append(row)

time1 = time.strftime('%Y-%m-%d %H:%M:%S')        # 格式化当地时间并赋值给变量time1
ws.append([time1])      # 将当前时间添加到工作表
ws.append(4*['******'])      # 添加*号作为结束标记
# 字体设为微软雅黑，字体大小２５，红色
font = Font(name=' 微软雅黑', size=14, italic=False, color='00000000', bold=True)
# 设置对应单元格的字体样式
ws['A1'].font = font
for i in range(ws.min_row, ws.max_row + 1):
    for cell in ws[i]:
        cell.font = font

# 填充样式，交单元格背景色填充为蓝色
fill = PatternFill(fill_type='solid', start_color='00FFFFFF', end_color='00000000')
ws['A2'].fill = fill
# 边框样式
border = Border(left=Side(border_style='double'),
                right=Side(border_style='double'),
                top=Side(border_style='double'),
                bottom=Side(border_style='double')

                )
ws['C1'].border = border
for i in range(ws.min_row, ws.max_row):
    for cell in ws[i]:
        cell.border = border
# 单元格内容对齐
align = Alignment(horizontal='center', vertical='center', wrap_text=False)
ws['D1'].alignment = align

for i in range(ws.min_row, ws.max_row + 1):
    for cell in ws[i]:
        cell.alignment = align

# 第三行行高修改为25
for i in range(ws.min_row, ws.max_row + 1):
    ws.row_dimensions[i].height = 25
# Ａ列列宽修改为３０
list_cols = ['A', 'B', 'C', 'D']
for i in list_cols:
    for cell in ws[i]:
        ws.column_dimensions[i].width = 15
x = ws.max_row - 1
ws.merge_cells(start_row=x, start_column=1, end_row=x, end_column=4)
wb.save('body_formula.xlsx')        # 保存工作簿
"""
rs = ['一级土', '二级土', '黑泥', '煅烧土', '长石', '滑石', '石英', '石灰', '白云石']
list1 = [1, 2, 3, 4, 5, 6, 7, 8, 9]
list2 = [11, 12, 13, 14, 15, 16, 17, 18, 19]
zip_list = list(zip(rs, list1, list2))
print(zip_list)
enumerate_list = list(enumerate(zip_list, 1))
print(enumerate_list)
wb = openpyxl.Workbook()
ws = wb.active
for i in zip_list:
    print(i)
    ws.append(list(i))
for index, j in enumerate_list:
    print(tuple(str(index))+j)
    ws.append(tuple(str(index))+j)
wb.save('20220131.xlsx')
"""