lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

lesson12Title

lesson13Title

lesson14Title

lesson15Title

lesson16Title

lesson17Title

lesson18Title

automationIntroBasicsChapter1Title

automationIntroBasicsChapter2Title

automationIntroBasicsChapter3Title

automationIntroBasicsChapter4Title

from openpyxl import Workbook
from openpyxl.styles import PatternFill

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Input data from 1 to 10 in column C
for i in range(1, 11):
    # Set value to -1 for even rows, and double the row number for odd rows
    if i % 2 == 0:
        ws[f'C{i}'] = -1
    else:
        ws[f'C{i}'] = i * 2

# Input data from 1 to 100 in columns 1 to 5 for rows 12 to 31
for i in range(12, 32):
    for j in range(1, 6):
        ws.cell(row=i, column=j, value=(i - 12) * 5 + j)

# Save the modified workbook
wb.save('output_file.xlsx')

# How to Modify Cell Values Using Python Code

In this lesson, we will learn how to use `openpyxl` to modify cell values in various ways and change cell formatting.

 

## Modifying a Specific Cell in a Sheet

To modify a cell's value, simply assign a new value to it.

For example, to change the value of cell B1 to 100, you can do the following:

```python title="Modify the value of cell B1"
ws['B1'] = 100
```

Alternatively, you can use the `cell` method introduced in previous lessons, using `ws.cell(row=row_number, column=column_number)`.

 

## Modifying Multiple Cells Based on a Pattern

When you need to enter data following a specific pattern into multiple cells, you can use a loop, as shown below.

```python title="Modify Multiple Cells Based on a Pattern"
# Enter values from 1 to 10 in column B
for i in range(1, 11):
 ws[f'B{i}'] = str(i * 100) + 'km'
```

In this example, cells from B1 to B10 are filled with values from 100 to 1000 followed by 'km'.

 

You can also enter values from 1 to 10 in row 1, columns 1 through 10, as follows:

```python title="Modify Multiple Cells Based on a Pattern"
# Enter values from 1 to 10 in row 1
for i in range(1, 11):
 ws.cell(row=1, column=i, value=str(i) + "th")
```

 

By applying this concept, you can automatically enter patterned data into both rows and columns.

```python title="Modify Multiple Cells Based on a Pattern"
# Nested for loop to enter values from 1 to 100 (5 columns x 20 rows)
for i in range(1, 21):
 for j in range(1, 6):
 ws.cell(row=i, column=j, value=(i - 1) * 5 + j)
```

 

## Modifying Cell Data Based on Conditions

Using conditional statements, you can modify cell data based on specific conditions.

The code below changes the value of cells with values greater than 5 to 'Large', and changes others to 'Small'.

```python title="Modify Cell Data Based on Conditions"
# Change the value of cells with values greater than 5 to 'Large'
for i in range(1, 11):
 if ws[f'B{i}'].value > 5:
 ws[f'B{i}'] = 'Large'
 else:
 ws[f'B{i}'] = 'Small'

# Save the modified content
wb.save('output_file.xlsx')
```

Using openpyxl, you can modify cell data based on specific conditions. For example, you can change the values of cells with values greater than 5 to 'Large'. This allows you to manage data efficiently according to various conditions.

### When modifying an Excel file using openpyxl, it is impossible to change cell data based on specific conditions.