题目起的有些模糊，这里介绍一下需求：
我有一张表A，包含多条人员数据，简化为以下的表样：
姓名 | 身份证后四位 | 父亲信息 | 母亲信息 | 子女信息 |
张三 | | | | |
李四 | | | | |
还有很多表B，每个表B是人员的个人表，简化为以下的表样：
姓名 | 张三 | 1寸照片 |
家庭信息 | ||
称谓 | 单位 | 职务 |
父亲 | …… | …… |
母亲 | …… | …… |
现需要把大表 表A里的家庭成员信息 更新到所有的个人表表B当中。 表A为xlsx格式,表B为xls格式。（这也是遇到的困难来源之一）
而原来的表B表样格式不可以丢失，并且里面的一寸照也不可以丢失。
我是用Python做的，结果做的过程中遇到很多问题，几乎没有办法用一个库去处理我的需求。
网上搜到的库有xlrd,xlwt,openpyxl,xlutils
首先读大表A.xlsx我是用openpyxl来做的。
from openpyxl import load_workbook
wb = load_workbook(filename=bigFilePath)
#获取所有表格(worksheet)的名字
sheets = wb.get_sheet_names()
#第一个表格的名称
sheet_first = sheets[0]
#获取特定的worksheet
ws = wb.get_sheet_by_name(sheet_first)
#获取表格所有行和列，两者都是可迭代的
rows = ws.rows
columns = ws.columns
#迭代所有的行
for idx,row in enumerate(rows):
None # 处理自己的业务在迭代每一行数据之中，取到个人的相关信息，再去本地的路径下寻找以该名字命名的B.xls文件，这部分代码就不贴出来了。
问题是找到B.xls之后，发现openpyxl只支持xlsx，而xlrd只能处理xls格式文件，我想基于已有的B文件去做修改（而不是插入新的sheet），找到了xlutils这个库，用他的copy来复制出来一份表进行写入，再保存可以达到目的。
部分代码如下：
from xlutils.copy import copy
import xlrd
data = xlrd.open_workbook(filename, formatting_info=True)#先用xlrd去读 formatting_info=True会让复制的文件尽量保留原来的样式，但也会有局部丢失样式的情况
wb_wr = copy(data) #复制操作
ws_wr = wb_wr.get_sheet(0)
ws_wr.write(row, col, content, style) #参数意义就是字面意义，对指定单元格进行覆盖写入
wb_wr.save(filePath+name+'.xls')#保存结果，这么弄完，我发现复制出来修改再替换，表和内容基本没问题，但是照片没有了。搜素一通之后发现excel的照片插入跟单元格本身的内容无关，所以操作单元格是会忽略照片的。
又搜索一通找到了两种获取excel图片的方式，方案一是把xlsx文件试作zip包里面会有图片，我试了一下把xls转换成xlsx之后再转成zip发现并没有图片，所以我这里想必无法使用方案一，方案二是用win32com.client去获取，这个类似一个截屏的感觉，会损失不少图片清晰度，并且只有windows上支持。由于没找到更好的办法，就暂时先用方案二。
import win32com.client as win32
from PIL import ImageGrab
import random
def getImageFromExcel(filePath):
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(filePath)
num = random.randint(1,500)
output_file = "";
for sheet in workbook.Worksheets:
for i, shape in enumerate(sheet.Shapes):
if shape.Name.startswith('Picture'):
shape.Copy()
image = ImageGrab.grabclipboard()
image.convert('RGB').save(r'D:\img\{}.jpg'.format(num), 'jpeg')
output_file = 'D:\\img\\{}.jpg'.format(num)
num+=1
excel.Quit()
return output_file用这段代码，我能“事先”把原本的B.xls表里的图片，截图到我本地。我用一个随机数命名保存。
之后，我需要把图片写入的操作加到 刚刚做到的复制修改那一步后面。我发现插入图片这又是一个困难的需求，找了半天还是openpyxl才可以插入，但是openpyxl又只支持xlsx，我需要把xls先转成xlsx，再插入图片，再转回来（我焯！！！）
我又去搜转换文件的代码：
import win32com.client as win32
def exchange(filename):
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(filename)
wb.SaveAs(filename+r"x", FileFormat = 51) #FileFormat = 51 is for .xlsx extension
wb.Close() #FileFormat = 56 is for .xls extension
excel.Application.Quit()同样是用的win32.这里只贴出来从xls换成xlsx，后缀加个x就行，换回来的代码就不贴了，用类似[:-1]或者分隔等等方式去掉“x”，并把上面FileFormat参数改成56.
在xlsx类型的时候插入图片，再转换回来。
# 文件转为xlsx
exchange(filename)
#开始插入图片
#print(img_file)
isexist_img = os.path.exists(img_file)
if (isexist_img):
img = Image(img_file)
new_size = (115, 185)
img.width, img.height = new_size
wb3 = load_workbook(filename_x)
sheet3 = wb3["sheet1"]
sheet3.add_image(img, 'H5')
wb3.save(filename_x)
else:
print("没有照片："+filename)
# 文件转为xls
unexchange(filename_x, name)至此，我才得到了一个基本保留样式，且局部单元格被我修改，并且照片清晰度有损失的一个xls文件。
迭代大表A.xlsx里的每一条数据去执行这么一套流程去改，最终就完成了我的需求。
真的非常之恶心。首先图片清晰度有损失，其次处理了几百个B表，小部分样式有微小的缺失，不完美。然后执行也非常慢（主要应该是慢在了涉及win32的部分）。而且win32只能在windows上运行，想在家里mac上跑一下，但是目前还没找到mac上替换win32的东西。
有空我再搜索一下有没有其他库可以更舒服简单的完成这些，尤其能不损失图片质量的情况下。
完整代码贴到下面，有点乱，自己刚刚跑通，只是刚好顺利跑完了所有文件的版本，有类似需求的小伙伴可以忽略我自己业务的部分，只参考那几个库的运用即可。有时间我再整理一下，把该分的模块分出来。应该还能少100行代码。。
注：python版本3.7.2 其他涉及的库都是用pip install安装的，没有特别难找的库
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from datetime import date,datetime
import os
import xlrd
import xlwt
import xlutils
from xlutils.copy import copy
from xlrd import xldate_as_tuple
import time
from openpyxl.drawing.image import Image
import win32com.client as win32
import xlsxwriter
from PIL import ImageGrab
import random
# 本程序从一张xlsx表中取每个人的家庭信息，保存到各自姓名命名的xls表中
def start(filePath):
#打开一个workbook
bigFilePath = filePath + r"4.xlsx"
wb = load_workbook(filename=bigFilePath)
#获取当前活跃的worksheet,默认就是第一个worksheet
#ws = wb.active
#当然也可以使用下面的方法
#获取所有表格(worksheet)的名字
sheets = wb.get_sheet_names()
#第一个表格的名称
sheet_first = sheets[0]
#获取特定的worksheet
ws = wb.get_sheet_by_name(sheet_first)
#获取表格所有行和列，两者都是可迭代的
rows = ws.rows
columns = ws.columns
#迭代所有的行
for idx,row in enumerate(rows):
if (idx>1):
print("开始第"+str(idx-1)+"行")
line = [col.value for col in row]
name = row[1].value
name_id = row[4].value
filename = filePath + name + '.xls'
filename_x = filePath + name + '.xlsx'
filename2 = filePath + name_id + '.xls'
filename2_x = filePath + name_id + '.xlsx'
isexist = os.path.exists(filename)
if (not isexist):
isexist = os.path.exists(filename2)
filename = filename2
filename_x = filename2_x
if(isexist):
# 找到该人员对应文件
#print(filename)
if(os.path.exists(filePath +"out\\"+ name + '.xls') or os.path.exists(filePath +"out\\"+ name + '.xlsx')):
continue
# 获取照片，存入临时文件夹
img_file = getImageFromExcel(filename)
# 获取配偶信息
# 称谓 用身份证号码17位判断个人性别（奇数男 偶数女） 进而判断配偶的称谓 TODO
id_card_4 = row[3].value
id_card_4 = str(id_card_4)
if (len(id_card_4) == 4):
sex = id_card_4[2]
if int(sex)%2 == 0:
# 个人性别为女：配偶称谓设置为丈夫
peiou_cw = "丈夫"
else:
# 个人性别为男：配偶称谓设置为妻子
peiou_cw = "妻子"
# 配偶姓名
peiou_name = row[7].value
# 配偶政治面貌
peiou_zz = row[9].value
# 配偶工作单位
peiou_comp = row[10].value
# 配偶出生日期
peiou_birth = row[8].value
if (peiou_birth != None and peiou_birth != "" and isinstance(peiou_birth, datetime)):
peiou_birth = peiou_birth.strftime('%Y/%m/%d')
# 配偶职务
peiou_zw = row[11].value
# 获取子女信息 TODO
# 子女1
zinv1_cw = row[23].value
zinv1_name = row[22].value
zinv1_birth = row[24].value
if (zinv1_birth != None and zinv1_birth != "" and isinstance(zinv1_birth, datetime)):
zinv1_birth = zinv1_birth.strftime('%Y/%m/%d')
zinv1_zz = row[25].value
zinv1_comp = row[26].value
zinv1_zw = row[27].value
#子女2
zinv2_cw = row[29].value
zinv2_name = row[28].value
zinv2_birth = row[30].value
if (zinv2_birth != None and zinv2_birth != "" and isinstance(zinv2_birth, datetime)):
zinv2_birth = zinv2_birth.strftime('%Y/%m/%d')
zinv2_zz = row[31].value
zinv2_comp = row[32].value
zinv2_zw = row[33].value
#子女3
zinv3_cw = row[35].value
zinv3_name = row[34].value
zinv3_birth = row[36].value
if (zinv3_birth != None and zinv3_birth != ""and isinstance(zinv3_birth, datetime)):
zinv3_birth = zinv3_birth.strftime('%Y/%m/%d')
zinv3_zz = row[37].value
zinv3_comp = row[38].value
zinv3_zw = row[39].value
# 获取父母信息 TODO
fu_cw = "父亲"
fu_name = row[12].value
fu_birth = row[13].value
if (fu_birth != None and fu_birth != "" and isinstance(fu_birth, datetime)):
fu_birth = fu_birth.strftime('%Y/%m/%d')
fu_zz = row[14].value
fu_comp = row[15].value
fu_zw = row[16].value
mu_cw = "母亲"
mu_name = row[17].value
mu_birth = row[18].value
if (mu_birth != None and mu_birth != ""and isinstance(mu_birth, datetime)):
mu_birth = mu_birth.strftime('%Y/%m/%d')
mu_zz = row[19].value
mu_comp = row[20].value
mu_zw = row[21].value
data = xlrd.open_workbook(filename, formatting_info=True)
table = data.sheets()[0]
# 寻找家庭单元格位置
location_family = "";
r_title = table.col( 0)
#迭代所有的行
count = 0
for row2 in r_title:
count+=1
if row2.value == "家 庭 主 要 成 员 及 重 要 社 会 关 系 ":
location_family = count
break
# location_family为家庭关系所在的第一行(表头)
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 初始化字体
font.name = "宋体" # 设置字体名称
font.height = 180 # 设置字体大小，excel里的字体大小为9，那在这里要乘以20，为180
style.font = font # 将设置好的字体配置，放入我们新建的样式当中
borders = xlwt.Borders() # 初始化边框
borders.top = xlwt.Borders.THIN # THIN是细线框
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
style.borders = borders # 将设置好的边框格式，放入新建的样式中
alignment = xlwt.Alignment() # 初始化对齐方式
alignment.horz = xlwt.Alignment.HORZ_CENTER # horz,设置水平对齐方式,HORZ_CENTER为水平居中
alignment.vert = xlwt.Alignment.VERT_CENTER # vert，设置垂直对齐方式，VERT_CENTER为垂直居中
style.alignment = alignment
wb_wr = copy(data)
ws_wr = wb_wr.get_sheet(0)
#写入配偶信息
if peiou_name != None and peiou_name !="":
ws_wr.write(location_family, 1, peiou_cw, style)
ws_wr.write(location_family, 2, peiou_name, style)
ws_wr.write(location_family, 3, peiou_birth, style)
ws_wr.write(location_family, 4, peiou_zz, style)
ws_wr.write(location_family, 5, peiou_comp, style)
ws_wr.write(location_family, 7, peiou_zw, style)
location_family +=1
#写入子女信息
if zinv1_cw != None and zinv1_cw !="":
ws_wr.write(location_family, 1, zinv1_cw, style)
ws_wr.write(location_family, 2, zinv1_name, style)
ws_wr.write(location_family, 3, zinv1_birth, style)
ws_wr.write(location_family, 4, zinv1_zz, style)
ws_wr.write(location_family, 5, zinv1_comp, style)
ws_wr.write(location_family, 7, zinv1_zw, style)
location_family+=1
if zinv2_cw != None and zinv2_cw !="":
ws_wr.write(location_family, 1, zinv2_cw, style)
ws_wr.write(location_family, 2, zinv2_name, style)
ws_wr.write(location_family, 3, zinv2_birth, style)
ws_wr.write(location_family, 4, zinv2_zz, style)
ws_wr.write(location_family, 5, zinv2_comp, style)
ws_wr.write(location_family, 7, zinv2_zw, style)
location_family+=1
if zinv3_cw != None and zinv3_cw !="":
ws_wr.write(location_family, 1, zinv3_cw, style)
ws_wr.write(location_family, 2, zinv3_name, style)
ws_wr.write(location_family, 3, zinv3_birth, style)
ws_wr.write(location_family, 4, zinv3_zz, style)
ws_wr.write(location_family, 5, zinv3_comp, style)
ws_wr.write(location_family, 7, zinv3_zw, style)
location_family+=1
#写入父母信息
if fu_cw != None and fu_cw !="":
ws_wr.write(location_family, 1, fu_cw, style)
ws_wr.write(location_family, 2, fu_name, style)
ws_wr.write(location_family, 3, fu_birth, style)
ws_wr.write(location_family, 4, fu_zz, style)
ws_wr.write(location_family, 5, fu_comp, style)
ws_wr.write(location_family, 7, fu_zw, style)
location_family +=1
if mu_cw != None and mu_cw !="":
ws_wr.write(location_family, 1, mu_cw, style)
ws_wr.write(location_family, 2, mu_name, style)
ws_wr.write(location_family, 3, mu_birth, style)
ws_wr.write(location_family, 4, mu_zz, style)
ws_wr.write(location_family, 5, mu_comp, style)
ws_wr.write(location_family, 7, mu_zw, style)
location_family+=1
wb_wr.save(filePath+name+'.xls')
# 文件转为xlsx
exchange(filename)
#开始插入图片
#print(img_file)
isexist_img = os.path.exists(img_file)
if (isexist_img):
img = Image(img_file)
new_size = (115, 185)
img.width, img.height = new_size
wb3 = load_workbook(filename_x)
sheet3 = wb3["sheet1"]
sheet3.add_image(img, 'H5')
wb3.save(filename_x)
else:
print("没有照片："+filename)
# 文件转为xls
unexchange(filename_x, name)
else:
print("没找到:"+filename2)
def exchange(filename):
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(filename)
wb.SaveAs(filename+r"x", FileFormat = 51) #FileFormat = 51 is for .xlsx extension
wb.Close() #FileFormat = 56 is for .xls extension
excel.Application.Quit()
def unexchange(filename, name):
filename_list = filename.split(name)
f = filename_list[0] + r"out/" + name + r'.xls'
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(filename)
wb.SaveAs(f, FileFormat = 56) #FileFormat = 51 is for .xlsx extension
wb.Close() #FileFormat = 56 is for .xls extension
excel.Application.Quit()
def getImageFromExcel(filePath):
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(filePath)
num = random.randint(1,500)
output_file = "";
for sheet in workbook.Worksheets:
for i, shape in enumerate(sheet.Shapes):
if shape.Name.startswith('Picture'):
shape.Copy()
image = ImageGrab.grabclipboard()
image.convert('RGB').save(r'D:\img\{}.jpg'.format(num), 'jpeg')
output_file = 'D:\\img\\{}.jpg'.format(num)
num+=1
excel.Quit()
return output_file
if __name__ == '__main__':
print("Start...\n")
filePath = "C:\\Users\\THINKPAD\\Desktop\\src\\"
start(filePath)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
