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 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!

回答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.

回答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=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

回答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.