Python腾讯文档收集表转excel

# 玺乐幼儿园大二班粤康码、行程码截图收集

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


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)
    print(type_Name)
    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


def format_border(s_column, s_index, e_column, e_index):
    for row in tuple(newSheet[s_column + str(s_index):e_column + str(e_index)]):
        for cell in row:
            cell.border = bd




def addimg(current_col, current_row, file):
    if os.path.exists(file):
        file_img = Image(file)  # 调用图像函数
        file_img.width, file_img.height = newSize  # 这两个属性分别是对应添加图片的宽高

        dest_sheet.row_dimensions[current_row].height = image_height
        dest_sheet.add_image(file_img, get_column_letter(current_col) + str(current_row))  # 向d列中的单元格内指定添加图片

############################################################################
# 设置文字图片单元格的行高列宽
newSize = (200, 400)
column_width = 27
image_height = 400  # 设置行高，该设置的行高与excel文件中设置的行高值是一样的
text_height = 50  # 设置行高，该设置的行高与excel文件中设置的行高值是一样的
current_row = 5  # 设置开始行
order_num = 0

ft = Font(name=u'微软雅黑', size=11)  # 设置字体
ag = Alignment(vertical='center', horizontal='center', wrapText=True)  # 设置居中,设置换行
bd = Border(left=Side(border_style="thin", color='000000'),
            right=Side(border_style="thin", color='000000'),
            top=Side(border_style="thin", color='000000'),
            bottom=Side(border_style="thin", color='000000'))  # 设置边框格式

# 读取xlsx文件
wb = load_workbook("玺乐幼儿园(大二班)开学健康申报9月15日（收集结果） (2).xlsx")
# 获取表名
sheetname = wb.sheetnames
print(sheetname)
# 选择工作表
# sheet = wb['sheet1'] #精确的表名
sheet = wb.active  # 活动的表
# newSheet = wb.create_sheet("newSheet")

dest = load_workbook("玺乐幼儿园学生返校健康档案-空表.xlsx")
# 获取表名
dest_sheet = dest.active  # 活动的表

max_row = sheet.max_row + 1

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



    姓名 = sheet.cell(row=i, column=column_index_from_string('B'))
    print(姓名.value)
    三区 = sheet.cell(row=i, column=column_index_from_string('C'))
    print(三区.value)

    dest_sheet.cell(current_row, column_index_from_string('A')).value = order_num+1
    dest_sheet.cell(current_row, column_index_from_string('B')).value = 姓名.value
    dest_sheet.cell(current_row, column_index_from_string('C')).value = "大二班"
    dest_sheet.cell(current_row, column_index_from_string('D')).value = 三区.value

    img_0913_url = sheet.cell(row=i, column=column_index_from_string('D'))
    print(img_0913_url.value)
    img_0913_file=downloadImage(姓名.value+'_检测记录_0913',img_0913_url.value)
    addimg(column_index_from_string('E'),current_row,img_0913_file)

    img_0914_url = sheet.cell(row=i, column=column_index_from_string('E'))
    print(img_0914_url.value)
    img_0914_file=downloadImage(姓名.value+'_检测记录_0914',img_0914_url.value)
    addimg(column_index_from_string('F'),current_row,img_0914_file)

    img_0915_url = sheet.cell(row=i, column=column_index_from_string('F'))
    print(img_0915_url.value)
    img_0915_file=downloadImage(姓名.value+'_检测记录_0915',img_0915_url.value)
    addimg(column_index_from_string('G'),current_row,img_0915_file)

    img_ykm_url = sheet.cell(row=i, column=column_index_from_string('G'))
    print(img_ykm_url.value)
    img_ykm_file=downloadImage(姓名.value+'_粤康码_0915',img_ykm_url.value)
    addimg(column_index_from_string('H'),current_row,img_ykm_file)

    img_xcm_url = sheet.cell(row=i, column=column_index_from_string('H'))
    print(img_xcm_url.value)
    img_xcm_file=downloadImage(姓名.value+'_行程码_0915',img_xcm_url.value)
    addimg(column_index_from_string('J'),current_row,img_xcm_file)

    print('-------------------------')
    img_tzr_urls = sheet.cell(row=i, column=column_index_from_string('J'))
    img_tzr_count =len(img_tzr_urls.value.split()) #获取上传了多少个同住人（上传几张图片URL）
    print(img_tzr_count)
    img_tzr_index = 0
    tzr_col =column_index_from_string('K')
    while img_tzr_index < img_tzr_count:
        img_tzr_url =img_tzr_urls.value.split()[img_tzr_index] #根据下标获取一个url
        print(img_tzr_url)
        img_tzr_file=downloadImage(姓名.value+'_同住人_'+str(img_tzr_index),img_tzr_url)
        addimg(tzr_col,current_row,img_tzr_file)

        tzr_col=tzr_col+1
        img_tzr_index = img_tzr_index+1

    current_row =current_row+1
    order_num =order_num+1


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

dest.save('dest.xlsx')