Write python list of files names to excel using openpyxl

Question

I have been trying to get the name of files in a folder on my computer and open an excel worksheet and write the file names in a specific column. However, it returns to me the following message of error. "TypeError: Value must be a list, tuple, range or generator, or a dict. Supplied value is <class 'str'>". The code is:

from openpyxl import load_workbook
import os 
import glob, os


os.chdir("/content/drive/MyDrive/picture")
ox = []
for file in glob.glob("*.*"):
    for j in range(0, 15):
        replaced_text = file.replace('.JPG', '')
        ox.append(replaced_text)
   
oxx = ['K', ox] #k is a column
   
file1 = load_workbook(filename = '/content/drive/MyDrive/Default.xlsx')
sheet1 = file1['Enter Data Draft']
    
for item in oxx:
    sheet1.append(item)

Answer 1

I've taken a slightly different approach but looking at your code the problem is with the looping.

The problem.

for item in oxx: sheet1.append(item)

When looping over the items in oxx, there are two items. 'K' and then a list with filenames (x15 each) in it. Openpyxl was expecting a different data structure for append. Its actually after a tuple of tuples. documentation here.

The solution

So not knowing what other data you might have on the worksheet I've changed the approach to hopefully satisfy the expected outcome.

I got the following to work as expected.

from openpyxl import load_workbook
import os 
import glob, os


os.chdir("/content/drive/MyDrive/picture")
ox = []
for file in glob.glob("*.*"):
    for j in range(0, 15):  # I've kept this in here assuming you wanted to list the file name 15 times?
        replaced_text = file.replace('.JPG', '')
        ox.append(replaced_text)

file_dir = '/content/drive/MyDrive/Default.xlsx'
file1 = load_workbook(filename = file_dir)
sheet1 = file1['Enter Data Draft']

# If you were appending to the bottom of a list that was already there use this
# last_row = len(sheet1['K'])
# else use this
last_row = 1  # Excel starts at 1, adjust if you had a header in that column

for counter, item in enumerate(ox):
    # K is the 11th column.
    sheet1.cell(row=(last_row + counter), column=11).value = item

# Need to save the file or changes wont be reflected
file1.save(file_dir)