0

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)
MARCOS SANTOS
  • 87
  • 1
  • 7
  • Does this answer your question https://stackoverflow.com/questions/59327230/how-to-add-a-list-of-data-into-an-excel-column-using-openpyxl ? – Abhyuday Vaish Apr 19 '22 at 03:33
  • Does this answer your question? [How can openpyxl write list data in sheet?](https://stackoverflow.com/questions/29354868/how-can-openpyxl-write-list-data-in-sheet) – Abhyuday Vaish Apr 19 '22 at 03:34
  • First item in oxx is a string, which is unexpected. [append](https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html?highlight=append#openpyxl.worksheet.worksheet.Worksheet.append) is expecting an iterable, please fix that. – Devesh Apr 19 '22 at 03:35
  • Thank you but it didn't work out! – MARCOS SANTOS Apr 19 '22 at 04:09
  • You should include the full exception so that we know where the problem is but I suspect `sheet1.append(item)` is the cause. You can only append "rows" of data. – Charlie Clark Apr 19 '22 at 09:55

1 Answers1

0

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)
IMCSAM
  • 32
  • 4