def get_products(filename =None): print filename if not filename: return {} try: workbook=xlrdutils.open_workbook(filename=filename) except (xlrdutils.XlrdutilsOpenWorkbookError, xlrdutils.XlrdutilsOpenSheetError) as e: warningMessage = repr(e) print warningMessage raise e sys.exit(-1) try: data=xlrdutils.read_lines(workbook, headerKeys=['Product Code', 'Unit of Measure', 'Qty of Measure',],) except (xlrdutils.XlrdutilsReadHeaderError, xlrdutils.XlrdutilsDateParseError) as e: warningMessage = repr(e) print warningMessage raise e sys.exit(-1) productDict={} productCodeList = data['Product Code'] qtyOfMeasureList = data['Qty of Measure'] for k in range(len(productCodeList)): try: productDict[productCodeList[k]]=qtyOfMeasureList[k] except KeyError as e: print repr(e) raise e sys.exit(-1) return productDict
def get_sites(filename =None): if not filename: return {} try: workbook=xlrdutils.open_workbook(filename=filename) except (xlrdutils.XlrdutilsOpenWorkbookError, xlrdutils.XlrdutilsOpenSheetError) as e: warningMessage = repr(e) print warningMessage sys.exit(-1) try: data=xlrdutils.read_lines(workbook, headerKeys=['Site Number', 'Site Name', 'RC Site Name',],) except (xlrdutils.XlrdutilsReadHeaderError, xlrdutils.XlrdutilsDateParseError) as e: warningMessage = repr(e) print warningMessage raise e sys.exit(-1) siteDict={} rcSiteNameList = data['RC Site Name'] siteNameList = data['Site Name'] siteNumberList = data['Site Number'] for k in range(len(rcSiteNameList)): if 'Totals' not in rcSiteNameList[k] and rcSiteNameList[k] != '': try: siteDict[rcSiteNameList[k]]=(siteNameList[k],siteNumberList[k]) except KeyError as e: print repr(e) raise e sys.exit(-1) return siteDict
def import_sites_from_xls(cls, filename=None, file_contents=None): data = None workbook = None warningMessage = "" try: workbook = xlrdutils.open_workbook(filename=filename, file_contents=file_contents) except (xlrdutils.XlrdutilsOpenWorkbookError, xlrdutils.XlrdutilsOpenSheetError) as e: warningMessage = repr(e) return (data, workbook, warningMessage) try: data = xlrdutils.read_lines( workbook, sheet="Sites", headerKeys=["Site Number", "Site Name"], zone=settings.TIME_ZONE ) except (xlrdutils.XlrdutilsReadHeaderError, xlrdutils.XlrdutilsDateParseError) as e: warningMessage = repr(e) return (data, workbook, warningMessage)
def import_product_information_from_xls(cls, filename=None, file_contents=None): data = None workbook = None warningMessage = "" try: workbook = xlrdutils.open_workbook(filename=filename, file_contents=file_contents) except (xlrdutils.XlrdutilsOpenWorkbookError, xlrdutils.XlrdutilsOpenSheetError) as e: warningMessage = repr(e) return (data, workbook, warningMessage) try: data = xlrdutils.read_lines( workbook, sheet="Products", headerKeys=["Product Code", "Product Name", "Unit of Measure", "Qty of Measure"], zone=settings.TIME_ZONE, ) except (xlrdutils.XlrdutilsReadHeaderError, xlrdutils.XlrdutilsDateParseError) as e: warningMessage = repr(e) return (data, workbook, warningMessage)
def main(): # begin main program usage = "usage: %prog -d INVENTORYDIR" parser = OptionParser(usage) parser.add_option("-d", "--dir", dest="inventoryDirFullPathName", help="read inventory spreadsheets in INVENTORYDIR") (options, args) = parser.parse_args() if not options.inventoryDirFullPathName: parser.error("No -d INVENTORYDIR supplied") # determine inventory directory name inventoryDirFullPathName = options.inventoryDirFullPathName dirContents = glob.glob(inventoryDirFullPathName + os.sep + '*inventory*.xls') deliverySiteFilename = inventoryDirFullPathName + os.sep + 'Delivery_Sites.xls' productInformationFilename = inventoryDirFullPathName + os.sep + 'Product_Information_Each.xls' dirContents.sort() allData = {} sheets = ['DS Supplies', 'Food Related', 'Clothing', 'Other',] for filename in dirContents: print filename try: workbook=xlrdutils.open_workbook(filename=filename) except (xlrdutils.XlrdutilsOpenWorkbookError, xlrdutils.XlrdutilsOpenSheetError) as e: warningMessage = repr(e) print warningMessage raise e continue for k in range(len(sheets)): sheetName = sheets[k] try: data=xlrdutils.read_lines(workbook, headerKeys=['Location.*',], sheet=sheetName,) except (xlrdutils.XlrdutilsReadHeaderError, xlrdutils.XlrdutilsDateParseError) as e: warningMessage = repr(e) print warningMessage raise e sys.exit(-1) for header in data.keys(): if 'Location' in header: # returns list of tuples [(siteName, siteNumber),] siteList = parse_sites(data[header],deliverySiteFilename) for headerVal,siteQtyList in data.iteritems(): if 'Location' not in headerVal: if headerVal not in allData: allData[headerVal] = [] for k in range(len(siteList)): # headerVal is the product code, so we are appending a # three tuple (siteName, siteNumber, product quantity) to # a list of three tuples for a given product code allData[headerVal].append((siteList[k][0],siteList[k][1],siteQtyList[k])) # this puts out package counts extendedData = calculate_pkg_qty(allData, productInformationFilename) # this puts out piece counts #extendedData = allData inventoryData={} for code,siteInventoryList in extendedData.iteritems(): for inventoryTuple in siteInventoryList: if inventoryTuple[2] != '' and inventoryTuple[2] != 'na' and inventoryTuple[2] != 'x' and inventoryTuple[2] != 'X': if code not in inventoryData: inventoryData[code] = [] inventoryData[code].append(inventoryTuple) xls = create_inventory_workbook(inventoryData) xls.save(inventoryDirFullPathName + os.sep + 'Inventory.xls')