Python Excel ---- Specific data updation


#1

Hello Friends

I am struggling at an issue here which is related to excel in python. Below is the background to explain you very clearly about the system that I am programming. Kindly go through it. I hope the details are no that long and time consuming for you guys.

Background:

We needed a system consisting of: 1. QR codes of unique numbers, 2. Definately a QR code scanner to blew the QR codes up, 3. A program to run in the background to behave and operate things as we want. The system is an Automated Parts Ordering System. It consist of an Excel sheet with all the details of the parts for example: screws, bolts, nails. So these articles are stored on a cupboard with multiple drawers. Lets consider a situation, that you opened up a drawer to find 3 bolts. You found your bolts and also at the same time you also saw that only two bolts are remaining which is almost finished. So we need to order them in order to fill the stock. Then what you do, you scan a QR code of an unique number sticked onto the box where only bolts are stored. When scanned the program in the background opens an excel sheet search for the unique number, when found it copies the information associated in that whole row and closes the excel sheet and then opens another excel sheet writes it over there and save. The information which contains are: Unique number, supplier, item(Bolt), unit price and quantity. Then someone who is responsible to order these items will further order them and we have our items back stored again.

Issue statement:

The system is working the way it is described above. But we can only order only if the total order is above 100 dollars. All the items are no more than 20 dollars. So if the excel sheet contains 5 items to order and if all of these items cost 20 dollars each would make 100 dollars in total. This total amount I am able to write it to the ordering sheet but the previous total value remains on a separate row and when again scanned it sums all the numbers and also the previous total value where at the end the total value is incorrect and also a bigger value. What I want is to make this total value getting updated after each scan. Or I want the previous total value get deleted and then write the new total value when scanned.

I used, xlrd, openpyxl, and below is the code. I would appreciate any help in this issue. Thank you very much.

import sys
import xlsxwriter
import xlwt
import xlrd
import math
import openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
from xlutils.copy import copy


def open_file(path):
    wb = xlrd.open_workbook(path)
    sheet = wb.sheet_by_index(0)


    
    #wbk = xlsxwriter.Workbook('or.xlsx') 
    #ws = wbk.add_worksheet()
    for row_num in range(sheet.nrows):
        
        row_value = sheet.row_values(row_num)

        if row_value[0] == 100:
            print(row_value)
           
            wbk = load_workbook('or.xlsx')
            ws = wbk['Sheet1']
            ws.append(row_value)

            s = []
            input_row = ws.max_row + 2
            input_col = 6
            for row in ws[('F{}:F{}'.format(ws.min_row,ws.max_row))]:
                for cell in row:
                    q = cell.value
            
                    s.append(q)        
            total = sum(s)
            print(total)
            ws.cell(row = input_row, column=input_col).value = total
        
            wbk.save('or.xlsx')
if __name__ == "__main__":
    path = "ord.xlsx"
    open_file(path)