2020-07-05-学习python编制excel 和doc

#常规工作，支付签字表格自动生成。--支付小助手v1.0.py；学习python编制excel 和doc，依葫芦画瓢

#!/usr/bin/env python

#-- coding: utf-8 --

#Time : 2020/7/5

#Author : shawn_jn

from docximport Document

from docx.enum.textimport WD_LINE_SPACING, WD_PARAGRAPH_ALIGNMENT

from docx.enum.tableimport WD_TABLE_ALIGNMENT, WD_CELL_VERTICAL_ALIGNMENT

from docx.sharedimport Pt, RGBColor#榜数、颜色

from docx.oxml.nsimport qn#对应中文格式

import openpyxlas xl

import time

print('------注意：请再次确认是否正确填写《项目基本信息表》！------')

chose =1

while chose ==1:

print('【1】--已确认数据，继续执行！\n【2】--不执行，退出程序！')

chose_in =input('请选择【1】or【2】：')

print('\n')

if chose_in =="1":

ch =2

        chose =2

    elif chose_in =="2":

ch =1

        chose =2

    else:

print(f'"{chose_in}" 输入错误，请重新选择【1】or【2】\n')

if ch ==1:

print('程序已退出，谢谢使用！')

if ch ==2:

#支付审批表编辑，复制打开模板填写信息并另存为：合同名称-支付审批表(日期xxx）.xlsx文件

    today = time.strftime('%Y{y}%m{m}%d{d}', time.localtime()).format(y='年', m='月', d='日')

wb1 = xl.load_workbook('./data_base/【不许修改】-支付审批表-官方2020年OA文件.xlsx')#打开最新的"项目支付信息表"

    sheetw = wb1.worksheets[0]

sheetw_copy = wb1.copy_worksheet(wb1.worksheets[0])

sheetw_copy.title = today

# wb1.remove_sheet(wb1.get_sheet_by_name('sheet'))    #删除多余sheet

    del wb1['sheet']#删除多余sheet

#打开项目支付信息表

    wb2 = xl.load_workbook('【填写】-项目基本信息表-V1.0.xlsx', data_only=True)#data_only=True表示只获取数据，默认Fasle

    wb2_data = wb2.worksheets[0]

#在sheetw_copy中输入数据

    sheetw_copy.cell(4, 4).value = today#获取当前时间（填表时间）

    sheetw_copy.cell(6, 3).value = wb2_data.cell(1, 3).value#获取合同/规费名称

    sheetw_copy.cell(7, 3).value = wb2_data.cell(2, 3).value#获取合同编号

    sheetw_copy.cell(7, 7).value = wb2_data.cell(5, 3).value#获取合同金额

    sheetw_copy.cell(8, 3).value = wb2_data.cell(3, 3).value#获取收款单位

    sheetw_copy.cell(8, 6).value = wb2_data.cell(4, 3).value#获取开户行信息

    sheetw_copy.cell(9, 3).value = wb2_data.cell(8, 7).value#获取累计已支付预付款

    sheetw_copy.cell(9, 6).value = wb2_data.cell(6, 7).value#获取本期申请支付首付款

    sheetw_copy.cell(10, 3).value = wb2_data.cell(6, 3).value#获取累计已结算金额

    sheetw_copy.cell(10, 6).value = wb2_data.cell(7, 3).value#获取本次结算金额

    sheetw_copy.cell(12, 3).value = wb2_data.cell(1, 7).value#获取预付款

    sheetw_copy.cell(12, 5).value = wb2_data.cell(2, 7).value#获取投运款

    sheetw_copy.cell(12, 6).value = wb2_data.cell(3, 7).value#获取质保金

    sheetw_copy.cell(12, 7).value = wb2_data.cell(4, 7).value#获取考核或其他金额

    sheetw_copy.cell(13, 8).value = wb2_data.cell(10, 3).value#获取本次应支付金额

    wb1.save(f'《{wb2_data.cell(1, 3).value}》-支付审批表{today}.xlsx')#保存支付审批表

    wb1.close()

