Python腾讯文档收集表转Word

 

# 玺乐幼儿园中二班新冠疫苗接种记录

import openpyxl
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Font, Alignment, Border, Side
from openpyxl.worksheet.page import PageMargins
import requests
import os
from docx import Document
from docx.shared import Inches, Cm, Pt
from docx.shared import Inches
from docx.oxml.ns import qn
import docx
from urllib3.connectionpool import xrange
from docx.enum.section import WD_ORIENT
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT


def getImageType(url):
    # print(url)
    type_index = url.find('type=')
    type_Name = url[-(len(url) - type_index - 5):]
    # print(type_Name)
    return type_Name


def downloadImage(image_name, url):
    type_Name = getImageType(url)
    image = requests.get(url).content
    print(image_name + '正在保存...')
    with open('./img/' + image_name + '.' + type_Name, 'wb')as fp:
        fp.write(image)

    path = os.getcwd()
    return path + "\\img\\" + image_name + '.' + type_Name


############################################################################

document = Document()
# 设置横向
section = document.sections[0]
new_width, new_height = section.page_height, section.page_width
section.orientation = WD_ORIENT.LANDSCAPE
section.page_width = new_width
section.page_height = new_height
# 设置页边距
section.top_margin = Cm(2)
section.bottom_margin = Cm(2)
section.left_margin = Cm(1)
section.right_margin = Cm(1)

paragraph = document.add_heading('玺乐幼儿园幼儿接种凭证', 0)  # 添加标题
paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 居中

# 读取xlsx文件
wb = load_workbook("玺乐园（中二）班新冠疫苗接种记录收集表（收集结果）.xlsx")
# 获取表名
sheetname = wb.sheetnames
print(sheetname)
# 选择工作表
# sheet = wb['sheet1'] #精确的表名
sheet = wb.active  # 活动的表

current_row = 1  # 设置开始行
order_num = 0  # 编号
max_row = sheet.max_row + 1

for i in range(2, 29):
    print('##########################')

    order_num = order_num + 1  # 编号
    print(order_num)

    current_column = order_num % 5  # 一行5列，取余

    if (current_column == 0):  # 取余等于0
        current_column = 5  # 放在第5列
        document.add_page_break()  # 添加一空白页
    elif (current_column == 1):

        # mystyle = document.styles["Normal"]
        # font = mystyle.font
        # font.size = Pt(22)
        # font.name = u'宋体'
        # font._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
        # paragraph.style = mystyle

        table = document.add_table(rows=1, cols=5, style='Table Grid')  # 添加表
        for j in range(len(table.rows)):
            table.rows[j].height = Inches(5)  # 设置高度

        row_cells = table.add_row().cells  # 添加行

    name = sheet.cell(row=i, column=column_index_from_string('C'))  # C列是姓名
    print(name.value)

    img_url = sheet.cell(row=i, column=column_index_from_string('D')).hyperlink.target  # D列是图片url
    print(img_url)

    img_file = downloadImage(name.value + '_疫苗接种记录', img_url)  # 先下载并保存，返回文件路径

    img_paragraph = table.cell(0, current_column - 1).paragraphs[0]  # 添加一段落
    img_paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER  # 段落居中
    run = img_paragraph.add_run()  # 插入一个文字块
    # 添加图片操作
    picture = run.add_picture(img_file)  # 添加图片
    picture.height = Cm(12)  # 设置图片高度
    picture.width = Cm(5)  # 设置图片宽度

    # 添加文字，注意5列+5即为下一行
    table.cell(0, current_column - 1 + 5).text = '班级：中二班\n姓名：' + name.value + '\n序号：' + str(order_num)

    # mystyle2 = document.styles["Normal"]
    # font = mystyle.font
    # font.size = Pt(12.5)
    # font.name = u'宋体'
    # font._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
    # table.cell(0, current_column - 1 + 5).style = mystyle2
    print(current_row, current_column)

print('**********************************************************************')

document.save('玺乐园（中二）班新冠疫苗接种记录.docx')