Replace data with python Excel

kevin streit 0
Viewed 21

I have two excel sheets. One with formula and one without. Now I want to get the data from the Excel Sheet with Formula and replace it.

data_read_only = list()
wb = openpyxl.load_workbook(filename, data_only=True)
ws = wb['Abschätzung SD Intern ']

for row in ws.iter_rows():
    for cell in row:
        data_read_only.append(cell.value)




wb: Workbook = openpyxl.load_workbook(filename, data_only=False)
ws: Worksheet = wb['Abschätzung SD Intern ']

for element in data_read_only:
    print(element)
    for row in ws.iter_rows():
        for cell in row:
            ws.cell(row=row, column=cell).value = element

wb.save(filename)

How can I replace the rows?

openpyxl python
1 Answers

For reading values from file and storing, it may be more intuitive to use a list of lists to store cell values like this:

data_read_only = list()
wb = openpyxl.load_workbook(filename, data_only=True)
ws = wb['Sheet1']

for row in ws.iter_rows():
    cell_list = []
    for cell in row:
        cell_list.append(cell.value)
    data_read_only.append(cell_list)

For my test file which had 6 rows and 2 columns, the resulting data_read_only content was:

>>> data_read_only
[['Num', 'Factor'], [1, 5], [2, 10], [3, 15], [4, 20], [5, 25]]

For the overwriting part, it gets easier with this list of lists whose row and column indices are obtained by enumeration. The corresponding cell is then assigned the value from data_read_only using those indices as follows:

wb_form = openpyxl.load_workbook(filename, data_only=False)
ws_form = wb_form['Sheet1']

for r_index,row in enumerate(ws_form.iter_rows()):
    for c_index,cell in enumerate(row):
        ws_form[cell.column_letter+str(cell.row)] = data_read_only[r_index][c_index]

wb_form.save(filename)
Heelara0
Related