Openpyxl: Append Data To First Empty Column Cell

Background: I have an excel workbook containing metadata which spread across various worksheets. I need to take the relevant columns of data from the various worksheets and combine

Solution 1:

Not directly: ws.append() works with rows because this is the way the data is stored and thus the easiest to optimise for the read-only and write-only modes.

However, ws.cell(row=x, column=y, value=z) will allow you to do want you want. Version 2.4 (install from a checkout) will also let you work directly with columns by managing the assignment to cells for you: ws['E'] will return a tuple of the cells in the column up to the current ws.max_row; ws.iter_cols(min_col, min_row, max_col, max_row) will return a generator of columns as big as you need it.

Solution 2:

Thank you Charlie,

Your answer gave me the direction I needed to get this done. Referring to this question: how to write to a new cell in python using openpyxl

i've found out there are many ways to skin this cat - the method below is what I went for in the end!

    x=0forrowin shtEditionLNM.rows:
        x+=1
        shtMeta.cell(coordinate="E{}".format(x)).value=row[3].value
        shtMeta.cell(coordinate="F{}".format(x)).value=row[4].value

Solution 3:

I am new to openpyxl, but I believe we can convert a list to a list of tuple of each element, and then pass that object into the sheet.append() function:

L1=[a,b,c,d.....]
L2=[]

for a in L1:
   L2.append(tuple(a))

for a in L2:
    sheet.append(L2)

Please feel free to correct me.