openpyxl

### Python `openpyxl` Tutorial for Graduate Students with 20 Examples

**Overview:**
The `openpyxl` module is a Python library for reading and writing Excel files (`.xlsx`). It allows you to automate the manipulation of Excel workbooks, such as reading data, creating sheets, formatting cells, and more. This tutorial will guide you through 20 examples, using real-life scenarios involving Indian names and use cases such as managing employee records, exam scores, and financial data.

### Installation:

To use `openpyxl`, you need to install it first:

```bash
pip install openpyxl
```

### Example Dataset (Real-Life Use Case):
Imagine you are managing data for a small business in India. You have an Excel file with employee information such as **Name**, **Age**, **Position**, **Salary**, and **Date of Joining**.

**Excel File (`employees.xlsx`):**

| Name        | Age | Position      | Salary | Date of Joining |
|-------------|-----|---------------|--------|-----------------|
| Rohan Gupta | 28  | Data Analyst  | 55000  | 2021-04-10      |
| Priya Sharma| 32  | HR Manager    | 65000  | 2020-11-05      |
| Neha Verma  | 25  | Software Eng  | 72000  | 2022-01-15      |
| Aarav Patel | 29  | Accountant    | 60000  | 2021-06-25      |
| Kiran Rao   | 31  | Marketing Head| 85000  | 2019-09-12      |

### 1. **Reading an Excel File**

```python
import openpyxl

# Load an existing Excel workbook
wb = openpyxl.load_workbook('employees.xlsx')

# Select the active worksheet
ws = wb.active

# Read all rows
for row in ws.iter_rows(values_only=True):
    print(row)
```

### 2. **Accessing a Specific Sheet**

```python
# Access a specific sheet by name
ws = wb['Sheet1']

# Print the sheet title
print(ws.title)
```

### 3. **Writing to a New Excel File**

```python
# Create a new Excel workbook
new_wb = openpyxl.Workbook()

# Select the active worksheet
ws = new_wb.active

# Add data to cells
ws['A1'] = "Name"
ws['B1'] = "Age"
ws['A2'] = "Ravi Kumar"
ws['B2'] = 28

# Save the workbook
new_wb.save('new_employees.xlsx')
```

### 4. **Modifying Cell Values**

```python
# Modify a specific cell
ws['B2'] = 30

# Save the updated file
wb.save('employees.xlsx')
```

### 5. **Appending Rows to the Excel File**

```python
# Append a new row of data
new_employee = ['Rahul Singh', 27, 'Graphic Designer', 50000, '2022-09-15']
ws.append(new_employee)

# Save the updated file
wb.save('employees.xlsx')
```

### 6. **Iterating Over Rows and Columns**

```python
# Iterate over rows and print all data
for row in ws.iter_rows(min_row=2, max_row=6, min_col=1, max_col=5, values_only=True):
    print(row)
```

### 7. **Inserting Columns and Rows**

```python
# Insert a new column before the first column
ws.insert_cols(1)

# Insert a new row at position 3
ws.insert_rows(3)

# Save changes
wb.save('employees.xlsx')
```

### 8. **Deleting Columns and Rows**

```python
# Delete the second column (B)
ws.delete_cols(2)

# Delete the fourth row
ws.delete_rows(4)

# Save changes
wb.save('employees.xlsx')
```

### 9. **Merging Cells**

```python
# Merge cells from A1 to C1
ws.merge_cells('A1:C1')

# Save changes
wb.save('employees.xlsx')
```

### 10. **Unmerging Cells**

```python
# Unmerge the previously merged cells
ws.unmerge_cells('A1:C1')

# Save changes
wb.save('employees.xlsx')
```

### 11. **Formatting Cells**

```python
from openpyxl.styles import Font, Alignment

# Apply bold font to a cell
ws['A1'].font = Font(bold=True)

# Apply center alignment to a cell
ws['B1'].alignment = Alignment(horizontal='center')

# Save changes
wb.save('employees.xlsx')
```

### 12. **Setting Column Widths**

```python
# Set the width of column A
ws.column_dimensions['A'].width = 20

# Save changes
wb.save('employees.xlsx')
```

### 13. **Setting Row Heights**

```python
# Set the height of the first row
ws.row_dimensions[1].height = 25

# Save changes
wb.save('employees.xlsx')
```

### 14. **Freezing Panes**

```python
# Freeze the first row
ws.freeze_panes = 'A2'

# Save changes
wb.save('employees.xlsx')
```

### 15. **Applying Conditional Formatting**

```python
from openpyxl.formatting.rule import ColorScaleRule

# Apply color scale based on Salary
rule = ColorScaleRule(start_type="percentile", start_value=10, start_color="FF0000",
                      mid_type="percentile", mid_value=50, mid_color="FFFF00",
                      end_type="percentile", end_value=90, end_color="00FF00")
ws.conditional_formatting.add('D2:D6', rule)

# Save changes
wb.save('employees.xlsx')
```

### 16. **Adding Formulas**

```python
# Add a formula to calculate the average salary
ws['F1'] = 'Average Salary'
ws['F2'] = '=AVERAGE(D2:D6)'

# Save changes
wb.save('employees.xlsx')
```

### 17. **Protecting a Worksheet**

```python
# Protect the worksheet with a password
ws.protection.sheet = True
ws.protection.password = 'mypassword'

# Save changes
wb.save('employees.xlsx')
```

### 18. **Creating Charts (Bar Chart)**

```python
from openpyxl.chart import BarChart, Reference

# Create a bar chart for salary data
chart = BarChart()
data = Reference(ws, min_col=4, min_row=1, max_row=6)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, 'H5')

# Save changes
wb.save('employees.xlsx')
```

### 19. **Adding Images to Excel**

```python
from openpyxl.drawing.image import Image

# Load and insert an image
img = Image('logo.png')
ws.add_image(img, 'G1')

# Save changes
wb.save('employees.xlsx')
```

### 20. **Saving a Workbook with Different Names**

```python
# Save the workbook under a new name
wb.save('updated_employees.xlsx')
```

### Real-Life Use Case: Employee Management System

Consider that your small company in India is managing employees’ salary and performance data using Excel. The `openpyxl` library allows automating the management and updating of employee records. From modifying records, generating performance reports, adding charts, to applying conditional formatting, `openpyxl` is a powerful tool for such tasks.

In the above example, you manage a simple employee list, automate the addition of new employees, calculate average salary, and apply some formatting rules.

With `openpyxl`, these tasks can be extended to real-world scenarios such as:
- Managing payroll data for employees like Rohan Gupta, Priya Sharma, and Neha Verma.
- Automating exam score analysis for students in a class.
- Generating sales reports for a small business.

This set of examples gives a foundation for handling Excel files in Python using `openpyxl`, opening up possibilities for business automation, reporting, and data analysis.