Open mejrvs opened 8 months ago
I have a solution, where the actual Python program is on a worksheet in one of more columns. The columns are run from left to right. This requires a small runner program in a Py() cell:
df_runner = xl("B:C")
for column in range(len(df_runner.columns)):
as_list = df_runner.iloc[0:, column].to_list()
while as_list and as_list[-1] is None:
as_list.pop()
source = "\n".join("" if line is None else str(line) for line in as_list)
exec(source, globals())
globals().get("output")
Note that in this case, the columns B and C contain the program.
The program may contain print
statements and generate a list or dataframe called output, that will be targeted at the Py() cell.
A complete working example can be found here: Python in Excel - code on sheet mini.xlsx
Is there any way to increase the character limit? Splitting out a script in multiple cells is a bit clunky and can get pretty slow.
Also, I'm having trouble with variable references in for loops that span multiple cells. For example, in cell A1, I establish a for loop and a dynamic variable under the loop that changes with each iteration. The logic is long, so I exceed the single-cell character limit before finishing. I continue in A2, with proper indentations, and refer to the dynamic variable. I get a syntax error, where references to that same variable in A1 do not have an error.