How can openpyxl write list data in sheet?

Question

I am using openpyxl to deal with some scientific data, when I was writing the list data to an excel file, I have a problem.

wb = openpyxl.Workbook(optimized_write=Ture)
ws_write = wb.create_sheet(0)

for i in range(row):
   ws_write.append([datalist[i][0]])

wb.save(filename='data.xlsx')

When it is done, seems like that it only can write the list into the first column of the xlsx file. How can I assign the same col of my datalist to the same col of xlsx file in the same sheet? Is there any reference to control the output?

score 11 · Answer 1 · answered Mar 31 '15 at 06:58

When using ws.append(row) you must insert pass in a whole either as sequence or as a generator. I don't understand your data structure but something like the following should work.

for row in datalist:
    ws.append(row)

If this is not sufficient then please provide more information about the structure of your data.

score 4 · Answer 2 · edited May 23 '17 at 12:15

4

You are only writing the first column of your data (col=0). In order to include all the data, either add an internal loop:

for i in range(row):
    for j in range(col):
       ws_write.append([datalist[i][j]])
wb.save(filename='data.xlsx')

or, write the entire row at a time:

for i in range(row):
   ws_write.append([datalist[i]])
wb.save(filename='data.xlsx')

I do not know that package to make sure that the output syntax is correct.

edit: after looking at this:Insert row into Excel spreadsheet using openpyxl in Python it appears that you need to do something like:

for i in range(row):
    for j in range(col):
       ws_cell(row=i,col=j).value = datalist[i][j]
wb.save(filename='data.xlsx')

edited May 23 '17 at 12:15

Community

1
1

answered Mar 30 '15 at 20:21

Alan Hoover

1,430
2
9
13

Hi, Dear Hoover, for the two ways you said above, I have tried them again(actually I have used these ways), but the problem is, it only can write all data into first column of the xlsx file, it can not be write the 2nd col of my datalist to beginning of 2nd column of the xlsx file. Anyway, Thanks for your helping! – GuangWu Mar 30 '15 at 22:09
@AlanHoover he wants to use `write_only` mode which writes rows at a time. – Charlie Clark Mar 31 '15 at 06:56

score 3 · Answer 3 · answered Apr 10 '20 at 10:23

If I understood your question correctly, you would need something like this:

myList = ['First','Second','Third','Fourth','Fifth']
wb = xl.load_workbook('test.xlsx')
ws= wb.active
ws.append(myList)

This will append every item from the list on each column from same row.

Hope that helps :)

How can openpyxl write list data in sheet?

3 Answers3

Linked