Python cells in openpyxl
The Excel team announced that Python formulas are coming in a future version of Python.
Raising this issue with some technical detail about that integration and the implications on the format.
Cells in Python mode will have the following syntax:
import openpyxl
wb = openpyxl.load_workbook('Python in Excel - F1 analysis example.xlsx')
# Get cell D2 value from the "Race Dashboard" sheet
value = wb.worksheets[0]['D2'].value
f"Cell value {value}"
>>>'Cell value =_xlfn.PY(1,0,$B$2,$C2)'
_xlfn.PY()
refers to the position of the code tag in the new pythonScripts.xml
file. The arguments are substitutions to P
parameters in that block. In Excel, the cell D2 has the value in this example of driver_name(winner_of(xl("$B$2"), xl("$C2"))
[Content-Types].xml
now has the following element:
<Override PartName="/xl/pythonScripts.xml"
ContentType="application/vnd.ms-excel.pythonscripts+xml" />
Here is a sample of the pythonScripts.xml
file in xl/
, note how characters are escaped (e.g. >
is >
)
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<pythonScripts xmlns="http://schemas.microsoft.com/office/spreadsheetml/2022/pythonscript"><pythonScript><code>from functools import lru_cache
races = xl(%P2%, True)
race_results = xl(%P3%, True)
drivers = xl(%P4%, True)
lap_times = xl(%P5%, True)
@lru_cache
def driver_name(driverId: int) -> str:
driver = drivers[drivers.driverId == driverId]
if not driver.empty:
return driver.iloc[0].code
else:
return ''
@lru_cache
def race_id(season: int, race: str) -> int:
result = races[(races.year == season) & (races.name == race)]
if result.empty:
raise ValueError("No race exists")
return result.iloc[0].raceId
@lru_cache
def winner_of(season: int, race: str, position: int = 1) -> int:
try:
result_of_race = race_results[race_results.raceId == race_id(season, race)]
first_place = result_of_race[result_of_race.position == position].iloc[0]
return first_place.driverId
except ValueError:
return -1</code></pythonScript><pythonScript><code xml:space="preserve">driver_name(winner_of(xl(%P2%), xl(%P3%)))
</code></pythonScript><pythonScript><code xml:space="preserve">driver_name(winner_of(xl(%P2%), xl(%P3%), 2))
</code></pythonScript><pythonScript><code xml:space="preserve">driver_name(winner_of(xl(%P2%), xl(%P3%), 3))
</code></pythonScript><pythonScript><code xml:space="preserve">"hello world"
</code></pythonScript><pythonScript><code xml:space="preserve">import matplotlib.pyplot as plt
import pandas as pd
race_results[race_results.raceId == race_id(xl(%P2%), xl(%P3%))]
laps = lap_times[lap_times.raceId == race_id(xl(%P4%), xl(%P5%))]
laps_with_pitstops = pd.merge(laps, xl(%P6%, True), on=['raceId', 'driverId', 'lap'], how='outer')
laps_with_pitstops = laps_with_pitstops[laps_with_pitstops.stop.isnull()] # Did not stop
df = pd.merge(laps_with_pitstops, drivers)[['lap', 'milliseconds_x', 'code']].copy()
for code in df.code.unique():
plt.plot(df[df.code == code].lap, df[df.code == code].milliseconds_x, label=code)
plt.yscale('log')
plt.legend()
plt.show()
</code></pythonScript><pythonScript><code>races = xl(%P2%, True)
races_by_season = races[['raceId', 'year']].copy()
results= xl(%P3%, True)
constructor_names = xl(%P4%, True)[['constructorId', 'name']].copy()
pts_by_c_by_season = races_by_season.merge(
results, how='right'
)[['year','constructorId','driverId', 'points']].groupby(
["year", "constructorId", "driverId"]
).agg(
avg_points=("points", "mean"),
total_points=("points", "sum"))
pts_by_driver_by_season = pd.merge(pts_by_c_by_season, constructor_names, how='left', left_on='constructorId', right_on='constructorId')
pts_by_driver_by_season</code></pythonScript></pythonScripts>
openpyxl works with this new update but I'm raising this incase you want to expose the pythonScripts as an attribute.