0

Imagine a very simple problem:

  • Column A has a list of values (say A1: 1, A2: 2, A3: 3)
  • Cell C1 is a simple formula (say B1^2)

I wat to iterate the values in A, giving them to B1 each at a time, and save the results in a new list.

    for idx in range(number_inputs):
        input_cell = f'A{(1+idx)}'
        target_cell = f'D{(1+idx)}'

        sheet['B1'] = sheet[input_cell].value
        sheet[target_cell] = sheet['C1'].value

The problem is that I cannot copy the value of C1, only the formula. The solution to this problem seems to be to load the workbook with the option data_only=True, but this does not work for me, since I need the formula in B1.

I was trying inelegant and desperate solutions, like copying my sheet to a new one... but this also copies the formulas.

Frankly, this seems like a surprisingly hard thing to do given how basic the goal is. Is there anything I am missing, or is this impossible to achieve?

Pablo
  • 1,373
  • 16
  • 36
  • Open the workbook a second-time using read-only, data-only parameters. – Charlie Clark Aug 20 '19 at 13:46
  • The problem is that this won't have the values updated as I try new inputs in the first instance, right? – Pablo Aug 20 '19 at 13:57
  • 1
    openpyxl never updates calculations so you could never have that anyway. It's a file-format library, not an Excel replacement. – Charlie Clark Aug 20 '19 at 13:59
  • Then I assume what I want is not really possible to do. In the line of your advice I could create a new Excel file to save and load (as value-only) at every iteration. Not pretty, to be sure, but I guess it should work... – Pablo Aug 20 '19 at 14:01
  • 1
    You might want to look at xlwings, which can talk to Excel while a file is open. Otherwise, changing data in openpyxl and passing to Excel or OpenOffice is the way to go. – Charlie Clark Aug 20 '19 at 15:17
  • I don't know if thanking in comments is also frowned upon, but thank you for taking the time! – Pablo Aug 21 '19 at 12:09
  • You're welcome. If you can't write that kind of the stuff in the comments, where can you? – Charlie Clark Aug 21 '19 at 14:20

0 Answers0