Writing multiple Excel Values with PY

[ahagmann83]

Ok, I can read Excel Cell Values into my Python code with xl. But what about writing? (How) can I write values back into (multiple) Excel Cells?

[keyur32]

Python formulas only return data within the cell they were entered on. https://support.microsoft.com/en-us/office/data-security-and-python-in-excel-33cc88a4-4a87-485e-9ff9-f35958278327.

To write to other cells, you would need to manually enter or do it via automation (i.e. an Office Script to write out the =PY formula) to the desired cell.

[jflam]

You can also spill additional values back into the grid as well if you return lists, tuples or Pandas DataFrames from the Python formula.

[WillemWannenburg]

ahagmann83, have you found a work-around way to write from Python to Excel?

[jflam]

ahagmann83, have you found a work-around way to write from Python to Excel?

There are no workarounds for this, as this behavior is by design. What is your scenario?

[WillemWannenburg]

I regularly build physics models on excel that calculate a certain output value based on given parameters. Then I always want to plot the relationship that a chosen input parameter has to the output. If I worked only in Python, it's easy: I would make a function and iterate (loop) to plot. Now, I want to achieve the same but using python in excel. See the example below. Here I want to plot input parameter (C2) against output (C35). I achieved the desired output in "sheet=DP-Python", but this is not elegant enough, I would then rather just do it all in python. I tried using the fact that python in excel goes left to right and then down, but then I get a circular ref issue.

Pressure analysis - python for excel.xlsx

If there is an elegant way to do this, it would work well for monte carlo simulations as well. I know I can do this using a macro, but again then I'll just rather do it all in Python.

[salabim]

I would think that you can dump several output variables as a dataframe on your sheet. From there you could possibly reference to other cells on your sheet(s). If you want, we could discuss the details in private.

[WillemWannenburg]

In summary what I would want to do in a python cell in excel:

for i in [0,5,10,15,20,25,30,35,40,45,50]:
    xl("C2") = i  #write to excel
    output_list.append(xl("C35")) 
output_list

[WillemWannenburg]

I found out I can achieve my goal using a What-if -> Data Table. https://www.reddit.com/r/excel/comments/5sdlng/whatif_analysis_question/