1.将已知xlsx文件后缀直接修改为zip，这里不能使用xls文件，因为xls文件在修改后缀后内容不完整，他本身只是一个二进制文件。
def change_file_name(file_path, new_type='.zip'):
extend = os.path.splitext(file_path)[1]
file_name = os.path.basename(file_path) # 获取文件名
new_name = str(file_name.split('.')[0]) + new_type # 新的文件名，命名为：xxx.zip
dir_path = os.path.dirname(file_path) # 获取文件所在目录
new_path = os.path.join(dir_path, new_name) # 新的文件路径
if os.path.exists(new_path):
os.remove(new_path)
os.rename(file_path, new_path) # 保存新文件，旧文件会替换掉
file_zip = zipfile.ZipFile(new_path, 'r')
file_name = os.path.basename(new_path) # 获取文件名
zipdir = os.path.join(os.path.dirname(new_path), str(file_name.split('.')[0])) # 获取文件所在目录
for files in file_zip.namelist():
file_zip.extract(files, os.path.join(new_path, zipdir)) # 解压到指定文件目录
file_zip.close()
return new_path
2.读取指定sheet表在文件夹中的相应图片。在网上查找了很多相关方法，都没有针对图片顺序做处理的，只是单纯的提取图片，为了满足我的需求，通过xml文件中图片的末尾行排序来获取图片顺序，进而可以按序命名并存入数据库。这里图片只能针对PNG图片，在处理数据时发现其他类型图片在解压后的xml文件中有关顺序的xml文件组成不符合处理需求。（这里涉及到xml文件内容提取问题）
def read_img(zipfile_path):
dir_path = os.path.dirname(zipfile_path) # 获取文件所在目录
file_name = os.path.basename(zipfile_path) # 获取文件名
# 获取sheet2中所有图片名称
pic_dir = 'xl' + os.sep + 'worksheets'+ os.sep+'_rels' + os.sep+ 'sheet2.xml.rels'
pic_path = os.path.join(dir_path, str(file_name.split('.')[0]), pic_dir)
tree = ET.parse(pic_path)
root = tree.getroot()
draw_dir = []
for child in root:
draw_sheet2_path = child.attrib['Target']
pattern = re.compile(r'../drawings/(.*)')
draw_dir = re.findall(pattern, draw_sheet2_path)
print(draw_sheet2_path)
print(draw_dir)
break
draw_pic_dir = 'xl' + os.sep + 'drawings' + os.sep +'_rels'+os.sep+ draw_dir[0]+'.rels'
draw_path = os.path.join(dir_path, str(file_name.split('.')[0]), draw_pic_dir)
tree = ET.parse(draw_path)
root = tree.getroot()
pic_name_dict={}
for pic_name in root:
pic_name_path = pic_name.attrib['Target']
pic_id = pic_name.attrib['Id']
pic_name_dict[pic_id] = pic_name_path
tmp_pic_dir = 'xl' + os.sep + 'drawings' + os.sep + draw_dir[0]
tmp_path = os.path.join(dir_path, str(file_name.split('.')[0]), tmp_pic_dir)
tree = ET.parse(tmp_path)
root = tree.getroot()
id_row_off_dict = {}
for twoCellAnchor in root:
xdr_to = twoCellAnchor.getchildren()[1]
xdr_to_rowoff = xdr_to.getchildren()[2]
row_off = xdr_to_rowoff.text
xdr_pic = twoCellAnchor.getchildren()[2]
xdr_pic_blipfill = xdr_pic.getchildren()[1]
blip = xdr_pic_blipfill.getchildren()[0]
id_dict = blip.attrib
id = list(id_dict.values())[0]
id_row_off_dict[id] = int(row_off)
id_row_off_list= sorted(id_row_off_dict.items(), key=lambda x:x[1])
# 将列表中的元组转换成列表中的字典
word_freq_list=[]
for tmp_tuple in id_row_off_list:
word_pair_dict = {}
word_pair_list = list(tmp_tuple)
word_pair_dict[word_pair_list[0]] = word_pair_list[1]
word_freq_list.append(word_pair_dict)
img_list = []
for tmp_dic in word_freq_list:
rid = list(tmp_dic.keys())[0]
img_path =pic_name_dict[rid]
pattern = re.compile(r'../media/(.*)')
name = re.findall(pattern, img_path)
img_list.append(name[0])
return img_list
3.通过2获取的图片名称列表，在media文件夹中提取相应图片，并存入mongo数据库。
def img_save(zipfile_path,name_list,img_list,case_lib_id):
lib_id = int(case_lib_id)
dir_path = os.path.dirname(zipfile_path) # 获取文件所在目录
file_name = os.path.basename(zipfile_path) # 获取文件名
pic_dir = 'xl' + os.sep + 'media' # excel变成压缩包后，再解压，图片在media目录
pic_path = os.path.join(dir_path, str(file_name.split('.')[0]), pic_dir)
for i in range(len(img_list)):
if 'png' not in img_list[i]:
return False, '拓扑图片类型不符，应该为PNG格式', None
else:
filepath = os.path.join(pic_path, img_list[i])
pic = open(filepath, "rb")
pic_base64 = base64.b64encode(pic.read()).decode("utf-8")
pic_hash = hash(pic_base64)
topo_info_obj = TopoPicScheam.objects(case_lib_id = lib_id,pic_hash = pic_hash).first()
if topo_info_obj is not None:
if topo_info_obj.name == name_list[i]:
pass
else:
return False,'数据库中有该拓扑，且与导入文件中拓扑名称不一致',None
else:
topo_info_obj = TopoPicScheam.objects(case_lib_id=lib_id,name=name_list[i]).first()
if topo_info_obj is not None:
topo_info_obj.pic = 'data:image/*;base64,%s' % (pic_base64)
topo_info_obj.case_lib_id = lib_id
topo_info_obj.name = name_list[i]
topo_info_obj.pic_hash = pic_hash
topo_info_obj.save()
else:
topo_obj = TopoPicScheam()
topo_obj.pic = 'data:image/*;base64,%s'%(pic_base64)
topo_obj.case_lib_id = lib_id
topo_obj.name = name_list[i]
topo_obj.pic_hash = pic_hash
topo_obj.save()
pic.close()
return True,None,None
