👉 Explore All My Excel Solutions: [ Ссылка ]

𝗗𝗘𝗦𝗖𝗥𝗜𝗣𝗧𝗜𝗢𝗡
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
This tutorial will show you how to read & write closed Excel Files in Python using openpyxl. In particular, we will copy data from multiple Excel files in one Master workbook. After executing the Python script, openpyxl will create a new workbook that combines all the single Excel files.

✅ 𝗧𝗶𝗺𝗲𝘀𝘁𝗮𝗺𝗽𝘀:
00:00 | Problem Statement and Final Outcome
01:26 | Solution Approach
02:30 | Step 1
02:52 | Step 2
04:37 | Step 3
06:43 | Wrap Up

📝 𝗥𝗲𝘀𝗼𝘂𝗿𝗰𝗲𝘀:
Download the Python File & Excel Sample Files here [Google Drive]:
► [ Ссылка ]

◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️◼️
GistHub: [ Ссылка ]
👩‍💻 𝗣𝘆𝘁𝗵𝗼𝗻 𝗖𝗼𝗱𝗲:
from pathlib import Path # Standard Python Module
from openpyxl import load_workbook, Workbook # pip install openpyxl


# -- 1.STEP
# Get all excel file paths from given directory
SOURCE_DIR = "Daily_Reports" # e.g. r"C:\Users\Username\Desktop\Sample Files"
excel_files = list(Path(SOURCE_DIR).glob("*.xlsx"))

# -- 2.STEP:
# Iterate over all Excel files from step 1,
# access the worksheet and store the values in a dictionary
# values_excel_files = {['2021-01-01'] : [1,2,3, ..],
# ['2021-01-02'] : [1,2,3, ..]}
values_excel_files = {}
for excel_file in excel_files:
report_date = excel_file.stem.replace("_Report", "")
wb = load_workbook(filename=excel_file, read_only=True)
rng = wb["Sheet1"]["B2":"B19"]
rng_values = []
for cells in rng:
for cell in cells:
rng_values.append(cell.value)
values_excel_files[report_date] = rng_values

# -- 3.STEP:
# a) Iterate over all worksheets in the master workbook
# b) For each worksheet, iterate over defined Excel range (dates)
# c) If date matches with the key of dictionary (values_excel_files) then insert values & save workbook
wb = load_workbook(filename="Masterfile_Template.xlsx")
for ws in wb.worksheets:
clm = "B"
first_row = 3
last_row = len(ws[clm])
rng = ws[f"{clm}{first_row}:{clm}{last_row}"]
for cells in rng:
for cell in cells:
if cell.value in values_excel_files:
# Iterate over values (list inside the dictionary) and write values to column
for i, value in enumerate(values_excel_files[cell.value]):
cell.offset(row=0, column=i + 1).value = value
wb.save("Masterfile_Filled.xlsx")


𝗧𝗢𝗢𝗟𝗦 𝗔𝗡𝗗 𝗥𝗘𝗦𝗢𝗨𝗥𝗖𝗘𝗦
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🆓【𝗙𝗥𝗘𝗘】Excel Add-in (𝗠𝘆𝗧𝗼𝗼𝗹𝗕𝗲𝗹𝘁): [ Ссылка ]
📊 Dashboard Excel Add-In (𝗚𝗿𝗮𝗳𝗹𝘆): [ Ссылка ]
🎨 Cartoon Charts Excel Add-In (𝗖𝘂𝘁𝗲𝗣𝗹𝗼𝘁𝘀): [ Ссылка ]
🤪 Fun Emoji Excel Add-In (𝗘𝗺𝗼𝗷𝗶𝗳𝘆): [ Ссылка ]
📑 Excel Templates: [ Ссылка ]
🎓 My Courses: [ Ссылка ]
📚 Books, Tools, and More: [ Ссылка ]


𝗖𝗢𝗡𝗡𝗘𝗖𝗧 𝗪𝗜𝗧𝗛 𝗠𝗘
▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
🔗 LinkedIn: [ Ссылка ]
📸 Instagram: [ Ссылка ]
💻 GitHub: [ Ссылка ]
💬 Discord: [ Ссылка ]
📬 Contact: [ Ссылка ]


☕ 𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲?
If you want to support this channel, you can buy me a coffee here: [ Ссылка ]

свернуть