How to copy and paste data from each excel sheet into a final sheet using Python?

Question

I have 7 excel sheets in one workbook and I am trying to copy and paste the data from each excel sheet into my final sheet. the code below creates the final sheet called 'Final Sheet' but does not copy any of the data from each sheet. I need a loop to go through each sheet and copy and paste the data into the final sheet but don't know how to do it.

Sheet 1 = North America, Sheet 2 = Japan, Sheet 3 = China etc

`#create final list sheet
 open = openpyxl.load_workbook(filepath)
 ws2 = open.create_sheet('Final List') # this creates the final sheet
 open.save(filepath)`

`#put data into final list
 wb = openpyxl.load_workbook(filepath)
 sheet1 = open.get_sheet_by_name('North America')
 finalListSheet = open.get_sheet_by_name('Final List')
 wb.save(filepath)`

probitaille · Accepted Answer · 2019-03-22 20:13:17Z

1

A similar question was asked here: Python Loop through Excel sheets, place into one df

I simplify this here. This method use Pandas:

import pandas as pd

sheets_dict = pd.read_excel(filepath, sheetname=None)

full_table = pd.DataFrame()

//Loop in sheets
for name, sheet in sheets_dict.items():
    sheet['sheet'] = name
    full_table = full_table.append(sheet)

//Need to save the DF in your Final Sheet

Here's another question about how to save dataframe (DF) in specific Excel sheet: Pandas Dataframe to excel sheet

answered Mar 22, 2019 at 20:13

probitaille

2,0372 gold badges23 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zack Over a year ago

thanks - when I save the DF to the final sheet it removed all my other sheets. any help??

probitaille Over a year ago

You should look at how to add new sheet to Excel file for that part: stackoverflow.com/questions/40385689/…

Collectives™ on Stack Overflow

How to copy and paste data from each excel sheet into a final sheet using Python?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related