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

Question

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.

Possible duplicate of [Insert row into Excel spreadsheet using openpyxl in Python](http://stackoverflow.com/questions/17299364/insert-row-into-excel-spreadsheet-using-openpyxl-in-python) — Charlie Clark, Feb 01 '17 at 07:05

score 2 · Accepted Answer · answered Feb 06 '17 at 07:56

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

Amorpheuses · Answer 2 · 2017-02-01T16:59:59.303

1

Create a new file called 'demo.py' and copy the following into it. This should do something close to what it is you want. Hopefully the comments (along with prerequisite googling) will give you a good indication of what's going on. This will take an existing spreadsheet and the starting row that you what to shift up from. It'll perform the operation and if that starting row # is less than the existing rows, it'll add a blank row and append the rest of the source rows at their original row numbers. To be safe it will dump the results into a new workbook.

import sys
import os
from openpyxl import load_workbook, Workbook

# check that you have 2 command line arguments to use
if len(sys.argv)!=3:
   sys.exit("Usage demo.py xls_filename start_line")

# ensure you have an existing file
if not os.path.isfile(sys.argv[1]):
   sys.exit("input file does not exist")
excelFile=sys.argv[1]

# make sure the starting row is a number!
if not (sys.argv[2]).isdigit(): 
   sys.exit("2nd argument must be a digit")
num=int(sys.argv[2])

# make sure your extension is okay 
root,ext = os.path.splitext(excelFile)
if not ext in ['.xls','.xlsm','.xlsx','.xlsb']:
   sys.exit("%s does not have an allowable excel extension"%excelFile)
newExcelFile=root + '_new' + ext

# open the source (1) and destination (2) workbooks & worksheets
wb1 = load_workbook(excelFile)
wb2 = Workbook()
ws1 = wb1.active
ws2 = wb2.active

# move each source row up one in the destination 
# starting from row 1 to num
num=min(num,ws1.max_row)
for i in range(1,num):
  ws2.append(ws1.rows[i])

if num<ws1.max_row:
  # append a blank row
  ws2.append(tuple())
  # copy the rest of the rows
  for i in range(num,ws1.max_row):
    ws2.append(ws1.rows[i])

# save the destination workbook
wb2.save(newExcelFile)

Note that you will lose the first row of the source worksheet - which might not be what you want.

I have to add a disclaimer here: I can't vouch for its robustness/completeness as my python is rusty and I've only ever used 'win32com' to do something similar. I'll leave further development (and debugging) to you but let me know if there are issues with it.

edited Feb 01 '17 at 16:59

answered Feb 01 '17 at 05:19

Amorpheuses

1,403
1
9
13

bro, I appreciate this help. I will give it a try once my backlog has subsided. thank you – chip Feb 01 '17 at 23:02
I seem to get an error when using the script above, specifically on line ws1.rows[1]. the way I understand it, the sheet is not represented like a list. but on line ws2.append(ws1.rows[i]) I seem to be having an error. the message is typeError: 'generator' object is not subscriptable – chip Feb 03 '17 at 01:06
I'm using python 3.5 - I think that might be the problem - not sure that it will work for python 2.x. – Amorpheuses Feb 03 '17 at 01:25
apologize for the confusion, I was referring ws2.append(ws1.rows[i]) command near the bottom, under # copy the rest of the rows – chip Feb 03 '17 at 01:37
I get the exact same error using python 2.7. It works on python 3.5 - try installing and running it with 3.5. – Amorpheuses Feb 03 '17 at 01:39
I'm using python 3.6 in windows, perhaps its the openpyxl version? I have openpyxl 2.4.1 – chip Feb 03 '17 at 01:48
I ran it at home - and I think it was openpyxl-2.4.2 - but not sure. I'll be going home right now - I'll check both python and openpyxl versions when I get home. – Amorpheuses Feb 03 '17 at 01:50
Oh before I forget - I used the anaconda installation of python at home. Don't know if that makes a difference. It installs a lot of dependencies automatically. – Amorpheuses Feb 03 '17 at 01:58
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/134726/discussion-between-amorpheuses-and-niccolo-m). – Amorpheuses Feb 03 '17 at 03:34

score 0 · Answer 3 · answered Apr 13 '20 at 10:26

0

You can use openpyxl move_range function.

ws.move_range("D4:F10", rows=-1, cols=2)

That moves the D4:F10 range, up one row and two columns to the right.

Hope that helps :)

answered Apr 13 '20 at 10:26

Raccoon_17

153
2
15

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

3 Answers3

Linked