核算表部分区域（行）合计

核算表部分区域（行）合计

from openpyxl.reader.excel import load_workbook
from openpyxl import Workbook
import os

# 文件存在则删除
if os.path.exists('test_out.xlsx'):
    os.remove('test_out.xlsx')
    
# 新建一个xlsx文件
o = Workbook()
o1 = o.create_sheet('统计生产日报表',0) # 创建sheet
r = o['Sheet']
o.remove(r)

# 读取文件
file = load_workbook(filename='元一成本结转核算月度报表9月二厂.xlsx', data_only = True)
sheet1 = file['统计生产日报表']  # 统计生产日报表

# 设置读取行
row1 = [6, 7]  # 统计生产日报表

# 读取数据
data1 = []
for lineNum in row1:
    tilist = []
    for columnNum in range(1, sheet1.max_column + 1):
        value = sheet1.cell(row=lineNum, column=columnNum).value
        tilist.append(value)
    data1.append(tilist)
print(data1)

# 合计函数
def sumdata(names):  # 名称 列 数据
    dataform = names[1]
    if dataform == '统计生产日报表':
        data = data1
    name = names[0]
    lie = names[2]
    lienum = (ord(lie[0])-64)*26 + (ord(lie[1])-64)
    sumdata = 0
    for i in data:
        if i[lienum-1] != None:
            sumdata += i[lienum-1]
    names.insert(4, sumdata)  
    names += ['']*(13-len(names)) # 最长值8
    return names

def superurl(names):
    return '=HYPERLINK("#{}!{}","{}")'.format(names[1],names[3],names[0])

# mian
in_data = []  # 为保证最后的超链接有对象，采用倒序添加方式

# 分摊制造费用
fentanzhizaofeiyong = ['分摊制造费用', '统计生产日报表', 'BC', 'A11']
fentanzhizaofeiyong = sumdata(fentanzhizaofeiyong)
in_data.append(fentanzhizaofeiyong)
# 工资
gongzi = ['工资', '统计生产日报表', 'BB', 'A10']
gongzi = sumdata(gongzi)
in_data.append(gongzi)
# 固定制造
gudingzhizao = ['固定制造', '统计生产日报表', 'AZ', 'A9']
gudingzhizao = sumdata(gudingzhizao)
in_data.append(gudingzhizao)
# 外场冷费
waichanglengfei = ['外场冷费', '统计生产日报表', 'AY', 'A8']
ranliaojiqita = sumdata(waichanglengfei)
in_data.append(waichanglengfei)
# 燃料及其他
ranliaojiqita = ['燃料及其他', '统计生产日报表', 'AX', 'A7']
ranliaojiqita = sumdata(ranliaojiqita)
in_data.append(ranliaojiqita)
# 包装物金额
baozhuangwujine = ['包装物金额', '统计生产日报表', 'AW', 'A6']
baozhuangwujine = sumdata(baozhuangwujine)
in_data.append(baozhuangwujine)
# 调味料金额
tiaoweiliaojine = ['调味料金额', '统计生产日报表', 'AV', 'A5']
tiaoweiliaojine = sumdata(tiaoweiliaojine)
in_data.append(tiaoweiliaojine)
# 分摊耗原
fentanhaoyuan = ['分摊耗原', '统计生产日报表', 'AP', 'A4']
fentanhaoyuan = sumdata(fentanhaoyuan)
in_data.append(fentanhaoyuan)
# 加工费
jiagongfei = ['加工费','统计生产日报表',  'BD', 'A3', '调味料金额+包装物金额+燃料及其他+外场冷费+固定制造+工资+分摊制造费用', superurl(tiaoweiliaojine), superurl(baozhuangwujine), superurl(ranliaojiqita), superurl(waichanglengfei), superurl(gudingzhizao), superurl(gongzi), superurl(fentanzhizaofeiyong)]
jiagongfei = sumdata(jiagongfei)
in_data.append(jiagongfei)
# 成本合计
chengbenheji = ['成本合计', '统计生产日报表', 'BE', 'A2', '加工费+分摊耗原', superurl(jiagongfei), superurl(fentanhaoyuan)]
chengbenheji = sumdata(chengbenheji)
in_data.append(chengbenheji)

# 数据最长值
maxlen = 0
for i in in_data:
    maxlen = max(maxlen, len(i))
print('最长值', maxlen) # 9

# 写入数据
o1.cell(1,1).value = '数据名称'
o1.cell(1,2).value = '数据来源'
o1.cell(1,3).value = '原数据列'
o1.cell(1,4).value = '现数据行'
o1.cell(1,5).value = '合计值'
o1.cell(1,6).value = '公式'
o1.cell(1,7).value = '公式项1'
o1.cell(1,8).value = '公式项2'
o1.cell(1,9).value = '公式项3'
o1.cell(1,10).value = '公式项4'
o1.cell(1,11).value = '公式项5'
o1.cell(1,12).value = '公式项6'
o1.cell(1,13).value = '公式项7'
# o1.cell(1,14).value = '公式项8'
in_data = in_data[::-1]  # 倒序列表
for i in range(2,2+len(in_data)):
    for j in range(1,maxlen+1):
        o1.cell(i,j).value = in_data[i-2][j-1]
        
# 保存数据
o.save('test_out.xlsx')