0

I am using Pandas for excel manipulation.

I am creating hyperlink which will take me from one cell to another cell.

My cell after creating hyperlink contains data like:

=HYPERLINK("#Sheet1!A20", "Dog") 

=HYPERLINK("#Sheet1!B20", "Cat")

After creation of the hyperlink, I need to compare the value of the cells.

Here, for example I want to check if Dog is equal to Cat.

But I am not able to access the value of the cell (Dog, Cat)

Is there a way I can access the value of the cell for comparison using Pandas?

Warcupine
  • 4,460
  • 3
  • 15
  • 24
Us86
  • 13
  • 1
  • Has the file ever been opened in Excel? If not, the values won't exist. Python-based libraries (Pandas, Openpyxl etc.) don't have a copy of the Excel formula resolution engine and either read the raw value (formula) or the last calculated value when it was saved in Excel. – Alan Sep 14 '21 at 14:31
  • The file won't have this hyperlink or any formula initially. I am creating this hyperlink from the code. After creating the hyperlink, I am trying to access the value of the cell. – Us86 Sep 14 '21 at 14:39

1 Answers1

0

No, you cannot do what you are wanting to do.
Any calculations must be carried out in Python and not attempted through the Excel worksheet.

This is because behind the scenes Pandas is using the Openpyxl library to manipulate any Excel content, and neither Openpyxl nor Pandas has a copy of the Excel formula resolution engine to generate the output of any Excel formulas.

As commented in the documentation: "data_only controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet." [Emphasis mine]

If the file has never been opened in Excel, no calculated value will be available, and only the formula can be accessed.

Essentially this question is a duplicate of this: Read Excel cell value and not the formula computing it -openpyxl

If you goal is to compare the value of two fields ("A20" and "B20") and you don't actually care about the formula, you were just following "Excel-think", then that is a very different question and you need to take a different approach; what you would do is compare your original source data, whether that is a Pandas dataframe, Excel cells etc.

Alan
  • 2,914
  • 2
  • 14
  • 26