I've been trying to write a script to fetch images and paste them all together into one file. For this I use ZipFile from zipfile to extract .bin image files from a collection of .xlsx files and I planned to use openpyxl to paste them all into one new .xlsx file.
When I run this, I end up getting a KeyError: '.wmf'.
I tried converting the image type to .png (or other raster image types). This did fix the issue, but the .bin image files seem to act a bit like a vector type of image and I would like to keep this attribute.
I would love to know how to fix this, thank you!
Here is my code:
from zipfile import ZipFile
from pathlib import Path
import sys
import os
import xlsxwriter as xw
"Function to find location of images"
imgformats = ('bin')
def is_image(filename):
return any(filename.endswith(ext) for ext in imgformats)
"Create folder"
folders = ['imgsave']
for folder in folders:
if not os.path.exists(folder):
os.makedirs(folder)
"Group the files"
path = Path(".")
try:
files = path.glob('*.xlsx')
except PermissionError as e:
print(f'Permission Error: {str(e)}')
sys.exit()
"Set variables"
file_count = 0
overall_size = 0
data = []
"Loop over files and extract the images"
if not os.path.exists('xl'):
for file in files:
with ZipFile(file) as working_zip:
image_list = [name for name in working_zip.namelist() if is_image(name)]
for img in image_list:
overall_size = overall_size + working_zip.getinfo(img).file_size
file_count = len(image_list)
working_zip.extractall("", image_list)
for img in image_list:
os.rename(img, f'xl/media/{file}.bin')
data.append(file_count)
data.append(overall_size)
"Create list of pasting locations for in excel"
cellcycletemp = ['A', 'H', 'O', 'V', 'AC']
rows = 4
cellcycle = [cellcycletemp[n%rows] + str(n//rows+1+12*(n//rows)+1) for n in range(120)]
"Create excel file and paste .bin images into it"
wb = openpyxl.Workbook()
ws = wb.worksheets[0]
imagelist = [img for img in os.listdir('xl/media/.') if '.bin' in img]
for n, file in enumerate(imagelist):
img = openpyxl.drawing.image.Image('xl/media/' + file)
img.anchor = cellcycle[n]
ws.add_image(img)
wb.save('imgsave/combined images.xlsx')
This is the error I end up getting:
Traceback (most recent call last):
File "C:\<path>\test_zipandpaste.py", line 67, in <module>
wb.save('imgsave/combined images.xlsx')
File "C:\<path>\Python\Python310\lib\site-packages\openpyxl\workbook\workbook.py", line 407, in save
save_workbook(self, filename)
File "C:\<path>\Python\Python310\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook
writer.save()
File "C:\<path>\Python\Python310\lib\site-packages\openpyxl\writer\excel.py", line 275, in save
self.write_data()
File "C:\<path>\Python\Python310\lib\site-packages\openpyxl\writer\excel.py", line 94, in write_data
self.manifest._write(archive, self.workbook)
File "C:\<path>\Python\Python310\lib\site-packages\openpyxl\packaging\manifest.py", line 181, in _write
self._register_mimetypes(filenames=archive.namelist())
File "C:\<path>\Python\Python310\lib\site-packages\openpyxl\packaging\manifest.py", line 193, in _register_mimetypes
mime = mimetypes.types_map[True][ext]
KeyError: '.wmf'
Process finished with exit code 1
edit: found a mistake in the code I posted originally and fixed it.
I actually found a way to do it without openpyxl. Python has a build-in library xlsxwriter which can handle .wmf files and allows it to paste the .bin files as well.
import xlsxwriter as xw
wb = xw.Workbook('imgsave/combined images.xlsx')
ws = wb.add_worksheet()
imagelist = [img for img in os.listdir('xl/media/.') if '.bin' in img]
for n, file in enumerate(imagelist):
ws.insert_image(cellcycle[n], f'xl/media/{file}')
wb.close()