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)
