Michael Core Michael Core - 1 month ago 19
Python Question

Using Python and Openpyxl to loop through a .xlsx, but loop only save the last row's data

I am a Python newb and I am working on a project to automate a very time consuming project. I am using openpyxl to access a .xlsx to extract information that will eventually be converted into distance and direction bearings to be used with arcpy/arcgis, meaning I am using Python 2.7. I can access the data and make the first round of changes, but I cannot get my write command integrated into my loop. Currently it saves the last row's data to all cell's in the given range in the new .xlsx. Here is my code:

#Importing OpenPyXl and loads the workbook and sheet

import openpyxl
wb = openpyxl.load_workbook('TESTVECT.xlsx')
ws = wb.get_sheet_by_name('TEST')

#allows to save more than once

write_only = False

cell_range = ws['C']

#sorts through either the rows/columns and slices the required string

maxRow = ws.max_row + 1
for row in range(2, maxRow):
parID = ws['A' + str(row)].value
Lline = ws['B' + str(row)].value
Vect = ws['C' + str(row)].value
print parID, Lline, Vect
trash, keep = Vect.split("C")

#This part save the very last row to all rows in available columns
#need a way to integrate the save functionality so each row is unique

for rowNum in range(2, maxRow):
ws.cell(row=rowNum, column=3).value = keep
for rowNum in range (2, maxRow):
ws.cell(row=rowNum, column=1).value = parID
for rowNum in range (2, maxRow):
ws.cell(row=rowNum, column=2).value = Lline

#Only prints the very last keep entry from the .xlsx

print keep

print "all done"

#Saving does not write all of the the 'keep, parID, and Lline' records
#There is an issue with the for loop and integrating the write portion of
#the code.

wb.save('TESTMONKEYVECT.xlsx')


Can someone please give me some pointers on what I am doing wrong with the write process, I need each row to retain its unique data after the changes have been made.

Thank you,

Answer

Your intuition was correct, you need to combine the loops. The first loop goes through each row, and saves the parID, Lline, and keep over the last value in each of those variables. After the loop, they only have the values from the last row because that was the only row to not have another one come along after it and overwrite the values.

You can solve this by combining the actions into a single loop.

maxRow = ws.max_row + 1
for row in range(2, maxRow):
    parID = ws['A' + str(row)].value
    Lline = ws['B' + str(row)].value
    Vect = ws['C' + str(row)].value
    print parID, Lline, Vect
    trash, keep = Vect.split("C")

    ws.cell(row=rowNum, column=3).value = keep
    ws.cell(row=rowNum, column=1).value = parID
    ws.cell(row=rowNum, column=2).value = Lline