Openpyxl: Can't write part of string into excel (ValueError)

Question

So i created a list signal containing numbers and strings which i want to write into an excel sheet. Therefore im using the following for loop:

columns = list(string.ascii_uppercase)[:26]
row = 3
for j in range(len(columns)):
    ws[columns[j] + str(row)] = a[j]
row += 1

I create the list a and write each data into a own column of a row. Then i update the row for the next list. My problem now is, when i created the code everything worked fine but now i always get the same error in some files: ValueError: Cannot convert 'THE-DATA' to Excel. This string is only a part of the whole string and even if i change the - to _ i will get an error. But if i create an test code i also have no problems with writing everything into an excel sheet. Has anyone an idea why i could get this error message after changing nothing in the code?

Test code (only writing one list into the excel sheet):

columns = list(string.ascii_uppercase)[:6]
a_test = [1, 3, "sa", "ABC DEF THE-DATA",7,3]
row = 3  # starting row of the Excel sheet

for j in range(len(columns)):
    ws[columns[j] + str(row)] = a_test[j]

wb.save(fr"Test121314.xlsx")

Answer 1

I'm not sure what the problem is exactly, but you are trying to populate those cells in a rather unusual way. Give it a go:

# adjust max_row to your liking
# note that row and column indices in openpyxl are 1-based
for row in ws.iter_rows(min_row=3, min_col=1, max_row=3, max_col=26):
    j = 0    
    for cell in row:
        try:
            cell.value = a[j]
        except: # if the type of a[j] is actually not a string or any other acceptable datatype
            cell.value = str(a[j])

        j += 1