python操作excel生成word

python实现excel单元格内容操作

一、程序设计初衷

某天到驾校办点业务，看到驾校监考老师在用excel软件手动转移数据，当时我突发奇想，能不能用python全自动实现这个转移过程呢？

二、初试牛刀

到家之后我就开始研究数据转移的原理，其实看似很简单，就是把含所有学员成绩的excel表格，如下图所示：

原始excel表

转换成一张只打印4个人的成绩单，明白了原理之后就好办了，我的计划是把excel转换成word，每个页面打印4个学员的考试成绩，如下图所示：

转换成功的word文件

三、代码展示

# --------------------------------------------
# 开发时间：2022/3/31 17:40
# --------------------------------------------

import pandas as pd
import xlrd
from docx import Document
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT
from docx.shared import Cm,Pt,Inches
from docx.enum.section import WD_ORIENTATION
import time,os
import win32api,win32con

def cj():
    nowtime = time.strftime("%Y-%m-%d", time.localtime())
    file = os.listdir(os.getcwd())
    filelst = []
    for xl in file:
        if os.path.splitext(xl)[1] == ".xls":
            filelst.append(xl)

    df = xlrd.open_workbook(filelst[0])
    sheet1 = df.sheets()[0]
    hang = sheet1.nrows
    title = sheet1.row_values(0)
    data = []
    document = Document()
    document.styles['Normal'].font.size = Pt(10)
    section = document.sections[0]
    new_width, new_height = section.page_height, section.page_width
    section.orientation = WD_ORIENTATION.LANDSCAPE
    section.page_width = new_width
    section.page_height = new_height
    section.left_margin = Cm(1)
    section.right_margin = Cm(1)
    section.top_margin = Cm(1)     #上边距
    section.bottom_margin = Cm(1)  #下边距
    for j in range(1,hang):
        data.append(sheet1.row_values(j))
    for n in data:
        p1 = document.add_paragraph()
        p1.alignment = WD_TABLE_ALIGNMENT.CENTER
        run1 = p1.add_run('考生虚拟场景考试成绩单')
        run1.font.size = Pt(18)
        run1.font.bold = True
        table1 = document.add_table(rows=1, cols=2, style='Medium List 1')
        table = document.add_table(rows=2, cols=9, style='Table Grid')
        hdr_cell = table1.rows[0].cells
        hdr_cell[0].add_paragraph('考试类型：初考')
        hdr_cell[1].add_paragraph('考试时间：{}'.format(nowtime))
        cell = table1.cell(0,1)


        cell.paragraphs[1].alignment = WD_PARAGRAPH_ALIGNMENT.RIGHT

        table.cell(0, 0).width = Cm(1.6)
        table.cell(1, 0).width = Cm(1.6)
        table.cell(0, 1).width = Cm(1.2)
        table.cell(1, 1).width = Cm(1.2)
        table.cell(0, 4).width = Cm(2)
        table.cell(1, 4).width = Cm(2)
        table.cell(0, 5).width = Cm(3)
        table.cell(1, 5).width = Cm(3)
        table.cell(0, 6).width = Cm(6)
        table.cell(1, 6).width = Cm(6)
        table.cell(0, 7).width = Cm(2)
        table.cell(1, 7).width = Cm(2)




        # for row, obj_row in enumerate(table.rows):
        #     for col, cell in enumerate(obj_row.cells):
        #         cell.text = cell.text + "%d,%d " % (row, col)


        hdr_cells1 = table.rows[0].cells
        hdr_cells1[0].add_paragraph('姓名')
        hdr_cells1[1].add_paragraph('性别')
        hdr_cells1[2].add_paragraph('身份证号')
        hdr_cells1[3].add_paragraph('准考证号')
        hdr_cells1[4].add_paragraph('考试类别')
        hdr_cells1[5].add_paragraph('节能驾驶成绩')
        hdr_cells1[6].add_paragraph('危险源辨识与防御性驾驶成绩')
        hdr_cells1[7].add_paragraph('合计')
        hdr_cells1[8].add_paragraph('考生签字')

        for y in range(0,9):
            cell = table.cell(0,y)
            cell.paragraphs[1].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER

        hdr_cells2 = table.rows[1].cells
        hdr_cells2[0].add_paragraph(n[1])
        hdr_cells2[1].add_paragraph(n[2])
        hdr_cells2[2].add_paragraph(n[3])
        hdr_cells2[3].add_paragraph(n[4])
        hdr_cells2[4].add_paragraph(n[5])
        hdr_cells2[5].add_paragraph(n[6])
        hdr_cells2[6].add_paragraph(n[7])
        a = int(n[6])
        b = int(n[7])
        hdr_cells2[7].add_paragraph(str(a+b))

        for y in range(0,8):
            cell = table.cell(1,y)
            cell.paragraphs[1].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER

        p2 = document.add_paragraph()
        p2.alignment = WD_TABLE_ALIGNMENT.CENTER
        run2 = p2.add_run('')
        run2.font.size = Pt(18)

    document.save('成绩单.docx')
    win32api.MessageBox(0, "考试成绩单生成成功!", "成绩单",win32con.MB_OK)

if __name__ == '__main__':
    cj()