Learning Robot Framework : Work on Excel File

Prerequisite

pip install openpyxl

How to use the lib

import openpyxl

# Load workbook
workbook = openpyxl.load_workbook("testdata.xlsx")
print(workbook.sheetnames)
print("Active sheet= " + workbook.active.title)

sheet = workbook['Sheet1']
print(sheet.title)

Basically able to fetch any value

sheet = workbook['Sheet2']
print(sheet.title)
print(sheet["A1"].value)

cell = sheet.cell(1, 1)
print(cell.value)
print(cell.row)
print(cell.column)

Fetch ALL data

rows = sheet.max_row
columns = sheet.max_column
print(rows)
print(columns)

# Way1
for i in range(1, rows + 1):
    for j in range(1, columns + 1):
        c = sheet.cell(i, j)
        print(c.value)

# Way2
for row in sheet["A1":"B3"]:
    for cell in row:
        print(cell.value)

Write & Remove Data

import openpyxl

# 여기는 load_workbook이 아니네?
workbook = openpyxl.Workbook()
print(workbook.active.title)

sheet = workbook.active
sheet.title = "Hello World"
print(sheet.title)

workbook.create_sheet(title="NewSheet")
newSheet = workbook["NewSheet"]
newSheet['A1'].value = "test value"

sheet['A3'].value = "testuser3"

# Remove sheet
workbook.remove(newSheet)

# Saving
workbook.save("testdata.xlsx")