Python Excel openpyxl: A Practical Guide to Automate Excel Tasks

by

python excel openpyxl!!! Whether you’re a beginner learning Python or someone trying to automate your workflow, working with Excel files using Python is a game-changer. From parsing sales reports to exporting crawled data, Excel remains one of the most practical formats in the business world.

python openpyxl

In this post, we’ll go through:

  • How to load Excel files in Python
  • How to save data into Excel files
  • Real-life use cases including directory listings and Netflix Top 10 rankings

Let’s jump in. 🚀

🛠️ Prerequisite: Installing openpyxl

Before anything else, you’ll need the openpyxl library, which is widely used to work with .xlsx files.

PowerShell
pip install openpyxl

openpyxl supports reading/writing Excel 2010 xlsx/xlsm/xltx/xltm files.

https://openpyxl.readthedocs.io/en/stable/#

https://pypi.org/project/openpyxl

📥 Loading Excel Files in Python with openpyxl

Let’s say you have an Excel file named apt.xlsx. The following code reads and prints the entire contents of the active sheet.

Python
import openpyxl

filename = "wizpread.xlsx"
wb = openpyxl.load_workbook(filename)
sheet = wb.active

for r in range(1, sheet.max_row + 1):
    for c in range(1, sheet.max_column + 1):
        print(sheet.cell(r, c).value, end=" ")
    print()

What’s going on here?

  • load_workbook() loads the Excel file into memory.
  • wb.active accesses the first active worksheet.
  • sheet.cell(row, column).value fetches each cell’s value.
  • range() starts at 1 (not 0) because Excel’s row/column indexes start at 1.

If you run this script, you’ll see your spreadsheet content printed row-by-row in the terminal.

💾 Saving Data to Excel in Python with openpyxl

Now, let’s write something to Excel instead. This time, we’ll create a new file and fill it with simple (row, column) coordinates.

Python
import openpyxl

filename = "save_excel.xlsx"
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = "Export"

for r in range(1, 10):
    for c in range(1, 10):
        sheet.cell(r, c).value = f"({r}, {c})"

wb.save(filename)

The result? A new Excel file called save_excel.xlsx with a 9×9 grid of text values like (1, 1), (1, 2), and so on.

📁 Real-Life Example: Export Folder Contents to Excel

Need to export the contents of a folder — including file names, sizes, and timestamps — into Excel? Here’s a fully working example:

Python
import os
import openpyxl
import time
from datetime import datetime

def recursive_search(dir):
    results = []
    filenames = os.listdir(dir)
    for filename in filenames:
        full_path = os.path.join(dir, filename)
        if os.path.isdir(full_path):
            recursive_search(full_path)
        else:
            a_time = os.path.getatime(full_path)
            m_time = os.path.getmtime(full_path)
            a_time_str = datetime.strptime(time.ctime(a_time), "%a %b %d %H:%M:%S %Y").strftime("%Y-%m-%d %H:%M:%S")
            m_time_str = datetime.strptime(time.ctime(m_time), "%a %b %d %H:%M:%S %Y").strftime("%Y-%m-%d %H:%M:%S")
            file_size = os.stat(full_path).st_size
            results.append((f"{full_path}", file_size, a_time_str, m_time_str))
    return results

filename = "folder_list.xlsx"
target_dir = "c:\\temp"
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = "Folder List"

results = recursive_search(target_dir)
for r, d in enumerate(results):
    for c, col in enumerate(d):
        sheet.cell(r+1, c+1).value = col

wb.save(filename)

📈 BONUS: Export Netflix Top 10 Rankings

What if we want to fetch the Netflix global Top 10 and save them to Excel?
We can use flixpatrol.com as a data source.

Python
import requests
from bs4 import BeautifulSoup
import openpyxl

url = "https://flixpatrol.com/top10/netflix/world/"
headers = {"User-Agent": "Mozilla/5.0"}

res = requests.get(url, headers=headers)
soup = BeautifulSoup(res.text, "html.parser")

titles = soup.select("div.card div.filmtitle")
types = soup.select("div.card div.rankings-title span")

wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = "Netflix Top 10"
sheet.append(["Rank", "Type", "Title"])

for i in range(min(10, len(titles))):
    rank = i + 1
    content_type = types[i].text.strip() if i < len(types) else "Unknown"
    title = titles[i].text.strip()
    sheet.append([rank, content_type, title])

wb.save("netflix_top10.xlsx")

Whether you’re managing personal files or building data pipelines, Excel and Python can go hand in hand to simplify your tasks. No more copy-pasting or manual data entry!

Leave a Comment