Recording the names of pictures from certain folders in Excel using Python

Question

I need to write the names of photos from a certain folder in 2 columns of an excel file, the writing should be gradual, that is, A1:B1, A2:B2 and so on

pictures

result

I would appreciate any help or any information that would help me

Any library that writes to Excel files (openpyxl, xlsxwriter, etc.) — BigBen, Sep 01 '22 at 14:20

score 0 · Answer 1 · answered Sep 01 '22 at 15:00

You should look at How do I list all files of a directory? and create a list of the filenames from the dir using os library

you will also as stated by BigBen need to use a library that writes to Excel files. You could also write the list to a dataframe before writing that dataframe to an excel file

score 0 · Accepted Answer · answered Sep 04 '22 at 17:39

Assuming all the files in the Pictures directory have the same naming format, this should work:

import os
import openpyxl
from openpyxl.styles import Border, Side, Alignment, Font

# Get a list of the filenames in the directory
filenames = os.listdir(r"C:\Users\Dwigt Rortugal\Desktop\Pictures")

# Used to sort the list of filenames by the number between "-" and ".png"
def get_filename_number(filename):
    left = filename.find("-") + 1
    right = filename.find(".png", left)
    return int(filename[left:right])

# Sort the filenames by the number between "-" and ".png"
filenames.sort(key=get_filename_number)

# Create a new spreadsheet
wb = openpyxl.Workbook()
ws = wb.active

# Create the 2 column headers - bold, centered, with a border
font = Font(bold=True)
alignment = Alignment(horizontal='center')
side = Side(border_style="thin")
border = Border(left=side, right=side, top=side, bottom=side)

cell = ws["A1"]
cell.value = "number1"
cell.border = border
cell.alignment = alignment
cell.font = font

cell = ws["B1"]
cell.value = "number2"
cell.border = border
cell.alignment = alignment
cell.font = font

# Make the columns wider
ws.column_dimensions["A"].width = 14
ws.column_dimensions["B"].width = 14

# Write the filenames to the spreadsheet, starting at A1, then B1, A2, B2, etc
# Getting the correct cell row and column is achieved by using divmod
columns = 2
starting_row = 2
starting_column = 1
for i, filename in enumerate(filenames):
    row, column = divmod(i, columns)
    ws.cell(row=row+starting_row, column=column+starting_column).value = filename

# Save the spreadsheet to disk
wb.save(r"C:\Users\Dwigt Rortugal\Desktop\Picture Filenames.xlsx")

# Open the new spreadsheet
os.startfile(r"C:\Users\Dwigt Rortugal\Desktop\Picture Filenames.xlsx")

This is the spreadsheet:

Recording the names of pictures from certain folders in Excel using Python

2 Answers2