⚙️ Automating Data Entry in Excel with Python

Data entry is often one of the most tedious parts of any reporting or data management task. The good news? With Python and the openpyxl library, you can automate Excel data entry in just a few lines of code. Whether it's logging daily metrics, updating sales numbers, or inserting form responses, this guide will show you how to simplify the job.

📦 What You Need

Python 3
openpyxl: Python library to work with .xlsx files (Excel 2010+)

Install `openpyxl`

pip install openpyxl

📝 Scenario: Append Daily Sales Data

Imagine you’re tracking daily sales in a spreadsheet. Every day, you want to add a new row with:

Date
Product
Quantity Sold
Revenue

Instead of opening Excel manually, you can let Python handle it.

✅ Step-by-Step: Append Data Automatically

1. Load or Create the Workbook

from openpyxl import Workbook, load_workbook
import os

filename = 'daily_sales.xlsx'

# Check if file exists
if os.path.exists(filename):
    wb = load_workbook(filename)
    ws = wb.active
else:
    wb = Workbook()
    ws = wb.active
    # Create headers
    ws.append(["Date", "Product", "Quantity", "Revenue"])

2. Enter New Data Programmatically

from datetime import date

# Sample data
today = date.today().isoformat()
product = "Widget A"
quantity = 5
price_per_unit = 20
revenue = quantity * price_per_unit

# Append new row
ws.append([today, product, quantity, revenue])

3. Save the Workbook

wb.save(filename)

🔁 Automate This Daily

You can run this script:

Manually each morning
As a scheduled task using Task Scheduler (Windows) or cron (Linux/macOS)
From a web form (e.g., using Flask + Excel integration)
From an email or Google Sheets pull (with APIs)

🧠 Tip: Find the First Empty Row

If you need to insert in the first blank row (e.g., if the sheet has gaps):

def get_next_row(ws):
    for row in range(1, ws.max_row + 2):
        if not ws.cell(row=row, column=1).value:
            return row

next_row = get_next_row(ws)
ws.cell(row=next_row, column=1, value=today)
ws.cell(row=next_row, column=2, value=product)
ws.cell(row=next_row, column=3, value=quantity)
ws.cell(row=next_row, column=4, value=revenue)

🔐 Bonus: Add Timestamps or User Info

You can also log who entered the data and when:

import getpass
from datetime import datetime

user = getpass.getuser()
timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

# Add to sheet
ws.append([today, product, quantity, revenue, user, timestamp])

Just update the header row:

ws.append(["Date", "Product", "Quantity", "Revenue", "User", "Timestamp"])

✅ Summary

Python + openpyxl is a game-changer when it comes to automating repetitive Excel tasks. You can:

Insert new rows
Append data with timestamps
Schedule the script to run on its own

Say goodbye to manual updates and hello to smart automation.

Search This Blog