How to Merge all worksheets within the same excel workbook using Xlwings & Python

user_9344053 0
Viewed 170

***Update

Thank you all for your input! Mouwsy's code is able to merge all worksheets within a Excel Workbook but how would this be done if the input is multiple Excel Workbooks? The output file only seems to be created from one input file instead of going through the loop. For example if there's 3 Excel Workbooks as the Input, I'm trying to have it so the Output is one Workbook containing 3 Worksheets - each Worksheet being the merged data.

import xlwings as xw
import glob
import sys

folder = sys.argv[1]
inputFile = sys.argv[2]
outputFile = sys.argv[3]

path = r""+folder+""

excel_files = glob.glob(path + "*" + inputFile + "*")  
with xw.App(visible=False) as app:
    for excel_file in excel_files:
        wb_init = xw.Book(excel_file)
        wb_res = xw.Book()
        sheets = wb_init.sheets
        ws_res = wb_res.sheets[0]
    
        for ws in sheets:
            ws.used_range.copy()
            ws_res.used_range[-1:,:].offset(row_offset=1).paste()
        ws_res["1:1"].delete() 
        wb_res.save(outputFile)
        wb_res.close(); wb_init.close()

*OLD I'm trying to Merge all Worksheets within the same Excel Workbook using Xlwings if anyone could please advise on how this could be done?

The code below is able to grab all worksheets and combine them into a created output file but the worksheet tabs remain separated instead of being merged.

import xlwings as xw
import glob
import sys

folder = sys.argv[1]
inputFile = sys.argv[2]
outputFile = sys.argv[3]

path = r""+folder+""

excel_files = glob.glob(path + "*" + inputFile + "*")   
with xw.App(visible=False) as app:
    combined_wb = app.books.add()
    for excel_file in excel_files:
        print(excel_file)
        wb = app.books.open(excel_file)
        for sheet in wb.sheets:
            sheet.copy(after=combined_wb.sheets[0])
        wb.close()
    combined_wb.sheets[0].delete()
    combined_wb.save(outputFile)
    combined_wb.close()
xlwings python-3.x excel python
2 Answers


UPDATED**
Updated example code.
Data from each sheet in each workbook in your path is pasted down the default sheet in the created combined workbook. Each workbook contents is separated by 'row_separation' rows.
Each sheet in the workbook is pasted across the combined sheet starting at Column 'A' and separated by 'col_separation' columns.
You'd need to determine where each original sheet gets pasted into Sheet1 in the combined excel file.

Note; this copy/paste should also include the data styling like font name, color, size and format.

row_separation = 100
col_separation = 20

row = 1
with xw.App(visible=False) as app:
    combined_wb = app.books.add()
    for excel_file in excel_files:
        col = 1
        print("Reading Excel file: " + excel_file)
        wb = app.books.open(excel_file)
        for sheet in wb.sheets:
            ws = wb.sheets[sheet]
            wb_name = str(wb.name)
            sheet_name = str(sheet.name)
            print("Extracting data from " + wb_name + "-" + sheet_name)
            combined_wb.sheets[0].range(row, col).value = 'Data from Workbook: ' + wb_name + ' Sheet: ' + sheet_name
            combined_wb.sheets[0].range(row, col).font.bold = True
            ws.used_range.copy()
            combined_wb.sheets[0].range(row+1, col).paste(paste='all')
            col += col_separation
        wb.close()
        row += row_separation
    combined_wb.save(outputFile)
    combined_wb.close()

Example sheet from 3 workbooks, workbook1 has 2 sheets, workbook2 has 3 sheets and workbook3 has 1 sheet. Separation is set to 10 for rows and 8 for columns for display.



Answer 2 ----------------------------------
Selecting the same worksheets from the same workbooks and adding to combined_wb as separate sheets.

with xw.App(visible=False) as app:
    combined_wb = app.books.add()
    first_sheet = combined_wb.sheets[0]
    for excel_file in excel_files:
        col = 1
        print("Reading Excel file: " + excel_file)
        wb = app.books.open(excel_file)
        for sheet in wb.sheets:
            ws = wb.sheets[sheet]
            wb_name = str(wb.name)
            sheet_name = str(sheet.name)
            print("Extracting Sheet from " + wb_name + "-" + sheet_name)

            ws.api.Copy(After=first_sheet.api)
            first_sheet = combined_wb.sheets[sheet_name]

        wb.close()
    # combined_wb.sheets[0].delete()  # Delete initial Sheet1 if not required 
    combined_wb.save(outputFile)
    combined_wb.close()

enter image description here

user_136641370

You could loop over the worksheets and use an index on the range object of used_range, i.e. used_range[-1:,:] to position the worksheets in an output workbook or rather output worksheet.

import xlwings as xw

path_input = r"test.xlsx"
path_save = r"result.xlsx"

with xw.App(visible=False) as app:
    wb_init = xw.Book(path_input)
    wb_res = xw.Book()
    ws_res = wb_res.sheets[0]

    for ws in wb_init.sheets:
        ws.used_range.copy()
        ws_res.used_range[-1:,:].offset(row_offset=1).paste()
    ws_res["1:1"].delete() # This is just to delete the first row, which is empty.
    wb_res.save(path_save)
    wb_res.close(); wb_init.close()

Example with data
Assume an Excel workbook with three worksheets.

Worksheet1:

a b c
foo cor wal
bar gra plu
baz ult xyz
qux ply thu

Worksheet2:

u v w x y z
12 92 86 22 80
23 29 74 21
16 10 75 67 61 99

Worksheet3:

I II III IV
1 5 9 1
2 6 0 6
3 7 3
4 8 2 0

Final output (i.e. wb_res):

a b c
foo cor wal
bar gra plu
baz ult xyz
qux ply thu
u v w x y z
12 92 86 22 80
23 29 74 21
16 10 75 67 61 99
I II III IV
1 5 9 1
2 6 0 6
3 7 3
4 8 2 0
user_139683920
Related