Modify values of an excel sheet containing formulas, and looking at variations of an outcome when some input parameters are modified through a python script I have an excel sheet (.xlsx) with a lot of formulas inside. I have some parameters, that I want to vary, and some outcomes, depending on those parameters, whose variation/dependence on the parameters I want to analyze. Being well-aware of Design of Experiments (typically, Latin Hypercube Sampling), I would like to simply create a DoE (easy, there are a lot of good R/Python packages for that), whose combinations are automatically entered at the good cell in the sheet, and retain the corresponding output. Of course I could do this manually but this would be limited to few values and quite time consuming... A typical pseudo-code would be: X = DOE(n samples, d vairables) y = zeros(n,) for i = 1:n Change the adequate cells as x[i,1], ..., x[i,d] Look at the cell containing the output and affect its value y end I first tried using df = pd.read_excel, modify the corresponding cells of df, and then df.to_excel but the issue is when loading the df, the "output cell" contains a numeric value, that is written back during the to_excel step (so no longer a formula, hence the value is wrong). I have then tried with openpyxl hoping this time the formula would not be replaced by a numerical value... This time I manage to modify a cell, and to visualize the (modified) parameters and modified outcome in Excel. So I am pretty happy... except that when I try to read the outcome, python says it's nan when using pd.read_excel, or returns nothing when using openpyxl, though I visually see the cell contains (the good) number. Here's my code, hoping you can help me wb = load_workbook(file_path, data_only=False) ws = wb[sheet_name] ws['C3'] = 6500 wb.save("modified_file.xlsx") # Visually, modified_file contains the modified value, and accordingly, my target cell, N32 has changed too! # Ok great... let's store cell N32 of modified_file.xlsx as y now! wb_modif = load_workbook("modified_file.xlsx", data_only=True) ws_modif = wb_modif[sheet_name] ws_modif['N32'].value # output nothing (None if I store it in a variable), despite N32 has value 3189 in excel Of course I tried setting data_only=False and then ws_modif['N32'] returned the formula of cell N32; and when using pd.read_excel in the following manner, the output is none (many nans appear when loading the modified file using pd.read_excel) pd.read_excel("modified_file.xlsx", sheet_name=sheet_name, header=None).at[31,13] # attempt to see N32's value --> nan So... if by chance you have an idea of what I should do, I would be very thankful! Thanks a lot in advance for your feedback Continue reading...
