Hello, this is ricky. Do you think there is a real job of sticking screenshots to Excel? Until that day, I thought such a job was an urban legend. But just recently I was forced to do the job. The job of simply pasting images from a folder into Excel ... At that time, I suddenly noticed. Can this be automated programmatically? After investigating, I found that there is a library for pasting images in Excel and a library for reading the image folder and getting the file name! So I decided to go to the program.
Assumption: It is assumed that the image folder to be pasted already exists. This source code is executed by initializing the Excel path and the image folder path as variables. The process flow is to connect to the specified Excel, add a sheet to the end, and paste the image file on that sheet. In addition, how to name the sheet is represented by three numbers such as 1-1-1 according to the image folder. By the way, 1-1-1_1 and 1-1-1_2 are attached to the 1-1-1 sheet together. When the processing is completed, the character string is displayed.
Below is the source code.
png_file_name.py
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
import glob, os, re, cv2
'''
Description to shorten the interval of pasting
when the already pasted image is small
'''
def check_img_size(img):
img_check = cv2.imread(img)
img_check_hight = img_check.shape[0]
cell_interval = 0
if img_check_hight < 500:
cell_interval = 20
elif img_check_hight < 600:
cell_interval = 23
elif img_check_hight < 700:
cell_interval = 26
elif img_check_hight < 800:
cell_interval = 30
elif img_check_hight < 900:
cell_interval = 33
else:
print("ERROR There is a file of unexpected size.")
return cell_interval
def paste_image(ws2, right_num_groups):
before_cell_num = 0
for num in range(len(right_num_groups)):
capture_num = 2
if num != 0:
cell_size = check_img_size(right_num_groups[num - 1])
capture_num = before_cell_num + cell_size + 2
capture_area = 'A' + str(capture_num)
before_cell_num = capture_num
ws2.add_image(Image(right_num_groups[num]), capture_area)
def connect_excel(right_num_groups, folder_num):
wb = load_workbook(filename = excel_name)
sheet_name = str(folder_num + 1) + "-" + str(sheet_name_list[1]) + "-" + str(sheet_name_list[2])
sheet_name = sheet_name.rstrip("\'>[0-9]")
ws2 = wb.create_sheet(title = sheet_name)
ws2['A1'] = "* Attach a screen capture"
paste_image(ws2, right_num_groups)
wb.save(filename = excel_name)
# TODO: change excel's path and evidence folder path
excel_name = 'test.xlsx'
evidence_folder = "C:\\Users\\username\\Documents\\evidence"
folder_list = os.listdir(evidence_folder)
re_word = r'([0-9]-){2}[0-9]'
check_list = []
for folder_num in range(len(folder_list)):
num_folder = evidence_folder + "\\" + folder_list[folder_num]
png_list = os.listdir(num_folder)
for png_num in range(len(png_list)):
re_png_name = re.match(re_word, png_list[png_num])
sheet_name_list = str(re_png_name).split("-")
png_re_name = num_folder + "\\" + re_png_name.group() + "*"
right_num_groups = glob.glob(png_re_name)
if right_num_groups == check_list:
continue
connect_excel(right_num_groups, folder_num)
print(right_num_groups)
check_list = right_num_groups
print("Finish!!")
check_img_size(img) It evaluates the height of the target image and returns a numerical value. This is a description to prevent the images from overlapping or being too far apart when pasting to Excel.
paste_image(ws2, right_num_groups) This is the process of pasting an image. This is to evaluate the size of the image pasted earlier so that the images to be pasted with the variable before_cell_num do not overlap.
connect_excel(right_num_groups, folder_num) It is a process to connect to Excel. It connects to the specified Excel, adds a sheet, and inserts a character string.
main You can identify the image file, move the hierarchy, and get all the images with regular expressions by the glob library. And finally, when the process is finished, the character string is displayed.
This time, I started processing with images and started the program. I was worried about how to process the image, but I'm glad I was able to do it well. Looking back at the source code after finishing, I felt that there was a problem with how to name variables. I wonder if folder_num and num_folder exist at the same time ... However, I was purely happy when I saw the process work and all the images were pasted in Excel. I hope this code helps others. You can clone Code from GitHub. Please use it. Impressions are welcome. Thank you for reading this far.
Recommended Posts