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

Question

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()

You are copying sheets as sheets to a new workbook, not the data on the sheet into a combined sheet. — moken, Jul 19 '22 at 01:27
Do not edit the meaning of the question after answers to the original questions have been given. I request that you roll back the question to its previous state so as not to invalidate the answer I gave. If you have a new unique problem, ask a completely new question, without defacing the original question. — mouwsy, Aug 02 '22 at 18:47

moken · Accepted Answer · 2022-07-29T10:07:04.950

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()

Awesome this was able to copy/grab the data from the first sheet. Do you have any suggestions on how I can copy the data from the second sheet? I'm getting an Index 1 out of range error when trying this below: — Nantourakis, Jul 20 '22 at 15:49
The example code is based on your previous question https://stackoverflow.com/questions/72972301/how-can-combine-or-merge-all-worksheets-within-an-excel-file-into-one-worksheet and the example provided by 'mouwsy' using 3 workbooks with one sheet in each book combined into one workbook with one sheet containing all the data from the three sheets. Your update to the code is trying to open a second sheet in the first workbook **ws2 = wb.sheets[1]** which doesn't exist in this example and apparently not in your book(s) hence the error. Do you have more than one sheet in each workbook? — moken, Jul 21 '22 at 00:33
Hi Moken. thanks for helping me look into this, yes each workbook has more than one sheet within — Nantourakis, Jul 22 '22 at 19:31
@Nantourakis I updated the code to handle more than one sheet in the workbook. An example of the expected output is also shown. — moken, Jul 23 '22 at 13:04
Thank you for all your time and help you've put into this @Moken! I have a feeling this might be a requirement later on and really appreciate your help. Instead of separating each workbook contents by rows within the same sheet, could the contents of each workbook be new worksheet tab? — Nantourakis, Jul 28 '22 at 22:18
@Nantourakis, copy sheets from different workbooks into the one single workbook; see answer 2 above. — moken, Jul 29 '22 at 09:57
Thank you so much for all your input @Moken, I was able to solve my requirement — Nantourakis, Aug 01 '22 at 19:51

mouwsy · Answer 2 · 2022-08-24T17:37:40.037

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

Thank You! This is what I'm looking for but am having a hard time when trying to do this with multiple excel workbooks as the the input. The output file is only reading/containing one merged worksheet instead of a worksheet for each input file. Would you know how this could be down where the final output equals one workbook containing one worksheet for each merged file? I've added what I tried to your answer — Nantourakis, Jul 28 '22 at 19:32
Thank you so much for all your input @Mouwsy, I was able to solve my requirement — Nantourakis, Aug 01 '22 at 19:51

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

2 Answers2

Linked

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

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

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