ZPython - Merge Excel Files

 

import os
import datetime
import globimport xlwings as xw


class ExcelMerger:
    def __init__(self, input_dir, output_file):
        self.input_dir = input_dir
        self.output_file = output_file

    def merge_files(self):
        """Merge all .xlsx files in the input directory sequentially."""
        files = [f for f in glob.glob(os.path.join(self.input_dir, '*.xlsx'))
                 if not os.path.basename(f).startswith('~$')]
        files.sort(reverse=True)

        wb_dest = xw.Book()
        # leave only one sheet
        while len(wb_dest.sheets) > 1:
            wb_dest.sheets[-1].delete()

        for file in files:
            member_name = os.path.splitext(os.path.basename(file))[0].split('-')[-1]
            print(f"Copying {file} -> {member_name}")

            wb_src = xw.Book(file)
            ws_src = wb_src.sheets[0]

            ws_src.copy(after=wb_dest.sheets[0])
            wb_src.close()

            wb_dest.sheets[1].name = member_name

        # delete the first sheet
        wb_dest.sheets[0].delete()

        wb_dest.save(self.output_file)
        wb_dest.close()
        print(f"All files merged into {self.output_file}")


if __name__ == '__main__':
    input_dir = rf'D:\VSCodeWorkspace\WeeklyReports\input'
    date_str = datetime.datetime.today().strftime('%m%d')
    output_file = rf'D:\VSCodeWorkspace\WeeklyReports\output\WeeklyReport-{date_str}.xlsx'

    merger = ExcelMerger(input_dir, output_file)
    merger.merge_files()