openpyxl: Append data to first empty column cell

Question

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 them into a single worksheet. With the following code I have been able to create a new worksheet and add data to it.

        # Open workbook and assign worksheet
        try:
            wb = openpyxl.load_workbook(metadata)
            shtEditionLNM = wb.worksheets[0]  # Edition date & latest NM
            shtChartsTitles = wb.worksheets[1]  # Charts & Titles
            shtDepthHeight = wb.worksheets[4]  # Depth & heights
            shtChartProj = wb.worksheets[7]  # Chart Projection
        except:
            raise SystemExit(0)

        new = wb.create_sheet()
        new.title = "MT_CHARTS INFO"
        new.sheet_properties.tabColor = "1072BA"
        shtMeta = wb.get_sheet_by_name("MT_CHARTS INFO")

        for row in shtChartsTitles.rows:
            shtMeta.append([row[0].value, row[1].value, row[2].value, row[4].value])
        for row in shtEditionLNM.rows:
            shtMeta.append([row[3].value, row[4].value])


        wb.save('OW - Quarterly Extract of Metadata for Raster Charts Dec 2015.xlsx')

This works without any errors and I can see the data saved to my new workbook. However when I run a second loop and append values they are appended to cell A3169 whereas I actually want them to populate from E1.

My question boils down to 'is there a way I can append to a new column instead of a new row?'

Thanks in advance!

score 9 · Accepted Answer · answered Apr 08 '16 at 17:09

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.

score 1 · Answer 2 · edited May 23 '17 at 11:46

1

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=0
    for row in shtEditionLNM.rows:
        x+=1
        shtMeta.cell(coordinate="E{}".format(x)).value = row[3].value
        shtMeta.cell(coordinate="F{}".format(x)).value = row[4].value

edited May 23 '17 at 11:46

Community

1
1

answered Apr 11 '16 at 10:33

gwright170

75
1
1
8

2

My advice is to stop using `.cell(coordinate=…)` for anything as this is now deprecated. – Charlie Clark Apr 11 '16 at 15:08
@CharlieClark thank you, was not aware of this. I have now used the method you suggested and can see I should of used this in the first place to avoid string formatting! It's working great. P.S I've just realised you maintain openpyxl so i'd like to thank you as it's been a massive help in my role, do you or the plugin have a donation page? – gwright170 Apr 11 '16 at 16:06
Glad you find it useful. We don't have a specific donation page but do accept them willingly. Best drop me an e-mail for details. – Charlie Clark Apr 12 '16 at 07:06

score 0 · Answer 3 · answered Jul 25 '20 at 14:16

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.

openpyxl: Append data to first empty column cell

3 Answers3

Linked