Reading & Writing Excel Files with openpyxl

 What You’ll Learn

In this lesson, we will:

• Create a new Excel file.

• Add data (write).

• Save the file.

• Reopen the file and read the data.

We will use the openpyxl library in Python.
openpyxl helps us create, edit, and read Excel files (.xlsx).

Required

pip install openpyxl

 Code Example

from openpyxl import Workbook, load_workbook

def excel_read_write(file_path):
    """Read and write Excel files with openpyxl"""
    # Create new workbook
    wb = Workbook()
    ws = wb.active
    ws.title = "Data"
    
    # Write sample data
    data = [
        ["Name", "Age", "Department"],
        ["Alice", 28, "Marketing"],
        ["Bob", 32, "Engineering"]
    ]
    
    for row in data:
        ws.append(row)
    
    # Save the file
    wb.save(file_path)
    print(f"Created new Excel file: {file_path}")
    
    # Read the file
    wb = load_workbook(file_path)
    ws = wb.active
    
    print("nFile contents:")
    for row in ws.iter_rows(values_only=True):
        print(row)

excel_read_write("sample_data.xlsx")

 Step-by-Step Explanation

1. Import Library

   from openpyxl import Workbook, load_workbook
   

   • Workbook → used to create a new Excel file.

   • load_workbook → used to open an existing Excel file.

2. Create New File

   wb = Workbook()
   ws = wb.active
   ws.title = "Data"
   

   • Creates a new Excel workbook.

   • Gets the first sheet (active).

   • Renames the sheet to “Data”.

3. Add Data

   data = [
       ["Name", "Age", "Department"],
       ["Alice", 28, "Marketing"],
       ["Bob", 32, "Engineering"]
   ]
   for row in data:
       ws.append(row)
   

   • Makes a list of rows (like a table).

   • Adds each row to the Excel sheet.

4. Save File

   wb.save(file_path)
   

   • Saves the workbook to the given path.

   • Example: sample_data.xlsx.

5. Read File

   wb = load_workbook(file_path)
   ws = wb.active
   for row in ws.iter_rows(values_only=True):
       print(row)
   

   • Opens the saved file.

   • Reads each row and prints the values.

 Example Output

Created new Excel file: sample_data.xlsx

File contents:
('Name', 'Age', 'Department')
('Alice', 28, 'Marketing')
('Bob', 32, 'Engineering')

 Key Takeaways

• Workbook() → create new Excel.

• ws.append() → add rows of data.

• wb.save() → save Excel file.

• load_workbook() → open existing file.

• iter_rows() → read rows easily.

 With this, you can create and read Excel files in Python.