Working with Excel in Python

While Excel remains a critical tool for many business applications, Python provides the scalability, flexibility and advanced analytics capabilities needed to thrive in the era of big data.

Using openpyxl

openpyxl is a Python library specifically designed for reading and writing Excel 2010 .xlsx and .xlsm files. It provides a convenient API for manipulating Excel data from Python code.

Some key features include:

• Reading and writing Excel files directly from Python code
• Automatic conversion of Python data types to Excel formats
• Support for formulas, charts, images, and other Excel features
• Ability to append rows and assign data directly to cells
• Efficient processing of large files

Open CMD and type

pip install openpyxl

Then you can read and write Excel files using Python, for example:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws['A1'] = 42
ws.append([1, 2, 3])

import datetime
ws['A2'] = datetime.datetime.now()

wb.save("sample.xlsx")

This code creates a new Excel workbook, writes some data to cells and a row, and saves the file.

import openpyxl
book = openpyxl.load_workbook("C:\\Users\\admin\\PycharmProjects\\PythonFramework\\test.xlsx")
sheet = book.active
cell = sheet.cell(row=2, column = 2)
print(cell.value) #displays the value from the second 2, second column

This example reads the row 2 column 2 value from an existing Excel.

If you want to write use this code

sheet.cell(row=2, column = 2).value = "this comes from python"

How to get the max row and max column

maxRow = sheet.max_row
maxColumm = sheet.max_columm

How to iterate through the values of your excel

for i in range(1,sheet.max_row+1):
    for j in range(1,sheet.max_column+1):
        print(sheet.cell(row=i,column=j).value)

Build the test data dictionary from the Excel sheet

import openpyxl
book = openpyxl.load_workbook("C:\\Users\\admin\\PycharmProjects\\PythonFramework\\test.xlsx")
sheet = book.active
testDataDict = {}
for i in range(1,sheet.max_row+1):
    if sheet.cell(row=i, column=1).value == "test 1":
        for j in range(1,sheet.max_column+1):
            testDataDict[sheet.cell(row=1,column=j).value] = sheet.cell(row=i,column=j).value

print(testDataDict)

or embedded in a class called LoginPageData:

import openpyxl

class LoginPageData:
    #testData = [{"username":"adam", "age": 21},{"username":"joe", "age":21}]

    @staticmethod
    def getLoginPageData(testName):
        book = openpyxl.load_workbook("C:\\Users\\admin\\PycharmProjects\\PythonFramework\\test.xlsx")
        sheet = book.active
        testDataDict = {}
        for i in range(1, sheet.max_row + 1):
            if sheet.cell(row=i, column=1).value == testName:
                for j in range(1, sheet.max_column + 1):
                    testDataDict[sheet.cell(row=1, column=j).value] = sheet.cell(row=i, column=j).value
        print(testDataDict)
        return [testDataDict]