Efficiently Enter Data into an Excel File

In this lesson, we will learn how to enter data using the sheet.cell method and how to automate data entry using loops.

The most basic way to enter data into an Excel file is to use the sheet.cell method.

This method allows you to input the desired data into a specific cell on the Excel sheet.

Copy
from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Enter data into row 1, column 1
ws.cell(row=1, column=1, value="Hello, Excel")

# Save the file
wb.save("output_file.xlsx")

In the code above, row and column represent the row (horizontal line) and column (vertical line) numbers, respectively, and value indicates the data to be entered into that cell.

The code ws.cell(row=1, column=1, value="Hello, Excel") enters the data "Hello, Excel" into the first row and first column.

Changing row to 2 and column to 3 would enter data into the second row and third column.

Using loops allows you to efficiently enter data into multiple rows with the same data or sequential data with a specific pattern.

Copy
from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# From row 1 to row 10
for i in range(1, 11):
    # Enter Number 1~10 in column 1
    ws.cell(row=i, column=1, value="No. " + str(i))

    # Enter Hello in column 3
    ws.cell(row=i, column=3, value="Hello")

# Save the file
wb.save("output_file.xlsx")

The code above

• Enters "No. 1" to "No. 10" in column 1,

• Enters "Hello" in column 3,

for each row from 1 to 10.

Columns in an Excel file can also be specified using uppercase letters.

For example, the 1st column is A, 2nd column is B, and 3rd column is C in an Excel sheet.

You can specify a particular cell by indicating the row number after the uppercase letter.

Utilize this to change the code above as follows:

Copy
# From row 1 to row 10
for i in range(1, 11):
    # Enter Number 1~10 in column A
    ws['A' + str(i)] = "No. " + str(i)

    # Enter Hello in column C
    ws['C' + str(i)] = "Hello"

When specifying columns with uppercase letters, use brackets to specify the column for the ws(sheet) object.

There is no need to use the cell method separately.

By using the append method, you can enter data in list and tuple forms all at once.

Copy
from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

data = [
    ['Name', 'Age', 'City'],
    ['Alice', 30, 'New York'],
    ['Bob', 25, 'Los Angeles'],
    ['Charlie', 35, 'Chicago'],
]

# Enter data using a loop
for row in data:
    ws.append(row)

# Note: Entering data using a nested loop
# for row in data:
#     for cell in row:
#         ws.cell(row=data.index(row) + 1, column=row.index(cell) + 1, value=cell)

# Save the file
wb.save("output_file.xlsx")

In the code above, data in the nested data list is entered into the Excel file all at once.