Append multiple excel files sheet by sheet & column by column in python

Question

Please suggest how to append multiple excel files sheet by sheet & column by column, wherein all files have exactly same sheet & column names in python3.

                 **Input**                                **Output**

File name: abc.xlsx       File name: xyz.xlsx       File name: appended.xlsx
Sheet: One                Sheet: One                Sheet: One
Column: A & B             Column: A & B             Column: A & B
Rows: 100                 Rows: 100                 Rows: **200**
.                         .                         .
.                         .                         .
.                         .                         .
.                         .                         .
.                         .                         .
.                         .                         .
Sheet: Ten                Sheet: Ten                Sheet: Ten
Column: A & B             Column: A & B             Column: A & B
Rows: 100                 Rows: 100                 Rows: **200**

Answer 1

You can use python openpyxl module for this purpose.

from openpyxl import load_workbook

I don't know why you need a third file. You can append a file to another. Firstly, read one file with the following:

first_file_wb = load_workbook(/file_path, read_only=True) # wb stands for workbook
for sheet_name in first_file_wb.sheetnames: # You can iterate through sheets with sheetnames
    first_file_ws = first_file_wb[sheet_name]    # ws stands for worksheet
    # after this you have access to specific sheet.
    for row in first_file_ws.iter_rows():
        for cell in row:
            print('%s: cell.value=%s' % (cell, cell.value)) # You can write this values to a dict sheet by sheet.
            ...

After manipulating the values, you can append it other file.

second_file_wb = load_workbook(/file_path)
for sheet_name in second_file_wb.sheetnames:
    second_file_ws = first_file_wb[sheet_name]    # ws stands for worksheet
    last_row = second_file_ws.max_row # This will give you last row number of the file
    # At this point, you can append the values after that row.
    for key,value in your_dict_from_first_file.items():
        # I assume key is a counter to values but if it is not, you can create a counter here. Just be sure <key+max> gives first available row to append.
        second_file_ws.cell(row=key+max, column={column from dict}, value={value})
        # or 
        second_file_ws.cell(row=key+max, column={column_number_from_dict}).value = value