Possibility for integration tests of Excel: http://docs.xlwings.org/en/stable/quickstart.html
For example, a test could be:
import xlwings as xw
with xw.Book(r'/path/to/drawdown.xlsx') as workbook:
sheet1 = workbook.sheets['Sheet1']
cell_A1 = sheet1.range('A1').value
integration test using xlwings and Excel can work with some compromises:
a) on MacOS, may have to enable macros within Excel. Otherwise it prompts to Enable Macros every time the sheet is opened, and I haven't found a good way to handle this automatically.
b) on MacOS, can not have a workbook already open. Otherwise it tries to launch another instance of Excel, which raises a weird protected worksheet error.
Expecting to have tests/excel_integration_test.py with a setup() function to start a flask process on an unassigned TCP port, check whether Excel already has a workbook open and fail if it does (to survive 1.b.), and then test cases for each xlsm file which will open the file, write the URL="http://localhost:port/path" to a cell, and then check the values of a few output cells again known golden values.
I will be heading with daughter to track practice at 5:30
An initial version of the gold standard test is in the cloned repo https://gitlab.com/DentonGentry/drawdown
There are at least two things left to do:
I made some more progress on a) starting and stopping a flask app reliably and b) skipping the test on systems where Excel is not present.
I have a videoconference at 10am which wasn't amenable to rescheduling. I reserved a conference room downstairs, and will be back up by 11am (probably earlier).
Continuous Integration is going to be tricky. Adding xlwings to requirements.txt fails because:
OSError: currently only Windows and OSX are supported.
No adding it to requirements.txt makes the excel_integration_test.py fail when trying to import modules.
I think excel_integration_test.py should put the import xlwings inside a try block, and skip the tests if it fails.