陶瓷原料化学成分分析

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

# 以列表形式列出各原料成份
raw = ['一级土', '二级土', '黑泥', '煅烧土'   , '红泥', '长石', '滑石', '石灰'   , '石英', '新石英', '白云石']
element_ = ['SiO2',    'Al2O3',   'K2O', 'Na2O',    'Fe2O3',   'TiO2',    'CaO', 'MgO', 'IL',  'TOTAL', 'Si/Al']
clay_1 = [65.54, 19.52, 0.63, 0.40, 2.58, 0.47, 1.28, 0.37, 9.02, 99.81]
clay_2 = [64.56, 18.33, 2.51, 0.38, 2.44, 0.56, 0.86, 0.63, 9.58, 99.85]
clay_black = [59.09,   18.36,     2.42,  0.40,  2.65,  0.73,  1.08,  0.47,  14.63,     99.83]
clay_fired = [74.22,   13.91, 1.03,  0.41,  1.80,  0.54,  1.91,  1.96,  4.02,  99.80]
clay_red = [62.03, 21.77, 1.60,  0.20,  3.29,  0.72,  1.00,  0.29,  8.91,  99.81]
feldspar = [70.27, 14.62, 4.08,    3.55,  1.63,  0.27,  2.41,  0.54,  2.50,  99.87]
talc = [44.76, 5.76,  0.47,  0.39,  4.75,  0.13,  2.71,  25.78, 15.07, 99.82]
lime = [0.99, 8.86,    1.32,  0.44,  2.47,  2.06,  44.21, 0.08,   37.66, 98.09]
silicate = [74.17, 16.06, 1.83,  1.37,  0.64,  0.14,  0.24,  0.18,  4.97,  99.60]
new_silicate = [96.52, 1.71,  0.15,  0.15,  0.09,  0.02,  0.04,  0.01,  0.65,  99.34]
dolomite = [25.68, 2.41,  0.36,  0.05,  0.42,  0.00,  29.21, 13.90, 27.93, 99.96]

raw_composition = ['clay_1', 'clay_2', 'clay_black', 'clay_fired', 'clay_red', 'feldspar', 'talc', 'lime',
                   'silicate', 'new_silicate', 'dolomite']

# 终端输入各种原料配比
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('请输入"新石英"百分比：'))
p10 = float(input('请输入"白云石"百分比：'))
# 用百分比乘以各原料成分组成新列表
clay_1_p1 = [p1*a for a in clay_1]
clay_2_p2 = [p2*b for b in clay_2]
clay_black_p3 = [p3*c for c in clay_black]
clay_fired_p4 = [p4*d for d in clay_fired]
feldspar_p5 = [p5*e for e in feldspar]
talc_p6 = [p6*f for f in talc]
lime_p7 = [p7*g for g in lime]
silicate_p8 = [p8*h for h in silicate]
new_silicate_p9 = [p9*i for i in new_silicate]
dolomite_p10 = [p10*j for j in dolomite]

# composition = []      # 设置一个成分的空列表

composition = zip(clay_1_p1, clay_2_p2, clay_black_p3, clay_fired_p4, feldspar_p5, talc_p6, lime_p7,
                  silicate_p8, new_silicate_p9, dolomite_p10)
# zip函数从相同长度列表中提取同序号元素组成新的列表

map_composition = list(map(sum, composition))
# map 函数在zip函数基础上，运用sum求和函数，将不同列表中相同序号的元素求和后组成新的列表。
si_al = map_composition[0]/map_composition[1]
# 求硅铝比
map_composition.append(si_al)
# 将硅铝比加入到列表中
print(map_composition)
# print(clay_1_p1)

# wb=openpyxl.Workbook()
# ws=wb.active
# ws.title='analysis_pengsheng'

wb = openpyxl.load_workbook('analysis_pengsheng.xlsx')
ws = wb['analysis_pengsheng']
ws.append(element_)
ws.append(map_composition)
ws.append([time.strftime('%Y-%m-%d %H:%M:%S')])
ws.append(['------------------------------'])
# 字体设为微软雅黑，字体大小２５，红色
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 + 1):
    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', 'E', 'F', 'G', 'H', 'I', 'J', 'K']
for i in list_cols:
    for cell in ws[i]:
        ws.column_dimensions[i].width = 15
x = ws.max_row - 1
y = ws.max_column
ws.merge_cells(start_row=x, start_column=1, end_row=x, end_column=y)
ws.merge_cells(start_row=x + 1, start_column=1, end_row=x + 1, end_column=y)
wb.save('analysis_pengsheng.xlsx')