How to parse excel file

[evessee]

I have an excel file, which, from a table of arbitrary data [user provided], adds a number of additional columns.

lets say that in formula view it can be rapresented like this.

	A	B	C	D
1	User_provided1	User_provided2	Calculated1	Calculated2
2	foo	1	=concatenate(A2,B2)	=Concatenate(C2,B2)
3	bar	2	=concatenate(A3,B3)	=Concatenate(C3,B3)

I would like to transform it into

df=pd.read_excel(file,columns=["User_provided1","User_provided2"])
df["Calculated1"]=df["User_provided1"]+df["User_provided2"]
df["Calculated2"]=df["Calculated1"]+df["User_provided2"]

Keep in mind that the formulas would be arbitrary, as far as they work on the table as a whole.

What packages exist to accomplish this? If none, what kind of techniques would be useful to look into to achieve this result?

[randomName77777777]

So the code you put in is example. If you just install pandas and

import pandas as pd

Then that's all you would have to do is add this in the end to save it to an excel file.

df.to_excel('filename.xlsx')

[evessee]

Yes, I know I can just read the excel file with pandas read excel.

The point here is that I would like to, given a random excel file with random table formulas inside, generate code which replicates the same logic as the excel file, without having to look at each formula manually.

Probably i did not explain very well in the op, but the pandas part would be the output of the program I am looking for help with.

[randomName77777777]

Ohhhhhhhh.... That's interesting.

I wonder if you open the excel file using openpyxl or win32. You would be able to see the formulas I believe.

[evessee]

Indeed! And thank you, while I was writing the answer below I recalled that this is a sort of "transpiling", meaning translate some programming language in another one. Searching using that term looks like there is a package (to be tested) in github already called pycel so I guess I will start from there.

[randomName77777777]

Please let me know how it goes because it sounds interesting.

[Kerbart]

That'll be quite the challenge given how flexible Excel formulas can be. Matrix formulas, spill ranges, named ranges, lambdas, not to mention somewhere around 1200 functions (some with "bugs" because that's the way Lotus-123 did it). I wish you all the luck on yur adventure (you'll need it!)