niccolo m. niccolo m. - 4 years ago 221
Python Question

Python openpyxl how do I shift row values to current position - 1?

I'm trying to format multiple excel 2007 files that will be consumed by a separate ETL program. I just need to shift the rows values up one level. So values in row 3, I would like to transfer to row 2.

Please bear with me I'm a noob at python and openpyxl.

I tried iterating over the worksheet and inside the loop I used ws.cell(param, param) I set the row to -1 but this doesn't appear to be working

I also tried to iterate over the rows, and inside the loop create another iteration that will start max row-1 from parent loop and assign the value from subloop row to main loop row but this doesn't appear to be working maybe I'm missing something.

Answer Source
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook("sample.xlsx")

ws1 = wb.active
ws2 = wb.create_sheet("modifiedSheet")

start_row = 3
start_col = 1

for row in ws1.iter_rows(min_row=start_row):
    for cell in row:
        # print(cell.value)
        ws2.cell(row = start_row-2, column = start_col, value=cell.value) # start_row - 2 will assign the value to the same column up 2 rows
        start_col += 1 # increment the column, for use of destination sheet
    start_row += 1 # increment the column, for use of destination sheet
    start_col = 1 # reset to first column after row processing

wb.save("modified.xlsx")

this is not as dynamic, but gets the job done

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download