CODEWITHSUNDEEP
pythonunit-testingmockingopenpyxlpython-3.9
Mark Hurenkamp
Mark Hurenkamp

Reputation: 11

Mocking Excel file content in Python with Openpyxl

The following is a simplification of a more complex problem, where I need to verify the contents of Excel files. I'd like to write unit tests for the functions I'll be needing, but ideally would like to mock these tests so I don't have to have a sample Excel sheet available.

The following function works:

functions_to_test.py:

import openpyxl

def read_excel_file_contents(filename: str, sheetname: str, cell: str) -> str:
    wb = openpyxl.load_workbook(filename, read_only=True)
    ws = wb[sheetname]
    return ws[cell].value

test.py

import unittest
from unittest.mock import MagicMock, patch

import functions_to_test


class FunctionsToTest(unittest.TestCase):
    
    @patch('functions_to_test.openpyxl')
    def test_read_mocked_excel_file(self, openpyxl_mock):
        wb = openpyxl_mock.workbook()
        ws = openpyxl_mock.worksheet()

        openpyxl_mock.load_workbook = MagicMock(return_value=wb)
        wb.get_sheet_by_name = MagicMock(return_value=ws)
        ws["A1"].value = "Some content"       
        self.assertEqual(
            functions_to_test.read_excel_file_contents("a mocked excel file", sheetname="somesheet", cell="A1"), 
            "Some content"
            )


if __name__ == "__main__":
    unittest.main()

Output:

======================================================================
FAIL: test_read_mocked_excel_file (__main__.FunctionsToTest)
Assert the correct contents of reading a mocked Excel file
----------------------------------------------------------------------
Traceback (most recent call last):
  File "C:\Users\markh\AppData\Local\Programs\Python\Python39\lib\unittest\mock.py", line 1337, in patched
    return func(*newargs, **newkeywargs)
  File "c:\Users\markh\Python\Testing\test.py", line 18, in test_read_mocked_excel_file
    self.assertEqual(
AssertionError: <MagicMock name='openpyxl.workbook().__ge[45 chars]200'> != 'Some content'

----------------------------------------------------------------------
Ran 1 test in 0.003s

I believe I have to use MagicMock() for this, but can't seem to figure out how exactly. I've looked at Mocking Method Calls In Python, but my case seems to go one step further compared to the answers given in there.

Upvotes: 1

Views: 3837

Answers (1)

niko
niko

Reputation: 5281

Consider the following modifications to test.py (please do check that everything makes sense):

Essentially, my idea was to make sure the newly initialized workbook wb has a sheet labelled somesheet using .create_sheet. Then, set ws as that sheet and finally set the value for cell A1.

test.py

import unittest
from unittest.mock import MagicMock, patch

import functions_to_test


class FunctionsToTest(unittest.TestCase):

    @patch('functions_to_test.openpyxl')
    def test_read_mocked_excel_file(self, openpyxl_mock):
        wb = openpyxl_mock.workbook()
        wb.create_sheet("somesheet")
        ws = wb["somesheet"]
        ws["A1"].value = "Some content"

        openpyxl_mock.load_workbook = MagicMock(return_value=wb)
        self.assertEqual(
            functions_to_test.read_excel_file_contents(
                "a mocked excel file",
                sheetname="somesheet",
                cell="A1"
            ),
            "Some content"
        )


if __name__ == "__main__":
    unittest.main()

Upvotes: 1

Related Questions