Copy column of cell values from one workbook to another with openpyxl

Question

I am extracting data from one workbook's column and need to copy the data to another existing workbook.

This is how I extract the data (works fine):

wb2 = load_workbook('C:\\folder\\AllSitesOpen2.xlsx')
ws2 = wb2['report1570826222449']
#Extract column A from Open Sites
DateColumnA = []
for row in ws2.iter_rows(min_row=16, max_row=None, min_col=1, max_col=1):
    for cell in row:
        DateColumnA.append(cell.value)
DateColumnA

The above code successfully outputs the cell values in each row of the first column to DateColumnA

I'd like to paste the values stored in DateColumnA to this existing destination workbook:

#file to be pasted into
wb3 = load_workbook('C:\\folder\\output.xlsx')
ws3 = wb3['Sheet1']

But I am missing a piece conceptually here. I can't connect the dots. Can someone advise how I can get this data from my source workbook to the new destination workbook?

Possible duplicate of [Copy worksheet from one workbook to another one using Openpyxl](https://stackoverflow.com/questions/42344041/copy-worksheet-from-one-workbook-to-another-one-using-openpyxl) — Charlie Clark, Oct 17 '19 at 07:50

xiaxio · Answer 1 · 2019-10-12T16:56:07.777

1

Lets say you want to copy the column starting in cell 'A1' of 'Sheet1' in wb3:

wb3 = load_workbook('C:\\folder\\output.xlsx')
ws3 = wb3['Sheet1']

for counter in range(len(DateColumnA)):
    cell_id = 'A' + str(counter + 1)
    ws3[cell_id] = DateColumnA[counter]

wb3.save('C:\\folder\\output.xlsx')

edited Oct 12 '19 at 16:56

answered Oct 11 '19 at 23:08

xiaxio

631
1
10
15

I can't figure out how to post reply with reasonable format. So please see my 'answer' which is a response to your suggestion. – Kierk Oct 12 '19 at 14:08
Hi @Kierk. I modified the code to put the bracket where it belongs. As I don't have the excel file, I cannot completely simulate the scenario, but I hope this helps. Sorry for the mistake. – xiaxio Oct 12 '19 at 16:53
Thanks for helping. No matter what I tried I could not get the list into another worksheet with you your approach or the other person. But I found another way thankfully. But I will come back to this as I'd like to get your approach to work for future use. – Kierk Oct 12 '19 at 21:27

score 0 · Answer 2 · answered Oct 12 '19 at 19:21

I ended up getting this to write the list to another pre-existing spreadsheet:

for x, rows in enumerate(DateColumnA):
    ws3.cell(row=x+1, column=1).value = rows
    #print(rows)
    wb3.save('C:\\folder\\output.xlsx')

Works great but now I need to determine how to write the data to output.xlsx starting at row 16 instead of row 1 so I don't overwrite the first 16 existing header rows in output.xlsx. Any ideas appreciated.

score 0 · Accepted Answer · answered Oct 12 '19 at 23:12

I figured out a more concise way to write the source data to a different starting row on destination sheet in a different workbook. I do not need to dump the values in to a list as I did above. iter_rows does all the work and openpyxl nicely passes it to a different workbook and worksheet:

row_offset=5
for rows in ws2.iter_rows(min_row=2, max_row=None, min_col=1, max_col=1):
        for cell in rows:
            ws3.cell(row=cell.row + row_offset, column=1, value=cell.value)
            wb3.save('C:\\folder\\DestFile.xlsx')

Hi @Kierk, glad you found your solution. Just an improvement: place the wb3.save('C:\\folder\\DestFile.xlsx') out of the for loops, I believe you only need to save at the end of all iterations, not on each one of them. — xiaxio, Oct 14 '19 at 12:16

Copy column of cell values from one workbook to another with openpyxl

3 Answers3