print(f'------{wb2_data.cell(1, 3).value}支付审批表已填写完成------')

#设置真整个文档的字体和段落基本格式

    document = Document()

document.styles['Normal'].font.name =u'方正仿宋_GBK'

    document.styles['Normal'].element.rPr.rFonts.set(qn('w:eastAsia'), u'方正仿宋_GBK')

# today1 = time.strftime('%Y-%m-%d', time.localtime())

#由于time库对中文支持不好，可以用以下方式处理

    today = time.strftime('%Y{y}%m{m}%d{d}', time.localtime()).format(y='年', m='月', d='日')

# contract_name = input('请输入合同名称：')  #后续从数据库内筛选

#编辑word内容

    p = document.add_paragraph()

run_01 = p.add_run('xxxxxx有限公司\n合同款付款说明')

run_01.font.name =u'方正小标宋_GBK'

    run_01.element.rPr.rFonts.set(qn('w:eastAsia'), '方正小标宋_GBK')

run_01.font.size = Pt(22)# 字体大小

    p.paragraph_format.line_spacing = Pt(28)#行间距设置

    p.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER

#创建表格,并将表格在word页面中居中

    table = document.add_table(rows=5, cols=4, style="TableGrid")

table.alignment = WD_TABLE_ALIGNMENT.CENTER

# table.style.font.name = u'方正仿宋_GBK'

    textlist = [

'合同/征收单位', '合同/规费名称', '合同/规费总价', '合同编号', '上期累计支付', '本期申请支付', '本期累计支付', '本期支付说明']

n =0

    for iin range(4):

for jin (0, 2):

table.cell(i, j).text = textlist[n]

n +=1

            # print(table.cell(i, j).text, n)

    table.cell(0, 1).text = wb2_data.cell(3, 3).value#收款单位

    table.cell(0, 3).text = wb2_data.cell(1, 3).value#合同名称

    table.cell(1, 1).text =str('%.2f' % wb2_data.cell(5, 3).value) +'元'  #合同总价

    table.cell(1, 3).text =str(wb2_data.cell(2, 3).value)#合同编号

    table.cell(2, 1).text =str('%.2f' % wb2_data.cell(6, 3).value) +'元'  #上期累计支付

    table.cell(2, 3).text =str('%.2f' % wb2_data.cell(10, 3).value) +'元'  #本期申请支付

    table.cell(3, 1).text =str('%.2f' % wb2_data.cell(11, 3).value) +'元'  #本期累计支付

    table.cell(3, 3).text = wb2_data.cell(9, 3).value#本期支付说明

# 表格排版格式进行调整，left格式

    for rowin range(0, 4):

for colin range(0, 4):

table.cell(row, col).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER

paragraphs_table = table.cell(row, col).paragraphs[0]

paragraphs_table.alignment = WD_PARAGRAPH_ALIGNMENT.LEFT#等同paragraphs_table.alignment = 0

# 也可（for left, 1 for right, 2 center, 3 justify ....）

    table.cell(4, 0).merge(table.cell(4, 3))#合并单元格

    table_parag_1 = table.cell(4, 0).paragraphs[0]#将合并单元

    table_parag_1.paragraph_format.line_spacing =Pt(20)

#(2)数据输入，最后格表格输入

    run_1_1 = table_parag_1.add_run('    1.支付条款，合同第')

run_1_2 = table_parag_1.add_run('%s' % wb2_data.cell(2, 10).value).font.underline =True    #第X条

    run_1_3 = table_parag_1.add_run('条第')

run_1_4 = table_parag_1.add_run('%s' % wb2_data.cell(2, 12).value).font.underline =True    #第X款

    run_1_5 = table_parag_1.add_run('款约定:')

num =int(wb2_data.cell(2, 12).value)

text_t =wb2_data.cell(num+1, 14).value

run_1_6 = table_parag_1.add_run(text_t).font.underline =True  #合同支付条款

    table_parag_2 = table.cell(4, 0).add_paragraph()

run_2_1 = table_parag_2.add_run('    2.')

