Home > Net >  How to merge multiple xlsx files into one single xlsx file with Different sheets by openpyxl
How to merge multiple xlsx files into one single xlsx file with Different sheets by openpyxl

Time:01-28

I would like to create one master_xlsx file which would consist of many smaller xlsx files.

I have xlsx files:

  • users_general_inf.xlsx enter image description here
  • users_dates_inf.xlsx enter image description here
  • users_controls_inf.xlsx enter image description here

I would like master_xlsx to contain all files in such a way that one xlsx file is one sheet with its sheet_name and data from the file preserved enter image description here

My code:

import openpyxl

# --- Creating the master excel
# ------ create the master excel workbook
wb_master = openpyxl.Workbook()
# ------ create the master excel worksheet
wb_master_sheet = wb_master.active
# ------ import .xlsx files
# --------- users_general_inf.xlsx
wb_01 = openpyxl.load_workbook('./tmp/users_general_inf.xlsx')
wb_01_sheet = wb_01['users_general_inf']
# --------- users_dates_inf.xlsx
wb_02 = openpyxl.load_workbook('./tmp/users_dates_inf.xlsx')
wb_02_sheet = wb_02['users_dates']
# --------- users_controls_inf.xlsx
wb_03 = openpyxl.load_workbook('./tmp/users_controls_inf.xlsx')
wb_03_sheet = wb_03['userAccountControl']


xlsx_files = [wb_01, wb_02, wb_03] # all xlsx files to be merged


for f in xlsx_files:
    ? - universal code independent of the number of xlsx files

# ------ saving the master excel workbook
wb_master.save('./reports/master_users_inf.xlsx')

As I am just learning PYTHON I will appreciate any help related to this project.

CodePudding user response:

You're close, you just need to implement (inside your for loop) create_sheet to create a new sheet in the master file and then use iter_rows to grab all the values from all the rows.

Here is a proposition (without your comments to make a short code) :

from openpyxl import load_workbook, Workbook

wb_master = Workbook()

xlsx_files = ['./tmp/users_general_inf.xlsx',
              './tmp/users_dates_inf.xlsx',
              './tmp/users_controls_inf.xlsx']

for f in xlsx_files:
    sub_wb = load_workbook(f)
    sub_ws = sub_wb.active
    
    wb_master_sheet = wb_master.create_sheet(sub_ws.title)
    
    for row in sub_ws.iter_rows():
        for cell in row:
            wb_master_sheet[cell.coordinate].value = cell.value
    
   #wb_master_sheet.column_dimensions["A"].width = 20 #uncomment this line to set a width    

wb_master.remove_sheet("Sheet") #or del wb_master["Sheet"] 
wb_master.save('./reports/master_users_inf.xlsx')
  • Related