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.
The excel integration test is in https://gitlab.com/DentonGentry/drawdown.
It fails continuous integration:
because I don't have the rights to have the Heroku API key, so staging deployment fails and fails the commit. We'll figure this out later, and get it merged into the code.earth drawdown repository.