run_2_2 = table_parag_2.add_run(today).font.underline =True

    run_2_3 = table_parag_2.add_run(',乙方')

run_2_4 = table_parag_2.add_run(wb2_data.cell(9, 3).value).font.underline =True    #验收xxx事项，说明

    run_2_5 = table_parag_2.add_run('事项通过了我公司')

run_2_6 = table_parag_2.add_run('运维检修部').font.underline =True

    run_2_7 = table_parag_2.add_run('验收，符合合同约定的结算支付条件。')

table_parag_3 = table.cell(4, 0).add_paragraph()

run_3_1 = table_parag_3.add_run('    3.乙方已提交相应的')

text_elements =[]

for iin range(5):

if wb2_data.cell(i+7, 14).value !=None:

text_elements.append(wb2_data.cell(i+7, 14).value)

text1 =''

    text2 =''

    text3 =''

    i =1

    for tin range(len(text_elements)-1):

text1 = text_elements[t] +'、' + text1

text3 = text3 +str(i) +'.' + text_elements[t] +'、'

        i +=1

    text2 = text1 + text_elements[-1]

text3 = text3 +str(i) +'.' + text_elements[-1]

# text1 = input('付款申请、发票及xxx资料：')

    run_3_2 = table_parag_3.add_run('%s' % text2).font.underline =True

    run_3_3 = table_parag_3.add_run('等，符合合同约定。')

table_parag_4 = table.cell(4, 0).add_paragraph()

run4_1 = table_parag_4.add_run('    4. 本次结算金额为：')

run4_2 = table_parag_4.add_run(str('%.2f' % wb2_data.cell(7, 3).value)).font.underline =True #输入结算金额

    run4_3 = table_parag_4.add_run('元(不含税金额为：')

run4_4 = table_parag_4.add_run(str('%.2f' % (wb2_data.cell(7, 3).value/(1+wb2_data.cell(8, 3).value))))#不含税金额

    run4_4.font.underline =True

    run4_5 = table_parag_4.add_run('元），')

run4_4 = table_parag_4.add_run('应扣款项为：')

sum_decrees =0

    for min range(4):

sum_decrees += wb2_data.cell(m+1, 7).value

run4_5 = table_parag_4.add_run(str('%.2f' % sum_decrees) +'元').font.underline =True

    run4_6 = table_parag_4.add_run('，应支付：')

run4_7 = table_parag_4.add_run(str('%.2f' % wb2_data.cell(10, 3).value) +'元').font.underline =True    #应支付金额

    run4_8 = table_parag_4.add_run('，累计支付金额为：')

run4_9 = table_parag_4.add_run(str('%.2f' % wb2_data.cell(11, 3).value) +'元').font.underline =True    #累计支付金额

    table_parag_5 = table.cell(4, 0).add_paragraph()

run5_1 = table_parag_5.add_run('    本期累计支付占合同总价的比例为：')

per = wb2_data.cell(11, 3).value*100/wb2_data.cell(5, 3).value

run5_2 = table_parag_5.add_run(str(round(per, 2)) +'%。')#支付比例

    run5_2.font.underline =True

    table_parag_6 = table.cell(4, 0).add_paragraph()

run6_1 = table_parag_6.add_run('    附  件： （合同规定的有关材料）\n')

table_parag_7 = table.cell(4, 0).add_paragraph('')

run7_1 = table_parag_6.add_run('    ' + text3)

table_parag_8 = table.cell(4, 0).add_paragraph('\n\n\n经办人：\n审核：\n运维检修部')

table_parag_8.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT

table_parag_8.paragraph_format.right_indent = Pt(60)

table_parag_9 = table.cell(4, 0).add_paragraph('年  月  日\n\n')

table_parag_9.alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT

table_parag_9.paragraph_format.right_indent = Pt(30)

document.save(f'%s-{today}-付款说明.docx' % wb2_data.cell(1, 3).value)

print(f'------{wb2_data.cell(1, 3).value}付款说明已编制完成！！！------')

wb2.close()