Unexpected corruption of excel file. Python-Openpyxl-CodePudding

I have had a pretty rough experience with this module of python. Well, here is the code and I speculate the error to occur in line 34. I am using the latest version of the module, excel and python. The code does not pop any kind of error in the console but the excel file hence created is corrupt.

The code

the code was too long to send here.

CodePudding user response：

I have come across a solution to the problem without compromising the amount of stored information. I found a helpful command of Openpyxl that is .max_row This command tells you the rows of data filled in the sheet.

Using that I changed line 34 and it fixed everything, see for yourself if you want.

GITHUB PAGE

The given excel sheet is to be used and shall not be altered, and no corruption takes place. Further suggestions will be appreciated.

CodePudding user response：

Seshrut I see you have made a change to your code but the issue still remains, you are appending off the end of the sheet.
Add this print line to your code at line 68 (the ws.append will be moved to line 69).

Y = Y   QNT
print("Appending data after Row: "   str(ws._current_row)) # Add this line
ws.append([srNO, phno, name, d, m, H, M, billno, Art_no, "desc", QNT, "OUM", MRP, amount, discount, SP, MOP, bday, bmon, aday, amon, loyalty, "F"])

You will see that the next line where your code makes the append is right at the end of the sheet and this will corrupt the workbook. Note the Row numbers from the added print statement. The first line is appended after Row 1048576, and for each article the appends go beyond the sheet max row count.

No. of articles 5 
Enter the same article number   11 
Enter no. of similar article    1 
Appending data after Row: 1048576 
Enter the same article number   12 
Enter no. of similar article    1 
Appending data after Row: 1048577 
Enter the same article number   13 
Enter no. of similar article    1 
Appending data after Row: 1048578 
Enter the same article number   14 
Enter no. of similar article    1 
Appending data after Row: 1048579 
Enter the same article number   15 
Enter no. of similar article    1 
Appending data after Row: 1048580 
SAVED

Change the line

if pch != phno and row == 1048576:

to some smaller number

if pch != phno and row == 10:

The output results in an uncorrupted workbook since the appends are not off the end of the sheet

No. of articles 5
Enter the same article number   11
Enter no. of similar article    1
Appending data after Row: 10
Enter the same article number   12
Enter no. of similar article    1
Appending data after Row: 11
Enter the same article number   13
Enter no. of similar article    1
Appending data after Row: 12
Enter the same article number   14
Enter no. of similar article    1
Appending data after Row: 13
Enter the same article number   15
Enter no. of similar article    1
Appending data after Row: 14
SAVED

If you really need to have such a long column for phone numbers, then put these appended rows on another sheet or do not append but add them in a column where you can start from row 1 (or 2).