Created
April 1, 2020 15:16
-
-
Save mujeebishaque/b2afda874e68f0dae860e48ae7919ef4 to your computer and use it in GitHub Desktop.
write and position multiple images in excel using openpyxl
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| def write_excel_second(self, folder_name=None, images=None): | |
| import openpyxl | |
| from openpyxl import load_workbook | |
| from openpyxl.drawing.spreadsheet_drawing import AbsoluteAnchor | |
| from openpyxl.drawing.xdr import XDRPoint2D, XDRPositiveSize2D | |
| from openpyxl.utils.units import pixels_to_EMU, cm_to_EMU | |
| # print('========>', folder_name, images) | |
| if images is None or len(images) == 0: | |
| return | |
| wb = openpyxl.Workbook() | |
| ws1 = wb.create_sheet('primary_sheet') | |
| img1 = openpyxl.drawing.image.Image(str(images[0])) | |
| p2e = pixels_to_EMU | |
| h, w = img1.height, img1.width | |
| position = XDRPoint2D(p2e(500), p2e(500)) | |
| size = XDRPositiveSize2D(p2e(h), p2e(w)) | |
| img1.anchor = AbsoluteAnchor(pos=position, ext=size) | |
| ws1.add_image(img1) | |
| # img1.anchor = ws1.cell(row=2, column=1).coordinate | |
| # ws1.add_image(img1, 'B1') | |
| # wb.save(self.FOLDER_PATH/'test_output.xlsx') | |
| # wb = load_workbook(filename='test_output.xlsx') | |
| # ws2 = wb['primary_sheet'] | |
| img2 = openpyxl.drawing.image.Image(str(images[1])) | |
| # img2.anchor = ws1.cell(row=5, column=5).coordinate | |
| p2e = pixels_to_EMU | |
| h, w = img1.height, img1.width | |
| position = XDRPoint2D(p2e(500), p2e(500)) | |
| size = XDRPositiveSize2D(p2e(h), p2e(w)) | |
| img2.anchor = AbsoluteAnchor(pos=position, ext=size) | |
| ws1.add_image(img2, 'C2') | |
| wb.save(self.FOLDER_PATH/'test_output.xlsx') | |
| def write_excel(self, folder_name=None, image_paths=None): | |
| # Importing packages and storing string for image file | |
| import pandas as pd | |
| import xlsxwriter | |
| from numpy import random | |
| image_files = image_paths | |
| print(folder_name, image_files) | |
| # Creating a fictitious data set since the actual data doesn't matter | |
| dataframe = pd.DataFrame(random.rand(5,2),columns=['a','b']) | |
| # Opening the xlsxwriter object to a path on the C:/ drive | |
| writer = pd.ExcelWriter(self.FOLDER_PATH/'file.xlsx', engine='xlsxwriter') | |
| dataframe.to_excel(writer, sheet_name = 'Arbitrary', startrow=3) | |
| # Accessing the workbook / worksheet | |
| workbook = writer.book | |
| worksheet = writer.sheets['Arbitrary'] | |
| # Inserting the image into the workbook in cell A1 | |
| count = 0 | |
| for image in image_files: | |
| count += 1 | |
| worksheet.insert_image(f'A{count}', image) | |
| # Closing the workbook and saving the file to the specified path and filename | |
| writer.save() | |
| def write_excel_writer(self, folder_name=None, images=None): | |
| from PIL import Image | |
| import xlsxwriter | |
| filename = "Where are you now.xlsx" | |
| if images is None or len(images) == 0: | |
| return | |
| img1 = Image.open(str(images[0])) | |
| imgwidth1, imgheight1 = img1.size | |
| workbook = xlsxwriter.Workbook(filename) | |
| worksheet = workbook.add_worksheet('Summary') | |
| worksheet.insert_image('A1', str(images[0])) | |
| worksheet.insert_image('A1', str(images[1]),{'y_offset':imgheight1}) | |
| worksheet.write(str(self.FOLDER_PATH) + sep + filename) | |
| workbook.close() | |
| def real_python_writer(self, folder_name=None, images=None): | |
| if images is None or len(images) ==0: | |
| return | |
| import openpyxl | |
| from openpyxl.drawing.image import Image | |
| folder_name = str(folder_name).rsplit(sep, 1)[-1] | |
| default_location = self.FOLDER_PATH/folder_name/'output.xlsx' | |
| workbook = openpyxl.Workbook() | |
| # Let's use the hello_world spreadsheet since it has less data | |
| # workbook = load_workbook(filename="hello_world.xlsx") | |
| sheet = workbook.active | |
| count = 0 | |
| for image in images: | |
| proto_image = Image(str(image)) | |
| # A bit of resizing to not fill the whole spreadsheet with the logo | |
| proto_image.height = 250 | |
| proto_image.width = 300 | |
| if count == 0: | |
| proto_image.anchor = 'A3' | |
| if count == 1: | |
| proto_image.anchor = 'F3' | |
| if count == 2: | |
| proto_image.anchor = 'L3' | |
| if count == 3: | |
| proto_image.anchor = 'A20' | |
| if count == 4: | |
| proto_image.anchor = 'F20' | |
| if count == 5: | |
| proto_image.anchor = 'L20' | |
| if count == 6: | |
| proto_image.anchor = 'A43' | |
| if count == 7: | |
| proto_image.anchor = 'F43' | |
| sheet.add_image(proto_image, proto_image.anchor) | |
| count += 1 | |
| if Path.exists(default_location): | |
| Path.unlink(default_location) | |
| workbook.save(default_location) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
real_python_writer() method works only, others are there just to learn from.