0

Let's say we have this Excel sheet:

enter image description here

The following code works to iterate over all rows, and get the specific value of some columns, but it is definitely not very "pythonic":

from openpyxl import load_workbook
wb = load_workbook('test.xlsx')
ws = wb.active

header = {col[0].value: i+1 for i, col in enumerate(ws.iter_cols())}  # mapping between column 
                                                                      # names (from header row) 
                                                                      # and their index
for i in range(2, ws.max_row+1):  # it would be interesting to not have to think about 2, max_row+1, etc.
    print(ws.cell(row=i, column=header['abc']).value)
    print(ws.cell(row=i, column=header['def']).value)
    # for example, print(ws[i, 'abc']) would be nicer (pandas-style)

Question: what is the openpyxl built-in way to do this?

Note: I don't want to use pandas for this because I need to modify an existing Excel file and keep layout/formatting; see also Modify an Excel file with Pandas, with minimal change of the layout.

Second example: let's say we want to set a value 128 in column abc for the row which has 523 in column def. The best I could find was:

def find_row(ws, column, query):
    for i in range(2, ws.max_row+1):
        if ws.cell(row=i, column=column).value == query:
            return i
    return None

ws.cell(row=find_row(ws, header['def'], 523), column=header['abc'], value=128)

which is probably not optimal.

TL;DR: How to peform this "find row + replace value" with the built-in openpyxl API?

Basj
  • 41,386
  • 99
  • 383
  • 673
  • Q: what do you want? find and replace? I suggest you use Pandas, easy and fast – Johnny Mar 09 '21 at 08:29
  • @Johnny, Yes (I just edited to emphasize what is the question). – Basj Mar 09 '21 at 08:30
  • @Johnny As mentioned in the question (see in the middle): *Note: I don't want to use pandas for this because I need to modify an existing Excel file and keep layout/formatting; see also [Modify an Excel file with Pandas, with minimal change of the layout](https://stackoverflow.com/questions/66421693/modify-an-excel-file-with-pandas-with-minimal-change-of-the-layout)*. – Basj Mar 09 '21 at 08:31
  • There seems to be no ready-made method, or you need to write one or raise issues – Johnny Mar 09 '21 at 08:42
  • I just did here @Johnny: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1636. – Basj Mar 09 '21 at 09:09

0 Answers0