Hello Tamashevich, Tatsiana,
Welcome to the Microsoft Q&A and thank you for posting your questions here.
Problem
I understand that you are having issue when reading Excel files containing formulas into a Synapse Spark pool, the formulas are not being evaluated correctly. Instead of returning the calculated results of the formulas, only partial or incorrect values are retrieved, and this issue persists across different libraries and methods used for reading the Excel files.
Solution
To solve this issue, you will need to understand that not all libraries evaluate the formulas correctly, and reading Excel files with formulas in Synapse Spark pool can be tricky. So, what come to my mind are the followings:
- You will need to evaluate formulas in Excel Before Reading.
- Use
xlwings
to Evaluate Formulas. https://docs.xlwings.org/en/stable/macro.html - Use
com.crealytics.spark.excel
with Formula Evaluation. - Lastly, use VBA Script to Pre-evaluate Formulas. https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
Accept Answer
I hope this is helpful! Do not hesitate to let me know if you have any other questions.
** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful ** so that others in the community facing similar issues can easily find the solution.
Best Regards,
Sina Salam