Source File - openpyxl fails to interpret excel formula results

[walker-philips]

Connector Name

source-file

Connector Version

0.5.15

What step the error happened?

During the sync

Relevant information

The openpyxl approach to reading excel files seems to extract the formula(s) itself rather than the value the formula evaluates to.

The standard approach of using pandas to read the file results in the expected outputs.

I saw this issue while fixing/debugging another issue in source-file, so my testing was via CLI with poetry using the read() method. I would imagine the results would be the same when run from a deployed connector within the UI.

The easy solution solution would be to provide an option to choose between openpyxl and pandas within the connector setup rather than the current try except approach.

There appears to be a potential issue with openpyxl itself. The quick fix may be the data_only parameter into load_workbook.

I think the behavior is extremely dependent on what formulas someone is using. I have run into this issue with excel add-ons where there is a much for involved process to trigger a refresh of the workbook. I will try and dig up old code and offer that up as a bullet proof approach to reading any excel data.

Relevant log output

Contribute

• Yes, I want to contribute