def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) second_column_values = sheet.col_values(1,1) min_value = min(second_column_values) max_value = max(second_column_values) avg_value = sum(second_column_values) / len(second_column_values) min_pos = second_column_values.index(min_value) max_pos = second_column_values.index(max_value) min_value_exceltime = sheet.cell_value(min_pos+1,0) max_value_exceltime = sheet.cell_value(max_pos+1,0) min_value_time = xlrd.xldate_as_tuple(min_value_exceltime, 0) max_value_time = xlrd.xldate_as_tuple(max_value_exceltime, 0) ### example on how you can get the data #sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] ### other useful methods: # print "\nROWS, COLUMNS, and CELLS:" # print "Number of rows in the sheet:", # print sheet.nrows # print "Type of data in cell (row 3, col 2):", # print sheet.cell_type(3, 2) # print "Value in cell (row 3, col 2):", # print sheet.cell_value(3, 2) # print "Get a slice of values in column 3, from rows 1-3:" # print sheet.col_values(3, start_rowx=1, end_rowx=4) # print "\nDATES:" # print "Type of data in cell (row 1, col 0):", # print sheet.cell_type(1, 0) # exceltime = sheet.cell_value(1, 0) # print "Time in Excel format:", # print exceltime # print "Convert time to a Python datetime tuple, from the Excel float:", # print xlrd.xldate_as_tuple(exceltime, 0) """data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': 0, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 0, 'avgcoast': 0 }""" data = {} data['maxtime'] = max_value_time data['maxvalue'] = max_value data['mintime'] = min_value_time data['minvalue'] = min_value data['avgcoast'] = avg_value return data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) header_dict = {} ### example on how you can get the data sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] cv = sheet.col_values(1, start_rowx=1, end_rowx=None) maxval = max(cv) minval = min(cv) maxpos = cv.index(maxval) + 1 minpos = cv.index(minval) + 1 maxtime = sheet.cell_value(maxpos, 0) realtime = xlrd.xldate_as_tuple(maxtime, 0) mintime = sheet.cell_value(minpos, 0) realmintime = xlrd.xldate_as_tuple(mintime, 0) data = {} data['maxtime'] = realtime data['maxvalue'] = maxval data['mintime'] = realmintime data['minvalue'] = minval data['avgcoast'] = sum(cv) / float(len(cv)) return data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': 0, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 0, 'avgcoast': 0 } column_index = -1 for index, row_name in enumerate(sheet.row_values(0)): if row_name == KEYWORD: column_index = index # sanity check assert column_index != -1, "The keyword {0} is not contained in the given table".format(KEYWORD) vals = sheet.col_values(column_index)[1:] # return both index and value a given list. Also taking advantage of the # max/min defaultly measuring the 1st item for each tuple data["maxvalue"], index = max((v, i) for i, v in enumerate(vals)) data["maxtime"] = xlrd.xldate_as_tuple(sheet.cell_value(index+1, 0), 0) data["minvalue"], index = min((v, i) for i, v in enumerate(vals)) data["mintime"] = xlrd.xldate_as_tuple(sheet.cell_value(index+1, 0), 0) data["avgcoast"] = sum(vals)/len(vals) return data
def parse_xls_2(infile, account_number='unknown'): ''' Parses another XLS format detected on the exports. This format is used for prepaid VISA cards. Columns: * Accounting date * Operation date * Card number * Description * Original amount * Amount EUR ''' account_info = AccountInfo(account_number, '') book = open_workbook(infile.name) sheet = book.sheet_by_index(0) transactions = [] for row_index in range(1, sheet.nrows): line = [sheet.cell(row_index, col_index).value for col_index in range(sheet.ncols)] acdate, op_date, card_no, description, orig_amount, real_amount = line acdate = date(*xldate_as_tuple(acdate, book.datemode)[:3]) op_date = date(*xldate_as_tuple(op_date, book.datemode)[:3]) transactions.append(QIFTransaction( acdate, Decimal('%.2f' % real_amount), description, '', '' )) return TransactionList(account_info, transactions)
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ## get the data from sheet data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] print data cv = sheet.col_values(1, start_rowx=1, end_rowx=None) maxval = max(cv) minval = min(cv) ## add +1 because our data (cv) was formed from 1 row, not from 0 row. maxpos = cv.index(maxval) + 1 minpos = cv.index(minval) + 1 ## get max and min time according to position of maxval and minval maxtime = sheet.cell_value(maxpos, 0) realmaxtime = xlrd.xldate_as_tuple(maxtime,0) mintime = sheet.cell_value(minpos, 0) realmintime = xlrd.xldate_as_tuple(mintime, 0) data = { 'maxtime': realmaxtime, 'maxvalue': maxval, 'mintime': realmintime, 'minvalue': minval, 'avgcoast': numpy.mean(cv) } print data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] coast_row = sheet.col_values(1, start_rowx=1, end_rowx=sheet.nrows) sum = 0 min = sys.float_info.max minidx = -1 max = -sys.float_info.max maxidx = -1 count = 0 for num in coast_row: if isinstance(num, float): sum += num count += 1 if min > num: min = num minidx = coast_row.index(num) if max < num: max = num maxidx = coast_row.index(num) data = { 'maxtime': xlrd.xldate_as_tuple(sheet.cell_value(maxidx + 1, 0), 0), 'maxvalue': max, 'mintime': xlrd.xldate_as_tuple(sheet.cell_value(minidx + 1, 0), 0), 'minvalue': min, 'avgcoast': sum / count } return data
def get_daily_report(sheet): """Get Daily Report data from excel file""" daily_report = [] for i in range(sheet.nrows): a = [] for j in range(sheet.ncols): if type(sheet.cell_value(i, 12)) == float: if j == 10: # checkin date checkOut = xldate_as_tuple(sheet.cell(i, j).value, book.datemode) a.append(checkOut) if j == 8: # checkout date checkIn = xldate_as_tuple(sheet.cell(i, j).value, book.datemode) a.append(checkIn) elif j == 1: room = sheet.cell_value(i, j) a.append(room) elif j == 2: name = sheet.cell_value(i, j) a.append(name) else: pass if a != []: daily_report.append(a) return daily_report
def readExcel(inputFile): donation_ids = [] fields = {"DonationDate":"date","Amount":"amount","DonationMode":"mode","ModeDescription":"mode_description","DonorEmail":"email_address","Name":"name","ContactNo":"contact_number","Address":"address"} order_of_fields = {} workbook = xlrd.open_workbook(inputFile) worksheet = workbook.sheet_by_index(0) num_rows = worksheet.nrows - 1 num_cells = worksheet.ncols - 1 curr_row = -1 if num_cells > 0: curr_row += 1 curr_cell = -1 while curr_cell < num_cells: curr_cell += 1 cell_value = worksheet.cell_value(curr_row, curr_cell) order_of_fields[cell_value] = curr_cell while curr_row < num_rows: row = [] curr_row += 1 row = worksheet.row(curr_row) print 'Row:', curr_row curr_cell = -1 while curr_cell < num_cells: curr_cell += 1 cell_type = worksheet.cell_type(curr_row, curr_cell) cell_value = worksheet.cell_value(curr_row, curr_cell) if(cell_type > 4 or cell_type == 0): row.append(None) elif(cell_type == 3): year, month, day, hour, minute, second = xlrd.xldate_as_tuple(cell_value, workbook.datemode) row.append(datetime.datetime(year, month, day, hour, minute, second)) else: row.append(cell_value) email = row[order_of_fields["DonorEmail"]].value if not email: raise Exception("no email"); donor = get_donor_by_email(email) is_new_donor = False if not donor: password = ''.join(random.choice(string.ascii_letters + string.digits) for _ in range(6)) name = row[order_of_fields["Name"]].value address = row[order_of_fields["Address"]].value contact_number = row[order_of_fields["ContactNo"]].value donor = Donor(email_address=email, password_sha256=hashlib.sha256(password).hexdigest(), is_admin=False, name=name, contact_number=contact_number, address=address) is_new_donor = True date = row[order_of_fields["DonationDate"]].value year, month, day, hour, minute, second = xlrd.xldate_as_tuple(date, workbook.datemode) date = datetime.datetime(year, month, day, hour, minute, second) amount = row[order_of_fields["Amount"]].value mode = row[order_of_fields["DonationMode"]].value mode_description = row[order_of_fields["ModeDescription"]].value donation = Donation(date, amount, mode, mode_description) donor.donations.append(donation) db.session.add(donor) db.session.commit() if is_new_donor: sendEmail(donor.email_address, donor_created_text.format(password), None) donation_ids.append(donation.id) return donation_ids
def parse_excel(input_directory, output_directory): for path, dirs, files in os.walk(input_directory): for file_name in files: if not file_name.endswith('.xlsx'): continue wb = xlrd.open_workbook(os.path.join(path, file_name)) sh = wb.sheet_by_index(0) records = [] for rownum in range(1, sh.nrows): row = sh.row_values(rownum) year, month, day = xlrd.xldate_as_tuple(row[2], wb.datemode)[:3] hour, minute, second = xlrd.xldate_as_tuple(row[3], wb.datemode)[3:] records.append({ 'row': int(row[0]), 'device': row[1], 'year': year, 'month': month, 'day': day, 'hour': hour, 'minute': minute, 'second': second, 'latitude': row[4], 'longitude': row[5], 'speed': row[6], 'direction': row[7], 'numSatellites': int(row[8]) }) json_file_name = os.path.splitext(file_name)[0] + '.json' json_path = os.path.join(output_directory, json_file_name) with open(json_path, 'w') as f: json.dump(records, f, separators=(',', ':'))
def read_xl_doc(): # We want the data in the first columns of the various sheets. alldata = [] print_data = [] import xlrd with xlrd.open_workbook('SchwartzSmithMultiMarketExampleData.xls') as wb: for sheet_name in wb.sheet_names(): sheet = wb.sheet_by_name(sheet_name) month_col_title = sheet.cell_value(1, 0) assert month_col_title == 'Month', month_col_title from xlrd import xldate_as_tuple months = [datetime.datetime(*xldate_as_tuple(c.value, wb.datemode)) for c in sheet.col_slice(0)[2:]] fo_col_title = sheet.cell_value(1, 1) assert fo_col_title == 'F0', fo_col_title futures = [c.value for c in sheet.col_slice(1)[2:]] iv_col_title = sheet.cell_value(1, 2) assert iv_col_title == "Vol", iv_col_title impvols = [c.value for c in sheet.col_slice(2)[2:]] def param(params, key, row, col): v = sheet.cell_value(row, col) if isinstance(key, basestring) and key.lower()[-4:] == 'date': v = datetime.datetime(*xldate_as_tuple(v, wb.datemode)) params[key] = v observation_date = datetime.datetime(*xldate_as_tuple(sheet.cell_value(21, 15), wb.datemode)) seasonal_factors = [1] * 12 for month_int in range(12): param(seasonal_factors, month_int, month_int+24, 14) params = {} param(params, 'kappa', 18, 13) param(params, 'mue', 18, 14) param(params, 'sigmax', 18, 15) param(params, 'sigmae', 18, 16) param(params, 'lambdae', 18, 17) param(params, 'lambdax', 18, 18) param(params, 'pxe', 18, 19) param(params, 'x0', 18, 20) param(params, 'e0', 18, 21) alldata.append([observation_date, months, futures, impvols, seasonal_factors, params]) idata = { 'observation_date': "%04d-%02d-%02d" % (observation_date.year, observation_date.month, observation_date.day), 'months': ["%04d-%02d-%02d" % (m.year, m.month, m.day) for m in months], 'futures': [i for i in futures], 'impvols': [i for i in impvols] } print_data.append(idata) import json print("import datetime") print("from numpy import array") print() print(json.dumps(print_data, indent=4)) return alldata
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': 0, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 0, 'avgcoast': 0 } data['maxvalue'] = sheet_data[1][1] data['maxtime'] = xlrd.xldate_as_tuple(sheet_data[1][0], 0) data['minvalue'] = sheet_data[1][1] data['mintime'] = xlrd.xldate_as_tuple(sheet_data[1][0], 0) data['avgcoast'] += data['avgcoast'] + sheet_data[1][1] for i in (range(2,sheet.nrows)): if (sheet_data[i][1]>data['maxvalue']): data['maxvalue'] = sheet_data[i][1] data['maxtime'] = xlrd.xldate_as_tuple(sheet_data[i][0], 0) if (sheet_data[i][1]<data['minvalue']): data['minvalue'] = sheet_data[i][1] data['mintime'] = xlrd.xldate_as_tuple(sheet_data[i][0], 0) data['avgcoast'] += sheet_data[i][1] #print data['avgcoast'] data['avgcoast'] = data['avgcoast']/(sheet.nrows-1) return data
def create_event(row_data, datemode): title, start_date, end_date, slug, topics, importance = row_data # we could bulk create the events for efficiency later if necessary start_date = datetime(*xldate_as_tuple(start_date, datemode)) end_date = datetime(*xldate_as_tuple(end_date, datemode)) ev, new = Event.objects.get_or_create(title=title.strip(), start_date=start_date, end_date=end_date, slug=slug.strip(), importance=importance) if not new: ev.start_date = start_date ev.end_date = end_date ev.title = title.strip() ev.importance = importance topics = topics.split(',') for topic in topics: topic = topic.strip() t, created = Topic.objects.get_or_create(name=topic) t.save() ev.topics.add(t) ev.save() db.close_connection()
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] cv =sheet.col_values(1, start_rowx=1, end_rowx=None) maxval = max(cv) minval = min(cv) maxpos=cv.index(maxval) +1 minpos=cv.index(minval) +1 maxtime = sheet.cell_value(maxpos,0) realtime = xlrd.xldate_as_tuple(maxtime,0) mintime = sheet.cell_value(minpos,0) realmintime = xlrd.xldate_as_tuple(mintime,0) data = { 'maxtime': realtime, 'maxvalue': maxval, 'mintime': realmintime, 'minvalue': minval, 'avgcoast': sum(cv) / float(len(cv)) } return data
def parse_xfile(DataFile): workbook=xlrd.open_workbook(DataFile) sheet=workbook.sheet_by_index(0) cv=sheet.col_values(1,start_rowx=1,end_rowx=None) avgcoast=sum(cv)/len(cv) maxvalue=max(cv) minvalue=min(cv) maxpos=cv.index(maxvalue)+1 minpos=cv.index(minvalue)+1 maxtime=sheet.cell_value(maxpos,0) mintime=sheet.cell_value(minpos,0) realmaxtime=xlrd.xldate_as_tuple(maxtime,0) realmintime=xlrd.xldate_as_tuple(mintime,0) data={ 'maxvalue': maxvalue, 'realmaxtime': realmaxtime, 'minvalue':minvalue, 'realmintime':realmintime, 'avgcoast':avgcoast } return data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ### example on how you can get the data #sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] ### other useful methods: # print "\nROWS, COLUMNS, and CELLS:" # print "Number of rows in the sheet:", # print sheet.nrows # print "Type of data in cell (row 3, col 2):", # print sheet.cell_type(3, 2) # print "Value in cell (row 3, col 2):", # print sheet.cell_value(3, 2) # print "Get a slice of values in column 3, from rows 1-3:" # print sheet.col_values(3, start_rowx=1, end_rowx=4) # print "\nDATES:" # print "Type of data in cell (row 1, col 0):", # print sheet.cell_type(1, 0) # exceltime = sheet.cell_value(1, 0) # print "Time in Excel format:", # print exceltime # print "Convert time to a Python datetime tuple, from the Excel float:", # print xlrd.xldate_as_tuple(exceltime, 0) cv = sheet.col_values(1,start_rowx = 1, end_rowx = None) data = { 'maxtime': xlrd.xldate_as_tuple(sheet.cell_value(cv.index(max(cv))+1, 0),0), 'maxvalue': max(cv), 'mintime': xlrd.xldate_as_tuple(sheet.cell_value(cv.index(min(cv))+1, 0),0), 'minvalue': min(cv), 'avgcoast': sum(cv)/len(cv) } return data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ### example on how you can get the data minval = min(sheet.col_values(1,1,sheet.nrows)) maxval = max(sheet.col_values(1,1,sheet.nrows)) avg = (sum(sheet.col_values(1,1,sheet.nrows))/(sheet.nrows-1)) #print minval minpos = sheet.col_values(1,1, sheet.nrows-1).index(minval) + 1 #print minpos #print maxval maxpos = sheet.col_values(1,1, sheet.nrows-1).index(maxval) + 1 #print maxpos maxtime = xlrd.xldate_as_tuple(sheet.cell_value(maxpos,0),0) mintime = xlrd.xldate_as_tuple(sheet.cell_value(minpos,0),0) print(maxtime) #print mintime '''sheet_data = [sheet.cell_value(r, c) for r in range(1, sheet.nrows) for c in range(0, 1)] minval = min(sheet_data) maxval = max(sheet_data) print(sheet_data.col_values(1,1,100))''' #print(maxval) ### other useful methods: # print "\nROWS, COLUMNS, and CELLS:" # print "Number of rows in the sheet:", # print sheet.nrows # print "Type of data in cell (row 3, col 2):", # print sheet.cell_type(3, 2) # print "Value in cell (row 3, col 2):", # print sheet.cell_value(3, 2) # print "Get a slice of values in column 3, from rows 1-3:" # print sheet.col_values(3, start_rowx=1, end_rowx=4) # print "\nDATES:" # print "Type of data in cell (row 1, col 0):", # print sheet.cell_type(1, 0) # exceltime = sheet.cell_value(1, 0) # print "Time in Excel format:", # print exceltime # print "Convert time to a Python datetime tuple, from the Excel float:", # print xlrd.xldate_as_tuple(exceltime, 0) data = { 'maxtime': maxtime, 'maxvalue': maxval, 'mintime': mintime, 'minvalue': minval, 'avgcoast': avg } return data
def parse_date(val, wb): try: date_value = xldate_as_tuple(val,wb.datemode) t = datetime(* (xldate_as_tuple(val,wb.datemode))).strftime('%d/%m/%Y') return t except xldate.XLDateAmbiguous: return val
def parse_file(df): workbook = xlrd.open_workbook(df) sheet = workbook.sheet_by_index(0) #all values in sheet #sheet_data = [[sheet.cell_value(r, col) # for col in range(sheet.ncols)] #for r in range(sheet.nrows)] coast = sheet.col_values(1, start_rowx=1, end_rowx=7297) #or end_rox=None for all values maxval = max(coast) minval = min(coast) maxpos = coast.index(maxval)+1 minpos = coast.index(minval)+1 maxtime = sheet.cell_value(maxpos,0) realmaxtime = xlrd.xldate_as_tuple(maxtime,0) mintime = sheet.cell_value(minpos,0) realmintime = xlrd.xldate_as_tuple(mintime,0) avgcoast = float(sum(coast))/ len(coast) data = { 'maxtime': realmaxtime, 'maxvalue': maxval, 'mintime': realmintime, 'minvalue': minval, 'avgcoast': avgcoast } return data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) max_coast = None max_time = None min_coast = None min_time = None total_coast = 0 for r in range(1, sheet.nrows): coast = sheet.cell_value(r, 1) if coast > max_coast or max_coast is None: max_coast = coast max_time = sheet.cell_value(r, 0) if coast < min_coast or min_coast is None: min_coast = coast min_time = sheet.cell_value(r, 0) total_coast += coast data = { 'maxtime': xlrd.xldate_as_tuple(max_time, 0), 'maxvalue': max_coast, 'mintime': xlrd.xldate_as_tuple(min_time, 0), 'minvalue': min_coast, 'avgcoast': total_coast / (sheet.nrows - 1) } return data
def process_plan_rates(plans, filename): from xlrd import open_workbook, xldate_as_tuple wb = open_workbook(filename) for ws in wb.sheets(): if not ws.name.startswith("Rate Table"): continue if ws.cell(0,0).value not in ("Rates Table Template v2.2", "Rates Table Template v2.3"): raise Exception("Invalid rates table: " + ws.cell(0,0).value + " in " + filename) rate_effective_date = datetime(*xldate_as_tuple(ws.cell(7, 1).value, wb.datemode)).isoformat() rate_expiration_date = datetime(*xldate_as_tuple(ws.cell(8, 1).value, wb.datemode)).isoformat() for r in xrange(13, ws.nrows): plan = ws.cell(r, 0).value age = ws.cell(r, 3).value if isinstance(age, float): age = str(int(age)) plan_rates = plans[plan].setdefault("rates", []) plan_rates.append({ "rating_area": ws.cell(r, 1).value, "tobacco_use": ws.cell(r, 2).value, "age": age, "rate": ws.cell(r, 4).value, "effective": rate_effective_date, # applies to all rates, so this isn't the right place to encode it "expires": rate_expiration_date, # applies to all rates, so this isn't the right place to encode it })
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) coast = 1 date = 0 sum = 0 val = 0 data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': -1, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 9999999, 'avgcoast': 0 } for row in range(1, sheet.nrows): val = sheet.cell_value(row, coast) sum += val #find min if val < data['minvalue']: data['minvalue'] = val data['mintime'] = xlrd.xldate_as_tuple(sheet.cell_value(row, date), 0) #find max if val > data['maxvalue']: data['maxvalue'] = val data['maxtime'] = xlrd.xldate_as_tuple(sheet.cell_value(row, date), 0) data['avgcoast'] = sum / (sheet.nrows - 1) return data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ### example on how you can get the data #sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] ### other useful methods: # print "\nROWS, COLUMNS, and CELLS:" # print "Number of rows in the sheet:", # print sheet.nrows # print "Type of data in cell (row 3, col 2):", # print sheet.cell_type(3, 2) # print "Value in cell (row 3, col 2):", # print sheet.cell_value(3, 2) # print "Get a slice of values in column 3, from rows 1-3:" # print sheet.col_values(3, start_rowx=1, end_rowx=4) # print "\nDATES:" # print "Type of data in cell (row 1, col 0):", # print sheet.cell_type(1, 0) # exceltime = sheet.cell_value(7200, 0) # print "Time in Excel format:", # print exceltime # print "Convert time to a Python datetime tuple, from the Excel float:", # print xlrd.xldate_as_tuple(exceltime, 0) max_candidate = sheet.cell_value(1, 1) max_row = 0 min_candidate, min_row = max_candidate, max_row total = 0 for r in range(1, sheet.nrows): # used for loop to only loop through list once, # but it has more comparisons and declarations # than slicing methods total += sheet.cell_value(r, 1) if sheet.cell_value(r, 1) > max_candidate: max_candidate = sheet.cell_value(r, 1) max_row = r elif sheet.cell_value(r, 1) < min_candidate: min_candidate = sheet.cell_value(r, 1) min_row = r data = { 'maxtime': xlrd.xldate_as_tuple(sheet.cell_value(max_row, 0), 0), 'maxvalue': max_candidate, 'mintime': xlrd.xldate_as_tuple(sheet.cell_value(min_row, 0), 0), 'minvalue': min_candidate, 'avgcoast': total / (sheet.nrows - 1) } return data
def import_relationships_from_excel(import_model, field_name, clean, file_contents, override_import_dict=None, request=None): import_dict = override_import_dict or import_model.import_dict or None if not import_dict: raise Exception('No import dictionary!') import_wb = xlrd.open_workbook(file_contents=file_contents) import_sheet = import_wb.sheet_by_index(0) relation_info_dict = import_dict['relationship_colmap'][field_name] already_cleaned_object_list = [] relationships_added = 0 relationships_not_added = 0 relationships_not_added_error = 0 for rx in range(1, import_sheet.nrows): #try: src_identity_dict = {} trg_identity_dict = {} src_identity_col_list = relation_info_dict['src_identity_cols'] trg_identity_col_list = relation_info_dict['trg_identity_cols'] # Get source object for this row. for src_identity_col in src_identity_col_list: src_identity_field_name = relation_info_dict['src_col_mapping'][src_identity_col] cell_value = import_sheet.cell_value(rowx=rx, colx=src_identity_col) if import_sheet.cell_type(rowx=rx, colx=src_identity_col) == xlrd.XL_CELL_DATE: date_tuple = xlrd.xldate_as_tuple(cell_value, import_wb.datemode) cell_value = datetime.date(date_tuple[0],date_tuple[1],date_tuple[2]) src_identity_dict[src_identity_field_name] = cell_value src_object = import_model.objects.get(**src_identity_dict) related_objects_set = getattr(src_object, field_name) # Clean out related objects property if needed but only if you've # not already done so for this particular src object. if (not src_object in already_cleaned_object_list) and clean: related_objects_set.clear() already_cleaned_object_list.append(src_object) # Get the target (related) object for this row. trg_app_name = relation_info_dict['trg_app'] trg_model_name = relation_info_dict['trg_model'] trg_model = get_model(trg_app_name, trg_model_name) for trg_identity_col in trg_identity_col_list: trg_identity_field_name = relation_info_dict['trg_col_mapping'][trg_identity_col] cell_value = import_sheet.cell_value(rowx=rx, colx=trg_identity_col) if import_sheet.cell_type(rowx=rx, colx=trg_identity_col) == xlrd.XL_CELL_DATE: print cell_value date_tuple = xlrd.xldate_as_tuple(cell_value, import_wb.datemode) cell_value = datetime.date(date_tuple[0],date_tuple[1],date_tuple[2]) trg_identity_dict[trg_identity_field_name] = cell_value trg_object = trg_model.objects.get(**trg_identity_dict) # Add the trg object to the related objects property of the src object. if not trg_object in related_objects_set.all(): related_objects_set.add(trg_object) relationships_added += 1 else: relationships_not_added += 1 #except: # relationships_not_changed_error += 1 return relationships_added, relationships_not_added, relationships_not_added_error
def get_upcoming_increases(): ##################################read xls file book = xlrd.open_workbook('/home/ubuntu/dgrpred/U.S.DividendChampionsmr.xls') sheet = book.sheet_by_index(0) sheet2 = book.sheet_by_index(1) ex_date_dict = {} for i in range(6, 112): ex_date = sheet.row_values(i, start_colx=12, end_colx=13)[0] ex_date = datetime.datetime(*xlrd.xldate_as_tuple(ex_date, book.datemode)) name = sheet.row_values(i, start_colx = 1, end_colx=2)[0] ex_date_dict[name.lower()] = ex_date for i in range(6, 256): name = sheet2.row_values(i, start_colx = 1, end_colx=2)[0] ex_date = sheet2.row_values(i, start_colx=12, end_colx=13)[0] ex_date = datetime.datetime(*xlrd.xldate_as_tuple(ex_date, book.datemode)) ex_date_dict[name.lower()] = ex_date ###################################################################################################### ##################################estimate next increase date, 30 days and 60 days into future, return dictionaries now = datetime.datetime.now() this_year = now.year ex_dec_diff = joblib.load('/home/ubuntu/dgrpred/dump/ex_dec_diff') #the difference between declaration/ex date STATIC upcoming_one_month = {} upcoming_two_month = {} for i in ex_date_dict: #for each ticker symbol if ex_date_dict[i].year == this_year or i not in ex_dec_diff: # if the MR increase was last year continue ex_date_dict[i]= ex_date_dict[i].replace(year=this_year) now = datetime.datetime.now() one_month_ahead= now + datetime.timedelta(days = 30) two_months_ahead= now + datetime.timedelta(days = 60) one_week_ago = now - datetime.timedelta(days = 7) est = abs(ex_dec_diff[i][0])+ abs(ex_dec_diff[i][1]) #average + std + 7 days announcement_est = ex_date_dict[i] - datetime.timedelta(days = est) #announcement_est -= datetime.timedelta(days = 5) if (announcement_est <= one_month_ahead) and (announcement_est > one_week_ago ): upcoming_one_month[i] = announcement_est # print announcement_est, i, ex_dec_diff[i][0], ex_dec_diff[i][1] # print if (announcement_est <= two_months_ahead) and (announcement_est > one_week_ago ): upcoming_two_month[i] = announcement_est # print '60 days', announcement_est, i, ex_dec_diff[i][0], ex_dec_diff[i][1] joblib.dump(upcoming_one_month, '/home/ubuntu/dgrpred/dump/temp_persistence/upcoming_one_month') joblib.dump(upcoming_two_month, '/home/ubuntu/dgrpred/dump/temp_persistence/upcoming_two_month') return upcoming_one_month, upcoming_two_month
def get_voyage_data(book): voyage_data = {} voyage_data['ship_name'] = book.sheet_by_index(2).cell(0, 7).value voyage_data['voyage_number'] = book.sheet_by_index(2).cell(1, 7).value voyage_data['load_condition'] = book.sheet_by_index(2).cell(2, 7).value voyage_data['cargo_desc'] = book.sheet_by_index(2).cell(3, 7).value if book.sheet_by_index(2).cell(3, 7).value else None voyage_data['cargo_weight'] = book.sheet_by_index(2).cell(4, 7).value if book.sheet_by_index(2).cell(4, 7).value else None voyage_data['departure_port'] = book.sheet_by_index(2).cell(0, 12).value voyage_data['departure_date'] = xlrd.xldate_as_tuple(book.sheet_by_index(2).cell(1, 12).value, book.datemode) if book.sheet_by_index(2).cell(1, 12).value else None if voyage_data['departure_date']: voyage_data['departure_date'] = np.datetime64( '{:>04}'.format(str(voyage_data['departure_date'][0])) + '-' + '{:>02}'.format(str(voyage_data['departure_date'][1])) + '-' + '{:>02}'.format(str(voyage_data['departure_date'][2])) ) voyage_data['departure_draft_fore'] = book.sheet_by_index(2).cell(2, 12).value voyage_data['departure_draft_mid'] = book.sheet_by_index(2).cell(3, 12).value voyage_data['departure_draft_aft'] = book.sheet_by_index(2).cell(4, 12).value voyage_data['arrival_port'] = book.sheet_by_index(2).cell(0, 16).value voyage_data['arrival_date'] = xlrd.xldate_as_tuple(book.sheet_by_index(2).cell(1, 16).value, book.datemode) if book.sheet_by_index(2).cell(1, 16).value else None if voyage_data['arrival_date']: voyage_data['arrival_date'] = np.datetime64( '{:>04}'.format(str(voyage_data['arrival_date'][0])) + '-' + '{:>02}'.format(str(voyage_data['arrival_date'][1])) + '-' + '{:>02}'.format(str(voyage_data['arrival_date'][2])) ) voyage_data['arrival_draft_fore'] = book.sheet_by_index(2).cell(2, 16).value voyage_data['arrival_draft_mid'] = book.sheet_by_index(2).cell(3, 16).value voyage_data['arrival_draft_aft'] = book.sheet_by_index(2).cell(4, 16).value voyage_data['head_charterer'] = book.sheet_by_index(2).cell(0, 20).value if book.sheet_by_index(2).cell(0, 20).value else None voyage_data['head_charterer_date'] = xlrd.xldate_as_tuple(book.sheet_by_index(2).cell(1, 20).value, book.datemode) if book.sheet_by_index(2).cell(1, 20).value else None if voyage_data['head_charterer_date']: voyage_data['head_charterer_date'] = np.datetime64( '{:>04}'.format(str(voyage_data['head_charterer_date'][0])) + '-' + '{:>02}'.format(str(voyage_data['head_charterer_date'][1])) + '-' + '{:>02}'.format(str(voyage_data['head_charterer_date'][2])) ) voyage_data['sub_charterer'] = book.sheet_by_index(2).cell(3, 20).value if book.sheet_by_index(2).cell(3, 20).value else None voyage_data['sub_charterer_date'] = xlrd.xldate_as_tuple(book.sheet_by_index(2).cell(4, 20).value, book.datemode) if book.sheet_by_index(2).cell(4, 20).value else None if voyage_data['sub_charterer_date']: voyage_data['sub_charterer_date'] = np.datetime64( '{:>04}'.format(str(voyage_data['sub_charterer_date'][0])) + '-' + '{:>02}'.format(str(voyage_data['sub_charterer_date'][1])) + '-' + '{:>02}'.format(str(voyage_data['sub_charterer_date'][2])) ) return voyage_data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ### example on how you can get the data #sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] ### other useful methods: # print "\nROWS, COLUMNS, and CELLS:" # print "Number of rows in the sheet:", # print sheet.nrows # print "Type of data in cell (row 3, col 2):", # print sheet.cell_type(3, 2) # print "Value in cell (row 3, col 2):", # print sheet.cell_value(3, 2) # print "Get a slice of values in column 3, from rows 1-3:" # print sheet.col_values(3, start_rowx=1, end_rowx=4) # print "\nDATES:" # print "Type of data in cell (row 1, col 0):", # print sheet.cell_type(1, 0) # exceltime = sheet.cell_value(1, 0) # print "Time in Excel format:", # print exceltime # print "Convert time to a Python datetime tuple, from the Excel float:", # print xlrd.xldate_as_tuple(exceltime, 0) data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': 0, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 0, 'avgcoast': 0 } maxval = 0 minval = 100000000 coasttotal = 0 for row in range(1,sheet.nrows): tval = sheet.cell_value(row,1) coasttotal = coasttotal + float(tval) if float(tval) > maxval: maxval = tval maxtime = sheet.cell_value(row,0) maxtime = xlrd.xldate_as_tuple(maxtime, 0) data['maxtime'] = maxtime data['maxvalue'] = maxval if float(tval) < minval: minval = tval mintime = sheet.cell_value(row,0) mintime = xlrd.xldate_as_tuple(mintime, 0) data['mintime'] = mintime data['minvalue'] = minval avg = coasttotal / ( sheet.nrows - 1) data['avgcoast'] = avg #print("Maxtime is " + str(maxtime)) #print("average is " + str(avg)) return data
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ### example on how you can get the data #sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] ### other useful methods: # print "\nROWS, COLUMNS, and CELLS:" # print "Number of rows in the sheet:", # print sheet.nrows # print "Type of data in cell (row 3, col 2):", # print sheet.cell_type(3, 2) # print "Value in cell (row 3, col 2):", # print sheet.cell_value(3, 2) # print "Get a slice of values in column 3, from rows 1-3:" # print sheet.col_values(3, start_rowx=1, end_rowx=4) # print "\nDATES:" # print "Type of data in cell (row 1, col 0):", # print sheet.cell_type(1, 0) # exceltime = sheet.cell_value(1, 0) # print "Time in Excel format:", # print exceltime # print "Convert time to a Python datetime tuple, from the Excel float:", # print xlrd.xldate_as_tuple(exceltime, 0) data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': 0, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 0, 'avgcoast': 0 } column = sheet.col_values(1) total = 0 numr = sheet.nrows count = 1 while count < numr: cell = sheet.cell_value(count, 1) date = sheet.cell_value(count, 0) if count == 1: data['maxvalue'] = cell data['minvalue'] = cell else: if cell > data['maxvalue']: data['maxvalue'] = cell data['maxtime'] = xlrd.xldate_as_tuple(date, 0) elif cell < data['minvalue']: data['minvalue'] = cell data['mintime'] = xlrd.xldate_as_tuple(date, 0) count += 1 total += cell data['avgcoast'] = total / (numr - 1) return data
def Lire(self): """ Lecture des données """ nomFichier = self.ctrl_fichier.GetValue() if len(nomFichier) == 0 : dlg = wx.MessageDialog(self, _(u"Vous devez obligatoirement sélectionner un fichier de données à importer !"), _(u"Erreur"), wx.OK | wx.ICON_EXCLAMATION) dlg.ShowModal() dlg.Destroy() return False if os.path.isfile(nomFichier) == False : dlg = wx.MessageDialog(self, _(u"L'emplacement fichier que vous avez saisi n'existe pas !"), _(u"Erreur"), wx.OK | wx.ICON_EXCLAMATION) dlg.ShowModal() dlg.Destroy() return False # Lecture du fichier XLS try : classeur = xlrd.open_workbook(nomFichier) except : dlg = wx.MessageDialog(self, _(u"Le fichier Excel ne semble pas valide !"), _(u"Erreur"), wx.OK | wx.ICON_EXCLAMATION) dlg.ShowModal() dlg.Destroy() return False # Sélection de la feuille feuille = None feuilles = classeur.sheet_names() if len(feuilles) == 1 : feuille = classeur.sheet_by_index(0) else : # Demande la feuille à ouvrir dlg = wx.SingleChoiceDialog(None, _(u"Veuillez sélectionner la feuille du classeur qui comporte les données à importer :"), _(u"Sélection d'une feuille"), feuilles, wx.CHOICEDLG_STYLE) if dlg.ShowModal() == wx.ID_OK: feuille = classeur.sheet_by_index(dlg.GetSelection()) dlg.Destroy() # Lecture des données de la feuille listeDonnees = [] try : for num_ligne in range(feuille.nrows): # Codebarres codebarres = feuille.cell(rowx=num_ligne, colx=0).value # Date dateTuple = xlrd.xldate_as_tuple(feuille.cell(rowx=num_ligne, colx=1).value, classeur.datemode) date = datetime.date(*dateTuple[:3]) # Heure dateTuple = xlrd.xldate_as_tuple(feuille.cell(rowx=num_ligne, colx=2).value, classeur.datemode) heure = u"%02d:%02d" % (dateTuple[3], dateTuple[4]) # Mémorisation listeDonnees.append((codebarres, date, heure)) except Exception as err : listeDonnees = [] dlg = wx.MessageDialog(self, _(u"Noethys n'a pas réussi à lire les données !\n\nErreur : %s.\n\nNotez que les colonnes du fichier Excel doivent être les suivantes : 1. Code-barres 2. Date, 3. Heure.") % err, _(u"Erreur"), wx.OK | wx.ICON_EXCLAMATION) dlg.ShowModal() dlg.Destroy() return False return listeDonnees
def readPricesData(filePath): # Constants of the transformers excel files # Number of header lines. HEADER_LINES=2 # Number of the column with the energy prices ENERGY_PRICES_COLUMN=2 # Number of the column with the upward imbalance prices UPWARD_IMBALANCE_PRICES_COLUMN=3 # Number of the column with the downward imbalance prices DOWNWARD_IMBALANCE_PRICES_COLUMN=4 # Number of the column with the system imbalance SYSTEM_IMBALANCE_COLUMN=5 # Prices labels PRICES_LABELS={"energy price":ENERGY_PRICES_COLUMN, "upward imbalance price":UPWARD_IMBALANCE_PRICES_COLUMN, "downward imbalance price":DOWNWARD_IMBALANCE_PRICES_COLUMN,"system imbalance":SYSTEM_IMBALANCE_COLUMN} # Open the file workbook=xlrd.open_workbook(filePath,on_demand=True) sheet=workbook.sheet_by_index(0) # Get the initial date initialDayTuple = xlrd.xldate_as_tuple(sheet.cell_value(HEADER_LINES,0),workbook.datemode) initialDay = datetime.datetime(initialDayTuple[0], 1, 1, 0, 0, 0).toordinal() # Parse content dayRaw = None dayTimeSeries = {} timeSeries = {} for r in range(HEADER_LINES,sheet.nrows): row = sheet.row_values(r, 0, sheet.ncols) # New day? if row[0] != dayRaw: # Check the previous one is full if dayRaw is not None and len(dayTimeSeries['energy price']) != 96: dayTuple = xlrd.xldate_as_tuple(dayRaw,workbook.datemode) # Gregorian (year, month, day, hour, minute, nearest_second) d = datetime.datetime(dayTuple[0], dayTuple[1], dayTuple[2], dayTuple[3], dayTuple[4], dayTuple[5]) raise Exception("Laking prices for day %s, only %s entries instead of 96." % (d, len(dayTimeSeries['energy price']))) # New day, cast it as a day and convert it to day number dayRaw = row[0] dayTuple = xlrd.xldate_as_tuple(row[0],workbook.datemode) # Gregorian (year, month, day, hour, minute, nearest_second) day = datetime.datetime(dayTuple[0], dayTuple[1], dayTuple[2], dayTuple[3], dayTuple[4], dayTuple[5]) day = (day.toordinal()-initialDay)+1 # Initialize the time series dayTimeSeries = {} for p in PRICES_LABELS.keys(): dayTimeSeries[p] = [] timeSeries[day] = dayTimeSeries # Add the data for p,col in PRICES_LABELS.items(): v = float(row[col]) dayTimeSeries[p].append(v) return timeSeries
def parse_file(datafile): workbook = xlrd.open_workbook(datafile) sheet = workbook.sheet_by_index(0) ### example on how you can get the data sheet_data = [[sheet.cell_value(r, col) for col in range(sheet.ncols)] for r in range(sheet.nrows)] ### other useful methods: #print "\nROWS, COLUMNS, and CELLS:" #print "Number of rows in the sheet:", #print sheet.nrows #print "Type of data in cell (row 3, col 2):", #print sheet.cell_type(3, 2) #print "Value in cell (row 3, col 2):", #print sheet.cell_value(3, 2) #print "Get a slice of values in column 3, from rows 1-3:" #print sheet.col_values(3, start_rowx=1, end_rowx=4) #print "\nDATES:" #print "Type of data in cell (row 1, col 0):", #print sheet.cell_type(1, 0) #exceltime = sheet.cell_value(1, 0) #print "Time in Excel format:", #print exceltime #print "Convert time to a Python datetime tuple, from the Excel float:", #print xlrd.xldate_as_tuple(exceltime, 0) data = { 'maxtime': (0, 0, 0, 0, 0, 0), 'maxvalue': 0, 'mintime': (0, 0, 0, 0, 0, 0), 'minvalue': 0, 'avgcoast': 0 } times = [] values = [] for row in sheet_data[1:]: times.append(row[0]) values.append(float(row[1])) data['maxvalue'] = max(values) data['minvalue'] = min(values) for i in range(len(times)): if max(values) == values[i]: data['maxtime'] = xlrd.xldate_as_tuple(times[i],0) if min(values) == values[i]: data['mintime'] = xlrd.xldate_as_tuple(times[i],0) data['avgcoast'] = sum(values) / len(values) return data
def import_product_pricelist(self): if self.import_option == 'csv': if (self.file): keys = [ 'product', 'pricelist', 'price', 'min_qty', 'start_dt', 'end_dt' ] csv_data = base64.b64decode(self.file) data_file = io.StringIO(csv_data.decode("utf-8")) data_file.seek(0) file_reader = [] csv_reader = csv.reader(data_file, delimiter=',') try: file_reader.extend(csv_reader) except Exception: raise exceptions.Warning(_("Invalid file!")) values = {} for i in range(len(file_reader)): field = list(map(str, file_reader[i])) values = dict(zip(keys, field)) if values: if i == 0: continue else: values.update({'option': self.import_option}) res = self.make_product_pricelist(values) else: raise Warning(_('Please Seelect a file.')) else: if (self.file): fp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") fp.write(binascii.a2b_base64(self.file)) fp.seek(0) values = {} workbook = xlrd.open_workbook(fp.name) sheet = workbook.sheet_by_index(0) for row_no in range(sheet.nrows): val = {} if row_no <= 0: fields = map(lambda row: row.value.encode('utf-8'), sheet.row(row_no)) else: line = list( map( lambda row: isinstance(row.value, bytes) and row.value.encode('utf-8') or str(row.value), sheet.row(row_no))) start_date_string = False end_dt_string = False if line[4]: start_dt = int(float(line[4])) start_dt_datetime = datetime(*xlrd.xldate_as_tuple( start_dt, workbook.datemode)) start_date_string = start_dt_datetime.date( ).strftime('%Y-%m-%d') if line[5]: end_dt = int(float(line[5])) end_dt_datetime = datetime(*xlrd.xldate_as_tuple( end_dt, workbook.datemode)) end_dt_string = end_dt_datetime.date().strftime( '%Y-%m-%d') min_qty = 1 if line[3]: min_qty = int(float(line[3])) values.update({ 'product': line[0], 'pricelist': line[1], 'price': line[2], 'min_qty': min_qty, 'start_dt': start_date_string, 'end_dt': end_dt_string, }) res = self.make_product_pricelist(values) else: raise Warning(_('Please Seelect a file.'))
def import_ext_xls(): files = import_path_xls() # document = xlrd.open_workbook(DATA_DIR) document = xlrd.open_workbook(files[1]) feuille_1 = document.sheet_by_index(0) if feuille_1.cell_type(rowx=0, colx=0) == 1: num_monument = (feuille_1.cell_value(0, 0)) elif feuille_1.cell_type(rowx=0, colx=0) == 2: num_monument = str(int(feuille_1.cell_value(0, 0))) else: num_monument = "Erreur Syntaxe" # Si case vide # num_monument = str(int(feuille_1.cell_value(0, 0))) # name_monument = (feuille_1.cell_value(0, 1)) # name_monument = " ".join(name_monument.split()) # rem_monument = (feuille_1.cell_value(0, colx=2)) i = 0 name_monument = rem_monument = "" # try: while feuille_1.cell_value(rowx=i, colx=1).strip( ) != "HISTORIQUE": # boucle sur le Nom avant le Historique et Remarque name_monument += feuille_1.cell_value(rowx=i, colx=1) + " " rem_monument += feuille_1.cell_value(rowx=i, colx=2) + " " i += 1 name_monument = name_monument.strip() while " " in name_monument: name_monument = name_monument.replace(" ", " ") cols = feuille_1.ncols # Nombre de colonne rows = feuille_1.nrows # Nombre de ligne #################################################### COLONNE DATE : list_dates = [] for r in range(2, rows): if feuille_1.cell_type(rowx=r, colx=0) == 1 or feuille_1.cell_type( rowx=r, colx=0) == 6: # Cellule Texte ou Vide list_dates += [feuille_1.cell_value(rowx=r, colx=0)] elif feuille_1.cell_type(rowx=r, colx=0) == 3: # Cellule Date ms_date_number = feuille_1.cell_value(rowx=r, colx=0) year, month, day, hour, minute, second = xlrd.xldate_as_tuple( ms_date_number, document.datemode) py_date = datetime.datetime(year, month, day) py_date = py_date.strftime("%d-%m-%Y") list_dates += [py_date] elif feuille_1.cell_type(rowx=r, colx=0) == 2: # Cellule Nombre py_num = str(int(feuille_1.cell_value(rowx=r, colx=0))) list_dates += [py_num] else: # Cellule "autre" /!\ WARNING /!\ list_dates += [feuille_1.cell_value(rowx=r, colx=0)] #################################################### COL HITORIQUE : list_historique = [] for r in range(2, rows): list_historique += [feuille_1.cell_value(rowx=r, colx=1)] #################################################### COL REMARQUE : list_remarque = [] for r in range(2, rows): list_remarque += [feuille_1.cell_value(rowx=r, colx=2)] ######################################################### TEST # Il FAUT GARDER LA COMPARAISON ENTRE LES LISTES : len(list_dates) == len(list_historique) == len(list_remarque) nb_list_dates = len(list_dates) nb_list_historique = len(list_historique) nb_list_remarque = len(list_remarque) ######################################################### Création des listes i = 0 while i < nb_list_dates: if list_dates[i] != "" and list_historique[i] != "" and list_dates[ i - 1] == "" and list_historique[i - 1] == "": list_dates[i - 1] = list_historique[i - 1] = list_remarque[i - 1] = "&&&" i += 1 list_historique = ' '.join(list_historique) list_historique = list_historique.split("&&&") list_dates = ' '.join(list_dates) list_dates = list_dates.split("&&&") list_remarque = ' '.join(list_remarque) list_remarque = list_remarque.split("&&&") for dates in reversed(list_dates): if dates == "" or dates == " ": list_dates.remove(dates) for n, m in enumerate(list_dates): m = m.strip() list_dates[n] = m for historique in reversed(list_historique): if historique == "" or historique == " ": list_historique.remove(historique) for n, m in enumerate(list_historique): m = m.strip() list_historique[n] = m for remarque in reversed(list_remarque): if remarque == "": list_remarque.remove(remarque) for n, m in enumerate(list_remarque): m = m.strip() list_remarque[n] = m list_monument = zip(list_dates, list_historique, list_remarque) # print(num_monument) # print("---------") # print(name_monument) # print("---------") # print(rem_monument) # print("---------") # print(list_monument) # print("---------") # print(files) # print("---------") return num_monument, name_monument, rem_monument, list_monument, files
def format_datetime(v): t = xldate_as_tuple(v, 0) return datetime.date(t[0], t[1], t[2])
def get_raw_prices(forwards_directory, output_type, is_combined): simulation_directory = "combined" if is_combined else forwards_directory simulations_dir = f"{simulation_directory}/pcas" fps = [ f"pcas/{fl}" for fl in os.listdir(simulations_dir) if fl.endswith(".csv") ] ## To get a set of the contract names (of the cluster representatives), we take the list of sub-directory files from above and just chop off "_cluster.csv" from the end - which is 12 characters from the end - and the directory name from the beginning. set_contract_names = set([fp[5:-12] for fp in fps]) ## Now, we go through the original csv, line by line. If the contract currency and date line up with a set member, we take the price, convert the simulation file log returns to pure prices, and do the plot. Then we remove the element from the set. We are only looking for the *first* price for each contract+date in the csv; that is, the most recent price. This acts as our baseline from which we derive all other prices. fp = os.getcwd() + f"/{forwards_directory}/historical_forwards_new.xlsx" wb = xlrd.open_workbook(fp) sheet = wb.sheet_by_index(0) col_currency = 0 col_setdate = 4 col_contract = 5 col_price = 7 ## To be changed later num_simulations = 0 simulated_prices_dict = {} for i in range(1, sheet.nrows): if not set_contract_names: break tup_contract_date = xlrd.xldate_as_tuple( sheet.cell_value(i, col_contract), wb.datemode) dt = datetime(*tup_contract_date) month_year = dt.strftime("%B%Y") currency_to_match = sheet.cell_value(i, col_currency) if forwards_directory == "FX": currency_to_match = currency_to_match.replace('USD', '') currency_date = currency_to_match + "_" + month_year if currency_date in set_contract_names: dta = os.getcwd( ) + f"/{simulation_directory}/pcas/{currency_date}_cluster.csv" df = pd.read_csv(dta) original_price = sheet.cell_value(i, col_price) if currency_to_match == "FX_USDEUR": original_price = (1.0 / original_price) num_simulations = df.shape[1] all_simulations_prices = [] for j in range(1, df.shape[1]): one_simulation_prices = [] one_simulation_prices.append(original_price) for k in range(0, df.shape[0]): raw_return = df.iloc[k, j] curr_price = raw_return * original_price one_simulation_prices.append(curr_price) all_simulations_prices.append(one_simulation_prices) ## 1 --> this will output a graph of each cluster's set of simulations as they evolve over time if output_type == 1: graph_price_histories(all_simulations_prices, currency_date, forwards_directory) ## Stores all simulated prices in a dict with each key being one currency, and the value being a dict storing the simulated prices for each cluster representative of that currency #print(f"Contract: {currency_to_match}_{month_year}") if month_year not in simulated_prices_dict: new_sub_dict = {currency_to_match: all_simulations_prices} simulated_prices_dict[month_year] = new_sub_dict else: sub_dict = simulated_prices_dict[month_year] sub_dict[currency_to_match] = all_simulations_prices simulated_prices_dict[month_year] = sub_dict set_contract_names.remove(currency_date) ## 2 --> this will output a graph similar to that used by AES to represent price curves at 1, 5, and 20 holding days if output_type == 2: make_aes_graphs(simulated_prices_dict, num_simulations, forwards_directory) ## 3 --> return the simulated prices dict to then be used however we want return (simulated_prices_dict, num_simulations)
def import_row(self, cr, uid, row_list): if self.first_row: row_str_list = [self.simple_string(value) for value in row_list] for column in row_str_list: # print column if column in self.HEADER: _logger.info('Riga {0}: Trovato Header'.format( self.processed_lines)) return True self.first_row = False if not len(row_list) == len(self.HEADER): row_str_list = [self.simple_string(value) for value in row_list] if DEBUG: if len(row_list) > len(self.HEADER): pprint(zip(self.HEADER, row_str_list[:len(self.HEADER)])) else: pprint(zip(self.HEADER[:len(row_list)], row_str_list)) error = u"""Row {row}: Row_list is {row_len} long. We expect it to be {expected} long, with this columns: {keys} Instead of this we got this: {header} """.format(row=self.processed_lines, row_len=len(row_list), expected=len(self.HEADER), keys=self.HEADER, header=', '.join(row_str_list)) _logger.error(str(row_list)) _logger.error(error) self.error.append(error) return False elif DEBUG: # pprint(row_list) row_str_list = [self.simple_string(value) for value in row_list] pprint(zip(self.HEADER, row_str_list)) # Sometime value is only numeric and we don't want string to be treated as Float record = self.RecordSales._make( [self.simple_string(value) for value in row_list]) for field in self.REQUIRED: if not getattr(record, field): error = "Riga {0}: Manca il valore della {1}. La riga viene ignorata.".format( self.processed_lines, field) _logger.debug(error) self.error.append(error) return False if self.format == 'FormatTwo': partner_ids = self.partner_obj.search( cr, uid, [('name', 'like', record.partner_name.split(' ')[1])], context=self.context) if partner_ids: partner_id = partner_ids[0] vals_invoice = {'type': self.type} vals_invoice.update( self.account_invoice_obj.onchange_journal_id( cr, uid, [], self.journal_id.id, self.context).get('value')) vals_invoice.update( self.account_invoice_obj.onchange_partner_id( cr, uid, [], self.type, partner_id, date_invoice=record.date_invoice or '').get('value')) vals_invoice.update({ 'partner_id': partner_id, 'name': record.number_invoice.split('.')[0], 'internal_number': record.number_invoice.split('.')[0], 'date_invoice': datetime( *xlrd.xldate_as_tuple(float(record.date_invoice), 0)). strftime(DEFAULT_SERVER_DATE_FORMAT) or '', 'journal_id': self.journal_id.id, }) invoice_id = self.account_invoice_obj.create( cr, uid, vals_invoice, self.context) vals_account_invoice_line = {} vals_account_invoice_line.update({ 'name': 'Import Total Amount', 'invoice_id': invoice_id, 'quantity': 1.0, 'account_id': self.account_id.id, 'price_unit': record.total_amount or 0.0 }) self.account_invoice_line_obj.create( cr, uid, vals_account_invoice_line, self.context) _logger.info( u'Row {row}: Adding amount {amount} to Invoice {invoice}'. format(row=self.processed_lines, amount=record.total_amount, invoice=invoice_id)) self.uo_new += 1 else: _logger.warning(u'Row {row}: Not Find {partner}'.format( row=self.processed_lines, partner=record.partner_name)) invoice_id = False return invoice_id elif self.format == 'FormatOne': name = record.location_name.split('.')[0] if self.cache.get(name): invoice_id = self.cache[name] _logger.info( u'Sales {0} already processed in cache'.format(name)) elif self.account_invoice_obj.search( cr, uid, [('name', '=', name), ('state', '=', 'draft'), ('partner_id', '=', self.partner_id.id)]): invoice_id = self.account_invoice_obj.search( cr, uid, [('name', '=', name), ('state', '=', 'draft'), ('partner_id', '=', self.partner_id.id)])[0] self.cache[name] = invoice_id _logger.warning(u'Invoice {0} already exist'.format(name)) else: # i need to create invoice # so need to create one vals_invoice = {'type': self.type} vals_invoice.update( self.account_invoice_obj.onchange_journal_id( cr, uid, [], self.journal_id.id, self.context).get('value')) vals_invoice.update( self.account_invoice_obj.onchange_partner_id( cr, uid, [], self.type, self.partner_id.id, date_invoice=self.date_invoice).get('value')) vals_invoice.update({ 'partner_id': self.partner_id.id, 'name': self.origin + ': ' + name, 'date_invoice': self.date_invoice, 'journal_id': self.journal_id.id, }) if self.location_id: vals_invoice.update({ 'location_id': self.location_id.id, 'move_products': True, }) if vals_invoice.get('sale_agent_ids'): del vals_invoice['sale_agent_ids'] invoice_id = self.account_invoice_obj.create( cr, uid, vals_invoice, self.context) self.cache[name] = invoice_id _logger.info(u'Create Invoice {0} on Draft '.format(name)) self.fiscal_position = vals_invoice['fiscal_position'] product_id = False if hasattr(record, 'item') and record.item: product = record.item if self.cache_product.get(product): product_id = self.cache_product[product] _logger.warning( u'Product {0} already processed in cache'.format( product)) else: product_ids = self.product_obj.search( cr, uid, [('default_code', '=', product)], context=self.context) if not product_ids: product_ids = self.product_obj.search( cr, uid, [('name', '=', product)], context=self.context) if not product_ids: product_ids = self.product_obj.search( cr, uid, [('ean13', '=', product)], context=self.context) if product_ids: product_id = product_ids[0] self.cache_product[product] = product_id else: error = u'Row {row}: Product "{product}" not Found'.format( row=self.processed_lines, product=product) _logger.error(error) self.error.append(error) if product_id: if hasattr(record, 'qty') and record.qty: product_qty = float(record.qty) if hasattr(record, 'cost') and record.cost: cost = float(record.cost) # riga vals_account_invoice_line = self.account_invoice_line_obj.product_id_change( cr, uid, [], product_id, 1, qty=product_qty, name='', type=self.type, partner_id=self.partner_id.id, fposition_id=self.fiscal_position, price_unit=False, address_invoice_id=False, currency_id=False, context=None, company_id=None).get('value') vals_account_invoice_line.update({ 'invoice_id': invoice_id, 'product_id': product_id, 'quantity': product_qty, }) if self.update_price: price_sell = cost / product_qty vals_account_invoice_line.update({ 'price_unit': price_sell, 'discount': 0, }) # list_price_sell = vals_account_invoice_line['price_unit'] # price_sell = cost / product_qty # # vals_account_invoice_line.update({ # 'discount': (list_price_sell - price_sell) / list_price_sell * 100 # }) _logger.info( u'Row {row}: Adding product {product} to Invoice {invoice}' .format(row=self.processed_lines, product=record.item, invoice=invoice_id)) self.account_invoice_line_obj.create( cr, uid, vals_account_invoice_line, self.context) self.uo_new += 1 else: _logger.warning(u'Row {row}: Not Found {product}'.format( row=self.processed_lines, product=record.item)) invoice_id = False return invoice_id
RH11 = worksheet.cell(r, 14).value RH22 = worksheet.cell(r, 15).value VP11 = worksheet.cell(r, 16).value VP22 = worksheet.cell(r, 17).value CLOUDM = worksheet.cell(r, 18).value CLOUDE = worksheet.cell(r, 19).value SOIL1 = worksheet.cell(r, 20).value SOIL2 = worksheet.cell(r, 21).value SOIL3 = worksheet.cell(r, 22).value SOIL4 = worksheet.cell(r, 23).value SOIL5 = worksheet.cell(r, 24).value SOIL6 = worksheet.cell(r, 25).value MinTtest = worksheet.cell(r, 26).value MaxTtest1 = worksheet.cell(r, 27).value MaxTtest2 = worksheet.cell(r, 28).value x = xlrd.xldate_as_tuple(DATE, 0) x = list(x) y = x[0] y = str(y) z = x[1] z = str(z) a = x[2] a = str(a) y = int(y) z = int(z) a = int(a) x = datetime.datetime(y, z, a) values = (x, ET, EP, BSS, RF, WD, WD1, WS, DT1, WT1, DT2, WT2, MAXT, MINT, RH11, RH22, VP11, VP22, CLOUDM, CLOUDE, SOIL1, SOIL2, SOIL3, SOIL4, SOIL5, SOIL6, MinTtest, MaxTtest1, MaxTtest2)
def get_ct_date(row, name, datemode): val = get_value(row, name) if isinstance(val, float): return to_ct(Datetime(*xldate_as_tuple(val, datemode)))
for worksheet in workbook.sheets(): try: header = worksheet.row_values(0) except IndexError: pass for row in range(1, worksheet.nrows): row_of_output = [] for column in range(len(header)): if column < 3: cell_value = str(worksheet.cell_value(row,column)).strip() row_of_output.append(cell_value) elif column == 3: cell_value = str(worksheet.cell_value(row,column)).split('.')[0].strip() row_of_output.append(cell_value) else: cell_value = xldate_as_tuple(worksheet.cell(row,column).value,workbook.datemode) cell_value = str(date(*cell_value[0:3])).strip() row_of_output.append(cell_value) row_of_output.append(os.path.basename(input_file)) row_of_output.append(worksheet.name) if str(worksheet.cell(row,0).value).split('.')[0].strip() in item_numbers_to_find: filewriter.writerow(row_of_output) count_of_item_numbers += 1 line_counter += 1 print('Number of files: {}'.format(file_counter)) print('Number of lines: {}'.format(line_counter)) print('Number of item numbers: {}'.format(count_of_item_numbers)) # 인자값 # item_numbers_to_find.csv historical_files output_files/1output.csv
def checkDuplicate(self): convert_date_of_birth = self.current_data.get('DateOfBirth') self.date_of_birth = datetime.datetime(*xlrd.xldate_as_tuple( convert_date_of_birth, excel_read_obj.excel_file.datemode)) self.date_of_birth = self.date_of_birth.strftime("%Y-%m-%d") self.data = { "FirstName": self.current_data.get('FirstName'), "MiddleName": self.current_data.get('MiddleName'), "LastName": self.current_data.get('LastName'), "Email1": self.current_data.get('EmailAddress'), "Mobile1": int(self.current_data.get('MobileNumber')) if self.current_data.get('MobileNumber') else None, "PhoneOffice": int(self.current_data.get('PhoneNumber')) if self.current_data.get('PhoneNumber') else None, "MaritalStatus": int(self.current_data.get('MaritalStatus')) if self.current_data.get('MaritalStatus') else None, "Gender": int(self.current_data.get('Gender')), "DateOfBirth": self.date_of_birth, "PanNo": self.current_data.get('Pancard'), "PassportNo": self.current_data.get('Passport'), "AadhaarNo": int(self.current_data.get('Aadhar')) if self.current_data.get('Aadhar') else None, "CollegeId": int(self.current_data.get('College')) if self.current_data.get('College') else None, "DegreeId": int(self.current_data.get('Degree')) if self.current_data.get('Degree') else None, "USN": self.current_data.get('USN'), "CurrentLocationId": int(self.current_data.get('Location')) if self.current_data.get('Location') else None, "TotalExperience": int(self.current_data.get('TotalExperienceInMonths')) if self.current_data.get('TotalExperienceInMonths') else None, "FacebookLink": self.current_data.get('Facebook'), "TwitterLink": self.current_data.get('Twitter'), "LinkedInLink": self.current_data.get('LinkedIn'), "Integer1": int(self.current_data.get('Integer1')) if self.current_data.get('Integer1') else None, "Integer2": int(self.current_data.get('Integer2')) if self.current_data.get('Integer2') else None, "Integer3": int(self.current_data.get('Integer3')) if self.current_data.get('Integer3') else None, "Integer4": int(self.current_data.get('Integer4')) if self.current_data.get('Integer4') else None, "Integer5": int(self.current_data.get('Integer5')) if self.current_data.get('Integer5') else None, "Integer6": int(self.current_data.get('Integer6')) if self.current_data.get('Integer6') else None, "Integer7": int(self.current_data.get('Integer7')) if self.current_data.get('Integer7') else None, "Integer8": int(self.current_data.get('Integer8')) if self.current_data.get('Integer8') else None, "Integer9": int(self.current_data.get('Integer9')) if self.current_data.get('Integer9') else None, "Integer10": int(self.current_data.get('Integer10')) if self.current_data.get('Integer10') else None, "Integer11": int(self.current_data.get('Integer11')) if self.current_data.get('Integer11') else None, "Integer12": int(self.current_data.get('Integer12')) if self.current_data.get('Integer12') else None, "Integer13": int(self.current_data.get('Integer13')) if self.current_data.get('Integer13') else None, "Integer14": int(self.current_data.get('Integer14')) if self.current_data.get('Integer14') else None, "Integer15": int(self.current_data.get('Integer15')) if self.current_data.get('Integer15') else None, "Text1": self.current_data.get('Text1'), "Text2": self.current_data.get('Text2'), "Text3": self.current_data.get('Text3'), "Text4": self.current_data.get('Text4'), "Text5": self.current_data.get('Text5'), "Text6": self.current_data.get('Text6'), "Text7": self.current_data.get('Text7'), "Text8": self.current_data.get('Text8'), "Text9": self.current_data.get('Text9'), "Text10": self.current_data.get('Text10'), "Text11": self.current_data.get('Text11'), "Text12": self.current_data.get('Text12'), "Text13": self.current_data.get('Text13'), "Text14": self.current_data.get('Text14'), "Text15": self.current_data.get('Text15') } # print self.data self.lambda_function('candidate_duplicate_check') self.headers['APP-NAME'] = 'crpo' r = requests.post(self.webapi, headers=self.headers, data=json.dumps(self.data, default=str), verify=False) print(r.headers) time.sleep(1) resp_dict = json.loads(r.content) self.is_duplicate = resp_dict["IsDuplicate"] # print self.is_duplicate self.message = resp_dict['Message'] if self.is_duplicate: self.is_duplicate1 = "Duplicate" # print self.is_duplicate1 else: self.is_duplicate1 = "NotDuplicate" # print self.is_duplicate1 if self.is_duplicate1 == self.current_data.get('ExpectedOutput'): self.style6 = self.style14 else: self.style6 = self.style13 self.excelwrite(self.message)
if s: # Calculate cell range rows = cell_range(rows, s.nrows) cols = cell_range(cols, s.ncols) # Column headers if fields: headers = fields elif not header_row: headers = dict((i, "%s" % i) for i in range(cols[1]- cols[0])) else: # Use header row in the work sheet headers = {} # Lambda to decode XLS dates into an ISO datetime-string decode_date = lambda v: datetime.datetime(*xlrd.xldate_as_tuple(v, wb.datemode)) def decode(t, v): """ Helper method to decode the cell value by type @param t: the cell type @param v: the cell value @return: text representation of the cell value """ text = "" if v: if t is None: text = s3_unicode(v).strip() elif t == xlrd.XL_CELL_TEXT: text = v.strip()
def read_tecan_xlsx(filename, label=u'OD', sheets=None, max_time=None, plate=None, PRINT=False): """Reads growth measurements from a Tecan Infinity Excel output file. Parameters ---------- filename : str path to the file. label : str / sequence of str a string or sequence of strings containing measurment names used as titles of the data tables in the file. sheets : list, optional list of sheet numbers, if known. Otherwise the function will try to all the sheets. max_time : float, optional maximal time in hours, defaults to infinity plate : pandas.DataFrame, optional data frame representing a plate, usually generated by reading a CSV file generated by `Plato <http://plato.yoavram.com/>`_. Returns ------- pandas.DataFrame Data frame containing the columns: - ``Time`` (:py:class:`float`, in hours) - ``Temp. [°C]`` (:py:class:`float`) - ``Cycle Nr.`` (:py:class:`int`) - ``Well`` (:py:class:`str`): the well name, usually a letter for the row and a number of the column. - ``Row`` (:py:class:`str`): the letter corresponding to the well row. - ``Col`` (:py:class:`str`): the number corresponding to the well column. - ``Strain`` (:py:class:`str`): if a `plate` was given, this is the strain name corresponding to the well from the plate. - ``Color`` (:py:class:`str`, hex format): if a `plate` was given, this is the strain color corresponding to the well from the plate. There will also be a separate column for each label, and if there is more than one label, a separate `Time` and `Temp. [°C]` column for each label. Raises ------ ValueError if not data was parsed from the file. Examples -------- >>> plate = pd.read_csv("plate_templates/G-RG-R.csv") >>> df = curveball.ioutils.read_tecan_xlsx("data/Tecan_210115.xlsx", label=('OD','Green','Red'), max_time=12, plate=plate) >>> df.shape (8544, 9) """ import xlrd wb = xlrd.open_workbook(filename) dateandtime = datetime.datetime.now() # default if isinstance(label, string_types): label = [label] if sheets is None: sheets = range(wb.nsheets) if PRINT: print("Reading {0} worksheets from workbook {1}".format( len(sheets), filename)) label_dataframes = [] for lbl in label: sheet_dataframes = [] ## FOR sheet for sh_i in sheets: sh = wb.sheet_by_index(sh_i) if sh.nrows == 0: continue # to next sheet for i in range(sh.nrows): ## FOR row row = sh.row_values(i) if row[0].startswith(u'Date'): if isinstance(row[1], string_types): date = ''.join(row[1:]) next_row = sh.row_values(i + 1) if next_row[0].startswith(u'Time'): time = ''.join(next_row[1:]) else: warn( u"Warning: time row missing (sheet '{0}', row{1}), found row starting with {2}" .format(sh.name, i, row[0])) dateandtime = dateutil.parser.parse("%s %s" % (date, time)) elif isinstance(row[1], float): date_tuple = xlrd.xldate_as_tuple(row[1], wb.datemode) next_row = sh.row_values(i + 1) if next_row[0].startswith(u'Time'): time = tuple(map(int, next_row[1].split(':')))[:3] date_tuple = date_tuple[:3] + time else: warn( u"Warning: time row missing (sheet '{0}', row{1}), found row starting with {2}" .format(sh.name, i, row[0])) dateandtime = datetime.datetime(*date_tuple) else: warn( u"Warning: date row (sheet '{2}', row {3}) could not be parsed: {0} {1}" .format(row[1], type(row[1]), sh.name, i)) if row[0] == lbl: break ## FOR row ENDS data = {} for j in range(i + 1, sh.nrows): ## FOR row row = sh.row(j) if not row[0].value: break data[row[0].value] = [x.value for x in row[1:] if x.ctype == 2] ## FOR row ENDS if not data: raise ValueError( "No data found in sheet {1} of workbook {0}".format( filename, sh_i)) min_length = min(map(len, data.values())) for k, v in data.items(): data[k] = v[:min_length] df = pd.DataFrame(data) df = pd.melt(df, id_vars=(u'Time [s]', u'Temp. [°C]', u'Cycle Nr.'), var_name=u'Well', value_name=lbl) df.rename(columns={u'Time [s]': u'Time'}, inplace=True) df.Time = [dateandtime + datetime.timedelta(0, t) for t in df.Time] df[u'Row'] = [x[0] for x in df.Well] df[u'Col'] = [int(x[1:]) for x in df.Well] sheet_dataframes.append(df) ## FOR sheet ENDS n_sheet_dataframes = len(sheet_dataframes) if n_sheet_dataframes == 0: df = pd.DataFrame() elif n_sheet_dataframes == 1: df = sheet_dataframes[0] else: df = pd.concat(sheet_dataframes) min_time = df.Time.min() if PRINT: print("Starting time", min_time) df.Time = [(t - min_time).total_seconds() / 3600.0 for t in df.Time] if max_time is not None: df = df[df.Time <= max_time] df.sort_values([u'Row', u'Col', u'Time'], inplace=True) label_dataframes.append((lbl, df)) n_label_dataframes = len(label_dataframes) if n_label_dataframes == 0: # no dataframes return pd.DataFrame() if n_label_dataframes == 1: # just one dataframe df = label_dataframes[0][1] else: # multiple dataframes, merge together # FIXME last label isn't used as a suffix, not sure why lbl, df = label_dataframes[0] lbl = '_' + lbl for lbli, dfi in label_dataframes[1:]: lbli = '_' + lbli df = pd.merge(df, dfi, on=(u'Cycle Nr.', u'Well', u'Row', u'Col'), suffixes=(lbl, lbli)) if plate is None: df[u'Strain'] = u'0' df[u'Color'] = u'#000000' else: df = pd.merge(df, plate, on=(u'Row', u'Col')) if PRINT: print("Read {0} records from workbook".format(df.shape[0])) _fix_dtypes(df) return df
def import_csv(self): """Load Inventory data from the CSV file.""" if self.import_option == 'csv': keys = ['invoice', 'customer', 'currency', 'product','account', 'quantity', 'uom', 'description', 'price','salesperson','tax','date','journal','amount','disc'] csv_data = base64.b64decode(self.file) data_file = io.StringIO(csv_data.decode("utf-8")) data_file.seek(0) file_reader = [] csv_reader = csv.reader(data_file, delimiter=',') try: file_reader.extend(csv_reader) except Exception: raise exceptions.Warning(_("Invalid file!")) values = {} invoice_ids=[] payment = {} for i in range(len(file_reader)): field = list(map(str, file_reader[i])) values = dict(zip(keys, field)) if values: if i == 0: continue else: values.update({'type':self.type,'option':self.import_option,'seq_opt':self.sequence_opt}) res = self.make_invoice(values) invoice_ids.append(res) if self.stage == 'payment': if values.get('journal') and values.get('amount'): if res in payment: if payment[res][0] != values.get('journal'): raise Warning(_('Please Use same Journal for Invoice %s' %values.get('invoice'))) else: payment.update({res:[values.get('journal'),float(values.get('amount'))+float(payment[res][1]) ]}) else: payment.update({res:[values.get('journal'),values.get('amount')]}) else: raise Warning(_('Please Specify Payment Journal and Amount for Invoice %s' %values.get('invoice'))) if self.stage == 'confirm': for res in invoice_ids: if res.state in ['draft']: res.action_post() if self.stage == 'payment': self.create_payment(payment) else: fp = tempfile.NamedTemporaryFile(delete= False,suffix=".xlsx") fp.write(binascii.a2b_base64(self.file)) fp.seek(0) values = {} invoice_ids=[] payment = {} workbook = xlrd.open_workbook(fp.name) sheet = workbook.sheet_by_index(0) for row_no in range(sheet.nrows): val = {} if row_no <= 0: fields = map(lambda row:row.value.encode('utf-8'), sheet.row(row_no)) else: line = list(map(lambda row:isinstance(row.value, bytes) and row.value.encode('utf-8') or str(row.value), sheet.row(row_no))) if self.account_opt == 'default': if line[10]: a1 = int(float(line[10])) a1_as_datetime = datetime(*xlrd.xldate_as_tuple(a1, workbook.datemode)) date_string = a1_as_datetime.date().strftime('%Y-%m-%d') values.update( {'invoice':line[0], 'customer': line[1], 'currency': line[2], 'product': line[3].split('.')[0], 'quantity': line[5], 'uom': line[6], 'description': line[7], 'price': line[8], 'salesperson': line[9], 'tax': line[10], 'date': date_string, 'seq_opt':self.sequence_opt, 'disc':line[14] }) else: if line[11]: a1 = int(float(line[11])) a1_as_datetime = datetime(*xlrd.xldate_as_tuple(a1, workbook.datemode)) date_string = a1_as_datetime.date().strftime('%Y-%m-%d') values.update( {'invoice':line[0], 'customer': line[1], 'currency': line[2], 'product': line[3].split('.')[0], 'account': line[4], 'quantity': line[5], 'uom': line[6], 'description': line[7], 'price': line[8], 'salesperson': line[9], 'tax': line[10], 'date': date_string, 'seq_opt':self.sequence_opt, 'disc':line[14] }) res = self.make_invoice(values) invoice_ids.append(res) if self.stage == 'payment': if self.account_opt == 'default': if line[11] and line[12]: if res in payment: if payment[res][0] != line[11]: raise Warning(_('Please Use same Journal for Invoice %s' %line[0])) else: payment.update({res:[line[11],float(line[12])+float(payment[res][1]) ]}) else: payment.update({res:[line[11],line[12] ]}) else: raise Warning(_('Please Specify Payment Journal and Amount for Invoice %s' %line[0])) else: if line[12] and line[13]: if res in payment: if payment[res][0] != line[12]: raise Warning(_('Please Use same Journal for Invoice %s' %line[0])) else: payment.update({res:[line[12],float(line[13])+float(payment[res][1]) ]}) else: payment.update({res:[line[12],line[13] ]}) else: raise Warning(_('Please Specify Payment Journal and Amount for Invoice %s' %line[0])) if self.stage == 'confirm': for res in invoice_ids: if res.state in ['draft']: res.action_post() if self.stage == 'payment': self.create_payment(payment) return res
output_file = sys.argv[2] output_workbook = Workbook() output_worksheet = output_workbook.add_sheet('selected_columns_all_worksheets') my_columns = ['Customer Name', 'Sale Amount'] first_worksheet = True with open_workbook(input_file) as workbook: data = [my_columns] index_of_cols_to_keep = [] for worksheet in workbook.sheets(): if first_worksheet: header = worksheet.row_values(0) for column_index in range(len(header)): if header[column_index] in my_columns: index_of_cols_to_keep.append(column_index) first_worksheet = False for row_index in range(1, worksheet.nrows): row_list = [] for column_index in index_of_cols_to_keep: cell_value = worksheet.cell_value(row_index, column_index) cell_type = worksheet.cell_type(row_index, column_index) if cell_type == 3: date_cell = xldate_as_tuple(cell_value, workbook.datemode) date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y') row_list.append(date_cell) else: row_list.append(cell_value) data.append(row_list) for list_index, output_list in enumerate(data): for element_index, element in enumerate(output_list): output_worksheet.write(list_index, element_index, element) output_workbook.save(output_file)
query = """INSERT INTO T_VIS_ACT_PRO ( Production_Date, HM, PI, CS, CC, SS_Prod, SS_Desp ) VALUES ( %s, %s, %s, %s, %s, %s, %s)""" col = 9 prod_d = sheet.cell(0, col).value Production_Date = datetime.datetime( *xlrd.xldate_as_tuple(prod_d, excel_list.datemode)).strftime("%Y-%m-%d") HM = sheet.cell(114, col).value PI = sheet.cell(115, col).value CS = sheet.cell(116, col).value CC = sheet.cell(117, col).value SS_Prod = sheet.cell(118, col).value SS_Desp = sheet.cell(119, col).value values = (Production_Date, HM, PI, CS, CC, SS_Prod, SS_Desp) #print values cursor.execute(query, values) cursor.close() database.commit()
def import_sale_pricelist(self): if self.import_option == 'csv': if (self.file): if self.compute_type == 'both': keys = [ 'name', 'currency', 'apply_on', 'check_apply_on', 'min_qty', 'start_dt', 'end_dt', 'compute_price', 'amount' ] else: keys = [ 'name', 'currency', 'apply_on', 'check_apply_on', 'min_qty', 'start_dt', 'end_dt', 'based_on', 'discount', 'surcharge', 'rounding', 'min_margin', 'max_margin', 'other_pricelist' ] csv_data = base64.b64decode(self.file) data_file = io.StringIO(csv_data.decode("utf-8")) data_file.seek(0) file_reader = [] csv_reader = csv.reader(data_file, delimiter=',') try: file_reader.extend(csv_reader) except Exception: raise exceptions.Warning(_("Invalid file!")) values = {} for i in range(len(file_reader)): field = list(map(str, file_reader[i])) values = dict(zip(keys, field)) if values: if i == 0: continue else: values.update({'option': self.import_option}) res = self.make_pricelist(values) else: raise Warning(_('Please Seelect a file.')) else: if (self.file): fp = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx") fp.write(binascii.a2b_base64(self.file)) fp.seek(0) values = {} workbook = xlrd.open_workbook(fp.name) sheet = workbook.sheet_by_index(0) for row_no in range(sheet.nrows): val = {} if row_no <= 0: fields = map(lambda row: row.value.encode('utf-8'), sheet.row(row_no)) else: line = list( map( lambda row: isinstance(row.value, bytes) and row.value.encode('utf-8') or str(row.value), sheet.row(row_no))) start_date_string = False end_dt_string = False amount = line[8] or 0 if line[5] and line[6]: start_dt = int(float(line[5])) end_dt = int(float(line[6])) start_dt_datetime = datetime(*xlrd.xldate_as_tuple( start_dt, workbook.datemode)) end_dt_datetime = datetime(*xlrd.xldate_as_tuple( end_dt, workbook.datemode)) start_date_string = start_dt_datetime.date( ).strftime('%Y-%m-%d') end_dt_string = end_dt_datetime.date().strftime( '%Y-%m-%d') min_qty = 1 if line[4]: min_qty = int(float(line[4])) if self.compute_type == 'both': values.update({ 'name': line[0], 'currency': line[1], 'apply_on': line[2].strip(), 'check_apply_on': line[3], 'min_qty': min_qty, 'start_dt': start_date_string, 'end_dt': end_dt_string, 'compute_price': line[7], 'amount': float(amount), }) res = self.make_pricelist(values) else: if not len(line) > 9: raise Warning( _("Please select proper file when you select 'Formula' option." )) return discount = line[8] or 0 surcharge = line[9] or 0 rounding = line[10] or 0 min_margin = line[11] or 0 max_margin = line[12] or 0 values.update({ 'name': line[0], 'currency': line[1], 'apply_on': line[2].strip(), 'check_apply_on': line[3], 'min_qty': min_qty, 'start_dt': start_date_string, 'end_dt': end_dt_string, 'based_on': line[7], 'discount': float(discount), 'surcharge': float(surcharge), 'rounding': float(rounding), 'min_margin': float(min_margin), 'max_margin': float(max_margin), }) if line[7].lower( ) == 'other pricelist' and line[13]: values.update({'other_pricelist': line[13]}) res = self.make_pricelist(values) else: raise Warning(_('Please Seelect a file.'))
def _parse_row(self, sheet, row_index, date_as_tuple): """ Sanitize incoming excel data """ # Data Type Codes: # EMPTY 0 # TEXT 1 a Unicode string # NUMBER 2 float # DATE 3 float # BOOLEAN 4 int; 1 means TRUE, 0 means FALSE # ERROR 5 values = [] for type, value in itertools.izip(sheet.row_types(row_index), sheet.row_values(row_index)): if type == 2: if value == int(value): value = int(value) elif type == 3: if isinstance(value, float) and value < 1.0: t = value * (24.0 * 60.0 * 60.0) if int(t + 0.000001) == int(t + 1.0): secs = int(t + 0.000001) fract = 0.0 else: fract, secs = math.modf(t) if fract < 0.000000001: fract = 0.0 secs = int(secs) if fract: value = '%02d:%02d:%02d.%s' % (secs // (60 * 60), (secs // 60) % 60, secs % 60, ('%.20f' % fract)[2:]) else: value = '%02d:%02d:%02d' % (secs // (60 * 60), (secs // 60) % 60, secs % 60) else: try: datetuple = xlrd.xldate_as_tuple( value, self.book.datemode) validDate = True except: value = 'UnreadableDate' validDate = False if validDate: if date_as_tuple: value = datetuple else: # time only - no date component if datetuple[0] == 0 and datetuple[ 1] == 0 and datetuple[2] == 0: value = "%02d:%02d:%02d" % datetuple[3:] # date only, no time elif datetuple[3] == 0 and datetuple[ 4] == 0 and datetuple[5] == 0: value = "%04d/%02d/%02d" % datetuple[:3] else: # full date value = "%04d/%02d/%02d %02d:%02d:%02d" % datetuple elif type == 5: value = xlrd.error_text_from_code[value] values.append(value) return values
def excel_float_to_date(f, datemode=0): if f > 1: return datetime.datetime.strptime(str(int(f)), '%Y%m%d').date() else: t = xlrd.xldate_as_tuple(f, datemode) return datetime.time(t[3], t[4], t[5])
def xl_to_date(cell_value, wb): """Преобразуем дату из excel в дату python""" pydate = datetime.datetime(*xlrd.xldate_as_tuple(cell_value, wb.datemode)) return pydate
def load_config_excel(ConfigFile): """ Function that loads the DispaSET excel config file and returns a dictionary with the values :param ConfigFile: String with (relative) path to the DispaSET excel configuration file """ import xlrd wb = xlrd.open_workbook( filename=ConfigFile) # Option for csv to be added later sheet = wb.sheet_by_name('main') config = {} config['SimulationDirectory'] = sheet.cell_value(17, 2) config['WriteExcel'] = sheet.cell_value(18, 2) config['WriteGDX'] = sheet.cell_value(19, 2) config['WritePickle'] = sheet.cell_value(20, 2) config['GAMS_folder'] = sheet.cell_value(21, 2) config['cplex_path'] = sheet.cell_value(22, 2) config['StartDate'] = xlrd.xldate_as_tuple(sheet.cell_value(30, 2), wb.datemode) config['StopDate'] = xlrd.xldate_as_tuple(sheet.cell_value(31, 2), wb.datemode) config['HorizonLength'] = int(sheet.cell_value(32, 2)) config['LookAhead'] = int(sheet.cell_value(33, 2)) config['SimulationType'] = sheet.cell_value(46, 2) config['ReserveCalculation'] = sheet.cell_value(47, 2) config['AllowCurtailment'] = sheet.cell_value(48, 2) params = [ 'Demand', 'Outages', 'PowerPlantData', 'RenewablesAF', 'LoadShedding', 'NTC', 'Interconnections', 'ReservoirScaledInflows', 'PriceOfNuclear', 'PriceOfBlackCoal', 'PriceOfGas', 'PriceOfFuelOil', 'PriceOfBiomass', 'PriceOfCO2', 'ReservoirLevels', 'PriceOfLignite', 'PriceOfPeat', 'HeatDemand', 'CostHeatSlack', 'CostLoadShedding' ] for i, param in enumerate(params): config[param] = sheet.cell_value(61 + i, 2) config['default'] = {} config['default']['PriceOfNuclear'] = sheet.cell_value(69, 5) config['default']['PriceOfBlackCoal'] = sheet.cell_value(70, 5) config['default']['PriceOfGas'] = sheet.cell_value(71, 5) config['default']['PriceOfFuelOil'] = sheet.cell_value(72, 5) config['default']['PriceOfBiomass'] = sheet.cell_value(73, 5) config['default']['PriceOfCO2'] = sheet.cell_value(74, 5) config['default']['PriceOfLignite'] = sheet.cell_value(76, 5) config['default']['PriceOfPeat'] = sheet.cell_value(77, 5) config['default']['LoadShedding'] = sheet.cell_value(65, 5) config['default']['CostHeatSlack'] = sheet.cell_value(79, 5) config['default']['CostLoadShedding'] = sheet.cell_value(80, 5) # read the list of countries to consider: def read_truefalse(sheet, rowstart, colstart, rowstop, colstop): """ Function that reads a two column format with a list of strings in the first columns and a list of true false in the second column The list of strings associated with a True value is returned """ out = [] for i in range(rowstart, rowstop): if sheet.cell_value(i, colstart + 1) == 1: out.append(sheet.cell_value(i, colstart)) return out config['countries'] = read_truefalse(sheet, 86, 1, 101, 3) config['countries'] = config['countries'] + read_truefalse( sheet, 86, 4, 102, 6) config['modifiers'] = {} config['modifiers']['Demand'] = sheet.cell_value(111, 2) config['modifiers']['Wind'] = sheet.cell_value(112, 2) config['modifiers']['Solar'] = sheet.cell_value(113, 2) config['modifiers']['Storage'] = sheet.cell_value(114, 2) # Read the technologies participating to reserve markets: config['ReserveParticipation'] = read_truefalse(sheet, 131, 1, 145, 3) logging.info("Using config file " + ConfigFile + " to build the simulation environment") return config
def _parse_excel(self, sheetname, header=0, skiprows=None, skip_footer=0, index_col=None, has_index_names=None, parse_cols=None, parse_dates=False, date_parser=None, na_values=None, thousands=None, chunksize=None, convert_float=True, **kwds): from xlrd import (xldate_as_tuple, XL_CELL_DATE, XL_CELL_ERROR, XL_CELL_BOOLEAN, XL_CELL_NUMBER) datemode = self.book.datemode if isinstance(sheetname, compat.string_types): sheet = self.book.sheet_by_name(sheetname) else: # assume an integer if not a string sheet = self.book.sheet_by_index(sheetname) data = [] should_parse = {} for i in range(sheet.nrows): row = [] for j, (value, typ) in enumerate( zip(sheet.row_values(i), sheet.row_types(i))): if parse_cols is not None and j not in should_parse: should_parse[j] = self._should_parse(j, parse_cols) if parse_cols is None or should_parse[j]: if typ == XL_CELL_DATE: dt = xldate_as_tuple(value, datemode) # how to produce this first case? if dt[0] < datetime.MINYEAR: # pragma: no cover value = datetime.time(*dt[3:]) else: value = datetime.datetime(*dt) elif typ == XL_CELL_ERROR: value = np.nan elif typ == XL_CELL_BOOLEAN: value = bool(value) elif convert_float and typ == XL_CELL_NUMBER: # GH5394 - Excel 'numbers' are always floats # it's a minimal perf hit and less suprising val = int(value) if val == value: value = val row.append(value) data.append(row) if header is not None: data[header] = _trim_excel_header(data[header]) parser = TextParser(data, header=header, index_col=index_col, has_index_names=has_index_names, na_values=na_values, thousands=thousands, parse_dates=parse_dates, date_parser=date_parser, skiprows=skiprows, skip_footer=skip_footer, chunksize=chunksize, **kwds) return parser.read()
) if configdict['zip_folder'] <> '' else os.getcwd() MEOLUT_in = str(configdict['MEOLUT']) MEOLUT_list = [x.strip() for x in MEOLUT_in.split(',')] filetypesearch = str(configdict['filetypesearch']) csvoutfilestr = str(configdict['csvoutfile']) sql_out_folder = str(configdict['SQL_out_folder'] ) if configdict['SQL_out_folder'] <> '' else os.getcwd() sql_out_file = str( configdict['SQL_out_file'] ) if configdict['SQL_out_file'] <> '' else 'MEOLUT_packets.db' searchtag1 = str(configdict['searchtag1']) searchstr1 = str(configdict['searchstr1']) searchtag2 = str(configdict['searchtag2']) searchstr2 = str(configdict['searchstr2']) writeto = str(configdict['writeto']) date1 = datetime.datetime(*xlrd.xldate_as_tuple(configdict['start_date'], 0)) if configdict['end_date']: date2 = datetime.datetime(*xlrd.xldate_as_tuple(configdict['end_date'], 0)) datelist = [ date1 + datetime.timedelta(days=x) for x in range(0, (date2 - date1).days + 1) ] else: datelist = [date1] os.chdir(zip_folder) print '\nReading zips from:' print ' ' + os.getcwd() burstlist = list()
def main(): thresholdDate = datetime.strptime('Oct 15 2017', '%b %d %Y').date() memberIndex = 1 productNameIndex = 0 startCol = 6 args = argsFetch() logger = loggerFetch(args.get('log_level')) logger.info('args: %s', str(args)) print("test") if args['limit']: limit = int(args['limit']) else: limit = 1 #Lets Process Grocery Order Form if args['sheetID']: myOrderForms = ConsolidatedOrderForm.objects.filter( isProcessed=0, id=args['sheetID'])[:limit] else: myOrderForms = ConsolidatedOrderForm.objects.filter( isProcessed=0)[:limit] for eachOrderForm in myOrderForms: isBeforeSoftware = False productCodeIndex = 2 priceIndex = 4 orderDate = eachOrderForm.orderDate if orderDate < thresholdDate: isBeforeSoftware = True productCodeIndex = 1 priceIndex = 2 creditIndex = eachOrderForm.creditIndex - 1 creditSheetIndex = eachOrderForm.creditSheetIndex sheetIndex = eachOrderForm.sheetNo balanceSheetIndex = eachOrderForm.balanceSheetIndex maxOrderRow = eachOrderForm.maxRow myFile = eachOrderForm.orderFile.read() book = xlrd.open_workbook(file_contents=myFile) #Balance Sheet Index worksheet = book.sheet_by_index(balanceSheetIndex) num_rows = worksheet.nrows - 1 curr_row = 0 vegetableCostIndex = 11 creditIndex = 10 adjustmentIndex = 11 adjustmentReasonIndex = 14 nameIndex = 4 emailIndex = 5 boxIDIndex = 1 password = "******" while curr_row < num_rows: curr_row = curr_row + 1 memberCode = str(worksheet.cell_value(curr_row, 0)).replace( "-", "").lstrip().rstrip() vegetableCost = int( worksheet.cell_value(curr_row, vegetableCostIndex)) if isBeforeSoftware == True: memberCode = str(worksheet.cell_value(curr_row, 0)).replace( "-", "").lstrip().rstrip() myMember = Member.objects.filter( user__username=memberCode).first() else: memberCode = int(worksheet.cell_value(curr_row, 0)) if memberCode != 0: boxID = str(worksheet.cell_value( curr_row, boxIDIndex)).lstrip().rstrip() email = str(worksheet.cell_value( curr_row, emailIndex)).lstrip().rstrip() name = str(worksheet.cell_value( curr_row, nameIndex)).lstrip().rstrip() myMember = Member.objects.filter( user__username=memberCode).first() logger.info(str(int(memberCode))) if myMember is None: myUser = User.objects.create_user(username=memberCode, email=email, password=password) Member.objects.create(user=myUser, boxID=boxID, name=name) else: myUser = myMember.user myUser.email = email myUser.save() myMember = Member.objects.filter( user__username=memberCode).first() #myMember=Member.objects.filter(boxID=memberCode).first() if myMember is None: allMembersFound = 0 logger.info("Member not found %s " % (memberCode)) else: if vegetableCost != 0: error = addMemberTransaction(logger, myMember, "VEGBOX", 1, vegetableCost, "DR", orderDate) #Making the credit and Adjustment if isBeforeSoftware == True: if worksheet.cell_type(curr_row, creditIndex) != 0: credit = str( worksheet.cell_value(curr_row, creditIndex)).replace( "-", "").lstrip().rstrip() error = addMemberTransaction(logger, myMember, "CRR", 1, credit, "CR", orderDate) if worksheet.cell_type(curr_row, adjustmentIndex) != 0: adjustment = str( worksheet.cell_value( curr_row, adjustmentIndex)).lstrip().rstrip() logger.info(adjustment) adjustmentReason = str( worksheet.cell_value( curr_row, adjustmentReasonIndex)).replace( "-", "").lstrip().rstrip() if "-" in adjustment: logger.info("Adjustment is Negative") adjustmentTransactionType = "CR" adjustment = adjustment[1:] else: adjustmentTransactionType = "DR" error = addMemberTransaction(logger, myMember, "ADJST", 1, adjustment, adjustmentTransactionType, orderDate, remarks=adjustmentReason) #This is for calcuating Credits if creditSheetIndex != 0: worksheet = book.sheet_by_index(creditSheetIndex) num_cols = worksheet.ncols - 1 num_rows = worksheet.nrows - 1 curr_row = 0 while curr_row < num_rows: curr_row += 1 memberCode = int(worksheet.cell_value(curr_row, 0)) logger.info("Credit memberCode %s " % str(memberCode)) Amount = worksheet.cell_value(curr_row, 1) creditDateExcel = worksheet.cell_value(curr_row, 2) creditDate = datetime( *xlrd.xldate_as_tuple(creditDateExcel, 0)).date() logger.info(str(memberCode) + str(Amount) + str(creditDate)) myMember = Member.objects.filter( user__username=memberCode).first() if myMember is not None: error = addMemberTransaction(logger, myMember, "CRR", 1, Amount, "CR", creditDate) #This is for calculating Products if eachOrderForm.isItemizedOrderFormat == False: worksheet = book.sheet_by_index(sheetIndex) isComplete = 1 firstRow = worksheet.row_values(0) logger.info(str(firstRow)) num_cols = worksheet.ncols - 1 curr_col = startCol allMembersFound = 1 while curr_col < num_cols: curr_col += 1 if worksheet.cell_type(memberIndex, curr_col) != 0: memberCode = str( worksheet.cell_value(memberIndex, curr_col)).replace("-", "") memberCode = memberCode[2:] # logger.info(memberCode) if isBeforeSoftware == True: myMember = Member.objects.filter( user__username=memberCode).first() else: myMember = Member.objects.filter( user__username=memberCode).first() #myMember=Member.objects.filter(boxID=memberCode).first() if myMember is None: allMembersFound = 0 logger.info("Member not found %s " % (memberCode)) else: curr_row = 2 while curr_row < maxOrderRow: curr_row += 1 if worksheet.cell_type(curr_row, curr_col) != 0: if worksheet.cell_type(curr_row, priceIndex) != 0: productName = worksheet.cell_value( curr_row, productNameIndex).lstrip().rstrip() productCode = worksheet.cell_value( curr_row, productCodeIndex).lstrip().rstrip() price = worksheet.cell_value( curr_row, priceIndex) quantity = worksheet.cell_value( curr_row, curr_col) if float(quantity) != 0: logger.info( "Product Code: %s Price : %s Quantity %s Member %s " % (productCode, price, str(int(quantity)), memberCode)) error = addMemberTransaction( logger, myMember, productCode, quantity, price, "DR", orderDate) if error is not None: isComplete = False else: logger.info("I am in itemized biling") productCodeIndex = 15 priceIndex = 12 quantityIndex = 11 orderStatusIndex = 19 worksheet = book.sheet_by_index(sheetIndex) isComplete = 1 num_rows = worksheet.nrows - 1 num_cols = worksheet.ncols - 1 curr_col = startCol allMembersFound = 1 row = 0 while row < num_rows: row = row + 1 rowValues = worksheet.row_values(row) # logger.info(str(rowValues)) logger.info(row) logger.info(worksheet.cell_value(row, 1)) memberCode = int(worksheet.cell_value(row, 1)) myMember = Member.objects.filter( user__username=memberCode).first() productCode = worksheet.cell_value( row, productCodeIndex).lstrip().rstrip() orderStatus = worksheet.cell_value( row, orderStatusIndex).lstrip().rstrip() quantity = worksheet.cell_value(row, quantityIndex) price = worksheet.cell_value(row, priceIndex) error = addMemberTransaction(logger, myMember, productCode, quantity, price, "DR", orderDate, orderStatus=orderStatus) if (isComplete == True) and (allMembersFound == 1): logger.info("Sheet is Completely Processed") logger.info("...END PROCESSING") exit(0)
from xlwt import Workbook #input_file = sys.argv[1] #output_file = sys.argv[2] input_file = 'excel_files\\sales_2013.xlsx' output_file = 'excel_files\\6output.xls' output_workbook = Workbook() output_worksheet = output_workbook.add_sheet('jan_2013_output') important_dates = ['31/12/2013', '30/11/2013'] purchase_date_column_index = 4 with open_workbook(input_file) as workbook: worksheet = workbook.sheet_by_name('january_2013') data = [] header = worksheet.row_values(0) data.append(header) for row_index in range(1, worksheet.nrows): purchase_datetime = xldate_as_tuple(worksheet.cell_value(row_index, purchase_date_column_index),\ workbook.datemode) purchase_date = date(*purchase_datetime[0:3]).strftime('%d/%m/%Y') row_list = [] if purchase_date in important_dates: for column_index in range(worksheet.ncols): cell_value = worksheet.cell_value(row_index, column_index) cell_type = worksheet.cell_type(row_index, column_index) if cell_type == 3: date_cell = xldate_as_tuple(cell_value, workbook.datemode) date_cell = date(*date_cell[0:3]).strftime('%d/%m/%Y') row_list.append(date_cell) else: row_list.append(cell_value) if row_list: data.append(row_list) for list_index, output_list in enumerate(data):
def customer_import(): if request.method == 'POST': file = request.files['file'] filename = file.filename # 判断文件名是否合规 if file and allowed_file(filename): file.save(os.path.join('./upload', filename)) else: flash('失败:上传文件格式不对') return "导入失败!上传文件格式不对" # 添加到数据库 tables = excel_table_byindex(file='./upload/' + filename) sucess = 0 for row in tables: # 判断表格式是否对 if 'custid' not in row or 'custname' not in row or\ 'contact' not in row or 'baseinfo' not in row or\ 'status' not in row or 'fine' not in row or\ 'content' not in row or 'usedbattery' not in row or\ 'memo' not in row or 'date' not in row or\ 'saleman' not in row: flash('失败:excel表格式不对') return "导入失败!excel表格式不对" # 判断字段是否存在 #if row['custid'] != '' and row['custname'] != '' and \ # row['contact'] != '' and row['status'] != '': if row['date'] == '': tmpdate = '' else: tmpdate = date(*(xlrd.xldate_as_tuple(row['date'], 0) )[:3]).strftime('%Y-%m-%d') cust_info = custinfo(custid=row['custid'], custname=row['custname'], contact=row['contact'], baseinfo=row['baseinfo'], address=row['address'], status=row['status'], fine=row['fine'], content=row['content'], usedbattery=row['usedbattery'], memo=row['memo'], date=tmpdate, saleman=row['saleman'], createtime=timestamp_utc_now(), updatetime=timestamp_utc_now()) # 判断是否id重复 flag = True #if Device.query.filter_by(device_id=device.device_id).count() > 0: # flash('失败:设备ID:%s已存在' %device.device_id) # flag = False # 判断simid是否重复 if flag: db.session.add(cust_info) sucess += 1 else: return "导入失败!重复数据!" try: db.session.commit() except: db.session.rollback() return "导入失败!rollback!" return "导入成功!导入了" + str(sucess) + "条客户信息!"
def read_sunrise_xlsx(filename, label=u'OD', max_time=None, plate=None): """Reads growth measurements from a Tecan Sunrise Excel output file. Parameters ---------- filename : str pattern of the XLSX files to be read. Use * and ? in filename to read multiple files and parse them into a single data frame. label : str, optional label : str, optional measurment name to use for the data in the file, defaults to ``OD``. max_time : float, optional maximal time in hours, defaults to infinity plate : pandas.DataFrame, optional data frame representing a plate, usually generated by reading a CSV file generated by `Plato <http://plato.yoavram.com/>`_. Returns ------- pandas.DataFrame Data frame containing the columns: - ``Time`` (:py:class:`float`, in hours) - ``OD`` (or the value of `label`, if given) - ``Well`` (:py:class:`str`): the well name, usually a letter for the row and a number of the column. - ``Row`` (:py:class:`str`): the letter corresponding to the well row. - ``Col`` (:py:class:`str`): the number corresponding to the well column. - ``Filename`` (:py:class:`str`): the filename from which this measurement was read. - ``Strain`` (:py:class:`str`): if a `plate` was given, this is the strain name corresponding to the well from the plate. - ``Color`` (:py:class:`str`, hex format): if a `plate` was given, this is the strain color corresponding to the well from the plate. """ import xlrd dataframes = [] files = glob(filename) if not files: return pd.DataFrame() for filename in files: wb = xlrd.open_workbook(filename) for sh in wb.sheets(): if sh.nrows > 0: break parse_data = False # start with metadata index = [] data = [] for i in range(sh.nrows): row = sh.row_values(i) if row[0] == u'Date:': date = next(filter(lambda x: isinstance(x, float), row[1:])) date = xlrd.xldate_as_tuple(date, 0) elif row[0] == u'Time:': time = next(filter(lambda x: isinstance(x, float), row[1:])) time = xlrd.xldate_as_tuple(time, 0) elif row[0] == u'<>': columns = list(map(int, row[1:])) parse_data = True elif len(row[0]) == 0 and parse_data: break elif parse_data: index.append(row[0]) data.append(list(map(float, row[1:]))) dateandtime = date[:3] + time[-3:] dateandtime = datetime.datetime(*dateandtime) df = pd.DataFrame(data, columns=columns, index=index) df[u'Row'] = index df = pd.melt(df, id_vars=u'Row', var_name=u'Col', value_name=label) df[u'Time'] = dateandtime df[u'Well'] = [x[0] + str(x[1]) for x in zip(df.Row, df.Col)] df[u'Filename'] = os.path.split(filename)[-1] dataframes.append(df) df = pd.concat(dataframes) min_time = df.Time.min() df.Time = [(t - min_time).total_seconds() / 3600.0 for t in df.Time] if plate is None: df[u'Strain'] = u'0' df[u'Color'] = u'#000000' else: df = pd.merge(df, plate, on=(u'Row', u'Col')) if max_time is not None: df = df[df.Time <= max_time] df.sort_values([u'Row', u'Col', u'Time'], inplace=True) _fix_dtypes(df) return df
if column_number >= 2: print "增量计算,先获得曾经的数据" last_row_total_dict = {}.fromkeys(list_field_list, 1) for index_list in in_sheet.merged_cells: field = in_sheet.cell_value(index_list[0], index_list[2]).replace( "\n", "").encode("utf-8") last_row_total_dict[field] = index_list[1] - index_list[0] for i in range(1, column_number): this_row = 0 date = in_sheet.cell_value(1, i) #先处理一列的数据 for j in range(value_field_number): field = field_list[j] if field == "日期": try: date_tuple = xlrd.xldate_as_tuple( in_sheet.cell_value(j, i), 0) date_list = [str(date_tuple[i]) for i in range(0, 3)] total_dict[field]["child"].append("/".join(date_list)) this_row += 1 except: total_dict[field]["child"].append( in_sheet.cell_value(j, i)) this_row += 1 else: total_dict[field]["child"].append(in_sheet.cell_value( j, i)) this_row += 1 #再处理多列的数据 for field in list_field_list: total_dict[field]["child"][date] = [] for j in range(value_field_number, field_number):
def old_scrape(self, session=None): status_report_url = ( "https://www.legislature.ohio.gov/legislation/status-reports") # ssl verification off due Ohio not correctly implementing SSL if not session: session = self.latest_session() self.info("no session, using %s", session) doc = self.get(status_report_url).text doc = lxml.html.fromstring(doc) doc.make_links_absolute(status_report_url) xpath = "//div[contains(text(),'{}')]/following-sibling::table" status_table = doc.xpath(xpath.format(session))[0] status_links = status_table.xpath( ".//a[contains(text(),'Excel')]/@href") for url in status_links: try: fname, resp = self.urlretrieve(url) except scrapelib.HTTPError as report: self.logger.warning("Missing report {}".format(report)) continue sh = xlrd.open_workbook(fname).sheet_by_index(0) # once workbook is open, we can remove tempfile os.remove(fname) for rownum in range(1, sh.nrows): bill_id = sh.cell(rownum, 0).value bill_type = "resolution" if "R" in bill_id else "bill" chamber = "lower" if "H" in bill_id else "upper" bill_title = str(sh.cell(rownum, 3).value) bill = Bill( bill_id, legislative_session=session, chamber=chamber, title=bill_title, classification=bill_type, ) bill.add_source(url) bill.add_sponsor("primary", str(sh.cell(rownum, 1).value)) # add cosponsor if sh.cell(rownum, 2).value: bill.add_sponsor("cosponsor", str(sh.cell(rownum, 2).value)) actor = "" # Actions start column after bill title for colnum in range(4, sh.ncols - 1): action = str(sh.cell(0, colnum).value) cell = sh.cell(rownum, colnum) date = cell.value if len(action) != 0: if action.split()[0] == "House": actor = "lower" elif action.split()[0] == "Senate": actor = "upper" elif action.split()[-1] == "Governor": actor = "executive" elif action.split()[0] == "Gov.": actor = "executive" elif action.split()[-1] == "Gov.": actor = "executive" if action in ("House Intro. Date", "Senate Intro. Date"): atype = ["bill:introduced"] action = action.replace("Intro. Date", "Introduced") elif action == "3rd Consideration": atype = ["bill:reading:3", "bill:passed"] elif action == "Sent to Gov.": atype = ["governor:received"] elif action == "Signed By Governor": atype = ["governor:signed"] else: atype = ["other"] if type(date) == float: date = str(xlrd.xldate_as_tuple(date, 0)) date = datetime.datetime.strptime( date, "(%Y, %m, %d, %H, %M, %S)") date = self._tz.localize(date) date = "{:%Y-%m-%d}".format(date) bill.add_action(actor, action, date, type=atype) for idx, char in enumerate(bill_id): try: int(char) except ValueError: continue underscore_bill = bill_id[:idx] + "_" + bill_id[idx:] break yield from self.scrape_votes_old(bill, underscore_bill, session) self.scrape_versions_old(bill, underscore_bill, session) yield bill
dic = divide_year(main_month) r_price = [] r_yield = [] ln_yield = [] raw_mat = [] for j in range(len(main_month)): raw_mat.append([]) for i in range(1,nrows): for j in range(1,4): raw_mat[j-1].append(sum(datasheet.row_values(i)[j::3])) for i in range(1,nrows): year, month, day, a, b, c = xlrd.xldate_as_tuple(datasheet.cell(i,0).value, 0) if i == 1 or raw_mat[dic[month]][i-2] == 0: r_price.append(raw_mat[dic[month]][i-1]) r_yield.append(-12345) ln_yield.append(-12345) continue r_price.append(raw_mat[dic[month]][i-1]) r_yield.append((raw_mat[dic[month]][i-1]/raw_mat[dic[month]][i-2])-1) ln_yield.append(math.log(1+r_yield[i-1])) sigma30 = backvar(30, ln_yield) sigma60 = backvar(60, ln_yield) sigma90 = backvar(90, ln_yield) output = xlwt.Workbook(encoding = 'ascii') sheet1 = output.add_sheet('Sheet1',cell_overwrite_ok= True)
import sys from datetime import date from xlrd import open_workbook, xldate_as_tuple from xlwt import Workbook input_file = sys.argv[1] output_file = sys.argv[2] output_workbook = Workbook() output_worksheet = output_workbook.add_sheet('jan_2013_output') with open_workbook(input_file) as workbook: worksheet = workbook.sheet_by_name('january_2013') for row_index in range(worksheet.nrows): row_list_output = [] for col_index in range(worksheet.ncols): if worksheet.cell_type(row_index, col_index) == 3: date_cell = xldate_as_tuple(worksheet.cell_value\ (row_index, col_index), workbook.datemode) date_cell = date(*date_cell[0:3]).strftime\ ('%m/%d/%Y') row_list_output.append(date_cell) output_worksheet.write(row_index, col_index, date_cell) else: non_date_cell = worksheet.cell_value\ (row_index, col_index) row_list_output.append(non_date_cell) output_worksheet.write(row_index, col_index, \ non_date_cell) output_workbook.save(output_file)
# 获取第 3 列内容 print(sheet1.col_values(2)) cell1 = sheet1.cell(1, 1).value # 行索引 cell2 = sheet1.row(1)[1].value cell3 = sheet1.cell(1, 2).value # 列索引 cell4 = sheet1.col(2)[1].value print(cell1, cell2, cell3, cell4) date_value = xlrd.xldate_as_datetime(sheet1.cell_value(5, 3), workbook.datemode) print(type(date_value), date_value) date_tulp = xlrd.xldate_as_tuple(sheet1.cell_value(5, 3), workbook.datemode) print(type(date_tulp), date_tulp) year, month, day, hour, minute, second = date_tulp print(datetime.datetime(year, month, day, hour, minute, second)) # 求平均作业开始 yunwen_list = [] shuxue_list = [] yingyu_list = [] for sheet in workbook.sheets(): # 获取语文分数 for value in sheet1.col_values(1): # 这里取巧,如果类型是 float ,则加入 yunwen_list 中 if type(value) == float: yunwen_list.append(value)