Mocking Excel file content in Python with Openpyxl

Question

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.

score 1 · Answer 1 · answered Dec 27 '21 at 12:12

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()

Thanks so much for taking the time to answer! I'm trying to test your suggestion and if I change the return value of read_excel_file from `return ws[cell].value` to `return ws['A2'].value`, I would expect a failed test (or similarly, if I add `ws["A2"].value= "Some more content"` under ws["A1"].value = "Some content" to the test and call functions_to_tet.read_excel_file_contents(..., cell="A2"), it still looks for cell A1, so it doesn't seem to look for the correct cell in the test. Any suggestions? — Mark Hurenkamp, Dec 27 '21 at 13:16

Mocking Excel file content in Python with Openpyxl

1 Answers1