openpyxl issue - How to reset sheet.ins_row to the real last row ...

[ejwjohn]

Hi,

I have a utility running on a Raspberry Pi which is powered from battery and is powered off and on through a 24 hour period to run this utility before a limited number of tasks are performed, then before closing creates log entries into a .xlsx sheet, due to the nature of the setup i have to run the following code to sequentially store the log entries,

sheet = wb['Sheet1']
    sheet.ins_row = str(len(sheet['A']) + 1 )
    print(sheet.ins_row)
    if (drywet)=="Wet":
        row = (cdate,ctime,drywet,"N/A","N/A")
    else:
        row = (cdate,ctime,drywet,opentime1,valveonoff)
    sheet.append(row)
    wb.save(fpath)sheet = wb['Sheet1']
    sheet.ins_row = str(len(sheet['A']) + 1 )

Then to ensure that the cells are in an orderly format i run the following.

sheet = wb['Sheet1']
rows=range(1,100)
columns=range(1,10)
for row in rows:
    for col in columns:
        sheet.cell(row,col).alignment=Alignment(horizontal='center',vertical='center',wrap_text=True)

The very first time i do this the first lines of code work OK but the second time i run it they fail as the code in the second code snippet have reset the
"last row" to 101 as the alignment above is in rows 1 thru 100

Is there a way to allow me to still format the .xlsx sheet and keep the REAL last line so the sheet.append(row) still work and possibly does the alignment at the same time? is it possible to add the alignment to the following line for example?

row = (cdate,ctime,drywet,"N/A","N/A")

Thank You
JohnW

[ejwjohn]

Please note line 10 of first code snipppet is wrong and is redundant
Line 9 of the same snippet, should end directly after ..(path)

Apologies..

JohnW

[Pedroski55]

This link from the openpyxl docs should help you understand inserting rows

This below may give you some ideas. I think you want the latest data saved at the top of the XL file.

As my XL file has headers, that would be row 2.

import openpyxl
from datetime import datetime

# this XL has 1 sheet called test
path2XL = '/home/pedro/myPython/openpyxl/xlsx_files/test_file_simple.xlsx'
wb = openpyxl.load_workbook(path2XL)
#maxRow = wb['test'].max_row
maxCol = wb['test'].max_column
# show the headers
for i in range(1, maxCol + 1):
    print('The column headers are: ', wb['test'].cell(row=1, column=i).value)
# row 1 is presumably the headers so insert a row at 2 to put the latest data in row 2
""" from here put all this in a loop """

wb['test'].insert_rows(2)
# get time and date
dt = datetime.now()
cdate = dt.strftime('%x')
ctime = dt.strftime('%X')
# put values in row 2
# in this XL column 1 is id an integer starting at 1
# this increments id upwards, row 2 is the highest id, the latest data (if that is the goal)
idd = wb['test'].cell(row=3, column=1).value + 1
# not sure how you are getting rowlist but len(rowlist) should probably be = maxCol
rowlist = [idd, 'V', 'indifferent', ctime, cdate]
for i in range(1, maxCol + 1):
    wb['test'].cell(row=2, column=i, value=rowlist[i-1])
wb.save(path2XL)
print(f'Added a row to {path2XL} at row 2.')

[ejwjohn]

Hello,

And thanks for taking the time for a comprehensive response, i need some time to work my way through what your suggested code is doing.

One point in my existing set up the new data is appended to the bottom of the XL sheet?

Finally, within your suggested code you do not appear to be using Alignment to ensure cells are Centre aligned?

Ok, but thanks again i am going to review you code now thanks...

[ejwjohn]

Hello,

Ok, i can't get the code to work, to ensure that i have not altered anything i have copied the code again here:-

import openpyxl
from datetime import datetime
 
# this XL has 1 sheet called test
path2XL = '/home/john/Desktop/test.xlsx'
wb = openpyxl.load_workbook(path2XL)
#maxRow = wb['test'].max_row
maxCol = wb['test'].max_column
# show the headers
for i in range(1, maxCol + 1):
    print('The column headers are: ', wb['test'].cell(row=1, column=i).value)
# row 1 is presumably the headers so insert a row at 2 to put the latest data in row 2
""" from here put all this in a loop """
 
wb['test'].insert_rows(2)
# get time and date
dt = datetime.now()
cdate = dt.strftime('%x')
ctime = dt.strftime('%X')
# put values in row 2
# in this XL column 1 is id an integer starting at 1
# this increments id upwards, row 2 is the highest id, the latest data (if that is the goal)
idd = wb['test'].cell(row=3, column=1).value + 1
# not sure how you are getting rowlist but len(rowlist) should probably be = maxCol
rowlist = [idd, 'V', 'indifferent', ctime, cdate]
for i in range(1, maxCol + 1):
    wb['test'].cell(row=2, column=i, value=rowlist[i-1])
wb.save(path2XL)
print(f'Added a row to {path2XL} at row 2.')

I ran the code and i get the following results

Output:
>>> %Run -c $EDITOR_CONTENT
The column headers are:  Head 1
The column headers are:  Head 2
The column headers are:  Head 3
The column headers are:  Head 4
The column headers are:  Head 5
Traceback (most recent call last):
  File "<string>", line 23, in <module>
TypeError: unsupported operand type(s) for +: 'NoneType' and 'int'
>>> 

Line 23 is as indicated above and i am struggling to fix the issue ????

Also, it maybe a good idea for me to add the new lines at the top of the XL sheet so i will keep that idea, thanks.

[Pedroski55]

My XL has the headers

id, type, remark, time, date

just make any old XL with those column headers or any column headers and 1 other row with data.

Put some data in row 2, or not, just as long as row 2 column 1 contains an integer!

Make sure column 1 row 2 has an integer value in it.

You have nothing in column 1 so you get that error:

TypeError: unsupported operand type(s) for +: 'NoneType' and 'int'

You are trying to add an integer and None, which is not good!

Now load your XL file and it should work ok.

And if you put that in a loop, you can carry on adding rowlist.

If you want the newest data at the bottom, just keep reading wb['test'].max_row for each time you add data and insert data in row=maxRow + 1

[ejwjohn]

Hi,

Again, thanks, these are some of the issues i come up against, when i have no structured course experience of learning Python. I am trying to learn it "on the fly" as i go and it is far from ideal, i need to identify a good structured course.....


So thanks for your valued help.

JohnW